# LIB · 蛋蛋短剧生产管线 · 数据库模型
# D180 · 2026-07-10
import sqlite3
import json
import os
from datetime import datetime, timezone

DB_PATH = os.path.join(os.path.dirname(__file__), "lib.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS episodes (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id),
    number INTEGER NOT NULL,
    script_path TEXT,
    status TEXT DEFAULT 'pending',
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS shots (
    id TEXT PRIMARY KEY,
    episode_id TEXT NOT NULL REFERENCES episodes(id),
    shot_number TEXT NOT NULL,
    description TEXT,
    script_ref TEXT,
    camera TEXT,
    duration_sec REAL,
    status TEXT DEFAULT 'pending',
    prompt TEXT,
    model TEXT DEFAULT 'doubao-seedance-2-0-260128',
    resolution TEXT DEFAULT '720p',
    output_path TEXT,
    seedance_task_id TEXT,
    seed_value INTEGER DEFAULT 0,
    lock_seed INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    error_log TEXT
);

CREATE TABLE IF NOT EXISTS assets (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id),
    type TEXT NOT NULL CHECK(type IN ('CHAR','ENV','PROP','MASTER')),
    name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    approved_at TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS shot_assets (
    shot_id TEXT NOT NULL REFERENCES shots(id),
    asset_id TEXT NOT NULL REFERENCES assets(id),
    role TEXT NOT NULL CHECK(role IN ('reference','character','scene','prop','master')),
    PRIMARY KEY (shot_id, asset_id, role)
);

CREATE TABLE IF NOT EXISTS generation_tasks (
    id TEXT PRIMARY KEY,
    shot_id TEXT NOT NULL REFERENCES shots(id),
    task_type TEXT NOT NULL CHECK(task_type IN ('image','video','master_frame')),
    seedance_task_id TEXT,
    status TEXT DEFAULT 'submitted',
    prompt_used TEXT,
    references_used TEXT,
    error_message TEXT,
    cost_estimated REAL DEFAULT 0,
    cost_actual REAL DEFAULT 0,
    started_at TEXT,
    completed_at TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS experience_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    tags TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);
"""

def get_db():
    db = sqlite3.connect(DB_PATH)
    db.row_factory = sqlite3.Row
    db.executescript(SCHEMA)
    db.commit()
    return db

def init():
    db = get_db()
    # Create default project and episode if not exist
    db.execute("INSERT OR IGNORE INTO projects (id, name) VALUES (?, ?)",
               ("deep-sea-voyage", "深海迷航"))
    db.execute("""INSERT OR IGNORE INTO episodes (id, project_id, number, script_path, status)
                  VALUES (?, ?, ?, ?, ?)""",
               ("DSV-EP01", "deep-sea-voyage", 1,
                "projects/deep-sea-voyage/script/EP01-SCRIPT-LOCK.hdlp", "ready"))
    db.commit()
    print(f"LIB initialized: {DB_PATH}")
    return db

if __name__ == "__main__":
    init()