# Copyright (c) 2026 Mohammed Mahraj ul Alam Samrat,
# Head of Trade Facilitation & Regulatory Compliance, TRACE Consulting Ltd.
# All rights reserved. BETA release.
# See LICENSE in the repository root.
"""Seed loader: db/seed/*.json  ->  PostgreSQL (schema.sql must be applied first).

Usage:  DATABASE_URL=postgresql://tariff:tariff@localhost:5432/tariff python load_data.py ../db/seed
"""
import json, os, sys
import psycopg

SEED = sys.argv[1] if len(sys.argv) > 1 else "../db/seed"
DB = os.environ.get("DATABASE_URL", "postgresql://tariff:tariff@localhost:5432/tariff")

def j(name):
    with open(os.path.join(SEED, name), encoding="utf-8") as f:
        return json.load(f)

def main():
    tariff = j("tariff_data.json"); ipo = j("ipo_pack.json")
    sro = j("sro_pack.json"); en = j("en_proof.json"); ref = j("ref_data.json")
    with psycopg.connect(DB) as c, c.cursor() as cur:
        # fiscal years
        years = tariff["meta"]["years"]; current = tariff["meta"]["current"]
        for fy in years:
            cur.execute("INSERT INTO fiscal_year(fy,is_current) VALUES(%s,%s) ON CONFLICT (fy) DO UPDATE SET is_current=EXCLUDED.is_current",
                        (fy, fy == current))
        # tariff lines + rates + stats
        n = 0
        for code, rec in tariff["hs"].items():
            cur.execute("""INSERT INTO tariff_line(code,heading,chapter,description,unit,saf,is_specific)
                           VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (code) DO NOTHING""",
                        (code, code[:4], code[:2], rec.get("d", ""), rec.get("u"),
                         rec.get("saf"), bool(rec.get("t") and rec["t"].get(current) is not None)))
            for fy, r in (rec.get("r") or {}).items():
                cur.execute("""INSERT INTO tariff_rate(code,fy,cd,rd,sd,vat,ait,at)
                               VALUES(%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING""",
                            (code, fy, *[(r[i] or 0) for i in range(6)]))
            if rec.get("im_av") is not None:
                cur.execute("""INSERT INTO import_stat(code,fy,import_value_cr,revenue_cr,published_tti)
                               VALUES(%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING""",
                            (code, current, rec.get("im_av"), rec.get("im_rev"), rec.get("im_tti")))
            n += 1
        print("tariff lines:", n)
        # SRO register (defaults-compacted -> expand)
        d = sro.get("defaults", {})
        for r in sro["records"]:
            rec = {**d, **r}
            cur.execute("""INSERT INTO sro(sro_key,number,year,title,category,status,amends,source_url,confidence,raw)
                           VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (sro_key) DO NOTHING""",
                        (rec.get("key") or f"{rec.get('no')}/{rec.get('yr')}", str(rec.get("no")), rec.get("yr"),
                         rec.get("title"), rec.get("cat"), rec.get("status"), rec.get("amends"),
                         rec.get("url"), rec.get("conf"), json.dumps(rec)))
        print("sro records:", len(sro["records"]))
        # IPO provisions
        for e in ipo["entries"]:
            cur.execute("""INSERT INTO ipo_provision(lvl,k,scope,status,condition,ipo_ref,remarks,docs,auth,kws,inc,exc)
                           VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                        (e["lvl"], e.get("k", ""), e["scope"], e["st"], e.get("cond"), e["ref"], e.get("rem"),
                         json.dumps(e.get("docs", [])), json.dumps(e.get("auth", [])),
                         json.dumps(e.get("kws", [])), json.dumps(e.get("inc", [])), json.dumps(e.get("exc", []))))
        print("ipo provisions:", len(ipo["entries"]))
        # Explanatory Notes (licensed — see README §5)
        for h, body in en["hd"].items():
            cur.execute("INSERT INTO en_heading(heading,body) VALUES(%s,%s) ON CONFLICT (heading) DO UPDATE SET body=EXCLUDED.body", (h, body))
        for ch, body in en["ch"].items():
            cur.execute("INSERT INTO en_chapter(chapter,body) VALUES(%s,%s) ON CONFLICT (chapter) DO UPDATE SET body=EXCLUDED.body", (ch, body))
        print("EN headings:", len(en["hd"]), "| chapters:", len(en["ch"]))
        # concession layer from REF_DATA.conc -> sro_hs (linked to a synthetic register row when unmatched)
        conc = ref.get("conc", [])
        cur.execute("""INSERT INTO sro(sro_key,number,title,status,raw) VALUES('CONC/EMBED','-',
                       'Embedded HS-wise concession layer (HS Code-wise SRO Reference.xlsx)','embedded','{}')
                       ON CONFLICT (sro_key) DO NOTHING""")
        cur.execute("SELECT id FROM sro WHERE sro_key='CONC/EMBED'")
        cid = cur.fetchone()[0]
        for row in conc:
            code = "".join(ch for ch in str(row[0]) if ch.isdigit())
            cur.execute("""INSERT INTO sro_hs(sro_id,hs_code,ccd,csd,conditions)
                           VALUES(%s,%s,%s,%s,%s) ON CONFLICT DO NOTHING""",
                        (cid, code, row[3] or 0, row[4] or 0, f"{row[2] or ''} — {row[1] or ''}"))
        print("concession lines:", len(conc))
        c.commit()

if __name__ == "__main__":
    main()