-- اسکریپت به‌روزرسانی ساختار دیتابیس بدون حذف داده‌های موجود

SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS bot_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(64),
    name VARCHAR(255),
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    has_blocked_bot TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @needs_name := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'name');
SET @sql := IF(@needs_name = 0, 'ALTER TABLE bot_users ADD COLUMN name VARCHAR(255) AFTER username', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_joined := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'joined_at');
SET @sql := IF(@needs_joined = 0, 'ALTER TABLE bot_users ADD COLUMN joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_is_blocked := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'is_blocked');
SET @sql := IF(@needs_is_blocked = 0, 'ALTER TABLE bot_users ADD COLUMN is_blocked TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_has_blocked_bot := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'has_blocked_bot');
SET @sql := IF(@needs_has_blocked_bot = 0, 'ALTER TABLE bot_users ADD COLUMN has_blocked_bot TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := 'ALTER TABLE bot_users MODIFY last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP';
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_first_name := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'first_name');
SET @has_last_name := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'last_name');
SET @sql := IF(@has_first_name > 0 OR @has_last_name > 0,
    'UPDATE bot_users SET name = TRIM(CONCAT(IFNULL(first_name, ''''), '' '', IFNULL(last_name, ''''))) WHERE (name IS NULL OR name = '''') AND (first_name IS NOT NULL OR last_name IS NOT NULL)',
    'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_first_name > 0, 'ALTER TABLE bot_users DROP COLUMN first_name', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@has_last_name > 0, 'ALTER TABLE bot_users DROP COLUMN last_name', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_language := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'language_code');
SET @sql := IF(@has_language > 0, 'ALTER TABLE bot_users DROP COLUMN language_code', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_reason := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'bot_users' AND COLUMN_NAME = 'block_reason');
SET @sql := IF(@has_reason > 0, 'ALTER TABLE bot_users DROP COLUMN block_reason', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(64),
    full_name VARCHAR(128),
    role ENUM('owner','manager','editor') NOT NULL DEFAULT 'manager',
    pending_mode ENUM('idle','single','batch','delete','info','user_lookup','user_manage','user_message','user_stats','force_intro','force_add','force_verify','force_goal','force_target','force_remove','broadcast_filter','broadcast_options','broadcast_message') NOT NULL DEFAULT 'idle',
    pending_code INT UNSIGNED DEFAULT NULL,
    pending_position INT UNSIGNED NOT NULL DEFAULT 1,
    pending_group_id VARCHAR(128) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @sql := 'ALTER TABLE admins MODIFY pending_mode ENUM(''idle'',''single'',''batch'',''delete'',''info'',''user_lookup'',''user_manage'',''user_message'',''user_stats'',''force_intro'',''force_add'',''force_verify'',''force_goal'',''force_target'',''force_remove'',''broadcast_filter'',''broadcast_options'',''broadcast_message'') NOT NULL DEFAULT ''idle''';
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS force_channels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL UNIQUE,
    chat_type ENUM('channel','supergroup','bot') NOT NULL DEFAULT 'channel',
    title VARCHAR(255) NOT NULL,
    username VARCHAR(64) DEFAULT NULL,
    invite_link VARCHAR(255) DEFAULT NULL,
    added_by BIGINT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    bot_is_admin TINYINT(1) NOT NULL DEFAULT 0,
    target_joins INT UNSIGNED DEFAULT NULL,
    current_joins INT UNSIGNED NOT NULL DEFAULT 0,
    member_baseline INT UNSIGNED DEFAULT NULL,
    member_snapshot INT UNSIGNED DEFAULT NULL,
    total_invites INT UNSIGNED NOT NULL DEFAULT 0,
    last_checked_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @needs_chat_type := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'chat_type');
SET @sql := IF(@needs_chat_type = 0, 'ALTER TABLE force_channels ADD COLUMN chat_type ENUM(''channel'',''supergroup'',''bot'') NOT NULL DEFAULT ''channel'' AFTER chat_id', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_target := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'target_joins');
SET @sql := IF(@needs_target = 0, 'ALTER TABLE force_channels ADD COLUMN target_joins INT UNSIGNED DEFAULT NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_current := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'current_joins');
SET @sql := IF(@needs_current = 0, 'ALTER TABLE force_channels ADD COLUMN current_joins INT UNSIGNED NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_baseline := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'member_baseline');
SET @sql := IF(@needs_baseline = 0, 'ALTER TABLE force_channels ADD COLUMN member_baseline INT UNSIGNED DEFAULT NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_snapshot := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'member_snapshot');
SET @sql := IF(@needs_snapshot = 0, 'ALTER TABLE force_channels ADD COLUMN member_snapshot INT UNSIGNED DEFAULT NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_total_invites := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'total_invites');
SET @sql := IF(@needs_total_invites = 0, 'ALTER TABLE force_channels ADD COLUMN total_invites INT UNSIGNED NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_last_checked := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'last_checked_at');
SET @sql := IF(@needs_last_checked = 0, 'ALTER TABLE force_channels ADD COLUMN last_checked_at TIMESTAMP NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_created := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@needs_created = 0, 'ALTER TABLE force_channels ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_bot_admin := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'bot_is_admin');
SET @sql := IF(@needs_bot_admin = 0, 'ALTER TABLE force_channels ADD COLUMN bot_is_admin TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_is_active := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'is_active');
SET @sql := IF(@needs_is_active = 0, 'ALTER TABLE force_channels ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_added_by := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'added_by');
SET @sql := IF(@needs_added_by = 0, 'ALTER TABLE force_channels ADD COLUMN added_by BIGINT NOT NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_total_joins := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'total_joins');
SET @sql := IF(@needs_total_joins = 0, 'ALTER TABLE force_channels ADD COLUMN total_joins INT UNSIGNED NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_total_left := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'force_channels' AND COLUMN_NAME = 'total_left');
SET @sql := IF(@needs_total_left = 0, 'ALTER TABLE force_channels ADD COLUMN total_left INT UNSIGNED NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS media_library (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code INT UNSIGNED NOT NULL,
    media_type ENUM('audio','video','photo','document') NOT NULL,
    file_id VARCHAR(255) NOT NULL,
    caption TEXT,
    performer VARCHAR(128),
    title VARCHAR(255),
    position INT UNSIGNED NOT NULL DEFAULT 1,
    download_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_by BIGINT,
    group_message_id BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_code_position (code, position),
    INDEX idx_code (code),
    CONSTRAINT fk_media_admin FOREIGN KEY (created_by) REFERENCES admins(telegram_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @needs_group_message := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'media_library' AND COLUMN_NAME = 'group_message_id');
SET @sql := IF(@needs_group_message = 0, 'ALTER TABLE media_library ADD COLUMN group_message_id BIGINT UNSIGNED DEFAULT NULL', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_download_count := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'media_library' AND COLUMN_NAME = 'download_count');
SET @sql := IF(@needs_download_count = 0, 'ALTER TABLE media_library ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_created_by := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'media_library' AND COLUMN_NAME = 'created_by');
SET @sql := IF(@needs_created_by = 0, 'ALTER TABLE media_library ADD COLUMN created_by BIGINT', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_media_created := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'media_library' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@needs_media_created = 0, 'ALTER TABLE media_library ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor VARCHAR(64) NOT NULL,
    action VARCHAR(64) NOT NULL,
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcast_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending','running','cancelled','completed') NOT NULL DEFAULT 'pending',
    filter ENUM('all','day','week','month') NOT NULL DEFAULT 'all',
    source_chat_id BIGINT NOT NULL,
    source_message_id BIGINT NOT NULL,
    created_by BIGINT NOT NULL,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    failed_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_count INT UNSIGNED NOT NULL DEFAULT 0,
    auto_delete_seconds INT UNSIGNED NOT NULL DEFAULT 0,
    pin_message TINYINT(1) NOT NULL DEFAULT 0,
    progress_chat_id BIGINT DEFAULT NULL,
    progress_message_id BIGINT DEFAULT NULL,
    started_at TIMESTAMP NULL,
    finished_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_broadcast_status (status),
    INDEX idx_broadcast_creator (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @needs_auto_delete := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'broadcast_jobs' AND COLUMN_NAME = 'auto_delete_seconds');
SET @sql := IF(@needs_auto_delete = 0, 'ALTER TABLE broadcast_jobs ADD COLUMN auto_delete_seconds INT UNSIGNED NOT NULL DEFAULT 0 AFTER total_count', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @needs_pin_message := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'broadcast_jobs' AND COLUMN_NAME = 'pin_message');
SET @sql := IF(@needs_pin_message = 0, 'ALTER TABLE broadcast_jobs ADD COLUMN pin_message TINYINT(1) NOT NULL DEFAULT 0 AFTER auto_delete_seconds', 'SELECT 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS broadcast_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    last_error VARCHAR(255) DEFAULT NULL,
    last_attempt_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_job_user (job_id, user_id),
    INDEX idx_queue_job_status (job_id, status),
    CONSTRAINT fk_broadcast_queue_job FOREIGN KEY (job_id) REFERENCES broadcast_jobs(id) ON DELETE CASCADE,
    CONSTRAINT fk_broadcast_queue_user FOREIGN KEY (user_id) REFERENCES bot_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcast_deletes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT UNSIGNED NOT NULL,
    chat_id BIGINT NOT NULL,
    message_id BIGINT NOT NULL,
    delete_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_broadcast_delete_time (delete_at),
    CONSTRAINT fk_broadcast_deletes_job FOREIGN KEY (job_id) REFERENCES broadcast_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS force_channel_members (
    channel_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    telegram_id BIGINT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (channel_id, telegram_id),
    INDEX idx_force_member_channel (channel_id),
    CONSTRAINT fk_force_member_channel FOREIGN KEY (channel_id) REFERENCES force_channels(id) ON DELETE CASCADE,
    CONSTRAINT fk_force_member_user FOREIGN KEY (user_id) REFERENCES bot_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
