-- Image generation system for DALL-E 3 book covers and other generated images
-- Create generated_images table to store user's generated images
CREATE TABLE generated_images (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
    book_id UUID REFERENCES books(id) ON DELETE SET NULL, -- Optional association with a book
    
    -- Image details
    prompt TEXT NOT NULL,
    revised_prompt TEXT, -- DALL-E 3 may revise prompts
    image_url TEXT NOT NULL, -- URL to the generated image
    
    -- Generation settings
    model TEXT NOT NULL DEFAULT 'dall-e-3',
    size TEXT NOT NULL DEFAULT '1024x1024' CHECK (size IN ('1024x1024', '1792x1024', '1024x1792')),
    quality TEXT NOT NULL DEFAULT 'standard' CHECK (quality IN ('standard', 'hd')),
    style TEXT NOT NULL DEFAULT 'vivid' CHECK (style IN ('natural', 'vivid')),
    
    -- Usage tracking
    cost_usd DECIMAL(10, 6) DEFAULT 0 CHECK (cost_usd >= 0),
    
    -- Metadata
    metadata JSONB DEFAULT '{}'::jsonb,
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_generated_images_user_id ON generated_images(user_id);
CREATE INDEX idx_generated_images_book_id ON generated_images(book_id);
CREATE INDEX idx_generated_images_created_at ON generated_images(created_at DESC);
CREATE INDEX idx_generated_images_model ON generated_images(model);

-- Create trigger for updated_at
CREATE TRIGGER update_generated_images_updated_at 
    BEFORE UPDATE ON generated_images 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Row Level Security
ALTER TABLE generated_images ENABLE ROW LEVEL SECURITY;

-- Policies for generated_images
CREATE POLICY "Users can view their own generated images" ON generated_images
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own generated images" ON generated_images
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own generated images" ON generated_images
    FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own generated images" ON generated_images
    FOR DELETE USING (auth.uid() = user_id);

-- Add realtime subscription
ALTER PUBLICATION supabase_realtime ADD TABLE generated_images;

-- Add comment for documentation
COMMENT ON TABLE generated_images IS 'Stores AI-generated images from DALL-E 3 for book covers and other creative content';