-- ============================================================
-- Enterprise Telegram Bot Management System
-- Core database schema (Bot core + Force Join + Users)
-- MySQL 5.7+ / MariaDB 10.3+  |  utf8mb4
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Settings (key/value config, editable at runtime) ----------
CREATE TABLE IF NOT EXISTS `settings` (
  `k` VARCHAR(64) NOT NULL,
  `v` TEXT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Admins (web + telegram panel) ----------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `telegram_id` BIGINT NULL,
  `role` ENUM('superadmin','admin','moderator') NOT NULL DEFAULT 'admin',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`),
  KEY `idx_tg` (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Users ----------
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `username` VARCHAR(64) NULL,
  `first_name` VARCHAR(128) NULL,
  `last_name` VARCHAR(128) NULL,
  `language` VARCHAR(8) NULL DEFAULT 'en',
  `country` VARCHAR(64) NULL,
  `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
  `is_blocked` TINYINT(1) NOT NULL DEFAULT 0,
  `is_bot_blocked` TINYINT(1) NOT NULL DEFAULT 0, -- user blocked the bot
  `referral_by` BIGINT NULL,
  `admin_notes` TEXT NULL,
  `joined_channels` TINYINT(1) NOT NULL DEFAULT 0, -- last force-join verification result
  `state` VARCHAR(64) NULL, -- conversation state machine
  `join_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_active` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tg` (`telegram_id`),
  KEY `idx_ref` (`referral_by`),
  KEY `idx_premium` (`is_premium`),
  KEY `idx_active` (`last_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Force Join channels ----------
CREATE TABLE IF NOT EXISTS `force_channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NULL,
  `chat_id` VARCHAR(64) NULL,          -- numeric id like -100xxxx (for private/API checks)
  `username` VARCHAR(64) NULL,         -- @publicchannel (no @ stored)
  `invite_link` VARCHAR(255) NULL,     -- for private channels
  `type` ENUM('public','private') NOT NULL DEFAULT 'public',
  `is_group` TINYINT(1) NOT NULL DEFAULT 0,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `priority` INT NOT NULL DEFAULT 0,   -- lower = shown first
  `mode` ENUM('permanent','temporary') NOT NULL DEFAULT 'permanent',
  `expires_at` DATETIME NULL,          -- for temporary force join
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_enabled` (`is_enabled`),
  KEY `idx_priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Force Join verification history ----------
CREATE TABLE IF NOT EXISTS `force_join_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `channel_id` INT UNSIGNED NULL,
  `result` ENUM('joined','not_joined') NOT NULL,
  `checked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`telegram_id`),
  KEY `idx_time` (`checked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Activity logs ----------
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NULL,
  `action` VARCHAR(64) NOT NULL,
  `detail` VARCHAR(512) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`telegram_id`),
  KEY `idx_action` (`action`),
  KEY `idx_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Rate limiting ----------
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `telegram_id` BIGINT NOT NULL,
  `window_start` INT UNSIGNED NOT NULL, -- unix timestamp of window
  `hits` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------- Default settings ----------
INSERT INTO `settings` (`k`,`v`) VALUES
  ('bot_token',''),
  ('bot_username',''),
  ('webhook_secret',''),
  ('force_join_enabled','1'),
  ('welcome_text','👋 Welcome! You now have full access to the bot.'),
  ('rate_limit_max','20'),
  ('rate_limit_window','10')
ON DUPLICATE KEY UPDATE `k`=`k`;