-- ============================================================================= -- Twosome – database/migration_match_engine.sql -- Unified Match Engine Schema: matches, match_moves, scores, stakes -- ============================================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- 1. MATCHES CREATE TABLE IF NOT EXISTS matches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, game_type VARCHAR(64) NOT NULL, -- 'tic_tac_toe', 'rock_paper_scissors', etc. mode ENUM('turn', 'simultaneous', 'realtime') NOT NULL, play_mode ENUM('live', 'async', 'challenge') NOT NULL, relationship_mode ENUM('together', 'apart') NOT NULL DEFAULT 'apart', state_json MEDIUMTEXT NOT NULL, version INT UNSIGNED NOT NULL DEFAULT 1, status ENUM('waiting', 'active', 'finished', 'cancelled', 'expired') NOT NULL DEFAULT 'waiting', winner_id BIGINT UNSIGNED DEFAULT NULL, stake_json TEXT DEFAULT NULL, seed BIGINT UNSIGNED NOT NULL, start_at DATETIME DEFAULT NULL, deadline_at DATETIME DEFAULT NULL, last_reminder_at DATETIME DEFAULT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, finished_at DATETIME DEFAULT NULL, INDEX idx_matches_couple_status (couple_id, status), INDEX idx_matches_deadline (status, deadline_at), INDEX idx_matches_created_by (created_by), CONSTRAINT fk_matches_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_matches_winner FOREIGN KEY (winner_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_matches_creator FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. MATCH MOVES (Idempotent by client_move_id) CREATE TABLE IF NOT EXISTS match_moves ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, match_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, client_move_id VARCHAR(64) NOT NULL, move_json TEXT NOT NULL, seq INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_mm_client_move (client_move_id), UNIQUE KEY uq_mm_match_seq (match_id, seq), INDEX idx_mm_match_user (match_id, user_id), CONSTRAINT fk_mm_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE, CONSTRAINT fk_mm_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 3. SCORES (Scoreboard and streaks per couple and game type) CREATE TABLE IF NOT EXISTS scores ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, game_type VARCHAR(64) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, wins INT UNSIGNED NOT NULL DEFAULT 0, losses INT UNSIGNED NOT NULL DEFAULT 0, draws INT UNSIGNED NOT NULL DEFAULT 0, streak INT NOT NULL DEFAULT 0, -- positive = win streak, negative = loss streak best_streak INT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_scores_user (couple_id, game_type, user_id), INDEX idx_scores_couple (couple_id), CONSTRAINT fk_scores_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_scores_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 4. STAKES (Playful stakes and penalties tagged with relationship_mode) CREATE TABLE IF NOT EXISTS stakes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(128) NOT NULL, description TEXT NOT NULL, mode ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart', is_spicy TINYINT(1) NOT NULL DEFAULT 0, level TINYINT NOT NULL DEFAULT 1, INDEX idx_stakes_mode (mode, is_spicy) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 4B. DARES (Playful dares tagged with relationship_mode) CREATE TABLE IF NOT EXISTS dares ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(128) NOT NULL, text TEXT NOT NULL, mode ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart', is_spicy TINYINT(1) NOT NULL DEFAULT 0, level TINYINT NOT NULL DEFAULT 1, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_dares_mode (mode, is_spicy) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 5. Add mode ENUM('together','apart','both') to cards if not present -- Note: MySQL 5.7+ safe column addition SET @col_exists = ( SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cards' AND COLUMN_NAME = 'mode' ); SET @sql = IF(@col_exists = 0, 'ALTER TABLE cards ADD COLUMN mode ENUM(\'together\', \'apart\', \'both\') NOT NULL DEFAULT \'apart\' AFTER level', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- Seed default stakes for couples INSERT IGNORE INTO stakes (id, title, description, mode, is_spicy, level) VALUES (1, 'Virtual Coffee Date', 'The loser treats the winner to their favorite coffee or dessert delivery.', 'apart', 0, 1), (2, 'Voice Note Serenade', 'The loser must send a 30-second romantic song serenade voice note.', 'both', 0, 1), (3, 'Love Letter Paragraph', 'The loser writes a heartfelt 50-word letter on what they love most about their partner.', 'both', 0, 1), (4, 'Breakfast in Bed', 'The loser cooks or serves the winner breakfast in bed next morning.', 'together', 0, 1), (5, '10-Minute Shoulder Massage', 'The loser gives the winner a relaxing, uninterrupted shoulder rub.', 'together', 0, 1), (6, 'Choice of Movie Night', 'The winner picks the movie or show with zero complaints allowed.', 'both', 0, 1), (7, 'Flirty Video Clip Dare', 'The loser records a 15-second clothed flirty dance clip and sends it privately.', 'both', 1, 1), (8, 'Sensual Whisper Voice Note', 'The loser whispers their most romantic secret fantasy into a private voice note.', 'both', 1, 2), (9, 'Slow Dance Kiss', 'The loser must initiate a 2-minute slow dance with a tender kiss at the end.', 'together', 1, 1), (10, 'Foot & Ankle Massage', 'The loser treats the winner to a soothing foot rub with lotion.', 'together', 1, 2); SET FOREIGN_KEY_CHECKS = 1;