-- Damnation ou Hérésie - Database Schema
-- MySQL 8+

CREATE DATABASE IF NOT EXISTS damnation_heresie CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE damnation_heresie;

-- Table des parties
CREATE TABLE games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) UNIQUE NOT NULL,
    status ENUM('lobby', 'playing', 'finished') DEFAULT 'lobby',
    damnation_threshold INT DEFAULT 15,
    current_player_id INT NULL,
    turn_number INT DEFAULT 0,
    deck JSON,
    discard_pile JSON,
    active_rules JSON,
    reaction_phase BOOLEAN DEFAULT FALSE,
    reaction_target_player_id INT NULL,
    reaction_source_card VARCHAR(50) NULL,
    reaction_expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    host_player_id INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des joueurs
CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    damnation INT DEFAULT 0,
    hand JSON,
    is_alive BOOLEAN DEFAULT TRUE,
    is_host BOOLEAN DEFAULT FALSE,
    turn_order INT,
    is_cursed BOOLEAN DEFAULT FALSE,
    multiplier INT DEFAULT 1,
    has_used_resurrection BOOLEAN DEFAULT FALSE,
    session_token VARCHAR(64) UNIQUE,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table des pactes actifs
CREATE TABLE pacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    player1_id INT NOT NULL,
    player2_id INT NOT NULL,
    turns_remaining INT DEFAULT 3,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    FOREIGN KEY (player1_id) REFERENCES players(id),
    FOREIGN KEY (player2_id) REFERENCES players(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table de l'historique des actions
CREATE TABLE game_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT NOT NULL,
    player_id INT,
    action_type VARCHAR(50),
    card_id VARCHAR(50),
    target_player_id INT NULL,
    details JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Index pour les performances
CREATE INDEX idx_players_game_id ON players(game_id);
CREATE INDEX idx_players_session_token ON players(session_token);
CREATE INDEX idx_pacts_game_id ON pacts(game_id);
CREATE INDEX idx_game_log_game_id ON game_log(game_id);
CREATE INDEX idx_games_code ON games(code);
