-- Fix RLS performance issues reported by database linter
-- This migration addresses:
-- 1. Auth RLS Initialization Plan issues - replace auth.uid() with (select auth.uid())
-- 2. Multiple Permissive Policies issues - consolidate policies where possible

-- ============================================================================
-- 1. FIX PROFILES TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can insert their own profile." ON public.profiles;
DROP POLICY IF EXISTS "Users can update their own profile." ON public.profiles;

-- Recreate with optimized auth calls
CREATE POLICY "Users can insert their own profile." ON public.profiles
  FOR INSERT WITH CHECK ((select auth.uid()) = id);

CREATE POLICY "Users can update their own profile." ON public.profiles
  FOR UPDATE USING ((select auth.uid()) = id);

-- ============================================================================
-- 2. FIX BOOKS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own books" ON books;
DROP POLICY IF EXISTS "Users can create their own books" ON books;
DROP POLICY IF EXISTS "Users can update their own books" ON books;
DROP POLICY IF EXISTS "Users can delete their own books" ON books;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own books" ON books
    FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can create their own books" ON books
    FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "Users can update their own books" ON books
    FOR UPDATE USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can delete their own books" ON books
    FOR DELETE USING ((select auth.uid()) = user_id);

-- ============================================================================
-- 3. FIX FILE_SYSTEM_ITEMS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own file system items" ON file_system_items;
DROP POLICY IF EXISTS "Users can create file system items in their books" ON file_system_items;
DROP POLICY IF EXISTS "Users can update file system items in their books" ON file_system_items;
DROP POLICY IF EXISTS "Users can delete file system items in their books" ON file_system_items;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own file system items" ON file_system_items
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = file_system_items.book_id 
            AND books.user_id = (select auth.uid())
        )
    );

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

CREATE POLICY "Users can update file system items in their books" ON file_system_items
    FOR UPDATE USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = file_system_items.book_id 
            AND books.user_id = (select auth.uid())
        )
    );

CREATE POLICY "Users can delete file system items in their books" ON file_system_items
    FOR DELETE USING (
        EXISTS (
            SELECT 1 FROM books 
            WHERE books.id = file_system_items.book_id 
            AND books.user_id = (select auth.uid())
        )
    );

-- ============================================================================
-- 4. FIX CHATS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own chats" ON chats;
DROP POLICY IF EXISTS "Users can create chats in their books" ON chats;
DROP POLICY IF EXISTS "Users can update their own chats" ON chats;
DROP POLICY IF EXISTS "Users can delete their own chats" ON chats;

-- Recreate with optimized auth calls
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 = (select 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 = (select 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 = (select 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 = (select auth.uid())
        )
    );

-- ============================================================================
-- 5. FIX MESSAGES TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view messages in their chats" ON messages;
DROP POLICY IF EXISTS "Users can create messages in their chats" ON messages;
DROP POLICY IF EXISTS "Users can update messages in their chats" ON messages;
DROP POLICY IF EXISTS "Users can delete messages in their chats" ON messages;

-- Recreate with optimized auth calls
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 = (select 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 = (select 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 = (select 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 = (select auth.uid())
        )
    );

-- ============================================================================
-- 6. FIX AI_USAGE TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view own AI usage" ON ai_usage;
DROP POLICY IF EXISTS "System can insert AI usage" ON ai_usage;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view own AI usage" ON ai_usage
    FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "System can insert AI usage" ON ai_usage
    FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

-- ============================================================================
-- 7. FIX AI_OPERATIONS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own AI operations" ON ai_operations;
DROP POLICY IF EXISTS "Users can create AI operations for their books" ON ai_operations;
DROP POLICY IF EXISTS "Users can update their own AI operations" ON ai_operations;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own AI operations" ON ai_operations
    FOR SELECT USING (
        -- Allow if user_id matches auth.uid()
        (select auth.uid()) = user_id OR
        -- Allow if user_id is null but book belongs to authenticated user
        (user_id IS NULL AND
         EXISTS (
             SELECT 1 FROM books 
             WHERE books.id = ai_operations.book_id 
             AND books.user_id = (select auth.uid())
         ))
    );

CREATE POLICY "Users can create AI operations for their books" ON ai_operations
    FOR INSERT WITH CHECK (
        -- Allow if user_id matches auth.uid() and book belongs to user
        ((select auth.uid()) = user_id AND
         EXISTS (
             SELECT 1 FROM books 
             WHERE books.id = ai_operations.book_id 
             AND books.user_id = (select auth.uid())
         )) OR
        -- Allow if user_id is null but book belongs to authenticated user
        (user_id IS NULL AND
         EXISTS (
             SELECT 1 FROM books 
             WHERE books.id = ai_operations.book_id 
             AND books.user_id = (select auth.uid())
         ))
    );

CREATE POLICY "Users can update their own AI operations" ON ai_operations
    FOR UPDATE USING (
        -- Allow if user_id matches auth.uid()
        (select auth.uid()) = user_id OR
        -- Allow if user_id is null but book belongs to authenticated user
        (user_id IS NULL AND
         EXISTS (
             SELECT 1 FROM books 
             WHERE books.id = ai_operations.book_id 
             AND books.user_id = (select auth.uid())
         ))
    );

-- ============================================================================
-- 8. FIX FILE_SNAPSHOTS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view snapshots for their operations" ON file_snapshots;
DROP POLICY IF EXISTS "Users can create snapshots for their operations" ON file_snapshots;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view snapshots for their operations" ON file_snapshots
    FOR SELECT USING (
        EXISTS (
            SELECT 1 FROM ai_operations 
            WHERE ai_operations.id = file_snapshots.ai_operation_id 
            AND (
                ai_operations.user_id = (select auth.uid()) OR
                (ai_operations.user_id IS NULL AND
                 EXISTS (
                     SELECT 1 FROM books 
                     WHERE books.id = ai_operations.book_id 
                     AND books.user_id = (select auth.uid())
                 ))
            )
        )
    );

CREATE POLICY "Users can create snapshots for their operations" ON file_snapshots
    FOR INSERT WITH CHECK (
        EXISTS (
            SELECT 1 FROM ai_operations 
            WHERE ai_operations.id = file_snapshots.ai_operation_id 
            AND (
                ai_operations.user_id = (select auth.uid()) OR
                (ai_operations.user_id IS NULL AND
                 EXISTS (
                     SELECT 1 FROM books 
                     WHERE books.id = ai_operations.book_id 
                     AND books.user_id = (select auth.uid())
                 ))
            )
        )
    );

-- ============================================================================
-- 9. FIX USER_USAGE TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own usage" ON user_usage;
DROP POLICY IF EXISTS "Users can insert their own usage" ON user_usage;
DROP POLICY IF EXISTS "Users can update their own usage" ON user_usage;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own usage" ON user_usage
  FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can insert their own usage" ON user_usage
  FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "Users can update their own usage" ON user_usage
  FOR UPDATE USING ((select auth.uid()) = user_id);

-- ============================================================================
-- 10. FIX USAGE_LOGS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own usage logs" ON usage_logs;
DROP POLICY IF EXISTS "Users can insert their own usage logs" ON usage_logs;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own usage logs" ON usage_logs
  FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can insert their own usage logs" ON usage_logs
  FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

-- ============================================================================
-- 11. FIX SUBSCRIPTIONS TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own subscription" ON subscriptions;
DROP POLICY IF EXISTS "Users can insert their own subscription" ON subscriptions;
DROP POLICY IF EXISTS "Users can update their own subscription" ON subscriptions;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own subscription" ON subscriptions
  FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can insert their own subscription" ON subscriptions
  FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "Users can update their own subscription" ON subscriptions
  FOR UPDATE USING ((select auth.uid()) = user_id);

-- ============================================================================
-- 12. FIX EMAIL_PREFERENCES TABLE POLICIES
-- ============================================================================

-- Drop existing policies
DROP POLICY IF EXISTS "Users can view their own email preferences" ON email_preferences;
DROP POLICY IF EXISTS "Users can insert their own email preferences" ON email_preferences;
DROP POLICY IF EXISTS "Users can update their own email preferences" ON email_preferences;

-- Recreate with optimized auth calls
CREATE POLICY "Users can view their own email preferences" ON email_preferences
  FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can insert their own email preferences" ON email_preferences
  FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "Users can update their own email preferences" ON email_preferences
  FOR UPDATE USING ((select auth.uid()) = user_id);

-- ============================================================================
-- 13. FIX BOOK_TEMPLATES TABLE POLICIES (CONSOLIDATE MULTIPLE PERMISSIVE)
-- ============================================================================

-- Drop existing policies that cause multiple permissive issues
DROP POLICY IF EXISTS "Everyone can view system templates" ON book_templates;
DROP POLICY IF EXISTS "Users can view their own custom templates" ON book_templates;
DROP POLICY IF EXISTS "Users can create custom templates" ON book_templates;
DROP POLICY IF EXISTS "Users can update their own custom templates" ON book_templates;
DROP POLICY IF EXISTS "Users can delete their own custom templates" ON book_templates;

-- Create consolidated policies to avoid multiple permissive issues
-- Single SELECT policy that handles both system templates and user templates
CREATE POLICY "Users can view templates" ON book_templates
    FOR SELECT USING (
        is_system = true OR created_by = (select auth.uid())
    );

-- Keep separate policies for INSERT, UPDATE, DELETE as they don't conflict
CREATE POLICY "Users can create custom templates" ON book_templates
    FOR INSERT WITH CHECK (created_by = (select auth.uid()) AND is_system = false);

CREATE POLICY "Users can update their own custom templates" ON book_templates
    FOR UPDATE USING (created_by = (select auth.uid()) AND is_system = false);

CREATE POLICY "Users can delete their own custom templates" ON book_templates
    FOR DELETE USING (created_by = (select auth.uid()) AND is_system = false);

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

-- Add helpful comment
COMMENT ON SCHEMA public IS 'RLS policies optimized for performance - auth.uid() calls wrapped in SELECT subqueries to prevent re-evaluation per row';