ecw-charge-validation/tools/import_payer_groups.py

r"""
Refresh src\ecw_bot\payer_groups.json from the team's Claim Status Dashboard.

    python tools\import_payer_groups.py
    python tools\import_payer_groups.py "<path to the dashboard .xlsx>"
    python tools\import_payer_groups.py --dry-run

The dashboard (default: \\mecare2\Production\Salud-eCW RPA\Claim Status
Dashboard Details 1001.xlsx) is the team's own template for which payer goes
in which group: its "Payer Name" and "Payer Group Name" columns. This keeps
ONLY those two columns — distinct pairs, payer names and group names — so no
patient data from the dashboard ever reaches the repo.

Rules:
  * Every sheet with both headers is read; a payer seen with two different
    groups keeps the one it has on most rows, and the conflict is printed.
  * A payer already in payer_groups.json but NOT in the dashboard is kept (the
    document's RAE spellings, for one), with its earlier short group name
    translated to the dashboard's own ("RAE payer" -> "RAE (Behavioral
    Health)", ...), and listed so a person can check it.
  * Nothing is written with --dry-run; otherwise the old file is replaced and
    every added / changed / kept payer is listed.

Reads a network file only — never logs into eCW. openpyxl is used here (it is
a developer tool, not the bot) because the dashboard is a 5 MB, 25,000-row
workbook.
"""

import sys
import json
import argparse
import collections
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(ROOT / "src"))
for _s in (sys.stdout, sys.stderr):
    try:
        _s.reconfigure(encoding="utf-8", errors="replace")
    except Exception:
        pass

DEFAULT_DASHBOARD = (r"\\mecare2\Production\Salud-eCW RPA"
                     r"\Claim Status Dashboard Details 1001.xlsx")
CONFIG = ROOT / "src" / "ecw_bot" / "payer_groups.json"

# The bot's earlier short group names -> the dashboard's names, for payers
# the dashboard does not list.
_EARLIER_TO_DASHBOARD = {
    "Commercial": "Commercial Ins",
    "RAE payer": "RAE (Behavioral Health)",
    "Dentaquest": "Dental - CHP+/DentaQuest",
    "Program": "Self Pay",
}


def read_pairs(path) -> dict:
    """{payer: Counter({group: rows})} from every sheet with both headers."""
    import openpyxl
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    pairs = collections.defaultdict(collections.Counter)
    try:
        for ws in wb.worksheets:
            rows = ws.iter_rows(values_only=True)
            header = [str(h or "").strip() for h in (next(rows, None) or [])]
            if "Payer Name" not in header or "Payer Group Name" not in header:
                continue
            pi, gi = header.index("Payer Name"), header.index("Payer Group Name")
            for row in rows:
                if not row or len(row) <= max(pi, gi):
                    continue
                payer = str(row[pi] or "").strip()
                group = str(row[gi] or "").strip()
                if payer and group:
                    pairs[payer][group] += 1
    finally:
        wb.close()
    return pairs


def build(pairs: dict, current: dict):
    """(new mapping, conflicts, kept-from-current)."""
    mapping, conflicts = {}, {}
    for payer, groups in pairs.items():
        mapping[payer] = groups.most_common(1)[0][0]
        if len(groups) > 1:
            conflicts[payer] = dict(groups)
    kept = {}
    for payer, group in current.items():
        if payer not in mapping:
            mapping[payer] = _EARLIER_TO_DASHBOARD.get(group, group)
            kept[payer] = mapping[payer]
    return dict(sorted(mapping.items(), key=lambda kv: kv[0].lower())), \
        conflicts, kept


def main(argv=None) -> int:
    ap = argparse.ArgumentParser(description=__doc__.splitlines()[1])
    ap.add_argument("dashboard", nargs="?", default=DEFAULT_DASHBOARD)
    ap.add_argument("--dry-run", action="store_true")
    args = ap.parse_args(argv)

    current = json.loads(CONFIG.read_text(encoding="utf-8")) \
        if CONFIG.exists() else {}
    print(f"[IMPORT] Reading {args.dashboard}")
    pairs = read_pairs(args.dashboard)
    if not pairs:
        print("[IMPORT] No sheet with 'Payer Name' and 'Payer Group Name' — "
              "nothing written")
        return 1
    mapping, conflicts, kept = build(pairs, current)

    added = sorted(p for p in mapping if p not in current)
    changed = sorted(p for p in mapping
                     if p in current and current[p] != mapping[p])
    groups = collections.Counter(mapping.values())
    print(f"[IMPORT] {len(pairs)} payer(s) in the dashboard, "
          f"{len(mapping)} in the new table")
    for g, n in groups.most_common():
        print(f"[IMPORT]   {n:4d}  {g}")
    for p, gs in sorted(conflicts.items()):
        print(f"[IMPORT] CONFLICT {p!r}: {gs} -> kept {mapping[p]!r}")
    for p in sorted(kept):
        print(f"[IMPORT] KEPT (not in the dashboard) {p!r} -> {kept[p]!r}")
    for p in changed:
        print(f"[IMPORT] CHANGED {p!r}: {current[p]!r} -> {mapping[p]!r}")
    print(f"[IMPORT] {len(added)} added, {len(changed)} changed, "
          f"{len(kept)} kept from the old table")

    if args.dry_run:
        print("[IMPORT] --dry-run: nothing written")
        return 0
    CONFIG.write_text(json.dumps(mapping, indent=2, ensure_ascii=False) + "\n",
                      encoding="utf-8")
    print(f"[IMPORT] Wrote {CONFIG}")
    return 0


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