#!/usr/bin/env python3
"""4개 회차(GPT5.5 혼합 x2, GPT5.6 전면, Gemini 원복) 텍스트 파이프라인 전수 추출 (실험 전용, 커밋 금지)"""
import json, os
from datetime import datetime
import psycopg2

RUNS = [
    ("v16",  "1b4a975b-f4e5-46d7-8b28-876071d19907"),  # v16 정정검증 07-08, GPT-5.5 혼합
    ("r1",   "da87ee4a-b20b-45ab-ba40-f30d1166f6bd"),  # W22 1회차 07-10, GPT-5.5 혼합
    ("gpt",  "7872eda9-8b02-4cb3-bfd1-04ae1d43acac"),  # 3회차, GPT-5.6 전면
    ("gemini","8207aadc-7975-48f5-af17-ccc145a6660d"), # 4회차, Gemini 원복
]
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "compare4_data.json")

conn = psycopg2.connect(host="localhost", user="theroad", password="theroad_dev_2026", dbname="theroad")
cur = conn.cursor()
def rows(sql, params=()):
    cur.execute(sql, params); return cur.fetchall()
def pt(t):
    try: return datetime.fromisoformat(t.replace("Z", "+00:00"))
    except Exception: return None

data = {"runs": dict(RUNS), "steps": {}, "entities": {}, "shots": {}, "selected": {},
        "ve_empty": {}, "scene_chars": {}, "outlooks": {}, "episode_summary": {},
        "beats": {}, "durations": {}, "scene_count": {}}

for key, pid in RUNS:
    # steps + durations
    for sid, model, status, cc, fc, sa, ca in rows(
        "SELECT step_id, resolved_model, status, completed_count, failed_count, started_at, completed_at FROM step_run WHERE project_id=%s", (pid,)):
        data["steps"].setdefault(sid, {})[key] = {"model": model, "status": status, "completed": cc, "failed": fc}
        if sa and ca:
            a, b = pt(sa), pt(ca)
            if a and b:
                data["durations"].setdefault(sid, {})[key] = round((b - a).total_seconds())
    # entities
    data["entities"][key] = [
        {"id": sid, "type": et, "name": name, "desc": (desc or "")[:400]}
        for sid, et, name, desc in rows(
            "SELECT short_id, entity_type, name, description FROM entity_canon WHERE project_id=%s AND status='active' ORDER BY entity_type, short_id", (pid,))]
    # shots + selected + VE
    stats, sel = {}, []
    for si, shi, desc, ve_json, issel, stype in rows(
        """SELECT scene_index, shot_index, shot_description, visible_entities_json, is_selected, scene_type
           FROM scene_still WHERE project_id=%s ORDER BY scene_index, shot_index""", (pid,)):
        s = stats.setdefault(si, {"total": 0, "selected": 0})
        s["total"] += 1
        ve_chars = []
        try:
            for e in (json.loads(ve_json) if ve_json else []):
                if (e.get("short_id") or "").startswith("C"):
                    ve_chars.append(e["short_id"])
        except Exception:
            pass
        if issel:
            s["selected"] += 1
            sel.append({"scene": si, "shot": shi, "desc": (desc or "")[:300], "ve_chars": ve_chars, "scene_type": stype})
    data["shots"][key] = {str(k): v for k, v in stats.items()}
    data["selected"][key] = sel
    data["ve_empty"][key] = [f"S{s['scene']}sh{s['shot']}" for s in sel if not s["ve_chars"]]
    agg = {}
    for s in sel:
        agg.setdefault(s["scene"], set()).update(s["ve_chars"])
    data["scene_chars"][key] = {str(k): sorted(v) for k, v in sorted(agg.items())}
    # beats
    data["beats"][key] = sum(b for _, b in rows(
        "SELECT scene_index, COUNT(DISTINCT based_on_beat) FROM scene_still WHERE project_id=%s GROUP BY scene_index", (pid,)))
    # outlooks
    data["outlooks"][key] = [
        {"char": c, "outlook": o} for c, o in rows(
            """SELECT ch.name, ol.name FROM character_outlook co
               JOIN entity_canon ch ON ch.id=co.character_id
               JOIN entity_canon ol ON ol.id=co.outlook_id
               WHERE co.project_id=%s ORDER BY ch.short_id, ol.short_id""", (pid,))]
    # episode summary
    r = rows("SELECT summary FROM episode WHERE project_id=%s LIMIT 1", (pid,))
    data["episode_summary"][key] = (r[0][0] or "") if r else ""
    data["scene_count"][key] = len(stats)

json.dump(data, open(OUT, "w"), ensure_ascii=False, indent=1)
for key, _ in RUNS:
    ents = data["entities"][key]
    c = {t: sum(1 for e in ents if e["type"] == t) for t in ("character", "location", "prop", "outlook")}
    print(key, "| scenes", data["scene_count"][key], "| ents", c, "| beats", data["beats"][key],
          "| shots", sum(v["total"] for v in data["shots"][key].values()),
          "| sel", len(data["selected"][key]), "| ve_empty", len(data["ve_empty"][key]))