"""
Service for Reporte de Vencimiento CSV generation.

Translated from Clarion LLENA_SALDOS_VENC routine.

Logic:
  - Fetch all temporary import item lines whose pedimento end_date
    is <= baseline + days_ahead AND still have a positive balance.
  - baseline = fecha_corte (if usar_fecha_corte) else today.
  - No lower bound on end_date (mirrors Clarion FechaInicio = 1).
  - Output: CSV with 23 columns matching the legacy EXPORTAR A format.
"""
import csv
import io
import logging
from datetime import date, timedelta
from decimal import Decimal, ROUND_HALF_UP, InvalidOperation
from typing import Optional

from fastapi.responses import StreamingResponse
from sqlalchemy import text
from sqlalchemy.orm import Session

from .schemas import VencimientoFilter

logger = logging.getLogger(__name__)


# ---------------------------------------------------------------------------
# Decimal / format 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, julian: bool = False) -> str:
    """Format date value.
    julian=False → MM/DD/YY  (Clarion @D06)
    julian=True  → raw ISO string as stored in DB
    """
    if val is None:
        return ""
    if julian:
        return str(val)  # ISO: 2025-03-15
    if hasattr(val, "strftime"):
        return val.strftime("%m/%d/%y")
    return str(val)


def _fmt_num(d: Decimal) -> str:
    """Format Decimal as plain fixed-point string (no scientific notation)."""
    if d is None:
        return ""
    # Use fixed-point format to prevent scientific notation for large values
    s = format(d, "f")
    if "." in s:
        s = s.rstrip("0").rstrip(".")
    return "0" if s in ("", "-0") else s


def _text(val) -> str:
    """Force a value to plain text with ' prefix so Excel never coerces it
    to a number (same as Clarion ''''&CLIP(...) pattern)."""
    return "'" + _clean(val)


def _clean(text_val) -> str:
    """Remove commas and newlines (SACARCOMASENTERS equivalent)."""
    return str(text_val or "").replace("\n", " ").replace("\r", "").replace(",", " ").strip()


# ---------------------------------------------------------------------------
# SQL
# ---------------------------------------------------------------------------

_VENCIMIENTO_SQL = """
SELECT
    ih.invoice_number                                               AS "C1",
    ih.company_id                                                   AS "company_id",
    CONCAT(ped.year,'-',ped.license,'-',ped.pedimento_number)       AS "C2",
    COALESCE(
        CONCAT(ped_r1.year,'-',ped_r1.license,'-',ped_r1.pedimento_number),
        ''
    )                                                               AS "C_ped_r1",
    pd.payment_date                                                 AS "C7",
    pd.end_date                                                     AS "C_end_date",
    ih.invoice_date                                                 AS "C11",
    cl.id                                                           AS "C13_class_id",
    COALESCE(cl.us_fraction,'')                                     AS "C_frac_ame",
    REPLACE(REPLACE(COALESCE(ild.description_english,''),CHR(10),''),CHR(13),' ') AS "C15",
    COALESCE(ilc.origin_country,'')                                 AS "C16",
    COALESCE(ilq.quantity, 0)                                       AS "C17",
    COALESCE(bal.qty_used, 0)                                       AS "C18",
    COALESCE(uom.code,'')                                           AS "C19",
    COALESCE(ilf.value_mxn, 0)                                     AS "C20",
    COALESCE(bal.val_mn_used, 0)                                    AS "C21",
    COALESCE(ilf.value_usd, 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 "C_frac_type",
    COALESCE(ilc.sector,'')                                         AS "C_sector",
    COALESCE(p.part_number,'')                                      AS "C36",
    COALESCE(p.commercial_part_number,'')                           AS "C_part_ref",
    icm.sold_to_id                                                  AS "C_sold_to_id"
FROM a76.item_lines il
JOIN   a76.invoice_header          ih   ON ih.id = il.invoice_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.pedimentos           ped_r1 ON ped_r1.id = icm.pedimento_r1
LEFT JOIN a76.pedimento_dates      pd   ON pd.pedimento_id = ped.id
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
        import_item_line_id,
        SUM(CASE WHEN movement_type IN ('consumption','waste','scrap','destruction') THEN quantity
                 WHEN movement_type = 'return' THEN -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 -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 -COALESCE(value_mn, 0)
                 ELSE 0 END)            AS val_mn_used
    FROM a24.balance_movement
    WHERE tenant_id = :tenant_id
      AND (:bal_date_cutoff IS NULL OR operation_date <= :bal_date_cutoff)
    GROUP BY import_item_line_id
) bal ON bal.import_item_line_id = il.id
WHERE ih.tenant_id   = :tenant_id
  AND ih.operation_type = 'imp'
  AND ih.company_id  = :company_id
  AND COALESCE(ilq.quantity, 0) > 0
  AND COALESCE(ilq.quantity, 0) - COALESCE(bal.qty_used, 0) > 0
  AND pd.end_date   <= :date_to
  {client_filter}
  {anexo31_filter}
ORDER BY pd.end_date,
         CONCAT(ped.year, ped.customs_office, ped.license, ped.pedimento_number)
"""

# CSV column headers (exact match to legacy EXPORTAR A)
CSV_COLUMNS = [
    "Num. Factura Impo",
    "Pedimento",
    "Pedimento Rectificado",
    "Fecha Entrada",
    "Fecha Vencimiento",
    "Estatus/Dias",
    "Num. Parte",
    "Descripción",
    "Pais",
    "Cant. Original",
    "Cant. Usada",
    "Cant. Saldo",
    "U.M.",
    "Valor Original",
    "Valor Usado",
    "Valor Saldo",
    "Peso Original",
    "Peso Usado",
    "Peso Saldo",
    "Fraccion",
    "Tipo Fraccion",
    "Sector",
    "Fraccion Americana",
]


# ---------------------------------------------------------------------------
# Company header (IMPRIMIR_EMPRESA equivalent)
# ---------------------------------------------------------------------------

def _fetch_company_header_rows(db: Session, company_id: int) -> list[str]:
    """
    Return a list of strings representing the company header lines.
    All fields are always emitted with their label, even when empty.
    """
    row = db.execute(
        text("""
            SELECT
                c.name, c.rfc, c.program, c.program_number,
                ca.street, ca.exterior_number, ca.neighborhood,
                ca.postal_code, ca.city, ca.state
            FROM a76.company c
            LEFT JOIN a76.company_address ca
                ON ca.company_id = c.id AND ca.address_type = 'main'
            WHERE c.id = :cid
            LIMIT 1
        """),
        {"cid": company_id},
    ).fetchone()

    if not row:
        return []

    name, rfc, program, prog_num, street, ext_num, neighborhood, postal, city, state = row

    def s(v) -> str:
        return str(v).strip() if v else ""

    lines: list[str] = []

    # Line 1: Company name (no label, same as legacy)
    lines.append(s(name))

    # Line 2: Dirección (always emitted)
    dir_line = f"Dirección: {s(street)}"
    if s(ext_num):
        dir_line += f"   Ext. Num: {s(ext_num)}"
    lines.append(dir_line)

    # Line 3: Colonia + Código Postal (always emitted)
    lines.append(f"Colonia: {s(neighborhood)}   Código Postal: {s(postal)}")

    # Line 4: Ciudad + Estado (always emitted)
    ciudad_estado = f"{s(city)} {s(state)}".strip()
    lines.append(f"Ciudad: {ciudad_estado}")

    # Line 5: RFC (always emitted)
    lines.append(f"R.F.C: {s(rfc)}")

    # Line 6: Program (always emitted)
    prog_label = "SICEX" if s(program) == "Maquila" else (s(program) or "IMMEX")
    lines.append(f"{prog_label}: {s(prog_num)}")

    return lines


# ---------------------------------------------------------------------------
# Core CSV generation
# ---------------------------------------------------------------------------

def generate_vencimiento_csv(filters: VencimientoFilter, db: Session) -> bytes:
    """Build the CSV bytes for the Reporte de Vencimiento."""
    today = date.today()

    # Legacy exact behavior:
    #   FechaFinal = Today() + Loc:Dias
    #   IF UsarFechaCorte AND FechaCorte <> '' THEN FechaFinal = FechaCorte
    # → fecha_corte completely replaces today+days_ahead; days_ahead is ignored when usar_fecha_corte=True
    if filters.usar_fecha_corte and filters.fecha_corte:
        try:
            date_to = date.fromisoformat(filters.fecha_corte)
        except ValueError:
            date_to = today + timedelta(days=filters.days_ahead)
    else:
        date_to = today + timedelta(days=filters.days_ahead)

    # Build dynamic filter fragments
    client_filter = ""
    anexo31_filter = ""
    # Legacy: bal date filter only when usar_fecha_corte=1 (count all discharges otherwise)
    bal_date_cutoff = str(date_to) if filters.usar_fecha_corte and filters.fecha_corte else None
    params: dict = {
        "tenant_id": filters.tenant_id,
        "company_id": filters.company_id,
        "date_to": str(date_to),
        "bal_date_cutoff": bal_date_cutoff,
    }

    if filters.client_id:
        client_filter = "AND icm.sold_to_id = :client_id"
        params["client_id"] = filters.client_id

    if filters.conforme_anexo_31:
        # Mirrors legacy: only invoices marked to generate balances (IMMEX conforme)
        anexo31_filter = "AND icm.generate_balances = TRUE"

    sql = _VENCIMIENTO_SQL.format(
        client_filter=client_filter,
        anexo31_filter=anexo31_filter,
    )

    rows = db.execute(text(sql), params).mappings().fetchall()

    # Currency flag: ME = foreign (USD), MN = national (MXP)
    use_me = filters.currency == "foreign"
    min_bal = _d(filters.min_balance)
    julian = filters.julian_date

    output = io.StringIO()
    writer = csv.writer(output, lineterminator="\n")

    # ── Header block (IMPRIMIR_EMPRESA) ──────────────────────────────────────
    writer.writerow(["", "", "", "", "REPORTE DE VENCIMIENTO"])

    company_lines = _fetch_company_header_rows(db, filters.company_id)
    for line in company_lines:
        writer.writerow(["", "", "", "", line])

    writer.writerow(["", ""])            # blank separator
    writer.writerow(CSV_COLUMNS)        # column headers
    writer.writerow(["", ""])            # blank separator after headers

    # ── Data rows ────────────────────────────────────────────────────────────
    for row in rows:
        # -- Quantities --
        cant_orig  = _d(row["C17"])
        cant_used  = _d(row["C18"])
        cant_saldo = cant_orig - cant_used

        # Skip rows with zero or negative balance
        # Legacy: If CantidadOmitir <> 0 Then If CantidadSaldo <= CantidadOmitir Then Cycle
        if cant_saldo <= 0:
            continue
        if min_bal > 0 and cant_saldo <= min_bal:
            continue

        # -- Peso (proportional calculation, mirrors legacy formula) --
        peso_neto = _d(row["C24"])
        if cant_orig != 0:
            peso_usado = (cant_used * peso_neto) / cant_orig
        else:
            peso_usado = Decimal(0)
        peso_saldo = peso_neto - peso_usado

        # -- Valor según moneda (ME = foreign, MN = national) --
        # Legacy (UsarDescargos=1): ValorUsado = (CantUsada x ValorOrig) / CantOrig  (proporcional, igual que peso)
        if use_me:
            valor_orig = _d(row["C22"])
        else:
            valor_orig = _d(row["C20"])
        if cant_orig != 0:
            valor_usado = (cant_used * valor_orig) / cant_orig
        else:
            valor_usado = Decimal(0)
        valor_saldo = valor_orig - valor_usado

        # -- TextoVencimiento (días vs HOY, not baseline) --
        end_date = row.get("C_end_date")
        if end_date is not None:
            if hasattr(end_date, "date"):
                end_date = end_date.date()
            diff = (end_date - today).days
            if diff < 0:
                texto_venc = "VENCIDO"
            elif diff == 0:
                texto_venc = "VENCE HOY"
            else:
                texto_venc = str(diff)
        else:
            texto_venc = ""

        writer.writerow([
            _text(row["C1"]),                          # Num. Factura Impo  (prefijo ' para Excel)
            _text(row["C2"]),                          # Pedimento
            _text(row["C_ped_r1"]),                    # Pedimento Rectificado
            _fmt_date(row["C11"], julian),             # Fecha Entrada
            _fmt_date(row["C_end_date"], julian),      # Fecha Vencimiento
            texto_venc,                                 # Estatus/Dias
            _text(row["C36"]),                         # Num. Parte (prefijo ' para Excel)
            _clean(row["C15"]),                        # Descripción
            _clean(row["C16"]),                        # Pais
            _fmt_num(cant_orig),                       # Cant. Original
            _fmt_num(cant_used),                       # Cant. Usada
            _fmt_num(cant_saldo),                      # Cant. Saldo
            _clean(row["C19"]),                        # U.M.
            _fmt_num(valor_orig),                      # Valor Original
            _fmt_num(valor_usado),                     # Valor Usado
            _fmt_num(valor_saldo),                     # Valor Saldo
            _fmt_num(peso_neto),                       # Peso Original
            _fmt_num(peso_usado),                      # Peso Usado
            _fmt_num(peso_saldo),                      # Peso Saldo
            _text(row["C26"]),                         # Fraccion (código arancelario, forzar texto)
            _clean(row["C_frac_type"]),                # Tipo Fraccion
            _clean(row["C_sector"]),                   # Sector
            _text(row["C_frac_ame"]),                  # Fraccion Americana (código arancelario, forzar texto)
        ])

    return output.getvalue().encode("utf-8-sig")


# ---------------------------------------------------------------------------
# Service class (FastAPI integration)
# ---------------------------------------------------------------------------

class VencimientoReportService:

    def generate_csv_response(
        self, db: Session, filters: VencimientoFilter
    ) -> StreamingResponse:
        today = date.today()

        if filters.usar_fecha_corte and filters.fecha_corte:
            try:
                date_to = date.fromisoformat(filters.fecha_corte)
            except ValueError:
                date_to = today + timedelta(days=filters.days_ahead)
        else:
            date_to = today + timedelta(days=filters.days_ahead)

        filename = f"vencimiento_{today.isoformat()}_{date_to.isoformat()}.csv"

        csv_bytes = generate_vencimiento_csv(filters, db)

        return StreamingResponse(
            iter([csv_bytes]),
            media_type="text/csv",
            headers={"Content-Disposition": f'attachment; filename="{filename}"'},
        )
