-- Final fix for the process_word_count_queue function
-- This should resolve the "column reference book_id is ambiguous" error

DROP FUNCTION IF EXISTS process_word_count_queue(INTEGER);

CREATE OR REPLACE FUNCTION process_word_count_queue(batch_size INTEGER DEFAULT 10)
RETURNS TABLE(book_id UUID, old_count INTEGER, new_count INTEGER, success BOOLEAN, error_message TEXT)
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    queue_record RECORD;
    old_word_count INTEGER;
    new_word_count INTEGER;
    error_text TEXT;
BEGIN
    -- Process books that need recalculation
    -- Use explicit table aliases to avoid ambiguous column references
    FOR queue_record IN 
        SELECT 
            q.book_id as queue_book_id, 
            b.title, 
            b.word_count
        FROM book_word_count_queue q
        JOIN books b ON q.book_id = b.id
        WHERE q.processed_at IS NULL
        AND q.attempts < 3  -- Limit retry attempts
        ORDER BY q.queued_at ASC
        LIMIT batch_size
    LOOP
        BEGIN
            old_word_count := queue_record.word_count;
            
            -- Calculate new word count
            new_word_count := calculate_book_word_count(queue_record.queue_book_id);
            
            -- Update the book's word count
            UPDATE books 
            SET word_count = new_word_count, 
                updated_at = NOW()
            WHERE id = queue_record.queue_book_id;
            
            -- Mark as processed
            UPDATE book_word_count_queue
            SET processed_at = NOW(),
                last_error = NULL
            WHERE book_id = queue_record.queue_book_id;
            
            -- Return success result
            book_id := queue_record.queue_book_id;
            old_count := old_word_count;
            new_count := new_word_count;
            success := TRUE;
            error_message := NULL;
            
            RETURN NEXT;
            
        EXCEPTION WHEN OTHERS THEN
            -- Handle errors
            error_text := SQLERRM;
            
            -- Update attempt count and error
            UPDATE book_word_count_queue
            SET attempts = attempts + 1,
                last_error = error_text
            WHERE book_id = queue_record.queue_book_id;
            
            -- Return error result
            book_id := queue_record.queue_book_id;
            old_count := queue_record.word_count;
            new_count := NULL;
            success := FALSE;
            error_message := error_text;
            
            RETURN NEXT;
        END;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;