"""Rank repeat failures from a tagged work order file.

Reads tagged-work-orders.csv (one row per work order, tagged by Claude) and writes
repeat-failures.xlsx. No AI. Same file in, same ranking out, every time.

    python3 rank_repeats.py tagged-work-orders.csv
    python3 rank_repeats.py tagged-work-orders.csv --out repeat-failures.xlsx --min 2

A repeat failure is the same machine with the same cause, counted MIN times or more
(2 by default). Repeats are ranked by times, then by downtime.
Needs: openpyxl (already on Claude's code tool; pip install openpyxl elsewhere).
"""
import argparse
import csv
import sys
from collections import OrderedDict
from datetime import date
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill

COLUMNS = ["wo", "opened", "asset_as_written", "machine", "cause", "counts", "down_min", "evidence", "check"]
HEAD_FILL = PatternFill("solid", fgColor="3E4A59")
TOP_FILL = PatternFill("solid", fgColor="FCE9E4")


def fail(path, problems):
    print(f"Bad data in {path.name}. Nothing was written. Fix these and run it again:")
    for p in problems[:25]:
        print(f"  - {p}")
    if len(problems) > 25:
        print(f"  ...and {len(problems) - 25} more")
    sys.exit(1)


def read_rows(path):
    with open(path, newline="", encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        header = [h.strip() for h in (reader.fieldnames or [])]
        missing = [c for c in COLUMNS if c not in header]
        if missing:
            fail(path, [f"missing column(s): {', '.join(missing)}. The first line must be: {','.join(COLUMNS)}"])
        raw = [{k.strip(): (v or "").strip() for k, v in r.items() if k} for r in reader]
    problems, seen, rows = [], set(), []
    for i, r in enumerate(raw, start=2):
        where = f"line {i} ({r['wo'] or 'no WO number'})"
        if not r["wo"]:
            problems.append(f"{where}: wo is blank")
        elif r["wo"] in seen:
            problems.append(f"{where}: {r['wo']} is in the file twice")
        seen.add(r["wo"])
        try:
            r["date"] = date.fromisoformat(r["opened"])
        except ValueError:
            problems.append(f"{where}: opened must be a date like 2026-06-09. It says \"{r['opened']}\".")
        r["counts"] = r["counts"].lower()
        if r["counts"] not in ("yes", "no"):
            problems.append(f"{where}: counts must be yes or no. It says \"{r['counts']}\".")
        if r["down_min"] == "":
            r["down"] = None
        else:
            try:
                r["down"] = float(r["down_min"])
                if r["down"] < 0:
                    raise ValueError
            except ValueError:
                problems.append(f"{where}: down_min must be a number of minutes or blank. It says \"{r['down_min']}\".")
        rows.append(r)
    if not rows:
        problems.append("the file has no work orders")
    if problems:
        fail(path, problems)
    return rows


def hours(minutes):
    return round(minutes / 60, 1)


def group(rows):
    groups = OrderedDict()
    for r in rows:
        if r["counts"] != "yes" or not r["machine"] or not r["cause"]:
            continue
        key = (r["machine"].upper(), r["cause"].lower())
        g = groups.setdefault(key, {"machine": r["machine"].upper(), "cause": r["cause"].lower(), "rows": []})
        g["rows"].append(r)
    for g in groups.values():
        g["rows"].sort(key=lambda r: (r["date"], r["wo"]))
        g["times"] = len(g["rows"])
        g["down"] = sum(r["down"] for r in g["rows"] if r["down"] is not None)
        g["no_down"] = sum(1 for r in g["rows"] if r["down"] is None)
    return sorted(groups.values(), key=lambda g: (-g["times"], -g["down"], g["machine"], g["cause"]))


def table(ws, start_row, headers, widths, data, top=0):
    for c, (h, w) in enumerate(zip(headers, widths), start=1):
        cell = ws.cell(row=start_row, column=c, value=h)
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = HEAD_FILL
        cell.alignment = Alignment(vertical="center", wrap_text=True)
        ws.column_dimensions[cell.column_letter].width = w
    for i, vals in enumerate(data, start=1):
        for c, v in enumerate(vals, start=1):
            cell = ws.cell(row=start_row + i, column=c, value=v)
            cell.alignment = Alignment(vertical="top", wrap_text=True)
            if i <= top:
                cell.fill = TOP_FILL
            if isinstance(v, date):
                cell.number_format = "yyyy-mm-dd"
    ws.freeze_panes = ws.cell(row=start_row + 1, column=1)


def title(ws, text, sub):
    ws["A1"] = text
    ws["A1"].font = Font(bold=True, size=14, color="3E4A59")
    ws["A2"] = sub
    ws["A2"].font = Font(size=10, color="555555")


def build(rows, src, out, min_times):
    groups = group(rows)
    repeats = [g for g in groups if g["times"] >= min_times]
    counted = [r for r in rows if r["counts"] == "yes"]
    first, last = min(r["date"] for r in rows), max(r["date"] for r in rows)
    sub = (f"From {src.name}: {len(rows)} work orders, {len(counted)} counted as failures, "
           f"{first.isoformat()} to {last.isoformat()}. A repeat is the same machine with the same cause, "
           f"{min_times} or more times. Ranked by times, then downtime.")
    wb = Workbook()

    ws = wb.active
    ws.title = "Fix first"
    title(ws, "Repeat failures: fix these first", sub)
    data = []
    for i, g in enumerate(repeats, start=1):
        note = f"{g['no_down']} work order(s) have no downtime" if g["no_down"] else ""
        data.append([i, g["machine"], g["cause"], g["times"], hours(g["down"]),
                     g["rows"][0]["date"], g["rows"][-1]["date"], ", ".join(r["wo"] for r in g["rows"]), note])
    table(ws, 4, ["Rank", "Machine", "Cause", "Times", "Downtime (hrs)", "First", "Last", "Work orders", "Note"],
          [6, 11, 20, 7, 10, 11, 11, 44, 24], data, top=min(3, len(data)))
    if not data:
        ws.cell(row=5, column=1, value=f"No machine and cause came up {min_times} or more times.")

    ws = wb.create_sheet("By machine")
    title(ws, "Failures by machine", "Every counted failure with a machine, most first. Repeats and one-time failures together.")
    per = OrderedDict()
    for g in groups:
        p = per.setdefault(g["machine"], {"times": 0, "down": 0, "causes": []})
        p["times"] += g["times"]
        p["down"] += g["down"]
        p["causes"].append(f"{g['cause']} {g['times']}")
    order = sorted(per.items(), key=lambda kv: (-kv[1]["times"], -kv[1]["down"], kv[0]))
    table(ws, 4, ["Machine", "Failures", "Downtime (hrs)", "Causes"], [11, 9, 10, 60],
          [[m, p["times"], hours(p["down"]), ", ".join(p["causes"])] for m, p in order])

    ws = wb.create_sheet("Evidence")
    title(ws, "Evidence: every counted failure", "Repeats first, in rank order, then one-time failures. Check the words against your log.")
    data = []
    rank_of = {id(g): i for i, g in enumerate(repeats, start=1)}
    for g in groups:
        for r in g["rows"]:
            data.append([rank_of.get(id(g), ""), g["machine"], g["cause"], r["wo"], r["date"],
                         r["down"] if r["down"] is not None else "", r["asset_as_written"], r["evidence"], r["check"]])
    table(ws, 4, ["Rank", "Machine", "Cause", "WO", "Opened", "Down (min)", "As written", "Evidence", "Check"],
          [6, 11, 20, 12, 11, 9, 20, 60, 30], data)

    ws = wb.create_sheet("Check these")
    title(ws, "Check these by hand", "Rows Claude was not sure about, and failures with no machine or no cause. They are not in the ranking until you fix them.")
    data = []
    for r in rows:
        why = r["check"]
        if r["counts"] == "yes" and not r["machine"]:
            why = why or "no machine"
        if r["counts"] == "yes" and not r["cause"]:
            why = why or "no cause"
        if why:
            data.append([r["wo"], r["date"], r["asset_as_written"], r["machine"], r["cause"], r["counts"], r["evidence"], why])
    table(ws, 4, ["WO", "Opened", "As written", "Machine", "Cause", "Counts", "Evidence", "Why"],
          [12, 11, 20, 11, 20, 8, 50, 34], data)

    ws = wb.create_sheet("Not counted")
    title(ws, "Not counted as failures", "PMs, inspections, project work, and duplicates. Make sure no real breakdown is hiding here.")
    table(ws, 4, ["WO", "Opened", "As written", "Machine", "Evidence"], [12, 11, 20, 11, 70],
          [[r["wo"], r["date"], r["asset_as_written"], r["machine"], r["evidence"]] for r in rows if r["counts"] == "no"])

    wb.save(out)
    return repeats, counted, data


def main():
    ap = argparse.ArgumentParser(description="Rank repeat failures from tagged-work-orders.csv")
    ap.add_argument("tagged", help="the tagged work order file (CSV)")
    ap.add_argument("--out", default="repeat-failures.xlsx", help="the Excel file to write")
    ap.add_argument("--min", type=int, default=2, help="times before it counts as a repeat (default 2)")
    a = ap.parse_args()
    src = Path(a.tagged)
    if not src.exists():
        print(f"Cannot find {src}.")
        sys.exit(1)
    rows = read_rows(src)
    repeats, counted, check = build(rows, src, a.out, a.min)
    print(f"Read {len(rows)} work orders. {len(counted)} counted as failures. {len(check)} to check by hand.")
    print(f"Repeat failures (same machine and cause, {a.min} or more times):")
    for i, g in enumerate(repeats, start=1):
        extra = f", {g['no_down']} with no downtime" if g["no_down"] else ""
        print(f"  {i}. {g['machine']} {g['cause']}: {g['times']} times, {hours(g['down'])} hrs down{extra}")
    print(f"Wrote {a.out}")


if __name__ == "__main__":
    main()
