"""Compare two engine output workbooks.

Reads the 'Demand Planning' sheet from both files and compares per-(SKU,
sublabel, CW) values. Sublabel = the row label inside each SKU block
(e.g. 'Run Rate', 'Forecast', 'Total', 'Baseline', …). Both files are
expected to come from the engine's `build_dp_sheet` builder so the
structure matches exactly.

Threshold: differences below 0.1% (relative to the OLD value) are
treated as floating-point noise and counted as identical.

Usage:
    py -3.12 _compare_engine_outputs.py
    py -3.12 _compare_engine_outputs.py path/to/old.xlsx path/to/new.xlsx
"""
from __future__ import annotations

import sys
from collections import defaultdict
from pathlib import Path

import openpyxl


DEFAULT_OLD = "Polleo_Demand_Plan_OLD.xlsx"
DEFAULT_NEW = "Polleo_Demand_Plan.xlsx"
SHEET_NAME  = "Demand Planning"
THRESHOLD_PCT = 0.1           # ignore differences ≤ 0.1 %
MAX_DETAIL_ROWS = 100         # how many diff rows to print


def load_grid(path: str) -> dict:
    """Read the Demand Planning sheet into a dict.

    Returns:
        {(sku, sublabel, cw_label): float_value}

    Layout (matches engine's build_dp_sheet output):
      - Header row contains columns: SKU | Artikl | Grupacija | Oznaka |
        Label | RR-25 | ... | RR-0 | CW<n> | ... | Review | Promo
      - The 'Label' column (col 4) names the sub-row inside each block:
        '' (block header) / 'Baseline FC' / 'Planner Factor' /
        'Adjusted FC' / 'VP on-top' / 'VP regular' / 'MP on-top' /
        'MP regular' / 'On-top Total' / 'TOTAL DEMAND'.
      - The SKU code in column 0 is repeated on every row of the block.
      - We capture every numeric cell under any CW / Review / Promo header.
    """
    print(f"  Loading {path} ...", end="", flush=True)
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
    if SHEET_NAME not in wb.sheetnames:
        wb.close()
        raise ValueError(
            f"Sheet '{SHEET_NAME}' not found in {path}. "
            f"Sheets present: {wb.sheetnames}"
        )
    ws = wb[SHEET_NAME]
    rows = list(ws.iter_rows(values_only=True))
    wb.close()

    # Find the header row — first row whose column 0 is the literal "SKU"
    # and which contains ≥ 3 cells starting "CW".
    header_row_idx = None
    value_col_to_label: dict[int, str] = {}
    for r_idx, row in enumerate(rows):
        if not row:
            continue
        v0 = row[0]
        if not (isinstance(v0, str) and v0.strip() == "SKU"):
            continue
        cw_cells = [
            (i, str(v).strip())
            for i, v in enumerate(row)
            if isinstance(v, str) and str(v).strip().startswith("CW")
        ]
        if len(cw_cells) >= 3:
            header_row_idx = r_idx
            # Capture CW + Review + Promo + RR-* + any other forecast columns
            for i, v in enumerate(row):
                if not isinstance(v, str):
                    continue
                s = v.strip()
                if (s.startswith("CW") or s.startswith("RR-")
                        or s in ("Review", "Promo")):
                    value_col_to_label[i] = s
            break

    if header_row_idx is None:
        raise ValueError(
            f"Could not locate header row (col 0 = 'SKU', ≥ 3 'CW...' "
            f"columns) in {path}"
        )

    grid: dict = {}

    for r_idx, row in enumerate(rows):
        if r_idx <= header_row_idx:
            continue
        if not row or len(row) <= 4:
            continue

        v_sku = row[0]
        v_label = row[4]

        if not (isinstance(v_sku, str) and v_sku.strip()):
            continue
        sku = v_sku.strip()

        if isinstance(v_label, str) and v_label.strip():
            sublabel = v_label.strip()
        else:
            # Empty Label cell = the SKU-name "header" row of the block.
            sublabel = "__block_header__"

        # Read each value column for this row
        for col_idx, col_label in value_col_to_label.items():
            if col_idx >= len(row):
                continue
            val = row[col_idx]
            if val is None:
                continue
            try:
                fv = float(val)
            except (TypeError, ValueError):
                continue
            grid[(sku, sublabel, col_label)] = fv

    n_skus = len({k[0] for k in grid})
    print(f" {len(grid):,} cells, {n_skus:,} SKUs")
    return grid


def main() -> int:
    args = sys.argv[1:]
    old_path = args[0] if len(args) >= 1 else DEFAULT_OLD
    new_path = args[1] if len(args) >= 2 else DEFAULT_NEW

    if not Path(old_path).exists():
        print(f"ERROR: {old_path} does not exist")
        return 1
    if not Path(new_path).exists():
        print(f"ERROR: {new_path} does not exist")
        return 1

    print("\n=== Compare engine outputs ===")
    print(f"  OLD: {old_path}")
    print(f"  NEW: {new_path}")
    print(f"  Sheet: {SHEET_NAME}")
    print(f"  Noise threshold: {THRESHOLD_PCT}%\n")

    old = load_grid(old_path)
    new = load_grid(new_path)

    skus_old = {k[0] for k in old}
    skus_new = {k[0] for k in new}
    only_in_old = skus_old - skus_new
    only_in_new = skus_new - skus_old
    common_skus = skus_old & skus_new

    common_keys = set(old) & set(new)
    only_old_cells = set(old) - set(new)
    only_new_cells = set(new) - set(old)

    diffs: list[tuple[tuple, float, float, float]] = []
    identical_cells = 0
    sku_status: dict[str, dict] = defaultdict(lambda: {"identical": 0, "diff": 0})

    for k in common_keys:
        ov = old[k]
        nv = new[k]
        # Exact match (including double-zero) → identical
        if ov == nv:
            identical_cells += 1
            sku_status[k[0]]["identical"] += 1
            continue
        # Both effectively zero → identical
        if abs(ov) < 1e-9 and abs(nv) < 1e-9:
            identical_cells += 1
            sku_status[k[0]]["identical"] += 1
            continue
        # Relative delta vs OLD (avoid divide-by-zero — use 1.0 floor)
        denom = abs(ov) if abs(ov) > 1e-9 else max(abs(nv), 1.0)
        pct_diff = abs(nv - ov) / denom * 100.0
        if pct_diff > THRESHOLD_PCT:
            diffs.append((k, ov, nv, pct_diff))
            sku_status[k[0]]["diff"] += 1
        else:
            identical_cells += 1
            sku_status[k[0]]["identical"] += 1

    sku_identical = sum(1 for s in common_skus if sku_status[s]["diff"] == 0)
    sku_different = sum(1 for s in common_skus if sku_status[s]["diff"] > 0)

    # ----- Summary -----
    print(f"\n=== SKU coverage ===")
    print(f"  OLD has {len(skus_old):,} SKUs | NEW has {len(skus_new):,} SKUs")
    print(f"  Common: {len(common_skus):,}")
    if only_in_old:
        sample = ", ".join(sorted(only_in_old)[:5])
        print(f"  Only in OLD ({len(only_in_old):,}): {sample}"
              + (" ..." if len(only_in_old) > 5 else ""))
    if only_in_new:
        sample = ", ".join(sorted(only_in_new)[:5])
        print(f"  Only in NEW ({len(only_in_new):,}): {sample}"
              + (" ..." if len(only_in_new) > 5 else ""))

    print(f"\n=== Cell comparison ===")
    print(f"  Common cells:                       {len(common_keys):>10,}")
    print(f"  Identical (within {THRESHOLD_PCT}%):           {identical_cells:>10,}")
    print(f"  Different (> {THRESHOLD_PCT}%):                {len(diffs):>10,}")
    if only_old_cells:
        print(f"  Cells only in OLD:                  {len(only_old_cells):>10,}")
    if only_new_cells:
        print(f"  Cells only in NEW:                  {len(only_new_cells):>10,}")

    # ----- Per-SKU verdict -----
    print(f"\n=== Per-SKU verdict ===")
    print(f"  {sku_identical}/{len(common_skus)} SKUs identical")
    print(f"  {sku_different}/{len(common_skus)} SKUs with differences > {THRESHOLD_PCT}%")

    # ----- Detail table for differences -----
    if diffs:
        diffs.sort(key=lambda x: -x[3])
        print(f"\n=== Top {min(MAX_DETAIL_ROWS, len(diffs))} differences "
              f"(sorted by delta% descending) ===")
        print(f"  {'SKU':<14} {'Sublabel':<22} {'CW':<8} "
              f"{'OLD':>14} {'NEW':>14} {'delta %':>10}")
        print(f"  {'-'*14} {'-'*22} {'-'*8} {'-'*14} {'-'*14} {'-'*10}")
        for (sku, sub, cw), ov, nv, pct in diffs[:MAX_DETAIL_ROWS]:
            sub_str = (sub or "-")[:21]
            print(f"  {sku:<14} {sub_str:<22} {cw:<8} "
                  f"{ov:>14,.2f} {nv:>14,.2f} {pct:>9.2f}%")
        if len(diffs) > MAX_DETAIL_ROWS:
            print(f"  ... and {len(diffs) - MAX_DETAIL_ROWS:,} more")

    # ----- Final headline -----
    print()
    if sku_different == 0 and not only_in_old and not only_in_new:
        print(f"  [OK] {sku_identical}/{len(common_skus)} SKUs identical "
              f"-- engine output unchanged within {THRESHOLD_PCT}% tolerance")
        return 0
    else:
        print(f"  [DIFF] {sku_identical}/{len(common_skus)} SKUs identical, "
              f"{sku_different} with differences > {THRESHOLD_PCT}%")
        return 1


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