"""
cost_engine.py
==============
ENGCUT — Engine de Custo Financeiro.

Lê result.xlsx + audit_support.json + input_001.json e produz
cost_result.xlsx com três abas: COST_BARS, COST_PIECES, COST_PROJECT.

Fix F-014 (v6): usa imperial_parser.py para leitura de comprimentos
imperiais fracionários ('88 1/2"', '7.375 ft', etc.).
"""

import json
from fractions import Fraction
import re
from pathlib import Path
from collections import defaultdict

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

from imperial_parser import (
    parse_piece_length_imperial,
    parse_bar_length_imperial,
    is_imperial_base,
)

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

ROOT = _get_root()
INPUT_PATH    = ROOT / "inputs"  / "input_001.json"
RESULT_PATH   = ROOT / "outputs" / "result.xlsx"
SUPPORT_PATH  = ROOT / "outputs" / "audit_support.json"
OUTPUT_PATH   = ROOT / "outputs" / "cost_result.xlsx"
HISTORY_PATH  = ROOT / "outputs" / "cost_project_history.json"

# ── Estilos ──────────────────────────────────────────────────────────────────
H_FILL  = PatternFill("solid", fgColor="1F4E78")
H_FONT  = Font(bold=True, color="FFFFFF", name="Calibri", size=11)
S_FILL  = PatternFill("solid", fgColor="E2EFDA")   # summary row
T_FILL  = PatternFill("solid", fgColor="D6E4F0")   # title row
CENTER  = Alignment(horizontal="center", vertical="center")
RIGHT   = Alignment(horizontal="right",  vertical="center")
LEFT    = Alignment(horizontal="left",   vertical="center")
FMT_2   = "#,##0.00"
FMT_4   = "#,##0.0000"
FMT_PCT = "0.00%"
FMT_CUR = '"$"#,##0.00'
FMT_MULT = '0.00x'


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

def _read_cost_length(value, base: str, context: str = "piece") -> float:
    """Read length respecting unit base — uses imperial_parser for IMPERIAL."""
    if not is_imperial_base(base):
        try:
            return float(value) if value not in (None, "", "N/A", "-") else 0.0
        except (ValueError, TypeError):
            return 0.0
    if context == "bar":
        return parse_bar_length_imperial(value)
    return parse_piece_length_imperial(value)


def _parse_display_number(value):
    """Parse numeric text tolerant to BR/US separators and unit suffixes."""
    if value in (None, "", "N/A", "-"):
        return 0.0
    if isinstance(value, (int, float)):
        return float(value)

    text = str(value).strip()
    text = text.replace("$", "")

    m = re.search(r"[-+]?\d[\d.,]*", text)
    if not m:
        return 0.0

    num = m.group(0)

    if "," in num and "." in num:
        # 8.000,00 -> 8000.00 | 8,000.00 -> 8000.00
        if num.rfind(",") > num.rfind("."):
            num = num.replace(".", "").replace(",", ".")
        else:
            num = num.replace(",", "")
    elif "," in num:
        num = num.replace(",", ".")

    try:
        return float(num)
    except Exception:
        return 0.0


def _sf(v):
    return _parse_display_number(v)




def _unit_from_header(header_text):
    """Extract explicit length unit from an Excel header, e.g. 'Used (in)'."""
    text = str(header_text or "").lower()
    m = re.search(r"\(([^)]*)\)", text)
    if m:
        text = m.group(1).lower()
    if "mm" in text:
        return "mm"
    if re.search(r"\bcm\b", text):
        return "cm"
    if re.search(r"\bm\b", text) and "mm" not in text and "cm" not in text:
        return "m"
    if re.search(r"\bft\b|feet|foot", text):
        return "ft"
    if re.search(r"\bin\b|inch|inches", text):
        return "in"
    return None


def _normalize_length_unit(unit, base=None, default_context="piece"):
    """Normalize free-text unit labels to mm/cm/m/in/ft."""
    raw = str(unit or "").strip().lower()
    raw = raw.replace('"', 'in').replace("'", "ft")
    raw = raw.replace(" ", "_").replace("-", "_")

    if raw in ("mm", "millimeter", "millimeters", "milimetro", "milímetros", "milimetros"):
        return "mm"
    if raw in ("cm", "centimeter", "centimeters", "centimetro", "centímetros", "centimetros"):
        return "cm"
    if raw in ("m", "meter", "meters", "metro", "metros"):
        return "m"
    if raw in ("in", "inch", "inches", "decimal_inches", "fractional_inches"):
        return "in"
    if raw in ("ft", "feet", "foot", "decimal_feet", "feet_and_fractional_inches", "feet_fractional_inches", "ft_in"):
        return "ft"
    if raw in ("in/ft", "in_ft", "inch/foot"):
        return "in" if default_context == "piece" else "ft"

    if base and is_imperial_base(str(base).upper()):
        return "in" if default_context == "piece" else "ft"
    return "cm" if default_context == "piece" else "m"


def _input_piece_price_and_display_map(input_data):
    """
    Canonical piece-length map from input_001.json.

    This avoids the historic bug where COST_PIECES interpreted a numeric CUT_LIST
    piece length according to the selected visual output_unit. Piece cost must be
    based on the original piece input unit (in/cm etc.), then converted to the
    pricing unit ($/ft or $/m).
    """
    base = str(input_data.get("unit_system", "METRIC")).upper()
    result = {}

    for piece in input_data.get("pieces", []) or []:
        cg = str(piece.get("cut_group", ""))
        pid = str(piece.get("piece_id", ""))
        name = str(piece.get("piece_name", ""))
        length = piece.get("length", 0)
        unit = _normalize_length_unit(piece.get("unit"), base, "piece")

        if is_imperial_base(base):
            if unit == "ft":
                inches = parse_bar_length_imperial(length) if isinstance(length, str) else float(length or 0) * 12.0
                display_len = _parse_display_number(length)  # keep original INPUT numeric unit for COST_PIECES
            else:
                inches = parse_piece_length_imperial(length)
                display_len = _parse_display_number(length)  # keep original INPUT numeric unit (in)
            price_len = inches / 12.0     # pricing unit = ft
        else:
            numeric = _parse_display_number(length)
            if unit == "mm":
                meters = numeric / 1000.0
            elif unit == "m":
                meters = numeric
            else:
                meters = numeric / 100.0  # default input piece unit = cm
            price_len = meters            # pricing unit = m
            display_len = numeric         # keep original INPUT numeric unit for COST_PIECES

        keys = [
            (cg, pid, name),
            (cg, pid, ""),
        ]
        for key in keys:
            if key not in result:
                result[key] = (price_len, display_len)
    return result


def _format_inches_for_output(inches, input_data):
    """Format canonical inches using current output_unit without changing cost basis."""
    mode = _output_mode_key(input_data)
    if mode == "fractional_inches":
        return _in_to_fractional_inches_text(inches)
    if mode == "feet_and_fractional_inches":
        return _in_to_feet_fractional_inches_text(inches)
    if mode == "decimal_feet":
        return float(inches or 0) / 12.0
    return float(inches or 0)


def _format_meters_for_output(meters, input_data):
    """Format canonical metres using current output_unit without changing cost basis."""
    mode = _output_mode_key(input_data)
    if mode == "mm":
        return float(meters or 0) * 1000.0
    if mode == "cm":
        return float(meters or 0) * 100.0
    return float(meters or 0)


def _output_mode_key(input_data):
    raw = (
        input_data.get("output_unit", "")
        or input_data.get("project", {}).get("Unit_Output", "")
        or ""
    )

    text = str(raw).strip().lower()
    text = text.replace(" ", "_").replace("-", "_")

    aliases = {
        "decimal_inches": "decimal_inches",
        "in": "decimal_inches",
        "inch": "decimal_inches",
        "inches": "decimal_inches",
        "fractional_inches": "fractional_inches",
        "fractional": "fractional_inches",
        "feet_and_fractional_inches": "feet_and_fractional_inches",
        "feet_fractional_inches": "feet_and_fractional_inches",
        "ft_in": "feet_and_fractional_inches",
        "decimal_feet": "decimal_feet",
        "ft": "decimal_feet",
        "feet": "decimal_feet",
        "foot": "decimal_feet",
        "m": "m",
        "meter": "m",
        "meters": "m",
        "metro": "m",
        "cm": "cm",
        "centimeter": "cm",
        "centimeters": "cm",
        "centimetro": "cm",
        "centímetro": "cm",
        "mm": "mm",
        "millimeter": "mm",
        "millimeters": "mm",
        "milimetro": "mm",
        "milímetro": "mm",
    }

    base = str(input_data.get("unit_system", "METRIC")).upper()
    return aliases.get(text, "decimal_inches" if is_imperial_base(base) else "cm")


def _output_unit_key(input_data):
    mode = _output_mode_key(input_data)
    if mode in ("decimal_feet", "feet_and_fractional_inches"):
        return "ft"
    if mode in ("decimal_inches", "fractional_inches"):
        return "in"
    return mode


def _metric_length_to_cm(value, input_data, source_unit=None):
    """
    Convert metric value to cm.
    If source_unit is explicit, it overrides output_unit. This is critical because
    result.xlsx headers may state (in)/(cm)/(m) independently of the selected display mode.
    """
    if value in (None, "", "N/A", "-"):
        return 0.0

    text = str(value).strip().lower() if not isinstance(value, (int, float)) else ""
    numeric = _parse_display_number(value)
    unit = _normalize_length_unit(source_unit, "METRIC", "piece") if source_unit else None

    if unit == "mm" or "mm" in text:
        return numeric / 10.0
    if unit == "m" or (re.search(r"\bm\b", text) and "mm" not in text and "cm" not in text):
        return numeric * 100.0
    if unit == "cm" or "cm" in text:
        return numeric

    mode = _output_mode_key(input_data)
    if mode == "mm":
        return numeric / 10.0
    if mode == "m":
        return numeric * 100.0
    return numeric


def _metric_length_to_display(value, input_data, source_unit=None):
    cm = _metric_length_to_cm(value, input_data, source_unit)
    mode = _output_mode_key(input_data)
    if mode == "mm":
        return cm * 10.0
    if mode == "m":
        return cm / 100.0
    return cm


def _imperial_length_to_inches(value, input_data, source_unit=None):
    """
    Convert imperial value to inches.
    Explicit source_unit overrides current output_unit. Numeric CUT_LIST/REPORT
    values must not be blindly treated as feet just because output_unit is ft-in.
    """
    if value in (None, "", "N/A", "-"):
        return 0.0

    unit = _normalize_length_unit(source_unit, "IMPERIAL", "piece") if source_unit else None

    if isinstance(value, (int, float)):
        numeric = float(value)
        if unit == "ft":
            return numeric * 12.0
        if unit == "in":
            return numeric
        mode = _output_mode_key(input_data)
        if mode in ("decimal_feet", "feet_and_fractional_inches"):
            return numeric * 12.0
        return numeric

    text = str(value).strip().lower()

    if unit == "ft":
        return parse_bar_length_imperial(value)
    if unit == "in":
        return parse_piece_length_imperial(value)

    # Explicit feet or feet-inches notation.
    if "ft" in text or "'" in text:
        return parse_bar_length_imperial(value)

    # Explicit inches/fractions/plain text inches.
    return parse_piece_length_imperial(value)


def _imperial_length_to_display(value, input_data, source_unit=None):
    inches = _imperial_length_to_inches(value, input_data, source_unit)
    return _format_inches_for_output(inches, input_data)


def _display_length_to_price_unit(value, base, input_data, source_unit=None):
    """
    Convert source/display length to pricing unit.
    METRIC price unit = m. IMPERIAL price unit = ft.
    source_unit must be passed when the source column/header has an explicit unit.
    """
    if value in (None, "", "N/A", "-"):
        return 0.0

    if is_imperial_base(base):
        return _imperial_length_to_inches(value, input_data, source_unit) / 12.0

    return _metric_length_to_cm(value, input_data, source_unit) / 100.0


def _display_length_value(value, base, input_data, source_unit=None):
    """Return value in the active visual output unit for COST display."""
    if is_imperial_base(base):
        return _imperial_length_to_display(value, input_data, source_unit)
    return _metric_length_to_display(value, input_data, source_unit)


def _cost_display_cell_value(value, base, input_data, source_unit=None):
    """
    Return display-ready length for COST sheets.
    Decimal modes return numeric values.
    Fractional imperial modes return text.
    """
    if is_imperial_base(base):
        inches = _imperial_length_to_inches(value, input_data, source_unit)
        return _format_inches_for_output(inches, input_data)

    return _display_length_value(value, base, input_data, source_unit)


def _fraction(value, denominator=16):
    value = float(value or 0)
    numerator = round(value * denominator)
    return Fraction(numerator, denominator)


def _in_to_fractional_inches_text(value, denominator=16):
    value = float(value or 0)
    whole = int(value)
    fraction = value - whole
    frac = _fraction(fraction, denominator)

    if frac.numerator == frac.denominator:
        whole += 1
        frac = Fraction(0, 1)

    if frac.numerator == 0:
        return f'{whole}"'

    if whole == 0:
        return f'{frac.numerator}/{frac.denominator}"'

    return f'{whole} {frac.numerator}/{frac.denominator}"'


def _in_to_feet_fractional_inches_text(value, denominator=16):
    total_inches = float(value or 0)
    feet = int(total_inches // 12)
    inches = total_inches - (feet * 12)

    frac = _fraction(inches - int(inches), denominator)
    if int(inches) == 11 and frac.numerator == frac.denominator:
        feet += 1
        inches = 0

    return f"{feet}'- {_in_to_fractional_inches_text(inches, denominator)}"


def _cost_display_number_format(input_data):
    """Excel number format for COST length columns. None means text mode."""
    base = str(input_data.get("unit_system", "METRIC")).upper()
    mode = _output_mode_key(input_data)

    if is_imperial_base(base) and mode in ("fractional_inches", "feet_and_fractional_inches"):
        return None

    unit = _output_unit_key(input_data)
    return f'0.00 "{unit}"'


def _cost_piece_input_number_format(input_data):
    """
    COST_PIECES must show the original piece input unit, not the visual output unit.
    ENGCUT official units:
      METRIC   pieces = cm
      IMPERIAL pieces = in
    This keeps the displayed length consistent with the cost formula:
      metric:   Comp(cm) / 100 * $/m
      imperial: Comp(in) / 12  * $/ft
    """
    base = str(input_data.get("unit_system", "METRIC")).upper()
    return '0.00 "in"' if is_imperial_base(base) else '0.00 "cm"'


def _parse_bar_combination_to_price_unit(value, base, input_data=None):
    """
    Read Bar Combination text and return bar length in pricing unit:
    METRIC -> m
    IMPERIAL -> ft
    """
    if input_data is None:
        input_data = {"unit_system": base}

    if is_imperial_base(base):
        return _imperial_length_to_inches(value, input_data) / 12.0

    return _metric_length_to_cm(value, input_data) / 100.0

def _style_header(ws, row=1):
    for cell in ws[row]:
        if cell.value is not None:
            cell.fill = H_FILL
            cell.font = H_FONT
            cell.alignment = CENTER


def _autowidth(ws):
    for col in ws.columns:
        col_letter = get_column_letter(col[0].column)
        maxlen = max((len(str(c.value or "")) for c in col), default=8)
        ws.column_dimensions[col_letter].width = min(maxlen + 4, 45)


def _freeze(ws, cell="A2"):
    ws.freeze_panes = cell


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

def load_sources():
    wb_result = load_workbook(RESULT_PATH, read_only=True, data_only=True)
    with open(SUPPORT_PATH, "r", encoding="utf-8") as f:
        support = json.load(f)
    with open(INPUT_PATH, "r", encoding="utf-8") as f:
        input_data = json.load(f)
    return wb_result, support, input_data


# ── Price lookup ──────────────────────────────────────────────────────────────

def build_price_map(input_data):
    """Returns {cut_group: price_per_unit} from stock_rules."""
    base = str(input_data.get("unit_system", "METRIC")).upper()
    rules = input_data.get("stock_rules", {})
    prices = {}
    for cg, rule in rules.items():
        if is_imperial_base(base):
            p = (_sf(rule.get("price_bar_ft")) or
                 _sf(rule.get("price_ft")) or
                 _sf(rule.get("Preço Barra ($/ft)")) or
                 _sf(rule.get("price_per_ft")))
        else:
            p = (_sf(rule.get("price_bar_m")) or
                 _sf(rule.get("price_m")) or
                 _sf(rule.get("Preço Barra ($/m)")) or
                 _sf(rule.get("price_per_m")))
        prices[str(cg)] = p
    return prices


def build_junk_map(input_data):
    """
    Returns {cut_group: junk_price_per_weight_unit}.

    CORRECAO RISK-COST-001 (AUD-005):
    A versao anterior sempre retornava scrap_price_kg independente do unit_system.
    Para projetos IMPERIAL (peso em lb/ft), a formula slen_ft * weight_lb * junk_kg
    produzia $ x (lb/kg) — unidade errada, valor 2.2046x maior que o correto.

    Regra corrigida:
      METRIC   → $/kg  (combina com weight_kg_m de build_weight_map)
      IMPERIAL → $/lb  (combina com weight_lb_ft de build_weight_map)
    """
    base  = str(input_data.get("unit_system", "METRIC")).upper()
    rules = input_data.get("stock_rules", {})
    result = {}
    for cg, rule in rules.items():
        if is_imperial_base(base):
            # IMPERIAL: peso em lb/ft → preco de sucata deve ser $/lb
            j = (_sf(rule.get("scrap_price_lb")) or
                 _sf(rule.get("Preço Ferro Velho ($/lb)")) or
                 _sf(rule.get("junk_price_lb")) or 0)
        else:
            # METRIC: peso em kg/m → preco de sucata deve ser $/kg
            j = (_sf(rule.get("scrap_price_kg")) or
                 _sf(rule.get("Preço Ferro Velho ($/kg)")) or
                 _sf(rule.get("junk_price")) or 0)
        result[str(cg)] = j
    return result


def build_weight_map(input_data):
    """Returns {cut_group: weight_per_unit (lb/ft or kg/m)}."""
    base = str(input_data.get("unit_system", "METRIC")).upper()
    rules = input_data.get("stock_rules", {})
    weights = {}
    for cg, rule in rules.items():
        if is_imperial_base(base):
            w = (_sf(rule.get("weight_lb_ft")) or
                 _sf(rule.get("Peso (lb/ft)")) or 0)
        else:
            w = (_sf(rule.get("weight_kg_m")) or
                 _sf(rule.get("Peso (kg/m)")) or 0)
        weights[str(cg)] = w
    return weights


def build_profile_map(input_data):
    """Returns {cut_group: description}."""
    rules = input_data.get("stock_rules", {})
    return {str(cg): str(rule.get("description") or
                         rule.get("Perfil / Descrição") or cg)
            for cg, rule in rules.items()}


def build_assembly_map(input_data):
    """Returns {cut_group: {assembly_code, family_code}} from assembly section."""
    asm = input_data.get("assembly", {})
    result = {}
    for cg, info in asm.items():
        result[str(cg)] = {
            "assembly_code": str(info.get("assembly_code") or ""),
            "family_code":   str(info.get("family_code")   or ""),
        }
    return result


# ── COST_BARS ─────────────────────────────────────────────────────────────────

def build_cost_bars(wb_result, input_data):
    """Compute cost per bar from CUT_PLAN."""
    base    = str(input_data.get("unit_system", "METRIC")).upper()
    prices  = build_price_map(input_data)
    weights = build_weight_map(input_data)
    profiles = build_profile_map(input_data)
    project  = input_data.get("project", {}).get("Project_Name", "")

    ws_cp = wb_result["CUT_PLAN"]
    rows_cp = list(ws_cp.iter_rows(values_only=True))
    h = rows_cp[0]

    try:
        col_bid   = h.index("Bar ID")
        col_cg    = next(i for i, v in enumerate(h) if v and "Cut Group" in str(v))
        col_blen  = next(i for i, v in enumerate(h) if v and "Bar Combination" in str(v))
        col_pcount= next(i for i, v in enumerate(h) if v and "Piece Count" in str(v))
        col_used  = next(i for i, v in enumerate(h) if v and "Net Used" in str(v))
        col_waste = next(i for i, v in enumerate(h) if v and "Waste" in str(v))
        col_yield = next(i for i, v in enumerate(h) if v and "Yield" in str(v))
        col_src   = next(i for i, v in enumerate(h) if v and "Source Type" in str(v))
    except (ValueError, StopIteration):
        col_bid, col_cg, col_blen, col_pcount = 0, 1, 2, 3
        col_used, col_waste, col_yield, col_src = 6, 7, 8, 9

    used_source_unit = _unit_from_header(h[col_used]) if h and col_used < len(h) else None
    waste_source_unit = _unit_from_header(h[col_waste]) if h and col_waste < len(h) else None

    import re

    bars = []
    for r in rows_cp[1:]:
        if not r[col_bid] or r[col_bid] == "Bar ID":
            continue
        bid   = str(r[col_bid])
        cg    = str(r[col_cg]) if r[col_cg] else ""
        comb  = str(r[col_blen]) if r[col_blen] else ""
        src   = str(r[col_src]) if r[col_src] else "NEW BAR"

        n_pieces = int(r[col_pcount]) if r[col_pcount] else 0
        yld      = _sf(r[col_yield])

        raw_used  = r[col_used]
        raw_waste = r[col_waste]

        bar_len_price = _parse_bar_combination_to_price_unit(
            comb,
            base,
            input_data,
        )

        used_price = _display_length_to_price_unit(
            raw_used,
            base,
            input_data,
            source_unit=used_source_unit,
        )

        waste_price = _display_length_to_price_unit(
            raw_waste,
            base,
            input_data,
            source_unit=waste_source_unit,
        )

        used_disp = _cost_display_cell_value(
            raw_used,
            base,
            input_data,
            source_unit=used_source_unit,
        )

        waste_disp = _cost_display_cell_value(
            raw_waste,
            base,
            input_data,
            source_unit=waste_source_unit,
        )

        if not bar_len_price:
            bar_len_price = used_price + waste_price

        bar_len_display = _cost_display_cell_value(
            comb,
            base,
            input_data,
        )

        if not bar_len_display:
            bar_len_display = _cost_display_cell_value(
                bar_len_price,
                base,
                {"unit_system": base, "output_unit": "decimal_feet" if is_imperial_base(base) else "m"},
            )

        price_unit = prices.get(cg, 0.0)     # $/m or $/ft
        used_cost  = used_price  * price_unit
        waste_cost = waste_price * price_unit
        bar_cost   = bar_len_price * price_unit
        profile  = profiles.get(cg, cg)

        bars.append({
            "bar_id":     bid,
            "cut_group":  cg,
            "profile":    profile,
            "bar_len":    round(bar_len_display, 4) if isinstance(bar_len_display, (int, float)) else bar_len_display,
            "price":      price_unit,              # $/m or $/ft
            "bar_cost":   round(bar_cost,   2),
            "used":       round(used_disp, 4) if isinstance(used_disp, (int, float)) else used_disp,
            "waste":      round(waste_disp, 4) if isinstance(waste_disp, (int, float)) else waste_disp,
            "used_cost":  round(used_cost,  2),
            "waste_cost": round(waste_cost, 2),
            "yield":      round(yld, 6),
            "n_pieces":   n_pieces,
            "source":     src,
            "project":    project,
        })

    return bars


# ── COST_PIECES ───────────────────────────────────────────────────────────────

def build_cost_pieces(wb_result, support, input_data):
    """Compute cost per piece from CUT_LIST."""
    base    = str(input_data.get("unit_system", "METRIC")).upper()
    prices  = build_price_map(input_data)
    asm_map = build_assembly_map(input_data)
    project = input_data.get("project", {}).get("Project_Name", "")

    input_piece_map = _input_piece_price_and_display_map(input_data)

    ws_cl = wb_result["CUT_LIST"]
    rows_cl = list(ws_cl.iter_rows(values_only=True))
    h = rows_cl[0]

    try:
        col_bid   = h.index("Bar ID")
        col_pid   = next(i for i, v in enumerate(h) if v and "Piece ID" in str(v) and "Source" not in str(v))
        col_src   = next(i for i, v in enumerate(h) if v and "Source Piece ID" in str(v))
        col_cg    = next(i for i, v in enumerate(h) if v and "Cut Group" in str(v))
        col_name  = next(i for i, v in enumerate(h) if v and "Piece Name" in str(v))
        col_plen  = next(i for i, v in enumerate(h) if v and "Piece Length" in str(v))
        col_stype = next(i for i, v in enumerate(h) if v and "Source Type" in str(v))
    except (ValueError, StopIteration):
        col_bid, col_pid, col_src, col_cg = 0, 2, 3, 4
        col_name, col_plen, col_stype = 5, 6, 7

    # Build bar cost by bar_id
    ws_cp = wb_result["CUT_PLAN"]
    rows_cp = list(ws_cp.iter_rows(values_only=True))
    h_cp = rows_cp[0]

    try:
        col_bcg   = next(i for i, v in enumerate(h_cp) if v and "Cut Group" in str(v))
        col_bused = next(i for i, v in enumerate(h_cp) if v and "Net Used" in str(v))
        col_bwst  = next(i for i, v in enumerate(h_cp) if v and "Waste" in str(v))
        col_blen  = next(i for i, v in enumerate(h_cp) if v and "Bar Combination" in str(v))
    except (ValueError, StopIteration):
        col_bcg, col_bused, col_bwst, col_blen = 1, 6, 7, 2

    import re
    bar_costs = {}
    for r in rows_cp[1:]:
        if not r[0] or r[0] == "Bar ID":
            continue
        bid  = str(r[0])
        cg   = str(r[col_bcg]) if r[col_bcg] else ""
        comb = str(r[col_blen]) if r[col_blen] else ""
        bar_len = _parse_bar_combination_to_price_unit(comb, base, input_data)
        price_unit = prices.get(cg, 0.0)
        bar_costs[(cg, bid)] = {
            "cost": round(bar_len * price_unit, 2),
            "cg": cg,
        }

    pieces = []
    for r in rows_cl[1:]:
        if not r[col_bid] or r[col_bid] == "Bar ID":
            continue
        bid   = str(r[col_bid])
        pid   = str(r[col_pid]) if r[col_pid] else ""
        src   = str(r[col_src]) if r[col_src] else ""
        cg    = str(r[col_cg]) if r[col_cg] else ""
        name  = str(r[col_name]) if r[col_name] else ""
        stype = str(r[col_stype]) if r[col_stype] else "NEW BAR"
        asm_info = asm_map.get(cg, {"assembly_code": "", "family_code": ""})

        # Canonical piece length from INPUT has priority.
        # CUT_LIST can be formatted in the active output unit, but piece cost must
        # always follow the original input unit converted to $/ft or $/m.
        raw_len = r[col_plen]
        canonical = (
            input_piece_map.get((cg, src, name))
            or input_piece_map.get((cg, src, ""))
        )

        if canonical:
            plen_price, plen_disp = canonical
        else:
            # Fallback: interpret CUT_LIST cell according to explicit text/unit.
            # If numeric and ambiguous, current output_unit is the last resort.
            piece_source_unit = _unit_from_header(h[col_plen]) if h and col_plen < len(h) else None
            plen_price = _display_length_to_price_unit(
                raw_len,
                base,
                input_data,
                source_unit=piece_source_unit,
            )

            plen_disp = _cost_display_cell_value(
                raw_len,
                base,
                input_data,
                source_unit=piece_source_unit,
            )

        price_unit = prices.get(cg, 0.0)      # $/m or $/ft
        piece_cost = round(plen_price * price_unit, 2)

        bar_info = bar_costs.get(
            (cg, bid),
            {"cost": 0.0, "cg": cg},
        )
        bar_cost   = bar_info["cost"]
        pct_bar    = round(piece_cost / bar_cost, 6) if bar_cost > 0 else 0.0

        pieces.append({
            "piece_id":      pid,
            "bar_id":        bid,
            "cut_group":     cg,
            "assembly_code": asm_info["assembly_code"],
            "family_code":   asm_info["family_code"],
            "piece_name":    name,
            "source_pid":    src,
            "comp":          round(plen_disp, 4) if isinstance(plen_disp, (int, float)) else plen_disp,
            "price":         price_unit,
            "piece_cost":    piece_cost,
            "pct_bar":       pct_bar,
            "source_type":   stype,
            "project":       project,
        })

    return pieces


# ── COST_PROJECT ──────────────────────────────────────────────────────────────

def build_cost_project(bars, pieces, support, input_data):
    """Build project-level financial summary."""
    base      = str(input_data.get("unit_system", "METRIC")).upper()
    prices    = build_price_map(input_data)
    weights   = build_weight_map(input_data)
    junk      = build_junk_map(input_data)
    project   = input_data.get("project", {}).get("Project_Name", "")

    total_bar_cost  = sum(b["bar_cost"]  for b in bars)
    total_piece_cost = sum(p["piece_cost"] for p in pieces)

    # Stock reuse cost
    stock_used = support.get("stock_used", [])
    stock_reuse_cost = 0.0
    for s in stock_used:
        cg    = s.get("cut_group", "")
        slen  = _sf(s.get("used_length", 0))
        slen_ft = slen / 12.0 if is_imperial_base(base) else slen / 1000.0
        stock_reuse_cost += slen_ft * prices.get(cg, 0.0)

    # Scrap revenue
    gen_scrap = support.get("generated_scrap", [])
    scrap_revenue = 0.0
    for s in gen_scrap:
        cg   = s.get("cut_group", "")
        slen = _sf(s.get("length_internal", 0))
        slen_ft = slen / 12.0 if is_imperial_base(base) else slen / 1000.0
        w    = weights.get(cg, 0.0)
        j    = junk.get(cg, 0.0)
        scrap_revenue += slen_ft * w * j

    # Stock retained value
    gen_stock = support.get("generated_stock", [])
    stock_retained = 0.0
    for s in gen_stock:
        cg   = s.get("cut_group", "")
        slen = _sf(s.get("length_internal", 0))
        slen_ft = slen / 12.0 if is_imperial_base(base) else slen / 1000.0
        stock_retained += slen_ft * prices.get(cg, 0.0)

    total_geral = total_bar_cost + stock_reuse_cost - scrap_revenue - stock_retained

    return {
        "project":           project,
        "material_purchased":round(total_bar_cost,   2),
        "stock_reuse_cost":  round(stock_reuse_cost, 2),
        "piece_total":       round(total_piece_cost, 2),
        "scrap_revenue":     round(scrap_revenue,    2),
        "stock_retained":    round(stock_retained,   2),
        "total_geral":       round(total_geral,      2),
        "n_bars":            len(bars),
        "n_pieces":          len(pieces),
        "n_stock_used":      len(stock_used),
        "n_stock_generated": len(gen_stock),
        "n_scrap_generated": len(gen_scrap),
    }


# ── Write cost_result.xlsx ────────────────────────────────────────────────────

def _load_project_history():
    """Load accumulated project history for BALANÇO PROJETOS."""
    if not HISTORY_PATH.exists():
        return []
    try:
        with open(HISTORY_PATH, "r", encoding="utf-8") as f:
            return json.load(f)
    except Exception:
        return []


def _save_project_history(history):
    """Save updated project history."""
    HISTORY_PATH.parent.mkdir(parents=True, exist_ok=True)
    with open(HISTORY_PATH, "w", encoding="utf-8") as f:
        json.dump(history, f, ensure_ascii=False, indent=2)


def write_cost_xlsx(bars, pieces, proj_summary, input_data, support):
    """Generate cost_result.xlsx reproducing the original template exactly."""
    from datetime import datetime
    import re

    base       = str(input_data.get("unit_system", "METRIC")).upper()
    imperial   = is_imperial_base(base)
    unit_label = "ft" if imperial else "m"          # pricing unit
    unit_label_comp = _output_unit_key(input_data)   # visual output unit
    fmt_len    = _cost_display_number_format(input_data)
    fmt_comp   = _cost_display_number_format(input_data)
    fmt_piece_comp = _cost_piece_input_number_format(input_data)
    fmt_cur    = "$ #,##0.00"
    fmt_pct    = "0.00%"

    project    = input_data.get("project", {})
    proj_name  = project.get("Project_Name", "")
    proj_id    = project.get("Numero_do Projeto", project.get("Numero_do_Projeto", ""))
    proj_addr  = project.get("Endereço_do_Projeto", "")
    proj_resp  = project.get("Responsavel_Tecnico", "")
    proj_date  = datetime.now().strftime("%d/%m/%Y %H:%M")

    # ── Style constants (matching original exactly) ─────────────────────────
    FILL_DARK   = PatternFill("solid", fgColor="002060")   # row 1 title
    FILL_TEAL   = PatternFill("solid", fgColor="00705A")   # row 2 subtitle
    FILL_HEADER = PatternFill("solid", fgColor="1F4E78")   # column headers
    FILL_META_V = PatternFill("solid", fgColor="EBF1F8")   # meta value cells
    FILL_SUM_H  = PatternFill("solid", fgColor="C6E0B4")   # summary header
    FILL_BAL_H  = PatternFill("solid", fgColor="FCE4D6")   # balanço header

    F_TITLE  = Font(name="Calibri", size=14, bold=True, color="FFFFFF")
    F_SUB    = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
    F_HEADER = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
    F_META_K = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
    F_META_V = Font(name="Calibri", size=11, bold=False)
    F_BOLD   = Font(name="Calibri", size=11, bold=True)
    F_BOLD14 = Font(name="Calibri", size=14, bold=True)
    F_BOLD12 = Font(name="Calibri", size=12, bold=True)
    F_REG    = Font(name="Calibri", size=11, bold=False)
    F_SUM_H  = Font(name="Calibri", size=11, bold=True)
    F_BAL_H  = Font(name="Calibri", size=11, bold=True)

    C = Alignment(horizontal="center", vertical="center")
    L = Alignment(horizontal="left",   vertical="center")
    R = Alignment(horizontal="right",  vertical="center")

    def sc(ws, row, col, value, fill=None, font=None, fmt=None, align=C):
        c = ws.cell(row=row, column=col, value=value)
        if fill: c.fill = fill
        if font: c.font = font
        if fmt:  c.number_format = fmt
        if align: c.alignment = align
        return c

    wb = Workbook()

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

    # Template aprovado:
    # BarID | Cut Group | Bar Length | Pieces | Used Length | Waste Length
    # Yield % | $/m or $/ft | Bar Cost | Used Value | Waste Value | Source | Project
    hdr_bars = [
        "BarID",
        "Cut Group",
        "Bar Length",
        "Pieces",
        "Used Length",
        "Waste Length",
        "Yield %",
        f"$/ {unit_label}",
        "Bar Cost",
        "Used Value",
        "Waste Value",
        "Source",
        "Project",
    ]

    for i, h in enumerate(hdr_bars, 1):
        sc(ws, 1, i, h, fill=FILL_HEADER, font=F_HEADER, align=C)

    for b in bars:
        r = ws.max_row + 1

        sc(ws, r,  1, b["bar_id"],    align=C)
        sc(ws, r,  2, b["cut_group"], align=C)
        sc(ws, r,  3, b["bar_len"],   fmt=fmt_len,  align=C)
        sc(ws, r,  4, b["n_pieces"],  align=C)
        sc(ws, r,  5, b["used"],      fmt=fmt_comp, align=C)
        sc(ws, r,  6, b["waste"],     fmt=fmt_comp, align=C)
        sc(ws, r,  7, b["yield"],     fmt=fmt_pct,  align=C)
        sc(ws, r,  8, b["price"],     fmt=fmt_cur,  align=C)
        sc(ws, r,  9, b["bar_cost"],  fmt=fmt_cur,  align=C)
        sc(ws, r, 10, b["used_cost"], fmt=fmt_cur,  align=C)
        sc(ws, r, 11, b["waste_cost"],fmt=fmt_cur,  align=C)
        sc(ws, r, 12, b["source"],    align=C)
        sc(ws, r, 13, b["project"],   align=L)

    ws.freeze_panes = "A2"

    col_widths_bars = {
        "A": 12,
        "B": 14,
        "C": 14,
        "D": 10,
        "E": 16,
        "F": 16,
        "G": 12,
        "H": 10,
        "I": 14,
        "J": 14,
        "K": 14,
        "L": 14,
        "M": 40,
    }

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

    # Alinhamento aprovado:
    # C, E, F, H, I, J, K à direita.
    # M à esquerda.
    for row in range(1, ws.max_row + 1):
        for col in (3, 5, 6, 8, 9, 10, 11):
            ws.cell(row, col).alignment = RIGHT

        ws.cell(row, 13).alignment = LEFT

    # ════════════════════════════ COST_PIECES ════════════════════════════
    ws2 = wb.create_sheet("COST_PIECES")

    sc(ws2, 1, 1, "CUSTO POR PEÇA CORTADA", fill=FILL_DARK, font=F_TITLE, align=L)
    ws2.merge_cells("A1:L1")
    sc(ws2, 2, 1,
       "PieceID rastreável via QR | AssemblyCode BIM-Ready | Integração com CUT_LIST",
       fill=FILL_DARK, font=F_SUB, align=L)
    ws2.merge_cells("A2:L2")

    hdr_pcs = [
        "PieceID", "BarID", "Cut Group", "Assembly Code", "Family Code",
        "Descrição Peça", "Comp Cortado", f"$/ {unit_label}",
        "Custo Peça ($)", "% Custo Barra", "Source Type", "Projeto"
    ]
    for i, h in enumerate(hdr_pcs, 1):
        sc(ws2, 4, i, h, fill=FILL_HEADER, font=F_HEADER)

    for p in pieces:
        r = ws2.max_row + 1
        sc(ws2, r,  1, p["piece_id"],      align=C)
        sc(ws2, r,  2, p["bar_id"],        align=C)
        sc(ws2, r,  3, p["cut_group"],     align=C)
        sc(ws2, r,  4, p["assembly_code"], align=C)
        sc(ws2, r,  5, p["family_code"],   align=C)
        sc(ws2, r,  6, p["piece_name"],    align=C)
        sc(ws2, r,  7, p["comp"],          fmt=fmt_piece_comp, align=C)
        sc(ws2, r,  8, p["price"],         fmt=fmt_cur,  align=C)
        sc(ws2, r,  9, p["piece_cost"],    fmt=fmt_cur,  align=C)
        sc(ws2, r, 10, p["pct_bar"],       fmt=fmt_pct,  align=C)
        sc(ws2, r, 11, p["source_type"],   align=C)
        sc(ws2, r, 12, p["project"],       align=C)

    ws2.freeze_panes = "A5"
    col_widths_pcs = {"A":15,"B":12,"C":12,"D":15,"E":13,"F":16,
                      "G":14,"H":12,"I":16,"J":21,"K":13,"L":40}
    for col, w in col_widths_pcs.items():
        ws2.column_dimensions[col].width = w

    # ════════════════════════════ COST_PROJECT ════════════════════════════
    ws3 = wb.create_sheet("COST_PROJECT")
    p = proj_summary

    # Row 1: Title merged A1:L1
    sc(ws3, 1, 1, "RESUMO FINANCEIRO DO PROJETO", fill=FILL_DARK, font=F_TITLE, align=L)
    ws3.merge_cells("A1:L1")

    # Row 2: Subtitle merged A2:L2
    sc(ws3, 2, 1,
       "Consolidação automática + lógica equivalente ao EngCut_Custo_v1",
       fill=FILL_TEAL, font=F_SUB, align=L)
    ws3.merge_cells("A2:L2")

    # Row 3: blank

    # Rows 4-8: Metadata
    meta = [
        ("Projeto",     proj_name),
        ("ID Projeto",  proj_id),
        ("Endereço",    proj_addr),
        ("Responsavel", proj_resp),
        ("Data",        proj_date),
    ]
    for i, (k, v) in enumerate(meta, start=4):
        sc(ws3, i, 1, k, fill=FILL_DARK, font=F_META_K, align=L)
        sc(ws3, i, 2, v, fill=FILL_META_V, font=F_META_V, align=L)

    # Row 9: blank

    # Row 10: DEMONSTRATIVO header merged A10:B10
    sc(ws3, 10, 1, "DEMONSTRATIVO DE CUSTO", fill=FILL_DARK, font=F_TITLE, align=L)
    ws3.merge_cells("A10:B10")

    # Rows 11-16: Financial items
    fin = [
        ("(+) Material comprado - barras novas",    p["material_purchased"]),
        ("Preço Total Peças Fabricadas",             p["piece_total"]),
        ("(+) Retalho consumido do estoque",         p["stock_reuse_cost"]),
        ("(-) Receita venda ferro velho (junkyard)", p["scrap_revenue"]),
        ("(+) Custo transporte / descarte",          0),
        ("(-) Retalhos gerados para estoque",        p["stock_retained"]),
    ]
    for i, (label, value) in enumerate(fin, start=11):
        sc(ws3, i, 1, label, font=F_REG, align=L)
        sc(ws3, i, 2, value, font=F_REG, fmt=fmt_cur, align=R)

    # Row 17: blank

    # Row 18: TOTAL GERAL (formula)
    sc(ws3, 18, 1, "TOTAL GERAL", font=F_BOLD12, align=L)
    sc(ws3, 18, 2, "=B11+B13+B15-B14-B16", font=F_BOLD12, fmt=fmt_cur, align=R)

    # Row 19: Multiplicador Patrimonial
    sc(ws3, 19, 1, "Multiplicador Patrimonial", font=F_BOLD, align=L)
    sc(ws3, 19, 2, "=IF(B11>0,B18/B11,0)", font=F_BOLD, fmt=FMT_MULT, align=R)

    # Row 20: Interpretação automática
    sc(ws3, 20, 1, "Interpretação", font=F_BOLD, align=L)
    sc(ws3, 20, 2,
       '=IF(B19<1.5,"Predominância de material novo",IF(B19<3,"Reaproveitamento moderado de estoque",IF(B19<10,"Alto reaproveitamento patrimonial","Produção fortemente alavancada por estoque existente")))',
       font=F_REG, align=L)

    # Row 21: Total estoque
    sc(ws3, 21, 1, "TOTAL GERAL DO ESTOQUE (ativo empresa)", font=F_BOLD, align=L)
    sc(ws3, 21, 2, p["stock_retained"], font=F_REG, fmt=fmt_cur, align=R)

    # Row 22: blank

    # Row 23: SUMMARY header merged A23:B23
    sc(ws3, 23, 1, "SUMMARY OF CURRENT PROJECT COSTS", fill=FILL_DARK, font=F_SUB, align=L)
    ws3.merge_cells("A23:B23")

    # Row 24: Summary column headers
    sum_hdr = ["Projeto", "Cut Group", f"Tot. Material",
               f"$/ {unit_label}", "Custo Mat ($)",
               "Waste (und)", "Custo Waste ($)", "Yield %"]
    for i, h in enumerate(sum_hdr, 1):
        sc(ws3, 24, i, h, fill=FILL_SUM_H, font=F_SUM_H)

    # Summary data rows (one per cut group from REPORT)
    wb_result_r = load_workbook(RESULT_PATH, read_only=True, data_only=True)
    ws_rep = wb_result_r["REPORT"]
    rep_header = next(ws_rep.iter_rows(values_only=True))
    rep_rows = [r for r in ws_rep.iter_rows(min_row=2, values_only=True)
                if r[0] and r[0] != "Cut Group" and isinstance(r[3], int)]
    rep_mat_unit = _unit_from_header(rep_header[5]) if len(rep_header) > 5 else None
    rep_waste_unit = _unit_from_header(rep_header[9]) if len(rep_header) > 9 else None
    wb_result_r.close()

    prices_map  = build_price_map(input_data)
    weights_map = build_weight_map(input_data)

    summary_row_idx = 25
    for r in rep_rows:
        cg = r[0]
        # Material/Waste from REPORT are visual/output values.
        # Convert to pricing unit before multiplying:
        # METRIC -> m | IMPERIAL -> ft
        mat_raw = r[5]
        waste_raw = r[9]

        mat_price = _display_length_to_price_unit(
            mat_raw,
            base,
            input_data,
            source_unit=rep_mat_unit,
        )

        waste_price = _display_length_to_price_unit(
            waste_raw,
            base,
            input_data,
            source_unit=rep_waste_unit,
        )

        mat_display = _cost_display_cell_value(
            mat_raw,
            base,
            input_data,
            source_unit=rep_mat_unit,
        )

        waste_display = _cost_display_cell_value(
            waste_raw,
            base,
            input_data,
            source_unit=rep_waste_unit,
        )

        price_u   = prices_map.get(cg, 0.0)
        mat_cost  = round(mat_price  * price_u, 2)
        wst_cost  = round(waste_price * price_u, 2)
        yld       = float(r[10]) if r[10] else 0.0

        sc(ws3, summary_row_idx, 1, proj_name, align=L)
        sc(ws3, summary_row_idx, 2, cg, align=C)
        sc(ws3, summary_row_idx, 3, mat_display,  fmt=fmt_len, align=R)
        sc(ws3, summary_row_idx, 4, price_u,  fmt=fmt_cur, align=R)
        sc(ws3, summary_row_idx, 5, mat_cost, fmt=fmt_cur, align=R)
        sc(ws3, summary_row_idx, 6, waste_display, fmt=fmt_comp, align=R)
        sc(ws3, summary_row_idx, 7, wst_cost,  fmt=fmt_cur, align=R)
        sc(ws3, summary_row_idx, 8, yld,       fmt=fmt_pct, align=R)
        summary_row_idx += 1

    # blank row, then BALANÇO
    bal_start = summary_row_idx + 2

    # BALANÇO PROJETOS header merged
    sc(ws3, bal_start, 1, "BALANÇO PROJETOS", fill=FILL_DARK, font=F_SUB, align=L)
    ws3.merge_cells(f"A{bal_start}:B{bal_start}")

    bal_hdr = ["Projeto", "Data", "Custo Material", "Preço Total Peças",
               "Retalhos Estoque", "Receita Descartes", "Transporte",
               "Custo Líquido", "Índice de Custo Líquido %",
               "Yield Patrimonial %", "Status"]
    for i, h in enumerate(bal_hdr, 1):
        sc(ws3, bal_start + 1, i, h, fill=FILL_BAL_H, font=F_BAL_H)

    # Load history + append/update current project
    history = _load_project_history()
    existing = {e["Projeto"]: i for i, e in enumerate(history)}

    current_entry = {
        "Projeto":            proj_name,
        "Data":               proj_date,
        "Custo Material":     round(p["material_purchased"] + p["stock_reuse_cost"], 2),
        "Preço Total Peças":  p["piece_total"],
        "Retalhos Estoque":   p["stock_retained"],
        "Receita Descartes":  p["scrap_revenue"],
        "Transporte":         0.0,
        "Status":             "OK",
    }

    if proj_name in existing:
        history[existing[proj_name]] = current_entry
    else:
        history.append(current_entry)

    _save_project_history(history)

    for i, entry in enumerate(history):
        br = bal_start + 2 + i
        sc(ws3, br, 1, entry["Projeto"], align=L)
        sc(ws3, br, 2, entry["Data"], align=C)
        sc(ws3, br, 3, entry["Custo Material"],    fmt=fmt_cur, align=R)
        sc(ws3, br, 4, entry["Preço Total Peças"], fmt=fmt_cur, align=R)
        sc(ws3, br, 5, entry["Retalhos Estoque"],  fmt=fmt_cur, align=R)
        sc(ws3, br, 6, entry["Receita Descartes"], fmt=fmt_cur, align=R)
        sc(ws3, br, 7, entry["Transporte"],        fmt=fmt_cur, align=R)
        sc(ws3, br, 8, f"=C{br}+G{br}-E{br}-F{br}", fmt=fmt_cur, align=R)
        sc(ws3, br, 9, f"=IF(C{br}>0,H{br}/C{br},0)", fmt=fmt_pct, align=R)
        sc(ws3, br,10, f"=IF((C{br}-E{br})>0,H{br}/(C{br}-E{br}),0)", fmt=fmt_pct, align=R)
        sc(ws3, br,11, entry["Status"], align=C)

    # Column widths
    col_widths_proj = {"A":40,"B":32,"C":16,"D":16,"E":18,
                       "F":16,"G":15,"H":17,"I":18,"J":18,"K":12}
    for col, w in col_widths_proj.items():
        ws3.column_dimensions[col].width = w

    OUTPUT_PATH.parent.mkdir(parents=True, exist_ok=True)
    wb.save(OUTPUT_PATH)

    print(f"cost_result.xlsx salvo em {OUTPUT_PATH}")
    print(f"  Barras: {p['n_bars']} | Peças: {p['n_pieces']}")
    print(f"  Material: ${p['material_purchased']:,.2f} | Peças: ${p['piece_total']:,.2f}")
    print(f"  Total Geral: ${p['material_purchased'] + p['stock_reuse_cost'] - p['scrap_revenue'] - p['stock_retained']:,.2f}")


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

def run_cost():
    wb_result, support, input_data = load_sources()

    bars   = build_cost_bars(wb_result, input_data)
    pieces = build_cost_pieces(wb_result, support, input_data)
    proj   = build_cost_project(bars, pieces, support, input_data)

    wb_result.close()

    write_cost_xlsx(bars, pieces, proj, input_data, support)


def main():
    run_cost()


if __name__ == "__main__":
    run_cost()
