import json, os
from datetime import datetime
import psycopg

CATALOG_PATH = os.environ.get("CATALOG_PATH","../data/sirep_catalog.json")
DATABASE_URL = os.environ.get("DATABASE_URL","postgresql://monark:monark@localhost:5432/monark")

def main():
    with open(CATALOG_PATH,"r",encoding="utf-8") as f:
        catalog=json.load(f)

    with psycopg.connect(DATABASE_URL) as conn:
        with conn.cursor() as cur:
            for p in catalog["projects"]:
                cur.execute("""
                    insert into projects(name, kind, domain, potential, projectness, time_spent, complexity, dispersion,
                                         convs, start_date, end_date, meta)
                    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    on conflict (name) do update set
                      kind=excluded.kind,
                      domain=excluded.domain,
                      potential=excluded.potential,
                      projectness=excluded.projectness,
                      time_spent=excluded.time_spent,
                      complexity=excluded.complexity,
                      dispersion=excluded.dispersion,
                      convs=excluded.convs,
                      start_date=excluded.start_date,
                      end_date=excluded.end_date,
                      meta=excluded.meta
                """, (
                    p["project"], p.get("kind"), p.get("domain"),
                    int(p.get("potential",0)), float(p.get("projectness",0)), float(p.get("time_spent",0)),
                    float(p.get("complexity",0)), float(p.get("dispersion",0)),
                    int(p.get("convs",0)), p.get("start_date"), p.get("end_date"),
                    json.dumps({"dominant_theme": p.get("dominant_theme"), "theme_variety": p.get("theme_variety")})
                ))

            for c in catalog["conversations"]:
                created_at = c.get("date")
                created_ts = datetime.fromisoformat(created_at).isoformat()+"Z" if created_at else None
                cur.execute("""
                    insert into conversations(id, title, created_at, source, chat_link, project_name, themes,
                                              msg_count, user_chars, assistant_chars, urls, files, code_blocks, meta)
                    values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    on conflict (id) do update set
                      title=excluded.title,
                      created_at=excluded.created_at,
                      source=excluded.source,
                      chat_link=excluded.chat_link,
                      project_name=excluded.project_name,
                      themes=excluded.themes,
                      msg_count=excluded.msg_count,
                      user_chars=excluded.user_chars,
                      assistant_chars=excluded.assistant_chars,
                      urls=excluded.urls,
                      files=excluded.files,
                      code_blocks=excluded.code_blocks,
                      meta=excluded.meta
                """, (
                    c["id"], c.get("title"), created_ts, c.get("source"), c.get("chat_link"),
                    c.get("proj_final"), c.get("themes",[]),
                    int(c.get("msg_count",0)), int(c.get("user_chars",0)), int(c.get("assistant_chars",0)),
                    int(c.get("urls",0)), int(c.get("files",0)), int(c.get("code_blocks",0)),
                    json.dumps({"proj_hits": c.get("proj_hits",[])})
                ))
        conn.commit()
    print("OK: cargado catálogo en Postgres")

if __name__=="__main__":
    main()