server/supabase_migration_roles.sql

-- 1. Create the new global 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
);

-- 2. Backfill existing user roles into chat_users
-- We find the most recent message for each user and take their roles.
-- Since the old DB only had is_mod and is_sub, we use those.
INSERT INTO chat_users (username, display_name, is_mod, is_sub, is_vip, last_seen)
SELECT 
    username, 
    max(display_name), 
    bool_or(is_mod), 
    bool_or(is_sub), 
    FALSE, 
    max(first_seen)
FROM stream_viewers
GROUP BY username
ON CONFLICT (username) DO NOTHING;

-- 3. We can optionally drop is_mod and is_sub from messages and stream_viewers, 
-- but it's safer to just leave them (or drop them later). For now, we leave them to not break things.

-- 4. Recreate the global_chatter_stats view to use the new global roles
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;

-- 5. Recreate the stream_chatter_stats view to use the new global roles
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;