schema.sql

CREATE TABLE player (
    player_id     INT PRIMARY KEY AUTO_INCREMENT,
    uuid          VARCHAR(36) NOT NULL UNIQUE,
    name          VARCHAR(16) NOT NULL UNIQUE,
    role ENUM(
        'Booster', 
        'Partner', 
        'VIP', 
        'MVP', 
        'Monkey', 
        'Helper', 
        'Moderator', 
        'Team', 
        'Admin'
    ) NULL,
    last_login    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    lang          VARCHAR(10) NOT NULL DEFAULT 'fr',
    banana        INT NULL DEFAULT 0,
    host          INT NULL DEFAULT 1,

    INDEX idx_uuid (uuid),
    INDEX idx_name (name),
    INDEX idx_role (role)
);

ALTER TABLE player
    ADD CONSTRAINT chk_banana_non_negative CHECK (banana >= 0),
    ADD CONSTRAINT chk_host_non_negative CHECK (host >= 0);

CREATE TABLE player_settings (
    player_id     INT NOT NULL,
    setting_key   VARCHAR(50) NOT NULL,
    setting_value TEXT NOT NULL,

    PRIMARY KEY (player_id, setting_key),
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    INDEX idx_setting_key (setting_key)
);

CREATE TABLE player_stats (
    player_id     INT PRIMARY KEY,
    kills         INT NOT NULL DEFAULT 0,
    deaths        INT NOT NULL DEFAULT 0,
    wins          INT NOT NULL DEFAULT 0,
    games_played  INT NOT NULL DEFAULT 0,
    playtime      INT NOT NULL DEFAULT 0,
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE
);

-- ALTER TABLE player
--     ADD CONSTRAINT chk_kills_non_negative CHECK (kills >= 0),
--     ADD CONSTRAINT chk_deaths_non_negative CHECK (deaths >= 0),
--     ADD CONSTRAINT chk_wins_non_negative CHECK (wins >= 0),
--     ADD CONSTRAINT chk_games_played_non_negative CHECK (games_played >= 0),
--     ADD CONSTRAINT chk_playtime_non_negative CHECK (playtime >= 0);

CREATE TABLE player_link (
    player_id     INT PRIMARY KEY,
    discord_id    BIGINT UNSIGNED NULL,
    connected     BOOLEAN NOT NULL DEFAULT FALSE,
    code          VARCHAR(6) NULL, # 62^6 possibilities - 56,800,235,584
    status        ENUM('N/A', 'PENDING', 'CONNECTED') NULL DEFAULT 'N/A',
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE
);

CREATE TABLE friend_request (
    sender_id    INT NOT NULL,
    receiver_id  INT NOT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (sender_id, receiver_id),
    FOREIGN KEY (sender_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES player(player_id) ON DELETE CASCADE,
    INDEX idx_friend_request (sender_id, receiver_id)
);

ALTER TABLE friend_request
    ADD CONSTRAINT chk_not_self_request CHECK (sender_id <> receiver_id);

CREATE TABLE friendship (
    player_id    INT NOT NULL,
    friend_id    INT NOT NULL,
    since        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	
    PRIMARY KEY (player_id, friend_id),
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (friend_id) REFERENCES player(player_id) ON DELETE CASCADE,
    INDEX idx_friendship (player_id, friend_id)
);

ALTER TABLE friendship
    ADD CONSTRAINT chk_not_self_friendship CHECK (player_id <> friend_id);

CREATE TABLE ban (
    ban_id          INT PRIMARY KEY AUTO_INCREMENT,
    player_id       INT NOT NULL,
    staff_id        INT NOT NULL,
    reason          TEXT NOT NULL,
    type ENUM(
        'CHEAT',
        'GLITCH',
        'DISRESPECTFUL BEHAVIOR',
        'INAPPROPRIATE CONTENT',
        'IMPERSONATION',
        'SANCTION CIRCUMVENTION',
        'BAN WAVE',
        'OTHER'
    ) NOT NULL,
    expiration_date DATETIME DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES player(player_id),
    INDEX idx_ban_player (player_id),
    INDEX idx_ban_expiration (expiration_date)
);

ALTER TABLE ban
    ADD CONSTRAINT chk_ban_not_self CHECK (player_id <> staff_id);

CREATE TABLE mute (
    mute_id         INT PRIMARY KEY AUTO_INCREMENT,
    player_id       INT NOT NULL,
    staff_id        INT NOT NULL,
    reason          TEXT NOT NULL,
    type ENUM(
        'TEXT ABUSE',
        'VOICE ABUSE',
        'DISRESPECTFUL BEHAVIOR',
        'OTHER'
    ) NOT NULL,
    expiration_date DATETIME DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES player(player_id),
    INDEX idx_mute_player (player_id),
    INDEX idx_mute_expiration (expiration_date)
);

ALTER TABLE mute
    ADD CONSTRAINT chk_mute_not_self CHECK (player_id <> staff_id);

CREATE TABLE warn (
    warn_id       INT PRIMARY KEY AUTO_INCREMENT,
    player_id     INT NOT NULL,
    staff_id      INT NOT NULL,
    reason        TEXT NOT NULL,
    type ENUM(
        'TEXT ABUSE',
        'VOICE ABUSE',
        'DISRESPECTFUL BEHAVIOR',
        'OTHER'
    ) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES player(player_id),
    INDEX idx_warn_player (player_id)
);

ALTER TABLE warn
    ADD CONSTRAINT chk_warn_not_self CHECK (player_id <> staff_id);

CREATE TABLE report (
    report_id     INT PRIMARY KEY AUTO_INCREMENT,
    player_id     INT NOT NULL,
    victim_id     INT NOT NULL,
    reason        TEXT NOT NULL,
    type ENUM(
        'TEXT ABUSE',
        'VOICE ABUSE',
        'OFFENSIVE NAME',
        'DISRESPECTFUL BEHAVIOR',
        'CHEAT',
        'GLITCH',
        'BOTTING',
        'OTHER'
    ) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	
    FOREIGN KEY (player_id) REFERENCES player(player_id) ON DELETE CASCADE,
    FOREIGN KEY (victim_id) REFERENCES player(player_id),
    INDEX idx_warn_player (player_id)
);

ALTER TABLE report
    ADD CONSTRAINT chk_report_not_self CHECK (player_id <> victim_id);