-- =============================================================================
-- Twosome – database/migration_couple_games.sql
-- Schema for couple-focused async games:
--   two_truths_one_lie, mind_meld, this_or_that_predict,
--   finish_the_line, rank_it, dare_auction, memory_lane_quiz
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. THIS OR THAT PAIRS (200+ original choices) ──
CREATE TABLE IF NOT EXISTS this_or_that_pairs (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    option_a   VARCHAR(128) NOT NULL,
    option_b   VARCHAR(128) NOT NULL,
    category   VARCHAR(64)  NOT NULL DEFAULT 'lifestyle',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. FINISH THE LINE PROMPTS (sentence starters) ──
CREATE TABLE IF NOT EXISTS finish_the_line_prompts (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prompt     VARCHAR(255) NOT NULL,
    category   VARCHAR(64)  NOT NULL DEFAULT 'couples',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. RANK IT CATEGORIES (5 items to rank) ──
CREATE TABLE IF NOT EXISTS rank_it_categories (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(128) NOT NULL,
    items      JSON         NOT NULL, -- array of 5 strings
    category   VARCHAR(64)  NOT NULL DEFAULT 'couples',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. MIND MELD PROMPTS (prompts for one-word cooperative sync) ──
CREATE TABLE IF NOT EXISTS mind_meld_prompts (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prompt     VARCHAR(255) NOT NULL,
    category   VARCHAR(64)  NOT NULL DEFAULT 'couples',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. DARE AUCTION ITEMS ──
CREATE TABLE IF NOT EXISTS dare_auction_items (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dare       VARCHAR(255) NOT NULL,
    mode       ENUM('together','apart','both') NOT NULL DEFAULT 'both',
    is_spicy   TINYINT(1)   NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 6. MEMORY LANE VAULT (Saved encrypted quizzes for replay) ──
CREATE TABLE IF NOT EXISTS memory_lane_vault (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id    BIGINT UNSIGNED NOT NULL,
    match_id     BIGINT UNSIGNED DEFAULT NULL,
    title        VARCHAR(128)    NOT NULL DEFAULT 'Our Memories',
    payload_enc  MEDIUMTEXT      NOT NULL, -- ChaCha20-Poly1305 encrypted JSON
    nonce        VARCHAR(64)     NOT NULL,
    created_by   BIGINT UNSIGNED NOT NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_mlv_couple (couple_id),
    CONSTRAINT fk_mlv_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE,
    CONSTRAINT fk_mlv_user   FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;