"""
Extract the Afya source spreadsheets into one clean JSON payload that
etl/import.mjs loads into SQLite.

Sources
  Afya_Legacy_Sheet_Updated_4Oct(2).xlsx  -> canonical facility pipeline (301)
  MASTER LIST 1Combined_Counties.xlsx     -> who sold / who deployed
  <rep>.xlsx                              -> closed deals, amounts paid, receipts
  brian.xlsx                              -> open leads
  Revenue.xlsx                            -> Navision/Business Central invoices

Output: etl/extracted.json
"""
import json, os, re, sys
import pandas as pd

ROOT = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "extracted.json")

YES = {"yes", "y", "true", "1"}


def s(v):
    if v is None:
        return None
    if isinstance(v, float) and pd.isna(v):
        return None
    t = str(v).strip()
    if t == "" or t.lower() in ("nan", "none", "nat", "-", "n/a", "#n/a"):
        return None
    return t


def norm(v):
    t = s(v)
    return t.lower() if t else None


def money(v):
    t = s(v)
    if t is None:
        return None
    t = re.sub(r"[^0-9.\-]", "", t.replace(",", ""))
    try:
        f = float(t)
        return f if f != 0 else None
    except ValueError:
        return None


def yn(v):
    t = norm(v)
    if t is None:
        return None
    if t in YES:
        return True
    if t in ("no", "n", "false", "0", "not yet", "notyet", "not yet received"):
        return False
    return None


def iso_date(v):
    """Best-effort date normalisation. Handles '2026-08-26 00:00:00', '27/08/2026',
    '16/09/2026', 'SEP/20/2026' and the odd '14/009/2026' typo."""
    t = s(v)
    if not t:
        return None
    # collapse a zero-padded 3-digit month, e.g. 14/009/2026
    t = re.sub(r"/(0+)(\d)/", r"/\2/", t)
    # ISO first, otherwise dayfirst would read 2026-11-09 as 11 September
    iso = re.match(r"^(\d{4})-(\d{2})-(\d{2})", t)
    if iso:
        return f"{iso.group(1)}-{iso.group(2)}-{iso.group(3)}"
    for kwargs in ({"dayfirst": True}, {"dayfirst": True, "format": "mixed"}, {}):
        try:
            d = pd.to_datetime(t, errors="raise", **kwargs)
            return d.strftime("%Y-%m-%d")
        except Exception:
            continue
    return None


def person(tok):
    """'Ernest - 0712565636' -> ('Ernest','0712565636')"""
    t = s(tok)
    if not t:
        return None, None
    t = t.replace("\u2013", "-").replace("\u2014", "-")
    m = re.split(r"\s*[-/]\s*", t, maxsplit=1)
    name = m[0].strip(" .:-")
    phone = None
    rest = t[len(name):]
    digits = re.sub(r"[^0-9]", "", rest)
    if len(digits) >= 9:
        phone = digits
    if not name or name.lower() in ("office", "n/a", "-", "yet to be deployed", "none"):
        return None, None
    return name.title() if name.isupper() else name, phone


def split_people(tok):
    t = s(tok)
    if not t:
        return []
    parts = re.split(r"[/,;]|\band\b", t)
    out = []
    for p in parts:
        n, ph = person(p)
        if n:
            out.append({"name": n, "phone": ph})
    return out


COUNTY_FIX = {
    "usin gishu": "Uasin Gishu", "uasin gishu": "Uasin Gishu", "uasingishu": "Uasin Gishu",
    "homabay": "Homa Bay", "homa bay": "Homa Bay",
    "trans nzoia": "Trans Nzoia", "transnzoia": "Trans Nzoia",
    "elgeyo marakwet": "Elgeyo Marakwet", "murang'a": "Murang'a", "muranga": "Murang'a",
    "tharaka-nithi": "Tharaka Nithi", "taita taveta": "Taita Taveta",
    "west pokot": "West Pokot", "tana river": "Tana River",
}


def county(v):
    t = s(v)
    if not t:
        return None
    return COUNTY_FIX.get(t.lower(), t.title())


def norm_name(x):
    t = (s(x) 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()


# The product being subscribed to is the CollabMed Hospital Management Information
# System ("CollabMed HMIS"), so the Business Central lines are sorted into recurring
# subscription income versus one-off hardware/setup work.
SUBSCRIPTION_ITEMS = {"2104", "4003"}
HARDWARE_ITEMS = {"4004", "4006"}


def line_category(item_no, description):
    item = (s(item_no) or "").strip()
    d = (s(description) or "").lower()
    if item in SUBSCRIPTION_ITEMS:
        return "subscription"
    if item in HARDWARE_ITEMS:
        return "hardware"
    if "deferred revenue" in d:
        return "deferred"
    if "correction" in d or "credit note" in d:
        return "adjustment"
    for word in ("subscription", "monthly support", "technical support", "management and",
                 "management &"):
        if word in d:
            return "subscription"
    for word in ("tablet", "printer", "desktop", "laptop", " hp ", "setup", "installation",
                 "router", "server"):
        if word in d:
            return "hardware"
    return "other"


def read(file, sheet, header=0):
    p = os.path.join(ROOT, file)
    if not os.path.exists(p):
        return None
    return pd.read_excel(p, sheet_name=sheet, header=header, dtype=str)


def col(df, *names):
    """first matching column by case-insensitive contains"""
    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))


users = {}
facilities = []
invoices = []
payments = []
contracts = []
deployments = []
meta = {"sources": []}


def add_user(name, phone=None, role="deployer", source=None):
    name, _ = person(name) if phone is None else (s(name), s(phone))
    if not name:
        return None
    key = norm_name(name)
    if not key:
        return None
    u = users.setdefault(key, {"name": name, "phone": None, "role": role, "source": source})
    if phone and not u["phone"]:
        u["phone"] = s(phone)
    # privilege order: admin > finance > supervisor > sales > deployer
    order = {"admin": 5, "finance": 4, "supervisor": 3, "sales": 2, "deployer": 1}
    if order.get(role, 0) > order.get(u.get("role", "deployer"), 0):
        u["role"] = role
    return key


def add_facility(rec):
    key = norm_name(rec.get("name"))
    if not key:
        return None
    for f in facilities:
        if f["_key"] == key:
            # enrich existing
            for k, v in rec.items():
                if k == "_key":
                    continue
                if v not in (None, "", []) and f.get(k) in (None, "", []):
                    f[k] = v
            return f
    rec = dict(rec)
    rec["_key"] = key
    facilities.append(rec)
    return rec


def find_facility(name):
    key = norm_name(name)
    if not key:
        return None
    for f in facilities:
        if f["_key"] == key:
            return f
    return None


# ---------------------------------------------------------------- 1. legacy canonical tracker
legacy = read("Afya_Legacy_Sheet_Updated_4Oct(2).xlsx", "Deployment Tracker")
if legacy is not None:
    meta["sources"].append({"file": "Afya_Legacy_Sheet_Updated_4Oct(2).xlsx", "sheet": "Deployment Tracker", "rows": int(len(legacy))})
    for _, r in legacy.iterrows():
        name = s(r.get("Name Of Instituiton"))
        if not name:
            continue
        sup_key = None
        sup_name, sup_phone = person(r.get("Supervisor"))
        if sup_name:
            sup_key = add_user(sup_name, sup_phone, "supervisor", "legacy-tracker")
        add_facility({
            "name": name, "level": s(r.get("Level")), "fid": s(r.get("Facility ID")),
            "county": county(r.get("County")), "subcounty": s(r.get("Subcounty")),
            "contact_person": s(r.get("Contact Person")), "phone": s(r.get("Phone No")),
            "email": s(r.get("Email Adresses.")),
            "agreed_price": money(r.get("agreed price")), "contract_type": s(r.get("Contract type")),
            "contracted": yn(r.get("Contracted (yes/no)")), "signed": yn(r.get("Signed(yes/no)")),
            "paid": yn(r.get("Paid ( yes/no)")), "invoiced": yn(r.get("Invoiced")),
            "next_step": s(r.get("Next Step")), "signrequest": s(r.get("SignRequest status (4 Oct)")),
            "supervisor_name": sup_name, "supervisor_key": sup_key,
            "remark": s(r.get("Remark")), "source": "legacy-tracker",
        })
        for field in ("Associate 1", "Associate 2"):
            for p in split_people(r.get(field)):
                k = add_user(p["name"], p["phone"], "deployer", "legacy-tracker")
                f = find_facility(name)
                if f is not None and k and k not in f.setdefault("deployer_keys", []):
                    f["deployer_keys"].append(k)

# ---------------------------------------------------------------- 2. master list (who sold / who deployed)
master = read("MASTER LIST 1Combined_Counties.xlsx", "Combined")
if master is not None:
    meta["sources"].append({"file": "MASTER LIST 1Combined_Counties.xlsx", "sheet": "Combined", "rows": int(len(master))})
    for _, r in master.iterrows():
        name = s(r.get("Name Of Instituiton"))
        if not name:
            continue
        sale_name, sale_phone = person(r.get("Person(s) who did the sale"))
        sale_key = add_user(sale_name, sale_phone, "sales", "master-list") if sale_name else None
        sup_name, sup_phone = person(r.get("Supervisor"))
        sup_key = add_user(sup_name, sup_phone, "supervisor", "master-list") if sup_name else None
        rec = {
            "name": name, "level": s(r.get("Level")), "fid": s(r.get("Facility ID")),
            "county": county(r.get("County")), "subcounty": s(r.get("Subcounty")),
            "contact_person": s(r.get("Facility Contact")), "phone": s(r.get("Phone No")),
            "email": s(r.get("Email Adresses.")),
            "agreed_price": money(r.get("agreed price")), "contract_type": s(r.get("Contract type")),
            "contracted": yn(r.get("Contracted (yes/no)")), "signed": yn(r.get("Signed(yes/no)")),
            "paid": yn(r.get("Paid ( yes/no)")), "invoiced": yn(r.get("Invoiced")),
            "sales_key": sale_key, "sales_name": sale_name,
            "supervisor_key": sup_key, "supervisor_name": sup_name,
            "remark": s(r.get("Remark")), "source": "master-list",
        }
        f = add_facility(rec)
        if sale_key:
            f["sales_key"] = sale_key
        for p in split_people(r.get("Person(s) who deployed")):
            k = add_user(p["name"], p["phone"], "deployer", "master-list")
            if k and k not in f.setdefault("deployer_keys", []):
                f["deployer_keys"].append(k)
        dd = s(r.get("Invoiced"))
        if dd and dd.lower() not in ("yes", "no", "y", "n", "true", "false", "nan"):
            invoices.append({"facility": name, "invoice_no": dd, "amount": money(r.get("agreed price")),
                             "posting_date": None, "source": "master-list"})

# ---------------------------------------------------------------- 3. rep closed-deal sheets
def deal_rows(file, sheet, header, fname_col, amount_cols, date_col, owner,
              county_col=None, level_col=None, contact_col=None, phone_col=None,
              email_col=None, receipt_col=None, billed_cols=None, paid_date_col=None):
    df = read(file, sheet, header)
    if df is None:
        return
    meta["sources"].append({"file": file, "sheet": sheet, "rows": int(len(df))})
    add_user(owner, None, "sales", file)
    for _, r in df.iterrows():
        name = s(r.get(fname_col))
        if not name or norm_name(name) in ("", "facility name"):
            continue
        amt = None
        for c in amount_cols:
            if c in df.columns:
                amt = money(r.get(c))
                if amt:
                    break
        # what one subscription period costs (used for months-paid maths)
        billed = None
        for c in (billed_cols or []):
            if c in df.columns:
                billed = money(r.get(c))
                if billed:
                    break
        contracted = iso_date(r.get(date_col)) if date_col else None
        f = add_facility({
            "name": name, "source": file,
            "level": s(r.get(level_col)) if level_col else None,
            "county": county(r.get(county_col)) if county_col else None,
            "contact_person": s(r.get(contact_col)) if contact_col else None,
            "phone": s(r.get(phone_col)) if phone_col else None,
            "email": s(r.get(email_col)) if email_col else None,
            "contracted": True, "signed": True, "paid": bool(amt),
            "contracted_date": contracted,
            "monthly_price": billed,
            "contract_months": (round(amt / billed) if (amt and billed) else None),
            "sales_name": owner, "sales_key": norm_name(owner),
        })
        if contracted and not f.get("contracted_date"):
            f["contracted_date"] = contracted
        if billed and not f.get("monthly_price"):
            f["monthly_price"] = billed
        if amt:
            payments.append({"facility": name, "amount": amt,
                             "paid_on": (iso_date(r.get(paid_date_col)) if paid_date_col else None) or contracted,
                             "reference": s(r.get(receipt_col)) if receipt_col else None,
                             "mode": "legacy", "source": file, "sales_key": norm_name(owner)})


deal_rows("ANNET CONTRACTED (1).xlsx", "Sheet1", 0, "Facility",
          ["Amount Paid", "Billed Amount (KES)"], "Stage", "Annet",
          county_col="County", level_col="Level", contact_col="Contact Person",
          phone_col="Contact Number", email_col="Email", receipt_col="Transaction code",
          billed_cols=["Billed Amount (KES)"])
deal_rows("John Michael.xlsx", "Sheet1", 0, "Facility",
          ["Amount Paid", "Billed Amount (KES)"], "Date ", "John Michael",
          county_col="County", level_col="Level", contact_col="Contact Person",
          phone_col="Contact Number", email_col="Email", receipt_col="REF. NO.",
          billed_cols=["Billed Amount (KES)"])
deal_rows("Adreans_Closed_Deals (2).xlsx", "Closed Deals", 1, "Facility Name",
          ["Amount Paid (KES)"], "Date Signed", "Adreans",
          level_col="Level", contact_col="Contact Person", phone_col="Phone Number",
          paid_date_col="Date Paid")
deal_rows("Abraham KIPKOSGEI CONTRACTED AND PAID FACILITIE (FIELD SALES).xlsx", "Sheet1", 1, "FACILITY NAME",
          ["Billed Amount"], "CONTRACTED DATE", "Abraham Kipkosgei",
          level_col="FACILITY LEVEL", contact_col="FACILITY OWNERS  NAME",
          phone_col="PHONE NUMER", email_col="EMAIL")
deal_rows("Akinyi Contracted Facilities.xlsx", "Sheet1", 0, "Name of facility",
          ["Amount"], None, "Akinyi", contact_col="Contact Person", phone_col="Phone",
          receipt_col="Receipt")
deal_rows("GabrielkipkoechDenisKibichi.xlsx", "Closed Deals", 0, "Facility",
          ["Amount"], None, "Gabriel Kipkoech", level_col="Level", contact_col="Contact Person",
          receipt_col="Receipt")
deal_rows("Lawerence Ngeno.xlsx", "Post-Sales Tracker", 2, "Facility Name",
          ["Payment Received (KES)", "Amount Signed (KES)"], "Date Contract Signed", "Lawrence Kipngeno",
          county_col="County", level_col="Hospital Level", contact_col="Facility Owner / Director",
          billed_cols=["Amount Signed (KES)"])

# ---------------------------------------------------------------- 4. brian.xlsx -> open leads
leads = read("brian.xlsx", "Leads")
if leads is not None:
    meta["sources"].append({"file": "brian.xlsx", "sheet": "Leads", "rows": int(len(leads))})
    add_user("Brian", None, "sales", "brian.xlsx")
    for _, r in leads.iterrows():
        name = s(r.get("Facility"))
        if not name:
            continue
        stage = (norm(r.get("Stage")) or "lead")
        add_facility({
            "name": name, "level": s(r.get("Level")), "county": county(r.get("Location / County")),
            "contact_person": s(r.get("Contact person")), "email": s(r.get("Email")), "phone": s(r.get("Phone")),
            "monthly_price": money(r.get("Monthly (KES)")), "contract_months": money(r.get("Months")),
            "agreed_price": money(r.get("Subtotal (KES)")),
            "source": "brian-leads", "sales_name": "Brian", "sales_key": norm_name("Brian"),
            "lead_stage": "paid" if stage == "paid" else ("pipeline" if stage in ("pipeline", "contact") else "lead"),
            "remark": s(r.get("Notes")),
        })

# ---------------------------------------------------------------- 5. Business Central invoices
rev = read("Revenue.xlsx", "Sales Invoices to Customers")
if rev is not None:
    meta["sources"].append({"file": "Revenue.xlsx", "sheet": "Sales Invoices to Customers", "rows": int(len(rev))})
    for _, r in rev.iterrows():
        cust = s(r.get("Source Name")) or s(r.get("Balancing Description"))
        amt = money(r.get("Amount (LCY)"))
        if not cust or not amt:
            continue
        doc = s(r.get("Document No."))
        invoices.append({
            "facility": cust, "invoice_no": doc, "amount": amt,
            "posting_date": s(r.get("Posting Date")), "source": "business-central",
            "external_doc": s(r.get("External Document No.")),
            # keep the customer name so unmatched rows are identifiable in the queue
            "description": cust,
            "doc_type": "invoice",
        })

# ---- detailed invoice lines + credit memos (the "Posted Sales ... Lines" export)
invoice_lines = []
credit_notes = []
lines_wb = read("Reporting_Sales.xlsx", "Posted Sales Invoice Lines")
if lines_wb is not None:
    meta["sources"].append({"file": "Reporting_Sales.xlsx", "sheet": "Posted Sales Invoice Lines", "rows": int(len(lines_wb))})
    line_no = {}
    for _, r in lines_wb.iterrows():
        doc = s(r.get("Document No."))
        amt = money(r.get("Amount"))
        desc = s(r.get("Description"))
        if not doc or amt is None:
            continue
        # the export includes blank spacer rows; skip anything without a real line
        if not s(r.get("No.")) and not desc:
            continue
        line_no[doc] = line_no.get(doc, 0) + 1
        invoice_lines.append({
            "invoice_no": doc,
            "line_no": line_no[doc],
            "item_no": s(r.get("No.")),
            "description": desc,
            "quantity": money(r.get("Quantity")),
            "unit_price": money(r.get("Unit Price Excl. VAT")),
            "discount_pct": money(r.get("Line Discount %")),
            "amount": amt,
            "category": line_category(r.get("No."), desc),
            "unit_of_measure": s(r.get("Unit of Measure Code")),
            "customer_no": s(r.get("Sell-to Customer No.")),
        })

memo_wb = read("Reporting_Sales.xlsx", "Posted Sales Credit Memo Lines")
if memo_wb is not None:
    meta["sources"].append({"file": "Reporting_Sales.xlsx", "sheet": "Posted Sales Credit Memo Lines", "rows": int(len(memo_wb))})
    groups = {}
    for _, r in memo_wb.iterrows():
        doc = s(r.get("Document No."))
        if not doc:
            continue
        g = groups.setdefault(doc, {"doc": doc, "amount": 0.0, "customer": None, "customer_no": None,
                                    "reverses": None, "lines": []})
        desc = s(r.get("Description"))
        if desc:
            m = re.search(r"Invoice No\.?\s*([A-Z]{2,4}\d[\w-]*)", desc)
            if m:
                g["reverses"] = m.group(1).rstrip(": ")
        cust = s(r.get("Sell-to Customer Name"))
        if cust:
            g["customer"] = g["customer"] or cust
        cno = s(r.get("Sell-to Customer No."))
        if cno:
            g["customer_no"] = g["customer_no"] or cno
        amt = money(r.get("Amount"))
        item = s(r.get("No."))
        if amt is not None and item:
            g["amount"] += abs(amt)
            g["lines"].append({
                "item_no": item, "description": desc,
                "quantity": money(r.get("Quantity")),
                "unit_price": money(r.get("Unit Price Excl. VAT")),
                "amount": abs(amt),
                "category": line_category(item, desc),
            })
    for g in groups.values():
        if not g["amount"]:
            continue
        credit_notes.append({
            "credit_no": g["doc"], "amount": round(g["amount"], 2),
            "facility": g["customer"], "customer_no": g["customer_no"],
            "reverses_no": g["reverses"], "lines": g["lines"],
        })

# ---------------------------------------------------------------- 6. contract + invoice + payment facts from legacy flags
for f in facilities:
    if f.get("signed") is True:
        contracts.append({
            "facility": f["name"], "signed_date": None,
            "amount": f.get("agreed_price"), "source": "legacy-tracker",
            "status": "verified", "note": "Signed per historical tracker (no file attached)",
        })

has_payment = {p["facility"] for p in payments}
for f in facilities:
    if f.get("paid") is True and f["name"] not in has_payment and f.get("agreed_price"):
        payments.append({
            "facility": f["name"], "amount": f["agreed_price"], "paid_on": None,
            "mode": "legacy", "reference": None, "source": "legacy-tracker",
            "note": "Reconstructed from historical 'Paid = yes' flag",
        })

# ---------------------------------------------------------------- 7. deployments from master-list deployer columns
for f in facilities:
    for k in f.get("deployer_keys", []):
        deployments.append({
            "facility": f["name"], "deployer_key": k, "supervisor_key": f.get("supervisor_key"),
            "status": "deployed", "source": f.get("source") or "import",
        })

payload = {
    "meta": meta,
    "users": [{"key": k, **v} for k, v in users.items()],
    "facilities": facilities,
    "invoices": invoices,
    "invoice_lines": invoice_lines,
    "credit_notes": credit_notes,
    "payments": payments,
    "contracts": contracts,
    "deployments": deployments,
}

with open(OUT, "w", encoding="utf-8") as fh:
    json.dump(payload, fh, ensure_ascii=False, indent=1)

print(json.dumps({
    "users": len(users),
    "facilities": len(facilities),
    "invoices": len(invoices),
    "invoice_lines": len(invoice_lines),
    "credit_notes": len(credit_notes),
    "payments": len(payments),
    "contracts": len(contracts),
    "deployments": len(deployments),
    "out": OUT,
}, indent=1))