#!/usr/bin/env python3
"""3회차(GPT)/4회차(Gemini) 텍스트 파이프라인 산출물 비교 데이터 추출 (실험 전용, 커밋 금지)"""
import json, os
import psycopg2

GPT_PID = "7872eda9-8b02-4cb3-bfd1-04ae1d43acac"
GEM_PID = "8207aadc-7975-48f5-af17-ccc145a6660d"
OUT = os.path.join(os.path.dirname(__file__), "compare_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()

data = {"runs": {"gpt": GPT_PID, "gemini": GEM_PID}}

# 1) step_run 모델 매핑
data["steps"] = {}
for key, pid in (("gpt", GPT_PID), ("gemini", GEM_PID)):
    for sid, model, status, cc, fc, pv, sa, ca in rows(
        "SELECT step_id, resolved_model, status, completed_count, failed_count, prompt_version, started_at, completed_at FROM step_run WHERE project_id=%s", (pid,)):
        d = data["steps"].setdefault(sid, {})
        d[key] = {"model": model, "status": status, "completed": cc, "failed": fc, "pv": pv}

# 2) 엔티티 전체
data["entities"] = {}
for key, pid in (("gpt", GPT_PID), ("gemini", GEM_PID)):
    ents = []
    for sid, et, name, desc, st in rows(
        "SELECT short_id, entity_type, name, description, status FROM entity_canon WHERE project_id=%s ORDER BY entity_type, short_id", (pid,)):
        ents.append({"id": sid, "type": et, "name": name, "desc": (desc or "")[:400], "status": st})
    data["entities"][key] = ents

# 3) 씬별 샷 통계 + 선택 샷 상세(설명, VE, 샷타입, t2i 여부)
data["shots"] = {}
data["selected"] = {}
for key, pid in (("gpt", GPT_PID), ("gemini", GEM_PID)):
    stats = {}
    sel = []
    for si, shi, desc, bt, ve_json, issel, st1, st2, stype, ssum in rows(
        """SELECT scene_index, shot_index, shot_description, beat_title, visible_entities_json,
                  is_selected, shot_type_1, shot_type_2, scene_type, scene_summary
           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, ve_all = [], []
        try:
            ve = json.loads(ve_json) if ve_json else []
            for e in ve:
                sid_ = e.get("short_id") or ""
                ve_all.append(sid_)
                if sid_.startswith("C"):
                    ve_chars.append(sid_)
        except Exception:
            pass
        if issel:
            s["selected"] += 1
            sel.append({"scene": si, "shot": shi, "desc": (desc or "")[:300], "beat": bt,
                        "ve_chars": ve_chars, "ve_all": ve_all, "shot_types": [st1, st2],
                        "scene_type": stype})
    data["shots"][key] = stats
    data["selected"][key] = sel

# 4) VE 캐릭터 빈 선택샷
data["ve_empty"] = {k: [f"S{s['scene']}sh{s['shot']}" for s in v if not s["ve_chars"]]
                    for k, v in data["selected"].items()}

# 5) 씬별 등장 캐릭터 합집합 (scene_director 배정 근사 — VE는 director 확정 데이터에서 자동 구축)
data["scene_chars"] = {}
for key in ("gpt", "gemini"):
    agg = {}
    for s in data["selected"][key]:
        agg.setdefault(s["scene"], set()).update(s["ve_chars"])
    data["scene_chars"][key] = {str(k): sorted(v) for k, v in sorted(agg.items())}

# 6) 아웃룩 상세 (character_outlook 매핑)
data["outlooks"] = {}
for key, pid in (("gpt", GPT_PID), ("gemini", GEM_PID)):
    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,))
    ]

# 7) episode summary
data["episode_summary"] = {}
for key, pid in (("gpt", GPT_PID), ("gemini", GEM_PID)):
    r = rows("SELECT summary FROM episode WHERE project_id=%s LIMIT 1", (pid,))
    data["episode_summary"][key] = (r[0][0] or "") if r else ""

with open(OUT, "w") as f:
    json.dump(data, f, ensure_ascii=False, indent=1, default=list)
print("written", OUT)
print("ve_empty:", data["ve_empty"])
print("selected counts:", {k: len(v) for k, v in data["selected"].items()})