-- Video Automation Pipeline - PostgreSQL Schema
-- Version: 001
-- Description: Initial schema with BYTEA support for assets

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    mode VARCHAR(50) NOT NULL DEFAULT 'plan',
    platform VARCHAR(50) NOT NULL DEFAULT 'tiktok',
    character VARCHAR(255) NOT NULL DEFAULT '',
    story_preview TEXT NOT NULL DEFAULT '',
    title TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    result_json JSONB,
    production_plan_md TEXT
);

-- Images table with BYTEA for binary data
CREATE TABLE IF NOT EXISTS images (
    id SERIAL PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    scene_number INTEGER NOT NULL,
    image_prompt TEXT NOT NULL DEFAULT '',
    file_path VARCHAR(500) NOT NULL DEFAULT '',
    image_data BYTEA,
    mime_type VARCHAR(50) DEFAULT 'image/png',
    file_size_bytes INTEGER,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(project_id, scene_number)
);

-- Videos table with BYTEA for binary data
CREATE TABLE IF NOT EXISTS videos (
    id SERIAL PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    scene_number INTEGER NOT NULL,
    video_prompt TEXT NOT NULL DEFAULT '',
    file_path VARCHAR(500) NOT NULL DEFAULT '',
    video_data BYTEA,
    mime_type VARCHAR(50) DEFAULT 'video/mp4',
    file_size_bytes INTEGER,
    duration_seconds FLOAT,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(project_id, scene_number)
);

-- Audios table (NEW - for TTS)
CREATE TABLE IF NOT EXISTS audios (
    id SERIAL PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    scene_number INTEGER NOT NULL,
    tts_text TEXT NOT NULL DEFAULT '',
    tts_provider VARCHAR(50) DEFAULT 'chatterbox',
    file_path VARCHAR(500) NOT NULL DEFAULT '',
    audio_data BYTEA,
    mime_type VARCHAR(50) DEFAULT 'audio/wav',
    file_size_bytes INTEGER,
    duration_seconds FLOAT,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(project_id, scene_number)
);

-- Characters table
CREATE TABLE IF NOT EXISTS characters (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL UNIQUE,
    physical_description TEXT NOT NULL DEFAULT '',
    style_keywords JSONB NOT NULL DEFAULT '[]'::jsonb,
    negative_keywords JSONB NOT NULL DEFAULT '[]'::jsonb,
    hard_prohibitions JSONB NOT NULL DEFAULT '[]'::jsonb,
    camera_settings TEXT NOT NULL DEFAULT '',
    lighting_settings TEXT NOT NULL DEFAULT '',
    pose_rule TEXT NOT NULL DEFAULT '',
    environment_rule TEXT NOT NULL DEFAULT '',
    clothing_rule TEXT NOT NULL DEFAULT '',
    anatomical_highlight_rules TEXT NOT NULL DEFAULT '',
    consequence_philosophy TEXT NOT NULL DEFAULT '',
    shot_variety_rules TEXT NOT NULL DEFAULT '',
    thumbnail_path VARCHAR(500) NOT NULL DEFAULT '',
    thumbnail_data BYTEA,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_images_project_id ON images(project_id);
CREATE INDEX IF NOT EXISTS idx_videos_project_id ON videos(project_id);
CREATE INDEX IF NOT EXISTS idx_audios_project_id ON audios(project_id);
CREATE INDEX IF NOT EXISTS idx_projects_created_at ON projects(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_characters_name ON characters(name);
CREATE INDEX IF NOT EXISTS idx_characters_is_default ON characters(is_default);

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply trigger to tables with updated_at
DROP TRIGGER IF EXISTS update_images_updated_at ON images;
CREATE TRIGGER update_images_updated_at
    BEFORE UPDATE ON images
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_videos_updated_at ON videos;
CREATE TRIGGER update_videos_updated_at
    BEFORE UPDATE ON videos
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_audios_updated_at ON audios;
CREATE TRIGGER update_audios_updated_at
    BEFORE UPDATE ON audios
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_characters_updated_at ON characters;
CREATE TRIGGER update_characters_updated_at
    BEFORE UPDATE ON characters
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
