"""
audit_engcut.py
================
ENGCUT — Engine de Auditoria Industrial em 5 camadas.

Gera audit_engcut.xlsx idêntico ao template original:
  SUMMARY · BAR_BALANCE · DROP_CLASSIFICATION · SCRAP_CLASSIFICATION_VALIDATION
  RECONCILIATION_AUDIT · INPUT_DATA_CERTIFICATION · STOCK_USED · GENERATED_STOCK
  GENERATED_SCRAP · UNCALCULATED

Fix F-014 (v6): imperial_parser.py integrado para leitura de comprimentos
imperiais fracionários e detecção de output_unit para ft decimal.
"""

import json, re
from datetime import datetime
from pathlib import Path
from collections import defaultdict

from openpyxl import load_workbook, Workbook
from openpyxl.styles import PatternFill, Font, Alignment
from openpyxl.utils import get_column_letter

from imperial_parser import (
    parse_piece_length_imperial,
    parse_bar_length_imperial,
    parse_kerf_imperial,
    is_imperial_base,
)
from input_loader import normalize_piece_length
from stock_engine import classify_drop

import sys as _sys
def _get_root():
    """Retorna o diretório raiz correto tanto em dev quanto em .exe (PyInstaller)."""
    if getattr(_sys, "frozen", False):
        return Path(_sys.executable).resolve().parent
    return Path(__file__).resolve().parent

ROOT = _get_root()
INPUT_PATH   = ROOT / "inputs"  / "input_001.json"
RESULT_PATH  = ROOT / "outputs" / "result.xlsx"
SUPPORT_PATH = ROOT / "outputs" / "audit_support.json"
DB_PATH      = ROOT / "database" / "stock_database.xlsx"
OUTPUT_PATH  = ROOT / "outputs" / "audit_engcut.xlsx"

# ── Style constants (matching original template exactly) ─────────────────────
FILL_HEADER = PatternFill("solid", fgColor="1F4E78")   # col headers
FILL_DARK   = PatternFill("solid", fgColor="002060")   # SUMMARY status keys
FILL_OK     = PatternFill("solid", fgColor="92D050")   # STATUS Ok
FILL_FAIL   = PatternFill("solid", fgColor="FF0000")   # STATUS FAIL
FILL_ROW_OK = PatternFill("solid", fgColor="C6EFCE")   # data row OK
FILL_ROW_FL = PatternFill("solid", fgColor="FFC7CE")   # data row FAIL
FILL_WARN   = PatternFill("solid", fgColor="FFEB9C")   # PASS_WITH_UNCALCULATED

FONT_HDR  = Font(name="Calibri", size=11, bold=True,  color="FFFFFF")
FONT_KEY  = Font(name="Calibri", size=11, bold=True,  color="000000")
FONT_VAL  = Font(name="Calibri", size=11, bold=False, color="000000")
FONT_DKEY = Font(name="Calibri", size=11, bold=True,  color="FFFFFF")  # dark key
FONT_OK   = Font(name="Calibri", size=11, bold=True,  color="000000")
FONT_FAIL = Font(name="Calibri", size=11, bold=True,  color="000000")

CENTER = Alignment(horizontal="center", vertical="center")
LEFT   = Alignment(horizontal="left",   vertical="center")


# ── Helpers ──────────────────────────────────────────────────────────────────

def _sf(v):
    try:
        return float(v) if v not in (None, "") else 0.0
    except (ValueError, TypeError):
        return 0.0


def _parse_metric_display(raw) -> float:
    """Parse a METRIC display value to float, handling PT-BR formatting.
    Handles: 510.9, '510,9', '6.500,00', '6500,00'
    Returns the numeric value as float (unit conversion applied separately).
    """
    if raw is None or raw == "":
        return 0.0
    if isinstance(raw, (int, float)):
        return float(raw)
    s = str(raw).strip()
    # PT-BR: remove thousand separator (dot before 3 digits), then comma→dot
    s = re.sub(r'\.(?=\d{3}(?:[,\s]|$|[a-zA-Z]))', '', s)
    s = s.replace(',', '.')
    # Strip any trailing unit text (e.g. " cm", " mm", " m")
    s = re.sub(r'\s*[a-zA-Z]+$', '', s).strip()
    try:
        return float(s)
    except (ValueError, TypeError):
        return 0.0


def _output_unit(input_data):
    """Return output_unit string from project config."""
    return str(
        input_data.get("output_unit", "") or
        input_data.get("project", {}).get("Unit_Output", "") or
        ("ft" if is_imperial_base(str(input_data.get("unit_system","METRIC")).upper()) else "cm")
    )


def _is_ft_output(input_data):
    ou = _output_unit(input_data).upper().replace(" ", "_").replace("-", "_")
    return ou in ("DECIMAL_FEET", "FT", "FEET", "FOOT", "FEET_FRACTIONAL_INCHES", "FEET_AND_FRACTIONAL_INCHES", "FT_IN")


def _metric_output_factor(input_data):
    """Return multiplier to convert METRIC CUT_PLAN bar value → mm (internal base).

    RC-007 CONGELAMENTO: BARRAS/ESTOQUE em sistema METRICO sempre "m",
    independente de output_unit. CUT_PLAN Net Used/Waste sao sempre m → mm = ×1000.
    """
    return 1000.0



def _detect_result_piece_display_unit(wb_result, input_data):
    """Detect the unit used by RESULT.xlsx CUT_LIST Piece Length.

    RC-007 CONGELAMENTO: PECAS em sistema METRICO sempre "cm", independente
    de output_unit (excel_exporter.py grava CUT_LIST sempre em cm).

    ENGCUT CUT_LIST storage rules (IMPERIAL, confirmed empirically):
      - decimal_inches            → CUT_LIST stores numeric inches
      - feet_and_fractional_inches → CUT_LIST stores numeric inches
      - decimal_feet              → CUT_LIST stores numeric feet

    The REPORT 'Unit' column reflects the *display* unit, not the CUT_LIST
    storage unit. For feet_and_fractional_inches the REPORT says 'ft-in'
    but the CUT_LIST is still in inches. We must NOT rely on REPORT unit
    to decide CUT_LIST conversion.

    Rule: only multiply by 12 when output_unit is decimal_feet (numeric ft).
    For feet_and_fractional_inches the CUT_LIST is already in inches.
    """
    base = str(input_data.get("unit_system", "METRIC")).upper().strip()

    if is_imperial_base(base):
        ou = _output_unit(input_data).lower().replace(" ", "_").replace("-", "_")
        # Only decimal_feet causes CUT_LIST to store in ft
        if ou in ("decimal_feet", "ft", "feet", "foot"):
            return "ft"
        # decimal_inches, feet_and_fractional_inches, fractional_inches → CUT_LIST in inches
        return "in"

    # METRIC: CUT_LIST Piece Length sempre cm (congelado)
    return "cm"


def _cut_list_piece_length_to_internal(raw, wb_result, input_data):
    """Convert CUT_LIST Piece Length to internal base (in or mm).

    CUT_LIST storage rules (empirically verified):
      IMPERIAL decimal_inches              -> numeric inches  (no conversion)
      IMPERIAL decimal_feet                -> numeric feet    (must x12)
      IMPERIAL feet_and_fractional_inches  -> numeric inches  (no conversion)
                                             Text like "7'- 6 1/2\"" also in inches.
      METRIC cm  -> numeric cm  (x10  -> mm)
      METRIC m   -> numeric m   (x1000 -> mm)
      METRIC mm  -> numeric mm  (x1)

    The CUT_LIST stores feet only for decimal_feet output mode. For
    feet_and_fractional_inches the CUT_LIST keeps inches; the fractional
    text appears only in CUT_PLAN/REPORT display columns.
    """
    base = str(input_data.get("unit_system", "METRIC")).upper().strip()
    unit = _detect_result_piece_display_unit(wb_result, input_data)

    if is_imperial_base(base):
        if isinstance(raw, (int, float)):
            val = float(raw)
            # Only decimal_feet stores ft in CUT_LIST numeric cells
            return val * 12.0 if unit == "ft" else val
        else:
            txt = str(raw or "").strip()
            # Text with both ' and " -> feet+inches notation -> always in inches
            if "'" in txt and '"' in txt:
                return parse_piece_length_imperial(txt)
            # Explicit ft suffix
            if "ft" in txt.lower():
                return parse_bar_length_imperial(txt)
            # Explicit inches
            if "in" in txt.lower() or '"' in txt:
                return parse_piece_length_imperial(txt)
            # Lone foot marker
            if "'" in txt:
                return parse_bar_length_imperial(txt)
            # Pure numeric text: apply same rule as numeric
            try:
                val = float(txt)
                return val * 12.0 if unit == "ft" else val
            except (ValueError, TypeError):
                return 0.0

    val = _parse_metric_display(raw)
    return val * {"m": 1000.0, "mm": 1.0}.get(unit, 10.0)

def _read_len(raw, base, context="piece", ft_output=False):
    """
    Read a length value from Excel cell.
    IMPERIAL: uses imperial_parser; if plain float and ft_output → multiply by 12
    METRIC:   plain float()
    Always returns the value in the INTERNAL unit (mm for METRIC, in for IMPERIAL).
    """
    if not is_imperial_base(base):
        return _sf(raw)
    # Imperial
    if isinstance(raw, (int, float)):
        val = float(raw)
        return val * 12.0 if ft_output else val
    # String
    parsed = parse_piece_length_imperial(raw) if context == "piece" else parse_bar_length_imperial(raw)
    if ft_output and context == "piece":
        try:
            plain = float(str(raw).strip())
            return plain * 12.0
        except (ValueError, TypeError):
            pass
    return parsed


def _m(value_internal, base):
    """Convert internal unit value → metres for display (METRIC) or ft (IMPERIAL)."""
    if is_imperial_base(base):
        return round(value_internal / 12.0, 4)     # in → ft
    return round(value_internal / 1000.0, 4)       # mm → m


def _cm(value_internal, base):
    """Convert internal unit → cm (METRIC) or in (IMPERIAL) for display."""
    if is_imperial_base(base):
        return round(value_internal, 4)             # already in
    return round(value_internal / 10.0, 4)         # mm → cm


def _fmt_len(base):
    """NumberFormat for bar/stock lengths."""
    return '0.000 "ft"' if is_imperial_base(base) else '0.000 "m"'


def _fmt_cm(base):
    """NumberFormat for piece/waste lengths."""
    return '0.000 "in"' if is_imperial_base(base) else '0.000 "cm"'


def _unit_label(base):
    return "ft" if is_imperial_base(base) else "m"


def _effective_tolerance_internal(input_data):
    """ENGCUT official effective tolerance in internal units.

    METRIC internal unit = mm.
    IMPERIAL internal unit = in.

    Official rule:
      METRIC   = max(input_tolerance, 1.0 mm)
      IMPERIAL = max(input_tolerance, 1/16 in)
    """
    base = str(input_data.get("unit_system", "METRIC")).upper().strip()
    raw_tol = _sf(input_data.get("tolerance", 0))
    if is_imperial_base(base):
        return max(raw_tol, 0.0625)
    return max(raw_tol, 1.0)


def _len_within_tolerance(actual, expected, tol):
    try:
        return abs(float(actual) - float(expected)) <= float(tol)
    except (ValueError, TypeError):
        return False


def _all_lengths_match_expected(lengths, expected_lengths, tol):
    """Return True if every output length matches at least one expected length."""
    if not lengths:
        return False
    if not expected_lengths:
        return False
    for length in lengths:
        if not any(_len_within_tolerance(length, exp, tol) for exp in expected_lengths):
            return False
    return True


def _hdr(ws, cols):
    """Write header row 1 with standard style."""
    for i, label in enumerate(cols, 1):
        c = ws.cell(1, i, label)
        c.fill = FILL_HEADER
        c.font = FONT_HDR
        c.alignment = CENTER
    ws.freeze_panes = "A2"


def _row_style(ws, row_idx, ok):
    fill = FILL_ROW_OK if ok else FILL_ROW_FL
    for cell in ws[row_idx]:
        if cell.value is not None or cell.column <= 13:
            cell.fill = fill
            cell.alignment = CENTER


def _set_widths(ws, widths):
    for col, w in widths.items():
        ws.column_dimensions[col].width = w


# ── Load sources ─────────────────────────────────────────────────────────────

def load_sources():
    # CORRECAO RISK-AUD-006 (AUD-006): tratamento explicito de arquivos ausentes.
    # Antes: FileNotFoundError propagava sem mensagem clara ao usuario.
    for path, label in [(RESULT_PATH, "result.xlsx"), (SUPPORT_PATH, "audit_support.json"), (INPUT_PATH, "input_001.json")]:
        if not path.exists():
            raise FileNotFoundError(
                f"\n\n  ARQUIVO AUSENTE: {label}\n"
                f"  Caminho esperado: {path}\n"
                f"  Execute run_engcut.py antes de audit_engcut.py.\n"
            )
    wb_result = load_workbook(RESULT_PATH, read_only=True, data_only=True)
    with open(SUPPORT_PATH, "r", encoding="utf-8") as f:
        support = json.load(f)
    with open(INPUT_PATH, "r", encoding="utf-8") as f:
        input_data = json.load(f)
    return wb_result, support, input_data


# ── BAR_BALANCE ───────────────────────────────────────────────────────────────

def build_bar_balance(wb_result, support, input_data):
    base        = str(input_data.get("unit_system", "METRIC")).upper().strip()
    ft_out      = _is_ft_output(input_data)
    result_piece_unit = _detect_result_piece_display_unit(wb_result, input_data)
    result_ft_out = (result_piece_unit == "ft")
    metric_fac  = _metric_output_factor(input_data)   # cm→mm=×10, m→mm=×1000, mm→mm=×1
    kerf_raw = input_data.get("kerf", 0)
    kerf_in  = parse_kerf_imperial(kerf_raw) if is_imperial_base(base) else _sf(kerf_raw)
    ul       = _unit_label(base)
    # ENGCUT official tolerance in internal units:
    # METRIC   = max(input_tolerance, 1 mm)
    # IMPERIAL = max(input_tolerance, 1/16")
    tol_bal = _effective_tolerance_internal(input_data)


    # CUT_LIST → pieces by bar
    ws_cl   = wb_result["CUT_LIST"]
    rows_cl = list(ws_cl.iter_rows(values_only=True))
    h_cl    = rows_cl[0]
    try:
        c_bar  = h_cl.index("Bar ID")
        c_len  = next(i for i, h in enumerate(h_cl) if h and "Piece Length" in str(h))
        c_cg   = next(i for i, h in enumerate(h_cl) if h and "Cut Group" in str(h))
    except (ValueError, StopIteration):
        c_bar, c_len, c_cg = 0, 6, 4

    pieces_by_bar = defaultdict(list)
    for r in rows_cl[1:]:
        if not r[c_bar] or r[c_bar] == "Bar ID":
            continue
        bid     = str(r[c_bar]).upper()
        cg_cl   = str(r[c_cg]) if c_cg < len(r) and r[c_cg] else ""
        raw_len = r[c_len]
        if is_imperial_base(base):
            len_in = _cut_list_piece_length_to_internal(raw_len, wb_result, input_data)
        else:
            # METRIC: CUT_LIST stores in display unit → convert to internal mm
            len_in = _cut_list_piece_length_to_internal(raw_len, wb_result, input_data)
        pieces_by_bar[(bid, cg_cl)].append(len_in)

    # CUT_PLAN → bar info
    ws_cp   = wb_result["CUT_PLAN"]
    rows_cp = list(ws_cp.iter_rows(values_only=True))
    h_cp    = rows_cp[0]
    try:
        c_bid   = h_cp.index("Bar ID")
        c_cg2   = next(i for i, h in enumerate(h_cp) if h and "Cut Group" in str(h))
        c_blen  = next(i for i, h in enumerate(h_cp) if h and "Bar Combination" in str(h))
        c_pct   = next(i for i, h in enumerate(h_cp) if h and "Piece Count" in str(h))
        c_used  = next(i for i, h in enumerate(h_cp) if h and "Net Used" in str(h))
        c_waste = next(i for i, h in enumerate(h_cp) if h and "Waste" in str(h))
    except (ValueError, StopIteration):
        c_bid, c_cg2, c_blen, c_pct, c_used, c_waste = 0, 1, 2, 3, 6, 7

    # REPORT → piece count per bar_id
    ws_rep   = wb_result["REPORT"]
    # not per-bar; we use CUT_LIST count as piece_count_real

    rows = []
    n_ok = n_fail = 0

    for r in rows_cp[1:]:
        if not r[c_bid] or r[c_bid] == "Bar ID":
            continue
        bid  = str(r[c_bid])
        cg   = str(r[c_cg2] or "")
        comb = str(r[c_blen] or "")
        n_pcs_rep = int(r[c_pct]) if r[c_pct] else 0

        # bar length from combination text — handle ALL output_unit formats:
        #   IMPERIAL: "180,00 in" | '180"' | '15,00 ft' | "14'- 5\"" | "15.00 ft"
        #   METRIC:   "6,50 m" | "650,00 cm" | "6.500,00 mm"
        comb_clean = str(r[c_blen] or "")

        if is_imperial_base(base):
            # imperial_parser handles all formats including PT-BR (180,00 in / 15,00 ft)
            bar_in = parse_bar_length_imperial(comb_clean)
            bar_ft = bar_in / 12.0
        else:
            # METRIC: detect unit suffix, parse number via _parse_metric_display (PT-BR safe)
            comb_up = comb_clean.upper()
            if "MM" in comb_up:
                bar_in = _parse_metric_display(comb_clean)          # mm → mm (internal)
                bar_ft = bar_in / 1000.0
            elif "CM" in comb_up:
                bar_in = _parse_metric_display(comb_clean) * 10.0   # cm → mm
                bar_ft = bar_in / 1000.0
            elif re.search(r'\d\s*m\b', comb_clean, re.IGNORECASE):
                bar_in = _parse_metric_display(comb_clean) * 1000.0  # m → mm
                bar_ft = bar_in / 1000.0
            else:
                bar_in = 0.0
                bar_ft = 0.0

        # Used/Waste from CUT_PLAN → convert to internal base (mm or in)
        # IMPERIAL: may be string (fractional) or float (decimal) → imperial_parser
        # METRIC:   numeric in display unit → _parse_metric_display + metric_fac
        raw_used  = r[c_used]
        raw_waste = r[c_waste]
        if is_imperial_base(base):
            # CUT_PLAN may contain decimal inches, decimal feet, or feet-and-fractional-inches.
            # Centralize conversion through _cut_list_piece_length_to_internal() so values
            # such as 7'- 6 7/16" become 90.4375 in exactly once.
            used_in  = _cut_list_piece_length_to_internal(raw_used,  wb_result, input_data)
            waste_in = _cut_list_piece_length_to_internal(raw_waste, wb_result, input_data)
        else:
            # METRIC: CUT_PLAN stores in output_unit → convert to mm
            used_in  = _parse_metric_display(raw_used)  * metric_fac
            waste_in = _parse_metric_display(raw_waste) * metric_fac


        pieces = pieces_by_bar.get((bid.upper(), cg), [])
        n_real  = len(pieces)
        piece_sum_in = sum(pieces)
        # Modelo físico correto: N peças produzem (N-1) kerfs entre elas.
        # O solver usa este modelo — o auditor deve usar a mesma equação.
        kerf_total   = max(0, n_real - 1) * kerf_in

        used_expected = piece_sum_in + kerf_total
        bal_exported  = bar_in - used_in  - waste_in
        bal_expected  = bar_in - used_expected - waste_in

        ok = abs(bal_expected) <= tol_bal

        if ok: n_ok   += 1
        else:  n_fail += 1

        # Convert to display unit (m or ft)
        rows.append({
            "cut_group":       cg,
            "bar_id":          bid.lower(),
            "bar_len":         _m(bar_in,          base) if not is_imperial_base(base) else round(bar_ft, 4),
            "pcs_report":      n_pcs_rep,
            "pcs_real":        n_real,
            "piece_sum":       _m(piece_sum_in,     base) if not is_imperial_base(base) else round(piece_sum_in/12, 4),
            "kerf_exp":        _m(kerf_total,       base) if not is_imperial_base(base) else round(kerf_total/12, 4),
            "used_exp_raw":    _m(used_in,          base) if not is_imperial_base(base) else round(used_in/12, 4),
            "used_expected":   _m(used_expected,    base) if not is_imperial_base(base) else round(used_expected/12, 4),
            "waste":           _m(waste_in,         base) if not is_imperial_base(base) else round(waste_in/12, 4),
            "bal_exp":         _m(bal_exported,     base) if not is_imperial_base(base) else round(bal_exported/12, 4),
            "bal_expected":    _m(bal_expected,     base) if not is_imperial_base(base) else round(bal_expected/12, 4),
            "ok":              ok,
        })

    return rows, n_ok, n_fail


# ── DROP_CLASSIFICATION ───────────────────────────────────────────────────────

def build_drop_classification(support, input_data):
    """
    REGRA OFICIAL (CONGELADA): a classificação SCRAP/STOCK de cada retalho é
    decidida em UM único local — stock_engine.classify_drop(). Esta função
    NÃO reimplementa a comparação >=/<; ela apenas RECALCULA a classificação
    esperada via classify_drop() (mesma assinatura, mesmos valores internos
    usados em generate_drops_from_cut_plan()) e compara com a classificação
    APLICADA (a lista — generated_stock ou generated_scrap — em que o motor
    efetivamente colocou o item).

    threshold_internal é preservado como veio do audit_support.json: None
    significa "threshold não configurado para este Cut Group" (classify_drop
    trata None como SCRAP sempre); um float (incluindo 0.0) é um threshold
    explicitamente configurado.
    """
    base = str(input_data.get("unit_system", "METRIC")).upper().strip()
    ul   = _unit_label(base)

    rows  = []
    n_ok  = n_fail = 0

    def _to_display(value_internal):
        return round(
            _m(value_internal, base) if not is_imperial_base(base) else value_internal / 12,
            4,
        )

    def _add_row(item, applied):
        cg              = item.get("cut_group", "")
        len_int         = _sf(item.get("length_internal", 0))
        thresh_internal = item.get("threshold_internal")
        expected        = classify_drop(len_int, thresh_internal)
        ok              = (expected == applied)
        return ok, {
            "cut_group": cg,
            "bar_id":    item.get("source_bar_id", ""),
            "waste":     _to_display(len_int),
            "threshold": _to_display(_sf(thresh_internal)),
            "applied":   applied,
            "expected":  expected,
            "ok":        ok,
        }

    for item in support.get("generated_stock", []):
        ok, row = _add_row(item, "STOCK")
        if ok: n_ok   += 1
        else:  n_fail += 1
        rows.append(row)

    for item in support.get("generated_scrap", []):
        ok, row = _add_row(item, "SCRAP")
        if ok: n_ok   += 1
        else:  n_fail += 1
        rows.append(row)

    return rows, n_ok, n_fail


# ── RECONCILIATION_AUDIT ──────────────────────────────────────────────────────


def _reconciliation_length_key(value):
    """Stable key for matching INPUT length with CUT_LIST length.

    Avoid Python banker's rounding / binary float artifacts such as:
      7.5375 ft * 12 = 90.449999999 -> round(..., 1) = 90.4
    while INPUT has 90.45 -> round(..., 1) = 90.5.
    Keep 0.001 internal unit precision: inches in IMPERIAL, mm in METRIC.
    """
    try:
        return round(float(value) + 1e-9, 3)
    except Exception:
        return 0.0

def build_reconciliation(wb_result, support, input_data):
    """Reconcile INPUT quantity against CUT_LIST output.

    Definitive matching rule for scaled use:
      PRIMARY MATCH  = Source Piece ID + Cut Group
      SECONDARY CHECK = length within ENGCUT official tolerance

    Length must not be part of the primary key because visual formats such as
    feet_and_fractional_inches round to 1/16" and can change 90.450" into
    90.4375" in the exported workbook. Quantity reconciliation must remain
    stable across all display units.
    """
    ws_cl   = wb_result["CUT_LIST"]
    rows_cl = list(ws_cl.iter_rows(values_only=True))
    h_cl    = rows_cl[0]
    try:
        c_src  = next(i for i, h in enumerate(h_cl) if h and "Source Piece ID" in str(h))
        c_cg   = next(i for i, h in enumerate(h_cl) if h and "Cut Group" in str(h))
        c_name = next(i for i, h in enumerate(h_cl) if h and "Piece Name" in str(h))
        c_plen = next(i for i, h in enumerate(h_cl) if h and "Piece Length" in str(h))
    except (ValueError, StopIteration):
        c_src, c_cg, c_name, c_plen = 3, 4, 5, 6

    base = str(input_data.get("unit_system", "METRIC")).upper().strip()
    tol  = _effective_tolerance_internal(input_data)

    cut_counts   = defaultdict(int)
    motor_counts = defaultdict(int)
    cut_lengths  = defaultdict(list)

    try:
        c_stype = next(i for i, h in enumerate(h_cl) if h and "Source Type" in str(h))
    except StopIteration:
        c_stype = 7

    for r in rows_cl[1:]:
        if not r[0] or r[0] == "Bar ID":
            continue

        pid = str(r[c_src])
        cg  = str(r[c_cg])
        key = (pid, cg)

        try:
            plen_internal = _cut_list_piece_length_to_internal(r[c_plen], wb_result, input_data)
        except (ValueError, TypeError):
            plen_internal = 0.0

        cut_counts[key] += 1
        cut_lengths[key].append(plen_internal)

        src_type = str(r[c_stype] or "").upper() if c_stype < len(r) else ""
        if "STOCK" not in src_type:
            motor_counts[key] += 1

    stock_qty = defaultdict(int)
    for st in support.get("stock_used", []):
        pid = str(st.get("source_piece_id", ""))
        cg  = str(st.get("cut_group", ""))
        stock_qty[(pid, cg)] += 1

    pieces = input_data.get("pieces", [])
    rows   = []
    n_ok   = n_fail = 0
    _uncalc_qty = defaultdict(int)
    for _u in support.get("uncalculated", []):
        _uid = str(_u.get("piece_id", ""))
        _ucg = str(_u.get("cut_group", ""))
        _uncalc_qty[(_uid, _ucg)] += int(_u.get("qty", 1))

    from collections import defaultdict as _dd
    grouped = _dd(lambda: {"name": "", "length": None, "qty": 0, "expected_lengths": []})

    for piece in pieces:
        pid    = str(piece.get("piece_id", ""))
        cg     = str(piece.get("cut_group", ""))
        name   = str(piece.get("piece_name", ""))
        qty    = int(piece.get("qty", 0))
        length = piece.get("length", 0)
        p_unit = str(piece.get("unit", "")).lower().strip() if piece.get("unit") else None

        try:
            norm_len = normalize_piece_length(length, base, input_unit=p_unit if p_unit else None)
        except (ValueError, TypeError):
            norm_len = 0.0

        key = (pid, cg)
        grouped[key]["name"] = name
        grouped[key]["length"] = length if grouped[key]["length"] is None else grouped[key]["length"]
        grouped[key]["qty"] += qty
        grouped[key]["expected_lengths"].append(norm_len)

    for key, info in grouped.items():
        pid, cg = key
        name   = info["name"]
        length = info["length"]
        qty    = info["qty"]

        qty_uncalc = _uncalc_qty.get(key, 0)

        if qty_uncalc >= qty:  # all pieces uncalculated → full EXEMPT
            n_ok += 1
            rows.append({
                "piece_name":   name,
                "cut_group":    cg,
                "length":       length,
                "qty_original": qty,
                "qty_stock":    0,
                "qty_motor":    0,
                "qty_cortada":  0,
                "qty_proc":     0,
                "bal_motor":    0,
                "bal_total":    0,
                "obs":          "EXEMPT - PIECE_LENGTH_GREATER_THAN_MAX_STOCK_LENGTH",
                "ok":           True,
            })
            continue

        qty_effective = qty - qty_uncalc  # pieces expected to be fabricated

        q_cut   = cut_counts.get(key, 0)
        q_stock = stock_qty.get(key, 0)
        q_motor = motor_counts.get(key, 0)
        q_proc  = q_cut

        bal_m = q_motor - (qty_effective - q_stock)
        bal_t = q_proc  - qty_effective

        qty_ok = (bal_t == 0)
        length_ok = _all_lengths_match_expected(
            cut_lengths.get(key, []),
            info["expected_lengths"],
            tol,
        ) if q_cut > 0 else False

        ok = qty_ok and length_ok

        if ok:
            _pref = f"PARTIAL [{qty_uncalc} UNCALCULATED] " if qty_uncalc > 0 else ""
            obs = f"{_pref}OK - Qty Cortada fecha INPUT com Stock + Motor"
            n_ok += 1
        else:
            if not qty_ok:
                obs = "FAIL - Qty cortada não fecha com INPUT"
            elif not length_ok:
                obs = "FAIL - Comprimento fora da tolerância oficial ENGCUT"
            else:
                obs = "FAIL"
            n_fail += 1

        rows.append({
            "piece_name":   name,
            "cut_group":    cg,
            "length":       length,
            "qty_original": qty,
            "qty_stock":    q_stock,
            "qty_motor":    q_motor,
            "qty_cortada":  q_cut,
            "qty_proc":     q_proc,
            "bal_motor":    bal_m,
            "bal_total":    bal_t,
            "obs":          obs,
            "ok":           ok,
        })

    return rows, n_ok, n_fail

# ── INPUT_DATA_CERTIFICATION ──────────────────────────────────────────────────

def build_input_certification(wb_result, input_data, uncalc_pids=None, uncalc_qty=None):
    """Certify that CUT_LIST output preserves INPUT quantities and lengths.

    Uses ENGCUT official effective tolerance:
      METRIC   = max(input_tolerance, 1 mm)
      IMPERIAL = max(input_tolerance, 1/16")

    Matching is by Source Piece ID + Cut Group; length is validated within
    tolerance to avoid false FAILs caused by display rounding.
    """
    base       = str(input_data.get("unit_system", "METRIC")).upper().strip()
    metric_fac = _metric_output_factor(input_data)
    tol        = _effective_tolerance_internal(input_data)

    ws_cl   = wb_result["CUT_LIST"]
    rows_cl = list(ws_cl.iter_rows(values_only=True))
    h_cl    = rows_cl[0]
    try:
        c_src  = next(i for i, h in enumerate(h_cl) if h and "Source Piece ID" in str(h))
        c_cg   = next(i for i, h in enumerate(h_cl) if h and "Cut Group" in str(h))
        c_plen = next(i for i, h in enumerate(h_cl) if h and "Piece Length" in str(h))
    except (ValueError, StopIteration):
        c_src, c_cg, c_plen = 3, 4, 6

    out_lens = defaultdict(list)
    for r in rows_cl[1:]:
        if not r[0] or r[0] == "Bar ID":
            continue
        pid = str(r[c_src])
        cg  = str(r[c_cg])
        raw = r[c_plen]
        val = _cut_list_piece_length_to_internal(raw, wb_result, input_data)
        out_lens[(pid, cg)].append(val)

    from collections import defaultdict as _dd
    inp_map = _dd(lambda: {"name": "", "length": None, "qty": 0, "expected_lengths": []})

    for piece in input_data.get("pieces", []):
        pid     = str(piece.get("piece_id", ""))
        cg      = str(piece.get("cut_group", ""))
        raw_len = _sf(piece.get("length", 0))
        p_unit  = str(piece.get("unit", "")).lower().strip()
        try:
            norm_len = normalize_piece_length(raw_len, base, input_unit=p_unit if p_unit else None)
        except (ValueError, TypeError):
            norm_len = 0.0
        key = (pid, cg)
        inp_map[key]["name"] = piece.get("piece_name", "")
        inp_map[key]["length"] = norm_len if inp_map[key]["length"] is None else inp_map[key]["length"]
        inp_map[key]["qty"] += int(piece.get("qty", 0))
        inp_map[key]["expected_lengths"].append(norm_len)

    if is_imperial_base(base):
        ul_len = "in"
        disp_fac = 1.0
    else:
        disp_fac = 1.0 / metric_fac
        ul_len = {1000.0: "m", 1.0: "mm"}.get(metric_fac, "cm")

    rows  = []
    n_ok  = n_fail = 0

    for (pid, cg), data in sorted(inp_map.items()):
        _qty_uncalc = (uncalc_qty or {}).get((pid, cg), 0)
        # Legacy fallback: if only the old-style set was passed, use it for full-exempt check
        if not uncalc_qty and uncalc_pids and pid in uncalc_pids:
            _qty_uncalc = data["qty"]

        if _qty_uncalc >= data["qty"]:  # all pieces uncalculated → full EXEMPT
            n_ok += 1
            representative_len = data["length"] or 0.0
            rows.append({
                "source_pid": pid,
                "piece_name": data["name"],
                "cut_group":  cg,
                "input_len":  round(representative_len * disp_fac, 4),
                "out_min":    None,
                "out_max":    None,
                "qty_input":  data["qty"],
                "qty_output": 0,
                "qty_bal":    0,
                "max_diff":   0.0,
                "ok":         True,
                "ul_len":     ul_len,
            })
            continue

        qty_effective = data["qty"] - _qty_uncalc

        ol = out_lens.get((pid, cg), [])
        expected_lengths = data["expected_lengths"]
        representative_len = data["length"] or 0.0

        length_ok = _all_lengths_match_expected(ol, expected_lengths, tol) if ol else False
        qty_ok = (len(ol) == qty_effective)
        ok = length_ok and qty_ok

        max_diff = 0.0
        if ol and expected_lengths:
            max_diff = max(min(abs(v - exp) for exp in expected_lengths) for v in ol)

        if ok:
            n_ok += 1
        else:
            n_fail += 1

        rows.append({
            "source_pid": pid,
            "piece_name": data["name"],
            "cut_group":  cg,
            "input_len":  round(representative_len * disp_fac, 4),
            "out_min":    round(min(ol) * disp_fac, 4) if ol else None,
            "out_max":    round(max(ol) * disp_fac, 4) if ol else None,
            "qty_input":  data["qty"],
            "qty_output": len(ol),
            "qty_bal":    qty_effective - len(ol),
            "max_diff":   round(max_diff * disp_fac, 4),
            "ok":         ok,
            "ul_len":     ul_len,
        })

    return rows, n_ok, n_fail, ul_len

# ── DATABASE VALIDATION ──────────────────────────────────────────────────────

def _count_database_failures():
    """
    CORRECAO CROSS-002 (AUD-006 RISK-AUD-001):
    Antes: "Database Stock/Scrap Failures" estava hardcoded como "0".
    Agora: conta falhas reais no banco de retalhos.

    Conta:
      - Registros em QUARANTINE (itens isolados por origem desconhecida)
      - Registros AVAILABLE em STOCK com campos obrigatorios ausentes
        (source_project, source_bar_id, entry_date) — excluindo entradas manuais STK-

    Retorna:
      int >= 0: numero de falhas encontradas
      -1: falha ao ler o banco (erro de leitura)
    """
    try:
        if not DB_PATH.exists():
            return 0
        wb = load_workbook(str(DB_PATH), read_only=True, data_only=True)
        count = 0

        # Conta itens em QUARANTINE
        if "QUARANTINE" in wb.sheetnames:
            ws_quar = wb["QUARANTINE"]
            for row in ws_quar.iter_rows(min_row=3, values_only=True):
                if row[0]:
                    count += 1

        # Conta itens AVAILABLE em STOCK com campos obrigatorios ausentes
        if "STOCK" in wb.sheetnames:
            ws_stock = wb["STOCK"]
            for row in ws_stock.iter_rows(min_row=5, values_only=True):
                if not row[0]:
                    continue
                stock_id = str(row[0]).strip()
                if stock_id.startswith("STK-"):
                    continue  # entradas manuais sao isentas
                status = str(row[1] or "").strip().upper()
                if status == "AVAILABLE":
                    source_project = str(row[8] or "").strip()
                    source_bar     = str(row[9] or "").strip()
                    entry_date     = row[10]
                    if not source_project or not source_bar or entry_date in (None, ""):
                        count += 1

        wb.close()
        return count
    except Exception:
        return -1  # indica falha na leitura do banco


def build_stock_reuse_balance(support, input_data):
    """
    CORRECAO CROSS-005 (AUD-006 RISK-AUD-002):
    Antes: BAR_BALANCE auditava apenas barras novas (STOCK REUSE excluido do CUT_PLAN).
    Agora: valida conservacao de material para barras reutilizadas.

    Para cada retalho consumido (stock_used):
      source_length = used_length + drop_gerado
      onde drop e o item em generated_stock/generated_scrap com source_bar_id == stock_id

    Tolerancia: mesma do BAR_BALANCE (_effective_tolerance_internal)
    """
    base = str(input_data.get("unit_system", "METRIC")).upper().strip()
    tol  = _effective_tolerance_internal(input_data)
    ul   = _unit_label(base)

    rows  = []
    n_ok  = n_fail = 0

    # Indexar drops por source_bar_id
    drops_by_source = defaultdict(float)
    for item in support.get("generated_stock", []):
        sid = item.get("source_bar_id", "")
        if sid:
            drops_by_source[sid] += _sf(item.get("length_internal", 0))
    for item in support.get("generated_scrap", []):
        sid = item.get("source_bar_id", "")
        if sid:
            drops_by_source[sid] += _sf(item.get("length_internal", 0))

    for s in support.get("stock_used", []):
        stock_id     = s.get("stock_id", "")
        cg           = s.get("cut_group", "")
        used_len     = _sf(s.get("used_length", 0))
        source_len   = _sf(s.get("source_length", 0))
        drop_len     = drops_by_source.get(stock_id, 0.0)
        # HOTFIX-007B: generate_drops_from_cut_plan() prefixa source_bar_id com
        # cut_group (CORRECAO RISK-SOLVER-005): "STK-1X2-001" -> "1x2_STK-1X2-001".
        # O lookup direto por stock_id nao encontra o drop -> drop_len = 0 -> FAIL.
        # Tentar com prefixo cut_group quando lookup direto retorna zero.
        if drop_len == 0.0 and cg:
            drop_len = drops_by_source.get(f"{cg}_{stock_id}", 0.0)

        balance = abs(source_len - used_len - drop_len)
        ok = balance <= tol

        if ok:
            n_ok += 1
        else:
            n_fail += 1

        rows.append({
            "cut_group":   cg,
            "stock_id":    stock_id,
            "source_len":  _m(source_len, base) if not is_imperial_base(base) else round(source_len / 12, 4),
            "used_len":    _m(used_len,   base) if not is_imperial_base(base) else round(used_len / 12, 4),
            "drop_len":    _m(drop_len,   base) if not is_imperial_base(base) else round(drop_len / 12, 4),
            "balance":     _m(balance,    base) if not is_imperial_base(base) else round(balance / 12, 4),
            "ok":          ok,
        })

    return rows, n_ok, n_fail


# ── WRITE XLSX ────────────────────────────────────────────────────────────────

def write_audit_xlsx(bb_rows, bb_ok, bb_fail,
                     dc_rows, dc_ok, dc_fail,
                     rec_rows, rec_ok, rec_fail,
                     cert_rows, cert_ok, cert_fail, cert_ul,
                     uncalc,
                     support, input_data,
                     sr_rows=None, sr_ok=0, sr_fail=0):

    base  = str(input_data.get("unit_system", "METRIC")).upper().strip()
    ul    = _unit_label(base)
    fmt_m = _fmt_len(base)
    fmt_c = _fmt_cm(base)

    project  = input_data.get("project", {})
    proj_name= project.get("Project_Name", "")
    kerf     = input_data.get("kerf", 0)
    now      = datetime.now()

    n_pieces = sum(r["qty_input"] for r in cert_rows)
    cert_ok_n  = sum(1 for r in cert_rows if r["ok"])
    cert_fail_n= sum(1 for r in cert_rows if not r["ok"])

    sr_rows    = sr_rows or []
    db_failures = _count_database_failures()

    _uncalc_controlled = len(uncalc) > 0 and all(
        u.get("reason") == "PIECE_LENGTH_GREATER_THAN_MAX_STOCK_LENGTH"
        for u in uncalc
    )
    all_ok = (bb_fail == 0 and dc_fail == 0 and
              rec_fail == 0 and cert_fail_n == 0 and
              (len(uncalc) == 0 or _uncalc_controlled) and sr_fail == 0)
    if all_ok and _uncalc_controlled:
        status_str = "PASS_WITH_UNCALCULATED"
    elif all_ok:
        status_str = "Ok"
    else:
        status_str = "FAIL"

    wb = Workbook()

    # ═══════════════════════════ SUMMARY ═══════════════════════════
    ws = wb.active
    ws.title = "SUMMARY"

    def srow(ws, row, key, val, dark_key=False):
        c1 = ws.cell(row, 1, key)
        c1.font = FONT_DKEY if dark_key else FONT_KEY
        c1.fill = FILL_DARK if dark_key else PatternFill()
        c1.alignment = LEFT
        c2 = ws.cell(row, 2, val)
        c2.font = FONT_VAL
        c2.alignment = LEFT

    srow(ws,  1, "Audit Date",                        now.strftime("%Y-%m-%dT%H:%M:%S"))
    srow(ws,  2, "Input File",                        str(INPUT_PATH))
    srow(ws,  3, "Result File",                       str(RESULT_PATH))
    srow(ws,  4, "Database File",                     str(DB_PATH))
    srow(ws,  5, "Project",                           proj_name)
    srow(ws,  6, "Unit System",                       base)
    srow(ws,  7, "Kerf",                              str(kerf))
    srow(ws,  8, "Bar Balance Failures",              str(bb_fail))
    srow(ws,  9, "Drop Rule Failures",                str(dc_fail))
    # CORRECAO CROSS-002: valor real validado, nao hardcoded
    db_failures_str = str(db_failures) if db_failures >= 0 else "ERR(leitura)"
    srow(ws, 10, "Database Stock/Scrap Failures",     db_failures_str)
    srow(ws, 11, "Reconciliation Failures",           str(rec_fail))
    srow(ws, 12, "Input Data Certification Failures", str(cert_fail_n))
    srow(ws, 13, "Project_Name",                      proj_name,  dark_key=True)
    srow(ws, 14, "Data:",                             now.strftime("%d/%m/%y %H:%M"), dark_key=True)
    srow(ws, 15, "Nº Total Piece Audit:",             str(n_pieces), dark_key=True)
    srow(ws, 16, "Piece Audit:",                      f"{cert_ok_n}/{len(cert_rows)}", dark_key=True)
    srow(ws, 17, "Piece Fail:",                       str(cert_fail_n), dark_key=True)
    srow(ws, 18, "Nº Total Bar Audit:",               str(len(bb_rows)), dark_key=True)
    srow(ws, 19, "Bar Audit:",                        f"{bb_ok}/{len(bb_rows)}", dark_key=True)
    srow(ws, 20, "Bar Fail:",                         str(bb_fail), dark_key=True)

    # Row 22: STATUS INPUT_CERTIFICATION
    c1 = ws.cell(22, 1, "STATUS INPUT_CERTIFICATION")
    c1.fill = FILL_DARK; c1.font = FONT_DKEY; c1.alignment = LEFT
    c2 = ws.cell(22, 2, "Ok" if cert_fail_n == 0 else "FAIL")
    c2.fill = FILL_OK if cert_fail_n == 0 else FILL_FAIL
    c2.font = FONT_OK; c2.alignment = CENTER

    # Row 24: STATUS FINAL
    c1 = ws.cell(24, 1, "STATUS FINAL")
    c1.fill = FILL_DARK; c1.font = FONT_DKEY; c1.alignment = LEFT
    c2 = ws.cell(24, 2, status_str)
    c2.fill = (FILL_OK if status_str == "Ok" else
               FILL_WARN if status_str == "PASS_WITH_UNCALCULATED" else
               FILL_FAIL)
    c2.font = FONT_OK; c2.alignment = CENTER

    ws.column_dimensions["A"].width = 36.0
    ws.column_dimensions["B"].width = 82.0

    # ═══════════════════════════ BAR_BALANCE ═══════════════════════════
    ws2 = wb.create_sheet("BAR_BALANCE")
    hdr_bb = [f"cut_group", f"bar_id",
              f"bar_length ({ul})", "piece_count_report", "piece_count_real",
              f"piece_sum ({ul})", f"kerf_expected ({ul})",
              f"used_exported ({ul})", f"used_expected ({ul})",
              f"waste ({ul})", f"balance_exported ({ul})",
              f"balance_expected ({ul})", "status"]
    _hdr(ws2, hdr_bb)

    for r in bb_rows:
        row = ws2.max_row + 1
        ok  = r["ok"]
        ws2.cell(row, 1,  r["cut_group"])
        ws2.cell(row, 2,  r["bar_id"])
        c = ws2.cell(row, 3,  r["bar_len"]);       c.number_format = fmt_m
        ws2.cell(row, 4,  r["pcs_report"])
        ws2.cell(row, 5,  r["pcs_real"])
        c = ws2.cell(row, 6,  r["piece_sum"]);     c.number_format = fmt_m
        c = ws2.cell(row, 7,  r["kerf_exp"]);      c.number_format = fmt_m
        c = ws2.cell(row, 8,  r["used_exp_raw"]);  c.number_format = fmt_m
        c = ws2.cell(row, 9,  r["used_expected"]); c.number_format = fmt_m
        c = ws2.cell(row, 10, r["waste"]);         c.number_format = fmt_m
        c = ws2.cell(row, 11, r["bal_exp"]);       c.number_format = fmt_m
        c = ws2.cell(row, 12, r["bal_expected"]);  c.number_format = fmt_m
        ws2.cell(row, 13, "OK" if ok else "FAIL")
        _row_style(ws2, row, ok)

    _set_widths(ws2, {"A":12,"B":12,"C":16,"D":20,"E":18,"F":20,
                      "G":23,"H":20,"I":20,"J":21,"K":24,"L":25,"M":12})

    # ═══════════════════════════ DROP_CLASSIFICATION ═══════════════════════════
    ws3 = wb.create_sheet("DROP_CLASSIFICATION")
    _hdr(ws3, [f"cut_group", f"bar_id", f"waste ({ul})",
               f"threshold ({ul})", "expected_destination", "status"])

    for r in dc_rows:
        row = ws3.max_row + 1
        ok  = r["ok"]
        ws3.cell(row, 1, r["cut_group"])
        ws3.cell(row, 2, r["bar_id"])
        c = ws3.cell(row, 3, r["waste"]);     c.number_format = fmt_m
        c = ws3.cell(row, 4, r["threshold"]); c.number_format = fmt_m
        ws3.cell(row, 5, r["expected"])
        ws3.cell(row, 6, "OK" if ok else "FAIL")
        _row_style(ws3, row, ok)

    _set_widths(ws3, {"A":12,"B":12,"C":21,"D":15,"E":22,"F":12})

    # ═══════════════ SCRAP_CLASSIFICATION_VALIDATION ═══════════════════════════
    # Mesma fonte de dados que DROP_CLASSIFICATION (dc_rows) — única regra
    # (stock_engine.classify_drop), apresentada no formato solicitado:
    # Grupo / Threshold / Comprimento do Retalho / Classificação Aplicada /
    # Classificação Esperada / Status.
    ws3b = wb.create_sheet("SCRAP_CLASSIFICATION_VALIDATION")
    _hdr(ws3b, ["Grupo", f"Threshold ({ul})", f"Comprimento do Retalho ({ul})",
                "Classificação Aplicada", "Classificação Esperada", "Status"])

    for r in dc_rows:
        row = ws3b.max_row + 1
        ok  = r["ok"]
        ws3b.cell(row, 1, r["cut_group"])
        c = ws3b.cell(row, 2, r["threshold"]); c.number_format = fmt_m
        c = ws3b.cell(row, 3, r["waste"]);     c.number_format = fmt_m
        ws3b.cell(row, 4, r["applied"])
        ws3b.cell(row, 5, r["expected"])
        ws3b.cell(row, 6, "PASS" if ok else "FAIL")
        _row_style(ws3b, row, ok)

    if ws3b.max_row == 1:
        ws3b.cell(2, 1, "(nenhum retalho gerado neste projeto)")

    _set_widths(ws3b, {"A":12,"B":15,"C":26,"D":24,"E":24,"F":10})

    # ═══════════════════════════ RECONCILIATION_AUDIT ═══════════════════════════
    ws4 = wb.create_sheet("RECONCILIATION_AUDIT")
    _hdr(ws4, ["piece_name","cut_group","length","qty_original","qty_stock",
               "qty_motor","qty_cortada","qty_processada",
               "balanco_motor","balanco_total","obs","status"])

    for r in rec_rows:
        row = ws4.max_row + 1
        ok  = r["ok"]
        ws4.cell(row, 1,  r["piece_name"])
        ws4.cell(row, 2,  r["cut_group"])
        ws4.cell(row, 3,  r["length"])
        ws4.cell(row, 4,  r["qty_original"])
        ws4.cell(row, 5,  r["qty_stock"])
        ws4.cell(row, 6,  r["qty_motor"])
        ws4.cell(row, 7,  r["qty_cortada"])
        ws4.cell(row, 8,  r["qty_proc"])
        ws4.cell(row, 9,  r["bal_motor"])
        ws4.cell(row, 10, r["bal_total"])
        ws4.cell(row, 11, r["obs"])
        ws4.cell(row, 12, "OK" if ok else "FAIL")
        _row_style(ws4, row, ok)

    _set_widths(ws4, {"A":14,"B":12,"C":12,"D":14,"E":12,"F":12,
                      "G":13,"H":16,"I":15,"J":15,"K":32,"L":12})

    # ═══════════════════════════ INPUT_DATA_CERTIFICATION ══════════════════════
    ws5 = wb.create_sheet("INPUT_DATA_CERTIFICATION")
    ul_c = cert_ul
    _hdr(ws5, ["Source Piece ID","Piece Name","Cut Group",
               f"Input Length ({ul_c})", f"Output Length Min ({ul_c})",
               f"Output Length Max ({ul_c})",
               "Input Qty","Output Qty","Qty Balance",
               f"Max Length Diff ({ul_c})","Status","Obs"])

    for r in cert_rows:
        row = ws5.max_row + 1
        ok  = r["ok"]
        ws5.cell(row, 1,  r["source_pid"])
        ws5.cell(row, 2,  r["piece_name"])
        ws5.cell(row, 3,  r["cut_group"])
        ws5.cell(row, 4,  r["input_len"])
        ws5.cell(row, 5,  r["out_min"])
        ws5.cell(row, 6,  r["out_max"])
        ws5.cell(row, 7,  r["qty_input"])
        ws5.cell(row, 8,  r["qty_output"])
        ws5.cell(row, 9,  r["qty_bal"])
        ws5.cell(row, 10, r["max_diff"])
        ws5.cell(row, 11, "OK" if ok else "FAIL")
        ws5.cell(row, 12, None)
        _row_style(ws5, row, ok)

    _set_widths(ws5, {"A":18,"B":22,"C":14,"D":18,"E":22,"F":22,
                      "G":12,"H":12,"I":12,"J":20,"K":12,"L":46})

    # ═══════════════════════════ STOCK_USED ════════════════════════════════════
    ws6 = wb.create_sheet("STOCK_USED")
    _hdr(ws6, ["Project","Stock ID","Cut Group",
               f"Used Length ({ul})", f"Source Length ({ul})",
               "Source Project","Source Bar ID","Used At"])

    for s in support.get("stock_used", []):
        row = ws6.max_row + 1
        ul_used  = s.get("display_unit") or s.get("unit") or ul
        used_v   = _m(_sf(s.get("used_length",  0)), base)
        src_len  = _m(_sf(s.get("source_length", 0)), base)
        ws6.cell(row, 1, s.get("project_name", s.get("source_project", "")))
        ws6.cell(row, 2, s.get("stock_id", ""))
        ws6.cell(row, 3, s.get("cut_group", ""))
        ws6.cell(row, 4, f"{used_v:.4f} {ul_used}")
        ws6.cell(row, 5, f"{src_len:.4f} {ul_used}")
        ws6.cell(row, 6, s.get("source_project", ""))
        ws6.cell(row, 7, s.get("source_bar_id", ""))
        ws6.cell(row, 8, s.get("used_at", ""))

    if ws6.max_row == 1:
        ws6.cell(2, 1, "(no stock reuse in this project)")

    _set_widths(ws6, {"A":19,"B":12,"C":12,"D":17,"E":19,"F":19,"G":15,"H":21})

    # ═══════════════════════════ GENERATED_STOCK ═══════════════════════════════
    ws7 = wb.create_sheet("GENERATED_STOCK")
    _hdr(ws7, ["Stock ID","Status","Cut Group","Profile",
               f"Length ({ul})","Unit","Source Project","Source Bar ID","Created At"])

    for s in support.get("generated_stock", []):
        row  = ws7.max_row + 1
        lval = _sf(s.get("length", 0))
        ul_s = s.get("unit", ul)
        ws7.cell(row, 1, s.get("stock_id", ""))
        ws7.cell(row, 2, s.get("status", "AVAILABLE"))
        ws7.cell(row, 3, s.get("cut_group", ""))
        ws7.cell(row, 4, s.get("profile", s.get("cut_group", "")))
        ws7.cell(row, 5, f"{lval:.1f} {ul_s}")
        ws7.cell(row, 6, ul_s)
        ws7.cell(row, 7, s.get("source_project", ""))
        ws7.cell(row, 8, s.get("source_bar_id", ""))
        ws7.cell(row, 9, s.get("created_at", ""))

    _set_widths(ws7, {"A":12,"B":12,"C":12,"D":12,"E":12,"F":12,"G":19,"H":15,"I":21})

    # ═══════════════════════════ GENERATED_SCRAP ═══════════════════════════════
    ws8 = wb.create_sheet("GENERATED_SCRAP")
    _hdr(ws8, ["Scrap ID","Status","Cut Group","Profile",
               f"Length ({ul})","Unit","Source Project","Source Bar ID","Created At"])

    for s in support.get("generated_scrap", []):
        row  = ws8.max_row + 1
        lval = _sf(s.get("length", 0))
        ul_s = s.get("unit", ul)
        ws8.cell(row, 1, s.get("scrap_id", ""))
        ws8.cell(row, 2, s.get("status", "SCRAP"))
        ws8.cell(row, 3, s.get("cut_group", ""))
        ws8.cell(row, 4, s.get("profile", s.get("cut_group", "")))
        ws8.cell(row, 5, f"{lval:.1f} {ul_s}")
        ws8.cell(row, 6, ul_s)
        ws8.cell(row, 7, s.get("source_project", ""))
        ws8.cell(row, 8, s.get("source_bar_id", ""))
        ws8.cell(row, 9, s.get("created_at", ""))

    _set_widths(ws8, {"A":12,"B":12,"C":12,"D":12,"E":12,"F":12,"G":19,"H":15,"I":21})

    # ═══════════════════════════ UNCALCULATED ══════════════════════════════════
    ws9 = wb.create_sheet("UNCALCULATED")
    ul_c2 = "in" if is_imperial_base(base) else "cm"
    _hdr(ws9, ["Cut Group","Reason","Piece ID","Piece Name",
               f"Piece Length ({ul_c2})",f"Max Stock ({ul})","Qty","Status"])

    for item in uncalc:
        row = ws9.max_row + 1
        ws9.cell(row, 1, item.get("cut_group", ""))
        ws9.cell(row, 2, item.get("reason", ""))
        ws9.cell(row, 3, item.get("piece_id", ""))
        ws9.cell(row, 4, item.get("piece_name", ""))
        ws9.cell(row, 5, item.get("length", ""))
        ws9.cell(row, 6, item.get("max_stock", ""))
        ws9.cell(row, 7, item.get("qty", ""))
        ws9.cell(row, 8, item.get("status", "UNCALCULATED"))

    _set_widths(ws9, {"A":12,"B":12,"C":12,"D":12,"E":19,"F":15,"G":12,"H":12})

    # ═══════════════════════════ STOCK_REUSE_BALANCE ═══════════════════════════
    # CORRECAO CROSS-005 (AUD-006 RISK-AUD-002): auditoria do material reutilizado.
    ws10 = wb.create_sheet("STOCK_REUSE_BALANCE")
    _hdr(ws10, ["cut_group","stock_id",
                f"source_length ({ul})", f"used_length ({ul})",
                f"drop_generated ({ul})", f"balance ({ul})", "status"])

    for r in sr_rows:
        row = ws10.max_row + 1
        ok  = r["ok"]
        ws10.cell(row, 1, r["cut_group"])
        ws10.cell(row, 2, r["stock_id"])
        c = ws10.cell(row, 3, r["source_len"]); c.number_format = fmt_m
        c = ws10.cell(row, 4, r["used_len"]);   c.number_format = fmt_m
        c = ws10.cell(row, 5, r["drop_len"]);   c.number_format = fmt_m
        c = ws10.cell(row, 6, r["balance"]);    c.number_format = fmt_m
        ws10.cell(row, 7, "OK" if ok else "FAIL")
        _row_style(ws10, row, ok)

    if ws10.max_row == 1:
        ws10.cell(2, 1, "(no stock reuse in this project)")

    _set_widths(ws10, {"A":12,"B":14,"C":20,"D":20,"E":22,"F":16,"G":12})

    # CORRECAO RISK-AUD-005 (AUD-006): write atomico via .tmp + rename.
    # Padrao identico ao excel_exporter.py: evita arquivo corrompido em crash.
    OUTPUT_PATH.parent.mkdir(parents=True, exist_ok=True)
    tmp_path = OUTPUT_PATH.with_suffix(".tmp")
    try:
        wb.save(tmp_path)
        tmp_path.replace(OUTPUT_PATH)
    except Exception as exc:
        tmp_path.unlink(missing_ok=True)
        raise RuntimeError(f"Falha ao gravar auditoria: {exc}") from exc

    print(f"audit_engcut.xlsx salvo em {OUTPUT_PATH}")
    print(f"  STATUS FINAL: {status_str}")
    print(f"  Bar Balance Failures:          {bb_fail}")
    print(f"  Drop Rule Failures:            {dc_fail}")
    print(f"  Reconciliation Failures:       {rec_fail}")
    print(f"  Input Certification Failures:  {cert_fail_n}")
    print(f"  Stock Reuse Balance Failures:  {sr_fail}")
    print(f"  Database Failures:             {db_failures_str}")
    print(f"  Uncalculated:                  {len(uncalc)}")


# ── Main ──────────────────────────────────────────────────────────────────────

def _enrich_support_financials(support, input_data):
    """Populate residual_value/revenue/weight in generated_stock/generated_scrap.

    Fields are always '' when written by run_engcut.py (pricing data unavailable
    at that stage). Uses same formula as cost_engine.build_cost_project and
    report_generator._drop_value_by_group — guarantees JSON == XLSX vitrine.
    Guard: skips if stock_rules is empty (test/dry-run context).
    """
    rules = input_data.get("stock_rules", {})
    if not rules:
        return

    base = str(input_data.get("unit_system", "METRIC")).upper()
    if is_imperial_base(base):
        prices  = {cg: _sf(r.get("price_bar_ft") or r.get("price_ft") or r.get("price_per_ft"))
                   for cg, r in rules.items()}
        weights = {cg: _sf(r.get("weight_lb_ft") or r.get("Peso (lb/ft)"))
                   for cg, r in rules.items()}
        junk    = {cg: _sf(r.get("scrap_price_lb") or r.get("Preço Ferro Velho ($/lb)") or r.get("junk_price_lb"))
                   for cg, r in rules.items()}
        div = 12.0
    else:
        prices  = {cg: _sf(r.get("price_bar_m") or r.get("price_m") or r.get("price_per_m"))
                   for cg, r in rules.items()}
        weights = {cg: _sf(r.get("weight_kg_m"))
                   for cg, r in rules.items()}
        junk    = {cg: _sf(r.get("scrap_price_kg") or r.get("Preço Ferro Velho ($/kg)") or r.get("junk_price"))
                   for cg, r in rules.items()}
        div = 1000.0

    for item in support.get("generated_stock", []):
        cg      = str(item.get("cut_group", ""))
        len_ft  = _sf(item.get("length_internal", 0)) / div
        price   = prices.get(cg, 0.0)
        w_rate  = weights.get(cg, 0.0)
        # No per-item rounding: exact float so aggregates reconcile with XLSX exactly
        item["residual_value"] = len_ft * price
        item["weight"]         = round(len_ft * w_rate, 6) if w_rate else ""

    for item in support.get("generated_scrap", []):
        cg      = str(item.get("cut_group", ""))
        len_ft  = _sf(item.get("length_internal", 0)) / div
        w_rate  = weights.get(cg, 0.0)
        j_rate  = junk.get(cg, 0.0)
        # No per-item rounding: exact float so aggregates reconcile with XLSX exactly
        item["revenue"] = len_ft * w_rate * j_rate
        item["weight"]  = round(len_ft * w_rate, 6) if w_rate else ""

    with open(SUPPORT_PATH, "w", encoding="utf-8") as fh:
        json.dump(support, fh, ensure_ascii=False, indent=2)


def run_audit():
    wb_result, support, input_data = load_sources()
    _enrich_support_financials(support, input_data)

    bb_rows,   bb_ok,   bb_fail   = build_bar_balance(wb_result, support, input_data)
    dc_rows,   dc_ok,   dc_fail   = build_drop_classification(support, input_data)
    uncalc                                 = support.get("uncalculated", [])
    _uncalc_qty = defaultdict(int)
    for _u in uncalc:
        _uid = str(_u.get("piece_id", ""))
        _ucg = str(_u.get("cut_group", ""))
        _uncalc_qty[(_uid, _ucg)] += int(_u.get("qty", 1))
    rec_rows,  rec_ok,  rec_fail  = build_reconciliation(wb_result, support, input_data)
    cert_rows, cert_ok, cert_fail, cert_ul = build_input_certification(wb_result, input_data, uncalc_qty=_uncalc_qty)
    # CORRECAO CROSS-005: adiciona auditoria de conservacao de material do stock reuse
    sr_rows,   sr_ok,   sr_fail   = build_stock_reuse_balance(support, input_data)

    wb_result.close()

    write_audit_xlsx(
        bb_rows,   bb_ok,   bb_fail,
        dc_rows,   dc_ok,   dc_fail,
        rec_rows,  rec_ok,  rec_fail,
        cert_rows, cert_ok, cert_fail, cert_ul,
        uncalc,
        support, input_data,
        sr_rows=sr_rows, sr_ok=sr_ok, sr_fail=sr_fail,
    )


def main():
    run_audit()


if __name__ == "__main__":
    run_audit()
# ── Patch applied inline above: fix _m() and bar parsing for both bases ──
