-- =====================================================================
-- Telegram Media Bot — MySQL schema
-- Engine: InnoDB, charset: utf8mb4
-- =====================================================================


-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id       BIGINT NOT NULL,
    username          VARCHAR(255) NULL,
    first_name        VARCHAR(255) NULL,
    last_name         VARCHAR(255) NULL,
    is_blocked        TINYINT(1) NOT NULL DEFAULT 0,
    is_admin          TINYINT(1) NOT NULL DEFAULT 0,
    last_activity_at  DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_telegram_id (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- bot_states  (finite-state-machine storage for multi-step conversations,
-- e.g. "awaiting receipt photo", "awaiting broadcast text")
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bot_states (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id  BIGINT NOT NULL,
    state        VARCHAR(100) NOT NULL,
    payload      JSON NULL,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_bot_states_telegram_id (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- categories  (self-referencing tree: e.g. موسیقی ایرانی -> پاپ -> ...)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id   BIGINT UNSIGNED NULL,
    title       VARCHAR(255) NOT NULL,
    icon        VARCHAR(32) NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE,
    KEY idx_categories_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- media_files
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS media_files (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id       BIGINT UNSIGNED NULL,
    title             VARCHAR(255) NOT NULL,
    artist            VARCHAR(255) NULL,
    file_id           VARCHAR(255) NOT NULL COMMENT 'Telegram file_id, used for instant resend without re-upload',
    file_unique_id    VARCHAR(255) NULL,
    source_url        VARCHAR(1000) NULL COMMENT 'Optional: URL on your download host',
    duration          INT NULL COMMENT 'seconds',
    cover_file_id     VARCHAR(255) NULL,
    is_premium        TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'requires active subscription',
    downloads_count   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    uploaded_by       BIGINT UNSIGNED NULL COMMENT 'admin user.id who uploaded it',
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_media_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_media_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
    KEY idx_media_category (category_id),
    KEY idx_media_downloads (downloads_count),
    FULLTEXT KEY ft_media_search (title, artist)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- media_logs  (per-user play/download history, also used for
-- "نزدیک به تو" recommendations and duplicate-play prevention)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS media_logs (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id        BIGINT UNSIGNED NOT NULL,
    media_file_id  BIGINT UNSIGNED NOT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_logs_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE,
    KEY idx_logs_user (user_id),
    KEY idx_logs_media (media_file_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- plans  (subscription plans, e.g. 1 month / 3 months / 1 year)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title          VARCHAR(255) NOT NULL,
    description    VARCHAR(1000) NULL,
    price          DECIMAL(12,2) NOT NULL DEFAULT 0,
    currency       VARCHAR(10) NOT NULL DEFAULT 'IRT',
    duration_days  INT NOT NULL,
    is_active      TINYINT(1) NOT NULL DEFAULT 1,
    sort_order     INT NOT NULL DEFAULT 0,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- subscriptions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    plan_id     BIGINT UNSIGNED NOT NULL,
    starts_at   DATETIME NOT NULL,
    ends_at     DATETIME NOT NULL,
    status      ENUM('pending','active','expired','cancelled') NOT NULL DEFAULT 'pending',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_subs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subs_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
    KEY idx_subs_user_status (user_id, status),
    KEY idx_subs_ends_at (ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- payments  (manual receipt-review flow: user sends a payment screenshot,
-- admin approves/rejects from the admin panel)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id           BIGINT UNSIGNED NOT NULL,
    plan_id           BIGINT UNSIGNED NOT NULL,
    subscription_id   BIGINT UNSIGNED NULL,
    amount            DECIMAL(12,2) NOT NULL,
    receipt_file_id   VARCHAR(255) NULL,
    status            ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by       BIGINT UNSIGNED NULL COMMENT 'admin telegram_id',
    reviewed_at       DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_payments_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payments_sub FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
    KEY idx_payments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- mandatory_channels  (join-to-use gate)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mandatory_channels (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id        BIGINT NULL COMMENT 'numeric chat id, e.g. -1001234567890',
    channel_username  VARCHAR(255) NULL COMMENT '@channel_username, used for getChatMember + invite link display',
    title             VARCHAR(255) NOT NULL,
    invite_link       VARCHAR(500) NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    sort_order        INT NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- playlists / playlist_items
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS playlists (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(255) NOT NULL,
    description  VARCHAR(1000) NULL,
    created_by   BIGINT UNSIGNED NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_playlists_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS playlist_items (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    playlist_id    BIGINT UNSIGNED NOT NULL,
    media_file_id  BIGINT UNSIGNED NOT NULL,
    sort_order     INT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_playlist_media (playlist_id, media_file_id),
    CONSTRAINT fk_items_playlist FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
    CONSTRAINT fk_items_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

