-- ============================================================ -- Chess Stakes Platform — Database Schema -- Core PHP / MySQL (PDO) -- ============================================================ CREATE DATABASE IF NOT EXISTS chess_stakes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE chess_stakes; -- ------------------------------------------------------------ -- Countries: drives currency + payment gateway + feature flags -- ------------------------------------------------------------ CREATE TABLE countries ( id INT AUTO_INCREMENT PRIMARY KEY, iso_code CHAR(2) NOT NULL UNIQUE, -- e.g. 'NG', 'GH', 'US' name VARCHAR(100) NOT NULL, currency_code CHAR(3) NOT NULL, -- e.g. 'NGN', 'GHS', 'USD' currency_symbol VARCHAR(5) NOT NULL DEFAULT '', payment_gateway VARCHAR(50) NOT NULL DEFAULT '', -- 'paystack', 'flutterwave', 'none' real_money_enabled TINYINT(1) NOT NULL DEFAULT 0, -- master switch — compliance gate deposits_enabled TINYINT(1) NOT NULL DEFAULT 0, withdrawals_enabled TINYINT(1) NOT NULL DEFAULT 0, staking_enabled TINYINT(1) NOT NULL DEFAULT 0, min_stake DECIMAL(18,2) NOT NULL DEFAULT 0, max_stake DECIMAL(18,2) NOT NULL DEFAULT 0, rake_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00, -- admin-configurable platform fee created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Users -- ------------------------------------------------------------ CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(150) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, country_id INT NOT NULL, kyc_status ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified', id_document_ref VARCHAR(255) DEFAULT NULL, -- pointer to stored KYC doc, not raw data status ENUM('active','suspended','banned') NOT NULL DEFAULT 'active', role ENUM('player','admin','superadmin') NOT NULL DEFAULT 'player', rating INT NOT NULL DEFAULT 1200, -- chess rating, simple Elo created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_login_at TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (country_id) REFERENCES countries(id) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Wallets — one balance row per user, per currency they hold -- Actual balance is a cached sum; ledger is source of truth -- ------------------------------------------------------------ CREATE TABLE wallets ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, currency_code CHAR(3) NOT NULL, balance DECIMAL(18,2) NOT NULL DEFAULT 0, -- available balance escrow_balance DECIMAL(18,2) NOT NULL DEFAULT 0, -- funds locked in active stakes updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_user_currency (user_id, currency_code), FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Ledger — append-only, source of truth for every fund movement -- ------------------------------------------------------------ CREATE TABLE ledger_entries ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, wallet_id INT NOT NULL, game_id INT DEFAULT NULL, type ENUM( 'deposit', 'withdrawal', 'stake_escrow', -- funds moved into escrow for a game 'stake_release_win', -- escrow released to winner (net of rake) 'stake_release_loss',-- escrow debited from loser 'stake_refund', -- escrow returned (abandoned/disconnect/draw) 'platform_fee' -- rake taken by platform ) NOT NULL, amount DECIMAL(18,2) NOT NULL, -- always positive; direction implied by type currency_code CHAR(3) NOT NULL, balance_after DECIMAL(18,2) NOT NULL, -- available balance after this entry reference VARCHAR(100) DEFAULT NULL, -- payment gateway transaction ref note VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (wallet_id) REFERENCES wallets(id), INDEX idx_user_created (user_id, created_at) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Games — one row per chess match -- ------------------------------------------------------------ CREATE TABLE games ( id INT AUTO_INCREMENT PRIMARY KEY, white_user_id INT NOT NULL, black_user_id INT DEFAULT NULL, -- NULL until a stake is accepted stake_amount DECIMAL(18,2) NOT NULL DEFAULT 0, currency_code CHAR(3) NOT NULL, rake_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00, -- snapshot of rate at game creation status ENUM( 'open', -- waiting for opponent to accept stake 'active', -- both players in, game in progress 'finished', -- completed normally (checkmate/resign/draw) 'abandoned', -- disconnect before completion -> refunded 'cancelled' -- creator cancelled before anyone accepted ) NOT NULL DEFAULT 'open', result ENUM('white_win','black_win','draw','void') DEFAULT NULL, result_reason VARCHAR(100) DEFAULT NULL, -- 'checkmate','resignation','timeout','abandonment','draw_agreed' board_fen VARCHAR(100) NOT NULL DEFAULT 'rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR w KQkq - 0 1', turn ENUM('white','black') NOT NULL DEFAULT 'white', white_last_seen TIMESTAMP NULL DEFAULT NULL, black_last_seen TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, started_at TIMESTAMP NULL DEFAULT NULL, finished_at TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (white_user_id) REFERENCES users(id), FOREIGN KEY (black_user_id) REFERENCES users(id), INDEX idx_status (status) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Moves — full move history per game (also used for replay/audit) -- ------------------------------------------------------------ CREATE TABLE moves ( id BIGINT AUTO_INCREMENT PRIMARY KEY, game_id INT NOT NULL, move_number INT NOT NULL, player_color ENUM('white','black') NOT NULL, from_square CHAR(2) NOT NULL, to_square CHAR(2) NOT NULL, promotion CHAR(1) DEFAULT NULL, -- 'q','r','b','n' san VARCHAR(10) NOT NULL, -- standard algebraic notation e.g. 'Nf3' fen_after VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (game_id) REFERENCES games(id), INDEX idx_game (game_id) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Admin action audit log -- ------------------------------------------------------------ CREATE TABLE admin_actions ( id INT AUTO_INCREMENT PRIMARY KEY, admin_user_id INT NOT NULL, action VARCHAR(100) NOT NULL, target_type VARCHAR(50) DEFAULT NULL, target_id INT DEFAULT NULL, details TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (admin_user_id) REFERENCES users(id) ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- Seed a starting country (compliance gate OFF by default — -- must be deliberately switched on per country by an admin) -- ------------------------------------------------------------ INSERT INTO countries (iso_code, name, currency_code, currency_symbol, payment_gateway, real_money_enabled, deposits_enabled, withdrawals_enabled, staking_enabled, min_stake, max_stake, rake_percent) VALUES ('NG', 'Nigeria', 'NGN', '₦', 'paystack', 0, 0, 0, 0, 500.00, 500000.00, 10.00);