-- =============================================================================
-- Twosome – database/migration_flirt_wars.sql
-- Schema for Flirt Wars: Two-Player Flirty Card Battler
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS flirt_wars_cards (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code               VARCHAR(64) NOT NULL UNIQUE,
    name               VARCHAR(128) NOT NULL,
    type               ENUM('attack', 'counter', 'wild') NOT NULL,
    deck_theme         ENUM('smooth_talker', 'sweet_savage', 'chaos_agent', 'neutral') NOT NULL DEFAULT 'neutral',
    blush_val          INT NOT NULL DEFAULT 0,
    energy_cost        INT NOT NULL DEFAULT 1,
    flavor_text        VARCHAR(255) NOT NULL,
    art_icon           VARCHAR(32) NOT NULL,
    effect_description TEXT NOT NULL,
    mode               ENUM('apart', 'together', 'both') NOT NULL DEFAULT 'both',
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_theme (type, deck_theme),
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;