-- Database Schema for AI Video Production Pipeline (MySQL)
-- 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 INT AUTO_INCREMENT PRIMARY KEY,
    project_id VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    overall_mood VARCHAR(255),
    script_json JSON,
    status VARCHAR(50) DEFAULT 'in_progress',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to store individual scene images and their processing status
CREATE TABLE IF NOT EXISTS scene_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id VARCHAR(100) NOT NULL,
    scene_number INT 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 ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_project_scene (project_id, scene_number),
    FOREIGN KEY (project_id) REFERENCES video_projects(project_id) ON DELETE CASCADE
);

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

-- 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,
    SUM(CASE WHEN si.status = 'completed' THEN 1 ELSE 0 END) AS completed_scenes,
    SUM(CASE WHEN si.motion_video_url IS NOT NULL THEN 1 ELSE 0 END) AS scenes_with_motion,
    SUM(CASE WHEN si.audio_file IS NOT NULL THEN 1 ELSE 0 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;
