"""
ENGCUT Report Generator
Fills official Excel templates (documentation/General Reports/) with real project data.
Saves populated copies to outputs/reports/.

READS:
  inputs/input_001.json
  outputs/result.xlsx
  outputs/cost_result.xlsx
  outputs/audit_support.json
  database/stock_database.xlsx

WRITES:
  outputs/reports/<name>.xlsx   (fixed official output, overwritten on each generation)

DOES NOT MODIFY the original templates in documentation/General Reports/.
"""

import copy
import io
import os
import re
import json
import shutil
import datetime
from collections import defaultdict

from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Font, Border, Alignment

from imperial_parser import parse_imperial_length


# ─── Session Run ID ───────────────────────────────────────────────────────────

_SESSION_RUN_ID: str = ""
# Fallback: generated once at import time so all reports in the same Python session
# share the same Run ID even when set_run_id() is never explicitly called.
_FALLBACK_RUN_ID: str = datetime.datetime.now().strftime("RUN_%Y%m%d_%H%M%S")


def set_run_id(run_id: str) -> None:
    """Call this after a project run so all reports share the same Run ID."""
    global _SESSION_RUN_ID
    _SESSION_RUN_ID = run_id


def get_run_id() -> str:
    if _SESSION_RUN_ID:
        return _SESSION_RUN_ID
    # Try manifest written by run_project.py after the last pipeline run
    try:
        from manifest_writer import get_run_id as _manifest_rid
        rid = _manifest_rid()
        if rid:
            return rid
    except Exception:
        pass
    return _FALLBACK_RUN_ID


# Fallback: generated once at import time so all reports in the same Python
# session share the same "Data da Execucao" even when the manifest is missing.
_FALLBACK_GENERATED_AT: str = datetime.datetime.now().strftime("%d/%m/%Y %H:%M")


def get_generated_at() -> str:
    """RC-CERT-008 G-002: 'Data da Execucao' must be identical across all
    reports generated for the same Run ID. Reads the run's generated_at
    timestamp from runtime/run_manifest.json (same single source as
    get_run_id()), falling back to a session-wide timestamp."""
    try:
        from manifest_writer import read_manifest
        ts = read_manifest().get("generated_at", "")
        if ts:
            return datetime.datetime.fromisoformat(ts).strftime("%d/%m/%Y %H:%M")
    except Exception:
        pass
    return _FALLBACK_GENERATED_AT


# ─── Paths ────────────────────────────────────────────────────────────────────

def _eng():
    return os.path.abspath(os.path.dirname(__file__))


def _template(name):
    return os.path.join(_eng(), "documentation", "General Reports", name)


def _reports_dir():
    d = os.path.join(_eng(), "outputs", "reports")
    os.makedirs(d, exist_ok=True)
    return d


def _stamped(name):
    # RC: official reports are overwritten; do not create timestamp duplicates.
    return os.path.join(_reports_dir(), name)


# ─── Unit Conversion ──────────────────────────────────────────────────────────

_UNIT_FACTORS = {
    "mm": 1.0,
    "cm": 0.1,
    "m":  0.001,
    "ft": 1 / 304.8,
    "in": 1 / 25.4,
}


def _unit_factor(output_unit: str) -> float:
    return _UNIT_FACTORS.get(str(output_unit).lower(), 1.0)


# ─── Data Loaders ─────────────────────────────────────────────────────────────

def _load_json(rel_path):
    with open(os.path.join(_eng(), rel_path), encoding="utf-8") as f:
        return json.load(f)


def _criterios_description_map():
    """Mapa Cut Group -> Descricao lido direto da aba CRITERIOS do
    INPUT_sistema.xlsx (cadastro mestre/mandante de todos os perfis,
    linhas 13-200 — ver CLAUDE.md secao Cut Group dropdown).

    Usado como fallback quando stock_rules (escopo do projeto ativo, vindo
    de input_001.json) nao contem o cut_group do item de STOCK/SCRAP — caso
    de retalho inserido manualmente (ADD TO STOCK) ou herdado de projeto
    anterior cujo perfil nao faz parte do projeto corrente.
    """
    desc_map = {}
    path = os.path.join(_eng(), "inputs", "INPUT_sistema.xlsx")
    try:
        _wb_cr = load_workbook(path, data_only=True)
        if "CRITERIOS" in _wb_cr.sheetnames:
            _ws_cr = _wb_cr["CRITERIOS"]
            for _r in range(13, 201):
                _desc = str(_ws_cr.cell(_r, 1).value or "").strip()
                _cg   = str(_ws_cr.cell(_r, 3).value or "").strip()
                if _cg and _desc:
                    desc_map[_cg.upper().replace(" ", "")] = _desc
        _wb_cr.close()
    except Exception:
        pass
    return desc_map


def _wb_rows(rel_path, sheet, skip_rows=1, require_col1=False):
    wb = load_workbook(os.path.join(_eng(), rel_path), data_only=True)
    ws = wb[sheet]
    if require_col1:
        rows = [r for r in ws.iter_rows(min_row=1 + skip_rows, values_only=True)
                if r[0] is not None]
    else:
        rows = [r for r in ws.iter_rows(min_row=1 + skip_rows, values_only=True)
                if any(v is not None for v in r)]
    wb.close()
    return rows


def _cost_project_map():
    wb = load_workbook(os.path.join(_eng(), "outputs", "cost_result.xlsx"), data_only=True)
    ws = wb["COST_PROJECT"]
    m = {}
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, values_only=True):
        if row[0] and isinstance(row[0], str):
            m[row[0].strip()] = row[1] if len(row) > 1 else None
    wb.close()
    return m


def _cb_by_group(unit_system: str = ""):
    """Return dict {cut_group: {bars, bar_len_mm, total_len_mm, cost, rate}} from COST_BARS."""
    rows = _wb_rows("outputs/cost_result.xlsx", "COST_BARS", skip_rows=1)
    grp = defaultdict(lambda: {"bars": 0, "bar_len_mm": 0, "total_len_mm": 0.0,
                                "cost": 0.0, "rate": 0.0, "source": ""})
    for row in rows:
        cg = _safe(row[1])
        grp[cg]["bars"] += 1
        grp[cg]["bar_len_mm"] = _parse_bar_len(row[2], unit_system)   # single bar length (same for all)
        grp[cg]["total_len_mm"] += _parse_bar_len(row[2], unit_system)
        grp[cg]["cost"] += _num(row[8])
        grp[cg]["rate"] = _num(row[7])          # $/m (METRIC) or $/ft (IMPERIAL) - same for all bars in group
        grp[cg]["source"] = _safe(row[11])
    return dict(grp)


def _drop_value_by_group(pd):
    """RC-CERT-002 CS-003: per-cut-group $ breakdown of the project-level
    "Retalhos gerados para estoque" (REUSABLE STOCK) and "Receita venda
    ferro velho" (SCRAP) totals from COST_PROJECT. Same formula as
    cost_engine.build_cost_project, applied per drop from
    outputs/audit_support.json so the per-group sums reconcile exactly with
    the approved project totals (fonte unica)."""
    unit_system = project_data_unit_system(pd)
    rules = pd.get("stock_rules", {})
    if unit_system.startswith("MET"):
        prices  = {cg: _num(r.get("price_bar_m"))    for cg, r in rules.items()}
        weights = {cg: _num(r.get("weight_kg_m"))    for cg, r in rules.items()}
        junk    = {cg: _num(r.get("scrap_price_kg")) for cg, r in rules.items()}
        div = 1000.0
    else:
        prices  = {cg: _num(r.get("price_bar_ft"))   for cg, r in rules.items()}
        weights = {cg: _num(r.get("weight_lb_ft"))   for cg, r in rules.items()}
        junk    = {cg: _num(r.get("scrap_price_lb")) for cg, r in rules.items()}
        div = 12.0

    audit = _load_json("outputs/audit_support.json")
    stock_by_grp = defaultdict(float)
    for s in audit.get("generated_stock", []):
        cg = s.get("cut_group", "")
        stock_by_grp[cg] += _num(s.get("length_internal", 0)) / div * prices.get(cg, 0)

    scrap_by_grp = defaultdict(float)
    for s in audit.get("generated_scrap", []):
        cg = s.get("cut_group", "")
        scrap_by_grp[cg] += (_num(s.get("length_internal", 0)) / div
                              * weights.get(cg, 0) * junk.get(cg, 0))

    return dict(stock_by_grp), dict(scrap_by_grp)


def _cp_cost_by_group():
    """Return (dict {cut_group: total_piece_cost}, grand_total) from COST_PIECES."""
    rows = _wb_rows("outputs/cost_result.xlsx", "COST_PIECES", skip_rows=4)
    # PieceID(0) BarID(1) CutGroup(2) ... CustoPeca(8)
    grp = defaultdict(float)
    total = 0.0
    for row in rows:
        cg = _safe(row[2])
        cost = _num(row[8])
        grp[cg] += cost
        total += cost
    return dict(grp), total


def _cutlist_rows():
    """Load CUT_LIST from result.xlsx, return as list of dicts."""
    rows = _wb_rows("outputs/result.xlsx", "CUT_LIST", skip_rows=1)
    # cols: BarID(0) CutOrder(1) PieceID(2) SrcPieceID(3) CutGroup(4) PieceName(5)
    #       PieceLen(6) SourceType(7) UserTag(8) Zone(9) AssemblyTag(10)
    result = []
    for r in rows:
        result.append({
            "bar_id":       _safe(r[0]),
            "cut_order":    _safe(r[1]),
            "piece_id":     _safe(r[2]),
            "product_code": _safe(r[3]),
            "cut_group":    _safe(r[4]),
            "piece_name":   _safe(r[5]),
            "length_mm":    _num(r[6]),
            "source_type":  _safe(r[7]),
            "user_tag":     _safe(r[8]),
            "zone":         _safe(r[9]),
            "assembly_tag": _safe(r[10]),
        })
    return result


# ─── Placeholder Replacement ──────────────────────────────────────────────────

_PLACEHOLDER_ORDER = [
    "DD/MM/YYYY HH:MM",
    "PROJETO_XXXXXXXX",
    "Project Name",
    "Client Name",
    "BRA-XXXXX",
    "Project Description",
    "IMPERIAL / METRIC",
    "decimal_inches",
    "0.625 in",
    "0.063 in",
    "DD/MM/YYYY",
    "USD / BRL",
    "Address",
    "''''",
]


def _build_replacements(project_data):
    proj = project_data.get("project", {})
    kerf = project_data.get("kerf", proj.get("Kerf", 0))
    tol  = project_data.get("tolerance", proj.get("Tolerance", 0))
    unit_sys    = project_data.get("unit_system", proj.get("Unit_System", "METRIC"))
    output_unit = project_data.get("output_unit", proj.get("Unit_Output", "mm"))
    currency    = proj.get("Currency", "USD")

    raw = proj.get("Data_de_Inicio", "")
    try:
        start = datetime.datetime.fromisoformat(
            str(raw).replace("T00:00:00", "").split("T")[0]
        ).strftime("%d/%m/%Y")
    except Exception:
        start = str(raw)[:10]

    name = proj.get("Project_Name", "PROJECT")

    run_id = get_run_id()

    return {
        "Project Name":        proj.get("Project_Name", ""),
        "Client Name":         proj.get("Nome_Cliente", ""),
        "Address":             proj.get("Endereço_do_Projeto", ""),
        "BRA-XXXXX":           proj.get("Numero_do_Projeto", ""),
        "DD/MM/YYYY":          start,
        "Project Description": proj.get("Descrição_do_Projeto", ""),
        "IMPERIAL / METRIC":   unit_sys,
        "decimal_inches":      output_unit,
        "0.625 in":            str(kerf),
        "0.063 in":            str(tol),
        "DD/MM/YYYY HH:MM":    get_generated_at(),
        "PROJETO_XXXXXXXX":    run_id,
        "USD / BRL":           currency,
        "''''":                "",
    }


def _fill_placeholders(ws, replacements):
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell, MergedCell):
                continue
            if not isinstance(cell.value, str):
                continue
            val = cell.value.strip()
            if not (val.startswith("[") or val.startswith("'")):
                continue
            inner = val.lstrip("[").rstrip("]")
            for frag in _PLACEHOLDER_ORDER:
                if frag.lower() in inner.lower() or frag.lower() in val.lower():
                    cell.value = replacements.get(frag, "")
                    break


def _clear_rows(ws, first_row, last_row):
    for r in range(first_row, last_row + 1):
        for c in range(1, ws.max_column + 1):
            cell = ws.cell(r, c)
            if not isinstance(cell, MergedCell):
                cell.value = None


def _safe(v, default=""):
    return v if v is not None else default


def _num(v, default=0):
    try:
        return float(v)
    except (TypeError, ValueError):
        return default


def _parse_bar_len(v, unit_system: str) -> float:
    """Parse a bar-length value to the frozen bar display unit.
    IMPERIAL: fractional-inch strings like '360"' → ft (÷12).
    METRIC: pass-through float → m (already stored in m)."""
    if not unit_system.startswith("MET"):
        return parse_imperial_length(v, convert_ft_to_in=False) / 12.0
    return _num(v)


# ─── Official Template Column Widths ─────────────────────────────────────────
# Widths are set manually in the official templates and must be preserved exactly.
# The generator must NOT recalculate widths (no autofit). Apply after filling data.

_TEMPLATE_WIDTHS = {
    # ── Sheets with LARGURA= instruction cells — authoritative source ──────────
    "PROJECT_SUMMARY": {
        "A": 21, "B": 13, "C": 23, "D": 19, "E": 19,
        "F": 15, "G": 15, "H": 15, "I": 15, "J": 15, "K": 8,
    },
    "Purchase_order": {
        "A": 19, "B": 30, "C": 6, "D": 14, "E": 14,
        "F": 14, "G": 14, "H": 14, "I": 14, "J": 14,
    },
    "SUMMARY": {
        "A": 15, "B": 15, "C": 15, "D": 15, "E": 15,
        "F": 15, "G": 15, "H": 15, "I": 15,
    },
    "CUT LIST": {
        "A": 4, "B": 16, "C": 15, "D": 25, "E": 14,
        "F": 14, "G": 14, "H": 14, "I": 14, "J": 14, "K": 14,
    },
    "CUT PLAN": {
        "A": 15, "B": 15, "C": 15, "D": 15, "E": 10,
        "F": 38, "G": 15, "H": 15, "I": 15, "J": 15, "K": 15,
    },
    # ── Sheets without LARGURA= — from actual template column_dimensions ───────
    "STOCK": {
        "A": 19, "B": 14, "D": 14.5547, "E": 35, "F": 12,
        "H": 19.5547, "I": 13, "J": 20.2188, "K": 14, "L": 18,
    },
    "SCRAP": {
        "A": 19, "B": 9, "C": 12, "D": 15, "E": 22, "F": 15,
        "G": 13, "H": 17, "I": 30, "J": 15, "K": 22,
    },
    "HISTORY": {
        "A": 18, "B": 18, "C": 14, "D": 14, "E": 18,
        "F": 18, "G": 12, "H": 12, "I": 14, "J": 30,
        "K": 14, "L": 14, "M": 16, "N": 16,
    },
    "COST SUMMARY": {
        "A": 19.6641, "B": 10, "D": 16, "E": 15.4414, "F": 16.2188,
        "G": 16, "I": 14, "J": 15.332, "K": 10.6641, "M": 19.332,
    },
    "COST BY BAR": {
        "A": 3, "B": 10, "C": 14, "E": 10, "F": 12,
        "H": 10, "I": 15.6641, "J": 14, "K": 17.5547, "L": 12,
        "M": 16.8867, "N": 8.8867, "O": 8.8867,
    },
    "COST BY PIECE": {
        "A": 12, "B": 10, "C": 14, "E": 12, "F": 14.6641,
        "G": 12, "H": 10, "I": 17.4414, "J": 14,
    },
    "QR_DATA": {
        "A": 16.6641, "B": 7.8867, "C": 8.2188, "D": 9.8867, "E": 7,
        "F": 9.332, "G": 6, "H": 11.5547, "I": 10.8867, "J": 13.5547,
        "K": 14.1094, "L": 40, "M": 41, "N": 66.332, "O": 30.5547, "P": 8.2188,
    },
    "QR_LABEL": {
        "A": 3, "B": 23, "C": 9.332, "D": 10, "I": 30,
    },
    "THERMAL_CONFIG": {
        "A": 3, "B": 18, "C": 32, "D": 25.4414, "E": 24, "F": 22, "G": 36,
    },
}


def _apply_template_widths(ws) -> None:
    """Restore official template column widths. Called after data fill, never autofit."""
    widths = _TEMPLATE_WIDTHS.get(ws.title, {})
    for col_letter, width in widths.items():
        ws.column_dimensions[col_letter].width = width


# ─── Internationalization ─────────────────────────────────────────────────────

# Maps static English label text found in templates → language dictionary key.
# After _fill_placeholders(), call _apply_lang_labels(ws) to translate header cells.
_LABEL_MAP = {
    # ── Project header fields (all sheets) ───────────────────────────────────
    "PROJECT":                              "project",
    "CLIENT":                               "client",
    "PROJECT ADDRESS":                      "project_address",
    "PROJECT NUMBER":                       "project_number",
    "START DATE":                           "start_date",
    "DESCRIPTION":                          "description",
    "UNIT SYSTEM":                          "unit_system",
    "KERF":                                 "kerf",
    "TOLERANCE":                            "tolerance",
    "RUN DATE":                             "run_date",
    "RUN ID":                               "run_id",
    "CURRENCY":                             "currency",
    "OUTPUT DISPLAY":                       "output_display",
    "ENGINE PAYLOAD":                       "engine_payload",
    "OPTIMIZATION VERIFIED":                "optimization_verified",

    # ── Common column headers ─────────────────────────────────────────────────
    "PRODUCT CODE":                         "product_code",
    "CUT GROUP":                            "cut_group",
    "BAR ID":                               "bar_id",
    "PIECE ID":                             "piece_id",
    "ASSEMBLY TAG":                         "assembly_tag",
    "USER TAG":                             "user_tag",
    "AREA / ZONE":                          "area_zone",
    "SOURCE":                               "source",
    "UNIT":                                 "unit",
    "TOTAL":                                "total",
    "COLOR":                                "color",
    "Collor":                               "color",
    "YIELD":                                "yield_hdr",
    "YIELD %":                              "yield_pct_hdr",
    "BAR SIZE":                             "bar_size_hdr",
    "PIECES":                               "pieces_hdr",
    "BAR COMBINATION":                      "bar_combination_hdr",
    "MATERIAL":                             "material_hdr",
    "USED":                                 "used_hdr",
    "WASTE / DROP":                         "waste_drop_hdr",
    "WASTE":                                "waste_hdr",
    "LENGTH":                               "length_hdr",
    "QTY BARS":                             "qty_bars_hdr",
    "QTY PIECES":                           "qty_pieces_hdr",
    "QTY":                                  "qty_hdr",
    "NEW BAR":                              "new_bar_lbl",
    "UNCALCULATED":                         "uncalculated_lbl",
    "Description":                          "description_col",
    "Qty Bars":                             "qty_bars_col",
    "Bar Size":                             "bar_size_col",
    "Total Length":                         "total_length_col",
    "Estimate Cost":                        "estimate_cost_col",
    "Qty":                                  "qty_col",
    "Length":                               "length_col",
    "Status":                               "status_col",
    "SOURCE TYPE":                          "source_type_hdr",

    # ── Report 01 — Project Summary ───────────────────────────────────────────
    "EngCut Engine — Project Summary":      "rpt_title_project_summary",
    "Total Bars":                           "total_bars_kpi",
    "Total Pieces":                         "total_pieces_kpi",
    "Global Yield":                         "global_yield_kpi",
    "Material Cost":                        "material_cost_kpi",
    "Net Cost":                             "net_cost_kpi",
    "Stock Reuse":                          "stock_reuse_kpi",
    "Stock Returned":                       "stock_returned_kpi",
    "bars required":                        "bars_required_desc",
    "fabricated pieces":                    "fabricated_pieces_desc",
    "physical cut yield":                   "physical_cut_yield_desc",
    "new + reused material":                "new_reused_material_desc",
    "after stock/scrap":                    "after_stock_scrap_desc",
    "OPTIMIZATION SUMMARY":                 "optimization_summary_hdr",
    "TOTAL BARS":                           "total_bars_hdr",
    "TOTAL PIECES":                         "total_pieces_hdr",
    "TOTAL MATERIAL":                       "total_material_hdr",
    "TOTAL USED":                           "total_used_hdr",
    "TOTAL WASTE":                          "total_waste_hdr",
    "GLOBAL YIELD":                         "global_yield_hdr",
    "TOTAL COST":                           "total_cost_hdr",
    "Pieces Cost":                          "pieces_cost_col",
    "Stock Consumed":                       "stock_consumed_col",

    # ── Report 02 — Purchase Order ────────────────────────────────────────────
    "EngCut Engine — Purchase Report":      "rpt_title_purchase_order",
    "Total Bars Required":                  "total_bars_required",
    "Estimated Purchase Cost":              "estimated_purchase_cost",
    "new material":                         "new_material_desc",

    # ── Report 03 — Cutting Report ────────────────────────────────────────────
    "EngCut Engine — Cutting Report":       "rpt_title_cutting_report",
    "EngCut Engine — Individual Pieces":    "rpt_title_cut_list",
    "EngCut Engine — Bar Layout":           "rpt_title_cut_plan",
    "Optimization verified by EngCut Engine": "optimization_verified_msg",
    "All cuts audited":                     "all_cuts_audited_msg",
    "CUT LIST — Individual Pieces":         "cut_list_section_hdr",
    "CUT PLAN — Bar Layout":                "cut_plan_section_hdr",

    # ── RC-006: composite verification banner (single cell, 3 reports) ───────
    "✓  Optimization verified by EngCut Engine  |  All cuts audited — 0 failures": "optimization_verified_banner",

    # ── Report 04 — Stock Report ──────────────────────────────────────────────
    "EngCut Engine — Stock":                "rpt_title_stock",
    "Summary - Stock":                      "summary_stock_hdr",
    "ID Retalho":                           "scrap_id_col",
    "Peso Total":                           "total_weight_col",
    "Data Entrada":                         "entry_date_col",
    "Perfil":                               "profile_col",
    "Valor residual":                       "residual_value_col",
    "Projeto Origem":                       "source_project_col",
    "Barra Origem":                         "source_bar_col",
    "Data Descarte":                        "discard_date_col",
    "Log imutável. Cada evento = uma linha. Nunca deletar registros.": "history_immutable_note",
    "Data/Hora":                            "datetime_col",
    "Evento":                               "event_col",
    "Comprimento Peça":                     "piece_length_col",
    "Operador":                             "operator_col",
    "Status Anterior":                      "previous_status_col",
    "Status Novo":                          "new_status_col",

    # ── Report 05 — Cost Report ───────────────────────────────────────────────
    "EngCut Engine — COST SUMMARY":         "rpt_title_cost_summary",
    "EngCut Engine — COST BY BAR":          "rpt_title_cost_by_bar",
    "EngCut Engine — COST BY PIECE":        "rpt_title_cost_by_piece",
    "COST SUMMARY — By Cut Group":          "cost_summary_by_group_hdr",
    "COST BY BAR — Detailed":               "cost_by_bar_section_hdr",
    "COST BY PIECE — Individual Traceability": "cost_by_piece_section_hdr",
    "TOTAL MATERIAL BARS":                  "total_material_bars_hdr",
    "TOTAL MATERIAL COST BARS":             "total_material_cost_bars_hdr",
    "TOTAL WASTE (Reusable)":               "total_waste_reusable_hdr",
    "TOTAL SCRAP VALUE":                    "total_scrap_value_hdr",
    "NET PROJECT COST":                     "net_project_cost_hdr",
    "TOTAL STOCK (Reusable)":               "total_stock_reusable_hdr",
    "REUSABLE STOCK ($)":                   "reusable_stock_col",
    "NET COST ($)":                         "net_cost_col",
    "MATERIAL COST ($)":                    "material_cost_col",
    "BAR COST ($)":                         "bar_cost_col",
    "USED VALUE ($)":                       "used_value_col",
    "WASTE VALUE ($)":                      "waste_value_col",
    "PIECE COST ($)":                       "piece_cost_col",
    "SCRAP ($)":                            "scrap_cost_col",
    "ASSEMBLY CODE":                        "assembly_code_col",
    "FAMILY CODE":                          "family_code_col",
    "BAR LENGTH":                           "bar_length_hdr",
    "PieceID traceable via QR | AssemblyCode BIM-Ready": "qr_bim_ready_note",

    # ── Report 06 — QR Labels ────────────────────────────────────────────────
    "QR_DATA — Engine Payload":             "qr_data_section_hdr",
    "Generated by EngCut Engine":           "generated_by_engine",
    "Do Not Edit Manually":                 "do_not_edit",
    "PieceID + Length + CutGroup + BarID":  "qr_data_fields_note",
    "+ Zone + UserTag + AssemblyCode":      "zone_usertag_assemblycode",
    "Tier 1 — Minimum":                     "tier_1_minimum",
    "Tier 2 — Field":                       "tier_2_field",
    "Tier 3 — BIM":                         "tier_3_bim",
    "Tier 4 — URL":                         "tier_4_url",
    "QR_LABEL — Label Display Template":    "qr_label_section_hdr",
    "LABEL CONFIGURATION":                  "label_config_hdr",
    "Label size":                           "label_size_col",
    "QR tier":                              "qr_tier_col",
    "Font size":                            "font_size_col",
    "Fields to print":                      "fields_to_print_col",
    "PieceID · Length · CutGroup · Zone · Tag": "qr_label_fields_value",
    "Language":                             "language_col",
    "Output format":                        "output_format_col",
    "Printer protocol":                     "printer_protocol_col",
    "LABEL PREVIEW":                        "label_preview_hdr",
    "THERMAL_CONFIG — Printer Reference":   "thermal_config_section_hdr",
    "PROTOCOL":                             "protocol_hdr",
    "BRANDS":                               "brands_hdr",
    "LABEL SIZES":                          "label_sizes_hdr",
    "CONNECTION":                           "connection_hdr",
    "OUTPUT FILE":                          "output_file_hdr",
    "NOTES":                                "notes_hdr",
    "ZPL SAMPLE — Etiqueta 50x25mm (Zebra)": "zpl_sample_label",

    # ── Unit-bearing labels (all length unit variants) ────────────────────────
    "LENGTH (ft)":                          "length_unit_hdr",
    "LENGTH (in)":                          "length_unit_hdr",
    "LENGTH (mm)":                          "length_unit_hdr",
    "LENGTH (cm)":                          "length_unit_hdr",
    "LENGTH (m)":                           "length_unit_hdr",
    "WASTE (ft)":                           "waste_unit_hdr",
    "WASTE (in)":                           "waste_unit_hdr",
    "WASTE (mm)":                           "waste_unit_hdr",
    "WASTE (cm)":                           "waste_unit_hdr",
    "WASTE (m)":                            "waste_unit_hdr",
    "Comprimento (ft)":                     "comprimento_unit_col",
    "Comprimento (in)":                     "comprimento_unit_col",
    "Comprimento (mm)":                     "comprimento_unit_col",
    "Comprimento (cm)":                     "comprimento_unit_col",
    "Comprimento (m)":                      "comprimento_unit_col",
    "Comprimento Retalho (ft)":             "comprimento_retalho_col",
    "Comprimento Retalho (in)":             "comprimento_retalho_col",
    "Comprimento Retalho (mm)":             "comprimento_retalho_col",
    "Comprimento Retalho (m)":              "comprimento_retalho_col",
    "Peso (lb/ft)":                         "peso_unit_col",
    "Peso (lb/in)":                         "peso_unit_col",
    "Peso (kg/m)":                          "peso_unit_col",

    # ── RC-XLSX: typo / casing variants found in official templates ──────────
    "GLOBAL YILD %":                        "global_yield_pct_hdr",
    "Weigth":                               "stock_summary_weight_hdr",

    # ── RC-XLSX FINAL P2: residual mixed-language headers ─────────────────────
    "ZONE":                                 "zone",
    "ASSEMBLY":                             "assembly",
    "PROJECT ID":                           "project_id_hdr",
    "PROJECT NAME":                         "project_name_hdr",
    "QRCODE":                               "qrcode",
    "PIECE LENGTHS":                        "piece_lengths",
    "STOCK ID":                             "stock_id",

    # ── RC-XLSX FINAL P2: residual mixed-language Source/Status values ───────
    "STOCK REUSE":                          "stock_reuse_kpi",
    "AVAILABLE":                            "available",
    "SCRAP":                                "scrap",

    # ── Report 04 — Stock history event codes ─────────────────────────────────
    "GENERATE_STOCK":                       "generate_stock",
    "USE_STOCK":                            "use_stock",
    "GENERATE_SCRAP":                       "generate_scrap",

    # ── RC-XLSX FINAL P2: QR_LABEL composite sub-header ───────────────────────
    "LABEL PREVIEW — 50 x 25 mm (Tier 2)":  "label_preview_full_hdr",

    # ── RC-006: QR_LABEL preview short labels (D22/D23/D24) ───────────────────
    "GROUP":                                "group",
    "TAG":                                  "tag",
    "BAR":                                  "bar",

    # ── RC-006: Project Summary residual English headers/values ──────────────
    "Bars":                                 "bars",
    "inventory consumed":                   "inventory_consumed",
    "Scrap/Junkyard":                       "scrap_junkyard",
    "Scrap/\nJunkyard":                     "scrap_junkyard",
    "Patrimonial Multiplier":               "stock_dependency_short",
    "Interpretation":                       "interpretation",
    "Predominance of new material":         "predominance_new_material",
    "Predominance of reuse":                "predominance_reuse",
    # ── C22 Stock Dependency ──────────────────────────────────────────────────
    "Stock Dependency Rate":                "stock_dependency_rate",
    "Consumed Stock / (New Bars + Stock)":  "stock_dependency_formula",
    "Stock Dep. criterion text":            "stock_dependency_criterion",
    "Predominantly new material":           "predominantly_new_material",
    "Partial stock use":                    "partial_stock_use",
    "High stock dependency":                "high_stock_dependency",
}

# Case-insensitive lookup, built once. Exact-case matches in _LABEL_MAP always
# win first; this is only a fallback for templates that use different casing
# (e.g. "Cut Group" vs "CUT GROUP", "Valor Residual" vs "Valor residual").
_LABEL_MAP_LOWER = {k.lower(): v for k, v in _LABEL_MAP.items()}


def _apply_lang_labels(ws) -> int:
    """Rewrite known English static labels with the current-language translation.
    Returns the count of cells updated."""
    try:
        import language_manager as _lm
    except Exception:
        return 0
    updated = 0
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell, MergedCell):
                continue
            if not isinstance(cell.value, str):
                continue
            stripped = cell.value.strip()
            normalized = " ".join(stripped.split())
            key = (_LABEL_MAP.get(stripped)
                   or _LABEL_MAP.get(normalized)
                   or _LABEL_MAP_LOWER.get(stripped.lower())
                   or _LABEL_MAP_LOWER.get(normalized.lower()))
            if key:
                translated = _lm.t(key)
                if translated and translated != key:
                    cell.value = translated
                    updated += 1
    return updated


def _translate_label(text):
    """Translate a known English label/value (e.g. "NEW BAR", "STOCK REUSE",
    "AVAILABLE") via _LABEL_MAP for dynamically-written cell values that are
    not present in the static template at _apply_lang_labels() time.
    Returns the original text unchanged if no mapping or translation exists."""
    if not isinstance(text, str):
        return text
    try:
        import language_manager as _lm
    except Exception:
        return text
    stripped = text.strip()
    normalized = " ".join(stripped.split())
    key = (_LABEL_MAP.get(stripped)
           or _LABEL_MAP.get(normalized)
           or _LABEL_MAP_LOWER.get(stripped.lower())
           or _LABEL_MAP_LOWER.get(normalized.lower()))
    if not key:
        return text
    translated = _lm.t(key)
    return translated if translated and translated != key else text


# ─── Formatting Helpers ───────────────────────────────────────────────────────

def _detect_pattern_length(ws, data_start: int) -> int:
    """Detect whether template uses 1-row (solid) or 2-row (zebra) fill pattern.
    Compares the fgColor fill signature of rows data_start and data_start+1.
    Returns 2 if they differ (zebra banding), 1 otherwise.
    """
    def _sig(row_num):
        sigs = []
        for col in range(1, min(ws.max_column + 1, 9)):
            cell = ws.cell(row_num, col)
            if isinstance(cell, MergedCell) or not cell.has_style:
                continue
            f = cell.fill
            if f and f.patternType and f.patternType != "none":
                try:
                    sigs.append(str(f.fgColor.rgb))
                except Exception:
                    sigs.append(str(f.patternType))
        return tuple(sigs)
    return 2 if _sig(data_start) != _sig(data_start + 1) else 1


def _fix_unit_headers(ws, output_unit: str) -> None:
    """Replace length/weight/rate unit tokens in all cell values with project output_unit.

    Handles:
      - "(ft)", "(mm)", "(cm)", "(m)", "(in)"    → "(output_unit)"
      - "(lb/ft)", "(lb/in)", "(kg/m)"            → correct weight unit
      - "$/ft", "$/mm" etc.                       → "$/{output_unit}"
      - "{unit}" placeholder in translated strings → output_unit
    """
    if output_unit in ("ft", "decimal_feet"):
        len_u, wt_u = "ft", "lb/ft"
    elif output_unit in ("in", "decimal_inches", "fractional_inches"):
        len_u, wt_u = "in", "lb/in"
    else:
        len_u, wt_u = output_unit, "kg/m"

    _unit_re   = re.compile(r'\((ft|in|mm|cm|m)\)')
    _weight_re = re.compile(r'\((lb/ft|lb/in|kg/m)\)')
    _rate_re   = re.compile(r'\$/(ft|in|mm|cm|m)\b')

    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell, MergedCell) or not isinstance(cell.value, str):
                continue
            val = cell.value
            new_val = val.replace("{unit}", len_u)
            new_val = new_val.replace("{wunit}", _weight_total_unit(output_unit))
            new_val = _unit_re.sub(f"({len_u})", new_val)
            new_val = _weight_re.sub(f"({wt_u})", new_val)
            new_val = _rate_re.sub(f"$/{len_u}", new_val)
            if new_val != val:
                cell.value = new_val


def _fix_output_display(ws, row: int, col: int, output_unit: str, unit_system: str) -> None:
    """RC-CERT-007 G-002/G-003: "Exibicao de Saida" segue a regra congelada de
    unidade de BARRA (METRIC=m / IMPERIAL=ft), nunca o codigo interno cru do
    output_unit (ex.: "decimal_inches"). Sempre substitui — hint strings como
    '[decimal_inches / ...]' no template nao devem chegar ao XLSX gerado."""
    cell = ws.cell(row, col)
    if not isinstance(cell, MergedCell):
        cell.value = _bar_display_unit(unit_system)


def _apply_print_setup(ws, orientation="landscape", fit_to_page=True,
                       print_area=None, repeat_rows=None):
    """Configure print settings on a worksheet."""
    try:
        ws.page_setup.orientation = orientation
        ws.page_setup.fitToWidth  = 1
        ws.page_setup.fitToHeight = 0
    except Exception:
        pass
    try:
        if fit_to_page:
            ws.sheet_properties.pageSetUpPr.fitToPage = True
    except Exception:
        pass
    if print_area:
        try:
            ws.print_area = print_area
        except Exception:
            pass
    if repeat_rows:
        try:
            ws.print_title_rows = repeat_rows
        except Exception:
            pass


def copy_row_style(ws, source_row: int, target_row: int) -> None:
    """Copy all cell formatting + row height from source_row to target_row.

    Called before writing values to every dynamic data row beyond the first
    template data row, ensuring borders, fill, font, alignment, and number
    format are preserved regardless of how many rows the engine produces.
    """
    # Row height
    src_dim = ws.row_dimensions.get(source_row)
    if src_dim and src_dim.height:
        ws.row_dimensions[target_row].height = src_dim.height

    for col in range(1, ws.max_column + 1):
        src_cell = ws.cell(source_row, col)
        tgt_cell = ws.cell(target_row, col)

        if isinstance(src_cell, MergedCell) or isinstance(tgt_cell, MergedCell):
            continue
        if not src_cell.has_style:
            continue

        tgt_cell.font         = copy.copy(src_cell.font)
        tgt_cell.fill         = copy.copy(src_cell.fill)
        tgt_cell.border       = copy.copy(src_cell.border)
        tgt_cell.alignment    = copy.copy(src_cell.alignment)
        tgt_cell.number_format = src_cell.number_format
        tgt_cell.protection   = copy.copy(src_cell.protection)


def _percent_value(v):
    """Return Excel percent value as decimal fraction.

    Accepts 0.9552, 95.52, "95.52%" and returns 0.9552.
    This is display-only normalization for XLSX output; it does not alter engine math.
    """
    if v is None:
        return None
    if isinstance(v, str):
        txt = v.strip().replace(",", ".")
        has_pct = txt.endswith("%")
        if has_pct:
            txt = txt[:-1].strip()
        try:
            n = float(txt)
        except ValueError:
            return None
        return n / 100.0 if has_pct or n > 1 else n
    try:
        n = float(v)
    except (TypeError, ValueError):
        return None
    return n / 100.0 if n > 1 else n


def _remove_row_fill(ws, row_idx: int) -> None:
    """Remove helper-row fill from generated output without changing dimensions."""
    no_fill = PatternFill(fill_type=None)
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row_idx, col)
        if not isinstance(cell, MergedCell):
            cell.fill = copy.copy(no_fill)


# ─── Locale-Aware Numeric Formatting (RC-XLSX) ────────────────────────────────
# METRIC projects   -> pt-BR locale number format: 1.000,00  (comma decimal)
# IMPERIAL projects -> en-US locale number format: 1,000.00  (period decimal)
# The [$-xxx] locale prefix forces these separators regardless of the end
# user's Excel regional settings, so a report never mixes decimal styles.

def _is_imperial(output_unit: str) -> bool:
    return str(output_unit).lower() in (
        "ft", "in", "decimal_inches", "fractional_inches", "decimal_feet"
    )


def _loc(output_unit: str) -> str:
    return "409" if _is_imperial(output_unit) else "416"


def _weight_total_unit(output_unit: str) -> str:
    return "lb" if _is_imperial(output_unit) else "kg"


def _display_unit_suffix(output_unit: str) -> str:
    """RC-CERT-007 G-002: unidade exibida em sufixos de number_format.

    "decimal_inches" e' o codigo interno do output_unit, mas o rotulo de
    exibicao congelado (frozen) e' "in" (regra: pecas=in em IMPERIAL). Esta
    funcao normaliza apenas o TEXTO do sufixo do formato, sem alterar o
    valor numerico nem a conversao de unidade ja calculada pelo chamador.
    """
    u = str(output_unit).lower()
    if u in ("decimal_inches", "fractional_inches"):
        return "in"
    if u == "decimal_feet":
        return "ft"
    return output_unit


def _set_num(cell, value, output_unit, decimals: int = 2) -> None:
    """Plain locale-aware numeric cell, no unit suffix."""
    cell.value = round(_num(value), decimals)
    if decimals > 0:
        cell.number_format = f'[$-{_loc(output_unit)}]#,##0.{"0" * decimals}'
    else:
        cell.number_format = f'[$-{_loc(output_unit)}]#,##0'


def _set_len(cell, value, output_unit, decimals: int = 2) -> None:
    """Length value already expressed in output_unit, with unit suffix."""
    cell.value = round(_num(value), decimals)
    disp = _display_unit_suffix(output_unit)
    cell.number_format = f'[$-{_loc(output_unit)}]#,##0.{"0" * decimals}" {disp}"'


def _set_weight(cell, value, output_unit, decimals: int = 2) -> None:
    """Total-weight value (kg for METRIC, lb for IMPERIAL), with unit suffix."""
    cell.value = round(_num(value), decimals)
    wu = _weight_total_unit(output_unit)
    cell.number_format = f'[$-{_loc(output_unit)}]#,##0.{"0" * decimals}" {wu}"'


def _set_pct(cell, fraction, output_unit, decimals: int = 2) -> None:
    """Percentage as a real numeric fraction (0.9117 -> 91.17%)."""
    val = _percent_value(fraction)
    cell.value = val if val is not None else 0
    cell.number_format = f'[$-{_loc(output_unit)}]0.{"0" * decimals}%'


def _set_money(cell, value, output_unit, decimals: int = 2) -> None:
    cell.value = round(_num(value), decimals)
    cell.number_format = f'[$$-{_loc(output_unit)}]\\ #,##0.{"0" * decimals}'


def _set_rate(cell, val_per_m, output_unit, decimals: int = 4) -> None:
    """Convert a $/m rate to $/output_unit and write a locale-aware numeric cell."""
    mm_per_unit = 1.0 / _unit_factor(output_unit) if _unit_factor(output_unit) else 1.0
    rate = _num(val_per_m) * mm_per_unit / 1000.0
    cell.value = round(rate, decimals)
    disp = _display_unit_suffix(output_unit)
    cell.number_format = f'[$$-{_loc(output_unit)}]#,##0.{"0" * decimals}"/{disp}"'


def _set_bar_rate(cell, val, unit_system, decimals: int = 2) -> None:
    """RC-CERT-007 02-PO-004/05-CBB-003: COST_BARS/COST_PIECES col7 is already
    expressed as $ per BAR unit (m METRICO | ft IMPERIAL) - write the raw rate
    unchanged with a "$/{bar_unit}" suffix (no further unit conversion)."""
    bar_unit = _bar_display_unit(unit_system)
    cell.value = round(_num(val), decimals)
    cell.number_format = f'[$$-{_loc(bar_unit)}]#,##0.{"0" * decimals}"/{bar_unit}"'


def _set_date(cell, value, output_unit) -> None:
    """Store a real Excel datetime (parsed from an ISO string or datetime/date
    object) and apply the locale-aware date format matching output_unit."""
    if isinstance(value, (datetime.datetime, datetime.date)):
        dt = value
    elif isinstance(value, str) and value.strip():
        try:
            dt = datetime.datetime.fromisoformat(value.strip())
        except ValueError:
            cell.value = value
            return
    else:
        cell.value = ""
        return
    cell.value = dt
    cell.number_format = "mm/dd/yyyy" if _is_imperial(output_unit) else "dd/mm/yyyy"


_DECIMAL_NUM_RE = re.compile(r'(\d+)[.,](\d{2})(?=\s*(?:mm|cm|m|in|ft)\b)')


def _fix_decimal_sep(text, output_unit) -> str:
    """RC-CERT-002 CP-005/QD-004: normalize the decimal separator of
    "<number> <unit>" tokens inside a free-form Cut Engine label (e.g. the
    Cut Sequence string "3x(123,40 in)") to the locale driven by output_unit
    (period for IMPERIAL, comma for METRIC). Display-only string fix; the
    underlying numeric value is unchanged. Assumes piece-length magnitudes
    (< 1000) so no thousands separator is present."""
    if not isinstance(text, str):
        return text
    target = "." if _is_imperial(output_unit) else ","
    return _DECIMAL_NUM_RE.sub(lambda m: f"{m.group(1)}{target}{m.group(2)}", text)


def _fmt_num_locale(value, output_unit, decimals: int = 2) -> str:
    """Render a number as plain text using the locale matching output_unit
    (comma decimal for METRIC, period decimal for IMPERIAL). Used for QR
    payload strings, which are plain text and not subject to cell number_format."""
    s = f"{_num(value):,.{decimals}f}"
    if _is_imperial(output_unit):
        return s
    return s.translate(str.maketrans({",": "\x00", ".": ","})).replace("\x00", ".")


# ─── Report 01 — Project Summary ─────────────────────────────────────────────

def _fill_balanco_sheet(wb, output_unit):
    """Add / overwrite a 'BALANÇO' sheet in *wb* with the accumulated project history."""
    import json as _json
    from pathlib import Path as _Path

    hist_path = _Path("outputs") / "cost_project_history.json"
    history = []
    if hist_path.exists():
        try:
            with open(hist_path, encoding="utf-8") as _f:
                history = _json.load(_f)
        except Exception:
            history = []

    # Remove old sheet if it exists (regenerate clean)
    if "BALANÇO" in wb.sheetnames:
        del wb["BALANÇO"]

    ws = wb.create_sheet("BALANÇO")

    # Style constants
    from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
    _DARK   = PatternFill("solid", fgColor="002060")
    _HDR    = PatternFill("solid", fgColor="FCE4D6")
    _EVEN   = PatternFill("solid", fgColor="F9F9F9")
    _F_TTL  = Font(name="Calibri", size=13, bold=True,  color="FFFFFF")
    _F_HDR  = Font(name="Calibri", size=11, bold=True,  color="403020")
    _F_REG  = Font(name="Calibri", size=11, bold=False)
    _F_TOT  = Font(name="Calibri", size=11, bold=True)
    _C_CTR  = Alignment(horizontal="center", vertical="center")
    _C_LFT  = Alignment(horizontal="left",   vertical="center")
    _C_RGT  = Alignment(horizontal="right",  vertical="center")
    _THIN   = Side(style="thin", color="CCCCCC")
    _BORD   = Border(bottom=_THIN)

    fmt_cur = "$ #,##0.00"
    fmt_pct = "0.00%"

    # Row 1: title
    try:
        import language_manager as _lmb
        _tb = _lmb.t
    except Exception:
        _tb = lambda k: k
    ws.row_dimensions[1].height = 28
    _t = ws.cell(1, 1, _tb("balanco_title"))
    _t.fill  = _DARK
    _t.font  = _F_TTL
    _t.alignment = _C_LFT
    ws.merge_cells("A1:K1")

    # Row 2: column headers
    ws.row_dimensions[2].height = 22
    _headers = [
        _tb("balanco_col_project"), _tb("balanco_col_date"),
        _tb("balanco_col_material_cost"), _tb("balanco_col_pieces_price"),
        _tb("balanco_col_stock_scrap"), _tb("balanco_col_scrap_revenue"),
        _tb("balanco_col_transport"), _tb("balanco_col_net_cost"),
        _tb("balanco_col_net_cost_idx"), _tb("balanco_col_yield_pct"),
        _tb("balanco_col_status"),
    ]
    for _ci, _h in enumerate(_headers, 1):
        _c = ws.cell(2, _ci, _h)
        _c.fill      = _HDR
        _c.font      = _F_HDR
        _c.alignment = _C_CTR
        _c.border    = _BORD

    # Data rows
    for _ri, _ent in enumerate(history, 3):
        ws.row_dimensions[_ri].height = 18
        _cm  = float(_ent.get("Custo Material",    0) or 0)
        _pp  = float(_ent.get("Preço Total Peças", 0) or 0)
        _re  = float(_ent.get("Retalhos Estoque",  0) or 0)
        _rd  = float(_ent.get("Receita Descartes", 0) or 0)
        _tr  = float(_ent.get("Transporte",        0) or 0)
        _cl  = round(_cm + _tr - _re - _rd, 2)
        _idx = round(_cl / _cm, 4)  if _cm  > 0 else 0.0
        _yld = round(_cl / (_cm - _re), 4) if (_cm - _re) > 0 else 0.0
        _fill = _EVEN if _ri % 2 == 0 else PatternFill()

        def _sc(_col, _val, _fmt=None, _aln=None):
            _cell = ws.cell(_ri, _col, _val)
            if _fill.fill_type:
                _cell.fill = _fill
            _cell.font   = _F_REG
            _cell.alignment = _aln or _C_CTR
            if _fmt:
                _cell.number_format = _fmt
            _cell.border = _BORD

        _sc(1,  _ent.get("Projeto", ""),  _aln=_C_LFT)
        _sc(2,  _ent.get("Data",    ""),  _aln=_C_CTR)
        _sc(3,  _cm,  fmt_cur, _C_RGT)
        _sc(4,  _pp,  fmt_cur, _C_RGT)
        _sc(5,  _re,  fmt_cur, _C_RGT)
        _sc(6,  _rd,  fmt_cur, _C_RGT)
        _sc(7,  _tr,  fmt_cur, _C_RGT)
        _sc(8,  _cl,  fmt_cur, _C_RGT)
        _sc(9,  _idx, fmt_pct, _C_RGT)
        _sc(10, _yld, fmt_pct, _C_RGT)
        _sc(11, _ent.get("Status", "OK"), _aln=_C_CTR)

    # Totals row (if there is data)
    if history:
        _tr_row = len(history) + 3
        ws.row_dimensions[_tr_row].height = 20
        _all_cm  = sum(float(e.get("Custo Material",    0) or 0) for e in history)
        _all_pp  = sum(float(e.get("Preço Total Peças", 0) or 0) for e in history)
        _all_re  = sum(float(e.get("Retalhos Estoque",  0) or 0) for e in history)
        _all_rd  = sum(float(e.get("Receita Descartes", 0) or 0) for e in history)
        _all_tr  = sum(float(e.get("Transporte",        0) or 0) for e in history)
        _all_cl  = round(_all_cm + _all_tr - _all_re - _all_rd, 2)
        _tot_fill = PatternFill("solid", fgColor="C6E0B4")
        for _ci, (_val, _fmt, _aln) in enumerate([
            (_tb("balanco_col_project").upper(), None, _C_LFT),
            (_tb("balanco_n_projects").format(n=len(history)), None, _C_CTR),
            (_all_cm, fmt_cur, _C_RGT),
            (_all_pp, fmt_cur, _C_RGT),
            (_all_re, fmt_cur, _C_RGT),
            (_all_rd, fmt_cur, _C_RGT),
            (_all_tr, fmt_cur, _C_RGT),
            (_all_cl, fmt_cur, _C_RGT),
            (None, None, None),
            (None, None, None),
            (None, None, None),
        ], 1):
            _c = ws.cell(_tr_row, _ci, _val)
            _c.fill = _tot_fill
            _c.font = _F_TOT
            if _fmt:
                _c.number_format = _fmt
            if _aln:
                _c.alignment = _aln

    # Column widths
    _col_w = {"A": 38, "B": 22, "C": 16, "D": 16, "E": 18,
               "F": 16, "G": 14, "H": 17, "I": 20, "J": 20, "K": 10}
    for _col, _w in _col_w.items():
        ws.column_dimensions[_col].width = _w


def generate_project_summary(active_tab=None):
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)
    proj = pd.get("project", {})
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)

    dst = _stamped("01_Project_Summary.xlsx")
    shutil.copy2(_template("01_Project_Summary.xlsx"), dst)

    wb = load_workbook(dst)
    ws = wb["PROJECT_SUMMARY"]
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)

    # RC-CERT-002 PS-012: "Exibicao de Saida" segue a regra congelada de
    # unidade de BARRA (METRIC=m / IMPERIAL=ft), nao o output_unit cru.
    _disp_cell = ws.cell(11, 2)
    if not isinstance(_disp_cell, MergedCell) and _disp_cell.value == output_unit:
        _disp_cell.value = _bar_display_unit(unit_system)

    _last_data_row = 26  # updated in data loop; fallback keeps print area tight

    try:
        report_rows = _wb_rows("outputs/result.xlsx", "REPORT", skip_rows=1, require_col1=True)
        # REPORT cols (0-indexed): CutGroup(0) BarCombo(1) SolID(2) QtyBars(3) QtyPieces(4)
        #   MaterialComprado_mm(5) RetalhoConsumido_mm(6) MaterialProcessado_mm(7)
        #   Used_mm(8) WasteDrop_mm(9) YieldOp%(10) Status(11)
        total_bars   = sum(int(_safe(r[3], 0)) for r in report_rows)
        total_pieces = sum(int(_safe(r[4], 0)) for r in report_rows)
        total_mat    = sum(_parse_bar_len(r[7], unit_system) for r in report_rows)
        total_used   = sum(_parse_bar_len(r[8], unit_system) for r in report_rows)
        global_yield = round(total_used / total_mat, 4) if total_mat else 0

        cp = _cost_project_map()
        mat_cost     = _num(cp.get("(+) Material comprado - barras novas"))
        scrap_rev    = _num(cp.get("(-) Receita venda ferro velho (junkyard)"))
        stock_reuse  = _num(cp.get("(+) Retalho consumido do estoque"))
        stock_return = _num(cp.get("(-) Retalhos gerados para estoque"))
        # RC-CERT-002 PS-011: fonte unica de "Custo Liquido" = Custo Material
        # menos valor reaproveitavel (estoque + sucata), igual a COST BY BAR M16.
        net_cost     = mat_cost - stock_return - scrap_rev

        # KPI summary row 18
        ws.cell(18, 1).value = total_bars
        ws.cell(18, 2).value = total_pieces
        _set_pct(ws.cell(18, 3), global_yield, output_unit)
        _set_money(ws.cell(18, 4), mat_cost, output_unit)
        _set_money(ws.cell(18, 5), net_cost, output_unit)
        _set_money(ws.cell(18, 6), stock_reuse, output_unit)

        # Secondary KPIs row 22
        _set_money(ws.cell(22, 1), stock_return, output_unit)
        _set_money(ws.cell(22, 2), scrap_rev, output_unit)

        # C22 — Taxa de Dependência de Estoque
        # = Retalho consumido do estoque / (Barras novas + Retalho consumido)
        # Sinaliza quanto do material total consumido veio do estoque, evitando
        # confusão quando mat_cost subestima o custo real por uso de retalhos.
        _total_mat = mat_cost + stock_reuse
        _stock_dep = round(stock_reuse / _total_mat, 4) if _total_mat else 0

        _c22 = ws.cell(22, 3)
        _c22.value          = _stock_dep
        _c22.number_format  = '0.00%'
        _c22.font           = copy.copy(ws.cell(22, 4).font)   # roxo bold Carlito 11
        _c22.alignment      = copy.copy(ws.cell(22, 4).alignment)  # center+wrap

        # D22 — classificação em 3 níveis
        if _stock_dep >= 0.5:
            _dep_label = _translate_label("High stock dependency")
        elif _stock_dep >= 0.1:
            _dep_label = _translate_label("Partial stock use")
        else:
            _dep_label = _translate_label("Predominantly new material")
        ws.cell(22, 4).value = _dep_label

        # Row 22: altura para acomodar D22 ("Material predominantemente novo") em 2+ linhas
        ws.row_dimensions[22].height = 39

        # Row 24 (spacer entre KPIs secundários e tabela de grupos):
        # premissa única da métrica C22, roxo sem negrito — abaixo dos valores,
        # acima do cabeçalho da tabela por grupo (row 25).
        ws.merge_cells('A24:K24')
        _r24           = ws.cell(24, 1)
        _r24.value     = _translate_label("Stock Dep. criterion text")
        _r24.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False)
        _r24.font      = Font(name='Carlito', size=9, bold=False, italic=False,
                              color='FF7030A0')

        # E22 e F22: limpar — premissa declarada em A24, sem redundância
        ws.cell(22, 5).value     = None
        ws.cell(22, 6).value     = None
        ws.cell(22, 5).alignment = Alignment()
        ws.cell(22, 6).alignment = Alignment()

        # Per-group table (row 25=header, row 26=TOTAL, row 27+=data)
        input_pieces = pd.get("pieces", [])
        pc_map   = {}
        name_map = {}
        for p in input_pieces:
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg]   = p.get("product_code", "")
                name_map[cg] = p.get("piece_name", "")

        cb_grp = _cb_by_group(unit_system)
        cp_grp, total_piece_cost = _cp_cost_by_group()
        stock_by_grp, scrap_by_grp = _drop_value_by_group(pd)

        # Unmerge any template merged cells in the data area so that
        # copy_row_style + value writes can reach every column without
        # hitting the 'MergedCell is read-only' AttributeError.
        _merges_to_remove = [
            m for m in list(ws.merged_cells.ranges)
            if m.min_row >= 26
        ]
        for _mc in _merges_to_remove:
            ws.unmerge_cells(str(_mc))

        _clear_rows(ws, 26, 36)
        ws.cell(26, 1).value = "TOTAL"
        ws.cell(26, 3).value = total_bars
        ws.cell(26, 4).value = "-"
        ws.cell(26, 5).value = total_pieces
        _set_money(ws.cell(26, 6), mat_cost, output_unit)
        # RC-CERT-002 PS-006: TOTAL row - preencher Custo das Pecas / Retorno ao
        # Estoque / Sucata-Descarte / Estoque Consumido com os mesmos totais
        # ja usados nos KPIs (linhas 18 e 22), fonte unica.
        _set_money(ws.cell(26, 7), total_piece_cost, output_unit)
        _set_money(ws.cell(26, 8), stock_return, output_unit)
        _set_money(ws.cell(26, 9), scrap_rev, output_unit)
        _set_money(ws.cell(26, 10), stock_reuse, output_unit)
        _set_pct(ws.cell(26, 11), global_yield, output_unit)

        _data_start_01 = 27
        for i, row in enumerate(report_rows):
            cg       = _safe(row[0])
            r        = _data_start_01 + i
            _last_data_row = r
            qty_bars = int(_safe(row[3], 0))
            qty_pcs  = int(_safe(row[4], 0))
            grp_cost = round(cb_grp.get(cg, {}).get("cost", 0), 2)
            yld = _percent_value(row[10])
            if i > 0:
                copy_row_style(ws, _data_start_01, r)
            ws.cell(r, 1).value = cg
            ws.cell(r, 2).value = pc_map.get(cg, "")
            ws.cell(r, 3).value = qty_bars
            ws.cell(r, 4).value = _safe(row[2])  # solution_id: fiel ao motor (ex: 20ft x4 + 30ft x4)
            ws.cell(r, 5).value = qty_pcs
            _set_money(ws.cell(r, 6), grp_cost, output_unit)
            # RC-CERT-002 PS-006: Custo das Pecas por grupo (COST_PIECES). Sem
            # quebra por grupo aprovada para Retorno ao Estoque/Sucata/Reuso ->
            # mantem o marcador "-" ja usado pela coluna Tamanho da Barra (D26).
            _set_money(ws.cell(r, 7), round(cp_grp.get(cg, 0), 2), output_unit)
            # RC-CERT-007 01-PS-005: Retorno ao Estoque / Sucata-Descarte por
            # grupo, fonte unica _drop_value_by_group (mesma usada em COST
            # SUMMARY H/I), reconciliando com os totais H26/I26.
            _set_money(ws.cell(r, 8), stock_by_grp.get(cg, 0.0), output_unit)
            _set_money(ws.cell(r, 9), scrap_by_grp.get(cg, 0.0), output_unit)
            ws.cell(r, 10).value = "-"
            # RC-005: keep Yield in column K as numeric percent, not text.
            _set_pct(ws.cell(r, 11), yld if yld is not None else 0, output_unit)

    except Exception:
        pass

    # RC-005: row 35 in the template contains yellow LARGURA helper markers.
    # They must not appear in the generated report. Keep column widths separately.
    _remove_row_fill(ws, 35)

    # Clear template-inherited borders from rows not filled by data.
    # The template has 5 example data rows (27-31) with borders; _clear_rows removes
    # values but not borders.  When the project has fewer than 5 cut groups those
    # rows appear as ghost lines.  Also covers rows 32-36 for safety.
    # Use ws._cells to avoid creating new cell objects (setting Border() on a
    # non-existent cell materializes it and inflates max_row).
    for _br in range(_last_data_row + 1, 37):
        for _bc in range(1, 12):
            if (_br, _bc) in ws._cells:
                _bcell = ws._cells[(_br, _bc)]
                if not isinstance(_bcell, MergedCell):
                    _bcell.border = Border()

    # Apply percent number-format only to rows that were actually written, avoiding
    # ghost cells in empty rows (previously hardcoded to max 40 which created them).
    for _r in range(26, _last_data_row + 1):
        ws.cell(_r, 11).number_format = f'[$-{_loc(output_unit)}]0.00%'

    _last_row_01 = _last_data_row
    _fix_unit_headers(ws, output_unit)
    _apply_template_widths(ws)
    _apply_print_setup(ws, orientation="portrait",
                       print_area=f"A1:{get_column_letter(ws.max_column)}{_last_row_01}",
                       repeat_rows="25:25")

    # Append persistent project history tab
    _fill_balanco_sheet(wb, output_unit)

    if active_tab and active_tab in wb.sheetnames:
        wb.active = wb[active_tab]
    wb.save(dst)
    return dst


# ─── Report 02 — Purchase Order ──────────────────────────────────────────────

def _parse_bar_components(solution_id_text):
    """Parse solution_id '(20.00 ft x4) + (30.00 ft x4)' into
    [(20.0, 4), (30.0, 4)] — bar_len already in display unit (ft/m).
    Returns [] on failure."""
    import re
    return [
        (float(m[0]), int(m[1]))
        for m in re.findall(
            r'\(([0-9]+\.?[0-9]*)\s*(?:ft|m|in|mm|cm)?\s*x(\d+)\)',
            solution_id_text or ""
        )
    ]


def generate_purchase_order():
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)

    dst = _stamped("02_Purchase_Order.xlsx")
    shutil.copy2(_template("02_Purchase_Order.xlsx"), dst)

    wb = load_workbook(dst)
    ws = wb["Purchase_order"]
    _fill_placeholders(ws, rpl)
    _fix_output_display(ws, 11, 2, output_unit, unit_system)

    # Fix template typo "Collor" -> "Color" before label translation, so the
    # corrected English text is picked up by the language dictionary below.
    try:
        if not isinstance(ws.cell(19, 3), MergedCell):
            ws.cell(19, 3).value = "Color"
    except Exception:
        pass

    _apply_lang_labels(ws)

    # F16/J16: tamanho de fonte = 10 (template usa 11)
    for _fc in (6, 10):
        _hcell = ws.cell(16, _fc)
        if not isinstance(_hcell, MergedCell) and _hcell.font:
            _old_f = _hcell.font
            _hcell.font = Font(name=_old_f.name, bold=_old_f.bold,
                               italic=_old_f.italic, color=_old_f.color, size=10)

    # RC-XLSX: row 29 in the template carries a yellow LARGURA= helper marker.
    _remove_row_fill(ws, 29)

    # RC-XLSX FINAL P1: J17 (=SUM(J20:J25)) must use the exact same
    # locale-aware money format as the summed rows (see _set_money).
    ws.cell(17, 10).number_format = f'[$$-{_loc(output_unit)}]\\ #,##0.00'

    _last_data_row_02 = 19  # fallback: nenhum dado gravado

    try:
        report_rows = _wb_rows("outputs/result.xlsx", "REPORT", skip_rows=1, require_col1=True)
        input_pieces = pd.get("pieces", [])
        pc_map   = {}
        name_map = {}
        color_map = {}
        for p in input_pieces:
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg]   = p.get("product_code", "")
                name_map[cg] = p.get("piece_name", p.get("commercial_key", cg))
                color_map[cg] = p.get("color", p.get("finish", ""))

        cb_grp = _cb_by_group(unit_system)
        total_bars = sum(cb_grp[cg]["bars"] for cg in cb_grp)
        total_cost = sum(cb_grp[cg].get("cost", 0) for cg in cb_grp)
        ws.cell(17, 6).value = total_bars
        # RC-CERT-002 PO-007/PO-008: J17 = total da coluna J (Custo Estimado),
        # mesmo total usado em PROJECT_SUMMARY/COST SUMMARY (fonte unica),
        # gravado como valor estatico para nao depender de recalculo do Excel.
        _set_money(ws.cell(17, 10), round(total_cost, 2), output_unit)

        _bar_unit = _bar_display_unit(unit_system)
        _data_start_02 = 20

        # Unmerge células do template na área de dados para evitar crash ao escrever
        # (template tem A27:J27 = "Purchase Summary" merged → MergedCell write error)
        for _mc in list(ws.merged_cells.ranges):
            if _mc.min_row >= _data_start_02:
                ws.unmerge_cells(str(_mc))

        _clear_rows(ws, _data_start_02, _data_start_02 + 60)
        item_num = 0
        for row in report_rows:
            cg          = _safe(row[0])
            solution_id = _safe(row[2])
            grp         = cb_grp.get(cg, {})
            rate_raw    = grp.get("rate", 0)

            # STOCK_ONLY groups have qty_bars == 0 — no bars to purchase.
            # Skip them; they must not appear in the Purchase Order.
            if int(_safe(row[3], 0)) == 0:
                continue

            # Build one sub-row per distinct bar size (NC 02-PO split).
            components = _parse_bar_components(solution_id)
            if components:
                sub_rows = []
                for bar_len, qty in components:
                    # bar_len is already in display unit (ft/m) — extracted directly
                    sub_rows.append((qty, bar_len, round(qty * bar_len, 2),
                                     round(qty * bar_len * rate_raw, 2)))
            else:
                # Fallback: single row (original behaviour).
                bl  = _bar_unit_value(grp.get("bar_len_mm", 0), unit_system)
                tot = _bar_unit_value(grp.get("total_len_mm", 0), unit_system)
                sub_rows = [(grp.get("bars", int(_safe(row[3], 0))),
                             bl, round(tot, 2), round(grp.get("cost", 0), 2))]

            for qty, bar_len, total_len, cost_row in sub_rows:
                item_num += 1
                r = _data_start_02 + item_num - 1
                _last_data_row_02 = r
                if item_num > 1:
                    copy_row_style(ws, _data_start_02, r)
                ws.cell(r, 1).value  = item_num
                ws.cell(r, 2).value  = name_map.get(cg, cg)
                ws.cell(r, 3).value  = color_map.get(cg, "")
                ws.cell(r, 4).value  = pc_map.get(cg, "")
                ws.cell(r, 5).value  = cg
                ws.cell(r, 6).value  = qty
                # RC-CERT-002 PO-005: unidade de BARRA congelada (m / ft).
                _set_len(ws.cell(r, 7), bar_len, _bar_unit, 2)
                _set_len(ws.cell(r, 8), total_len, _bar_unit, 2)
                _set_bar_rate(ws.cell(r, 9), rate_raw, unit_system)
                _set_money(ws.cell(r, 10), cost_row, output_unit)

    except Exception:
        pass

    _last_row_02 = _last_data_row_02
    _fix_unit_headers(ws, output_unit)

    # RC-CERT-007 02-PO-004: coluna I (Custo/Unidade) e' a taxa por unidade de
    # BARRA (m METRICO | ft IMPERIAL), igual ao COST_BARS col7. Restaura o
    # rotulo apos _fix_unit_headers, que normalizaria "$/..." para output_unit.
    if not isinstance(ws.cell(19, 9), MergedCell):
        ws.cell(19, 9).value = f"$/{_bar_display_unit(unit_system)}"

    _apply_template_widths(ws)
    _apply_print_setup(ws, orientation="landscape",
                       print_area=f"A1:{get_column_letter(ws.max_column)}{_last_row_02}",
                       repeat_rows="19:19")

    # Elimina células além do último dado via del ws._cells[key] (antes de salvar),
    # reduz max_row e remove linhas fantasmas (mesma técnica de delete_rows_bottom_up).
    for _key in [k for k in list(ws._cells) if k[0] > _last_data_row_02]:
        del ws._cells[_key]

    wb.save(dst)
    return dst


# ─── Report 03 — Cutting Report ──────────────────────────────────────────────

def _fill_cutting_summary(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    _fix_output_display(ws, 11, 3, output_unit, unit_system)
    # RC-CERT-002 SUM-003: totais de material/uso/descarte sao agregados de
    # comprimento de BARRA -> seguem a unidade de barra congelada (m / ft).
    bar_unit = _bar_display_unit(unit_system)
    try:
        report_rows = _wb_rows("outputs/result.xlsx", "REPORT", skip_rows=1, require_col1=True)
        # REPORT: CutGroup(0) BarCombo(1) SolID(2) QtyBars(3) QtyPieces(4)
        #   MatComprado_mm(5) RetalhoConsumido_mm(6) MatProcessado_mm(7)
        #   Used_mm(8) WasteDrop_mm(9) YieldOp(10)
        total_bars   = sum(int(_safe(r[3], 0)) for r in report_rows)
        total_pieces = sum(int(_safe(r[4], 0)) for r in report_rows)
        total_mat    = sum(_parse_bar_len(r[7], unit_system) for r in report_rows)
        total_used   = sum(_parse_bar_len(r[8], unit_system) for r in report_rows)
        total_waste  = sum(_parse_bar_len(r[9], unit_system) for r in report_rows)
        global_yield = round(total_used / total_mat, 4) if total_mat else 0

        ws.cell(21, 3).value = total_bars
        ws.cell(21, 4).value = total_pieces
        _set_len(ws.cell(21, 6), _bar_unit_value(total_mat, unit_system), bar_unit)
        _set_len(ws.cell(21, 7), _bar_unit_value(total_used, unit_system), bar_unit)
        _set_len(ws.cell(21, 8), _bar_unit_value(total_waste, unit_system), bar_unit)
        _set_pct(ws.cell(21, 9), global_yield, output_unit)

        input_pieces = pd.get("pieces", [])
        pc_map = {}
        for p in input_pieces:
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg] = p.get("product_code", "")

        # RC-XLSX: row 33 in the template carries a yellow LARGURA= helper marker.
        _remove_row_fill(ws, 33)

        _data_start_cs = 25
        _clear_rows(ws, _data_start_cs, _data_start_cs + 30)
        for i, row in enumerate(report_rows):
            cg      = _safe(row[0])
            r       = _data_start_cs + i
            mat_mm  = _parse_bar_len(row[7], unit_system)
            used_mm = _parse_bar_len(row[8], unit_system)
            wst_mm  = _parse_bar_len(row[9], unit_system)
            yld     = round(used_mm / mat_mm, 4) if mat_mm else 0
            if i > 0:
                copy_row_style(ws, _data_start_cs, r)
            ws.cell(r, 1).value = pc_map.get(cg, "")
            ws.cell(r, 2).value = cg
            ws.cell(r, 3).value = int(_safe(row[3], 0))
            ws.cell(r, 4).value = int(_safe(row[4], 0))
            ws.cell(r, 5).value = _safe(row[1])
            _set_len(ws.cell(r, 6), _bar_unit_value(mat_mm, unit_system), bar_unit)
            _set_len(ws.cell(r, 7), _bar_unit_value(used_mm, unit_system), bar_unit)
            _set_len(ws.cell(r, 8), _bar_unit_value(wst_mm, unit_system), bar_unit)
            _set_pct(ws.cell(r, 9), yld, output_unit)
    except Exception:
        pass


def _fill_cut_list(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    piece_unit = _piece_display_unit(unit_system, output_unit)
    _fix_output_display(ws, 11, 3, output_unit, unit_system)
    try:
        cl_data = _cutlist_rows()

        # RC-XLSX: row 29 in the template carries a yellow LARGURA= helper marker.
        _remove_row_fill(ws, 29)

        _data_start_cl = 18
        _pat_cl = _detect_pattern_length(ws, _data_start_cl)
        _clear_rows(ws, _data_start_cl, _data_start_cl + max(len(cl_data), 10) + 2)
        for i, p in enumerate(cl_data):
            r = _data_start_cl + i
            if i > 0:
                copy_row_style(ws, _data_start_cl + (i % _pat_cl), r)
            ws.cell(r, 1).value  = i + 1
            ws.cell(r, 2).value  = p["product_code"]
            ws.cell(r, 3).value  = p["piece_id"]
            ws.cell(r, 4).value  = p["piece_name"]
            ws.cell(r, 5).value  = p["cut_group"]
            ws.cell(r, 6).value  = p["bar_id"]
            # RC-007 CONGELAMENTO: PECAS sempre cm (METRICO) / output_unit (IMPERIAL).
            _set_len(ws.cell(r, 7), p["length_mm"], piece_unit)  # LENGTH
            ws.cell(r, 8).value  = p["assembly_tag"]         # ASSEMBLY TAG
            ws.cell(r, 9).value  = p["zone"]                 # ZONE
            ws.cell(r, 10).value = p["user_tag"]             # USER TAG
            ws.cell(r, 11).value = _translate_label(p["source_type"])  # SOURCE
    except Exception:
        pass


def _fill_cut_plan(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    _fix_output_display(ws, 10, 3, output_unit, unit_system)
    # RC-CERT-002 CP-004/CP-005: comprimento/uso/descarte de BARRA seguem a
    # unidade de barra congelada (m / ft), fonte unica = COST_BARS.
    bar_unit = _bar_display_unit(unit_system)
    cb_grp = _cb_by_group(unit_system)
    try:
        cp_rows = _wb_rows("outputs/result.xlsx", "CUT_PLAN", skip_rows=1)
        # cols: BarID(0) CutGroup(1) BarCombo(2) PieceCount(3) CutSeq(4)
        #       PieceIDs(5) NetUsed(6) Waste(7) Yield(8) SourceType(9)
        input_pieces = pd.get("pieces", [])
        pc_map  = {}
        stk_map = {}   # cut_group → stock_id (if sourced from stock)
        for p in input_pieces:
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg] = p.get("product_code", "")

        # RC-XLSX: row 26 in the template carries a yellow LARGURA= helper marker.
        _remove_row_fill(ws, 26)

        _data_start_cp = 18
        _pat_cp = _detect_pattern_length(ws, _data_start_cp)
        _clear_rows(ws, _data_start_cp, _data_start_cp + max(len(cp_rows), 10) + 2)
        for i, row in enumerate(cp_rows):
            cg      = _safe(row[1])
            src     = _safe(row[9])
            used_mm = _parse_bar_len(row[6], unit_system)
            wst_mm  = _parse_bar_len(row[7], unit_system)
            yld     = _num(row[8])
            r = _data_start_cp + i
            if i > 0:
                copy_row_style(ws, _data_start_cp + (i % _pat_cp), r)
            ws.cell(r, 1).value  = pc_map.get(cg, "")         # PRODUCT CODE
            ws.cell(r, 2).value  = cg                          # CUT GROUP
            ws.cell(r, 3).value  = _safe(row[0])               # BAR ID
            # RC-CERT-002 CP-004: Comprimento da Barra na unidade de barra
            # congelada (m / ft), nao no rotulo cru do Cut Engine.
            bar_len_mm = cb_grp.get(cg, {}).get("bar_len_mm", 0)
            _set_len(ws.cell(r, 4), _bar_unit_value(bar_len_mm, unit_system), bar_unit)  # BAR LENGTH
            ws.cell(r, 5).value  = _safe(row[3])               # PIECES count
            # RC-CERT-002 CP-005: separador decimal do rotulo "NxM" -> mesmo
            # padrao numerico do output_unit (in/mm).
            ws.cell(r, 6).value  = _fix_decimal_sep(_safe(row[4]), output_unit)  # CUT SEQUENCE (piece lengths)
            _set_len(ws.cell(r, 7), _bar_unit_value(used_mm, unit_system), bar_unit)  # USED
            _set_len(ws.cell(r, 8), _bar_unit_value(wst_mm, unit_system), bar_unit)   # WASTE
            _set_pct(ws.cell(r, 9), yld, output_unit)          # YIELD %
            ws.cell(r, 10).value = _translate_label(src)        # SOURCE
            ws.cell(r, 11).value = "-" if src == "NEW BAR" else _safe(row[0])  # STOCK ID
    except Exception:
        pass


def generate_cutting_report(active_tab=None):
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)

    dst = _stamped("03_Cutting_Report.xlsx")
    shutil.copy2(_template("03_Cutting_Report.xlsx"), dst)

    wb = load_workbook(dst)
    _fill_cutting_summary(wb["SUMMARY"], rpl, pd)
    _fill_cut_list(wb["CUT LIST"],       rpl, pd)
    _fill_cut_plan(wb["CUT PLAN"],       rpl, pd)

    _output_unit_03 = project_data_output_unit(pd)
    for sh_name in ["SUMMARY", "CUT LIST", "CUT PLAN"]:
        ws = wb[sh_name]
        _fix_unit_headers(ws, _output_unit_03)
        _apply_template_widths(ws)
        _apply_print_setup(ws, orientation="landscape",
                           print_area=f"A1:{get_column_letter(ws.max_column)}{ws.max_row}",
                           repeat_rows="17:17")

    if active_tab and active_tab in wb.sheetnames:
        wb.active = wb[active_tab]
    wb.save(dst)
    return dst


# ─── Report 04 — Stock Report ────────────────────────────────────────────────

def generate_stock_report(active_tab=None):
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    bar_unit    = _bar_display_unit(unit_system)

    dst = _stamped("04_Stock_Report.xlsx")
    shutil.copy2(_template("04_Stock_Report.xlsx"), dst)

    wb = load_workbook(dst)

    ws_s = wb["STOCK"]
    _fill_placeholders(ws_s, rpl)
    _apply_lang_labels(ws_s)
    _fix_output_display(ws_s, 10, 3, output_unit, unit_system)
    db_rows = []
    _data_start_st = 24
    try:
        from stock_repository_sqlite import load_stock_sqlite
        _div_st = 12.0 if _is_imperial(output_unit) else 1000.0
        _proj_name = (pd.get("project") or {}).get("Project_Name", "")

        # Tuple: índices [0..11]; [11]=dest_project (filtragem apenas, não exibido)
        _all_db_rows = [
            (
                _it.id,            # [0]
                _it.status,        # [1]
                _it.cut_group,     # [2]
                _it.profile,       # [3]
                (_it.length_internal or 0.0) / _div_st,  # [4]
                _it.weight_linear, # [5]
                _it.weight_total,  # [6]
                _it.value,         # [7]
                _it.project_origin,# [8]
                _it.bar_origin,    # [9]
                _it.date_entry,    # [10]
                _it.dest_project,  # [11] — projeto destino (consumo de estoque)
            )
            for _it in load_stock_sqlite()
        ]
        # Detalhe filtrado ao projeto atual:
        #   • project_origin == proj  → retalhos gerados por esta corrida
        #   • dest_project == proj    → estoque consumido por esta corrida
        if _proj_name:
            db_rows = [
                row for row in _all_db_rows
                if (_safe(row[8]) == _proj_name or _safe(row[11]) == _proj_name)
            ]
        else:
            db_rows = _all_db_rows

        # ── Priority 2: dynamic "Summary - Stock" table (H3:L8 + H10) ──────────
        # Aggregate ALL AVAILABLE stock items (global warehouse view — intentional).
        summary = {}
        order = []
        for row in _all_db_rows:
            if str(_safe(row[1])).strip().upper() != "AVAILABLE":
                continue
            cg = _safe(row[2])
            if cg not in summary:
                summary[cg] = {"qty": 0, "length": 0.0, "weight": 0.0, "value": 0.0}
                order.append(cg)
            g = summary[cg]
            g["qty"]    += 1
            g["length"] += _num(row[4])
            g["weight"] += _num(row[6])
            g["value"]  += _num(row[7])

        # Template STOCK sheet summary area: H5:L13 (9 data rows by design).
        # Row 14 = RUN ID metadata; row 23 H-L = detail column headers
        # ("Peso Total", "Valor residual", etc.) that MUST NOT be overwritten.
        # _clear_rows(ws_s, _data_start_st, ...) also clears ALL columns, so
        # any summary row >= _data_start_st (24) would be silently wiped.
        _SUMMARY_MAX_ROW = 13
        _summary_last = min(max(8, 10, 5 + len(order) - 1), _SUMMARY_MAX_ROW)
        for r in range(5, _SUMMARY_MAX_ROW + 1):
            for c in range(8, 13):  # H..L
                cell = ws_s.cell(r, c)
                if not isinstance(cell, MergedCell):
                    cell.value = None
            _remove_row_fill(ws_s, r)
        for i, cg in enumerate(order):
            r = 5 + i
            if r > _SUMMARY_MAX_ROW:
                break
            if i > 0:
                copy_row_style(ws_s, 5, r)
                ws_s.row_dimensions[r].hidden = False
            g = summary[cg]
            ws_s.cell(r, 8).value = cg
            _set_num(ws_s.cell(r, 9), g["qty"], output_unit, 0)
            _set_len(ws_s.cell(r, 10), g["length"], bar_unit)
            _set_weight(ws_s.cell(r, 11), g["weight"], output_unit)
            _set_money(ws_s.cell(r, 12), g["value"], output_unit)

        # RC-CERT-002 STK-005: "Codigo do Produto" por Cut Group (mesmo mapa
        # usado nos demais relatorios), em vez de coluna vazia.
        pc_map = {}
        for p in pd.get("pieces", []):
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg] = p.get("product_code", "")

        # HOTFIX (29/06/2026): col E ("Perfil") exibia profile=cut_group
        # (vindo do stock_engine, que so conhece o Cut Group — campo
        # congelado, nao pode ser alterado). A descricao real do perfil,
        # conforme INPUT, esta em stock_rules[cut_group]["description"]
        # (input_excel_converter.py:559/584). Lookup feito aqui, na camada
        # de relatorio (Nivel 4), sem tocar no motor (Nivel 1).
        _rule_map_st = pd.get("stock_rules", {})
        # HOTFIX (29/06/2026): stock_rules so cobre os cut_groups do projeto
        # ativo. CRITERIOS (mandante) cobre todos os perfis cadastrados —
        # fallback necessario para retalho ADD TO STOCK / projeto anterior.
        _crit_map_st = _criterios_description_map()

        _clear_rows(ws_s, _data_start_st, _data_start_st + max(len(db_rows) + 5, 20))
        for i, row in enumerate(db_rows):
            r = _data_start_st + i
            if i > 0:
                copy_row_style(ws_s, _data_start_st, r)
            _cg_st = _safe(row[2])
            ws_s.cell(r, 1).value  = pc_map.get(_cg_st, "")
            ws_s.cell(r, 2).value  = _safe(row[0])
            ws_s.cell(r, 3).value  = _translate_label(_safe(row[1]))
            ws_s.cell(r, 4).value  = _cg_st
            ws_s.cell(r, 5).value  = (
                _rule_map_st.get(_cg_st, {}).get("description", "")
                or _crit_map_st.get(str(_cg_st).upper().replace(" ", ""), "")
                or _safe(row[3])
            )
            _set_len(ws_s.cell(r, 6), row[4], bar_unit)
            _set_num(ws_s.cell(r, 7), row[5], output_unit)
            _set_weight(ws_s.cell(r, 8), row[6], output_unit)
            _set_money(ws_s.cell(r, 9), row[7], output_unit)
            ws_s.cell(r, 10).value = _safe(row[8])
            ws_s.cell(r, 11).value = _safe(row[9])
            _set_date(ws_s.cell(r, 12), row[10], output_unit)

        # Linhas com status AVAILABLE recebem texto verde — segundo passo para
        # não contaminar copy_row_style das linhas seguintes.
        for i, row in enumerate(db_rows):
            if str(_safe(row[1])).strip().upper() == "AVAILABLE":
                r = _data_start_st + i
                for c in range(1, 13):
                    cell = ws_s.cell(r, c)
                    if not isinstance(cell, MergedCell):
                        f = cell.font
                        cell.font = Font(
                            name=f.name, size=f.size,
                            bold=f.bold, italic=f.italic,
                            color="00B050",
                        )

        # RC-XLSX: scrub leftover yellow "ghost" rows from the template
        # (e.g. row 32 'OK' markers) that fall beyond the written data range.
        for _r in range(_data_start_st + len(db_rows), _data_start_st + max(len(db_rows) + 5, 20) + 1):
            _remove_row_fill(ws_s, _r)
    except Exception:
        pass

    # RC-CERT-008 G-006: o template traz ~1600 linhas e 2 colunas (M:N)
    # residuais e vazias muito alem dos dados reais (A1:N1629 vs A1:L_).
    # Apos preencher a tabela, remove esse excesso para que ws.dimensions
    # reflita o range real dos dados.
    _real_last_row_st = _data_start_st + len(db_rows) - 1 if db_rows else ws_s.max_row
    if ws_s.max_row > _real_last_row_st:
        ws_s.delete_rows(_real_last_row_st + 1, ws_s.max_row - _real_last_row_st)
    if ws_s.max_column > 12:
        ws_s.delete_cols(13, ws_s.max_column - 12)

    _last_row_st = max(ws_s.max_row, 30)
    _fix_unit_headers(ws_s, bar_unit)

    # RC-CERT-008 04-STK-006: cabecalhos de comprimento ("Comprimento" /
    # "Length" / "Longitud") nao traziam a unidade explicita. Acrescenta o
    # sufixo de unidade de saida (mesma unidade ja usada por _set_len nas
    # celulas) ao cabecalho do resumo (J4) e da tabela detalhada (F23).
    _len_suffix = _display_unit_suffix(bar_unit)
    for _hcell in (ws_s.cell(4, 10), ws_s.cell(23, 6)):
        if isinstance(_hcell.value, str) and "(" not in _hcell.value:
            _hcell.value = f"{_hcell.value} ({_len_suffix})"

    _apply_template_widths(ws_s)
    _apply_print_setup(ws_s, orientation="landscape",
                       print_area=f"A1:{get_column_letter(ws_s.max_column)}{_last_row_st}",
                       repeat_rows="23:23")

    ws_sc = wb["SCRAP"]
    _fill_placeholders(ws_sc, rpl)
    _apply_lang_labels(ws_sc)
    _fix_output_display(ws_sc, 10, 3, output_unit, unit_system)
    # RC-XLSX: the SCRAP table header lives at row 18 (after the project info
    # block in rows 3-14 and the section title/note in rows 16-17), so data
    # rows must start at 19 — not overwrite the project info block.
    _data_start_sc = 19
    try:
        # KM-022 BUG-A: audit_support.json e estatico e pode conter dados de
        # projeto anterior (contaminacao cross-project). Fonte correta: SQLite
        # filtrado por project_origin — consistente com HISTORY tab (KM-021).
        from stock_repository_sqlite import load_scrap_sqlite
        _proj_scraps = load_scrap_sqlite(project_origin=_proj_name) if _proj_name else []
        _rules = pd.get("stock_rules", {})
        _crit_map_sc = _criterios_description_map()
        if unit_system.startswith("MET"):
            _w_rates = {cg: _num(r.get("weight_kg_m"))    for cg, r in _rules.items()}
            _j_rates = {cg: _num(r.get("scrap_price_kg")) for cg, r in _rules.items()}
            _scr_div = 1000.0
        else:
            _w_rates = {cg: _num(r.get("weight_lb_ft"))   for cg, r in _rules.items()}
            _j_rates = {cg: _num(r.get("scrap_price_lb")) for cg, r in _rules.items()}
            _scr_div = 12.0
        _clear_rows(ws_sc, _data_start_sc, _data_start_sc + max(len(_proj_scraps) + 5, 10))
        for i, s in enumerate(_proj_scraps):
            r = _data_start_sc + i
            if i > 0:
                copy_row_style(ws_sc, _data_start_sc, r)
            ws_sc.cell(r, 1).value  = s.id
            ws_sc.cell(r, 2).value  = _translate_label(s.status or "SCRAP")
            ws_sc.cell(r, 3).value  = s.cut_group or ""
            _cg_sc = s.cut_group or ""
            ws_sc.cell(r, 4).value  = (
                _rules.get(_cg_sc, {}).get("description", "")
                or _crit_map_sc.get(str(_cg_sc).upper().replace(" ", ""), "")
                or s.profile or ""
            )
            # length_internal em mm (METRIC) ou in (IMPERIAL); _scr_div converte
            # para bar_unit (m ou ft) — mesma logica do _drop_value_by_group.
            _len_int  = s.length_internal or 0.0
            _len_out  = _len_int / _scr_div
            _set_len(ws_sc.cell(r, 5), _len_out, bar_unit)
            _cg       = s.cut_group or ""
            _w_rate   = _w_rates.get(_cg, 0)
            _w_total  = _len_int / _scr_div * _w_rate
            _residual = _w_total * _j_rates.get(_cg, 0)
            _set_num(ws_sc.cell(r, 6), _w_rate, output_unit)
            _set_weight(ws_sc.cell(r, 7), _w_total, output_unit)
            _set_money(ws_sc.cell(r, 8), _residual, output_unit)
            ws_sc.cell(r, 9).value  = s.project_origin or ""
            ws_sc.cell(r, 10).value = s.bar_origin or ""
            _set_date(ws_sc.cell(r, 11), s.date_entry or "", output_unit)
    except Exception:
        pass
    _last_row_sc = max(ws_sc.max_row, _data_start_sc)
    _fix_unit_headers(ws_sc, bar_unit)
    _apply_template_widths(ws_sc)
    _apply_print_setup(ws_sc, orientation="landscape",
                       print_area=f"A1:{get_column_letter(ws_sc.max_column)}{_last_row_sc}",
                       repeat_rows="18:18")

    ws_h = wb["HISTORY"]
    _fill_placeholders(ws_h, rpl)
    _apply_lang_labels(ws_h)
    _fix_output_display(ws_h, 10, 3, output_unit, unit_system)
    # RC-XLSX: the HISTORY table header lives at row 18 (after the project
    # info block in rows 3-14 and the section title/note in rows 16-17), so
    # data rows must start at 19 — not overwrite the project info block.
    _data_start_hi = 19
    try:
        from stock_repository_sqlite import load_history_sqlite
        _div_hi = 12.0 if _is_imperial(output_unit) else 1000.0
        h_rows = [
            (
                _hi.datetime,
                _hi.event,
                _hi.item_id,
                _hi.cut_group,
                (_hi.length_remnant or 0.0) / _div_hi,
                (_hi.length_piece or 0.0) / _div_hi,
                _hi.weight_linear,
                _hi.weight_total,
                _hi.value,
                _hi.project,
                _hi.bar_origin,
                _hi.operator,
                _hi.status,
                _hi.notes,
            )
            for _hi in load_history_sqlite(project=_proj_name or None)
        ]
        _clear_rows(ws_h, _data_start_hi, _data_start_hi + max(len(h_rows) + 5, 10))
        _fmt_dt = "mm/dd/yyyy hh:mm" if _is_imperial(output_unit) else "dd/mm/yyyy hh:mm"
        for i, row in enumerate(h_rows):
            r = _data_start_hi + i
            if i > 0:
                copy_row_style(ws_h, _data_start_hi, r)
            # A: timestamp preserving time
            _set_date(ws_h.cell(r, 1), row[0], output_unit)
            ws_h.cell(r, 1).number_format = _fmt_dt
            # B: event code → traduzido
            ws_h.cell(r, 2).value = _translate_label(_safe(row[1]))
            # C/D: stock_id / cut_group (text)
            ws_h.cell(r, 3).value = row[2]
            ws_h.cell(r, 4).value = row[3]
            # E/F: lengths already in display units
            _set_len(ws_h.cell(r, 5), row[4], bar_unit)
            _set_len(ws_h.cell(r, 6), row[5], bar_unit)
            # G: linear weight rate
            _set_num(ws_h.cell(r, 7), row[6], output_unit)
            # H: total weight
            _set_weight(ws_h.cell(r, 8), row[7], output_unit)
            # I: monetary value
            _set_money(ws_h.cell(r, 9), row[8], output_unit)
            # J/K/L: project / source bar / operator (text)
            for _c in [10, 11, 12]:
                if _c - 1 < len(row):
                    ws_h.cell(r, _c).value = row[_c - 1]
            # M/N: status anterior / status novo → traduzidos
            if 12 < len(row):
                ws_h.cell(r, 13).value = _translate_label(_safe(row[12]))
            if 13 < len(row):
                ws_h.cell(r, 14).value = _translate_label(_safe(row[13]))
    except Exception:
        pass
    _last_row_hi = max(ws_h.max_row, _data_start_hi)
    _fix_unit_headers(ws_h, bar_unit)
    # Cabeçalho F18 ("Comprimento da Peça") não tem token de unidade no template;
    # adiciona o sufixo aqui, da mesma forma que E18 já tem "Comprimento Retalho (ft)".
    _len_suffix_h = _display_unit_suffix(bar_unit)
    _piece_hdr = ws_h.cell(18, 6)
    if isinstance(_piece_hdr.value, str) and "(" not in _piece_hdr.value:
        _piece_hdr.value = f"{_piece_hdr.value} ({_len_suffix_h})"
    _apply_template_widths(ws_h)
    _apply_print_setup(ws_h, orientation="landscape",
                       print_area=f"A1:{get_column_letter(ws_h.max_column)}{_last_row_hi}",
                       repeat_rows="18:18")

    # Resetar seleção e tabSelected em cada sheet — impede:
    # (a) A1:XFD14 pre-selecionado → clipboard multi-area ao copiar
    # (b) tabSelected="1" em sheets nao-ativas → modo Agrupar automatico ao abrir
    _active_tab_name = (active_tab if (active_tab and active_tab in wb.sheetnames) else "STOCK")
    for _ws_name, _cell in [("STOCK", "A24"), ("SCRAP", "A24"), ("HISTORY", "A22")]:
        if _ws_name not in wb.sheetnames:
            continue
        _ws_sel = wb[_ws_name]
        try:
            _sv = _ws_sel.sheet_view
            # (a) reset sqref para celula unica
            for _sel in _sv.selection:
                _sel.activeCell = _cell
                _sel.sqref = _cell
            # (b) remover tabSelected de sheets nao-ativas (evita modo Agrupar)
            if _ws_name != _active_tab_name:
                _sv.tabSelected = False
        except Exception:
            pass

    if active_tab and active_tab in wb.sheetnames:
        wb.active = wb[active_tab]
    wb.save(dst)
    return dst


# ─── Report 05 — Cost Report ─────────────────────────────────────────────────

def _fill_cost_summary(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    _fix_output_display(ws, 9, 3, output_unit, unit_system)
    # RC-CERT-002 CS-006: Material Total / Descarte sao agregados de
    # comprimento de BARRA -> seguem a unidade de barra congelada (m / ft).
    bar_unit = _bar_display_unit(unit_system)
    try:
        cb_rows = _wb_rows("outputs/cost_result.xlsx", "COST_BARS", skip_rows=1)
        # COST_BARS: BarID(0) CutGroup(1) BarLen(2) Pieces(3) UsedLen(4) WasteLen(5)
        #            Yield(6) $/m(7) BarCost(8) UsedValue(9) WasteValue(10) Source(11)
        grp_bars  = defaultdict(int)
        grp_mat   = defaultdict(float)
        grp_cost  = defaultdict(float)
        grp_waste = defaultdict(float)
        grp_used  = defaultdict(float)
        grp_rate  = {}
        total_waste_cost = 0.0
        grp_pieces       = defaultdict(int)
        for row in cb_rows:
            cg = _safe(row[1])
            grp_bars[cg]  += 1
            grp_mat[cg]   += _parse_bar_len(row[2], unit_system)
            grp_used[cg]  += _parse_bar_len(row[4], unit_system)
            grp_cost[cg]      += _num(row[8])
            grp_waste[cg]     += _parse_bar_len(row[5], unit_system)
            grp_rate[cg]       = _num(row[7])
            total_waste_cost  += _num(row[10]) if len(row) > 10 else 0.0
            grp_pieces[cg]    += int(_safe(row[3], 0))

        total_mat    = sum(grp_mat.values())
        total_used   = sum(grp_used.values())
        total_cost   = sum(grp_cost.values())
        global_yield = round(total_used / total_mat, 4) if total_mat else 0
        cp        = _cost_project_map()
        scrap_rev = _num(cp.get("(-) Receita venda ferro velho (junkyard)"))
        stock_rtn = _num(cp.get("(-) Retalhos gerados para estoque"))
        # RC-CERT-002 CS-005: Custo Liquido fonte unica (igual a COST BY BAR
        # M16) = custo total - retalhos gerados p/ estoque - receita sucata.
        net_cost  = total_cost - stock_rtn - scrap_rev
        # RC-CERT-002 CS-003: breakdown por Cut Group dos mesmos totais
        # acima, para preencher REUSABLE STOCK ($) e SCRAP ($) por grupo.
        stock_by_grp, scrap_by_grp = _drop_value_by_group(pd)

        # RC-CERT-007 05-CS-002: H26 e' traduzido pelo template como
        # "total_waste_reusable_hdr" (DESCARTE TOTAL), mas H27 contem
        # stock_rtn (ESTOQUE Reaproveitavel, mesma fonte de COST SUMMARY
        # H21:H26 / PROJECT_SUMMARY H27:H32). Corrige o rotulo para
        # acompanhar o valor efetivamente exibido.
        # NOTE: rows shifted +8 after insert_rows(16, 8) in generate_cost_report.
        _h18 = ws.cell(26, 8)
        if not isinstance(_h18, MergedCell):
            import language_manager as _lm
            _h18.value = _lm.t("total_stock_reusable_hdr")
        ws.row_dimensions[26].height = 40

        _set_money(ws.cell(27, 6), total_cost, output_unit)
        _set_money(ws.cell(27, 8), stock_rtn, output_unit)
        _set_money(ws.cell(27, 9), scrap_rev, output_unit)
        _set_money(ws.cell(27, 10), net_cost, output_unit)
        # Rendimento Global no relatório de CUSTO = YIELD COST (eficiência financeira)
        _yield_cost_global = round(net_cost / total_cost, 4) if total_cost else 0.0
        _set_pct(ws.cell(27, 11), _yield_cost_global, output_unit)

        input_pieces = pd.get("pieces", [])
        pc_map = {}
        for p in input_pieces:
            cg = p.get("cut_group", "")
            if cg and cg not in pc_map:
                pc_map[cg] = p.get("product_code", "")

        # RC-CERT-008 05-CS-004: cut groups with zero new bars (100% stock
        # reuse, e.g. "1x2") have no COST_BARS rows and were missing from
        # this table entirely, so J19 (Net Cost) - which already accounts for
        # their stock/scrap drop value - did not equal sum(J21:J_last). Add a
        # row for those groups too (bars=0, material=0, cost=0) so the table
        # reconciles with J19, fonte unica _drop_value_by_group.
        _rate_key = "price_bar_m" if unit_system.startswith("MET") else "price_bar_ft"
        stock_rules = pd.get("stock_rules", {})
        extra_groups = []
        for cg in list(stock_by_grp) + list(scrap_by_grp):
            if cg not in grp_cost and cg not in extra_groups:
                extra_groups.append(cg)

        _data_start_csum = 29
        _clear_rows(ws, _data_start_csum, _data_start_csum + 30)
        all_groups = list(grp_cost.items()) + [(cg, 0.0) for cg in extra_groups]
        for i, (cg, cost) in enumerate(all_groups):
            r        = _data_start_csum + i
            total_g  = grp_mat.get(cg, 0.0)
            used_g   = grp_used.get(cg, 0.0)
            yld      = round(used_g / total_g, 4) if total_g else 0
            rate     = grp_rate.get(cg, 0) or _num(stock_rules.get(cg, {}).get(_rate_key))
            if i > 0:
                copy_row_style(ws, _data_start_csum, r)
            ws.cell(r, 1).value  = pc_map.get(cg, "")
            ws.cell(r, 2).value  = cg
            _set_num(ws.cell(r, 3), grp_bars.get(cg, 0), output_unit, 0)
            _set_bar_rate(ws.cell(r, 4), rate, unit_system)
            _set_len(ws.cell(r, 5), _bar_unit_value(total_g, unit_system), bar_unit)
            _set_money(ws.cell(r, 6), cost, output_unit)
            _set_len(ws.cell(r, 7), _bar_unit_value(grp_waste.get(cg, 0.0), unit_system), bar_unit)
            stock_val = stock_by_grp.get(cg, 0.0)
            scrap_val = scrap_by_grp.get(cg, 0.0)
            _set_money(ws.cell(r, 8), stock_val, output_unit)
            _set_money(ws.cell(r, 9), scrap_val, output_unit)
            _set_money(ws.cell(r, 10), cost - stock_val - scrap_val, output_unit)
            _set_pct(ws.cell(r, 11), yld, output_unit)

        # ── Métricas de Eficiência (custo desperdício, custo médio, economia) ──
        import language_manager as _lm_eff
        _, total_piece_cost = _cp_cost_by_group()
        total_pieces_csum = sum(grp_pieces.values())
        # Custo Médio por Peça = net_cost / n em runs normais.
        # Em runs 100% estoque total_cost=0 → net_cost é crédito negativo (retalho),
        # não o custo real das peças. Usar total_piece_cost (valorado pelo CRITERIOS).
        _avg_base = total_piece_cost if total_cost == 0 and total_piece_cost else net_cost
        avg_piece_cost = round(_avg_base / total_pieces_csum, 4) if total_pieces_csum else 0.0

        naive_cost = 0.0
        for _cg, _n_bars in grp_bars.items():
            if _n_bars > 0:
                naive_cost += grp_pieces.get(_cg, 0) * (grp_cost[_cg] / _n_bars)
        savings = max(naive_cost - total_cost, 0.0)
        savings_pct = savings / naive_cost if naive_cost > 0 else 0.0

        _mr = 17  # fixed: metrics header goes ABOVE the section header at R24
        copy_row_style(ws, _data_start_csum, _mr)
        _mr_cell = ws.cell(_mr, 1)
        _mr_cell.value = _lm_eff.t("cost_efficiency_metrics_hdr")
        _mrf = _mr_cell.font
        _mr_cell.font = Font(name=_mrf.name, size=_mrf.size, bold=True, color=_mrf.color)
        _mr_cell.alignment = Alignment(horizontal="left", vertical="center")

        for _ei, (_lbl_key, _val, _pct) in enumerate([
            ("cost_waste_cost_lbl", stock_rtn + scrap_rev, 1.0 - global_yield),
            ("cost_avg_piece_lbl",  avg_piece_cost,   None),
            ("cost_naive_lbl",      naive_cost,       None),
            ("cost_savings_lbl",    savings,          savings_pct),
        ]):
            _re = _mr + 1 + _ei
            copy_row_style(ws, _data_start_csum, _re)
            ws.cell(_re, 1).value = _lm_eff.t(_lbl_key)
            ws.cell(_re, 1).alignment = Alignment(horizontal="left", vertical="center", wrap_text=False)
            ws.row_dimensions[_re].height = 15
            _set_money(ws.cell(_re, 6), _val, output_unit)
            if _pct is not None:
                _set_pct(ws.cell(_re, 11), _pct, output_unit)
            else:
                _pct_cell = ws.cell(_re, 11)
                _pct_cell.value = "-"
                _pct_cell.alignment = Alignment(horizontal="center", vertical="center")

        _re_note = _mr + 5
        copy_row_style(ws, _data_start_csum, _re_note)
        _note_cell = ws.cell(_re_note, 1)
        _note_cell.value = _lm_eff.t("cost_efficiency_note")
        _notef = _note_cell.font
        _note_cell.font = Font(name=_notef.name, size=9, italic=True, color=_notef.color)
        _note_cell.alignment = Alignment(horizontal="left", vertical="center")
    except Exception:
        pass


def _fill_cost_by_bar(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    _fix_output_display(ws, 9, 3, output_unit, unit_system)
    # RC-CERT-002 CBB-003: Comprimento da Barra / Utilizado / Descarte sao
    # comprimentos de BARRA -> unidade de barra congelada (m / ft).
    bar_unit = _bar_display_unit(unit_system)
    try:
        cb_rows = _wb_rows("outputs/cost_result.xlsx", "COST_BARS", skip_rows=1)
        # Totals for R16
        total_bar_cost = sum(_num(r[8]) for r in cb_rows)
        total_used_mm  = sum(_parse_bar_len(r[4], unit_system) for r in cb_rows)
        total_waste_mm = sum(_parse_bar_len(r[5], unit_system) for r in cb_rows)
        total_mm       = total_used_mm + total_waste_mm
        global_yld     = round(total_used_mm / total_mm, 4) if total_mm else 0
        cp        = _cost_project_map()
        stock_rtn = _num(cp.get("(-) Retalhos gerados para estoque"))
        scrap_rev = _num(cp.get("(-) Receita venda ferro velho (junkyard)"))
        net_cost  = total_bar_cost - stock_rtn - scrap_rev

        _set_money(ws.cell(16, 9), total_bar_cost, output_unit)
        _set_money(ws.cell(16, 10), stock_rtn, output_unit)
        _set_money(ws.cell(16, 11), scrap_rev, output_unit)
        _set_pct(ws.cell(16, 12), global_yld, output_unit)
        _set_money(ws.cell(16, 13), net_cost, output_unit)

        _data_start_cbar = 18
        _clear_rows(ws, _data_start_cbar, _data_start_cbar + max(len(cb_rows) + 5, 10))
        for i, row in enumerate(cb_rows):
            r         = _data_start_cbar + i
            bar_len   = _parse_bar_len(row[2], unit_system)
            used_len  = _parse_bar_len(row[4], unit_system)
            waste_len = _parse_bar_len(row[5], unit_system)
            rate      = _num(row[7])
            bar_cost  = _num(row[8])
            yld_pct   = _num(row[6])
            if i > 0:
                copy_row_style(ws, _data_start_cbar, r)
            ws.cell(r, 2).value  = _safe(row[0])
            ws.cell(r, 3).value  = _safe(row[1])
            _set_len(ws.cell(r, 4), _bar_unit_value(bar_len, unit_system), bar_unit)
            ws.cell(r, 5).value  = _safe(row[3])
            _set_len(ws.cell(r, 6), _bar_unit_value(used_len, unit_system), bar_unit)
            _set_len(ws.cell(r, 7), _bar_unit_value(waste_len, unit_system), bar_unit)
            _set_bar_rate(ws.cell(r, 8), rate, unit_system)
            _set_money(ws.cell(r, 9), bar_cost, output_unit)
            _set_money(ws.cell(r, 10), _num(row[9]), output_unit)
            # RC-CERT-008 05-CBB-005: "Valor Descartado" (K) deve vir de
            # COST_BARS."Waste Value" (fonte unica), igual ao padrao ja usado
            # para "Valor Utilizado" (J = row[9]) acima. A formula anterior
            # (waste_len * rate / 1000.0) assumia waste_len em mm; agora
            # waste_len ja vem na unidade de barra (m/ft) e o resultado
            # arredondava para 0.00, quebrando I = J + K.
            _set_money(ws.cell(r, 11), _num(row[10]), output_unit)
            _set_pct(ws.cell(r, 12), yld_pct, output_unit)
            ws.cell(r, 13).value = _translate_label(_safe(row[11])) if len(row) > 11 else ""
    except Exception:
        pass


def _fill_cost_by_piece(ws, rpl, pd):
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    piece_unit = _piece_display_unit(unit_system, output_unit)
    _fix_output_display(ws, 9, 3, output_unit, unit_system)
    try:
        cp_rows = _wb_rows("outputs/cost_result.xlsx", "COST_PIECES", skip_rows=4)
        # PieceID(0) BarID(1) CutGroup(2) AssemblyCode(3) FamilyCode(4)
        # DescPeca(5) CompCortado(6) $/m(7) CustoPeca(8) %CustoBarra(9) Source(10)

        total_piece_cost = sum(_num(r[8]) for r in cp_rows)
        _c17 = ws.cell(17, 9)
        if not isinstance(_c17, MergedCell):
            _set_money(_c17, total_piece_cost, output_unit)

        # RC-CERT-002 CBP-004: I17 = soma de I19:I159 (custo atribuido as
        # pecas), nao o custo total de compra de barras. O rotulo precisa
        # refletir a regra efetivamente aplicada (sincronizar rotulo x valor).
        _i16 = ws.cell(16, 9)
        if not isinstance(_i16, MergedCell):
            import language_manager as _lm
            _i16.value = _lm.t("total_piece_cost_hdr")

        _data_start_cpc = 19
        _pat_cpc = _detect_pattern_length(ws, _data_start_cpc)
        _clear_rows(ws, _data_start_cpc, _data_start_cpc + max(len(cp_rows) + 5, 10))
        for i, row in enumerate(cp_rows):
            r = _data_start_cpc + i
            if i > 0:
                copy_row_style(ws, _data_start_cpc + (i % _pat_cpc), r)
            ws.cell(r, 1).value  = _safe(row[0])
            ws.cell(r, 2).value  = _safe(row[1])
            ws.cell(r, 3).value  = _safe(row[2])
            ws.cell(r, 4).value  = _safe(row[3])
            ws.cell(r, 5).value  = _safe(row[4])
            ws.cell(r, 6).value  = _safe(row[5])
            # RC-007 CONGELAMENTO: PECAS sempre cm (METRICO) / output_unit (IMPERIAL).
            _set_len(ws.cell(r, 7), _num(row[6]), piece_unit)
            _set_bar_rate(ws.cell(r, 8), _num(row[7]), unit_system)
            _set_money(ws.cell(r, 9), _num(row[8]), output_unit)
            ws.cell(r, 10).value = _translate_label(_safe(row[10] if len(row) > 10 else row[9]))
    except Exception:
        pass


def generate_cost_report(active_tab=None):
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)

    dst = _stamped("05_Cost_Report.xlsx")
    shutil.copy2(_template("05_Cost_Report.xlsx"), dst)

    wb = load_workbook(dst)
    # Insert 8 blank rows above R16 so efficiency metrics land at R17-R22
    # (above the "RESUMO DE CUSTOS" section header which shifts from R16 to R24).
    wb["COST SUMMARY"].insert_rows(16, 8)
    _fill_cost_summary(wb["COST SUMMARY"],  rpl, pd)
    _fill_cost_by_bar(wb["COST BY BAR"],    rpl, pd)
    _fill_cost_by_piece(wb["COST BY PIECE"], rpl, pd)

    # Cost Report template has no PROJETO_XXXXXXXX placeholder → write Run ID explicitly
    # R15 is a blank spacer in COST SUMMARY and COST BY BAR; COST BY PIECE uses R15 as
    # section title ("COST BY PIECE — Individual Traceability") — only write if R15 is blank.
    run_id_val = rpl.get("PROJETO_XXXXXXXX", "")
    for sh_name in ["COST SUMMARY", "COST BY BAR", "COST BY PIECE"]:
        ws = wb[sh_name]
        try:
            import language_manager as _lm
            rid_label = _lm.t("run_id")
            if rid_label == "run_id":
                rid_label = "Run ID"
        except Exception:
            rid_label = "Run ID"
        _r15c1 = ws.cell(15, 1)
        _r15_occupied = (not isinstance(_r15c1, MergedCell)
                         and _r15c1.value is not None
                         and str(_r15c1.value).strip() != "")
        if not _r15_occupied:
            if not isinstance(_r15c1, MergedCell):
                _r15c1.value = rid_label
            if not isinstance(ws.cell(15, 3), MergedCell):
                ws.cell(15, 3).value = run_id_val

    _output_unit_05 = project_data_output_unit(pd)
    _unit_system_05 = project_data_unit_system(pd)
    _piece_unit_05 = _piece_display_unit(_unit_system_05, _output_unit_05)
    _bar_unit_05 = _bar_display_unit(_unit_system_05)

    for sh_name in ["COST SUMMARY", "COST BY BAR", "COST BY PIECE"]:
        ws = wb[sh_name]
        _fix_unit_headers(ws, _bar_unit_05 if sh_name != "COST BY PIECE" else _piece_unit_05)
        _apply_template_widths(ws)
        # COST SUMMARY: per-group table header shifted to R28 after insert_rows(16,8)
        _rpt_rows = "28:28" if sh_name == "COST SUMMARY" else "18:18"
        _apply_print_setup(ws, orientation="landscape",
                           print_area=f"A1:{get_column_letter(ws.max_column)}{ws.max_row}",
                           repeat_rows=_rpt_rows)

    # RC-CERT-007 02-PO-004/05-CBB-003: cabecalhos "$/linear"/"$/..." (template
    # ou normalizados por _fix_unit_headers acima) representam a taxa por
    # unidade de BARRA ($/m METRICO | $/ft IMPERIAL), igual ao COST_BARS/
    # COST_PIECES col7 (_set_bar_rate). Aplicado APOS _fix_unit_headers para
    # que o sufixo correto nao seja sobrescrito pelo regex de normalizacao.
    for _sh_name, _r, _c in (("COST SUMMARY", 28, 4), ("COST BY BAR", 17, 8), ("COST BY PIECE", 18, 8)):
        _cell = wb[_sh_name].cell(_r, _c)
        if not isinstance(_cell, MergedCell):
            _cell.value = f"$/{_bar_unit_05}"

    # RC-007 CONGELAMENTO: "COST BY PIECE" col 7 (CompCortado) e' uma coluna de
    # PECA (sempre cm em METRICO), mas _fix_unit_headers acima aplicou o
    # output_unit (BAR) ao placeholder "{unit}". Corrige o sufixo de unidade
    # do cabecalho para refletir a unidade real de PECA.
    if _piece_unit_05 != _output_unit_05:
        _ws_cbp = wb["COST BY PIECE"]
        _hdr_cell = _ws_cbp.cell(18, 7)
        if isinstance(_hdr_cell.value, str):
            _hdr_cell.value = _hdr_cell.value.replace(
                f"({_output_unit_05})", f"({_piece_unit_05})")

    # RC-CERT-002 CS-006: "COST SUMMARY" col G (Descarte) e' um agregado de
    # comprimento de BARRA -> _bar_display_unit (ft/m), mas _fix_unit_headers
    # acima aplicou o output_unit (ex.: "in"). Corrige o sufixo do cabecalho
    # para acompanhar a unidade efetivamente usada na coluna G21:G26.
    if _bar_unit_05 != _output_unit_05:
        _hdr_g20 = wb["COST SUMMARY"].cell(28, 7)
        if isinstance(_hdr_g20.value, str):
            _hdr_g20.value = re.sub(r'\([a-zA-Z]+\)\s*$', f'({_bar_unit_05})', _hdr_g20.value)

    if active_tab and active_tab in wb.sheetnames:
        wb.active = wb[active_tab]
    wb.save(dst)
    return dst


# ─── Report 06 — QR Labels ───────────────────────────────────────────────────

def _validate_qr_vs_cutlist():
    """
    Verify QR_LABELS rows match CUT_LIST rows.
    Raises RuntimeError listing mismatches if any are found.
    """
    cl_rows = _wb_rows("outputs/result.xlsx", "CUT_LIST", skip_rows=1)
    qr_rows = _wb_rows("outputs/result.xlsx", "QR_LABELS", skip_rows=1)

    # Build CUT_LIST lookup by piece_id
    cl_map = {}
    for r in cl_rows:
        pid = str(_safe(r[2]))   # Piece ID = col 3
        cl_map[pid] = r

    errors = []
    for i, qr in enumerate(qr_rows):
        pid      = str(_safe(qr[1]))   # QR Piece ID = col 2
        qr_len   = parse_imperial_length(qr[2])
        qr_cg    = str(_safe(qr[4]))
        qr_zone  = str(_safe(qr[3]))   # Zone/Area in QR (col 4)
        qr_tag   = str(_safe(qr[5]))

        cl = cl_map.get(pid)
        if cl is None:
            errors.append(f"Row {i+1}: Piece ID {pid!r} not found in CUT_LIST")
            continue
        cl_len  = _num(cl[6])
        cl_cg   = str(_safe(cl[4]))
        cl_zone = str(_safe(cl[9]))
        cl_tag  = str(_safe(cl[8]))

        if abs(qr_len - cl_len) > 0.04:   # 1/16" rounding tolerance
            errors.append(f"Piece {pid}: Length mismatch QR={qr_len} CL={cl_len}")
        if qr_cg != cl_cg:
            errors.append(f"Piece {pid}: CutGroup mismatch QR={qr_cg!r} CL={cl_cg!r}")
        if qr_zone != cl_zone:
            errors.append(f"Piece {pid}: Zone mismatch QR={qr_zone!r} CL={cl_zone!r}")
        if qr_tag != cl_tag:
            errors.append(f"Piece {pid}: UserTag mismatch QR={qr_tag!r} CL={cl_tag!r}")

    if errors:
        raise RuntimeError(
            "QR_LABELS ≠ CUT_LIST validation FAIL:\n" + "\n".join(errors[:10])
        )


def _make_qr_image(data: str, width_px: int = 58, height_px: int = 58):
    """Generate a QR code as an in-memory PNG and return it as an openpyxl
    Image, scaled to (width_px, height_px) for embedding in a worksheet."""
    import qrcode
    from openpyxl.drawing.image import Image as XLImage
    qr = qrcode.QRCode(border=2, box_size=6)
    qr.add_data(str(data))
    qr.make(fit=True)
    img = qr.make_image(fill_color="black", back_color="white")
    buf = io.BytesIO()
    img.save(buf, format="PNG")
    buf.seek(0)
    xl_img = XLImage(buf)
    xl_img.width = width_px
    xl_img.height = height_px
    return xl_img


def generate_qr_labels():
    pd = _load_json("inputs/input_001.json")
    rpl = _build_replacements(pd)
    proj = pd.get("project", {})
    output_unit = project_data_output_unit(pd)
    unit_system = project_data_unit_system(pd)
    piece_unit = _piece_display_unit(unit_system, output_unit)

    proj_name = proj.get("Project_Name", "")
    proj_id   = proj.get("Numero_do_Projeto", "")
    run_id    = rpl.get("PROJETO_XXXXXXXX", "")

    # Validate QR vs CUT_LIST consistency before generating
    _validate_qr_vs_cutlist()

    dst = _stamped("06_QR_Labels.xlsx")
    shutil.copy2(_template("06_QR_Labels.xlsx"), dst)

    wb = load_workbook(dst)
    ws = wb["QR_DATA"]
    _fill_placeholders(ws, rpl)
    _apply_lang_labels(ws)
    _fix_output_display(ws, 9, 3, output_unit, unit_system)

    # Fix template title typo "CQR_DATA" in the generated copy
    try:
        import language_manager as _lm
        payload_label = _lm.t("qr_data_title")
        if payload_label == "qr_data_title":  # key not found, use safe default
            payload_label = "EngCut Engine — QR DATA — Engine Payload"
    except Exception:
        payload_label = "EngCut Engine — QR DATA — Engine Payload"
    for _r in [1]:
        for _c in range(1, ws.max_column + 1):
            cell = ws.cell(_r, _c)
            if isinstance(cell, MergedCell):
                continue
            if cell.value and "QR" in str(cell.value).upper():
                cell.value = payload_label
                break

    # Build lookup for Assembly Tag and Family Code from CUT_LIST
    cl_data = _cutlist_rows()
    cl_map  = {p["piece_id"]: p for p in cl_data}

    # Build input piece lookup for family code
    input_pieces = pd.get("pieces", [])
    fam_map = {}
    for p in input_pieces:
        cg = p.get("cut_group", "")
        if cg:
            fam_map[cg] = p.get("commercial_key", p.get("product_code", ""))

    # RC-XLSX Priority 1: drop the leftover ghost placeholder images baked into
    # the QR_DATA template (1cm x 1cm boxes in column P) before drawing real ones.
    ws._images = []

    _data_start_qr = 23
    _qr_count = 0
    _first_row = None
    try:
        qr_rows = _wb_rows("outputs/result.xlsx", "QR_LABELS", skip_rows=1)
        # QR_LABELS: BarID(0) PieceID(1) Length(2) Assembly/Zone(3) CutGroup(4)
        #            UserTag(5) QR-DATA(6) QR_LABEL(7) QRCODE(8)
        _pat_qr = _detect_pattern_length(ws, _data_start_qr)
        _clear_rows(ws, _data_start_qr, _data_start_qr + max(len(qr_rows), 10) + 2)
        for i, row in enumerate(qr_rows):
            r       = _data_start_qr + i
            pid     = _safe(row[1])
            cg      = _safe(row[4])
            cl      = cl_map.get(pid, {})
            asm_tag = cl.get("assembly_tag", "") if cl else ""
            zone    = _safe(row[3])       # Area/Zone from QR_LABELS col 4
            user_tag = _safe(row[5])
            bar_id  = _safe(row[0])
            fam     = fam_map.get(cg, "")
            len_val = (parse_imperial_length(row[2]) if not unit_system.startswith("MET")
                       else _num(row[2]))
            # RC-007 CONGELAMENTO: PECAS sempre cm (METRICO) / in (IMPERIAL).
            len_disp = f"{_fmt_num_locale(len_val, piece_unit)} {piece_unit}"
            # RC-CERT-002 QD-004: normalize decimal separator of the raw
            # QR-DATA/QR_LABEL payload strings to match output_unit locale.
            qr_t1   = _fix_decimal_sep(_safe(row[6]), output_unit)
            qr_t2   = _fix_decimal_sep(_safe(row[7]), output_unit)
            qr_t3   = f"{pid} | L={len_disp} | {cg} | {zone} | {asm_tag} | {fam} | {run_id}"
            qr_t4   = f"https://engcut.app/qr/{pid}"
            if i > 0:
                copy_row_style(ws, _data_start_qr + (i % _pat_qr), r)
            ws.cell(r, 1).value  = bar_id           # BAR ID
            ws.cell(r, 2).value  = pid              # PIECE ID
            _set_len(ws.cell(r, 3), len_val, piece_unit)  # LENGTH
            ws.cell(r, 4).value  = asm_tag          # ASSEMBLY (= Assembly Tag)
            ws.cell(r, 5).value  = cg               # CUT GROUP
            ws.cell(r, 6).value  = user_tag         # USER TAG
            ws.cell(r, 7).value  = zone             # ZONE (= Area/Zone)
            ws.cell(r, 8).value  = fam              # FAMILY CODE
            ws.cell(r, 9).value  = proj_id          # PROJECT ID
            ws.cell(r, 10).value = run_id           # RUN ID
            ws.cell(r, 11).value = proj_name        # PROJECT NAME
            ws.cell(r, 12).value = qr_t1            # QR-DATA T1 minimal
            ws.cell(r, 13).value = qr_t2            # QR-DATA T2 obra
            ws.cell(r, 14).value = qr_t3            # QR-DATA T3 BIM
            ws.cell(r, 15).value = qr_t4            # QR-DATA T4 URL

            # RC-XLSX Priority 1: render the QR Code image into col P
            try:
                ws.row_dimensions[r].height = 45
                ws.add_image(_make_qr_image(qr_t2), f"P{r}")
            except Exception:
                pass

            if i == 0:
                _first_row = {
                    "pid": pid, "len_val": len_val, "cg": cg, "zone": zone,
                    "user_tag": user_tag, "asm_tag": asm_tag, "bar_id": bar_id,
                    "qr_t2": qr_t2,
                }
        _qr_count = len(qr_rows)
    except RuntimeError:
        raise
    except Exception:
        pass

    # RC-XLSX Priority 1: restore the QR_LABEL print-preview area (B21:E25 +
    # QR image at F21) using the first piece of the project.
    if _first_row and "QR_LABEL" in wb.sheetnames:
        ws_lbl = wb["QR_LABEL"]
        ws_lbl.cell(21, 4).value = _first_row["pid"]                       # D21 PIECE ID
        _set_len(ws_lbl.cell(22, 3), _first_row["len_val"], piece_unit)    # C22 LENGTH
        ws_lbl.cell(22, 5).value = _first_row["cg"]                        # E22 GROUP
        ws_lbl.cell(23, 3).value = _first_row["zone"]                      # C23 ZONE
        ws_lbl.cell(23, 5).value = _first_row["user_tag"]                  # E23 TAG
        ws_lbl.cell(24, 3).value = _first_row["asm_tag"]                   # C24 ASSEMBLY
        ws_lbl.cell(24, 5).value = _first_row["bar_id"]                    # E24 BAR
        ws_lbl.cell(25, 2).value = _first_row["qr_t2"]                     # B25 payload preview
        try:
            ws_lbl.add_image(_make_qr_image(_first_row["qr_t2"], 110, 110), "F21")
            # RC-CERT-008 06-QL-001: a celula traz o texto placeholder '[ QR ]'
            # do template; com a imagem real do QR adicionada, o texto deve
            # ser removido para nao sobrepor o preview.
            ws_lbl.cell(21, 6).value = None
        except Exception:
            pass

    _last_row_qr = _data_start_qr + max(_qr_count, 1) + 2
    _fix_unit_headers(ws, output_unit)
    _apply_template_widths(ws)
    _apply_print_setup(ws, orientation="landscape",
                       print_area=f"A1:O{_last_row_qr}",
                       repeat_rows="22:22")

    # Apply placeholders, language labels, and template widths to QR_LABEL
    # and THERMAL_CONFIG sheets too (RC-XLSX FINAL P2: zero mistura de idiomas)
    for _sh in ["QR_LABEL", "THERMAL_CONFIG"]:
        if _sh in wb.sheetnames:
            _ws_sh = wb[_sh]
            _fill_placeholders(_ws_sh, rpl)
            _apply_lang_labels(_ws_sh)
            _apply_template_widths(_ws_sh)
    wb.save(dst)
    return dst


# ─── Helper: output unit from project data ────────────────────────────────────

def project_data_output_unit(pd: dict) -> str:
    proj = pd.get("project", {})
    return pd.get("output_unit", proj.get("Unit_Output", "mm"))


def project_data_unit_system(pd: dict) -> str:
    proj = pd.get("project", {})
    return str(pd.get("unit_system", proj.get("Unit_System", "METRIC"))).upper()


def _piece_display_unit(unit_system: str, output_unit: str) -> str:
    """RC-007 CONGELAMENTO: unidade de exibicao de comprimento de PECAS.

    METRIC sempre "cm", independente de output_unit.
    IMPERIAL: usa _display_unit_suffix para normalizar o codigo interno:
      decimal_inches/fractional_inches -> "in" | decimal_feet/ft -> "ft".
    Nunca expor o codigo interno (ex.: "decimal_feet") como label de usuario.
    """
    if unit_system.startswith("MET"):
        return "cm"
    return _display_unit_suffix(output_unit)


def _bar_display_unit(unit_system: str) -> str:
    """RC-CERT-002 PRIORIDADE 1 (congelado): unidade de exibicao de comprimento
    de BARRAS. METRIC -> "m". IMPERIAL -> "ft"."""
    return "m" if unit_system.startswith("MET") else "ft"


def _bar_unit_value(value, unit_system: str) -> float:
    """RC-CERT-008 G-003/03-SUM-002/03-CP-002/02-PO-002/05-CS-005/05-CBB-002:
    bar-length values from outputs/result.xlsx (REPORT, CUT_PLAN) and
    outputs/cost_result.xlsx (COST_BARS) are already expressed in the frozen
    bar display unit (m for METRIC, ft for IMPERIAL) by the Cut/Cost Engine —
    no further conversion is applied here. Kept as a pass-through so call
    sites stay self-documenting about which unit a value represents."""
    return _num(value)


# ─── Public API ───────────────────────────────────────────────────────────────

REPORT_TYPES = {
    "project_summary": (generate_project_summary,  None),
    "project_history": (generate_project_summary,  "BALANÇO"),
    "purchase_order":  (generate_purchase_order,   None),
    "cutting_report":  (generate_cutting_report,   "SUMMARY"),
    "cut_summary":     (generate_cutting_report,   "SUMMARY"),
    "cut_list":        (generate_cutting_report,   "CUT LIST"),
    "cut_plan":        (generate_cutting_report,   "CUT PLAN"),
    "stock_report":    (generate_stock_report,     "STOCK"),
    "stock":           (generate_stock_report,     "STOCK"),
    "scrap":           (generate_stock_report,     "SCRAP"),
    "history":         (generate_stock_report,     "HISTORY"),
    "cost_report":     (generate_cost_report,      "COST SUMMARY"),
    "cost_summary":    (generate_cost_report,      "COST SUMMARY"),
    "cost_by_bar":     (generate_cost_report,      "COST BY BAR"),
    "cost_by_piece":   (generate_cost_report,      "COST BY PIECE"),
    "qr_labels":       (generate_qr_labels,        None),
}


def generate_report(report_type: str, tab_name: str = None) -> str:
    entry = REPORT_TYPES.get(report_type)
    if entry is None:
        raise ValueError(f"Unknown report type: {report_type}")
    fn, default_tab = entry
    active = tab_name or default_tab
    try:
        if active and fn in (generate_cutting_report, generate_stock_report,
                             generate_cost_report, generate_project_summary):
            return fn(active_tab=active)
        return fn()
    except (FileNotFoundError, RuntimeError):
        raise
    except Exception as exc:
        raise RuntimeError(str(exc)) from exc
