CREATE DATABASE IF NOT EXISTS local_game_network;
USE local_game_network;

-- Tracks the overall room and game engine state
CREATE TABLE IF NOT EXISTS games (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(10) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'lobby', -- 'lobby', 'active', 'saved'
    turn_index INT DEFAULT 0,            -- Points to the player_index whose turn it is
    game_data LONGTEXT,                  -- Stores public game state (discard pile, board, etc.)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tracks players and their private, hidden information
CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_id INT,
    player_name VARCHAR(50) NOT NULL,
    player_index INT NOT NULL,           -- 0, 1, 2, 3 etc. (Defines turn order)
    session_token VARCHAR(64) NOT NULL,   -- Keeps players from spoofing turns
    private_data LONGTEXT,               -- Hidden info (e.g., their specific hand of cards)
    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
    UNIQUE KEY unique_player_per_game (game_id, player_index)
);