-- Database Schema for AI Video Production Pipeline
-- Run this SQL to create the required tables before using the workflow

-- Table to store video project metadata
CREATE TABLE IF NOT EXISTS video_projects (
    id SERIAL PRIMARY KEY,
    project_id VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    overall_mood VARCHAR(255),
    script_json JSONB,
    status VARCHAR(50) DEFAULT 'in_progress',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to store individual scene images and their processing status
CREATE TABLE IF NOT EXISTS scene_images (
    id SERIAL PRIMARY KEY,
    project_id VARCHAR(100) NOT NULL REFERENCES video_projects(project_id),
    scene_number INTEGER NOT NULL,
    scene_title VARCHAR(255),
    mood VARCHAR(255),
    visual_effects TEXT,
    narration TEXT,
    image_prompt TEXT,
    image_url TEXT,
    image_id VARCHAR(255),
    motion_video_url TEXT,
    audio_file VARCHAR(500),
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(project_id, scene_number)
);

-- Index for faster queries
CREATE INDEX IF NOT EXISTS idx_scene_images_project_id ON scene_images(project_id);
CREATE INDEX IF NOT EXISTS idx_scene_images_status ON scene_images(status);
CREATE INDEX IF NOT EXISTS idx_video_projects_status ON video_projects(status);

-- Trigger to auto-update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_video_projects_timestamp
    BEFORE UPDATE ON video_projects
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_scene_images_timestamp
    BEFORE UPDATE ON scene_images
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- View to get complete project status
CREATE OR REPLACE VIEW project_status AS
SELECT
    vp.project_id,
    vp.title,
    vp.overall_mood,
    vp.status AS project_status,
    COUNT(si.id) AS total_scenes,
    COUNT(CASE WHEN si.status = 'completed' THEN 1 END) AS completed_scenes,
    COUNT(CASE WHEN si.motion_video_url IS NOT NULL THEN 1 END) AS scenes_with_motion,
    COUNT(CASE WHEN si.audio_file IS NOT NULL THEN 1 END) AS scenes_with_audio,
    vp.created_at
FROM video_projects vp
LEFT JOIN scene_images si ON vp.project_id = si.project_id
GROUP BY vp.project_id, vp.title, vp.overall_mood, vp.status, vp.created_at;
