-- Add support for standalone chats (not associated with books)
-- This allows for general AI conversations outside of book context

-- First, modify the chats table to make book_id optional
ALTER TABLE chats ALTER COLUMN book_id DROP NOT NULL;

-- Add a new column to distinguish between book chats and standalone chats
ALTER TABLE chats ADD COLUMN chat_type TEXT DEFAULT 'book' CHECK (chat_type IN ('book', 'standalone'));

-- Update existing chats to have 'book' type
UPDATE chats SET chat_type = 'book' WHERE book_id IS NOT NULL;

-- Create index for efficient queries
CREATE INDEX idx_chats_type_user ON chats(chat_type, user_id);
CREATE INDEX idx_chats_standalone_updated ON chats(user_id, updated_at DESC) WHERE chat_type = 'standalone';

-- Update RLS policies for standalone chats
-- Drop existing policies
DROP POLICY "Users can view their own chats" ON chats;
DROP POLICY "Users can create chats in their books" ON chats;
DROP POLICY "Users can update their own chats" ON chats;
DROP POLICY "Users can delete their own chats" ON chats;

-- Create new policies that handle both book and standalone chats
CREATE POLICY "Users can view their own chats" ON chats
    FOR SELECT USING (
        user_id = auth.uid() OR
        (chat_type = 'book' AND EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        ))
    );

CREATE POLICY "Users can create their own chats" ON chats
    FOR INSERT WITH CHECK (
        user_id = auth.uid() AND
        (
            chat_type = 'standalone' OR
            (chat_type = 'book' AND 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 (
        user_id = auth.uid() OR
        (chat_type = 'book' AND 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 (
        user_id = auth.uid() OR
        (chat_type = 'book' AND EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = chats.book_id 
            AND books.user_id = auth.uid()
        ))
    );

-- Update message policies to handle standalone chats
DROP POLICY "Users can view messages in their chats" ON messages;
DROP POLICY "Users can create messages in their chats" ON messages;
DROP POLICY "Users can update messages in their chats" ON messages;
DROP POLICY "Users can delete messages in their chats" ON messages;

CREATE POLICY "Users can view messages in their chats" ON messages
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM chats 
            WHERE chats.id = messages.chat_id 
            AND (
                chats.user_id = auth.uid() OR
                (chats.chat_type = 'book' AND EXISTS (
                    SELECT 1 FROM books 
                    WHERE books.id = chats.book_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 
            WHERE chats.id = messages.chat_id 
            AND (
                chats.user_id = auth.uid() OR
                (chats.chat_type = 'book' AND EXISTS (
                    SELECT 1 FROM books 
                    WHERE books.id = chats.book_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 
            WHERE chats.id = messages.chat_id 
            AND (
                chats.user_id = auth.uid() OR
                (chats.chat_type = 'book' AND EXISTS (
                    SELECT 1 FROM books 
                    WHERE books.id = chats.book_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 
            WHERE chats.id = messages.chat_id 
            AND (
                chats.user_id = auth.uid() OR
                (chats.chat_type = 'book' AND EXISTS (
                    SELECT 1 FROM books 
                    WHERE books.id = chats.book_id 
                    AND books.user_id = auth.uid()
                ))
            )
        )
    );