-- Book templates system with content fixes applied
-- Consolidates: 008_create_book_templates.sql + 009_fix_template_content.sql
-- Depends on: 002_000_books_core_structure.sql

-- Create book templates table
CREATE TABLE book_templates (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT false,
    is_system BOOLEAN DEFAULT true, -- System vs user-created templates
    structure JSONB NOT NULL, -- Template structure definition
    created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Add template reference to books table
ALTER TABLE books ADD COLUMN template_id UUID REFERENCES book_templates(id) ON DELETE SET NULL;

-- Create indexes for performance
CREATE INDEX idx_book_templates_is_system ON book_templates(is_system);
CREATE INDEX idx_book_templates_is_default ON book_templates(is_default);
CREATE INDEX idx_book_templates_created_by ON book_templates(created_by);
CREATE INDEX idx_books_template_id ON books(template_id);

-- Add updated_at trigger for templates
CREATE TRIGGER update_book_templates_updated_at 
    BEFORE UPDATE ON book_templates 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Row Level Security for templates
ALTER TABLE book_templates ENABLE ROW LEVEL SECURITY;

-- Template policies
CREATE POLICY "Everyone can view system templates" ON book_templates
    FOR SELECT USING (is_system = true);

CREATE POLICY "Users can view their own custom templates" ON book_templates
    FOR SELECT USING (created_by = auth.uid());

CREATE POLICY "Users can create custom templates" ON book_templates
    FOR INSERT WITH CHECK (created_by = auth.uid() AND is_system = false);

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

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