#!/usr/bin/env python3
"""Join Bellingham budget positions to job-description specs.

Matching: normalized exact title -> level-stripped base title -> fuzzy
(difflib), with bargaining-unit compatibility as a tiebreaker/sanity check.

Output: bellingham/data/positions_joined.csv plus a printed coverage report.
"""
import csv
import difflib
import json
import re
from collections import defaultdict
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
DATA = ROOT / "bellingham/data"

# budget employee_group -> normalized unit key
GROUP_TO_UNIT = {
    "Local 1937": "1937", "Teamster 231": "231", "Non-Union": "E",
    "Police Guild": "POLICE", "FOP": "FOP", "W-C Disp Guild": "WHATCOMM",
    "AFSCME 114F": "114", "AFSCME 114L": "114L", "IAFF 106": "106",
    "IAFF 106S": "106S", "Temporary": "TEMP", "Elected": "ELECTED",
}
# JD index Bargaining Unit -> normalized unit key
JD_UNIT = {
    "Union:1937": "1937", "1937": "1937",
    "Union:231": "231", "231": "231", "Union: 231": "231",
    "E-Plan": "E", "Union:114": "114", "NON 114": "E",
    "Union:114L": "114L", "Union:106": "106", "": None,
}

LEVEL_RE = re.compile(
    r"\s*(?:[-,/]?\s*(?:i{1,3}v?|iv|vi?|[1-9]|it)\b\.?)+\s*$", re.IGNORECASE)


def norm(s):
    s = re.sub(r"\([^)]*\)", " ", s)  # drop parentheticals
    s = s.lower().replace("&", "and").replace("-", " ")
    s = re.sub(r"[^a-z0-9 /,]", " ", s)
    s = re.sub(r"\s+", " ", s).strip()
    return s


def base_title(s):
    """Strip trailing level designators: 'office assistant 1, 2, 3' -> 'office assistant'."""
    s = norm(s)
    prev = None
    while prev != s:
        prev = s
        s = LEVEL_RE.sub("", s).strip().rstrip(",/").strip()
    return s


def singular(s):
    """'patrol officers' -> 'patrol officer'; 'captains' -> 'captain'."""
    words = s.split()
    if words and words[-1].endswith("s") and not words[-1].endswith("ss"):
        words[-1] = words[-1][:-1]
    return " ".join(words)


def family(s):
    """Classification family: strip assignment suffix and level designators.

    'Utility Worker V- Stormwater Division' -> 'utility worker'
    'Park Technician - Athletic Fields/Facilities' -> 'park technician'
    'Custodial Maintenance Worker 1, 2' -> 'custodial maintenance worker'
    """
    s = re.split(r"\s*[–—]\s*|\s+-\s+|(?<=[a-zIV1-9])- ", s)[0]
    return singular(base_title(s))


def load():
    budget = list(csv.DictReader(open(DATA / "budget_positions.csv")))
    jds = list(csv.DictReader(open(DATA / "job_descriptions_index.csv")))
    pay = list(csv.DictReader(open(DATA / "pay_titles_2026.csv")))
    return budget, jds, pay


def build_pay_lookup(pay):
    idx = {"norm": defaultdict(list), "base": defaultdict(list),
           "family": defaultdict(list)}
    for p in pay:
        t = p["title"]
        idx["norm"][norm(t)].append(p)
        idx["base"][base_title(t)].append(p)
        idx["base"][singular(base_title(t))].append(p)
        idx["family"][family(t)].append(p)
    return idx


def match_pay(row, idx):
    """Find a pay-schedule entry for a budget row; prefer same employee group.

    Compound budget rows ('Firefighters,FF/Paramedics'; 'X-In-Training, Call
    Receiver, Dispatcher, ...') fall back to matching their first component.
    """
    ALIASES = {  # curated word-order / naming aliases (budget -> pay sheet)
        "chief of police": "Police Chief",
        "fire inspectors": "Inspector",
        "whatcomm deputy director": "Deputy Administrator",
    }
    group = row["employee_group"]
    variants = [row["title"]]
    variants += [p.strip() for p in row["title"].split(",") if p.strip()]
    variants += [v for k, v in ALIASES.items()
                 if norm(row["title"]) == k or family(row["title"]) == k]
    for title in variants:
        for level in ("norm", "base", "family"):
            key = {"norm": norm(title), "base": base_title(title),
                   "family": family(title)}[level]
            cands = idx[level].get(key) or (
                idx[level].get(singular(key)) if level != "norm" else None)
            if not cands:
                continue
            same = [c for c in cands if c["employee_group"] == group]
            chosen = same[0] if same else cands[0]
            return chosen, level, bool(same)
    near = difflib.get_close_matches(family(row["title"]),
                                     list(idx["family"].keys()),
                                     n=1, cutoff=0.86)
    if near:
        cands = idx["family"][near[0]]
        same = [c for c in cands if c["employee_group"] == group]
        chosen = same[0] if same else cands[0]
        return chosen, "fuzzy-family", bool(same)
    return None, "none", False


def build_jd_lookup(jds):
    by_norm, by_base, by_family = defaultdict(list), defaultdict(list), defaultdict(list)
    for jd in jds:
        jd["_unit"] = JD_UNIT.get(jd["Bargaining Unit"].strip(), None)
        # Index the display title, sort title, and civil-service classification
        names = {jd["Job Title"], jd["Job Title Sort"], jd["Classification"]}
        for name in filter(None, names):
            if name.strip().upper() in ("N/A", "NA", "-"):
                continue
            by_norm[norm(name)].append(jd)
            b = base_title(name)
            by_base[b].append(jd)
            by_base[singular(b)].append(jd)
            by_family[family(name)].append(jd)
            # 'Accountant I/II/III' -> index the pre-slash base too
            if "/" in name:
                by_base[base_title(name.split("/")[0])].append(jd)
    return by_norm, by_base, by_family


def pick(cands, unit):
    """Prefer a spec whose unit agrees with the budget row's unit."""
    if not cands:
        return None, False
    same = [c for c in cands if c["_unit"] == unit]
    chosen = same[0] if same else cands[0]
    return chosen, bool(same)


def dedupe(cands):
    seen, out = set(), []
    for c in cands:
        if c["pdf_file"] not in seen:
            seen.add(c["pdf_file"])
            out.append(c)
    return out


def match(budget, jds):
    by_norm, by_base, by_family = build_jd_lookup(jds)
    all_norms = list(by_norm.keys())
    out = []
    for row in budget:
        unit = GROUP_TO_UNIT.get(row["employee_group"])
        title = row["title"]
        method, cands = "none", None
        if by_norm.get(norm(title)):
            cands, method = by_norm[norm(title)], "exact"
        if not cands:
            for cand_key in (base_title(title), singular(base_title(title))):
                if by_base.get(cand_key):
                    cands, method = by_base[cand_key], "base"
                    break
        if not cands and by_family.get(family(title)):
            cands, method = by_family[family(title)], "family"
        if not cands:
            near = difflib.get_close_matches(norm(title), all_norms, n=1, cutoff=0.86)
            if near:
                cands, method = by_norm[near[0]], "fuzzy:" + near[0]
            else:
                near = difflib.get_close_matches(
                    family(title), list(by_family.keys()), n=1, cutoff=0.86)
                if near:
                    cands, method = by_family[near[0]], "fuzzy-family:" + near[0]
        cands = dedupe(cands or [])
        jd, unit_ok = pick(cands, unit)
        pay_hit, pay_method, pay_group_ok = match_pay(row, match.pay_idx)
        out.append({
            **row,
            "pay_plan": pay_hit["plan"] if pay_hit else "",
            "pay_title": pay_hit["title"] if pay_hit else "",
            "pay_grade": pay_hit["salary_grade"] if pay_hit else "",
            "pay_steps": pay_hit["steps"] if pay_hit else "",
            "pay_basis": pay_hit["basis"] if pay_hit else "",
            "pay_match_method": pay_method if pay_hit else "none",
            "pay_group_agrees": pay_group_ok,
            "jd_title": (jd["Job Title"] or jd["Job Title Sort"]) if jd else "",
            "jd_file": jd["pdf_file"] if jd else "",
            "jd_salary_grade": jd["Salary Grade"] if jd else "",
            "jd_department": jd["Department"] if jd else "",
            "jd_match_count": len(cands),
            "jd_files_all": ";".join(c["pdf_file"] for c in cands),
            "match_method": method if jd else "none",
            "unit_agrees": unit_ok,
        })
    return out


def report(rows):
    def fte(r):
        try:
            return float(r["fte_2026_adopted"] or 0)
        except ValueError:
            return 0.0
    total_rows = len(rows)
    live = [r for r in rows if fte(r) > 0]
    matched = [r for r in live if r["match_method"] != "none"]
    fte_all = sum(fte(r) for r in live)
    fte_matched = sum(fte(r) for r in matched)
    print(f"2026-active classification rows: {len(live)}/{total_rows}")
    print(f"matched to a job description: {len(matched)} "
          f"({100*len(matched)/len(live):.0f}% of rows, "
          f"{100*fte_matched/fte_all:.0f}% of {fte_all:.1f} FTEs)")
    by_group = defaultdict(lambda: [0, 0, 0.0, 0.0])
    for r in live:
        g = by_group[r["employee_group"]]
        g[0] += 1
        g[2] += fte(r)
        if r["match_method"] != "none":
            g[1] += 1
            g[3] += fte(r)
    print("\nby employee group (rows matched/rows, FTEs matched/FTEs):")
    for grp, (n, m, f, fm) in sorted(by_group.items(), key=lambda kv: -kv[1][2]):
        print(f"  {grp or '(none)':14} {m:3}/{n:3} rows   {fm:7.1f}/{f:7.1f} FTE")
    pay_matched = [r for r in live if r["pay_match_method"] != "none"]
    fte_pay = sum(fte(r) for r in pay_matched)
    both = [r for r in live if r["pay_match_method"] != "none"
            and r["match_method"] != "none"]
    fte_both = sum(fte(r) for r in both)
    print(f"\npay matched: {len(pay_matched)} rows "
          f"({100*fte_pay/fte_all:.0f}% of FTEs); "
          f"full position+spec+pay join: {len(both)} rows "
          f"({100*fte_both/fte_all:.0f}% of FTEs)")
    unmatched = [r for r in live if r["match_method"] == "none"]
    print(f"\nno-JD 2026-active titles ({len(unmatched)}), largest first:")
    for r in sorted(unmatched, key=fte, reverse=True)[:15]:
        pay_note = f"[pay:{r['pay_plan']}]" if r["pay_match_method"] != "none" else "[no pay]"
        print(f"  {fte(r):6.1f}  {r['employee_group'] or '-':14} {pay_note:10} "
              f"{r['department']}: {r['title']}")
    nopay = [r for r in live if r["pay_match_method"] == "none"]
    print(f"\nno-pay 2026-active titles ({len(nopay)}), largest first:")
    for r in sorted(nopay, key=fte, reverse=True)[:15]:
        print(f"  {fte(r):6.1f}  {r['employee_group'] or '-':14} "
              f"{r['department']}: {r['title']}")


def main():
    budget, jds, pay = load()
    match.pay_idx = build_pay_lookup(pay)
    rows = match(budget, jds)
    with open(DATA / "positions_joined.csv", "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        w.writeheader()
        w.writerows(rows)
    report(rows)


if __name__ == "__main__":
    main()