"""
Extract the Kenya national health facility registry (KMHFL / Master Facility List)
into etl/national_facilities.json.

Source workbook: data/src/kenya_health_facilities_ocha.xlsx
  "Kenya - Health facilities in Kenya" (OCHA ROSEA, derived from the Kenya
  Master Health Facility List). 8,932 operational facilities, all 47 counties,
  each with a unique MFL code, KEPH level, owner and administrative location.

Usage:  python etl/national_extract.py [path/to/facilities.xlsx]
"""
import json, os, re, sys
import pandas as pd

HERE = os.path.dirname(os.path.abspath(__file__))
ROOT = os.path.dirname(HERE)
DEFAULT = os.path.join(ROOT, "data", "src", "kenya_health_facilities_ocha.xlsx")
OUT = os.path.join(HERE, "national_facilities.json")


def s(v):
    if v is None:
        return None
    try:
        if pd.isna(v):
            return None
    except (TypeError, ValueError):
        pass
    t = str(v).strip()
    return t or None


def title(v):
    t = s(v)
    if not t:
        return None
    return re.sub(r"\s+", " ", t.title())


# canonical spellings used across the rest of the app (matches the facilities table)
COUNTY_CANON = {
    "elegeyo-marakwet": "Elgeyo Marakwet",
    "elegeyo marakwet": "Elgeyo Marakwet",
    "elgeyo marakwet": "Elgeyo Marakwet",
    "murang'a": "Murang'a",
    "muranga": "Murang'a",
    "tharaka-nithi": "Tharaka Nithi",
    "tharaka nithi": "Tharaka Nithi",
    "homabay": "Homa Bay",
    "homa bay": "Homa Bay",
    "transnzoia": "Trans Nzoia",
    "trans nzoia": "Trans Nzoia",
    "taita taveta": "Taita Taveta",
    "west pokot": "West Pokot",
    "tana river": "Tana River",
    "uasin gishu": "Uasin Gishu",
    "usin gishu": "Uasin Gishu",
}


def canon_county(v):
    t = title(v)
    if not t:
        return None
    return COUNTY_CANON.get(t.lower(), t)


def tier(v):
    t = s(v)
    if not t:
        return None
    m = re.search(r"[2-6]", t)
    return int(m.group(0)) if m else None


def integer(v):
    t = s(v)
    if not t:
        return None
    try:
        return int(float(t))
    except ValueError:
        return None


def norm_name(v):
    t = (s(v) or "").lower()
    t = re.sub(r"[^a-z0-9 ]+", " ", t)
    t = re.sub(r"\b(limited|ltd|company|co|centre|center|medical|hospital|clinic|nursing|home|health|care|services|service|and|the)\b", " ", t)
    return re.sub(r"\s+", " ", t).strip()


def main():
    path = sys.argv[1] if len(sys.argv) > 1 else DEFAULT
    if not os.path.exists(path):
        raise SystemExit(f"Source workbook not found: {path}")
    df = pd.read_excel(path, dtype=str)

    def col(*names):
        for want in names:
            for c in df.columns:
                if str(c).strip().lower() == want.lower():
                    return df[c]
        for want in names:
            for c in df.columns:
                if want.lower() in str(c).strip().lower():
                    return df[c]
        return pd.Series([None] * len(df))

    code = col("Code")
    name = col("Name")
    level = col("Keph level")
    rows, skipped = [], 0
    seen = set()
    for i in range(len(df)):
        nm = s(name.iloc[i])
        if not nm:
            skipped += 1
            continue
        c = s(code.iloc[i])
        if c and c in seen:
            skipped += 1
            continue
        if c:
            seen.add(c)
        rows.append({
            "code": c,
            "name": nm,
            "normalized_name": norm_name(nm),
            "keph_level": s(level.iloc[i]),
            "level_tier": tier(level.iloc[i]),
            "facility_type": s(col("Facility type").iloc[i]),
            "owner": s(col("Owner").iloc[i]),
            "county": canon_county(col("County").iloc[i]),
            "constituency": title(col("Constituency").iloc[i]),
            "subcounty": title(col("Sub county", "Subcounty").iloc[i]),
            "ward": title(col("Ward").iloc[i]),
            "operation_status": s(col("Operation status").iloc[i]),
            "beds": integer(col("Beds").iloc[i]),
            "cots": integer(col("Cots").iloc[i]),
            "services": s(col("Service_names", "Services").iloc[i]),
            "source": os.path.basename(path),
        })

    with open(OUT, "w", encoding="utf-8") as fh:
        json.dump(rows, fh, ensure_ascii=False)
    counties = sorted({r["county"] for r in rows if r["county"]})
    print(json.dumps({
        "facilities": len(rows), "skipped": skipped,
        "counties": len(counties),
        "with_code": sum(1 for r in rows if r["code"]),
        "levels": {str(t): sum(1 for r in rows if r["level_tier"] == t) for t in (2, 3, 4, 5, 6)},
        "out": OUT,
    }, indent=1))


if __name__ == "__main__":
    main()