#!/usr/bin/env python3
"""
Extract the shodh-forms registries from the production DB dump into an
import file for POST /admin/registry/import.

Reads:  legacy-import/backup/sho22815_shodhnet.sql.gz
Writes: legacy-import/registry-import.json   (NOT committed — parent dir has
        no ignore rule for it, so it is written under backup/ which is ignored)

Visibility mirrors the legacy approval flag: sstatus/status == "Yes"/"YES".
"""

import gzip
import json
import re
from pathlib import Path

REPO_ROOT = Path(__file__).resolve().parent.parent
DUMP = REPO_ROOT / "legacy-import" / "backup" / "sho22815_shodhnet.sql.gz"
OUT = REPO_ROOT / "legacy-import" / "backup" / "registry-import.json"


def parse_tuples(sql: str, table: str):
    for m in re.finditer(r"INSERT INTO `%s`[^;]*?VALUES\s*" % re.escape(table), sql):
        i = m.end()
        while i < len(sql) and sql[i] == "(":
            values, buf, in_str, esc = [], [], False, False
            i += 1
            while i < len(sql):
                c = sql[i]
                if esc:
                    buf.append({"n": "\n", "t": "\t", "r": "\r", "0": "\0"}.get(c, c))
                    esc = False
                elif in_str:
                    if c == "\\":
                        esc = True
                    elif c == "'":
                        in_str = False
                    else:
                        buf.append(c)
                elif c == "'":
                    in_str = True
                elif c == ",":
                    values.append("".join(buf)); buf = []
                elif c == ")":
                    values.append("".join(buf))
                    yield values
                    i += 1
                    break
                else:
                    buf.append(c)
                i += 1
            while i < len(sql) and sql[i] in ", \n\r\t":
                i += 1


def norm(v):
    v = (v or "").strip()
    return None if v in ("", "NULL", "null", "N/A", "-") else v


def norm_date(v):
    v = norm(v)
    if v and re.match(r"^\d{4}-\d{2}-\d{2}", v) and not v.startswith("0000"):
        return v[:10]
    return None


def main():
    sql = gzip.open(DUMP, "rt", encoding="utf-8", errors="replace").read()
    entries = []

    # php_supervisor_register_list:
    # 0 id, 1 sname, 2 sdesignation, 3 saddress, 4 semail, 5 sphone, 6 smobile,
    # 7 sinterest, 8 ssubject, 9 suniversity, 10 sstatus, 11 date_of_registration
    for v in parse_tuples(sql, "php_supervisor_register_list"):
        entries.append({
            "type": "SUPERVISOR", "legacyId": int(v[0]),
            "name": norm(v[1]), "designation": norm(v[2]), "address": norm(v[3]),
            "email": norm(v[4]), "phone": norm(v[5]), "mobile": norm(v[6]),
            "interest": norm(v[7]), "subject": norm(v[8]), "university": norm(v[9]),
            "visible": (norm(v[10]) or "").lower() == "yes",
            "registeredAt": norm_date(v[11]),
        })

    # php_doctoral_research / php_postdoctoral_research:
    # 0 id, 1 researcher, 2 department, 3 medium, 4 topic, 5 work, 6 supervisor,
    # 7 university, 8 yearr, 9 rdesignation, 10 raddress, 11 remail, 12 rphone,
    # 13 rmobile, 14 saddress, 15 scontact, 16 semail, 17 funding, 18 evidence,
    # 19 status, 20 date_of_registration
    for table, rtype in [("php_doctoral_research", "DOCTORAL"),
                         ("php_postdoctoral_research", "POSTDOCTORAL")]:
        for v in parse_tuples(sql, table):
            entries.append({
                "type": rtype, "legacyId": int(v[0]),
                "name": norm(v[1]), "department": norm(v[2]), "medium": norm(v[3]),
                "topic": norm(v[4]), "workStatus": norm(v[5]), "supervisor": norm(v[6]),
                "university": norm(v[7]), "researchYear": norm(v[8]),
                "designation": norm(v[9]), "address": norm(v[10]),
                "email": norm(v[11]), "phone": norm(v[12]), "mobile": norm(v[13]),
                "funding": norm(v[17]),
                "visible": (norm(v[19]) or "").lower() == "yes",
                "registeredAt": norm_date(v[20]),
            })

    OUT.write_text(json.dumps({"entries": entries}, ensure_ascii=False, indent=1),
                   encoding="utf-8")
    by_type = {}
    vis = {}
    for e in entries:
        by_type[e["type"]] = by_type.get(e["type"], 0) + 1
        if e["visible"]:
            vis[e["type"]] = vis.get(e["type"], 0) + 1
    print("extracted:", by_type)
    print("visible (legacy-approved):", vis)
    print("->", OUT)


if __name__ == "__main__":
    main()