CREATE TABLE IF NOT EXISTS `users` (
    `user_id`  BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `state`    VARCHAR(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `anime_cache` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `anime_id`       INT UNSIGNED NOT NULL UNIQUE,
    `title`          VARCHAR(500) NOT NULL,
    `cover_url`      VARCHAR(1000) NOT NULL,
    `score`          DECIMAL(3,1) UNSIGNED DEFAULT NULL,
    `genres`         VARCHAR(500) NOT NULL DEFAULT '',
    `description`    TEXT NOT NULL,
    `next_airing_at` INT UNSIGNED DEFAULT NULL,
    `next_episode`   INT UNSIGNED DEFAULT NULL,
    `is_updated`     TINYINT(1) NOT NULL DEFAULT 0,
    FULLTEXT INDEX `idx_title_ft` (`title`),
    INDEX `idx_next_airing` (`next_airing_at`, `is_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `watchlist` (
    `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id`  BIGINT UNSIGNED NOT NULL,
    `anime_id` INT UNSIGNED NOT NULL,
    `notified` TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY `uk_user_anime` (`user_id`, `anime_id`),
    INDEX `idx_anime_id` (`anime_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
    `user_id`   BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    `first_name` VARCHAR(100) NOT NULL DEFAULT '',
    `username`   VARCHAR(100) NOT NULL DEFAULT '',
    `is_super`   TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
    `key`   VARCHAR(64) NOT NULL PRIMARY KEY,
    `value` VARCHAR(500) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channels_force_join` (
    `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title`    VARCHAR(200) NOT NULL,
    `username` VARCHAR(200) NOT NULL DEFAULT '',
    `link`     VARCHAR(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channels_alert` (
    `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title`    VARCHAR(200) NOT NULL,
    `username` VARCHAR(200) NOT NULL DEFAULT '',
    `link`     VARCHAR(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `broadcast_campaigns` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `content_type`      ENUM('message','forward') NOT NULL,
    `payload`           TEXT NULL,
    `forward_chat_id`   VARCHAR(100) NULL,
    `forward_message_id` VARCHAR(100) NULL,
    `created_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `broadcast_queue` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `campaign_id` INT UNSIGNED NULL DEFAULT NULL,
    `anime_id`    INT UNSIGNED NULL DEFAULT NULL,
    `target_type` ENUM('user','channel') NOT NULL,
    `target_id`   VARCHAR(100) NOT NULL,
    `priority`    INT UNSIGNED NOT NULL DEFAULT 5,
    `status`      ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
    `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status_priority` (`status`, `priority`, `created_at`),
    INDEX `idx_campaign` (`campaign_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;