from pathlib import Path
from datetime import datetime
import contextlib
import json
import os
import time as _time

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment

from display_rules import apply_global_sheet_format, _ws_dimensions
from unit_display import internal_to_fixed_ft, length_with_unit_to_ft, operational_unit_label
from excel_numeric_display import make_workbook_text_numbers_calculable


def _get_engine_root():
    """Resolve o diretório engine/ corretamente em dev e no .exe frozen."""
    import sys as _sys
    if getattr(_sys, "frozen", False):
        # No .exe: usar o diretório do executável (engine/)
        return Path(_sys.executable).resolve().parent
    # Em dev: stock_repository.py está em engine/
    return Path(__file__).resolve().parent

DATABASE_PATH = _get_engine_root() / "database" / "stock_database.xlsx"

STOCK_COST_RULES = {}
STOCK_UNIT_SYSTEM = "METRIC"
_UNIT_SYSTEM_EXPLICITLY_SET = False  # True after configure_stock_cost_rules() is called


@contextlib.contextmanager
def _acquire_write_lock(timeout: float = 60.0):
    """
    CORRECAO CROSS-011 (AUD-007):
    Lock de escrita inter-processo via arquivo .lock atomico.
    Previne corrupcao por acessos concorrentes de multiplos processos Python.

    Usa os.O_CREAT | os.O_EXCL que e atomico tanto em POSIX quanto Win32.
    Lock stale (processo que travou sem liberar) e detectado por mtime > 5min.
    Timeout padrao: 60s — suficiente para a operacao mais longa do pipeline.
    """
    lock_file = Path(DATABASE_PATH).with_suffix(".lock")
    deadline = _time.monotonic() + timeout

    while True:
        try:
            fd = os.open(str(lock_file), os.O_CREAT | os.O_EXCL | os.O_WRONLY)
            os.close(fd)
            break
        except (FileExistsError, PermissionError):
            # Verifica se o lock e stale (processo que o criou crashou)
            try:
                age_s = _time.time() - lock_file.stat().st_mtime
                if age_s > 300:   # 5 minutos sem liberacao = stale
                    lock_file.unlink(missing_ok=True)
                    continue
            except Exception:
                pass
            if _time.monotonic() >= deadline:
                raise RuntimeError(
                    f"\n\n  BANCO DE DADOS BLOQUEADO\n"
                    f"  Timeout de {timeout:.0f}s aguardando acesso exclusivo.\n"
                    f"  Verifique se outro processo ENGCUT esta rodando.\n"
                )
            _time.sleep(0.1)

    try:
        yield
    finally:
        try:
            lock_file.unlink(missing_ok=True)
        except Exception:
            pass


def stock_length_to_internal(length, unit):

    unit = str(unit or "").lower().strip()

    if length in (None, ""):
        return 0

    length = float(length)

    if unit == "m":
        return length * 1000

    if unit == "mm":
        return length

    if unit == "cm":
        return length * 10

    if unit == "ft":
        return length * 12

    if unit == "in":
        return length

    return length


def safe_float(value):

    if value in (None, ""):
        return 0.0

    try:
        return float(value)
    except Exception:
        text = str(value).strip()
        text = text.replace("$", "")
        text = text.replace(",", ".")
        try:
            return float(text)
        except Exception:
            return 0.0


def normalize_cut_group(value):

    return str(value or "").strip().upper()


def configure_stock_cost_rules(stock_rules, unit_system):

    """
    Recebe os critérios oficiais do INPUT/STOCK_RULES.

    Esses critérios passam a alimentar automaticamente:
    - peso do STOCK
    - valor residual do STOCK
    - peso do SCRAP
    - receita do SCRAP
    - peso registrado no HISTORY
    """

    global STOCK_COST_RULES
    global STOCK_UNIT_SYSTEM
    global _UNIT_SYSTEM_EXPLICITLY_SET

    STOCK_UNIT_SYSTEM = str(unit_system or "METRIC").upper().strip()
    _UNIT_SYSTEM_EXPLICITLY_SET = True
    STOCK_COST_RULES = {}

    for cut_group, rule in (stock_rules or {}).items():

        if not isinstance(rule, dict):
            continue

        key = normalize_cut_group(cut_group)

        if not key:
            continue

        STOCK_COST_RULES[key] = rule


def get_cost_rule(cut_group):

    return STOCK_COST_RULES.get(
        normalize_cut_group(cut_group),
        {}
    )


def get_profile_description(cut_group, fallback=""):

    rule = get_cost_rule(cut_group)

    value = (
        rule.get("description")
        or rule.get("Perfil / Descrição")
        or rule.get("profile")
        or rule.get("perfil")
        or rule.get("tc_profile")
        or fallback
    )

    return str(value or "").strip()


def get_linear_weight(cut_group):

    rule = get_cost_rule(cut_group)

    if is_metric_system():
        return safe_float(
            rule.get("weight_kg_m")
            or rule.get("Peso (kg/m)")
            or rule.get("Peso kg/m")
            or rule.get("kg/m")
        )

    return safe_float(
        rule.get("weight_lb_ft")
        or rule.get("Peso (lb/ft)")
        or rule.get("Peso lb/ft")
        or rule.get("lb/ft")
    )




def get_length_unit_label():

    # REGRA VISUAL GLOBAL POR BASE: METRIC=m | IMPERIAL=ft.
    return database_display_unit()


def stock_length_column_header(label="Comprimento"):

    return f"{label} ({get_length_unit_label()})"


def get_stock_unit_label():

    return "kg/m" if is_metric_system() else "lb/ft"


def ensure_stock_cost_rules_loaded_from_input_json():

    """
    Segurança operacional:
    garante que STOCK/SCRAP/HISTORY consigam recalcular perfil, peso e valor
    mesmo quando save_database() for chamado depois de abrir um banco antigo.

    Fonte oficial:
    engine/inputs/input_001.json
    """

    global STOCK_COST_RULES
    global STOCK_UNIT_SYSTEM

    if STOCK_COST_RULES:
        return

    # Do not override a unit_system that was set explicitly by the caller via
    # configure_stock_cost_rules().  Only load from JSON when the caller never
    # set the unit system (e.g. save_database() called after creating a fresh DB).
    if _UNIT_SYSTEM_EXPLICITLY_SET:
        return

    input_json_path = Path("inputs") / "input_001.json"

    if not input_json_path.exists():
        return

    try:
        with open(input_json_path, "r", encoding="utf-8") as file:
            input_data = json.load(file)
    except Exception:
        return

    unit_system = input_data.get("unit_system", "METRIC")
    stock_rules = input_data.get("stock_rules", {})

    configure_stock_cost_rules(
        stock_rules=stock_rules,
        unit_system=unit_system,
    )


def get_scrap_price_value(cut_group):

    rule = get_cost_rule(cut_group)

    if is_metric_system():
        return safe_float(
            rule.get("scrap_price_kg")
            or rule.get("Preço Ferro Velho ($/kg)")
            or rule.get("price_scrap_kg")
            or rule.get("scrap_kg")
            or rule.get("$/kg")
        )

    scrap_price_lb = safe_float(
        rule.get("scrap_price_lb")
        or rule.get("Preço Ferro Velho ($/lb)")
        or rule.get("price_scrap_lb")
        or rule.get("scrap_lb")
        or rule.get("$/lb")
    )

    if scrap_price_lb > 0:
        return scrap_price_lb

    scrap_price_kg = safe_float(
        rule.get("scrap_price_kg")
        or rule.get("Preço Ferro Velho ($/kg)")
        or rule.get("price_scrap_kg")
        or rule.get("scrap_kg")
        or rule.get("$/kg")
    )

    if scrap_price_kg > 0:
        return scrap_price_kg

    return 0.0



def is_metric_system():

    return STOCK_UNIT_SYSTEM in (
        "METRIC",
        "METRO",
        "M",
        "CM",
        "MM",
    )


def _parse_stored_unit(ws, col=5):
    """Extract stored length unit from column header at row 4.

    E.g. 'Comprimento (ft)' → 'ft'. Returns None if header cannot be parsed.
    Used to detect what unit was in use when the database was last saved, so
    that cross-unit-system reads compute correct internal lengths.
    """
    import re as _re_su
    hdr = str(ws.cell(4, col).value or "")
    m = _re_su.search(r'\(([a-zA-Z]+)\)', hdr)
    return m.group(1).lower() if m else None


def display_length_to_metric_m(length, unit):

    length = safe_float(length)
    unit = str(unit or "").lower().strip()

    if unit == "m":
        return length

    if unit == "cm":
        return length / 100.0

    if unit == "mm":
        return length / 1000.0

    if unit == "ft":
        return length * 0.3048

    if unit == "in":
        return length * 0.0254

    # fallback métrico: display operacional atual usa m
    return length


def display_length_to_imperial_ft(length, unit):

    length = safe_float(length)
    unit = str(unit or "").lower().strip()

    if unit == "ft":
        return length

    if unit == "in":
        return length / 12.0

    if unit == "m":
        return length / 0.3048

    if unit == "cm":
        return (length / 100.0) / 0.3048

    if unit == "mm":
        return (length / 1000.0) / 0.3048

    # fallback imperial: display operacional atual usa ft
    return length


def get_metric_price_m(rule):

    price_m = safe_float(
        rule.get("price_bar_m")
        or rule.get("Preço Barra ($/m)")
        or rule.get("price_m")
        or rule.get("$/m")
    )

    if price_m > 0:
        return price_m

    price_ft = safe_float(
        rule.get("price_bar_ft")
        or rule.get("Preço Barra ($/ft)")
        or rule.get("price_ft")
        or rule.get("$/ft")
    )

    if price_ft > 0:
        return price_ft / 0.3048

    price_in = safe_float(
        rule.get("price_bar_in")
        or rule.get("Preço Barra ($/in)")
        or rule.get("price_in")
        or rule.get("$/in")
    )

    if price_in > 0:
        return price_in / 0.0254

    return 0.0


def get_imperial_price_ft(rule):

    price_ft = safe_float(
        rule.get("price_bar_ft")
        or rule.get("Preço Barra ($/ft)")
        or rule.get("price_ft")
        or rule.get("$/ft")
    )

    if price_ft > 0:
        return price_ft

    price_m = safe_float(
        rule.get("price_bar_m")
        or rule.get("Preço Barra ($/m)")
        or rule.get("price_m")
        or rule.get("$/m")
    )

    if price_m > 0:
        return price_m * 0.3048

    price_in = safe_float(
        rule.get("price_bar_in")
        or rule.get("Preço Barra ($/in)")
        or rule.get("price_in")
        or rule.get("$/in")
    )

    if price_in > 0:
        return price_in * 12.0

    return 0.0



def database_display_unit():
    # REGRA VISUAL GLOBAL POR BASE: METRIC=m | IMPERIAL=ft.
    return operational_unit_label(STOCK_UNIT_SYSTEM)


def internal_to_database_display_length(length_internal):
    # Precisao total preservada: sem round() para garantir round-trip exato.
    # Exemplo: 10 in / 12 = 0.8333333333333334 ft; * 12 = 10.0 in (exato em float64).
    # round(10/12, 2) = 0.83 ft; 0.83 * 12 = 9.96 in — perde precisao (HOTFIX-004).
    return internal_to_fixed_ft(length_internal, STOCK_UNIT_SYSTEM)


def item_display_length_for_database(item):
    if item.get("length_internal") not in (None, ""):
        return internal_to_database_display_length(item.get("length_internal"))

    unit = str(item.get("unit") or database_display_unit()).lower().strip()
    length = item.get("length", 0)

    # Precisao total preservada (ver HOTFIX-004).
    return length_with_unit_to_ft(length, unit, STOCK_UNIT_SYSTEM)


def calculate_stock_financials(cut_group, length, unit):

    """
    STOCK:
    Valor residual = comprimento aproveitável x preço da barra.

    Justificativa:
    O retalho em STOCK é material servível, portanto deve ser inventariado
    pelo valor proporcional da barra, não pelo valor de ferro velho.
    """

    ensure_stock_cost_rules_loaded_from_input_json()

    linear_weight = get_linear_weight(cut_group)

    if is_metric_system():

        length_m = display_length_to_metric_m(length, unit)
        price_m = get_metric_price_m(get_cost_rule(cut_group))

        total_weight = length_m * linear_weight if linear_weight > 0 else ""
        value = length_m * price_m if price_m > 0 else ""

    else:

        length_ft = display_length_to_imperial_ft(length, unit)
        price_ft = get_imperial_price_ft(get_cost_rule(cut_group))

        total_weight = length_ft * linear_weight if linear_weight > 0 else ""
        value = length_ft * price_ft if price_ft > 0 else ""

    return {
        "linear_weight": round(linear_weight, 2) if linear_weight > 0 else "",
        "total_weight": round(total_weight, 2) if total_weight != "" else "",
        "value": round(value, 2) if value != "" else "",
    }


def calculate_scrap_financials(cut_group, length, unit):

    """
    SCRAP:
    Valor residual = Peso Total x Preço Ferro Velho.
    """

    ensure_stock_cost_rules_loaded_from_input_json()

    linear_weight = get_linear_weight(cut_group)
    scrap_price = get_scrap_price_value(cut_group)

    if is_metric_system():

        length_m = display_length_to_metric_m(length, unit)
        total_weight = length_m * linear_weight if linear_weight > 0 else ""

        value = (
            total_weight * scrap_price
            if total_weight != "" and scrap_price > 0
            else ""
        )

    else:

        length_ft = display_length_to_imperial_ft(length, unit)
        total_weight = length_ft * linear_weight if linear_weight > 0 else ""

        if total_weight != "" and scrap_price > 0:
            rule = get_cost_rule(cut_group)
            has_lb_price = safe_float(
                rule.get("scrap_price_lb")
                or rule.get("Preço Ferro Velho ($/lb)")
                or rule.get("price_scrap_lb")
                or rule.get("scrap_lb")
                or rule.get("$/lb")
            ) > 0

            if has_lb_price:
                value = total_weight * scrap_price
            else:
                value = (total_weight * 0.45359237) * scrap_price
        else:
            value = ""

    return {
        "linear_weight": round(linear_weight, 2) if linear_weight > 0 else "",
        "total_weight": round(total_weight, 2) if total_weight != "" else "",
        "value": round(value, 2) if value != "" else "",
    }


def calculate_history_financials(cut_group, piece_length, piece_length_display, unit):

    length_value = piece_length_display if piece_length_display not in (None, "") else piece_length

    return calculate_stock_financials(
        cut_group=cut_group,
        length=length_value,
        unit=unit,
    )


# =========================================================
# DATABASE TEMPLATE / FORMATTING
# =========================================================

def remove_used_and_sanitize_stock_rows(ws):

    """
    REGRA DEFINITIVA DO INVENTÁRIO ATIVO

    A aba STOCK deve conter somente material disponível/servível em estoque.

    Remove automaticamente:
    - STATUS = USED;
    - linhas antigas/malformadas onde Projeto Origem não é projeto real;
    - linhas antigas/malformadas onde Barra Origem recebeu timestamp;
    - linhas antigas/malformadas sem Data Entrada;
    - AVAILABLE com Projeto Destino/Data Uso preenchidos.

    Isso limpa o lixo histórico e bloqueia o mesmo comportamento no futuro.
    """

    rows_to_delete = []

    for row_idx in range(5, ws.max_row + 1):

        stock_id = str(ws.cell(row_idx, 1).value or "").strip()
        status = str(ws.cell(row_idx, 2).value or "").strip().upper()
        source_project = str(ws.cell(row_idx, 9).value or "").strip()
        source_bar = str(ws.cell(row_idx, 10).value or "").strip()
        entry_date = ws.cell(row_idx, 11).value

        if not stock_id:
            continue

        # Entradas manuais (STK-) são sempre preservadas — material físico
        # cadastrado via ADD TO STOCK. Não passa por nenhuma sanitização.
        if stock_id.startswith("STK-"):
            continue

        # STATUS = USED é mantido no banco como registro operacional não ativo.
        # load_stock() + stock_consumer só consomem AVAILABLE, portanto USED
        # não interfere no inventário ativo, mas permite reprocessar o mesmo
        # projeto restaurando o retalho consumido anteriormente.

        # Linha antiga deslocada: Projeto Origem recebeu um ID de barra (B001, B002...)
        # em vez de nome de projeto — padrão de corrupção de versão anterior.
        # CORRECAO RISK-AUD-013 (AUD-002): a verificação anterior usava
        # startswith("PROJ") que deletava silenciosamente qualquer registro de
        # projeto com nome não iniciado em "PROJ" (ex: "TEST002_DONOR").
        # A regra correta: deletar apenas quando source_project parece um bar ID.
        import re as _re_san
        if source_project and _re_san.match(r'^B\d+$', source_project.upper().strip()):
            rows_to_delete.append(row_idx)
            continue

        # Linha antiga deslocada: Barra Origem recebeu timestamp/data.
        # IMPORTANTE: source_bar pode ser B001 ou STK00001.
        # STK00001 contém a letra "T" e é uma origem física válida da cadeia
        # de reaproveitamento. Portanto não pode ser apagado por conter "T".
        source_bar_is_datetime = False
        try:
            datetime.fromisoformat(source_bar.replace("Z", ""))
            source_bar_is_datetime = True
        except Exception:
            source_bar_is_datetime = False

        if source_bar_is_datetime:
            rows_to_delete.append(row_idx)
            continue

        # Linha de estoque válida precisa ter projeto de origem.
        if not source_project:
            rows_to_delete.append(row_idx)
            continue

        # Linha de estoque válida precisa ter barra de origem.
        if not source_bar:
            rows_to_delete.append(row_idx)
            continue

        # Linha de estoque válida precisa ter data de entrada.
        if entry_date in (None, ""):
            rows_to_delete.append(row_idx)
            continue

        if status in ("AVAILABLE", "DISPONIVEL", "DISPONÍVEL"):
            # L = Projeto Destino | M = Data Uso no template atual
            ws.cell(row_idx, 12).value = ""
            ws.cell(row_idx, 13).value = ""

    delete_rows_bottom_up(ws, rows_to_delete)

    # HOTFIX-008: linhas USED permanecem no banco (necessário para idempotência
    # via purge_project_generated_records), mas são ocultadas visualmente — a
    # aba STOCK responde apenas "o que está disponível agora".
    for row_idx in range(5, ws.max_row + 1):
        if not ws.cell(row_idx, 1).value:
            continue
        st = str(ws.cell(row_idx, 2).value or "").strip().upper()
        ws.row_dimensions[row_idx].hidden = (st == "USED")



def normalize_stock_sheet_structure(ws):

    """
    Normaliza a aba STOCK para o template industrial atual.

    Template atual:
    A ID Retalho
    B Status
    C Cut Group
    D Perfil
    E Comprimento
    F Peso linear
    G Peso Total
    H Valor residual
    I Projeto Origem
    J Barra Origem
    K Data Entrada
    L Projeto Destino
    M Data Uso
    N Localização
    O Observações
    """

    def header(col):
        return str(ws.cell(4, col).value or "").strip().upper()

    def sample_values(col):
        return {
            str(ws.cell(row_idx, col).value or "").strip().upper()
            for row_idx in range(5, min(ws.max_row, 20) + 1)
        }

    # Remove coluna antiga F = Und se ela ainda existir.
    if header(6) in ("UND", "UNIT") or sample_values(6).intersection({"M", "CM", "MM", "FT", "IN"}):
        ws.delete_cols(6, 1)

    # Remove coluna antiga "Validade" se ela ainda existir.
    for col_idx in range(1, ws.max_column + 1):
        if str(ws.cell(4, col_idx).value or "").strip().upper() in ("VALIDADE", "EXPIRATION"):
            ws.delete_cols(col_idx, 1)
            break

    # Remove colunas extras depois de O.
    if ws.max_column > 15:
        ws.delete_cols(16, ws.max_column - 15)

    # Limpa linhas vazias fantasma que inflam max_row e fazem
    # ws.append() escrever na linha 25+ em vez da linha 5.
    _empty = [r for r in range(5, ws.max_row + 1) if not ws.cell(r, 1).value]
    if _empty:
        delete_rows_bottom_up(ws, _empty)



def rebuild_stock_rows_from_current_template(ws):

    """
    Reidrata a aba STOCK inteira para o template atual.

    Corrige linhas antigas que ficaram com:
    - D = 2x2 / 3x2 em vez da descrição do STOCK_RULES coluna B;
    - F/G/H vazios;
    - campos operacionais deslocados.

    A regra é:
    C = Cut Group
    D = Perfil/Descrição oficial do STOCK_RULES
    E = Comprimento
    F = Peso linear
    G = Peso total
    H = Valor residual
    I = Projeto Origem
    J = Barra Origem
    K = Data Entrada
    L = Projeto Destino
    M = Data Uso
    N = Localização
    O = Observações
    """

    # Detect the unit that was stored in col5 BEFORE apply_database_templates()
    # rewrites the headers to the current unit.  This allows correct in-place
    # conversion when the project switches unit systems (e.g. imperial → metric).
    stored_unit = _parse_stored_unit(ws, 5)
    current_unit = get_length_unit_label()
    # Conversion is guarded by bool(STOCK_COST_RULES): the real pipeline always
    # supplies non-empty rules, so conversion triggers correctly.  Callers that
    # pass empty rules (e.g. isolated unit tests) leave the DB header potentially
    # inconsistent with freshly appended data; skipping conversion prevents
    # those scenarios from corrupting the stored lengths.
    unit_changed = (
        stored_unit is not None
        and stored_unit != current_unit
        and bool(STOCK_COST_RULES)
    )

    for row_idx in range(5, ws.max_row + 1):

        if not ws.cell(row_idx, 1).value:
            continue

        status = str(ws.cell(row_idx, 2).value or "").strip().upper()
        is_used = status == "USED"

        cut_group = ws.cell(row_idx, 3).value
        length = ws.cell(row_idx, 5).value

        if unit_changed:
            # Col14 (Localização) holds the HOTFIX-011 backup of the original
            # bar length written in stored_unit.  Convert it so that
            # purge_project_generated_records() restores a value in current_unit.
            _col14 = ws.cell(row_idx, 14).value
            if _col14 is not None:
                if is_metric_system():
                    ws.cell(row_idx, 14).value = display_length_to_metric_m(_col14, stored_unit)
                else:
                    ws.cell(row_idx, 14).value = display_length_to_imperial_ft(_col14, stored_unit)

            if not is_used:
                # AVAILABLE rows: col5 is still in stored_unit → convert in-place.
                # USED rows: col5 was already overwritten in current_unit by
                # commit_database_writes() (HOTFIX-007A), so skip to avoid
                # double-converting.
                if is_metric_system():
                    length = display_length_to_metric_m(length, stored_unit)
                else:
                    length = display_length_to_imperial_ft(length, stored_unit)
                ws.cell(row_idx, 5).value = length

        # USED rows are hidden (HOTFIX-008); skip financial recalculation.
        if is_used:
            continue

        financials = calculate_stock_financials(
            cut_group=cut_group,
            length=length,
            unit=current_unit,
        )

        ws.cell(row_idx, 4).value = get_profile_description(
            cut_group=cut_group,
            fallback=ws.cell(row_idx, 4).value,
        )

        if financials["linear_weight"] not in (None, ""):
            ws.cell(row_idx, 6).value = financials["linear_weight"]
            ws.cell(row_idx, 7).value = financials["total_weight"]
            ws.cell(row_idx, 8).value = financials["value"]



def normalize_history_sheet_structure(ws):

    """
    Normaliza HISTORY para o template atual.

    Remove a antiga coluna G = Und, se existir.

    Template atual:
    A Data/Hora
    B Evento
    C ID Retalho
    D Cut Group
    E Comprimento Retalho
    F Comprimento Peça
    G Peso linear
    H Peso Total
    I Valor residual
    J Projeto
    K Barra Origem
    L Operador
    M Status Anterior
    N Status Novo
    """

    def header(col):
        return str(ws.cell(4, col).value or "").strip().upper()

    def sample_values(col):
        return {
            str(ws.cell(row_idx, col).value or "").strip().upper()
            for row_idx in range(5, min(ws.max_row, 20) + 1)
        }

    if header(7) in ("UND", "UNIT") or sample_values(7).intersection({"M", "CM", "MM", "FT", "IN"}):
        ws.delete_cols(7, 1)

    if ws.max_column > 14:
        ws.delete_cols(15, ws.max_column - 14)

    # Remove linhas históricas impossíveis/deslocadas geradas por versão anterior.
    # Exemplo ruim:
    # J = Sistema, K = AVAILABLE, L = USED.
    # Linhas completamente vazias (sem valor em col A) também são marcadas para
    # remoção: acumulam quando save_database() aplica formatação a células sem
    # valor, causando que ws.append() escreva os dados mais abaixo do esperado.
    rows_to_delete = []

    for row_idx in range(5, ws.max_row + 1):

        if not ws.cell(row_idx, 1).value:
            rows_to_delete.append(row_idx)  # vazia ou sem timestamp → compactar
            continue

        project = str(ws.cell(row_idx, 10).value or "").strip().upper()
        source_bar = str(ws.cell(row_idx, 11).value or "").strip().upper()
        operator = str(ws.cell(row_idx, 12).value or "").strip().upper()

        if project == "SISTEMA" and source_bar in ("AVAILABLE", "DISPONIVEL", "DISPONÍVEL") and operator == "USED":
            rows_to_delete.append(row_idx)
            continue

        # CORRECAO RISK-AUD-013: mesmo padrão da STOCK — deletar somente se
        # projeto parece um bar ID (B001, B002...), não por ausência de prefixo.
        import re as _re_hist
        if project and _re_hist.match(r'^B\d+$', project.upper().strip()):
            rows_to_delete.append(row_idx)
            continue

    delete_rows_bottom_up(ws, rows_to_delete)



def rebuild_history_rows_from_current_template(ws):

    """
    Recalcula G/H/I da HISTORY com base no Cut Group e no comprimento da peça.

    E = comprimento total do retalho usado.
    F = comprimento da peça consumida.
    G = peso linear do perfil.
    H = peso total da peça consumida.
    I = valor residual proporcional da peça consumida.
    """

    # Col F (piece_length) header reveals the unit stored when these rows were written.
    # Using the stored unit avoids treating "12.5 ft" as "12.5 m" in the financial calc.
    # Note: HISTORY is an append-only log; values are NOT converted in-place here.
    _hist_stored_unit = _parse_stored_unit(ws, 6) or get_length_unit_label()

    for row_idx in range(5, ws.max_row + 1):

        if not ws.cell(row_idx, 1).value:
            continue

        cut_group = ws.cell(row_idx, 4).value
        piece_length = ws.cell(row_idx, 6).value

        financials = calculate_history_financials(
            cut_group=cut_group,
            piece_length=piece_length,
            piece_length_display=piece_length,
            unit=_hist_stored_unit,
        )

        ws.cell(row_idx, 7).value = financials["linear_weight"]
        ws.cell(row_idx, 8).value = financials["total_weight"]
        ws.cell(row_idx, 9).value = financials["value"]



def apply_database_templates(wb):

    if "STOCK" in wb.sheetnames:

        ws = wb["STOCK"]

        normalize_stock_sheet_structure(ws)

        remove_used_and_sanitize_stock_rows(ws)

        rebuild_stock_rows_from_current_template(ws)

        stock_headers = [
            "ID Retalho",
            "Status",
            "Cut Group",
            "Perfil",
            stock_length_column_header("Comprimento"),
            "Peso (kg/m)" if is_metric_system() else "Peso (lb/ft)",
            "Peso Total",
            "Valor residual",
            "Projeto Origem",
            "Barra Origem",
            "Data Entrada",
            "Projeto Destino",
            "Data Uso",
            "Localização",
            "Observações",
        ]

        for col_idx, header in enumerate(stock_headers, start=1):
            ws.cell(4, col_idx).value = header

        # HOTFIX-008 Regra 4: colunas de histórico ocultas na visualização STOCK.
        # Dados preservados integralmente — load_stock() e purge_project_generated_records()
        # continuam acessando por índice de coluna sem impacto.
        for col_letter in ("L", "M", "N"):
            ws.column_dimensions[col_letter].hidden = True

        # HOTFIX-008 Regra 5: H3 = valor financeiro total do estoque disponível.
        # Calculado após rebuild_stock_rows_from_current_template() para garantir
        # que col H está atualizada. Somente status AVAILABLE é somado.
        ws.cell(3, 7).value = "Valor Total Disponível:"
        total_val = 0.0
        for row_idx in range(5, ws.max_row + 1):
            if not ws.cell(row_idx, 1).value:
                continue
            st = str(ws.cell(row_idx, 2).value or "").strip().upper()
            if st in ("AVAILABLE", "DISPONIVEL", "DISPONÍVEL"):
                v = ws.cell(row_idx, 8).value
                if isinstance(v, (int, float)):
                    total_val += v
        ws.cell(3, 8).value = total_val

    if "SCRAP" in wb.sheetnames:

        ws = wb["SCRAP"]

        scrap_headers = [
            "ID Retalho",
            "Status",
            "Cut Group",
            "Perfil",
            stock_length_column_header("Comprimento"),
            f"Peso ({get_stock_unit_label()})",
            "Peso Total",
            "Valor residual",
            "Projeto Origem",
            "Barra Origem",
            "Data Descarte",
        ]

        for col_idx, header in enumerate(scrap_headers, start=1):
            ws.cell(4, col_idx).value = header

    if "HISTORY" in wb.sheetnames:

        ws = wb["HISTORY"]

        normalize_history_sheet_structure(ws)

        rebuild_history_rows_from_current_template(ws)

        history_headers = [
            "Data/Hora",
            "Evento",
            "ID Retalho",
            "Cut Group",
            stock_length_column_header("Comprimento Retalho"),
            stock_length_column_header("Comprimento Peça"),
            f"Peso ({get_stock_unit_label()})",
            "Peso Total",
            "Valor residual",
            "Projeto",
            "Barra Origem",
            "Operador",
            "Status Anterior",
            "Status Novo",
        ]

        for col_idx, header in enumerate(history_headers, start=1):
            ws.cell(4, col_idx).value = header


def normalize_excel_date_cell(cell):

    value = cell.value

    if value in (None, ""):
        return

    if isinstance(value, datetime):
        cell.value = value.date()
        cell.number_format = "dd/mm/yyyy"
        return

    text = str(value).strip()

    if not text:
        return

    try:
        parsed = datetime.fromisoformat(text.replace("Z", ""))
        cell.value = parsed.date()
    except Exception:
        # Mantém o valor original se não for ISO date.
        cell.value = text

    cell.number_format = "dd/mm/yyyy"


def _normalize_datetime_cell(cell):
    """Normaliza célula de data/hora preservando o componente de tempo."""
    value = cell.value

    if value in (None, ""):
        return

    if isinstance(value, datetime):
        cell.number_format = "dd/mm/yyyy hh:mm"
        return

    text = str(value).strip()

    if not text:
        return

    try:
        parsed = datetime.fromisoformat(text.replace("Z", ""))
        cell.value = parsed
    except Exception:
        pass

    cell.number_format = "dd/mm/yyyy hh:mm"


def _apply_column_widths(ws, sheet_name):
    """Define larguras fixas de colunas por sheet — chamado uma vez por save."""
    if sheet_name == "STOCK":
        for col, w in zip("ABCDEFGHIJKLMNO",
                          [19,14,14,34,12,12,12,14,30,14,14,18,14,14,16]):
            ws.column_dimensions[col].width = w
    elif sheet_name == "SCRAP":
        ws.column_dimensions["A"].width = 19
        ws.column_dimensions["I"].width = 30
    elif sheet_name == "HISTORY":
        for col, w in zip("ABCDEFGHIJKLMN",
                          [18,18,14,14,18,18,12,12,14,30,14,14,16,16]):
            ws.column_dimensions[col].width = w


def apply_database_number_formats(wb, first_new_row=None):
    """
    Aplica number_format e alignment nas linhas de dados do banco.

    first_new_row: se fornecido, aplica formatação apenas a partir dessa
    linha — evita reiterar linhas já formatadas em saves anteriores.
    Quando None, formata a partir da linha 5 (comportamento original).

    Otimizações aplicadas:
    - Loop CENTER global removido: cada campo define seu próprio alignment,
      eliminando N×max_col atribuições redundantes que disparavam serialize.
    - max_row resolvido via _ws_dimensions() UMA vez por sheet (O(1)).
    - Objetos Alignment instanciados UMA vez, reutilizados no loop.
    """
    money_format = '$ #,##0.00'
    number_format = '0.00'
    length_format = f'0.00 "{get_length_unit_label()}"'

    CENTER_ALIGN = Alignment(horizontal="center", vertical="center")
    RIGHT_ALIGN  = Alignment(horizontal="right",  vertical="center")
    LEFT_ALIGN   = Alignment(horizontal="left",   vertical="center")

    for sheet_name in ["STOCK", "SCRAP", "HISTORY"]:

        if sheet_name not in wb.sheetnames:
            continue

        ws = wb[sheet_name]

        # Resolve max_row UMA vez por sheet — nunca dentro do loop
        _, max_row = _ws_dimensions(ws)
        _apply_column_widths(ws, sheet_name)

        if max_row < 5:
            continue

        start_row = first_new_row if (first_new_row and first_new_row > 5) else 5

        if sheet_name == "STOCK":

            # HOTFIX-008: H3 = subtotal financeiro do estoque disponível
            ws.cell(3, 7).alignment = RIGHT_ALIGN
            ws.cell(3, 8).number_format = money_format
            ws.cell(3, 8).alignment = RIGHT_ALIGN

            for row_idx in range(start_row, max_row + 1):

                ws.cell(row_idx, 4).alignment = LEFT_ALIGN           # D Perfil
                ws.cell(row_idx, 5).number_format = length_format     # E Comprimento
                ws.cell(row_idx, 5).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 6).number_format = number_format     # F Peso linear
                ws.cell(row_idx, 6).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 7).number_format = number_format     # G Peso total
                ws.cell(row_idx, 7).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 8).number_format = money_format      # H Valor
                ws.cell(row_idx, 8).alignment = RIGHT_ALIGN
                for col_idx in [9, 10, 12, 14, 15]:
                    ws.cell(row_idx, col_idx).alignment = CENTER_ALIGN
                for col_idx in [11, 13]:                              # K/M datas
                    normalize_excel_date_cell(ws.cell(row_idx, col_idx))
                    ws.cell(row_idx, col_idx).alignment = CENTER_ALIGN

        elif sheet_name == "SCRAP":

            for row_idx in range(start_row, max_row + 1):

                ws.cell(row_idx, 4).alignment = LEFT_ALIGN            # D Perfil
                ws.cell(row_idx, 5).number_format = length_format     # E Comprimento
                ws.cell(row_idx, 5).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 6).number_format = number_format     # F Peso linear
                ws.cell(row_idx, 6).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 7).number_format = number_format     # G Peso total
                ws.cell(row_idx, 7).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 8).number_format = money_format      # H Valor
                ws.cell(row_idx, 8).alignment = RIGHT_ALIGN
                normalize_excel_date_cell(ws.cell(row_idx, 11))       # K Data Descarte
                ws.cell(row_idx, 11).alignment = CENTER_ALIGN

        elif sheet_name == "HISTORY":

            for row_idx in range(start_row, max_row + 1):

                _normalize_datetime_cell(ws.cell(row_idx, 1))         # A Data/Hora (preserva hora)
                ws.cell(row_idx, 1).alignment = CENTER_ALIGN
                ws.cell(row_idx, 2).alignment = LEFT_ALIGN            # B Evento
                for col_idx in [3, 4]:                                # C ID Retalho / D Cut Group
                    ws.cell(row_idx, col_idx).alignment = CENTER_ALIGN
                ws.cell(row_idx, 5).number_format = length_format     # E Comprimento retalho
                ws.cell(row_idx, 5).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 6).number_format = length_format     # F Comprimento peça
                ws.cell(row_idx, 6).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 7).number_format = number_format     # G Peso linear
                ws.cell(row_idx, 7).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 8).number_format = number_format     # H Peso total
                ws.cell(row_idx, 8).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 9).number_format = money_format      # I Valor
                ws.cell(row_idx, 9).alignment = RIGHT_ALIGN
                ws.cell(row_idx, 10).alignment = LEFT_ALIGN           # J Projeto
                ws.cell(row_idx, 11).alignment = LEFT_ALIGN           # K Barra Origem
                for col_idx in [12, 13, 14]:                          # L Operador / M Status Ant / N Status Novo
                    ws.cell(row_idx, col_idx).alignment = CENTER_ALIGN



def _check_file_writable(path):
    """
    Levanta PermissionError com mensagem clara se o arquivo estiver
    aberto no Excel ou bloqueado por outro processo.
    """
    import os
    p = Path(path)
    if not p.exists():
        return
    try:
        with open(p, "r+b"):
            pass
    except PermissionError:
        raise PermissionError(
            f"\n\n  ARQUIVO BLOQUEADO: {p.name}\n"
            f"  Feche o arquivo no Excel e execute novamente.\n"
        )


def save_database(wb):
    """
    Grava o banco de forma atômica:
      1. Salva em arquivo temporário (.tmp)
      2. Renomeia sobre o original (operação atômica no SO)
    Garante que um crash no meio nunca corrompe o banco.
    """
    ensure_stock_cost_rules_loaded_from_input_json()

    apply_database_templates(wb)

    for ws in wb.worksheets:
        apply_global_sheet_format(
            ws,
            header_row=4,
            title_row=1
        )

    apply_database_number_formats(wb)

    for ws in wb.worksheets:
        ws.row_dimensions[4].height = 30
        for cell in ws[4]:
            cell.alignment = Alignment(
                horizontal="center",
                vertical="center",
                wrap_text=True,
            )

    db_path = Path(DATABASE_PATH)
    tmp_path = db_path.with_suffix(".tmp")

    _check_file_writable(db_path)

    try:
        make_workbook_text_numbers_calculable(wb)
        wb.save(tmp_path)
        wb.close()
        tmp_path.replace(db_path)
    except Exception as exc:
        tmp_path.unlink(missing_ok=True)
        raise RuntimeError(
            f"Falha ao gravar banco de dados: {exc}"
        ) from exc


def create_empty_database():

    Path("database").mkdir(exist_ok=True)

    wb = Workbook()

    ws = wb.active
    ws.title = "STOCK"

    ws.append(["ESTOQUE DE RETALHOS"])
    ws.append(["Gerado automaticamente. Não editar manualmente."])
    ws.append([])
    ws.append([
        "ID Retalho",
        "Status",
        "Cut Group",
        "Perfil",
        stock_length_column_header("Comprimento"),
        f"Peso ({get_stock_unit_label()})",
        "Peso Total",
        "Valor residual",
        "Projeto Origem",
        "Barra Origem",
        "Data Entrada",
        "Projeto Destino",
        "Data Uso",
        "Localização",
        "Observações"
    ])

    ws = wb.create_sheet("SCRAP")
    ws.append(["DESCARTE DE RETALHOS"])
    ws.append(["Gerado automaticamente. Não editar manualmente."])
    ws.append([])
    ws.append([
        "ID Retalho",
        "Status",
        "Cut Group",
        "Perfil",
        stock_length_column_header("Comprimento"),
        f"Peso ({get_stock_unit_label()})",
        "Peso Total",
        "Valor residual",
        "Projeto Origem",
        "Barra Origem",
        "Data Descarte"
    ])

    ws = wb.create_sheet("HISTORY")
    ws.append(["HISTÓRICO DE RASTREABILIDADE"])
    ws.append(["Log imutável. Cada evento = uma linha. Nunca deletar registros."])
    ws.append([])
    ws.append([
        "Data/Hora",
        "Evento",
        "ID Retalho",
        "Cut Group",
        stock_length_column_header("Comprimento Retalho"),
        "Comprimento Peça",
        f"Peso ({get_stock_unit_label()})",
        "Peso Total",
        "Valor residual",
        "Projeto",
        "Barra Origem",
        "Operador",
        "Status Anterior",
        "Status Novo"
    ])

    save_database(wb)


def ensure_database_exists():

    Path("database").mkdir(exist_ok=True)

    if not Path(DATABASE_PATH).exists():
        create_empty_database()


def reset_stock_database():

    create_empty_database()


def normalize_project_name(value):

    """
    Normalização canônica de nome de projeto para controle de idempotência.

    Objetivo:
    - tratar como mesmo projeto variações acidentais de espaço, hífen e underscore;
    - evitar duplicação em STOCK/SCRAP/HISTORY quando o mesmo projeto é reprocessado;
    - preservar revisões reais quando o nome muda de fato.

    Exemplos tratados como mesmo projeto:
    PROJ_A -BASE METRO
    PROJ_A BASE METRO
    PROJ_A_BASE METRO
    """

    text = str(value or "").strip().upper()

    for char in ["-", "_"]:
        text = text.replace(char, " ")

    text = " ".join(text.split())

    return text


def delete_rows_bottom_up(ws, row_numbers):
    """
    Remove linhas da worksheet por reescrita em memória.

    A implementação original usava ws.delete_rows() em loop — cada chamada
    é O(cells) porque o openpyxl recalcula ws.max_column via generator para
    cada linha deletada. Com 1.838 linhas para deletar isso gerava ~46M
    iterações de generator.

    Estratégia alternativa (O(n)):
    1. Lê todas as linhas de dados para memória.
    2. Filtra as que devem ser removidas.
    3. Apaga o conteúdo das células da sheet.
    4. Reescreve as linhas mantidas sequencialmente.

    Preserva: estilos, number_format e alignment NÃO são copiados (serão
    reaplicados pelo apply_database_number_formats no save_database).
    Apenas valores são preservados — suficiente para integridade do dado.
    """
    if not row_numbers:
        return

    rows_to_delete = set(row_numbers)

    # Resolve max_row e max_col UMA vez — O(1) via _cells
    max_col, max_row = _ws_dimensions(ws)
    if max_row < 5 or max_col == 0:
        return

    # Lê todas as linhas de dados (row >= 5) para memória.
    # Linhas completamente vazias (todos os valores None) são descartadas:
    # podem surgir de formatação aplicada a células sem valor e causam gaps
    # no append subsequente (ex: dados de HISTORY iniciando na linha 7 em
    # vez de 5 após um purge que deixa rows vazias com alignment/number_format).
    kept_rows = []
    for row_idx in range(5, max_row + 1):
        if row_idx in rows_to_delete:
            continue
        row_values = [ws.cell(row_idx, col).value for col in range(1, max_col + 1)]
        if any(v is not None for v in row_values):
            kept_rows.append(row_values)

    # Remove todas as células de dados (row >= 5) de ws._cells para que
    # openpyxl não persista células com value=None mas com formatação residual.
    # Setar value=None deixa o Cell em _cells com estilo aplicado, o que faz
    # max_row permanecer alto e ws.append() escrever nas linhas erradas.
    for key in list(ws._cells.keys()):
        if key[0] >= 5:
            del ws._cells[key]

    # Reescreve as linhas mantidas sequencialmente a partir da linha 5
    for offset, row_values in enumerate(kept_rows):
        target_row = 5 + offset
        for col_idx, value in enumerate(row_values, start=1):
            ws.cell(target_row, col_idx).value = value


def _load_database_safe():
    """
    Carrega o banco com recuperação SELETIVA por tipo de exceção.

    CORRECAO CROSS-003 (AUD-002 RISK-006):
    A versão anterior usava 'except Exception' que capturava QUALQUER exceção,
    incluindo PermissionError e OSError transitórios, e então APAGAVA o banco.
    Isso podia causar perda catastrófica de dados por um erro temporário de
    bloqueio de arquivo (ex: antivírus, Excel aberto, rede instável).

    Regras após correção:
      - PermissionError / OSError  → PROPAGA (erro transitório — não apaga banco)
      - BadZipFile / KeyError / outros → tenta restaurar de .bak, depois recria
      - Arquivo inexistente → cria banco vazio (comportamento original correto)
    """
    import zipfile

    if not Path(DATABASE_PATH).exists():
        ensure_database_exists()
        return load_workbook(DATABASE_PATH)

    try:
        return load_workbook(DATABASE_PATH)
    except (PermissionError, OSError):
        # Erro transitório — propaga sem apagar banco válido
        raise
    except Exception:
        # Corrupção real (BadZipFile, KeyError, estrutura inválida, etc.)
        # Tenta recuperar do backup antes de recriar
        bak_path = Path(DATABASE_PATH).with_suffix(".bak")
        if bak_path.exists():
            try:
                import shutil as _sh
                _sh.copy2(bak_path, DATABASE_PATH)
                return load_workbook(DATABASE_PATH)
            except Exception:
                pass
        # Sem backup disponível — recriar banco vazio
        try:
            Path(DATABASE_PATH).unlink()
        except Exception:
            pass
        ensure_database_exists()
        return load_workbook(DATABASE_PATH)


def purge_project_generated_records(project_name):

    """
    REGRA DEFINITIVA DE IDEMPOTÊNCIA POR PROJETO

    O nome do projeto é chave única operacional.

    Ao reprocessar o mesmo projeto, o banco deve SOBRESCREVER a rodada anterior,
    e não acumular novos retalhos/scraps sobre os antigos.

    Esta rotina faz antes do solver:

    1) STOCK
       - remove todos os retalhos gerados pelo projeto atual;
       - também remove variações antigas equivalentes do nome do projeto
         por normalização canônica;
       - libera retalhos de outros projetos que tinham sido consumidos pelo
         projeto atual numa execução anterior.

    2) SCRAP
       - remove todos os descartes gerados pelo projeto atual.

    3) HISTORY
       - remove eventos cujo Projeto = projeto atual.

    Observação:
    - Revisões reais devem ter outro nome de projeto, como você definiu.
    - Portanto, mesmo nome = mesmo projeto = sobrescrever.
    """

    import shutil

    ensure_database_exists()

    project_key = normalize_project_name(project_name)

    if not project_key:
        return

    db_path = Path(DATABASE_PATH)
    bak_path = db_path.with_suffix(".bak")

    _check_file_writable(db_path)

    shutil.copy2(db_path, bak_path)

    wb = _load_database_safe()

    # =====================================================
    # STOCK
    # =====================================================

    ws_stock = wb["STOCK"]

    stock_rows_to_delete = []

    for row in ws_stock.iter_rows(min_row=5):

        row_number = row[0].row

        source_project = normalize_project_name(row[8].value)
        destination_project = normalize_project_name(row[11].value)

        # Retalho gerado pelo mesmo projeto: remove para idempotência.
        # EXCEÇÃO: STK- = entrada manual (ADD TO STOCK) — nunca apagar.
        stock_id_purge = str(row[0].value or "").strip()
        if source_project == project_key and not stock_id_purge.startswith("STK-"):
            stock_rows_to_delete.append(row_number)
            continue

        # Retalho de outro projeto usado pelo projeto atual numa execução anterior:
        # volta a AVAILABLE para o novo processamento decidir novamente.
        if destination_project == project_key:
            row[1].value = "AVAILABLE"
            # Restaura comprimento original (col 5) a partir do backup em col 14,
            # gravado por update_stock_status() antes de HOTFIX-007A sobrescrever
            # col 5 com o comprimento consumido.
            _original_len = row[13].value  # col 14 (índice 13)
            if _original_len:
                row[4].value = _original_len  # restaura col 5
                row[13].value = ""            # limpa o backup
            row[11].value = ""
            row[12].value = ""

    delete_rows_bottom_up(ws_stock, stock_rows_to_delete)

    # =====================================================
    # SCRAP
    # =====================================================

    ws_scrap = wb["SCRAP"]

    scrap_rows_to_delete = []

    for row in ws_scrap.iter_rows(min_row=5):

        row_number = row[0].row
        source_project = normalize_project_name(row[8].value)

        if source_project == project_key:
            scrap_rows_to_delete.append(row_number)

    delete_rows_bottom_up(ws_scrap, scrap_rows_to_delete)

    # =====================================================
    # HISTORY
    # =====================================================
    # Idempotência: remove eventos do projeto atual para evitar duplicatas
    # ao reprocessar. Eventos de OUTROS projetos são preservados integralmente
    # (filtro exclusivo pela coluna J = Projeto).

    ws_history = wb["HISTORY"]

    history_rows_to_delete = []

    for row in ws_history.iter_rows(min_row=5):

        row_number = row[0].row

        if not row[0].value:
            continue

        project_col = normalize_project_name(row[9].value)

        if project_col == project_key:
            history_rows_to_delete.append(row_number)

    delete_rows_bottom_up(ws_history, history_rows_to_delete)

    save_database(wb)



def load_stock():

    ensure_database_exists()

    wb = load_workbook(DATABASE_PATH, read_only=True)
    ws = wb["STOCK"]

    # Use the unit stored in the column-5 header to correctly interpret existing values.
    # Without this, switching from an imperial project to a metric one would treat
    # "12.5 ft" as "12.5 m", inflating the internal length by ~3.28×.
    _stored_unit = _parse_stored_unit(ws, 5) or get_length_unit_label()

    items = []

    for row in ws.iter_rows(min_row=5, values_only=True):

        if not row[0]:
            continue

        display_length = row[4]
        unit = _stored_unit

        # Cross-unit conversion: stored value may be in a different unit than the
        # current project (e.g. ft stored, metric project running).
        if is_metric_system():
            internal_length = display_length_to_metric_m(display_length, unit) * 1000.0
        else:
            internal_length = display_length_to_imperial_ft(display_length, unit) * 12.0

        items.append({
            "stock_id": row[0],
            "status": row[1],
            "cut_group": row[2],
            "profile": row[3],
            "length": internal_length,
            "display_length": display_length,
            "unit": unit,
            "linear_weight": row[5],
            "weight": row[6],
            "residual_value": row[7],
            "source_project": row[8],
            "source_bar_id": row[9],
            "created_at": row[10],
            "destination_project": row[11],
            "used_at": row[12],
            "location": row[13],
            "observation": row[14] if len(row) > 14 else ""
        })

    wb.close()

    return items


def load_scrap():

    ensure_database_exists()

    wb = load_workbook(DATABASE_PATH, read_only=True)
    ws = wb["SCRAP"]

    _stored_unit = _parse_stored_unit(ws, 5) or ("m" if is_metric_system() else "ft")

    items = []

    for row in ws.iter_rows(min_row=5, values_only=True):

        if not row[0]:
            continue

        items.append({
            "scrap_id": row[0],
            "status": row[1],
            "cut_group": row[2],
            "profile": row[3],
            "length": row[4],
            "unit": _stored_unit,
            "linear_weight": row[5],
            "weight": row[6],
            "revenue": row[7],
            "source_project": row[8],
            "source_bar_id": row[9],
            "created_at": row[10]
        })

    wb.close()

    return items


def append_stock(stock_items, _wb=None):
    """
    Adiciona retalhos de stock ao banco.

    _wb: workbook já aberto (passado por commit_database_writes para
         consolidar múltiplas operações em um único save). Quando None,
         opera de forma independente (compatibilidade retroativa).
    """
    ensure_database_exists()

    _standalone = _wb is None
    wb = _wb if _wb is not None else load_workbook(DATABASE_PATH)
    ws = wb["STOCK"]

    # Remover linhas vazias fantasma com ws.delete_rows() nativo —
    # único método que atualiza ws.max_row internamente no openpyxl,
    # garantindo que ws.append() escreva na posição correta.
    _r = ws.max_row
    while _r >= 5:
        if not ws.cell(_r, 1).value:
            ws.delete_rows(_r)
        _r -= 1

    existing_ids = set()
    for row in ws.iter_rows(min_row=5, values_only=True):
        if row[0]:
            existing_ids.add(row[0])

    for item in stock_items:

        stock_id = item.get("stock_id")
        if not stock_id or stock_id in existing_ids:
            continue

        display_length = item_display_length_for_database(item)
        display_unit = database_display_unit()

        financials = calculate_stock_financials(
            cut_group=item.get("cut_group"),
            length=display_length,
            unit=display_unit,
        )

        # Fallback para financeiros pré-calculados no dict (ADD TO STOCK via CRITERIOS)
        _lin_w = financials["linear_weight"] or item.get("linear_weight") or ""
        _tot_w = financials["total_weight"]  or item.get("weight")        or ""
        _val   = financials["value"]         or item.get("value")         or ""

        ws.append([
            stock_id,
            item.get("status", "AVAILABLE"),
            item.get("cut_group"),
            get_profile_description(
                item.get("cut_group"),
                item.get("profile", "")
            ),
            display_length,
            _lin_w,
            _tot_w,
            _val,
            item.get("source_project", "PROJECT"),
            item.get("source_bar_id"),
            item.get("created_at", datetime.now().isoformat(timespec="seconds")),
            item.get("destination_project", ""),
            item.get("used_at", ""),
            item.get("location", ""),
            item.get("observation", "")
        ])

        existing_ids.add(stock_id)

    if _standalone:
        save_database(wb)


def append_scrap(scrap_items, _wb=None):
    """
    Adiciona itens de scrap ao banco.

    _wb: workbook já aberto (ver append_stock).
    """
    ensure_database_exists()

    _standalone = _wb is None
    wb = _wb if _wb is not None else load_workbook(DATABASE_PATH)
    ws = wb["SCRAP"]

    existing_ids = set()
    for row in ws.iter_rows(min_row=5, values_only=True):
        if row[0]:
            existing_ids.add(row[0])

    for item in scrap_items:

        scrap_id = item.get("scrap_id")
        if not scrap_id or scrap_id in existing_ids:
            continue

        display_length = item_display_length_for_database(item)
        display_unit = database_display_unit()

        financials = calculate_scrap_financials(
            cut_group=item.get("cut_group"),
            length=display_length,
            unit=display_unit,
        )

        ws.append([
            scrap_id,
            item.get("status", "SCRAP"),
            item.get("cut_group"),
            get_profile_description(
                item.get("cut_group"),
                item.get("profile", "")
            ),
            display_length,
            financials["linear_weight"],
            financials["total_weight"],
            financials["value"],
            item.get("source_project", "PROJECT"),
            item.get("source_bar_id"),
            item.get("created_at", datetime.now().isoformat(timespec="seconds"))
        ])

        existing_ids.add(scrap_id)

    if _standalone:
        save_database(wb)


def append_history(history_items, _wb=None):
    """
    Adiciona eventos ao HISTORY imutável.

    _wb: workbook já aberto (ver append_stock).
    """
    ensure_database_exists()

    _standalone = _wb is None
    wb = _wb if _wb is not None else load_workbook(DATABASE_PATH)
    ws = wb["HISTORY"]

    # Garante estrutura atual antes de anexar novas linhas.
    apply_database_templates(wb)

    for item in history_items:

        financials = calculate_history_financials(
            cut_group=item.get("cut_group"),
            piece_length=item.get("piece_length", ""),
            piece_length_display=item.get("piece_length_display", ""),
            unit=item.get("unit", "m"),
        )

        ws.append([
            item.get("timestamp", datetime.now().isoformat(timespec="seconds")),
            item.get("event"),
            item.get("stock_id"),
            item.get("cut_group"),
            item.get("stock_length_display", item.get("stock_length", "")),
            item.get("piece_length_display", item.get("piece_length", "")),
            financials["linear_weight"],
            financials["total_weight"],
            financials["value"],
            item.get("project", item.get("project_name", "PROJECT")),
            item.get("source_bar_id", ""),
            item.get("operator", "Sistema"),
            item.get("old_status", "AVAILABLE"),
            item.get("new_status", "USED")
        ])

    if _standalone:
        save_database(wb)


def update_stock_status(updated_stock, _wb=None):
    """
    Atualiza status de retalhos existentes.

    _wb: workbook já aberto (ver append_stock).
    """
    ensure_database_exists()

    _standalone = _wb is None
    wb = _wb if _wb is not None else load_workbook(DATABASE_PATH)
    ws = wb["STOCK"]

    stock_map = {}
    for row in ws.iter_rows(min_row=5):
        stock_id = row[0].value
        if stock_id:
            stock_map[stock_id] = row[0].row

    for item in updated_stock:

        stock_id = item.get("stock_id")
        if stock_id not in stock_map:
            continue

        row_idx = stock_map[stock_id]
        new_status = str(item.get("status", "")).strip().upper()

        if new_status == "USED":
            # HISTORY registra o evento de uso.
            # STOCK mantém a linha como USED para preservar a cadeia física
            # e permitir reprocessamento idempotente do mesmo projeto.
            ws.cell(row_idx, 2).value = "USED"
            ws.cell(row_idx, 12).value = item.get(
                "destination_project",
                ws.cell(row_idx, 12).value,
            )
            ws.cell(row_idx, 13).value = item.get(
                "used_at",
                ws.cell(row_idx, 13).value,
            )
            # HOTFIX-007A: atualiza display_length para o comprimento consumido em pecas.
            # Sem esta correcao, o item USED fica com Length = source_length (original),
            # enquanto o SCRAP gerado adiciona o residual separadamente:
            #   USED(10 ft) + SCRAP(0.83 ft) = 10.83 ft > 10 ft (source) — VIOLACAO.
            # Apos a correcao: USED(9.17 ft) + SCRAP(0.83 ft) = 10.00 ft (conservado).
            #
            # consumed_length_display e pre-calculado em stock_consumer.py com unit_system
            # do parametro (nao o global STOCK_UNIT_SYSTEM) para evitar condicao de corrida
            # quando ensure_stock_cost_rules_loaded_from_input_json() reseta o global.
            _consumed_display = item.get("consumed_length_display")
            if _consumed_display is not None and _consumed_display > 0:
                # Salva comprimento original em col 14 (Localização) antes de sobrescrever.
                # purge_project_generated_records() usa este backup para restaurar col 5
                # quando o item volta a AVAILABLE (idempotência entre execuções).
                if not ws.cell(row_idx, 14).value:
                    ws.cell(row_idx, 14).value = ws.cell(row_idx, 5).value
                ws.cell(row_idx, 5).value = _consumed_display
            continue

        ws.cell(row_idx, 2).value = item.get(
            "status",
            ws.cell(row_idx, 2).value,
        )

    if _standalone:
        save_database(wb)


def _ensure_db_unit_consistency(wb):
    """
    Converte itens EXISTENTES no banco para current_unit ANTES de append_stock
    adicionar novos itens (que já vêm em current_unit).

    Problema corrigido: quando o banco foi criado em modo METRIC ("Comprimento (m)")
    e o primeiro pipeline IMPERIAL roda, append_stock grava comprimentos em ft.
    Depois, rebuild_stock_rows_from_current_template vê o header "m", interpreta os
    comprimentos como metros e converte para ft novamente (dupla conversão).

    Solução: converter e atualizar o header ANTES do append, para que rebuild veja
    stored_unit == current_unit e não aplique nenhuma conversão.

    Guard bool(STOCK_COST_RULES): idêntico ao guard de rebuild — garante que testes
    com regras vazias não disparem conversão acidental.
    """
    if not bool(STOCK_COST_RULES):
        return

    if "STOCK" not in wb.sheetnames:
        return

    ws = wb["STOCK"]
    stored_unit = _parse_stored_unit(ws, 5)
    current_unit = get_length_unit_label()

    if stored_unit is None or stored_unit == current_unit:
        return

    for row_idx in range(5, ws.max_row + 1):
        if not ws.cell(row_idx, 1).value:
            continue

        status = str(ws.cell(row_idx, 2).value or "").strip().upper()
        is_used = status == "USED"

        if is_used:
            # col14: backup do comprimento original (HOTFIX-011) — converter unidade
            _col14 = ws.cell(row_idx, 14).value
            if _col14 is not None:
                if is_metric_system():
                    ws.cell(row_idx, 14).value = display_length_to_metric_m(_col14, stored_unit)
                else:
                    ws.cell(row_idx, 14).value = display_length_to_imperial_ft(_col14, stored_unit)
        else:
            length = ws.cell(row_idx, 5).value
            if is_metric_system():
                ws.cell(row_idx, 5).value = display_length_to_metric_m(length, stored_unit)
            else:
                ws.cell(row_idx, 5).value = display_length_to_imperial_ft(length, stored_unit)

    # Atualiza o header para current_unit para que rebuild não converta novamente
    ws.cell(4, 5).value = stock_length_column_header("Comprimento")


def commit_database_writes(stock_items, scrap_items, history_items, updated_stock):
    """
    Consolida as quatro operações de escrita em um único load + save.

    Substitui a sequência:
        append_stock(...)      → load + save
        append_scrap(...)      → load + save
        append_history(...)    → load + save
        update_stock_status(…) → load + save

    Por um único ciclo:
        load → append_stock → append_scrap → append_history
             → update_stock_status → save

    Ganho: de 4 round-trips de I/O (cada um com apply_global_sheet_format
    + apply_database_number_formats sobre toda a planilha) para 1.
    """
    ensure_database_exists()

    # CORRECAO CROSS-011 (AUD-007): lock de escrita inter-processo.
    # Garante que nenhum outro processo Python possa ler/escrever o banco
    # durante este ciclo atomic de load + modificacoes + save.
    with _acquire_write_lock():
        wb = _load_database_safe()

        # Converter itens existentes para current_unit ANTES do append,
        # evitando dupla conversão em rebuild_stock_rows_from_current_template.
        _ensure_db_unit_consistency(wb)

        append_stock(stock_items, _wb=wb)
        append_scrap(scrap_items, _wb=wb)
        append_history(history_items, _wb=wb)
        update_stock_status(updated_stock, _wb=wb)

        save_database(wb)


# python3 run_project.py
