#!/usr/bin/env python3
"""Normalize NPCI ecosystem-statistics raw JSON into payments.duckdb tables.

Reads:  Projects/payments-stat-hub/raw/npci/eco/<tab>/<side>/<YYYY-MM>.json
Writes: eco_* tables in data/payments.duckdb
Idempotent: DELETE month range before insert.
"""
import json
import re
import sys
from datetime import date
from pathlib import Path

import duckdb

ROOT = Path("/home/workspace/Projects/payments-stat-hub")
RAW = ROOT / "raw/npci/eco"
DB = ROOT / "data/payments.duckdb"


def num(v):
    if v is None:
        return None
    s = re.sub(r"[,%]", "", str(v)).strip()
    if s in ("", "-", "NONE", "None", "NA", "N/A"):
        return None
    try:
        return float(s)
    except ValueError:
        return None


def month_of(path):
    m = re.match(r"^(\d{4})-(\d{2})\.json$", path.name)
    if not m:
        return None
    return date(int(m.group(1)), int(m.group(2)), 1)


def rows_of(path):
    try:
        d = json.loads(path.read_text())
    except Exception:
        return []
    r = (d.get("data") or {}).get("results")
    if isinstance(r, dict):
        r = r.get("tableDetail") or []
    if not isinstance(r, list):
        return []
    return [x for x in r if isinstance(x, dict)]


def load(tab):
    out = {}
    for f in sorted((RAW / tab).rglob("*.json")):
        side = f.parent.name if f.parent.name != tab else "_"
        m = month_of(f)
        if m:
            out[(m, side)] = rows_of(f)
    return out


def insert(con, table, cols, rows, keycol="month"):
    if not rows:
        return 0
    months = sorted({str(r["month"]) for r in rows})
    lo, hi = months[0], months[-1]
    con.execute(f"DELETE FROM {table} WHERE {keycol} BETWEEN ? AND ?", [lo, hi])
    con.executemany(
        f"INSERT INTO {table} VALUES ({','.join('?' * len(cols))})",
        [[r[c] for c in cols] for r in rows],
    )
    return len(rows)


def main():
    con = duckdb.connect(str(DB))

    con.execute("""CREATE TABLE IF NOT EXISTS eco_upi_apps (
        month DATE, app TEXT,
        cit_vol_mn REAL, cit_val_cr REAL, b2c_vol_mn REAL, b2c_val_cr REAL,
        b2b_vol_mn REAL, b2b_val_cr REAL, onus_vol_mn REAL, onus_val_cr REAL,
        total_vol_mn REAL, total_val_cr REAL)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_chargeback (
        month DATE, code TEXT, total_txns_mn REAL, cb_ratio REAL,
        cb_received INT, cb_represented INT, cb_accepted INT)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_top50_member (
        month DATE, side TEXT, bank TEXT, total_vol_mn REAL,
        approved_pct REAL, bd_pct REAL, td_pct REAL, debit_rev_mn REAL)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_psp (
        month DATE, side TEXT, psp TEXT, total_vol_mn REAL,
        approved_pct REAL, bd_pct REAL, td_pct REAL)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_mcc (
        month DATE, type TEXT, category TEXT, vol_mn REAL, vol_pct REAL,
        val_cr REAL, val_pct REAL, raw TEXT)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_p2p_p2m (
        month DATE, p2p_vol_mn REAL, p2p_val_cr REAL,
        p2m_vol_mn REAL, p2m_val_cr REAL, raw TEXT)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_statewise (
        month DATE, state TEXT, vol_mn REAL, vol_pct REAL,
        val_cr REAL, val_pct REAL, raw TEXT)""")
    con.execute("""CREATE TABLE IF NOT EXISTS eco_top50_volval (
        month DATE, bank TEXT, vol_mn REAL, val_cr REAL, is_total INT)""")

    counts = {}

    # upi-apps
    rows = []
    for (m, side), rs in load("upi-apps").items():
        for r in rs:
            if not r.get("application_name"):
                continue
            rows.append(dict(
                month=m, app=r["application_name"],
                cit_vol_mn=num(r.get("customer_initiated_transactions_volume_mn")),
                cit_val_cr=num(r.get("customer_initiated_transactions_value_cr")),
                b2c_vol_mn=num(r.get("b_2_c_transactions_volume_mn")),
                b2c_val_cr=num(r.get("b_2_c_transactions_value_cr")),
                b2b_vol_mn=num(r.get("b_2_b_transactions_volume_mn")),
                b2b_val_cr=num(r.get("b_2_b_transactions_value_cr")),
                onus_vol_mn=num(r.get("onus_transactions_volume_mn")),
                onus_val_cr=num(r.get("onus_transactions_value_cr")),
                total_vol_mn=num(r.get("total_volume_mn")),
                total_val_cr=num(r.get("total_value_cr"))))
    counts["eco_upi_apps"] = insert(con, "eco_upi_apps", list(rows[0].keys()) if rows else [], rows)

    # chargeback
    rows = []
    for (m, side), rs in load("chargeback").items():
        for r in rs:
            code = r.get("code") or r.get("bank_code")
            if code is None:
                continue
            rows.append(dict(
                month=m, code=str(code),
                total_txns_mn=num(r.get("total_txns_during_the_month")),
                cb_ratio=num(r.get("cb_ratio")),
                cb_received=int(num(r.get("chargebacks_received_during_the_month")) or 0),
                cb_represented=int(num(r.get("representment_raised_during_the_month")) or 0),
                cb_accepted=int(num(r.get("chargebacks_accepted_during_the_month")) or 0)))
    counts["eco_chargeback"] = insert(con, "eco_chargeback", list(rows[0].keys()) if rows else [], rows)

    # top50-member (remitter/beneficiary)
    rows = []
    for (m, side), rs in load("top50-member").items():
        if side not in ("remitter", "beneficiary"):
            continue
        for r in rs:
            bank = r.get("upi_remitter_banks") or r.get("upi_beneficiary_banks")
            if not bank:
                continue
            rows.append(dict(
                month=m, side=side, bank=bank,
                total_vol_mn=num(r.get("total_volume_in_mn")),
                approved_pct=num(r.get("approved_percent")),
                bd_pct=num(r.get("bd_percent")),
                td_pct=num(r.get("td_percent")),
                debit_rev_mn=num(r.get("total_debit_reversal_count_in_mn"))))
    counts["eco_top50_member"] = insert(con, "eco_top50_member", list(rows[0].keys()) if rows else [], rows)

    # top-15-psps (payer/payee)
    rows = []
    for (m, side), rs in load("top-15-psps").items():
        if side not in ("payer", "payee"):
            continue
        for r in rs:
            psp = r.get("payer_psp") or r.get("payee_psp") or r.get("upi_payer_psp")
            if not psp:
                continue
            rows.append(dict(
                month=m, side=side, psp=psp,
                total_vol_mn=num(r.get("total_volume_in_mn")),
                approved_pct=num(r.get("approved_percent")),
                bd_pct=num(r.get("bd_percent")),
                td_pct=num(r.get("td_percent"))))
    counts["eco_psp"] = insert(con, "eco_psp", list(rows[0].keys()) if rows else [], rows)

    # mcc (keep raw json as fallback)
    rows = []
    for (m, side), rs in load("mcc").items():
        for r in rs:
            if not any(v not in (None, "") for k, v in r.items() if k not in
                       ("id", "srno", "product_name", "tab_name", "type_name",
                        "year", "month", "created_at", "updated_at")):
                continue
            keys = {k.lower(): k for k in r}
            def g(*cands):
                for c in cands:
                    if c in keys:
                        return r[keys[c]]
                return None
            rows.append(dict(
                month=m, type=g("type"),
                category=g("mcc", "mcc_code", "category", "merchant_category"),
                vol_mn=num(g("volume_in_mn", "total_volume_mn", "volume_mn")),
                vol_pct=num(g("volume_contribution", "volume_percent")),
                val_cr=num(g("value_in_cr", "total_value_cr", "value_cr")),
                val_pct=num(g("value_contribution", "value_percent")),
                raw=json.dumps(r, ensure_ascii=False)))
    counts["eco_mcc"] = insert(con, "eco_mcc", list(rows[0].keys()) if rows else [], rows)

    # p2p-and-p2m
    rows = []
    for (m, side), rs in load("p2p-and-p2m-transactions").items():
        for r in rs:
            keys = {k.lower(): k for k in r}
            def g(*cands):
                for c in cands:
                    if c in keys:
                        return r[keys[c]]
                return None
            rows.append(dict(
                month=m,
                p2p_vol_mn=num(g("p_2_p_volume_mn", "p2p_volume_mn", "p2p_transaction_volume_mn")),
                p2p_val_cr=num(g("p_2_p_value_cr", "p2p_value_cr", "p2p_transaction_value_cr")),
                p2m_vol_mn=num(g("p_2_m_volume_mn", "p2m_volume_mn", "p2m_transaction_volume_mn")),
                p2m_val_cr=num(g("p_2_m_value_cr", "p2m_value_cr", "p2m_transaction_value_cr")),
                raw=json.dumps(r, ensure_ascii=False)))
    counts["eco_p2p_p2m"] = insert(con, "eco_p2p_p2m", list(rows[0].keys()) if rows else [], rows)

    # statewise
    rows = []
    for (m, side), rs in load("statewise-statistic").items():
        best = {}
        for r in rs:
            st = r.get("state_union_territory")
            if not st:
                continue
            ca = r.get("created_at") or ""
            if st not in best or ca >= (best[st].get("created_at") or ""):
                best[st] = r
        for st, r in best.items():
            rows.append(dict(
                month=m, state=st,
                vol_mn=num(r.get("volume_in_mn")),
                vol_pct=num(r.get("volume_contribution")),
                val_cr=num(r.get("value_in_cr")),
                val_pct=num(r.get("value_contribution")),
                raw=json.dumps(r, ensure_ascii=False)))
    counts["eco_statewise"] = insert(con, "eco_statewise", list(rows[0].keys()) if rows else [], rows)

    # top-50-mem-vol-val
    rows = []
    for (m, side), rs in load("top-50-mem-vol-val").items():
        for r in rs:
            bank = r.get("bank_name") or r.get("member_name")
            if not bank:
                continue
            rows.append(dict(
                month=m, bank=bank,
                vol_mn=num(r.get("volume_in_mn") or r.get("total_volume_mn")),
                val_cr=num(r.get("value_in_cr") or r.get("total_value_cr")),
                is_total=1 if str(r.get("is_total", "")).lower() in ("1", "true", "total") else 0))
    counts["eco_top50_volval"] = insert(con, "eco_top50_volval", list(rows[0].keys()) if rows else [], rows)

    for t, n in sorted(counts.items()):
        print(f"{t}: {n}")
    con.close()


if __name__ == "__main__":
    sys.exit(main())