-- =============================================================================
-- Twosome – database/migration_shared_cheeky_cards.sql
-- Unified Shared Card Table Schema for Cheeky & Companion Games:
-- (game_type, text, level 1-3, mode apart/together/both, tags, is_spicy)
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS cheeky_game_cards (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    game_type  VARCHAR(64) NOT NULL,
    text       TEXT NOT NULL,
    level      TINYINT UNSIGNED NOT NULL DEFAULT 1,
    mode       ENUM('apart', 'together', 'both') NOT NULL DEFAULT 'apart',
    tags       VARCHAR(255) DEFAULT NULL,
    is_spicy   TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_game_mode_level (game_type, mode, level),
    INDEX idx_game_type (game_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;