-- =============================================================================
-- Twosome – database/migration_cheeky_games.sql
-- Schema for playful, suggestive games in the locked Cheeky section:
--   cheeky_race, spin_the_bottle, naughty_dice, heat_check,
--   mind_in_the_gutter, hot_seat, date_builder
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. HEAT CHECK STATEMENTS (Rate 1-5 flames and predict partner's flame) ──
CREATE TABLE IF NOT EXISTS heat_check_statements (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    statement  VARCHAR(255) NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1=sweet/flirty, 2=spicy, 3=bold
    mode       ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    is_spicy   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. MIND IN THE GUTTER RIDDLES (Dirty mind guess wrong, clean answer right) ──
CREATE TABLE IF NOT EXISTS mind_in_the_gutter_riddles (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    riddle       TEXT         NOT NULL,
    clean_answer VARCHAR(128) NOT NULL,
    dirty_trap   VARCHAR(128) NOT NULL,
    distractors  JSON         NOT NULL, -- Array of 2 other innocent distractors
    mode         ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    level        TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_spicy     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. HOT SEAT QUESTIONS (Rated Mild, Spicy, or Fire) ──
CREATE TABLE IF NOT EXISTS hot_seat_questions (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question   VARCHAR(255) NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1=mild, 2=spicy, 3=fire
    mode       ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    is_spicy   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. SPIN THE BOTTLE CARDS (Cute, Flirty, Bold; together & apart versions) ──
CREATE TABLE IF NOT EXISTS spin_the_bottle_cards (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prompt        TEXT         NOT NULL,
    category      ENUM('cute','flirty','bold') NOT NULL DEFAULT 'cute',
    level         TINYINT UNSIGNED NOT NULL DEFAULT 1,
    mode          ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    delivery_type ENUM('action','voice_note','song','drawing','photo','compliment') NOT NULL DEFAULT 'action',
    is_spicy      TINYINT(1)   NOT NULL DEFAULT 1,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. SHARED DATE PLANS (Built cooperatively in Date Builder) ──
CREATE TABLE IF NOT EXISTS shared_date_plans (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id    BIGINT UNSIGNED NOT NULL,
    match_id     BIGINT UNSIGNED DEFAULT NULL,
    is_virtual   TINYINT(1)      NOT NULL DEFAULT 0,
    setting      VARCHAR(128)    NOT NULL,
    food         VARCHAR(128)    NOT NULL,
    music        VARCHAR(128)    NOT NULL,
    activity     VARCHAR(128)    NOT NULL,
    flirty_twist VARCHAR(128)    NOT NULL,
    status       ENUM('locked','completed') NOT NULL DEFAULT 'locked',
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_couple (couple_id),
    CONSTRAINT fk_date_plans_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;