-- Telegram Multi-Channel Uploader Bot - Database Schema
-- PHP 8.1+ / MySQL 5.7+ / MariaDB 10.3+
-- Charset: utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `username` VARCHAR(100) DEFAULT NULL,
  `first_name` VARCHAR(100) DEFAULT NULL,
  `is_super_admin` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_telegram_id` (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `username` VARCHAR(100) DEFAULT NULL,
  `first_name` VARCHAR(100) DEFAULT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `language_code` VARCHAR(10) DEFAULT 'fa',
  `is_blocked` TINYINT(1) DEFAULT 0,
  `is_banned` TINYINT(1) DEFAULT 0,
  `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_telegram_id` (`telegram_id`),
  KEY `idx_is_blocked` (`is_blocked`),
  KEY `idx_is_banned` (`is_banned`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `channel_id` BIGINT NOT NULL COMMENT 'Telegram channel chat_id (negative)',
  `channel_username` VARCHAR(100) DEFAULT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `is_forced_join` TINYINT(1) DEFAULT 0 COMMENT 'Required for forced membership',
  `invite_link` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_channel_id` (`channel_id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_forced_join` (`is_forced_join`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `files` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_unique_id` VARCHAR(64) NOT NULL COMMENT 'Telegram file_unique_id',
  `file_id` VARCHAR(255) NOT NULL COMMENT 'Telegram file_id for download/copy',
  `file_type` ENUM('document','photo','video','audio','voice','animation','video_note') NOT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `file_name` VARCHAR(255) DEFAULT NULL,
  `file_size` BIGINT UNSIGNED DEFAULT 0,
  `caption` TEXT DEFAULT NULL,
  `uploader_id` BIGINT NOT NULL COMMENT 'telegram_id of uploader',
  `message_id` INT UNSIGNED DEFAULT NULL COMMENT 'Original message_id in bot chat',
  `chat_id` BIGINT DEFAULT NULL,
  `download_token` VARCHAR(64) NOT NULL COMMENT 'Unique token for download links',
  `download_count` INT UNSIGNED DEFAULT 0,
  `host_download_count` INT UNSIGNED DEFAULT 0 COMMENT 'Downloads from host',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_download_token` (`download_token`),
  KEY `idx_file_unique_id` (`file_unique_id`),
  KEY `idx_uploader_id` (`uploader_id`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `file_channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_id` INT UNSIGNED NOT NULL,
  `channel_id` INT UNSIGNED NOT NULL,
  `channel_message_id` INT UNSIGNED DEFAULT NULL,
  `copied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_file_channel` (`file_id`,`channel_id`),
  KEY `fk_fc_channel` (`channel_id`),
  CONSTRAINT `fk_fc_file` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fc_channel` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `download_links` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `max_uses` INT UNSIGNED DEFAULT 1,
  `used_count` INT UNSIGNED DEFAULT 0,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_token` (`token`),
  KEY `idx_file_id` (`file_id`),
  KEY `idx_expires_at` (`expires_at`),
  CONSTRAINT `fk_dl_file` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `broadcasts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` BIGINT NOT NULL,
  `message_type` ENUM('text','photo','video','document','audio','voice','animation') NOT NULL DEFAULT 'text',
  `content` TEXT,
  `file_id` VARCHAR(255) DEFAULT NULL,
  `caption` TEXT DEFAULT NULL,
  `parse_mode` VARCHAR(20) DEFAULT 'HTML',
  `status` ENUM('pending','processing','completed','cancelled') DEFAULT 'pending',
  `total_users` INT UNSIGNED DEFAULT 0,
  `sent_count` INT UNSIGNED DEFAULT 0,
  `failed_count` INT UNSIGNED DEFAULT 0,
  `last_user_id` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `error_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level` ENUM('error','warning','info','debug') DEFAULT 'error',
  `message` TEXT NOT NULL,
  `context` TEXT DEFAULT NULL,
  `file` VARCHAR(255) DEFAULT NULL,
  `line` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_level` (`level`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `duplicate_checks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_unique_id` VARCHAR(64) NOT NULL,
  `uploader_id` BIGINT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_file_uploader` (`file_unique_id`,`uploader_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stats` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `stat_date` DATE NOT NULL,
  `uploads_count` INT UNSIGNED DEFAULT 0,
  `downloads_count` INT UNSIGNED DEFAULT 0,
  `host_downloads_count` INT UNSIGNED DEFAULT 0,
  `host_bytes_used` BIGINT UNSIGNED DEFAULT 0,
  `new_users` INT UNSIGNED DEFAULT 0,
  `active_users` INT UNSIGNED DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stat_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('bot_token', ''),
('bot_username', ''),
('admin_telegram_id', ''),
('webhook_secret', ''),
('force_join_enabled', '1'),
('max_host_downloads', '100'),
('current_host_downloads', '0'),
('host_download_enabled', '1'),
('link_expire_minutes', '60'),
('max_file_size_mb', '50'),
('welcome_message', 'سلام! 👋\nبه ربات آپلودر خوش آمدید.\nفایل، عکس، ویدیو یا صوت خود را ارسال کنید.'),
('force_join_message', '⚠️ برای استفاده از ربات باید در کانال‌های زیر عضو شوید:'),
('admin_panel_password', ''),
('site_url', ''),
('timezone', 'Asia/Tehran')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

SET FOREIGN_KEY_CHECKS = 1;