server/init_db.sql

-- SQL Script to initialize the Supabase PostgreSQL database
-- Copy and paste this into the Supabase SQL Editor (SQL Editor -> New Query)

-- 1. STREAMS TABLE
CREATE TABLE IF NOT EXISTS streams (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP WITH TIME ZONE,
    title TEXT,
    twitch_stream_id TEXT UNIQUE,
    category TEXT,
    backfill_status TEXT DEFAULT 'pending',
    twitch_vod_id TEXT
);

-- 2. MESSAGES TABLE
CREATE TABLE IF NOT EXISTS messages (
    id TEXT PRIMARY KEY, -- Twitch Message ID (UUID-like from tags)
    stream_id INTEGER REFERENCES streams(id) ON DELETE SET NULL,
    username TEXT NOT NULL,
    display_name TEXT,
    message TEXT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_streamer BOOLEAN DEFAULT FALSE,
    is_mod BOOLEAN DEFAULT FALSE,
    is_sub BOOLEAN DEFAULT FALSE
);

-- Create indexes for fast querying and statistics aggregation
CREATE INDEX IF NOT EXISTS idx_messages_stream_id ON messages(stream_id);
CREATE INDEX IF NOT EXISTS idx_messages_username ON messages(username);
CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp);

-- 2.5 STREAM VIEWERS TABLE
CREATE TABLE IF NOT EXISTS stream_viewers (
    stream_id INTEGER REFERENCES streams(id) ON DELETE CASCADE,
    username TEXT NOT NULL,
    display_name TEXT,
    has_chatted BOOLEAN DEFAULT FALSE,
    is_mod BOOLEAN DEFAULT FALSE,
    is_sub BOOLEAN DEFAULT FALSE,
    first_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (stream_id, username)
);
CREATE INDEX IF NOT EXISTS idx_stream_viewers_stream_id ON stream_viewers(stream_id);

-- 2.6 CHAT USERS TABLE
CREATE TABLE IF NOT EXISTS chat_users (
    username TEXT PRIMARY KEY,
    display_name TEXT,
    is_mod BOOLEAN DEFAULT FALSE,
    is_sub BOOLEAN DEFAULT FALSE,
    is_vip BOOLEAN DEFAULT FALSE,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. VOICE WORDS TABLE (transcribed spoken words from streamer)
CREATE TABLE IF NOT EXISTS voice_words (
    id SERIAL PRIMARY KEY,
    stream_id INTEGER REFERENCES streams(id) ON DELETE CASCADE,
    word TEXT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_voice_words_stream_id ON voice_words(stream_id);
CREATE INDEX IF NOT EXISTS idx_voice_words_word ON voice_words(word);

-- 4. MOD ACTIONS TABLE (from EventSub: bans, timeouts, message deletions)
CREATE TABLE IF NOT EXISTS mod_actions (
    id SERIAL PRIMARY KEY,
    stream_id INTEGER REFERENCES streams(id) ON DELETE SET NULL,
    action_type TEXT NOT NULL, -- 'ban', 'timeout', 'unban', 'delete'
    moderator TEXT NOT NULL, -- Moderator's username
    target_user TEXT, -- Banned/timed out user's username
    duration INTEGER, -- Timeout duration in seconds (NULL for bans/deletions)
    reason TEXT, -- Reason provided
    message_text TEXT, -- Text of deleted message (for 'delete' action)
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    reaction_time REAL
);

CREATE INDEX IF NOT EXISTS idx_mod_actions_stream_id ON mod_actions(stream_id);
CREATE INDEX IF NOT EXISTS idx_mod_actions_timestamp ON mod_actions(timestamp);

-- 5. SETTINGS TABLE (for persistent oauth tokens, etc.)
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- A trigger to automatically update updated_at in settings table
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE OR REPLACE TRIGGER update_settings_modtime
    BEFORE UPDATE ON settings
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

-- =========================================================================
-- HELPER VIEWS FOR STATISTICS
-- =========================================================================

-- View for chatter statistics per stream
DROP VIEW IF EXISTS stream_chatter_stats;
CREATE VIEW stream_chatter_stats AS
SELECT 
    v.stream_id,
    v.username,
    u.display_name,
    u.is_mod,
    u.is_sub,
    u.is_vip,
    v.has_chatted,
    v.first_seen,
    (SELECT COUNT(*) FROM messages m WHERE m.stream_id = v.stream_id AND m.username = v.username) as message_count
FROM stream_viewers v
LEFT JOIN chat_users u ON v.username = u.username;

-- View for chatter statistics overall (all-time)
DROP VIEW IF EXISTS global_chatter_stats;
CREATE VIEW global_chatter_stats AS
SELECT 
    m.username,
    u.display_name,
    u.is_mod,
    u.is_sub,
    u.is_vip,
    count(*) as message_count
FROM messages m
LEFT JOIN chat_users u ON m.username = u.username
GROUP BY m.username, u.display_name, u.is_mod, u.is_sub, u.is_vip;

-- View for spoken word statistics per stream
CREATE OR REPLACE VIEW stream_voice_word_stats AS
SELECT 
    stream_id,
    word,
    count(*) as word_count
FROM voice_words
GROUP BY stream_id, word;

-- View for spoken word statistics overall (all-time)
CREATE OR REPLACE VIEW global_voice_word_stats AS
SELECT 
    word,
    count(*) as word_count
FROM voice_words
GROUP BY word;