-- Fix column name references in update_book_word_count function
-- The function was referencing 'item_type' but the column is actually 'type'

CREATE OR REPLACE FUNCTION update_book_word_count()
RETURNS TRIGGER 
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    book_uuid UUID;
    new_word_count INTEGER;
BEGIN
    -- Get the book_id for the file that was modified
    IF TG_OP = 'DELETE' THEN
        book_uuid := OLD.book_id;
    ELSE
        book_uuid := NEW.book_id;
    END IF;
    
    -- Only process if this is a text file
    -- Use 'type' column (not 'item_type') and 'file_extension' column
    IF (TG_OP = 'DELETE' AND OLD.type = 'file' AND OLD.file_extension IN ('txt', 'md', 'markdown')) OR
       (TG_OP IN ('INSERT', 'UPDATE') AND NEW.type = 'file' AND NEW.file_extension IN ('txt', 'md', 'markdown')) THEN
        
        -- Calculate new word count for the book
        new_word_count := calculate_book_word_count(book_uuid);
        
        -- Update the book's word count
        UPDATE books 
        SET word_count = new_word_count, 
            updated_at = NOW()
        WHERE id = book_uuid;
    END IF;
    
    -- Return appropriate record based on operation
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Also fix the calculate_book_word_count function to use correct column names
CREATE OR REPLACE FUNCTION calculate_book_word_count(book_uuid UUID)
RETURNS INTEGER 
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    total_words INTEGER := 0;
    item_record RECORD;
BEGIN
    -- Sum word counts from all text files in the book
    -- Use 'type' column (not 'item_type') and 'file_extension' column
    FOR item_record IN 
        SELECT content 
        FROM file_system_items 
        WHERE book_id = book_uuid 
        AND type = 'file'
        AND file_extension IN ('txt', 'md', 'markdown')
        AND content IS NOT NULL
    LOOP
        -- Simple word count: split by whitespace and count non-empty elements
        total_words := total_words + (
            SELECT array_length(
                string_to_array(
                    regexp_replace(item_record.content, '\s+', ' ', 'g'), 
                    ' '
                ), 
                1
            )
        );
    END LOOP;
    
    RETURN COALESCE(total_words, 0);
END;
$$ LANGUAGE plpgsql;