-- =============================================================================
-- Twosome – database/migration_dares_and_stakes.sql
-- Dares packs, Stakes system, Clips Vault, and Weekly Couple Cup Leaderboard
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. STAKE CARDS (Pre-match stakes categorized as funny, sweet, or spicy)
CREATE TABLE IF NOT EXISTS stake_cards (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(128) NOT NULL,
    description TEXT         NOT NULL,
    category    ENUM('funny', 'sweet', 'spicy') NOT NULL DEFAULT 'sweet',
    mode        ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart',
    is_spicy    TINYINT(1)   NOT NULL DEFAULT 0,
    level       TINYINT      NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_stake_cards_cat (category, mode, is_spicy)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. CUSTOM COUPLE STAKES (Visible only to the creating couple)
CREATE TABLE IF NOT EXISTS custom_couple_stakes (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id   BIGINT UNSIGNED NOT NULL,
    created_by  BIGINT UNSIGNED NOT NULL,
    title       VARCHAR(128)    NOT NULL,
    description TEXT            NOT NULL,
    category    ENUM('funny', 'sweet', 'spicy') NOT NULL DEFAULT 'sweet',
    is_active   TINYINT(1)      NOT NULL DEFAULT 1,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- 3. DARE CARDS (Categorized dare packs: funny, dance, sweet, spicy)
CREATE TABLE IF NOT EXISTS dare_cards (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category      ENUM('funny', 'dance', 'sweet', 'spicy') NOT NULL,
    title         VARCHAR(128) NOT NULL,
    prompt_text   TEXT         NOT NULL,
    mode          ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart',
    media_type    ENUM('text', 'voice', 'drawing', 'photo', 'clip') NOT NULL DEFAULT 'text',
    level         TINYINT      NOT NULL DEFAULT 1,
    is_spicy      TINYINT(1)   NOT NULL DEFAULT 0,
    move_key      VARCHAR(64)  DEFAULT NULL, -- for dance moves e.g. 'shoulder_shimmy'
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_dare_cards_cat_mode (category, mode, is_spicy, level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. CLIPS VAULT (Encrypted short dance clips with auto-delete and view-once tracking)
CREATE TABLE IF NOT EXISTS clips_vault (
    id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id             BIGINT UNSIGNED NOT NULL,
    match_id              BIGINT UNSIGNED DEFAULT NULL,
    sender_id             BIGINT UNSIGNED NOT NULL,
    clip_token            VARCHAR(64)     NOT NULL UNIQUE,
    encrypted_blob_path   VARCHAR(255)    NOT NULL,
    duration_sec          DECIMAL(4,2)    NOT NULL DEFAULT 15.00,
    is_view_once          TINYINT(1)      NOT NULL DEFAULT 0,
    viewed_at             DATETIME        DEFAULT NULL,
    expires_at            DATETIME        NOT NULL,
    is_reported           TINYINT(1)      NOT NULL DEFAULT 0,
    reported_at           DATETIME        DEFAULT NULL,
    created_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_clips_expiry (expires_at, is_view_once, viewed_at),
    INDEX idx_clips_couple (couple_id),
    CONSTRAINT fk_clips_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE,
    CONSTRAINT fk_clips_sender FOREIGN KEY (sender_id) REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. WEEKLY COUPLE CUP LEADERBOARD (Resetting every Monday at 00:00 UTC)
CREATE TABLE IF NOT EXISTS couple_cup_leaderboard (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    couple_id         BIGINT UNSIGNED NOT NULL,
    week_year         VARCHAR(10)     NOT NULL, -- e.g. '2026-W43'
    weekly_points     INT UNSIGNED    NOT NULL DEFAULT 0,
    matches_won       INT UNSIGNED    NOT NULL DEFAULT 0,
    stakes_honored    INT UNSIGNED    NOT NULL DEFAULT 0,
    current_streak    INT UNSIGNED    NOT NULL DEFAULT 0,
    trophy_earned     VARCHAR(64)     DEFAULT NULL,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_couple_week (couple_id, week_year),
    INDEX      idx_cup_ranking (week_year, weekly_points DESC),
    CONSTRAINT fk_cup_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;