-- =============================================================================
-- Twosome – database/migration_adventurous_cheeky.sql
-- Schema for 13 Adventurous Cheeky Games:
--   slow_sway, hip_sway, midnight_dance_date, naughty_truth_or_dare,
--   naughty_would_you_rather, naughty_never_have_i_ever, fantasy_match,
--   flirt_battle, kiss_challenge, compliment_battle, tease_me_three_words,
--   midnight_menu, outfit_rating
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. NAUGHTY TRUTH OR DARE CARDS ──
CREATE TABLE IF NOT EXISTS naughty_truth_or_dare_cards (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type          ENUM('truth','dare') NOT NULL,
    prompt        TEXT NOT NULL,
    level         TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1=flirty, 2=spicy, 3=bold
    mode          ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    delivery_type ENUM('text','voice_note','dance','photo','action') NOT NULL DEFAULT 'text',
    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. NAUGHTY WOULD YOU RATHER PAIRS ──
CREATE TABLE IF NOT EXISTS naughty_would_you_rather_pairs (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    option_a   VARCHAR(255) NOT NULL,
    option_b   VARCHAR(255) NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    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;

-- ── 3. NAUGHTY NEVER HAVE I EVER STATEMENTS (150+ cards) ──
CREATE TABLE IF NOT EXISTS naughty_never_have_i_ever_statements (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    statement  VARCHAR(255) NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    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. FANTASY MATCH CARDS (150+ mutual consent cards) ──
CREATE TABLE IF NOT EXISTS fantasy_match_cards (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(128) NOT NULL,
    scenario   TEXT NOT NULL,
    category   VARCHAR(64) NOT NULL DEFAULT 'romantic',
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    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;

-- ── 5. FLIRT BATTLE SCENARIOS ──
CREATE TABLE IF NOT EXISTS flirt_battle_scenarios (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scenario   TEXT NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    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;

-- ── 6. MIDNIGHT MENU ITEMS ──
CREATE TABLE IF NOT EXISTS midnight_menu_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course      ENUM('starter','main','dessert') NOT NULL,
    title       VARCHAR(128) NOT NULL,
    description VARCHAR(255) NOT NULL,
    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;

-- ── 7. SAVED DATE MEMORIES (Midnight Dance Date) ──
CREATE TABLE IF NOT EXISTS saved_date_memories (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id    BIGINT UNSIGNED NOT NULL,
    match_id     BIGINT UNSIGNED DEFAULT NULL,
    mood         VARCHAR(64) NOT NULL,
    loop_name    VARCHAR(64) NOT NULL,
    rating_avg   FLOAT NOT NULL DEFAULT 5.0,
    memory_note  TEXT DEFAULT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date_mem_couple (couple_id),
    CONSTRAINT fk_date_mem_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 8. SAVED WHEN WE MEET ITEMS (Midnight Menu list) ──
CREATE TABLE IF NOT EXISTS saved_when_we_meet_items (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id  BIGINT UNSIGNED NOT NULL,
    match_id   BIGINT UNSIGNED DEFAULT NULL,
    starter    VARCHAR(128) NOT NULL,
    main       VARCHAR(128) NOT NULL,
    dessert    VARCHAR(128) NOT NULL,
    status     ENUM('planned','fulfilled') NOT NULL DEFAULT 'planned',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_when_we_meet_couple (couple_id),
    CONSTRAINT fk_when_we_meet_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;