schema.sql

CREATE DATABASE game_ledger_db;
USE game_ledger_db;

CREATE TABLE players(
    player_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    currency_balance INT UNSIGNED DEFAULT 0,
    account_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE items(
    item_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(255) NOT NULL UNIQUE,
    item_cost INT UNSIGNED NOT NULL,
    item_category VARCHAR(255) NOT NULL
);

CREATE TABLE player_inventory(
    inventory_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    player_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    date_acquired TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (player_id) REFERENCES players(player_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

CREATE TABLE matches(
    match_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    start_date_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    end_date_time DATETIME NULL,
    game_mode ENUM('coop_escape','asymmetrical_hunt','puzzle_gauntlet','survival_defense') NOT NULL,
    status ENUM('waiting','active','completed') NOT NULL
);

CREATE TABLE match_participants(
    match_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    score INT UNSIGNED DEFAULT 0,
    survival_status ENUM('survived','escaped','eliminated') NOT NULL,
    FOREIGN KEY (match_id) REFERENCES matches(match_id),
    FOREIGN KEY (player_id) REFERENCES players(player_id)
);

CREATE TABLE transactions_ledger(
    transaction_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    player_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED,
    amount INT NOT NULL,
    reason VARCHAR(255) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (player_id) REFERENCES players(player_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE SET NULL
);