#!/usr/bin/env python3
"""Parse Bellingham 2026 pay-schedule PDFs into a title -> grade/steps table.

Machine-parses the grade-structured plans (G, S, E1, E2, DEPTHEAD, L, D, C).
Special-cases Plan P (grade + hourly-rate line pairs) and Plan M (step blocks).
Encodes the formula-based plans (F, O/FOP) from their stated formulas.

Output: bellingham/data/pay_titles_2026.csv
        (plan, employee_group, title, salary_grade, steps, basis, note, source)
"""
import csv
import re
import sys
from pathlib import Path

import pdfplumber

sys.path.insert(0, str(Path(__file__).resolve().parent))
from parse_bellingham_budget_positions import rebuild_lines  # noqa: E402

ROOT = Path(__file__).resolve().parent.parent
RAW = ROOT / "bellingham/raw/pay-schedules"
OUT = ROOT / "bellingham/data/pay_titles_2026.csv"

GRADE_RE = re.compile(r"^Salary Grade\s*\(?([A-Z0-9]+)\)?\s*(.*)$")
MONEY_RE = re.compile(r"\$?([\d,]+\.?\d*)")

STOP_WORDS = (
    "Deferred Compensation", "Longevity", "Employees who",
    "The City", "Effective ", "Years Completed", "Section ", "I. ", "II. ",
)
HEADER_WORDS = (
    "APPENDIX", "CITY OF BELLINGHAM", "PLAN ", "Step ", "Salary Structure",
    "Increase", "Market Study", "Annual Step", "Part Time", "Monthly Salary",
    "Hourly", "January", "FLSA", "Employee Group", "Group", "Non-Uniformed",
    "Uniformed", "Salary Grade", "COLA", "Guild", "Local 114", "Librarian S",
)

# (file, plan, employee_group, basis)
GRADE_PLANS = [
    ("26_PLANG_1937_FEB.pdf", "G", "Local 1937", "hourly"),
    ("26_PLANS_231_JAN.pdf", "S", "Teamster 231", "monthly"),
    ("26_PLANE2_ETeam_JAN.pdf", "E2", "Non-Union", "monthly"),
    ("26_PLAN_E1_ETeam_JAN.pdf", "E1", "Non-Union", "monthly"),
    ("26_PLAND_Pol_JAN.pdf", "D", "Police Guild", "monthly"),
    ("26_PLANC_106_JAN.pdf", "C", "IAFF 106", "monthly"),
]

# The posted Plan L PDFs are image-only scans (no text layer); values below are
# transcribed from the 2025-2027 AFSCME 114L CBA, Appendix A, 2026 table (p.34).
PLAN_L_2026 = [
    ("Librarian 1", "6229;6595;6962;7328;7694;8061;8427"),
    ("Public Services Librarian", "6229;6595;6962;7328;7694;8061;8427"),
    ("Adjunct Librarian", "6229;6595;6962;7328;7694;8061;8427"),
    ("Librarian 2", "6525;6909;7293;7677;8061;8445;8829"),
    ("Children's Services Librarian", "6525;6909;7293;7677;8061;8445;8829"),
    ("Teen Services Librarian", "6525;6909;7293;7677;8061;8445;8829"),
    ("Adult Services Librarian", "6525;6909;7293;7677;8061;8445;8829"),
    ("Librarian 3", "7270;7698;8125;8553;8981;9408;9836"),
    ("Head of Digital Services", "7270;7698;8125;8553;8981;9408;9836"),
]


def looks_like_title(text):
    t = text.strip()
    if not t or "$" in t or any(h in t for h in HEADER_WORDS):
        return False
    if len(t.split()) > 8 or t.endswith((".", ":", ";")):
        return False
    if re.search(r"\b(shall|the|of the|will|per|and/or)\b", t.lower()):
        # sentences; but allow 'and' inside real titles like 'Parks and Rec'
        if not re.match(r"^[A-Z]", t) or " shall " in f" {t.lower()} ":
            return False
    if re.match(r"^[\d\s.%$,]+$", t):
        return False
    return bool(re.match(r"^[A-Z]", t))


def parse_grade_plan(path, plan, group, basis):
    rows = []
    grade, steps = None, []
    stopped = False
    with pdfplumber.open(path) as pdf:
        for page in pdf.pages:
            for _, clusters in rebuild_lines(page):
                line = " ".join(t for _, t in clusters).strip()
                if any(line.startswith(s) or s in line[:40] for s in STOP_WORDS):
                    stopped = True
                m = GRADE_RE.match(line)
                if m:
                    stopped = False
                    grade = m.group(1)
                    steps = [float(x.replace(",", ""))
                             for x in MONEY_RE.findall(m.group(2))]
                    continue
                if stopped or grade is None:
                    continue
                # money-only continuation line (steps wrapped)
                nums = MONEY_RE.findall(line)
                if nums and re.match(r"^[\s$.,\d]+$", line):
                    steps.extend(float(x.replace(",", "")) for x in nums)
                    continue
                for _, cluster in clusters:
                    if looks_like_title(cluster):
                        rows.append({
                            "plan": plan, "employee_group": group,
                            "title": cluster.strip(), "salary_grade": grade,
                            "steps": ";".join(f"{s:g}" for s in steps),
                            "basis": basis, "note": "",
                            "source": path.name,
                        })
    return rows


def clean_steps(steps):
    """Drop stray year tokens (e.g. a wrapped '2026' header) from step lists."""
    if len(steps) <= 1:
        return steps
    others = [s for s in steps if not (2020 <= s <= 2030 and s == int(s))]
    if others and len(others) < len(steps):
        import statistics
        med = statistics.median(others)
        return [s for s in steps
                if not (2020 <= s <= 2030 and s == int(s)
                        and abs(s - med) > 0.5 * med)]
    return steps


# Transcribed from 26_PLANE_DEPTHEAD_JUL.pdf (small, hand-verified; Groups A-D
# are the non-uniform department-head step ladders, titles listed under each).
PLAN_E_DH_2026 = [
    ("Council Members", "Elected", "6101"),
    ("Mayor", "Elected", "19745"),
    ("Municipal Court Judge", "Elected", "19410"),
    ("Court Commissioner", "Non-Union", "16499"),
    ("City Attorney", "Non-Union", "15289;16818;18500"),
    ("Public Works Director", "Non-Union", "14802;16282;17911"),
    ("Finance Director", "Non-Union", "14097;15507;17058"),
    ("Human Resources Director", "Non-Union", "14097;15507;17058"),
    ("Information Technology Services Director", "Non-Union", "14097;15507;17058"),
    ("Parks and Recreation Director", "Non-Union", "14097;15507;17058"),
    ("Planning and Community Development Director", "Non-Union", "14097;15507;17058"),
    ("Library Director", "Non-Union", "12179;13395;14735"),
    ("Court Director", "Non-Union", "12179;13395;14735"),
    ("Deputy Administrator", "Non-Union", "14097;15507;17058"),
    ("Fire Chief", "Non-Union", "18805"),
    ("Police Chief", "Non-Union", "18805"),
    ("Assistant Fire Chief", "Non-Union", "14407;15848;17432"),
    ("Deputy Police Chief", "Non-Union", "14407;15848;17432"),
]


def parse_plan_e_dh(path):
    return [{"plan": "E-DH", "employee_group": grp, "title": title,
             "salary_grade": "", "steps": steps, "basis": "monthly",
             "note": "hand-transcribed from dept-head sheet",
             "source": path.name}
            for title, grp, steps in PLAN_E_DH_2026]


def parse_plan_p(path):
    """Plan P: 'Salary Grade X' then 'Hourly Rate $...' then titles."""
    rows, grade, steps = [], None, []
    with pdfplumber.open(path) as pdf:
        for page in pdf.pages:
            for _, clusters in rebuild_lines(page):
                line = " ".join(t for _, t in clusters).strip()
                if any(s in line[:40] for s in STOP_WORDS):
                    grade = None
                    continue
                m = re.match(r"^Salary Grade\s+([A-Z])\s*$", line)
                if m:
                    grade, steps = m.group(1), []
                    continue
                if line.startswith("Hourly Rate"):
                    steps = [float(x.replace(",", ""))
                             for x in MONEY_RE.findall(line)]
                    continue
                if grade and looks_like_title(line):
                    rows.append({
                        "plan": "P", "employee_group": "W-C Disp Guild",
                        "title": line, "salary_grade": grade,
                        "steps": ";".join(f"{s:g}" for s in steps),
                        "basis": "hourly", "note": "", "source": path.name,
                    })
    return rows


def parse_plan_m(path):
    """Plan M: blocks of monthly steps + 'Hourly Rate' line, then the title."""
    rows, monthly, seen_block = [], [], False
    with pdfplumber.open(path) as pdf:
        for _, clusters in rebuild_lines(pdf.pages[0]):
            line = " ".join(t for _, t in clusters).strip()
            if re.match(r"^[\s$.,\d]+$", line) and "$" in line:
                monthly = [float(x.replace(",", ""))
                           for x in MONEY_RE.findall(line)]
                seen_block = True
                continue
            if line.startswith("Hourly Rate"):
                continue
            if seen_block and looks_like_title(line):
                rows.append({
                    "plan": "M", "employee_group": "AFSCME 114F",
                    "title": line, "salary_grade": "",
                    "steps": ";".join(f"{s:g}" for s in monthly),
                    "basis": "monthly", "note": "", "source": path.name,
                })
                seen_block = False
    return rows


def formula_plans(rows_so_far):
    """Plan F (fire staff officers) and Plan O (FOP) are % formulas."""
    out = []
    top_captain = top_sergeant = None
    for r in rows_so_far:
        steps = [float(s) for s in r["steps"].split(";") if s]
        if r["plan"] == "C" and r["title"] == "Captain" and steps:
            top_captain = max(steps)
        if r["plan"] == "D" and r["title"] == "Sergeant" and steps:
            top_sergeant = max(steps)
    if top_captain:
        for title in ("Battalion Chief", "Division Chief"):
            steps = [round(top_captain * f) for f in (1.08, 1.10, 1.12)]
            out.append({
                "plan": "F", "employee_group": "IAFF 106S", "title": title,
                "salary_grade": "", "steps": ";".join(str(s) for s in steps),
                "basis": "monthly",
                "note": "computed: 8/10/12% over top-step Captain (Plan F formula)",
                "source": "26_PLANF_106S_JAN.pdf",
            })
    if top_sergeant:
        out.append({
            "plan": "O", "employee_group": "FOP", "title": "Lieutenant",
            "salary_grade": "", "steps": str(round(top_sergeant * 1.18)),
            "basis": "monthly",
            "note": "computed: 18% over top-step Sergeant (Plan O formula; "
                    "posted 2024 sheet shows $13,301)",
            "source": "24_FOP-Plan-O-_JAN.pdf",
        })
    return out


def main():
    rows = []
    for fname, plan, group, basis in GRADE_PLANS:
        path = RAW / fname
        if not path.exists():
            print("MISSING", fname)
            continue
        got = parse_grade_plan(path, plan, group, basis)
        print(f"{plan:5} {len(got):3} titles from {fname}")
        rows.extend(got)
    rows.extend(parse_plan_e_dh(RAW / "26_PLANE_DEPTHEAD_JUL.pdf"))
    rows.extend(parse_plan_p(RAW / "26_PLANP_Whatcomm_JAN.pdf"))
    rows.extend(parse_plan_m(RAW / "26_PLANM_114F_JAN.pdf"))
    for title, steps in PLAN_L_2026:
        rows.append({"plan": "L", "employee_group": "AFSCME 114L",
                     "title": title, "salary_grade": "", "steps": steps,
                     "basis": "monthly",
                     "note": "transcribed from 2025-2027 114L CBA Appendix A "
                             "(posted Plan L PDFs are image-only scans)",
                     "source": "2025-2027-114L-CBA-FINAL.pdf"})
    rows.extend(formula_plans(rows))
    for r in rows:
        steps = [float(s) for s in r["steps"].split(";") if s]
        r["steps"] = ";".join(f"{s:g}" for s in clean_steps(steps))
    with open(OUT, "w", newline="") as f:
        w = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        w.writeheader()
        w.writerows(rows)
    print(f"total pay titles: {len(rows)} -> {OUT.name}")


if __name__ == "__main__":
    main()