#!/usr/bin/env python3
"""Verify the classroom quotation-to-shipment document chain.

Usage: python verify-commercial-document-set.py [folder]
The folder must contain the four XLSX files created in the exercise.
"""

from __future__ import annotations

import math
import sys
import unicodedata
from dataclasses import dataclass
from pathlib import Path
from typing import Iterable

from openpyxl import load_workbook


@dataclass(frozen=True)
class OrderLine:
    sku: str
    quantity: int
    unit: str
    unit_price: float
    cartons: int
    net_weight: float
    gross_weight: float
    cbm: float


LINES = (
    OrderLine("RD-CW1232", 100, "set", 10.25, 10, 170.0, 185.0, 0.681),
    OrderLine("RD-SW2412", 60, "set", 18.50, 10, 180.0, 195.0, 0.907),
    OrderLine("RD-AW0812", 600, "pcs", 4.15, 10, 205.0, 220.0, 0.568),
    OrderLine("RD-HK0910", 1000, "set", 2.50, 10, 128.0, 140.0, 0.487),
    OrderLine("RD-PL0300", 240, "set", 6.50, 10, 150.0, 165.0, 0.495),
)

FILES = {
    "quote": "quote-working.xlsx",
    "pi": "proforma-invoice.xlsx",
    "invoice": "commercial-invoice.xlsx",
    "packing": "packing-list.xlsx",
}


def norm(value: object) -> str:
    """Compare worksheet labels while ignoring case, whitespace and punctuation."""
    text = unicodedata.normalize("NFKC", str(value or "")).casefold()
    return "".join(char for char in text if char.isalnum())


def as_number(value: object) -> float | None:
    if isinstance(value, bool):
        return None
    try:
        return float(value)
    except (TypeError, ValueError):
        return None


def close(actual: object, expected: float) -> bool:
    number = as_number(actual)
    return number is not None and math.isclose(number, expected, rel_tol=0, abs_tol=0.001)


def all_cells(book) -> Iterable[object]:
    for sheet in book.worksheets:
        for row in sheet.iter_rows():
            yield from row


def contains(book, expected: str) -> bool:
    needle = norm(expected)
    return any(needle in norm(cell.value) for cell in all_cells(book) if cell.value is not None)


def formula(value: object) -> bool:
    return isinstance(value, str) and value.startswith("=")


def header_map(sheet, required: dict[str, set[str]]) -> tuple[int, dict[str, int]] | None:
    for row_index, row in enumerate(sheet.iter_rows(values_only=True), start=1):
        labels = {norm(value): index for index, value in enumerate(row, start=1) if value is not None}
        mapping: dict[str, int] = {}
        for field, aliases in required.items():
            for alias in aliases:
                if alias in labels:
                    mapping[field] = labels[alias]
                    break
        if len(mapping) == len(required):
            return row_index, mapping
    return None


COMMERCIAL_HEADERS = {
    "sku": {"sku", "productcode"},
    "quantity": {"qty", "quantity", "orderqty"},
    "unit": {"unit"},
    "price": {"unitprice", "unitpriceusd", "priceusd"},
    "amount": {"amount", "lineamount", "amountusd", "lineamountusd", "linetotal"},
}

PACKING_HEADERS = {
    "sku": {"sku", "productcode"},
    "quantity": {"qty", "quantity", "orderqty"},
    "unit": {"unit"},
    "cartons": {"cartons", "ctn", "ctns"},
    "net_weight": {"nwkg", "netweightkg", "netweight"},
    "gross_weight": {"gwkg", "grossweightkg", "grossweight"},
    "cbm": {"cbm", "totalcbm"},
}


def table_rows(book, headers: dict[str, set[str]], errors: list[str], label: str):
    for sheet in book.worksheets:
        found = header_map(sheet, headers)
        if not found:
            continue
        header_row, columns = found
        rows: dict[str, tuple[int, dict[str, object]]] = {}
        for row_index in range(header_row + 1, sheet.max_row + 1):
            sku = sheet.cell(row_index, columns["sku"]).value
            sku_text = str(sku or "").strip().upper()
            if sku_text in {line.sku for line in LINES}:
                rows[sku_text] = (
                    row_index,
                    {field: sheet.cell(row_index, column).value for field, column in columns.items()},
                )
        if len(rows) == len(LINES):
            return sheet, rows
    errors.append(f"{label}: no line-item table with the required English columns was found.")
    return None, {}


def total_row(sheet, start_row: int) -> int | None:
    for row_index in range(start_row, sheet.max_row + 1):
        if "total" in norm(sheet.cell(row_index, 1).value):
            return row_index
    return None


def require_text(book, label: str, values: tuple[str, ...], errors: list[str]) -> None:
    for value in values:
        if not contains(book, value):
            errors.append(f"{label}: missing required field/value: {value}")


def verify_commercial_lines(book, label: str, errors: list[str]) -> None:
    """Verify the shared commercial line schema once for quote, PI and invoice."""
    require_text(
        book,
        label, ("Yongkang Ruida Tools Co., Ltd.", "Müller Werkzeuge GmbH", "FOB Ningbo"), errors,
    )
    sheet, rows = table_rows(book, COMMERCIAL_HEADERS, errors, label)
    if sheet is None:
        return
    for line in LINES:
        row_index, values = rows[line.sku]
        if not close(values["quantity"], line.quantity):
            errors.append(f"{label}: {line.sku} quantity must be {line.quantity}.")
        if norm(values["unit"]) != norm(line.unit):
            errors.append(f"{label}: {line.sku} unit must be {line.unit}.")
        if not close(values["price"], line.unit_price):
            errors.append(f"{label}: {line.sku} unit price must be USD {line.unit_price:.2f}.")
        if not formula(values["amount"]):
            errors.append(f"{label}: {line.sku} Amount must remain an Excel formula (row {row_index}).")
    if not any(formula(cell.value) and "SUM(" in cell.value.upper() for cell in all_cells(book)):
        errors.append(f"{label}: missing a formula-driven Grand Total.")


def verify_quote_document(path: Path, errors: list[str]) -> None:
    label = "Quotation"
    book = load_workbook(path, data_only=False)
    require_text(book, label, ("QUOTATION",), errors)
    verify_commercial_lines(book, label, errors)


def verify_commercial_document(path: Path, label: str, number: str, title: str, errors: list[str]):
    book = load_workbook(path, data_only=False)
    require_text(
        book,
        label,
        (
            title,
            number,
            "MW-PO-260714",
            "Yongkang Ruida Tools Co., Ltd.",
            "Müller Werkzeuge GmbH",
            "FOB Ningbo",
            "Hamburg",
            "30% T/T deposit, 70% before shipment",
        ),
        errors,
    )
    verify_commercial_lines(book, label, errors)


def verify_packing_document(path: Path, errors: list[str]) -> None:
    label = "Packing List"
    book = load_workbook(path, data_only=False)
    require_text(
        book,
        label,
        (
            "PACKING LIST",
            "PL-2026-0818",
            "MW-PO-260714",
            "Yongkang Ruida Tools Co., Ltd.",
            "Müller Werkzeuge GmbH",
            "China",
            "Ningbo",
            "Hamburg",
            "MÜLLER / MW-PO-260714 / MADE IN CHINA",
        ),
        errors,
    )
    sheet, rows = table_rows(book, PACKING_HEADERS, errors, label)
    if sheet is None:
        return
    for line in LINES:
        row_index, values = rows[line.sku]
        if not close(values["quantity"], line.quantity):
            errors.append(f"{label}: {line.sku} quantity must be {line.quantity}.")
        if norm(values["unit"]) != norm(line.unit):
            errors.append(f"{label}: {line.sku} unit must be {line.unit}.")
        for field in ("cartons", "net_weight", "gross_weight", "cbm"):
            if not formula(values[field]):
                errors.append(f"{label}: {line.sku} {field} must remain an Excel formula (row {row_index}).")
    header_row, columns = header_map(sheet, PACKING_HEADERS)
    for column in range(1, sheet.max_column + 1):
        header = norm(sheet.cell(header_row, column).value)
        if any(token in header for token in ("price", "amount", "usd", "value")):
            errors.append(f"{label}: price/value column is not allowed ({sheet.cell(header_row, column).value}).")
    formula_text = " ".join(
        str(cell.value).upper() for cell in all_cells(book) if formula(cell.value)
    )
    if formula_text.count("SUM(") < 4:
        errors.append(f"{label}: totals for Cartons, N.W., G.W. and CBM must use formulas.")
    total = total_row(sheet, header_row + 1)
    if total is None:
        errors.append(f"{label}: missing Total row.")
        return
    totals = {"cartons": 50, "net_weight": 833, "gross_weight": 905, "cbm": 3.138}
    for field in totals:
        if not formula(sheet.cell(total, columns[field]).value):
            errors.append(f"{label}: Total {field} must remain an Excel formula.")

    cached_book = load_workbook(path, data_only=True)
    cached_sheet, cached_rows = table_rows(cached_book, PACKING_HEADERS, [], label)
    if cached_sheet is not None:
        for line in LINES:
            _, values = cached_rows[line.sku]
            expected = {
                "cartons": line.cartons,
                "net_weight": line.net_weight,
                "gross_weight": line.gross_weight,
                "cbm": line.cbm,
            }
            for field, value in expected.items():
                if values[field] is not None and not close(values[field], value):
                    errors.append(f"{label}: {line.sku} {field} formula result is incorrect.")
        cached_total = total_row(cached_sheet, header_row + 1)
        if cached_total is not None:
            for field, value in totals.items():
                actual = cached_sheet.cell(cached_total, columns[field]).value
                if actual is not None and not close(actual, value):
                    errors.append(f"{label}: Total {field} formula result is incorrect.")


def main() -> int:
    folder = Path(sys.argv[1]) if len(sys.argv) > 1 else Path.cwd()
    paths = {key: folder / filename for key, filename in FILES.items()}
    errors: list[str] = []
    for filename in paths.values():
        if not filename.is_file():
            errors.append(f"Missing required output: {filename.name}")
    if errors:
        print("Commercial document set: failed")
        print("\n".join(f"- {error}" for error in errors))
        return 1

    verify_quote_document(paths["quote"], errors)
    verify_commercial_document(paths["pi"], "Proforma Invoice", "PI-2026-0715", "PROFORMA INVOICE", errors)
    verify_commercial_document(paths["invoice"], "Commercial Invoice", "CI-2026-0818", "COMMERCIAL INVOICE", errors)
    verify_packing_document(paths["packing"], errors)
    if errors:
        print("Commercial document set: failed")
        print("\n".join(f"- {error}" for error in errors))
        return 1
    print("Commercial document set: passed (5 SKUs, USD 8,685.00, 50 CTN, N.W. 833 kg, G.W. 905 kg, 3.138 CBM).")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())