CREATE DOMAIN snowflake AS NUMERIC(20,0) CHECK (VALUE > 0);
CREATE DOMAIN uint AS INTEGER CHECK (VALUE >= 0);

CREATE TABLE IF NOT EXISTS guilds (
    id snowflake PRIMARY KEY,
    disabled_channels TEXT,
    fallback_channel snowflake,
    days_xp_remains INTEGER CHECK (days_xp_remains > 0) DEFAULT 90 
);

CREATE TABLE IF NOT EXISTS copaings (
    id snowflake UNIQUE,
    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
    PRIMARY KEY(id, guild_id)
);

CREATE TABLE IF NOT EXISTS copaing_xps (
    copaing_id snowflake REFERENCES copaings(id) ON DELETE CASCADE,
    guild_id snowflake REFERENCES copaings(id) ON DELETE CASCADE,
    xp uint DEFAULT 0,
    created_at TIMESTAMP DEFAULT now(),
    PRIMARY KEY(copaing_id, guild_id, created_at)
);

CREATE TABLE IF NOT EXISTS xp_roles (
    xp uint,
    role snowflake,
    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
    PRIMARY KEY(xp, role, guild_id)
);

CREATE TABLE IF NOT EXISTS role_react_messages (
    id SERIAL PRIMARY KEY,
    message_id snowflake UNIQUE,
    channel_id snowflake,
    guild_id snowflake REFERENCES guilds(id) ON DELETE CASCADE,
    note TEXT
);

CREATE TABLE IF NOT EXISTS role_reacts (
    role snowflake,
    message_id INTEGER REFERENCES role_react_messages(id) ON DELETE CASCADE,
    reaction TEXT,
    PRIMARY KEY(role, message_id, reaction)
);