-- AI operations tracking and revert functionality
-- Depends on: 002_001_books_file_system.sql, 003_000_chat_tables.sql

-- Add AI operations tracking for revert functionality
CREATE TABLE ai_operations (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    book_id UUID REFERENCES books(id) ON DELETE CASCADE NOT NULL,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    
    -- Operation details
    operation_type TEXT NOT NULL CHECK (operation_type IN ('start', 'completed', 'reverted', 'failed')),
    description TEXT NOT NULL,
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    
    -- Additional metadata (JSON)
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Optional: File snapshots table for more granular revert (Phase 2)
CREATE TABLE file_snapshots (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    ai_operation_id UUID REFERENCES ai_operations(id) ON DELETE CASCADE NOT NULL,
    file_id UUID REFERENCES file_system_items(id) ON DELETE CASCADE,
    
    -- Snapshot data
    snapshot_data JSONB NOT NULL, -- Complete file state
    snapshot_type TEXT NOT NULL CHECK (snapshot_type IN ('before', 'after')),
    
    -- Timestamp
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_ai_operations_book_id ON ai_operations(book_id);
CREATE INDEX idx_ai_operations_user_id ON ai_operations(user_id);
CREATE INDEX idx_ai_operations_created_at ON ai_operations(created_at DESC);
CREATE INDEX idx_ai_operations_type ON ai_operations(operation_type);

CREATE INDEX idx_file_snapshots_operation_id ON file_snapshots(ai_operation_id);
CREATE INDEX idx_file_snapshots_file_id ON file_snapshots(file_id);
CREATE INDEX idx_file_snapshots_type ON file_snapshots(snapshot_type);

-- Row Level Security
ALTER TABLE ai_operations ENABLE ROW LEVEL SECURITY;
ALTER TABLE file_snapshots ENABLE ROW LEVEL SECURITY;

-- AI Operations policies
CREATE POLICY "Users can view their own AI operations" ON ai_operations
    FOR SELECT USING (
        -- Allow if user_id matches auth.uid()
        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 = 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
        (auth.uid() = user_id AND
         EXISTS (
             SELECT 1 FROM books 
             WHERE books.id = ai_operations.book_id 
             AND books.user_id = 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 = auth.uid()
         ))
    );

CREATE POLICY "Users can update their own AI operations" ON ai_operations
    FOR UPDATE USING (
        -- Allow if user_id matches auth.uid()
        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 = auth.uid()
         ))
    );

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

-- Function to clean up old AI operations (optional - run periodically)
CREATE OR REPLACE FUNCTION cleanup_old_ai_operations()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Delete AI operations older than 30 days
    DELETE FROM ai_operations 
    WHERE created_at < NOW() - INTERVAL '30 days';
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Add metadata to existing file_system_items for tracking AI changes
ALTER TABLE file_system_items 
ADD COLUMN IF NOT EXISTS last_ai_operation_id UUID REFERENCES ai_operations(id) ON DELETE SET NULL;