"""Compare the Incoming inspection worksheet's CSV export with two rounding rules.

Save the sheet as a UTF-8, comma-delimited CSV in Excel or LibreOffice.
Run: python3 compare-rounding.py excel-migration-rounding.csv
"""

import argparse
import csv
import sys
from decimal import Decimal, ROUND_HALF_UP


COLUMNS = ["Shipment", "Inspected", "Accepted", "Acceptance (%)", "Rounded (%)", "Decision"]


def decision(rounded):
    return "PASS" if rounded >= 95 else "REVIEW"


def compare(rows):
    if next(rows, None) != COLUMNS:
        raise ValueError("expected the six original column headings in worksheet order")

    shipments = set()
    differences = 0
    for line, row in enumerate(rows, start=2):
        if len(row) != len(COLUMNS):
            raise ValueError(f"line {line}: expected six fields")
        shipment, inspected, accepted, _, workbook_pct, workbook_decision = row
        if not shipment.strip() or shipment in shipments:
            raise ValueError(f"line {line}: expected a unique shipment ID")
        shipments.add(shipment)
        try:
            inspected, accepted, workbook_pct = map(int, (inspected, accepted, workbook_pct))
        except ValueError as exc:
            raise ValueError(f"line {line}: counts and rounded percent must be integers") from exc
        if inspected <= 0 or not 0 <= accepted <= inspected:
            raise ValueError(f"line {line}: invalid accepted or inspected count")

        percent = Decimal(100 * accepted) / Decimal(inspected)
        default_pct = round(percent)
        corrected_pct = percent.quantize(Decimal(1), rounding=ROUND_HALF_UP)
        workbook = (workbook_pct, workbook_decision)
        corrected = (corrected_pct, decision(corrected_pct))
        matches = corrected == workbook
        differences += int(not matches)
        print(
            f"{shipment}: workbook {workbook_pct} {workbook_decision} | "
            f"default {default_pct} {decision(default_pct)} | "
            f"half-up {corrected_pct} {corrected[1]} | "
            f"{'MATCH' if matches else 'MISMATCH'}"
        )
    if not shipments:
        raise ValueError("no shipment rows to compare")
    print(f"Half-up matched {len(shipments) - differences}/{len(shipments)} workbook rows.")
    return 1 if differences else 0


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("csv_file", help="UTF-8, comma-delimited export of Incoming inspection")
    args = parser.parse_args()
    try:
        with open(args.csv_file, encoding="utf-8-sig", newline="") as source:
            return compare(csv.reader(source, strict=True))
    except (OSError, UnicodeError, csv.Error, ValueError) as exc:
        print(f"Could not compare: {exc}", file=sys.stderr)
        return 2


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