-- Creating the DB structure
-- Version 1.0 (2025-09-25)

CREATE TABLE IF NOT EXISTS minecraft_users (
    uuid VARCHAR(32) PRIMARY KEY not null,
    user_name VARCHAR(16) not null
);

CREATE TABLE IF NOT EXISTS discord_users (
    snowflake_id VARCHAR(32) PRIMARY KEY not null,
    handle VARCHAR(32) not null,
    minecraft_user_uuid VARCHAR(32),
    FOREIGN KEY (minecraft_user_uuid) REFERENCES minecraft_users(uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS minecraft_servers (
    domain VARCHAR(128) PRIMARY KEY not null
);

CREATE TABLE IF NOT EXISTS mc_users_on_mc_server (
    minecraft_user VARCHAR(32) not null,
    minecraft_server VARCHAR(64) not null,
    PRIMARY KEY(minecraft_user, minecraft_server),
    FOREIGN KEY(minecraft_user) REFERENCES minecraft_users(uuid) ON DELETE CASCADE,
    FOREIGN KEY(minecraft_server) REFERENCES minecraft_servers(domain) ON DELETE CASCADE
);