-- ============================================================
-- Migration امن برای دیتابیس موجود ربات
-- قبل از اجرا حتماً Backup بگیرید.
-- ============================================================
USE my_database;

DELIMITER $$
CREATE PROCEDURE apply_bot_performance_migration()
BEGIN
    DECLARE n INT DEFAULT 0;

    -- ستون‌های موردنیاز file
    SELECT COUNT(*) INTO n FROM information_schema.columns
      WHERE table_schema = DATABASE() AND table_name='file' AND column_name='is_subscription';
    IF n=0 THEN
      ALTER TABLE `file` ADD COLUMN `is_subscription` TINYINT(1) DEFAULT 0;
    END IF;

    SELECT COUNT(*) INTO n FROM information_schema.columns
      WHERE table_schema = DATABASE() AND table_name='file' AND column_name='original_filename';
    IF n=0 THEN
      ALTER TABLE `file` ADD COLUMN `original_filename` TEXT DEFAULT NULL;
    END IF;

    -- composite index اصلی Queryهای سریال/فصل/قسمت
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='file' AND index_name='idx_file_imdb_dub_season_episode_id';
    IF n=0 THEN
      ALTER TABLE `file` ADD INDEX `idx_file_imdb_dub_season_episode_id` (`imdb_id`,`is_dubbed`,`season`,`episode`,`id`);
    END IF;

    -- Queryهای لیست فایل‌های ادمین بر اساس تاریخ
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='file' AND index_name='idx_file_user_visible_date';
    IF n=0 THEN
      ALTER TABLE `file` ADD INDEX `idx_file_user_visible_date` (`user_id`,`is_visible`,`date`,`time`,`id`);
    END IF;

    -- lookupهای مستقیم IMDb + visible
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='file' AND index_name='idx_file_imdb_visible';
    IF n=0 THEN
      ALTER TABLE `file` ADD INDEX `idx_file_imdb_visible` (`imdb_id`,`is_visible`);
    END IF;

    -- Full-text واقعی برای search
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='file' AND index_name='idx_file_search';
    IF n=0 THEN
      ALTER TABLE `file` ADD FULLTEXT KEY `idx_file_search` (`caption`,`btn_name`,`original_filename`);
    END IF;

    -- settings: lookupهای type + type_id
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='settings' AND index_name='idx_type_typeid';
    IF n=0 THEN
      ALTER TABLE `settings` ADD INDEX `idx_type_typeid` (`type`,`type_id`(50));
    END IF;

    -- reverse lookup followers
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='follows' AND index_name='idx_base_file_id';
    IF n=0 THEN
      ALTER TABLE `follows` ADD INDEX `idx_base_file_id` (`base_file_id`);
    END IF;

    -- reverse lookup user_downloads
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='user_downloads' AND index_name='idx_file_id';
    IF n=0 THEN
      ALTER TABLE `user_downloads` ADD INDEX `idx_file_id` (`file_id`);
    END IF;

    -- صف امتیازدهی: پیدا کردن pendingهای موعدرسیده
    SELECT COUNT(*) INTO n FROM information_schema.statistics
      WHERE table_schema=DATABASE() AND table_name='rating_queue' AND index_name='idx_status_send_time';
    IF n=0 THEN
      ALTER TABLE `rating_queue` ADD INDEX `idx_status_send_time` (`status`,`send_time`);
    END IF;
END$$
DELIMITER ;

CALL apply_bot_performance_migration();
DROP PROCEDURE apply_bot_performance_migration;

-- بررسی نتیجه:
-- SHOW INDEX FROM file;
-- SHOW INDEX FROM settings;