-- ========================================================
-- Digital Sungka Database Schema (PostgreSQL / MariaDB Compatible)
-- Stores users, match histories, matchmaking rooms, and statistics.
-- ========================================================

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player Profiles & Sungka Stats
CREATE TABLE IF NOT EXISTS player_stats (
    user_id VARCHAR(36) PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    games_played INT DEFAULT 0,
    games_won INT DEFAULT 0,
    games_lost INT DEFAULT 0,
    games_drawn INT DEFAULT 0,
    total_shells_captured INT DEFAULT 0,
    highest_store_score INT DEFAULT 0,
    elo_rating INT DEFAULT 1200
);

-- Multiplayer Game Rooms
CREATE TABLE IF NOT EXISTS game_rooms (
    id VARCHAR(36) PRIMARY KEY,
    room_code VARCHAR(6) UNIQUE NOT NULL,
    player1_id VARCHAR(36) REFERENCES users(id),
    player2_id VARCHAR(36) REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'waiting', -- waiting, active, finished, abandoned
    ruleset_id VARCHAR(50) DEFAULT 'traditional',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Match Results & History Archive
CREATE TABLE IF NOT EXISTS matches (
    id VARCHAR(36) PRIMARY KEY,
    room_code VARCHAR(6),
    player1_id VARCHAR(36) REFERENCES users(id),
    player2_id VARCHAR(36) REFERENCES users(id),
    player1_score INT NOT NULL,
    player2_score INT NOT NULL,
    winner_id VARCHAR(36), -- NULL for draw
    ruleset VARCHAR(50) NOT NULL,
    total_moves INT DEFAULT 0,
    final_board_json TEXT,
    started_at TIMESTAMP,
    ended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Move Replay Log
CREATE TABLE IF NOT EXISTS match_moves (
    id BIGSERIAL PRIMARY KEY,
    match_id VARCHAR(36) REFERENCES matches(id) ON DELETE CASCADE,
    move_number INT NOT NULL,
    player_id VARCHAR(36) NOT NULL,
    pit_index INT NOT NULL,
    shells_sown INT NOT NULL,
    shells_captured INT DEFAULT 0,
    extra_turn_awarded BOOLEAN DEFAULT FALSE,
    board_snapshot_json TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
