-- Konkurs MiniApp - MySQL Schema
-- Charset: utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

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

-- ─────────────── Settings (admin panel) ───────────────
CREATE TABLE settings (
    `key` VARCHAR(64) PRIMARY KEY,
    `value` TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (`key`, `value`) VALUES
('channel_id', ''),
('channel_username', ''),
('channel_title', ''),
('channel_photo_url', ''),
('store_instagram', ''),
('bot_token', ''),
('api_key', '123'),
('user_resolve_base', 'https://api.virtualstore.uz/instagram-scraper'),
('media_api_base', 'https://api.brauzer.uz/instagram'),
('default_lang', 'uz'),
('points_follow', '10'),
('points_like', '10'),
('points_comment', '20'),
('points_invite', '30'),
('points_reply', '15'),
('min_tags', '3'),
('min_replies', '3'),
('check_cooldown_seconds', '30'),
('queue_max_retries', '5');

-- ─────────────── Users ───────────────
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    telegram_username VARCHAR(64) NULL,
    first_name VARCHAR(128) NULL,
    last_name VARCHAR(128) NULL,
    language_code VARCHAR(8) DEFAULT 'uz',
    is_premium TINYINT(1) DEFAULT 0,
    is_banned TINYINT(1) DEFAULT 0,
    channel_subscribed TINYINT(1) DEFAULT 0,
    channel_checked_at DATETIME NULL,
    instagram_username VARCHAR(64) NULL,
    instagram_id VARCHAR(64) NULL UNIQUE,
    instagram_full_name VARCHAR(255) NULL,
    instagram_photo TEXT NULL,
    is_private TINYINT(1) DEFAULT 0,
    is_verified TINYINT(1) DEFAULT 0,
    follower_count INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ig_username (instagram_username),
    INDEX idx_channel (channel_subscribed)
) ENGINE=InnoDB;

-- ─────────────── Contests / Campaigns ───────────────
CREATE TABLE contests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_uz VARCHAR(255) NOT NULL,
    title_ru VARCHAR(255) NOT NULL,
    description_uz TEXT NULL,
    description_ru TEXT NULL,
    prize_uz VARCHAR(255) NOT NULL,
    prize_ru VARCHAR(255) NOT NULL,
    media_url TEXT NOT NULL,
    media_id VARCHAR(64) NULL,
    media_shortcode VARCHAR(64) NULL,
    contest_type ENUM('ticket','equal') DEFAULT 'ticket',
    status ENUM('draft','active','ended','cancelled') DEFAULT 'draft',
    require_follow TINYINT(1) DEFAULT 1,
    require_like TINYINT(1) DEFAULT 1,
    require_comment TINYINT(1) DEFAULT 1,
    require_invite TINYINT(1) DEFAULT 1,
    require_replies TINYINT(1) DEFAULT 1,
    comment_keyword VARCHAR(128) NULL,
    points_follow INT DEFAULT 10,
    points_like INT DEFAULT 10,
    points_comment INT DEFAULT 20,
    points_invite INT DEFAULT 30,
    points_reply INT DEFAULT 15,
    min_tags INT DEFAULT 3,
    min_replies INT DEFAULT 3,
    min_invites INT DEFAULT 1,
    min_participants INT UNSIGNED DEFAULT 0,
    auto_end_on_participants TINYINT(1) DEFAULT 0,
    start_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    end_at DATETIME NOT NULL,
    winner_user_id BIGINT UNSIGNED NULL,
    winner_announced TINYINT(1) DEFAULT 0,
    draw_seed VARCHAR(64) NULL,
    draw_hash VARCHAR(128) NULL,
    drawn_at DATETIME NULL,
    winners_count INT UNSIGNED DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status_end (status, end_at)
) ENGINE=InnoDB;

-- See also: migration_draw.sql for prizes, draws, winners, draw_public_tokens


-- ─────────────── Participations ───────────────
CREATE TABLE participations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    contest_id INT UNSIGNED NOT NULL,
    followed TINYINT(1) DEFAULT 0,
    liked TINYINT(1) DEFAULT 0,
    commented TINYINT(1) DEFAULT 0,
    comment_text TEXT NULL,
    comment_id VARCHAR(64) NULL,
    tagged_usernames JSON NULL,
    invite_count INT DEFAULT 0,
    reply_count INT DEFAULT 0,
    total_points INT DEFAULT 0,
    is_valid TINYINT(1) DEFAULT 0,
    current_step TINYINT DEFAULT 1,  -- 1=follow,2=like,3=comment,4=invite+replies
    verified_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_contest (user_id, contest_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────── Points log ───────────────
CREATE TABLE points_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    contest_id INT UNSIGNED NOT NULL,
    reason ENUM('follow','like','comment','invite','reply','bonus','admin') NOT NULL,
    points INT NOT NULL,
    meta JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_contest (user_id, contest_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────── Referrals (per contest) ───────────────
CREATE TABLE referrals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contest_id INT UNSIGNED NOT NULL,
    referrer_id BIGINT UNSIGNED NOT NULL,
    referred_id BIGINT UNSIGNED NOT NULL,
    is_valid TINYINT(1) DEFAULT 0,  -- referred completed follow+like+comment
    points_awarded TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contest_referred (contest_id, referred_id),
    INDEX idx_referrer (contest_id, referrer_id),
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────── API Queue (processed by Python worker) ───────────────
CREATE TABLE api_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    contest_id INT UNSIGNED NULL,
    task_type ENUM(
        'resolve_user',
        'check_follow',
        'check_like',
        'check_comment',
        'check_replies',
        'media_info'
    ) NOT NULL,
    payload JSON NOT NULL,          -- input params
    status ENUM('pending','processing','done','failed') DEFAULT 'pending',
    result JSON NULL,
    priority TINYINT DEFAULT 5,     -- 1=highest, 10=lowest
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 5,
    error_message TEXT NULL,
    locked_at DATETIME NULL,
    locked_by VARCHAR(64) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status_priority (status, priority, created_at),
    INDEX idx_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────── Cooldown tracking ───────────────
CREATE TABLE check_cooldowns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    contest_id INT UNSIGNED NOT NULL,
    task_type VARCHAR(32) NOT NULL,
    last_check_at DATETIME NOT NULL,
    UNIQUE KEY uq_cooldown (user_id, contest_id, task_type),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────── Admins ───────────────
CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    name VARCHAR(128) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
-- Prizes (1+ per contest, ordered)
CREATE TABLE IF NOT EXISTS prizes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contest_id INT UNSIGNED NOT NULL,
    place INT UNSIGNED NOT NULL DEFAULT 1,  -- 1st, 2nd, 3rd...
    title_uz VARCHAR(255) NOT NULL,
    title_ru VARCHAR(255) NOT NULL,
    description_uz TEXT NULL,
    description_ru TEXT NULL,
    image_url TEXT NULL,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contest_place (contest_id, place),
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Draw runs (supports re-draw history, transparency)
CREATE TABLE IF NOT EXISTS draws (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contest_id INT UNSIGNED NOT NULL,
    seed VARCHAR(64) NOT NULL,           -- public random seed
    algorithm VARCHAR(32) DEFAULT 'weighted_points_v1',
    server_secret_hash VARCHAR(128) NOT NULL,  -- sha256(seed + server_nonce) published after
    server_nonce VARCHAR(64) NULL,       -- revealed after draw for verification
    total_participants INT UNSIGNED DEFAULT 0,
    total_points INT UNSIGNED DEFAULT 0,
    pool_snapshot JSON NULL,             -- [{user_id, points, instagram, tickets}]
    status ENUM('pending','live','completed','cancelled') DEFAULT 'pending',
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_by BIGINT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE,
    INDEX idx_contest (contest_id)
) ENGINE=InnoDB;

-- Winners linked to draw + prize
CREATE TABLE IF NOT EXISTS winners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    draw_id INT UNSIGNED NOT NULL,
    contest_id INT UNSIGNED NOT NULL,
    prize_id INT UNSIGNED NULL,
    place INT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    points_at_draw INT UNSIGNED DEFAULT 0,
    ticket_index INT UNSIGNED NULL,      -- which ticket slot was picked (transparency)
    pool_size INT UNSIGNED NULL,
    announced TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_draw_place (draw_id, place),
    FOREIGN KEY (draw_id) REFERENCES draws(id) ON DELETE CASCADE,
    FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE,
    FOREIGN KEY (prize_id) REFERENCES prizes(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Live draw public token (read-only view for stream)
CREATE TABLE IF NOT EXISTS draw_public_tokens (
    token VARCHAR(64) PRIMARY KEY,
    draw_id INT UNSIGNED NOT NULL,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (draw_id) REFERENCES draws(id) ON DELETE CASCADE
) ENGINE=InnoDB;