-- PostgreSQL schema for Velox
-- Use BIGINT for Discord snowflakes, INT for smaller counters, BOOLEAN properly.

CREATE TABLE IF NOT EXISTS levels (
    guild BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    level INT NOT NULL DEFAULT 0,
    xp BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (guild, user_id)
);

CREATE TABLE IF NOT EXISTS twitch (
    twitch_user TEXT NOT NULL,
    guild_id BIGINT NOT NULL,
    status TEXT NOT NULL DEFAULT 'not live',
    PRIMARY KEY (twitch_user, guild_id)
);

CREATE TABLE IF NOT EXISTS levelsettings (
    guild_id BIGINT PRIMARY KEY,
    levelsys BOOLEAN NOT NULL DEFAULT FALSE,
    message TEXT
);

-- Role rewards per level (supports multiple rewards per guild)
CREATE TABLE IF NOT EXISTS levelrewards (
    guild_id BIGINT NOT NULL,
    levelreq INT NOT NULL,
    role BIGINT NOT NULL,
    PRIMARY KEY (guild_id, levelreq)
);

CREATE TABLE IF NOT EXISTS welcome (
    guild_id BIGINT PRIMARY KEY,
    welcome_channel_id BIGINT,
    welcome_message TEXT,
    welcome_dm TEXT,
    welcome_gif_url TEXT
);

CREATE TABLE IF NOT EXISTS levelup (
    guild_id BIGINT PRIMARY KEY,
    levelup_channel_id BIGINT
);

CREATE TABLE IF NOT EXISTS twitch_config (
    guild_id BIGINT PRIMARY KEY,
    twitch_channel_id BIGINT
);

CREATE TABLE IF NOT EXISTS rps (
    guild_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    score INT NOT NULL DEFAULT 0,
    PRIMARY KEY (guild_id, user_id)
);

CREATE TABLE IF NOT EXISTS defaultrole (
    guild_id BIGINT PRIMARY KEY,
    role_id BIGINT
);

CREATE TABLE IF NOT EXISTS logsettings (
    guild_id BIGINT PRIMARY KEY,
    log_channel_id BIGINT,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE
);

-- Meeting rooms (voice lobby -> auto-created temporary voice channels)
CREATE TABLE IF NOT EXISTS meeting_settings (
    guild_id BIGINT PRIMARY KEY,
    lobby_channel_id BIGINT
);

CREATE TABLE IF NOT EXISTS meeting_temp_channels (
    guild_id BIGINT NOT NULL,
    channel_id BIGINT NOT NULL,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (guild_id, channel_id)
);

-- Projects & members
CREATE TABLE IF NOT EXISTS projects (
    id BIGSERIAL PRIMARY KEY,
    guild_id BIGINT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS project_members (
    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL,
    is_creator BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (project_id, user_id)
);

--
-- Scheduling (one-to-one sessions)
--
CREATE TABLE IF NOT EXISTS schedules (
    id BIGSERIAL PRIMARY KEY,
    guild_id BIGINT NOT NULL,
    requester_id BIGINT NOT NULL,
    invitee_id BIGINT NOT NULL,
    scheduled_at TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, accepted, declined, reschedule_requested
    description TEXT,
    reminder_sent BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS schedule_messages (
    schedule_id BIGINT NOT NULL REFERENCES schedules(id) ON DELETE CASCADE,
    message_id BIGINT NOT NULL,
    channel_id BIGINT NOT NULL,
    is_dm BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (schedule_id, message_id)
);

CREATE TABLE IF NOT EXISTS user_settings (
    user_id BIGINT PRIMARY KEY,
    timezone TEXT NOT NULL
);