-- Migration: Server-side unread tracking
-- Adds last_read_message_id for precise unread counting
-- Adds indexes for efficient unread computation

-- Add last_read_message_id to channel_read_receipts
ALTER TABLE channel_read_receipts
  ADD COLUMN IF NOT EXISTS last_read_message_id UUID REFERENCES messages(id) ON DELETE SET NULL;

-- Index for fast unread count queries
CREATE INDEX IF NOT EXISTS idx_read_receipts_user ON channel_read_receipts(user_id);
CREATE INDEX IF NOT EXISTS idx_messages_channel_created ON messages(channel_id, created_at);

-- Add indexes to notifications table for efficient per-user queries
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_read ON notifications(read);
CREATE INDEX IF NOT EXISTS idx_notifications_type ON notifications(type);
CREATE INDEX IF NOT EXISTS idx_notifications_created ON notifications(created_at);
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread ON notifications(user_id, read) WHERE read = FALSE;

-- Add channel_id to notifications for per-channel mention counting
ALTER TABLE notifications
  ADD COLUMN IF NOT EXISTS channel_id UUID REFERENCES channels(id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS idx_notifications_channel ON notifications(channel_id);
