-- Book file system and content management
-- Split from original 001_create_books_structure.sql for better organization
-- Depends on: 002_000_books_core_structure.sql

-- Create file_system_items table (supports both files and folders)
CREATE TABLE file_system_items (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    book_id UUID REFERENCES books(id) ON DELETE CASCADE NOT NULL,
    parent_id UUID REFERENCES file_system_items(id) ON DELETE CASCADE,
    
    -- Basic properties
    name TEXT NOT NULL,
    type TEXT NOT NULL CHECK (type IN ('file', 'folder')),
    
    -- File-specific properties
    content TEXT, -- For text files
    file_extension TEXT, -- e.g., 'md', 'txt', 'docx'
    mime_type TEXT, -- e.g., 'text/markdown', 'image/jpeg', 'video/mp4'
    file_size BIGINT, -- in bytes
    file_url TEXT, -- URL for uploaded files (images, videos, etc.)
    
    -- Folder-specific properties
    expanded BOOLEAN DEFAULT false, -- UI state for folders
    
    -- Ordering and organization
    sort_order INTEGER DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,
    
    -- Constraints
    CONSTRAINT valid_file_content CHECK (
        (type = 'file' AND name IS NOT NULL) OR 
        (type = 'folder' AND name IS NOT NULL)
    ),
    CONSTRAINT valid_file_properties CHECK (
        (type = 'folder') OR 
        (type = 'file' AND file_extension IS NOT NULL)
    )
);

-- Create indexes for performance
CREATE INDEX idx_file_system_items_book_id ON file_system_items(book_id);
CREATE INDEX idx_file_system_items_parent_id ON file_system_items(parent_id);
CREATE INDEX idx_file_system_items_type ON file_system_items(type);
CREATE INDEX idx_file_system_items_sort_order ON file_system_items(book_id, parent_id, sort_order);

-- Create trigger for updated_at on file_system_items
CREATE TRIGGER update_file_system_items_updated_at 
    BEFORE UPDATE ON file_system_items 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Function to calculate and update word count for a book
CREATE OR REPLACE FUNCTION calculate_book_word_count(book_uuid UUID)
RETURNS INTEGER AS $$
DECLARE
    total_words INTEGER := 0;
    file_content TEXT;
    word_count INTEGER;
BEGIN
    -- Sum word counts from all text files in the book
    FOR file_content IN 
        SELECT content 
        FROM file_system_items 
        WHERE book_id = book_uuid 
        AND type = 'file' 
        AND content IS NOT NULL
        AND content != ''
    LOOP
        -- Simple word count: split by whitespace and count non-empty elements
        SELECT array_length(string_to_array(regexp_replace(file_content, E'[\\n\\r]+', ' ', 'g'), ' '), 1) 
        INTO word_count;
        
        -- Handle null case (empty content)
        IF word_count IS NULL THEN
            word_count := 0;
        END IF;
        
        total_words := total_words + word_count;
    END LOOP;
    
    -- Update the book's word count
    UPDATE books SET word_count = total_words WHERE id = book_uuid;
    
    RETURN total_words;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update word count when file content changes
CREATE OR REPLACE FUNCTION update_book_word_count()
RETURNS TRIGGER AS $$
BEGIN
    -- Only update word count for text files
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.type = 'file' THEN
        PERFORM calculate_book_word_count(NEW.book_id);
    ELSIF TG_OP = 'DELETE' AND OLD.type = 'file' THEN
        PERFORM calculate_book_word_count(OLD.book_id);
    END IF;
    
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_word_count_trigger
    AFTER INSERT OR UPDATE OR DELETE ON file_system_items
    FOR EACH ROW
    EXECUTE FUNCTION update_book_word_count();

-- Row Level Security (RLS) policies

-- Enable RLS
ALTER TABLE file_system_items ENABLE ROW LEVEL SECURITY;

-- File system items policies
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 = 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 = 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 = 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 = auth.uid()
        )
    );

-- Create a view for easier querying of file tree structure
CREATE OR REPLACE VIEW file_tree AS
WITH RECURSIVE tree AS (
    -- Base case: root level items (no parent)
    SELECT 
        fsi.*,
        0 as depth,
        ARRAY[fsi.sort_order] as path
    FROM file_system_items fsi
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case: children
    SELECT 
        fsi.*,
        tree.depth + 1,
        tree.path || fsi.sort_order
    FROM file_system_items fsi
    INNER JOIN tree ON tree.id = fsi.parent_id
)
SELECT * FROM tree ORDER BY book_id, path;

-- Enable Realtime for file_system_items table
-- Add after table creation to avoid dependency issues
DO $$
BEGIN
    BEGIN
        ALTER PUBLICATION supabase_realtime ADD TABLE file_system_items;
    EXCEPTION WHEN duplicate_object THEN
        -- Table already in publication, do nothing
        NULL;
    END;
END $$;