-- =============================================================================
-- Twosome – database/migration_new_games.sql
-- Schema additions for new match-engine games:
--   quick_math, word_duel, pattern_echo, trivia_faceoff, world_quiz,
--   hangman_duel, emoji_charades, draw_and_guess
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. TRIVIA QUESTIONS (300+ original MCQ, tagged by category + difficulty) ──
CREATE TABLE IF NOT EXISTS trivia_questions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category    ENUM('movies','food','science','india','general','couples') NOT NULL,
    difficulty  ENUM('easy','medium','hard') NOT NULL,
    question    TEXT NOT NULL,
    option_a    TEXT NOT NULL,
    option_b    TEXT NOT NULL,
    option_c    TEXT NOT NULL,
    option_d    TEXT NOT NULL,
    answer_idx  TINYINT UNSIGNED NOT NULL, -- 0-3
    explanation TEXT DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_trivia_category_difficulty (category, difficulty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. WORLD COUNTRIES (capitals + flag emoji from CC0 dataset) ──
CREATE TABLE IF NOT EXISTS world_countries (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_common  VARCHAR(128) NOT NULL,
    name_official VARCHAR(128) NOT NULL,
    capital      VARCHAR(128) NOT NULL,
    flag_emoji   VARCHAR(8) NOT NULL,
    region       VARCHAR(64) DEFAULT NULL,
    subregion    VARCHAR(64) DEFAULT NULL,
    cca2         CHAR(2) NOT NULL,
    cca3         CHAR(3) NOT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_countries_cca2 (cca2),
    INDEX idx_countries_region (region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. HANGMAN CUSTOM WORDS (couple-added words/phrases) ──
CREATE TABLE IF NOT EXISTS hangman_custom_words (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id   BIGINT UNSIGNED NOT NULL,
    word        VARCHAR(255) NOT NULL,
    category    VARCHAR(64) DEFAULT NULL,
    added_by    BIGINT UNSIGNED NOT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_hangman_couple (couple_id),
    INDEX idx_hangman_active (couple_id, is_active),

    CONSTRAINT fk_hw_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE,
    CONSTRAINT fk_hw_user   FOREIGN KEY (added_by)   REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. EMOJI CHARADES TITLES (original lists + couple custom) ──
CREATE TABLE IF NOT EXISTS emoji_charades_titles (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    emojis      VARCHAR(512) NOT NULL,
    category    VARCHAR(64) NOT NULL DEFAULT 'original',
    source      ENUM('original','couple_custom') NOT NULL DEFAULT 'original',
    couple_id   BIGINT UNSIGNED DEFAULT NULL,
    added_by    BIGINT UNSIGNED DEFAULT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_charades_category (category),
    INDEX idx_charades_active (is_active),

    CONSTRAINT fk_ect_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE,
    CONSTRAINT fk_ect_user   FOREIGN KEY (added_by)   REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. DRAW WORDS (easy/medium/hard lists + couple inside jokes) ──
CREATE TABLE IF NOT EXISTS draw_words (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    word        VARCHAR(128) NOT NULL,
    difficulty  ENUM('easy','medium','hard') NOT NULL,
    source      ENUM('original','couple_custom') NOT NULL DEFAULT 'original',
    couple_id   BIGINT UNSIGNED DEFAULT NULL,
    added_by    BIGINT UNSIGNED DEFAULT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_draw_difficulty (difficulty),
    INDEX idx_draw_active (is_active),

    CONSTRAINT fk_dw_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE,
    CONSTRAINT fk_dw_user   FOREIGN KEY (added_by)   REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 6. WORD DUEL SOURCE WORDS (6-letter scramble seeds) ──
CREATE TABLE IF NOT EXISTS word_duel_source_words (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    word    VARCHAR(32) NOT NULL UNIQUE,
    source  VARCHAR(128) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;