-- Migration: multi-prize, transparent weighted draw, auto-end rules
USE konkurs_bot;

-- Contest extra fields
ALTER TABLE contests
  ADD COLUMN min_participants INT UNSIGNED DEFAULT 0 AFTER min_invites,
  ADD COLUMN auto_end_on_participants TINYINT(1) DEFAULT 0 AFTER min_participants,
  ADD COLUMN draw_seed VARCHAR(64) NULL AFTER winner_announced,
  ADD COLUMN draw_hash VARCHAR(128) NULL AFTER draw_seed,
  ADD COLUMN drawn_at DATETIME NULL AFTER draw_hash,
  ADD COLUMN winners_count INT UNSIGNED DEFAULT 1 AFTER drawn_at;

-- 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;