#!/usr/bin/env python3
"""Flatten MobiKwik billerConfig JSONs into normalized tables (CSV + DuckDB)."""
import csv, json, sys
from pathlib import Path

RAW = Path("/home/workspace/Projects/payments-stat-hub/raw/bbps")
OUT = RAW / "normalized"
OUT.mkdir(exist_ok=True)

def flatten(version: str):
    d = json.loads((RAW / "catalogs" / f"mobikwik-billerConfig-{version}.json").read_text())
    billers, fields = [], []
    for cat in d.get("category", []):
        cid, cname = cat.get("categoryId"), cat.get("name")
        for grp in cat.get("billerRoot", []):
            gname = grp.get("name")
            for b in grp.get("billers", []):
                op = b.get("op")
                billers.append({
                    "version": version, "category_id": cid, "category_name": cname,
                    "biller_group": gname, "op": op,
                    "billertype": b.get("type", ""),
                    "is_prepaid": b.get("isPrepaid", ""),
                    "is_amount_required": b.get("isAmountRequired", ""),
                    "has_autorecharge": b.get("hasAutoRecharge", ""),
                    "seo_alias": b.get("seoAlias", ""),
                    "field_count": len(b.get("fields", [])),
                })
                for f in b.get("fields", []):
                    fields.append({
                        "version": version, "category_id": cid, "biller_group": gname, "op": op,
                        "field_id": f.get("id", ""), "field_name": f.get("name", ""),
                        "field_type": f.get("type", ""), "isnumeric": f.get("isnumeric", ""),
                        "regex": f.get("regex", ""), "min_len": f.get("minLen", ""),
                        "max_len": f.get("maxLen", ""), "min": f.get("min", ""), "max": f.get("max", ""),
                    })
    return billers, fields

all_b, all_f = [], []
for v in ("v3", "v5", "v4"):
    b, f = flatten(v)
    all_b += b
    all_f += f
    print(f"{v}: {len(b)} billers, {len(f)} fields")

for name, rows in (("billers", all_b), ("fields", all_f)):
    with open(OUT / f"mobikwik-{name}.csv", "w", newline="") as fh:
        w = csv.DictWriter(fh, fieldnames=list(rows[0].keys()))
        w.writeheader()
        w.writerows(rows)
    print(f"wrote mobikwik-{name}.csv: {len(rows)} rows")

# DuckDB
import duckdb
con = duckdb.connect(str(OUT / "bbps.duckdb"))
con.execute("CREATE OR REPLACE TABLE billers AS SELECT * FROM read_csv_auto(?)", [str(OUT / "mobikwik-billers.csv")])
con.execute("CREATE OR REPLACE TABLE fields AS SELECT * FROM read_csv_auto(?)", [str(OUT / "mobikwik-fields.csv")])
q = con.execute("""
  SELECT version, count(*) billers, count(DISTINCT op) ops, count(DISTINCT biller_group) n_groups
  FROM billers GROUP BY version ORDER BY version
""").fetchall()
for row in q:
    print("duckdb:", row)
q2 = con.execute("""
  SELECT category_id, count(*) n FROM billers WHERE version='v4'
  GROUP BY category_id ORDER BY n DESC LIMIT 8
""").fetchall()
print("v4 top categories:", q2)
con.close()
print("OK:", OUT / "bbps.duckdb")