-- Chat system for AI interactions with books
-- Depends on: 002_000_books_core_structure.sql

-- Create chats table to store chat sessions
CREATE TABLE chats (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    book_id UUID REFERENCES books(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
    title TEXT NOT NULL DEFAULT 'New Chat',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Chat metadata
    model TEXT DEFAULT 'Gemini 2.5 Flash',
    total_messages INTEGER DEFAULT 0,
    last_message_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create messages table to store individual chat messages
CREATE TABLE messages (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    chat_id UUID REFERENCES chats(id) ON DELETE CASCADE NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('user', 'ai')),
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Message ordering
    sequence_number INTEGER NOT NULL,
    
    -- Optional metadata for AI messages
    model TEXT, -- Which AI model generated this message
    tool_results JSONB, -- Store any tool execution results
    context_info JSONB -- Store context information used
);

-- Create indexes for performance
CREATE INDEX idx_chats_book_id ON chats(book_id);
CREATE INDEX idx_chats_user_id ON chats(user_id);
CREATE INDEX idx_chats_updated_at ON chats(updated_at DESC);

CREATE INDEX idx_messages_chat_id ON messages(chat_id);
CREATE INDEX idx_messages_sequence ON messages(chat_id, sequence_number);
CREATE INDEX idx_messages_created_at ON messages(created_at);

-- Update the updated_at timestamp for chats
CREATE TRIGGER update_chats_updated_at 
    BEFORE UPDATE ON chats 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Function to update chat metadata when messages are added
CREATE OR REPLACE FUNCTION update_chat_metadata()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE chats 
        SET 
            total_messages = total_messages + 1,
            last_message_at = NEW.created_at,
            updated_at = NOW()
        WHERE id = NEW.chat_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE chats 
        SET 
            total_messages = GREATEST(0, total_messages - 1),
            updated_at = NOW()
        WHERE id = OLD.chat_id;
        
        -- Update last_message_at to the latest remaining message
        UPDATE chats 
        SET last_message_at = (
            SELECT MAX(created_at) 
            FROM messages 
            WHERE chat_id = OLD.chat_id
        )
        WHERE id = OLD.chat_id;
        
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update chat metadata
CREATE TRIGGER update_chat_metadata_trigger
    AFTER INSERT OR DELETE ON messages
    FOR EACH ROW
    EXECUTE FUNCTION update_chat_metadata();

-- Enable Row Level Security
ALTER TABLE chats ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Chat policies - users can only access chats for their books
CREATE POLICY "Users can view their own chats" ON chats
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can create chats in their books" ON chats
    FOR INSERT WITH CHECK (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can update their own chats" ON chats
    FOR UPDATE USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can delete their own chats" ON chats
    FOR DELETE USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        )
    );

-- Message policies - users can only access messages in their chats
CREATE POLICY "Users can view messages in their chats" ON messages
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM chats 
            JOIN books ON books.id = chats.book_id
            WHERE chats.id = messages.chat_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can create messages in their chats" ON messages
    FOR INSERT WITH CHECK (
        EXISTS (
            SELECT 1 FROM chats 
            JOIN books ON books.id = chats.book_id
            WHERE chats.id = messages.chat_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can update messages in their chats" ON messages
    FOR UPDATE USING (
        EXISTS (
            SELECT 1 FROM chats 
            JOIN books ON books.id = chats.book_id
            WHERE chats.id = messages.chat_id 
            AND books.user_id = auth.uid()
        )
    );

CREATE POLICY "Users can delete messages in their chats" ON messages
    FOR DELETE USING (
        EXISTS (
            SELECT 1 FROM chats 
            JOIN books ON books.id = chats.book_id
            WHERE chats.id = messages.chat_id 
            AND books.user_id = auth.uid()
        )
    );

-- Enable Realtime for live chat updates
ALTER PUBLICATION supabase_realtime ADD TABLE chats;
ALTER PUBLICATION supabase_realtime ADD TABLE messages;