"""
CSV generation utilities for Saldos Temporales report.

Translated from Clarion routines:
  GENERA_EXCEL_CSV_PEDIMENTO  (range_type = 'pedimento')
  GENERA_EXCEL_CSV_FECHA      (range_type = 'payment_date' | 'invoice_date')
  GENERA_EXCEL_CSV_PARTE      (range_type = 'parts')
  GENERA_EXCEL_CSV_CLASE      (range_type = 'classes')

Real Anexo76 tables (schema a76):
  QEqiMaq / SPartidasImp   → a76.item_lines
  QFacImp                   → a76.invoice_header  + a76.invoice_compliance_mx
  QPedimentos               → a76.pedimentos      + a76.pedimento_dates
  QClaAct                   → a76.classes
  QSeriesImpo               → a76.item_line_series
  sFracciones               → a76.tariff_fractions  (column: umt = UMAbreviacion)
  GTipoCambio               → a76.exchange_rate
  Line quantities           → a76.item_line_quantities
  Line financials           → a76.item_line_financials
  Line customs              → a76.item_line_customs
  Line descriptions         → a76.item_line_descriptions
"""

import csv
import io
import logging
import pytz
from decimal import Decimal, ROUND_HALF_UP, InvalidOperation
from datetime import datetime, date
from typing import Dict, List, Optional

from sqlalchemy import text
from sqlalchemy.orm import Session

from .schemas import SaldosFilter

logger = logging.getLogger(__name__)

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def _d(value, decimals: int = 8) -> Decimal:
    try:
        exp = Decimal(10) ** -decimals
        return Decimal(str(value or 0)).quantize(exp, rounding=ROUND_HALF_UP)
    except (InvalidOperation, TypeError):
        return Decimal(0)


def _fmt_date(val) -> str:
    """Format a date value to MM/DD/YY (Clarion @D06 equivalent)."""
    if val is None:
        return ""
    if isinstance(val, (datetime, date)):
        return val.strftime("%m/%d/%y")
    return str(val)


def _fmt_num(d: Decimal) -> str:
    """Format Decimal without scientific notation and trailing zeros."""
    if d is None:
        return ""
    s = "{:f}".format(d)
    if "." in s:
        s = s.rstrip("0").rstrip(".")
    if s == "-0":
        return "0"
    return s


def _clean(text: str) -> str:
    """Remove newlines/carriage returns (Clarion SACARCOMASENTERS equivalent)."""
    return str(text or "").replace("\n", " ").replace("\r", "").replace(",", " ").strip()


def _subpartida_sql(level: str) -> str:
    # NOTE: is_sub_part column does not exist yet in item_lines — returns empty
    return ""


# ---------------------------------------------------------------------------
# Per-row lookups (per Clarion row-level ACCESS calls)
# ---------------------------------------------------------------------------

def _get_exchange_rate(db: Session, fecha, tenant_id: int, company_id: int) -> Optional[Decimal]:
    """GTipoCambio → a76.exchange_rate"""
    if not fecha:
        return None
    try:
        row = db.execute(
            text(
                "SELECT value FROM a76.exchange_rate "
                "WHERE tenant_id = :tid AND company_id = :cid AND date::date = :fecha "
                "LIMIT 1"
            ),
            {"tid": tenant_id, "cid": company_id, "fecha": fecha},
        ).fetchone()
        if row:
            return _d(row[0])
    except Exception as e:
        logger.warning(f"exchange_rate not found for {fecha}: {e}")
    return None


def _get_series_data(db: Session, line_item_id) -> Dict:
    """
    QSeriesImpo → a76.item_line_series
    Returns: SeriesSolas, ModeloSeries, NumParteSeries, NoId
    """
    out = {"SeriesSolas": None, "ModeloSeries": None, "NumParteSeries": None, "NoId": None}
    if not line_item_id:
        return out
    try:
        rows = db.execute(
            text(
                "SELECT serial_numbers, model, sub_model, number_id "
                "FROM a76.item_line_series "
                "WHERE line_item_id = :lid ORDER BY id"
            ),
            {"lid": line_item_id},
        ).fetchall()
    except Exception:
        return out

    serials, models, numbers_id = [], [], []
    for i, r in enumerate(rows, 1):
        if r[0]:
            serials.append(f"{i}) {r[0]}")
        if r[1]:
            models.append(f"{i}) {r[1]}")
        if r[3]:
            numbers_id.append(f"{i}) {r[3]}")

    out["SeriesSolas"]   = ", ".join(serials) if serials else None
    out["ModeloSeries"]  = ", ".join(models) if models else None
    out["NumParteSeries"] = None   # sub_model used as num-parte-series if relevant
    out["NoId"]          = ", ".join(numbers_id) if numbers_id else None
    return out


def _get_um_tarifa(db: Session, fraccion: str) -> str:
    """
    sFracciones → a76.tariff_fractions
    Clarion: SUB(fraccion,1,8) for code and SUB(fraccion,9,2) for historico
    We match on the first 8 chars of the code.
    """
    if not fraccion:
        return ""
    frac_code = fraccion.replace("'", "")[:8]  # strip leading quote and take 8 chars
    try:
        row = db.execute(
            text(
                "SELECT umt FROM a76.tariff_fractions "
                "WHERE LEFT(code, 8) = :frac LIMIT 1"
            ),
            {"frac": frac_code},
        ).fetchone()
        if row and row[0]:
            return str(row[0])
    except Exception as e:
        logger.debug(f"UMTarifa not found for {frac_code}: {e}")
    return ""


def _get_fraccion_ame(db: Session, class_id) -> str:
    """QClaAct.FraccionAme → a76.classes.us_fraction"""
    if not class_id:
        return ""
    try:
        row = db.execute(
            text("SELECT us_fraction FROM a76.classes WHERE id = :cid LIMIT 1"),
            {"cid": class_id},
        ).fetchone()
        if row and row[0]:
            return str(row[0])
    except Exception:
        pass
    return ""


# ---------------------------------------------------------------------------
# Base SQL (shared across all range types)
# ---------------------------------------------------------------------------

BASE_SELECT = """
    ih.invoice_number                       AS "C1",
    ih.company_id                           AS "company_id",
    CONCAT(ped.year,'-',ped.license,'-',ped.pedimento_number) AS "C2",
    COALESCE(icm.provider_header,'')        AS "C3",
    COALESCE(icm.sold_to_header,'')         AS "C_sold_to",
    il.rectification                        AS "C4",
    icm.pedimento_id                        AS "C5",
    icm.pedimento_r1                        AS "C5R1",
    pd.payment_date                         AS "C7",
    ped.customs_office                      AS "C8",
    pd.start_date                           AS "C9",
    ih.invoice_date                         AS "C11",
    ih.emission_date                        AS "C12_emission",
    cl.id                                   AS "C13_class_id",
    COALESCE(cl.class_code,'')              AS "C13",
    REPLACE(REPLACE(COALESCE(ild.description_spanish,''),CHR(10),''),CHR(13),' ') AS "C14",
    REPLACE(REPLACE(COALESCE(ild.description_english,''),CHR(10),''),CHR(13),' ') AS "C15",
    COALESCE(ilc.origin_country,'')         AS "C16",
    COALESCE(ent.qty_impo, 0)               AS "C17",
    COALESCE(bal.qty_used, 0)               AS "C18",
    COALESCE(uom.code,'')                   AS "C19",
    COALESCE(ent.val_mn_impo, 0)            AS "C20",
    COALESCE(bal.val_mn_used, 0)            AS "C21",
    COALESCE(ent.val_me_impo, 0)            AS "C22",
    COALESCE(bal.val_me_used, 0)            AS "C23",
    COALESCE(ilq.net_weight, 0)             AS "C24",
    COALESCE(ilc.fraction,'')               AS "C26",
    COALESCE(ilc.fraction_type,'')          AS "C27",
    COALESCE(ilc.advalorem,'')              AS "C28",
    COALESCE(ilc.sector,'')                 AS "C29",
    COALESCE(ilq.net_weight, 0)             AS "C30",
    COALESCE(ild.brand,'')                  AS "C32",
    COALESCE(ild.model,'')                  AS "C33",
    il.id                                   AS "C34",
    il.line_number                          AS "C35",
    COALESCE(p.part_number,'')              AS "C36",
    0                                       AS "C37",
    COALESCE(il.location,'')                AS "C38",
    ''                                      AS "C39",
    COALESCE(icm.edocument,'')              AS "C40",
    COALESCE(icm.vucem_operation_num,'')    AS "C41",
    COALESCE(cl.material_key,'')            AS "C42",
    CONCAT(ped.year,'-',ped.customs_office,'-',ped.license,'-',ped.pedimento_number) AS "C43",
    COALESCE(ptc.payment_date_code, 'P')    AS "C44",
    COALESCE(lm.last_movement_type, '')     AS "C_last_movement_type",
    COALESCE(ilc.octave_fraction,'')        AS "C45",
    ''                                      AS "C47",
    COALESCE(ped.pedimento_code,'')         AS "C48",
    COALESCE(ilc.rate,'')                   AS "C49",
    COALESCE(il.iv32_type_key,'')           AS "C50",
    COALESCE(il.guide_number,'')            AS "C_embarque",
    COALESCE(c_proj.name, '')               AS "C_proyecto",
    COALESCE(bal.qty_balance, 0)            AS "C_balance"
"""

BASE_JOINS = """
    JOIN   a76.invoice_header          ih   ON ih.id = il.invoice_id
    LEFT JOIN a76.company              c_proj ON c_proj.id = ih.company_id
    JOIN   a76.invoice_compliance_mx   icm  ON icm.invoice_id = ih.id
    LEFT JOIN a76.pedimentos           ped  ON ped.id = icm.pedimento_id
    LEFT JOIN a76.pedimento_dates      pd   ON pd.pedimento_id = ped.id
    LEFT JOIN a76.pedimento_transport_means ptm ON ptm.pedimento_id = ped.id
    LEFT JOIN public.pedimento_transport_catalog ptc ON ptc.code = ptm.entry_exit
    LEFT JOIN a76.classes              cl   ON cl.id = il.class_id
    LEFT JOIN a76.item_line_quantities ilq  ON ilq.item_line_id = il.id
    LEFT JOIN a76.item_line_financials ilf  ON ilf.item_line_id = il.id
    LEFT JOIN a76.item_line_customs    ilc  ON ilc.item_line_id = il.id
    LEFT JOIN a76.item_line_descriptions ild ON ild.item_line_id = il.id
    LEFT JOIN a76.parts                p   ON p.id = il.part_number_id
    LEFT JOIN a76.units_of_measure     uom ON uom.id = il.unit_of_measure
    LEFT JOIN (
        SELECT DISTINCT ON (import_item_line_id)
            import_item_line_id,
            movement_type AS last_movement_type
        FROM a24.balance_movement
        WHERE tenant_id = :tenant_id
        ORDER BY import_item_line_id, id DESC
    ) lm ON lm.import_item_line_id = il.id
    JOIN (
        SELECT
            import_item_line_id,
            SUM(
                CASE
                    WHEN movement_type = 'entry' THEN quantity
                    WHEN movement_type = 'entry_void' THEN -1 * quantity
                    ELSE 0
                END
            ) AS qty_impo,
            SUM(
                CASE
                    WHEN movement_type = 'entry' THEN COALESCE(value_me, 0)
                    WHEN movement_type = 'entry_void' THEN -1 * COALESCE(value_me, 0)
                    ELSE 0
                END
            ) AS val_me_impo,
            SUM(
                CASE
                    WHEN movement_type = 'entry' THEN COALESCE(value_mn, 0)
                    WHEN movement_type = 'entry_void' THEN -1 * COALESCE(value_mn, 0)
                    ELSE 0
                END
            ) AS val_mn_impo
        FROM a24.balance_movement
        WHERE tenant_id = :tenant_id
        GROUP BY import_item_line_id
    ) ent ON ent.import_item_line_id = il.id
    LEFT JOIN (
        SELECT 
            import_item_line_id,
            SUM(
                CASE
                    WHEN movement_type IN ('consumption', 'waste', 'scrap', 'destruction') THEN quantity
                    WHEN movement_type = 'return' THEN -1 * quantity
                    ELSE 0
                END
            ) as qty_used,
            SUM(
                CASE
                    WHEN movement_type IN ('consumption', 'waste', 'scrap', 'destruction') THEN COALESCE(value_me, 0)
                    WHEN movement_type = 'return' THEN -1 * COALESCE(value_me, 0)
                    ELSE 0
                END
            ) as val_me_used,
            SUM(
                CASE
                    WHEN movement_type IN ('consumption', 'waste', 'scrap', 'destruction') THEN COALESCE(value_mn, 0)
                    WHEN movement_type = 'return' THEN -1 * COALESCE(value_mn, 0)
                    ELSE 0
                END
            ) as val_mn_used,
            SUM(CASE WHEN movement_type IN ('consumption', 'waste', 'scrap', 'destruction', 'neg_adj', 'transfer_out', 'expiration', 'regime_chg_out', 'entry_void') 
                     THEN -1 * quantity ELSE quantity END) as qty_balance
        FROM a24.balance_movement
        WHERE tenant_id = :tenant_id
        GROUP BY import_item_line_id
    ) bal ON bal.import_item_line_id = il.id
"""

# ---------------------------------------------------------------------------
# 5 query builders
# ---------------------------------------------------------------------------

def _get_base_query_filters(filters: SaldosFilter) -> tuple:
    """Helper to return base where-clause and parameters for company/tenant."""
    company_filter = ""
    params: dict = {"tenant_id": filters.tenant_id}
    
    # If Shelter option is true, ignore company_id to fetch all projects in the tenant.
    if not filters.shelter:
        company_filter = "AND ih.company_id = :company_id"
        params["company_id"] = filters.company_id
        
    return company_filter, params

def _query_ped(filters: SaldosFilter) -> tuple:
    level_filter = _subpartida_sql(filters.level)
    date_filter = ""
    company_filter, params = _get_base_query_filters(filters)
    
    if filters.start_date and filters.end_date:
        s, e = filters.start_date, filters.end_date
        if s > e:
            s, e = e, s
        date_filter = "AND CONCAT(ped.year,'-',ped.license,'-',ped.pedimento_number) BETWEEN :start AND :end"
        params["start"] = s
        params["end"] = e
    sql = f"""
        SELECT {BASE_SELECT}
        FROM a76.item_lines il
        {BASE_JOINS}
        WHERE ih.tenant_id = :tenant_id
          AND ih.operation_type = 'imp'
          AND ent.qty_impo > 0
          {company_filter}
          {date_filter}
          {level_filter}
        ORDER BY CONCAT(ped.year,ped.customs_office,ped.license,ped.pedimento_number),
                 ih.invoice_date
    """
    return sql, params


def _query_fpp(filters: SaldosFilter) -> tuple:
    level_filter = _subpartida_sql(filters.level)
    date_filter = ""
    company_filter, params = _get_base_query_filters(filters)
    
    if filters.start_date and filters.end_date:
        date_filter = "AND pd.payment_date BETWEEN :start AND :end"
        params["start"] = filters.start_date
        params["end"] = filters.end_date
    sql = f"""
        SELECT {BASE_SELECT}
        FROM a76.item_lines il
        {BASE_JOINS}
        WHERE ih.tenant_id = :tenant_id
          AND ih.operation_type = 'imp'
          AND ent.qty_impo > 0
          {company_filter}
          {date_filter}
          {level_filter}
        ORDER BY ih.invoice_date
    """
    return sql, params


def _query_ffa(filters: SaldosFilter) -> tuple:
    level_filter = _subpartida_sql(filters.level)
    date_filter = ""
    company_filter, params = _get_base_query_filters(filters)
    
    if filters.start_date and filters.end_date:
        date_filter = "AND ih.invoice_date BETWEEN :start AND :end"
        params["start"] = filters.start_date
        params["end"] = filters.end_date
    sql = f"""
        SELECT {BASE_SELECT}
        FROM a76.item_lines il
        {BASE_JOINS}
        WHERE ih.tenant_id = :tenant_id
          AND ih.operation_type = 'imp'
          AND ent.qty_impo > 0
          {company_filter}
          {date_filter}
          {level_filter}
        ORDER BY ih.invoice_date
    """
    return sql, params


def _query_par(filters: SaldosFilter) -> tuple:
    level_filter = _subpartida_sql(filters.level)
    date_filter = ""
    id_filter = ""
    company_filter, params = _get_base_query_filters(filters)
    
    if filters.start_date and filters.end_date:
        s, e = filters.start_date, filters.end_date
        if s > e:
            s, e = e, s
        id_filter = "AND p.part_number BETWEEN :start AND :end"
        params["start"] = s
        params["end"] = e
    if filters.date_start and filters.date_end:
        date_filter = "AND ih.invoice_date BETWEEN :date_start AND :date_end"
        params["date_start"] = filters.date_start
        params["date_end"] = filters.date_end
    sql = f"""
        SELECT {BASE_SELECT}
        FROM a76.item_lines il
        {BASE_JOINS}
        WHERE ih.tenant_id = :tenant_id
          AND ih.operation_type = 'imp'
          AND ent.qty_impo > 0
          {company_filter}
          {id_filter}
          {date_filter}
          {level_filter}
        ORDER BY p.part_number, ih.invoice_date
    """
    return sql, params


def _query_cla(filters: SaldosFilter) -> tuple:
    level_filter = _subpartida_sql(filters.level)
    date_filter = ""
    id_filter = ""
    company_filter, params = _get_base_query_filters(filters)
    
    if filters.start_date and filters.end_date:
        s, e = filters.start_date, filters.end_date
        if s > e:
            s, e = e, s
        id_filter = "AND cl.class_code BETWEEN :start AND :end"
        params["start"] = s
        params["end"] = e
    if filters.date_start and filters.date_end:
        date_filter = "AND ih.invoice_date BETWEEN :date_start AND :date_end"
        params["date_start"] = filters.date_start
        params["date_end"] = filters.date_end
    sql = f"""
        SELECT {BASE_SELECT}
        FROM a76.item_lines il
        {BASE_JOINS}
        WHERE ih.tenant_id = :tenant_id
          AND ih.operation_type = 'imp'
          AND ent.qty_impo > 0
          {company_filter}
          {id_filter}
          {date_filter}
          {level_filter}
        ORDER BY cl.class_code, ih.invoice_date
    """
    return sql, params


# ---------------------------------------------------------------------------
# Row-level business logic (Clarion _build_row equivalent)
# ---------------------------------------------------------------------------

def _fraccion(row: Dict, include_regla_octava: bool) -> str:
    c26 = str(row.get("C26") or "")
    c45 = str(row.get("C45") or "")
    target = c45 if (include_regla_octava and c45) else c26
    if target and target[0] == "0":
        return "'" + target
    return target


def _build_row(
    row: Dict,
    db: Session,
    filters: SaldosFilter,
    *,
    num_parte_field: str = "C36",
) -> Optional[Dict]:
    """
    Apply business logic and enrich with per-row DB lookups.
    Returns None if the row should be filtered out.
    """
    use_mn = filters.currency == "national"
    use_fp = filters.exchange_rate == "payment_date"

    # CANTIDADES
    cant_orig  = _d(row.get("C17"))
    cant_used  = _d(row.get("C18"))
    cant_saldo = _d(row.get("C_balance"))

    # Mostrar saldo 0, excepto lotes anulados (último movimiento ENTRY_VOID).
    if str(row.get("C_last_movement_type") or "").lower() == "entry_void":
        return None

    if filters.omit_low_balance and cant_saldo <= Decimal(0):
        return None

    # PESO
    peso_neto  = _d(row.get("C30"))
    peso_usado = (cant_used * peso_neto / cant_orig) if cant_orig != 0 else Decimal(0)
    peso_saldo = peso_neto - peso_usado

    # TIPO DE CAMBIO:
    # C44 now carries payment_date_code from pedimento_transport_catalog:
    # E => Fecha_Inicio, P => Fecha_Pago.
    # If explicitly "invoice_date", we use invoice_date (C11) instead.
    tc = Decimal(1)
    fecha_pago   = row.get("C7")
    fecha_inicio = row.get("C9")
    fecha_factura= row.get("C11")
    payment_date_code = str(row.get("C44") or "").upper()

    tc_fecha_display = None
    # TIPO DE CAMBIO
    tc_fecha = None
    if use_fp:
        tc_fecha = fecha_inicio if payment_date_code == "E" else fecha_pago
    else:
        tc_fecha = fecha_factura

    if tc_fecha:
        target_company_id = row.get("company_id") or filters.company_id
        tc_val = _get_exchange_rate(db, tc_fecha, filters.tenant_id, target_company_id)
        if tc_val:
            tc = tc_val
        elif filters.shelter:
             # In Shelter mode, missing exchange rate skips the row (enforce strict data)
             return None

    # VALOR ORIGINAL
    if use_mn:
        valor_orig = _d(row.get("C22")) * tc if use_fp and fecha_pago else _d(row.get("C20"))
    else:
        valor_orig = _d(row.get("C22"))

    # VALOR USADO
    if cant_orig != 0:
        if use_mn:
            if use_fp and fecha_pago:
                valor_usado = cant_used * _d(row.get("C22")) * tc / cant_orig
            else:
                valor_usado = cant_used * _d(row.get("C20")) / cant_orig
        else:
            valor_usado = cant_used * _d(row.get("C22")) / cant_orig
    else:
        valor_usado = Decimal(0)

    # VALOR SALDO — SubPartida logic
    es_subpartida = str(row.get("C39") or "")
    contiene_subp = str(row.get("C47") or "")

    if es_subpartida == "S":
        valor_saldo = (
            _d(row.get("C20")) - _d(row.get("C21"))
            if use_mn else
            _d(row.get("C22")) - _d(row.get("C23"))
        )
    elif es_subpartida == "P" and contiene_subp == "S":
        valor_saldo = (
            valor_orig - _d(row.get("C21"))
            if use_mn else
            valor_orig - _d(row.get("C23"))
        )
    else:
        valor_saldo = valor_orig - valor_usado

    # BALANCE TYPE filter
    is_repair = row.get("C4")
    if filters.balance_type == "normal" and is_repair:
        return None
    if filters.balance_type == "repair" and not is_repair:
        return None

    # FRACCION ARANCELARIA
    fraccion = _fraccion(row, filters.include_regla_octava)

    # NUM PARTE display
    if num_parte_field == "C13" or filters.print_class:
        num_parte_display = str(row.get("C13") or "")
    else:
        num_parte_display = str(row.get("C36") or "")

    # Series (per-row DB lookup)
    series_data = _get_series_data(db, row.get("C34"))

    # UMTarifa (Clarion: query sFracciones with fraccion)
    um_tarifa = _get_um_tarifa(db, fraccion)

    # FraccionAme (Clarion: ACCESS:QClaAct → EqiCla:FraccionAme)
    fraccion_ame = _get_fraccion_ame(db, row.get("C13_class_id"))

    # TipoCambio value to embed in CSV
    tc_value = tc if tc != Decimal(1) else (tc if _get_exchange_rate(db, tc_fecha, filters.tenant_id, target_company_id) else None)
    tipo_cambio_str = str(tc_value) if tc_value else ""

    # Pedimento R1 logic (Clarion: some companies invert ped / pedR1)
    ped_impo  = str(row.get("C2") or "")
    ped_r1    = str(row.get("C5R1") or "")

    # Fechas formateadas
    fecha_pago_fmt    = _fmt_date(fecha_pago)
    fecha_entrada_fmt = _fmt_date(row.get("C11"))
    fecha_emision_fmt = _fmt_date(row.get("C12_emission"))

    # Aduana cruce (first 2-3 chars per Clarion)
    aduana = str(row.get("C8") or "")

    return {
        "TipoMovimiento":     "Importación Temporal:",
        "Pedimento":          ped_impo,
        "PedimentoR1":        ped_r1,
        "ClavePed":           str(row.get("C48") or ""),   # pedimento_code = Clave
        "FechaPago":          fecha_pago_fmt,
        "FechaEntrada":       fecha_entrada_fmt,
        "FechaEmision":       fecha_emision_fmt,
        "Factura":            "'" + str(row.get("C1") or ""),  # leading ' for Excel
        "Linea":              str(row.get("C35") or ""),
        "Tipo":               "P" if es_subpartida == "P" else ("S" if es_subpartida == "S" else ""),
        "NumParteClase":      num_parte_display,
        "NumParteFijo":       str(row.get("C36") or ""),
        "DescripcionEsp":     _clean(row.get("C14") or ""),
        "DescripcionIng":     _clean(row.get("C15") or ""),
        "CantidadOriginal":   _fmt_num(cant_orig),
        "UM":                 str(row.get("C19") or ""),
        "PesoNeto":           _fmt_num(peso_neto),
        "ValorOriginal":      _fmt_num(valor_orig),
        "CantidadUsada":      _fmt_num(cant_used),
        "PesoUsado":          _fmt_num(peso_usado),
        "ValorUsado":         _fmt_num(valor_usado),
        "CantidadSaldo":      _fmt_num(cant_saldo),
        "PesoSaldo":          _fmt_num(peso_saldo),
        "ValorSaldo":         _fmt_num(valor_saldo),
        "FraccionImpo":       fraccion,
        "Preferencia":        str(row.get("C27") or ""),
        "PaisOrigen":         str(row.get("C16") or ""),
        "Sector":             str(row.get("C29") or ""),
        "Marca":              str(row.get("C32") or ""),
        "Modelo":             str(row.get("C33") or ""),
        "SeriesSolas":        series_data["SeriesSolas"],
        "Assets":             "",   # QAssetTag — pending mapping
        "EDocument":          str(row.get("C40") or ""),
        "NumOperacionVU":     str(row.get("C41") or ""),
        "TipoMaqEquipo":      str(row.get("C42") or ""),
        "UbicacionMaq":       str(row.get("C38") or ""),
        "ModeloSeries":       series_data["ModeloSeries"],
        "NumParteSeries":     series_data["NumParteSeries"],
        "NoId":               series_data["NoId"],
        "Pedimento18":        str(row.get("C43") or ""),
        "AduanaCruce":        "'" + aduana,
        "NumEmbarque":        str(row.get("C_embarque") or ""),
        "UMTarifa":           um_tarifa,
        "IdType":             str(row.get("C50") or ""),
        "Secuencia":          "",   # not in Anexo76 models, leave blank
        "FraccionAmericana":  fraccion_ame,
        "Proyecto":           str(row.get("C_proyecto") or "") if filters.shelter else "",
        "TipoCambio":         tipo_cambio_str,
        # Internal only (not written to CSV, used for ordering)
        "_FechaFacturaRaw":   row.get("C11"),
        "_PedimentoRaw":      ped_impo,
        "_NumParteRaw":       str(row.get("C36") or ""),
        "_ClaseRaw":          str(row.get("C13") or ""),
    }


# ---------------------------------------------------------------------------
# CSV header definitions per range type (mirrors Clarion GTxt:Linea headers)
# ---------------------------------------------------------------------------

# Common columns (all range types end with these)
_COMMON_END = [
    "FraccionImpo", "Preferencia", "PaisOrigen", "Sector",
    "Marca", "Modelo", "SeriesSolas", "Assets",
    "EDocument", "NumOperacionVU", "TipoMaqEquipo",
    "UbicacionMaq", "ModeloSeries", "NumParteSeries", "NoId",
    "Pedimento18", "AduanaCruce", "NumEmbarque",
    "UMTarifa", "IdType", "Secuencia", "FraccionAmericana",
    "Proyecto", "TipoCambio",
]

# Column list per range type
COLUMNS_PED = [
    "TipoMovimiento", "Pedimento", "ClavePed", "FechaPago", "PedimentoR1",
    "NumParteClase", "NumParteFijo", "DescripcionEsp", "DescripcionIng",
    "FechaEntrada", "FechaEmision", "Factura", "Linea", "Tipo",
    "CantidadOriginal", "UM", "PesoNeto", "ValorOriginal",
    "CantidadUsada", "PesoUsado", "ValorUsado",
    "CantidadSaldo", "PesoSaldo", "ValorSaldo",
] + _COMMON_END

COLUMNS_FECHA = [
    "TipoMovimiento", "FechaEntrada", "Pedimento", "FechaPago", "ClavePed", "PedimentoR1",
    "NumParteClase", "NumParteFijo", "DescripcionEsp", "DescripcionIng",
    "Factura", "FechaEmision", "Linea", "Tipo",
    "CantidadOriginal", "UM", "PesoNeto", "ValorOriginal",
    "CantidadUsada", "PesoUsado", "ValorUsado",
    "CantidadSaldo", "PesoSaldo", "ValorSaldo",
] + _COMMON_END

COLUMNS_PAR = [
    "TipoMovimiento", "NumParteClase", "NumParteFijo", "DescripcionEsp", "DescripcionIng",
    "FechaEntrada", "FechaEmision", "Factura", "Linea", "Tipo",
    "Pedimento", "ClavePed", "FechaPago", "PedimentoR1",
    "CantidadOriginal", "UM", "PesoNeto", "ValorOriginal",
    "CantidadUsada", "PesoUsado", "ValorUsado",
    "CantidadSaldo", "PesoSaldo", "ValorSaldo",
] + _COMMON_END

COLUMNS_CLA = [
    "TipoMovimiento", "NumParteClase", "NumParteFijo", "DescripcionEsp", "DescripcionIng",
    "FechaEntrada", "FechaEmision", "Factura", "Linea", "Tipo",
    "Pedimento", "ClavePed", "FechaPago", "PedimentoR1",
    "CantidadOriginal", "UM", "PesoNeto", "ValorOriginal",
    "CantidadUsada", "PesoUsado", "ValorUsado",
    "CantidadSaldo", "PesoSaldo", "ValorSaldo",
] + _COMMON_END

_HEADER_LABELS: Dict[str, str] = {
    "TipoMovimiento":   "Tipo Movimiento",
    "Pedimento":        "Pedimento",
    "PedimentoR1":      "Pedimento Rectificacion",
    "ClavePed":         "Clave",
    "FechaPago":        "Fecha Pago",
    "FechaEntrada":     "Fecha Entrada",
    "FechaEmision":     "Fecha de Emisión",
    "Factura":          "Factura",
    "Linea":            "Línea",
    "Tipo":             "Tipo",
    "NumParteClase":    "Num. Parte/Clase",
    "NumParteFijo":     "Num.Parte",
    "DescripcionEsp":   "Descripción Español",
    "DescripcionIng":   "Descripción Inglés",
    "CantidadOriginal": "Cantidad Orig.",
    "UM":               "U.M.",
    "PesoNeto":         "Peso Orig.",
    "ValorOriginal":    "Valor Orig.",
    "CantidadUsada":    "Cantidad Usada",
    "PesoUsado":        "Peso Usado",
    "ValorUsado":       "Valor Usado",
    "CantidadSaldo":    "Cantidad Saldo",
    "PesoSaldo":        "Peso Saldo",
    "ValorSaldo":       "Valor Saldo",
    "FraccionImpo":     "Fracción Arancelaria",
    "Preferencia":      "Preferencia",
    "PaisOrigen":       "País",
    "Sector":           "Sector",
    "Marca":            "Marca",
    "Modelo":           "Modelo",
    "SeriesSolas":      "Series",
    "Assets":           "Asset Tags",
    "EDocument":        "E-Document",
    "NumOperacionVU":   "Núm. Operación",
    "TipoMaqEquipo":    "Tipo de Activo Fijo",
    "UbicacionMaq":     "Ubicacion",
    "ModeloSeries":     "Modelo",
    "NumParteSeries":   "Num. Parte Serie",
    "NoId":             "Num ID",
    "Pedimento18":      "Pedimento 18",
    "AduanaCruce":      "Aduana Cruce",
    "NumEmbarque":      "Advalorem",      # Clarion col position: NumEmbarque after AduanaCruce
    "UMTarifa":         "U.M. Tarifa",
    "IdType":           "ID TYPE",
    "Secuencia":        "Secuencia",
    "FraccionAmericana": "Fracción Americana",
    "Proyecto":         "",   # conditional (Shelter)
    "TipoCambio":       "Tipo de Cambio",
}

_COLUMNS_BY_RANGE = {
    "pedimento":    COLUMNS_PED,
    "payment_date": COLUMNS_FECHA,
    "invoice_date": COLUMNS_FECHA,
    "parts":        COLUMNS_PAR,
    "classes":      COLUMNS_CLA,
}

# ---------------------------------------------------------------------------
# Main fetch
# ---------------------------------------------------------------------------

def fetch_saldos_data(db: Session, filters: SaldosFilter) -> List[Dict]:
    range_map = {
        "pedimento":    _query_ped,
        "payment_date": _query_fpp,
        "invoice_date": _query_ffa,
        "parts":        _query_par,
        "classes":      _query_cla,
    }

    builder = range_map.get(filters.range_type, _query_ped)
    sql, params = builder(filters)

    if filters.asset_type:
        sql = sql.replace(
            "ORDER BY",
            "AND cl.material_key = :asset_type\n        ORDER BY",
        )
        params["asset_type"] = filters.asset_type

    try:
        result   = db.execute(text(sql), params)
        raw_rows = [dict(r._mapping) for r in result]
    except Exception as e:
        logger.error(f"fetch_saldos_data SQL error [{filters.range_type}]: {e}", exc_info=True)
        return []

    num_parte_field = "C13" if filters.range_type == "classes" else "C36"

    processed: List[Dict] = []
    for raw in raw_rows:
        # In-memory filters (Clarion CYCLE)
        if filters.buyer and str(raw.get("C_sold_to") or "") != filters.buyer:
            continue
        if filters.provider and str(raw.get("C3") or "") != filters.provider:
            continue
        if filters.location and str(raw.get("C38") or "") != filters.location:
            continue
        if filters.asset_class and filters.range_type != "classes":
            if str(raw.get("C13") or "") != filters.asset_class:
                continue
        if filters.pedimento_code and str(raw.get("C48") or "") != filters.pedimento_code:
            continue

        row_data = _build_row(raw, db, filters, num_parte_field=num_parte_field)
        if row_data is not None:
            processed.append(row_data)

    logger.info(f"fetch_saldos_data: {len(processed)} rows (range_type={filters.range_type})")
    return processed


# ---------------------------------------------------------------------------
# CSV builder
# ---------------------------------------------------------------------------

def generate_saldos_csv(filters: SaldosFilter, db: Optional[Session] = None) -> str:
    """Build the CSV string matching Clarion's GENERA_EXCEL_CSV_* output."""
    output = io.StringIO()
    writer = csv.writer(output)

    columns = _COLUMNS_BY_RANGE.get(filters.range_type, COLUMNS_PED)
    headers = [_HEADER_LABELS.get(c, c) for c in columns]
    if filters.shelter:
        # Enforce header label if Shelter is active
        for i, col in enumerate(columns):
            if col == "Proyecto":
                headers[i] = "Proyecto"

    # Dynamically build legacy title
    range_map_title = {
        "pedimento": "Por RANGO de PEDIMENTO",
        "payment_date": "Por RANGO de FECHA PAGO",
        "invoice_date": "Por RANGO de FECHA FAC.",
        "parts": "Por PARTE",
        "classes": "Por CLASE",
    }
    rango_str = range_map_title.get(filters.range_type, "")

    report_type_map = {
        "normal": "Normal",
        "detailed": "Detallado",
        "with_download": "Con Descargas",
    }
    tipo_reporte_str = report_type_map.get(filters.report_type, "Normal")
    agrupacion_str = "Por CLASE" if filters.print_class else "Por PARTE"
    
    title_row = f"REPORTE DE SALDOS DE ACTIVO FIJO {rango_str}, {tipo_reporte_str} {agrupacion_str}"

    # Fetch company profile if DB available
    c_name = ""
    c_rfc = ""
    c_immex = ""
    main_addr_str1 = ""
    main_addr_str2 = ""
    main_addr_str3 = ""
    ind_addr_str1 = ""
    ind_addr_str2 = ""
    ind_addr_str3 = ""

    if db is not None:
        try:
            from api.v1.modules.a76.general_catalogs.company.models import Company
            
            query = db.query(Company)
            if filters.company_id:
                query = query.filter(Company.id == filters.company_id)
            
            company = query.first()
            if company:
                c_name = company.name or ""
                c_rfc = company.rfc or ""
                # IMMEX often uses both program and program_number
                p_base = company.program or ""
                p_num = company.program_number or ""
                if p_base and p_num:
                    c_immex = f"{p_base}-{p_num}"
                else:
                    c_immex = p_base or p_num or ""
                
                for addr in company.addresses:
                    if addr.address_type == "main":
                        main_addr_str1 = f"Domicilio Fiscal: {addr.street or ''}   Ext. Num: {addr.exterior_number or ''}".strip()
                        main_addr_str2 = f"{addr.neighborhood or ''}   Código Postal: {addr.postal_code or ''}".strip()
                        main_addr_str3 = f"{addr.city or ''} {addr.state or ''}".strip()
                    elif addr.address_type == "industrial":
                        ind_addr_str1 = f"Domicilio Industrial: {addr.street or ''}   Ext. Num: {addr.exterior_number or ''}".strip()
                        ind_addr_str2 = f"{addr.neighborhood or ''}   Código Postal: {addr.postal_code or ''}".strip()
                        ind_addr_str3 = f"{addr.city or ''} {addr.state or ''}".strip()
        except Exception as e:
            logger.warning(f"Could not load company info for headers: {e}")

    # Format Spanish dates correctly (e.g. 5 MAR 2026 Hora Generación: 09:09PM)
    # Use America/Mexico_City timezone to match user's local time (-06:00)
    tz = pytz.timezone('America/Mexico_City')
    now = datetime.now(tz)
    mo_es = {1:"ENE", 2:"FEB", 3:"MAR", 4:"ABR", 5:"MAY", 6:"JUN", 7:"JUL", 8:"AGO", 9:"SEP", 10:"OCT", 11:"NOV", 12:"DIC"}[now.month]
    fecha_gen = f"{now.day} {mo_es} {now.year}"
    hora_gen = now.strftime("%I:%M%p").upper()

    # Write Title block strictly matching Clarion
    writer.writerow([title_row])
    if c_name: writer.writerow([c_name])
    if main_addr_str1: writer.writerow([main_addr_str1])
    if main_addr_str2: writer.writerow([main_addr_str2])
    if main_addr_str3: writer.writerow([main_addr_str3])
    if ind_addr_str1: writer.writerow([ind_addr_str1])
    if ind_addr_str2: writer.writerow([ind_addr_str2])
    if ind_addr_str3: writer.writerow([ind_addr_str3])
    writer.writerow([f"R.F.C: {c_rfc}"])
    writer.writerow([f"IMMEX:  {c_immex}"])
    writer.writerow([f"Fecha Generación:  {fecha_gen} Hora Generación: {hora_gen}"])
    writer.writerow(["PROVEEDOR DE SOFTWARE: ADUANASOFT"])
    writer.writerow([])
    writer.writerow([])

    writer.writerow(headers)
    writer.writerow([])  # blank second header row (Clarion: ConDescarga=0 → blank)

    if db is not None:
        rows = fetch_saldos_data(db, filters)
    else:
        rows = [{c: f"DEMO-{c}" for c in columns}]

    for row in rows:
        writer.writerow([row.get(col, "") for col in columns])

    writer.writerow([])
    return output.getvalue()
