-- GOTMYHOST : Server Strike Festival Game Database
-- Import this file into the NEW empty database from cPanel > phpMyAdmin.

CREATE TABLE IF NOT EXISTS players (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(60) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(120) NOT NULL,
    player_token CHAR(64) NOT NULL,
    created_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unique_mobile (mobile),
    UNIQUE KEY unique_player_token (player_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scores (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    player_id INT UNSIGNED NOT NULL,
    score INT UNSIGNED NOT NULL,
    level_reached INT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY index_player_score (player_id, score),
    KEY index_created_at (created_at),
    CONSTRAINT scores_player_fk
        FOREIGN KEY (player_id) REFERENCES players(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;