monquigrid/database/schema.sql

-- Grid Battle Game Database Schema
-- Comprehensive schema supporting gameplay, replays, tournaments, and leaderboards

-- =============================================
-- CORE GAME TABLES
-- =============================================

-- Players (persistent across multiple games)
CREATE TABLE IF NOT EXISTS players (
    id TEXT PRIMARY KEY,                    -- UUID
    username TEXT UNIQUE NOT NULL,          -- Display name
    email TEXT UNIQUE,                      -- Optional for tournaments
    password_hash TEXT,                     -- Optional for persistent accounts
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Statistics for leaderboard
    games_played INTEGER DEFAULT 0,
    games_won INTEGER DEFAULT 0,
    total_kills INTEGER DEFAULT 0,
    total_shots_fired INTEGER DEFAULT 0,
    total_moves_made INTEGER DEFAULT 0,
    avg_game_duration REAL DEFAULT 0,
    skill_rating INTEGER DEFAULT 1000       -- ELO-style rating
);

-- Game sessions
CREATE TABLE IF NOT EXISTS games (
    id TEXT PRIMARY KEY,                    -- UUID for invite links
    status TEXT DEFAULT 'waiting',         -- waiting, playing, finished, abandoned
    game_mode TEXT DEFAULT 'standard',     -- standard, tournament, practice
    tournament_id TEXT,                     -- NULL for regular games
    
    -- Game state
    current_turn INTEGER DEFAULT 1,
    winner_id TEXT,
    loser_id TEXT,
    end_reason TEXT,                        -- shot, timeout, disconnect, forfeit
    
    -- Configuration (for replay purposes)
    grid_width INTEGER DEFAULT 20,
    grid_height INTEGER DEFAULT 20,
    moves_per_turn INTEGER DEFAULT 4,
    shots_per_turn INTEGER DEFAULT 1,
    turn_timeout_seconds INTEGER DEFAULT 30,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME,                    -- When both players joined
    finished_at DATETIME,
    last_action_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (winner_id) REFERENCES players(id),
    FOREIGN KEY (loser_id) REFERENCES players(id),
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id)
);

-- Player participation in games
CREATE TABLE IF NOT EXISTS game_players (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id TEXT NOT NULL,
    player_id TEXT NOT NULL,
    player_number INTEGER NOT NULL,        -- 1 or 2
    
    -- Initial spawn position
    spawn_x INTEGER NOT NULL,
    spawn_y INTEGER NOT NULL,
    
    -- Current position (updated each turn)
    current_x INTEGER NOT NULL,
    current_y INTEGER NOT NULL,
    
    -- Game stats
    is_alive BOOLEAN DEFAULT 1,
    shots_fired INTEGER DEFAULT 0,
    moves_made INTEGER DEFAULT 0,
    turns_survived INTEGER DEFAULT 0,
    
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (game_id) REFERENCES games(id),
    FOREIGN KEY (player_id) REFERENCES players(id),
    UNIQUE(game_id, player_number)
);

-- =============================================
-- TURN MANAGEMENT
-- =============================================

-- Turn submissions (for turn-based gameplay)
CREATE TABLE IF NOT EXISTS turn_submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id TEXT NOT NULL,
    player_id TEXT NOT NULL,
    turn_number INTEGER NOT NULL,
    
    -- Action data
    actions TEXT NOT NULL,                  -- JSON array of moves/shots
    moves_used INTEGER DEFAULT 0,
    shot_used BOOLEAN DEFAULT 0,
    
    -- Timing
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    execution_order INTEGER,               -- Order of execution when both submitted
    
    FOREIGN KEY (game_id) REFERENCES games(id),
    FOREIGN KEY (player_id) REFERENCES players(id),
    UNIQUE(game_id, player_id, turn_number)
);

-- Complete game event log (for replays and analysis)
CREATE TABLE IF NOT EXISTS game_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id TEXT NOT NULL,
    turn_number INTEGER NOT NULL,
    sequence_number INTEGER NOT NULL,      -- Order within turn
    
    -- Event details
    event_type TEXT NOT NULL,              -- move, shoot, hit, miss, win, spawn, timeout
    player_id TEXT,
    
    -- Position data
    from_x INTEGER,
    from_y INTEGER,
    to_x INTEGER,
    to_y INTEGER,
    
    -- Additional data as JSON
    event_data TEXT,                       -- Flexible JSON for extra event info
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (game_id) REFERENCES games(id),
    FOREIGN KEY (player_id) REFERENCES players(id)
);

-- =============================================
-- TOURNAMENT SYSTEM
-- =============================================

-- Tournament definitions
CREATE TABLE IF NOT EXISTS tournaments (
    id TEXT PRIMARY KEY,                   -- UUID
    name TEXT NOT NULL,
    description TEXT,
    tournament_type TEXT DEFAULT 'single_elimination', -- single_elimination, round_robin, swiss
    
    -- Settings
    max_participants INTEGER,
    entry_fee REAL DEFAULT 0,
    prize_pool REAL DEFAULT 0,
    
    -- Status
    status TEXT DEFAULT 'registration',   -- registration, active, completed, cancelled
    
    -- Configuration
    game_config TEXT,                     -- JSON with game settings
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    registration_ends_at DATETIME,
    starts_at DATETIME,
    ends_at DATETIME,
    
    -- Creator
    organizer_id TEXT,
    FOREIGN KEY (organizer_id) REFERENCES players(id)
);

-- Tournament participants
CREATE TABLE IF NOT EXISTS tournament_participants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tournament_id TEXT NOT NULL,
    player_id TEXT NOT NULL,
    
    -- Registration
    registered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    seed_number INTEGER,                   -- Seeding for brackets
    
    -- Performance
    wins INTEGER DEFAULT 0,
    losses INTEGER DEFAULT 0,
    points INTEGER DEFAULT 0,             -- For point-based tournaments
    
    -- Final placement
    final_rank INTEGER,
    prize_amount REAL DEFAULT 0,
    
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
    FOREIGN KEY (player_id) REFERENCES players(id),
    UNIQUE(tournament_id, player_id)
);

-- Tournament brackets/rounds
CREATE TABLE IF NOT EXISTS tournament_rounds (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tournament_id TEXT NOT NULL,
    round_number INTEGER NOT NULL,
    round_name TEXT,                       -- "Semifinals", "Finals", etc.
    
    status TEXT DEFAULT 'pending',        -- pending, active, completed
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME,
    completed_at DATETIME,
    
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id)
);

-- Tournament matches
CREATE TABLE IF NOT EXISTS tournament_matches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tournament_id TEXT NOT NULL,
    round_id INTEGER NOT NULL,
    game_id TEXT,                         -- Links to actual game
    
    -- Participants
    player1_id TEXT,
    player2_id TEXT,
    winner_id TEXT,
    
    -- Match info
    match_number INTEGER,                 -- Position in bracket
    status TEXT DEFAULT 'pending',       -- pending, active, completed, bye
    
    scheduled_at DATETIME,
    completed_at DATETIME,
    
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
    FOREIGN KEY (round_id) REFERENCES tournament_rounds(id),
    FOREIGN KEY (game_id) REFERENCES games(id),
    FOREIGN KEY (player1_id) REFERENCES players(id),
    FOREIGN KEY (player2_id) REFERENCES players(id),
    FOREIGN KEY (winner_id) REFERENCES players(id)
);

-- =============================================
-- ANALYTICS AND INSIGHTS
-- =============================================

-- Player session tracking
CREATE TABLE IF NOT EXISTS player_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    player_id TEXT NOT NULL,
    session_token TEXT UNIQUE,
    ip_address TEXT,
    user_agent TEXT,
    
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME,
    
    FOREIGN KEY (player_id) REFERENCES players(id)
);

-- Game analytics (for balance and improvement)
CREATE TABLE IF NOT EXISTS game_analytics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    game_id TEXT NOT NULL,
    
    -- Performance metrics
    total_turns INTEGER,
    game_duration_seconds INTEGER,
    winner_moves_made INTEGER,
    winner_shots_fired INTEGER,
    loser_moves_made INTEGER,
    loser_shots_fired INTEGER,
    
    -- Map analysis
    spawn_distance INTEGER,              -- Distance between starting positions
    final_distance INTEGER,              -- Distance at game end
    
    -- Timing
    avg_turn_duration REAL,
    longest_turn_duration REAL,
    
    analyzed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (game_id) REFERENCES games(id)
);

-- =============================================
-- INDEXES FOR PERFORMANCE
-- =============================================

-- Game lookup indexes
CREATE INDEX IF NOT EXISTS idx_games_status ON games(status);
CREATE INDEX IF NOT EXISTS idx_games_created ON games(created_at);
CREATE INDEX IF NOT EXISTS idx_games_tournament ON games(tournament_id);

-- Player performance indexes
CREATE INDEX IF NOT EXISTS idx_players_rating ON players(skill_rating DESC);
CREATE INDEX IF NOT EXISTS idx_players_wins ON players(games_won DESC);
CREATE INDEX IF NOT EXISTS idx_players_username ON players(username);

-- Game events for replay
CREATE INDEX IF NOT EXISTS idx_events_game_turn ON game_events(game_id, turn_number, sequence_number);

-- Turn submissions
CREATE INDEX IF NOT EXISTS idx_submissions_game_turn ON turn_submissions(game_id, turn_number);

-- Tournament lookups
CREATE INDEX IF NOT EXISTS idx_tournament_status ON tournaments(status);
CREATE INDEX IF NOT EXISTS idx_tournament_participants ON tournament_participants(tournament_id);
CREATE INDEX IF NOT EXISTS idx_tournament_matches ON tournament_matches(tournament_id, round_id);

-- =============================================
-- VIEWS FOR COMMON QUERIES
-- =============================================

-- Leaderboard view
CREATE VIEW IF NOT EXISTS leaderboard AS
SELECT 
    p.id,
    p.username,
    p.games_played,
    p.games_won,
    ROUND(CAST(p.games_won AS REAL) / NULLIF(p.games_played, 0) * 100, 2) as win_rate,
    p.total_kills,
    p.skill_rating,
    p.last_active
FROM players p
WHERE p.games_played > 0
ORDER BY p.skill_rating DESC, p.games_won DESC;

-- Active games view
CREATE VIEW IF NOT EXISTS active_games AS
SELECT 
    g.id,
    g.status,
    g.current_turn,
    g.created_at,
    p1.username as player1_name,
    p2.username as player2_name,
    CASE 
        WHEN g.status = 'waiting' THEN 'Waiting for player 2'
        WHEN g.status = 'playing' THEN 'Turn ' || g.current_turn
        ELSE g.status
    END as display_status
FROM games g
LEFT JOIN game_players gp1 ON g.id = gp1.game_id AND gp1.player_number = 1
LEFT JOIN game_players gp2 ON g.id = gp2.game_id AND gp2.player_number = 2
LEFT JOIN players p1 ON gp1.player_id = p1.id
LEFT JOIN players p2 ON gp2.player_id = p2.id
WHERE g.status IN ('waiting', 'playing')
ORDER BY g.last_action_at DESC;