
-- 1. Admins (for host / control login)
CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(191) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin','host') DEFAULT 'host',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Game sessions (one per show)
CREATE TABLE game_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_code VARCHAR(32) NOT NULL UNIQUE,   -- used in player join link
    title VARCHAR(150) NOT NULL,
    status ENUM('draft','waiting_for_players','in_progress','completed','cancelled')
        DEFAULT 'draft',
    video_path VARCHAR(255) DEFAULT NULL,       -- single 1-min video
    total_rounds TINYINT UNSIGNED DEFAULT 13,
    created_by_admin_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by_admin_id) REFERENCES admins(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Session global state (for "real-time" sync)
CREATE TABLE session_state (
    session_id INT UNSIGNED PRIMARY KEY,
    current_round_id INT UNSIGNED DEFAULT NULL,
    current_phase ENUM(
        'waiting_for_players',
        'video_playing',
        'question_displayed',
        'waiting_for_answers',
        'reveal_answers',
        'round_summary',
        'card_selection',
        'card_reveal',
        'intermission',
        'finished'
    ) DEFAULT 'waiting_for_players',
    current_question_id INT UNSIGNED DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Rounds (13 per session)
CREATE TABLE session_rounds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    round_number TINYINT UNSIGNED NOT NULL,  -- 1..13
    is_elimination_round TINYINT(1) DEFAULT 0,
    prize_amount BIGINT UNSIGNED DEFAULT 1000000, -- normal rounds 1m, bumpers override
    status ENUM('pending','active','completed') DEFAULT 'pending',
    winner_player_id INT UNSIGNED DEFAULT NULL,
    started_at DATETIME DEFAULT NULL,
    ended_at DATETIME DEFAULT NULL,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_round_session ON session_rounds(session_id, round_number);

-- 5. Players per session
CREATE TABLE session_players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    slot_number TINYINT UNSIGNED NOT NULL,   -- 1..8
    display_name VARCHAR(100) NOT NULL,
    avatar_path VARCHAR(255) DEFAULT NULL,
    status ENUM('registered','active','eliminated','disconnected')
        DEFAULT 'registered',
    rewinds_left TINYINT UNSIGNED DEFAULT 3,
    total_rating INT DEFAULT 0,              -- across all rounds (for leaderboard)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_session_slot (session_id, slot_number),
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_players_session ON session_players(session_id);

-- 6. Questions
CREATE TABLE questions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    question_number TINYINT UNSIGNED NOT NULL,  -- order within round
    question_text TEXT NOT NULL,
    correct_option ENUM('A','B','C','D') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (round_id) REFERENCES session_rounds(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_questions_round ON questions(round_id);

-- 7. Question options
CREATE TABLE question_options (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_id INT UNSIGNED NOT NULL,
    option_key ENUM('A','B','C','D') NOT NULL,
    option_text VARCHAR(255) NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE UNIQUE INDEX uniq_question_option ON question_options(question_id, option_key);

-- 8. Player answers
CREATE TABLE player_answers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    question_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    selected_option ENUM('A','B','C','D') NOT NULL,
    is_correct TINYINT(1) DEFAULT 0,
    response_time_ms INT UNSIGNED NOT NULL,   -- client ms difference
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (round_id) REFERENCES session_rounds(id)
        ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES session_players(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_pa_round_question ON player_answers(round_id, question_id);
CREATE INDEX idx_pa_player ON player_answers(player_id);

-- 9. Per-round player stats (points & rating)
CREATE TABLE player_round_stats (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    points TINYINT UNSIGNED DEFAULT 0,      -- 0..3+ for the round
    rating_total INT DEFAULT 0,             -- rating accumulated in that round
    is_round_winner TINYINT(1) DEFAULT 0,
    is_eliminated_this_round TINYINT(1) DEFAULT 0,
    UNIQUE KEY uniq_round_player (round_id, player_id),
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (round_id) REFERENCES session_rounds(id)
        ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES session_players(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. Player wallets (money + streak)
CREATE TABLE player_wallets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    active_amount BIGINT DEFAULT 0,
    frozen_amount BIGINT DEFAULT 0,
    streak_wins TINYINT UNSIGNED DEFAULT 0,     -- consecutive round wins
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_wallet (session_id, player_id),
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES session_players(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. Destiny cards (master catalog)
CREATE TABLE destiny_cards (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    card_code VARCHAR(50) NOT NULL UNIQUE,   -- e.g. HEADSTART_1
    name VARCHAR(100) NOT NULL,
    category ENUM('privilege','responsibility','flow') NOT NULL,
    description TEXT NOT NULL,
    effect_key VARCHAR(50) NOT NULL,         -- headstart, rollover, insurance, etc.
    effect_payload TEXT DEFAULT NULL,        -- JSON/text parameters if needed
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. Card draws (per round per player)
CREATE TABLE card_draws (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    round_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    card_id INT UNSIGNED NOT NULL,
    draw_order TINYINT UNSIGNED NOT NULL,    -- order of selection/reveal
    applied_at DATETIME DEFAULT NULL,
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (round_id) REFERENCES session_rounds(id)
        ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES session_players(id)
        ON DELETE CASCADE,
    FOREIGN KEY (card_id) REFERENCES destiny_cards(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_carddraw_round ON card_draws(round_id);
CREATE INDEX idx_carddraw_player ON card_draws(player_id);

-- 13. Player active cards (privileges currently in effect)
CREATE TABLE player_active_cards (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    card_id INT UNSIGNED NOT NULL,
    status ENUM('active','cancelled','expired') DEFAULT 'active',
    activated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deactivated_at DATETIME DEFAULT NULL,
    UNIQUE KEY uniq_active_card (session_id, player_id, card_id, status),
    FOREIGN KEY (session_id) REFERENCES game_sessions(id)
        ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES session_players(id)
        ON DELETE CASCADE,
    FOREIGN KEY (card_id) REFERENCES destiny_cards(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 14. Optional: simple logs for debugging/production
CREATE TABLE event_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id INT UNSIGNED DEFAULT NULL,
    player_id INT UNSIGNED DEFAULT NULL,
    level ENUM('info','warning','error') DEFAULT 'info',
    event_type VARCHAR(100) NOT NULL,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_session (session_id),
    INDEX idx_logs_player (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
