CREATE DATABASE IF NOT EXISTS my_database DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE my_database;

-- بهینه‌سازی کامل Schema بر اساس Queryهای واقعی ربات.
-- قبل از اجرای این فایل روی دیتابیس موجود، Backup بگیرید.

CREATE TABLE IF NOT EXISTS user (
    id INT(11) NOT NULL AUTO_INCREMENT,
    from_id VARCHAR(20) NOT NULL,
    step VARCHAR(100) DEFAULT 'none',
    downloads INT(20) DEFAULT 0,
    getFile VARCHAR(255) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    update_at BIGINT(20) DEFAULT NULL,
    create_at BIGINT(20) DEFAULT NULL,
    PRIMARY KEY (id), UNIQUE KEY from_id (from_id),
    INDEX idx_update_at (update_at), INDEX idx_create_at (create_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS restricted_files (
    base_file_id VARCHAR(64) NOT NULL PRIMARY KEY,
    added_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS file (
    num INT(11) NOT NULL AUTO_INCREMENT,
    id VARCHAR(255) NOT NULL,
    type VARCHAR(50) DEFAULT 'none',
    file_id TEXT DEFAULT NULL,
    caption TEXT DEFAULT NULL,
    password VARCHAR(255) DEFAULT NULL,
    user_id VARCHAR(20) DEFAULT NULL,
    file_size BIGINT(20) DEFAULT 0,
    downloads INT(11) DEFAULT 0,
    likes INT DEFAULT 0,
    dislikes INT DEFAULT 0,
    thumbnail VARCHAR(255) DEFAULT NULL,
    date VARCHAR(20) DEFAULT NULL,
    time VARCHAR(20) DEFAULT NULL,
    imdb_id VARCHAR(25) DEFAULT NULL,
    imdb_rating VARCHAR(10) DEFAULT NULL,
    imdb_votes VARCHAR(20) DEFAULT NULL,
    imdb_cache_time BIGINT(20) DEFAULT 0,
    channel_message_id BIGINT DEFAULT NULL,
    update_text TEXT DEFAULT NULL,
    btn_name TEXT DEFAULT NULL,
    is_visible TINYINT(1) DEFAULT 1,
    season INT(11) DEFAULT NULL,
    episode INT(11) DEFAULT NULL,
    is_dubbed TINYINT(1) DEFAULT 0,
    thumbnail_id TEXT DEFAULT NULL,
    original_filename TEXT DEFAULT NULL,
    is_subscription TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (num),
    UNIQUE KEY idx_id (id),
    INDEX idx_file_imdb_dub_season_episode_id (imdb_id, is_dubbed, season, episode, id),
    INDEX idx_file_user_visible_date (user_id, is_visible, date, time, id),
    INDEX idx_file_imdb_visible (imdb_id, is_visible),
    INDEX idx_file_downloads (downloads),
    FULLTEXT KEY idx_file_search (caption, btn_name, original_filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS del (
    num INT(11) NOT NULL AUTO_INCREMENT,
    id VARCHAR(255) NOT NULL,
    user_id VARCHAR(20) NOT NULL,
    message_id BIGINT NOT NULL,
    timeDel BIGINT(20) NOT NULL,
    PRIMARY KEY (num), UNIQUE KEY idx_unique (id, user_id, message_id),
    INDEX idx_user_id (user_id), INDEX idx_timeDel (timeDel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    id INT(11) NOT NULL AUTO_INCREMENT,
    type VARCHAR(50) NOT NULL,
    type_id VARCHAR(255) NOT NULL,
    columnOne TEXT DEFAULT NULL,
    columnTwo TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_type_typeid (type, type_id(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS movie_requests (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL,
    request_text TEXT NOT NULL, status ENUM('pending','uploaded','replied') DEFAULT 'pending',
    admin_reply TEXT DEFAULT NULL, replied_by VARCHAR(20) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, replied_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id), INDEX idx_user_id (user_id), INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS send (
    id INT(11) NOT NULL AUTO_INCREMENT, step VARCHAR(50) NOT NULL, text TEXT NOT NULL,
    `from` VARCHAR(20) NOT NULL DEFAULT '0', user INT(11) NOT NULL DEFAULT 0, PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS file_likes (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),
    UNIQUE KEY unique_like (user_id, base_file_id), INDEX idx_base_file_id (base_file_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS file_ratings (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 10), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id), UNIQUE KEY unique_rating (user_id, base_file_id), INDEX idx_base_file_id (base_file_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS file_comments (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    comment TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id), INDEX idx_base_file_id (base_file_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS follows (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),
    UNIQUE KEY unique_follow (user_id, base_file_id), INDEX idx_base_file_id (base_file_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS follow_notif_log (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    notif_date DATE NOT NULL, PRIMARY KEY (id), UNIQUE KEY unique_notif (user_id, base_file_id, notif_date),
    INDEX idx_base_file_id (base_file_id), INDEX idx_notif_date (notif_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rating_queue (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, base_file_id VARCHAR(64) NOT NULL,
    send_time BIGINT(20) NOT NULL, status ENUM('pending','sent') DEFAULT 'pending', PRIMARY KEY (id),
    INDEX idx_status_send_time (status, send_time), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS forward_map (
    id INT(11) NOT NULL AUTO_INCREMENT, admin_chat_id BIGINT NOT NULL, admin_message_id BIGINT NOT NULL,
    user_id VARCHAR(20) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),
    UNIQUE KEY unique_map (admin_chat_id, admin_message_id), INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_downloads (
    id INT(11) NOT NULL AUTO_INCREMENT, user_id VARCHAR(20) NOT NULL, file_id VARCHAR(64) NOT NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id),
    UNIQUE KEY unique_download (user_id, file_id), INDEX idx_file_id (file_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS series_new_episode (
    id INT(11) NOT NULL AUTO_INCREMENT, imdb_id VARCHAR(25) NOT NULL, message_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id), UNIQUE KEY unique_imdb (imdb_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جداولی که کد PHP در runtime ایجاد می‌کند نیز با کلیدهای مورد نیاز خودشان ایجاد می‌شوند.
CREATE TABLE IF NOT EXISTS movie_custom_info (
    imdb_id VARCHAR(25) NOT NULL PRIMARY KEY,
    fa_title TEXT DEFAULT NULL, en_title TEXT DEFAULT NULL, overview_fa TEXT DEFAULT NULL,
    source ENUM('manual','tmdb','omdb','translate') DEFAULT 'manual',
    metadata_json MEDIUMTEXT DEFAULT NULL, poster_file_id TEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;