"""
LLENA_QUEUE_SALDOS_DISPONIBLES_FACTURA
For each entry in ``to_discharge`` (QueADescargar), validates the source
import invoice/line and computes the net available balance from
``a24.balance_movement`` using the PEPS ledger.

New logic vs. legacy
--------------------
The legacy Clarion routine read ``CantImpo - CantRetornadaTemp - CantRetornada``
directly from the import line record.  Since we migrated to an append-only
ledger (a24.balance_movement), the available balance is now computed as:

    SUM(signed_qty) per import_item_line_id

where sign = +1 for positive movement types and -1 for negative ones
(see NEGATIVE_MOVEMENTS set in the BalanceMovement model).

Validations preserved from legacy
----------------------------------
1. Import invoice must exist.
2. Import invoice must be processed (status != 'NA' / not 'unprocessed').
3. Import invoice date must not be later than the export invoice date.
4. Import line must exist.
5. Net available balance must be > 0 (otherwise the lot is skipped).

Skipped items (equivalent to legacy CYCLE)
------------------------------------------
- Entries already seen in the same call (duplicate import_invoice + import_line
  combination) — handled naturally since each entry is unique in QueADescargar.
- Lots with net balance <= 0.

Output
------
On success, ``entry.available_lots`` is populated with one ``AvailableLot``
per lot that has available balance.  Errors are added to ``errors``.
"""

import datetime
from decimal import Decimal
from typing import List, Optional

from sqlalchemy import case, func, select
from sqlalchemy.orm import Session

from api.v1.modules.a76.invoices.models import InvoiceHeader, InvoiceStatus
from api.v1.modules.a76.items.models import LineItem
from api.v1.modules.a24.balance_movements.models import BalanceMovement, NEGATIVE_MOVEMENTS
from core.exceptions import ErrorCollector
from .discharge_types import AvailableLot, DownloadEntry


# ---------------------------------------------------------------------------
# Private helpers
# ---------------------------------------------------------------------------

def _fetch_import_invoice(
    db: Session,
    invoice_number: str,
    export_invoice: InvoiceHeader,
) -> Optional[InvoiceHeader]:
    return (
        db.query(InvoiceHeader)
        .filter(
            InvoiceHeader.invoice_number == invoice_number,
            InvoiceHeader.tenant_id == export_invoice.tenant_id,
            InvoiceHeader.company_id == export_invoice.company_id,
        )
        .first()
    )


def _fetch_import_line(
    db: Session,
    invoice_id: int,
    line_number: int,
    tenant_id: int,
    company_id: int,
) -> Optional[LineItem]:
    return (
        db.query(LineItem)
        .filter(
            LineItem.invoice_id == invoice_id,
            LineItem.line_number == line_number,
            LineItem.tenant_id == tenant_id,
            LineItem.company_id == company_id,
        )
        .first()
    )


def _net_balance_for_lot(
    db: Session,
    import_item_line_id: int,
    as_of_date: datetime.date,
) -> Decimal:
    """
    Computes the net available balance for one import lot as of ``as_of_date``.

    Equivalent to the legacy two-step calculation:
      1. Base check: CantImpo - CantRetornadaTemp - CantRetornada (general balance)
      2. CALCULA_SALDO_FECHA_EXPO: only count exits with operation_date <= export date

    In the new ledger, ENTRY movements have no operation_date restriction (the
    lot exists from its import date). EXIT movements (CONSUMPTION, WASTE, etc.)
    are only counted if their operation_date <= as_of_date, mirroring the
    Clarion "FechaFactura > EqiFex:FechaFactura → CYCLE" guard.

        balance = SUM(+qty for ENTRY-type movements)
                - SUM( qty for EXIT-type movements WHERE operation_date <= as_of_date)
    """
    sign_expr = case(
        (BalanceMovement.movement_type.in_(NEGATIVE_MOVEMENTS), Decimal(-1)),
        else_=Decimal(1),
    )
    # Positive movements: always count (entries, returns, adjustments in)
    # Negative movements: only count if they occurred on or before the export date
    date_filter = case(
        (
            BalanceMovement.movement_type.in_(NEGATIVE_MOVEMENTS),
            BalanceMovement.operation_date <= as_of_date,
        ),
        else_=True,
    )
    result = db.execute(
        select(func.sum(sign_expr * BalanceMovement.quantity)).where(
            BalanceMovement.import_item_line_id == import_item_line_id,
            date_filter,
        )
    ).scalar()
    return Decimal(str(result or 0))


def _peps_order_for_lot(db: Session, import_item_line_id: int) -> int:
    """Returns the minimum (oldest) order_peps for this lot."""
    result = db.execute(
        select(func.min(BalanceMovement.order_peps)).where(
            BalanceMovement.import_item_line_id == import_item_line_id,
        )
    ).scalar()
    return int(result or 0)


# ---------------------------------------------------------------------------
# Public entry point
# ---------------------------------------------------------------------------

def fill_available_balances(
    db: Session,
    export_invoice: InvoiceHeader,
    to_discharge: List[DownloadEntry],
    errors: ErrorCollector,
) -> None:
    """
    LLENA_QUEUE_SALDOS_DISPONIBLES_FACTURA
    Validates each discharge entry and populates ``entry.available_lots``
    with the net balance available from the PEPS ledger.

    Parameters
    ----------
    db              : active SQLAlchemy session
    export_invoice  : the export invoice being processed
    to_discharge    : list of DownloadEntry objects (QueADescargar)
    errors          : shared error collector
    """
    export_date: datetime.date = (
        export_invoice.invoice_date.date()
        if hasattr(export_invoice.invoice_date, "date")
        else export_invoice.invoice_date
    )

    # Sort mirrors Clarion: Sort(QueADescargar, FacturaImpo, LineaImpo)
    sorted_entries = sorted(
        to_discharge,
        key=lambda e: (e.import_invoice, e.import_line),
    )

    # Track already-resolved (invoice, line) pairs to skip duplicates
    seen: set = set()

    for entry in sorted_entries:
        key = (entry.import_invoice, entry.import_line)
        if key in seen:
            continue
        seen.add(key)

        if not entry.import_invoice or entry.import_line == 0:
            continue

        # ── 1. Validate import invoice ────────────────────────────────────────
        import_invoice = _fetch_import_invoice(db, entry.import_invoice, export_invoice)

        if import_invoice is None:
            errors.add_error(
                field=f"line[{entry.export_line}].import_invoice",
                message=f"La Factura de Importación: '{entry.import_invoice}' no existe.",
                solution=["Seleccionar otra factura de Importación."],
                code="IMPORT_INVOICE_NOT_FOUND",
                value=entry.import_invoice,
            )
            continue

        # Status 'NA' == not processed (Clarion: Estatus = 'NA')
        if import_invoice.status == InvoiceStatus.PENDING:
            errors.add_error(
                field=f"line[{entry.export_line}].import_invoice",
                message=f"La Factura de Importación: '{entry.import_invoice}' está Desactualizada.",
                solution=["Actualizar la factura de Importación."],
                code="IMPORT_INVOICE_UNPROCESSED",
                value=entry.import_invoice,
            )
            continue

        # Import date must not be later than export date
        imp_date: datetime.date = (
            import_invoice.invoice_date.date()
            if hasattr(import_invoice.invoice_date, "date")
            else import_invoice.invoice_date
        )
        if imp_date > export_date:
            errors.add_error(
                field=f"line[{entry.export_line}].import_invoice",
                message=(
                    f"La Factura de Importación: '{entry.import_invoice}' tiene una Fecha Mayor "
                    f"a la Fecha de Descarga."
                ),
                solution=[
                    f"Seleccionar otra factura de Importación con Fecha Anterior al "
                    f"{export_date.strftime('%d/%m/%Y')}."
                ],
                code="IMPORT_INVOICE_DATE_AFTER_EXPORT",
                value={"import_date": str(imp_date), "export_date": str(export_date)},
            )
            continue

        # ── 2. Validate import line ───────────────────────────────────────────
        import_line = _fetch_import_line(
            db,
            import_invoice.id,
            entry.import_line,
            export_invoice.tenant_id,
            export_invoice.company_id,
        )

        if import_line is None:
            errors.add_error(
                field=f"line[{entry.export_line}].import_line",
                message=(
                    f"La Factura de Importación: '{entry.import_invoice}' "
                    f"con Línea: {entry.import_line} no existe."
                ),
                solution=["Seleccionar otra Línea de Importación a Descargar."],
                code="IMPORT_LINE_NOT_FOUND",
                value={"import_invoice": entry.import_invoice, "import_line": entry.import_line},
            )
            continue

        # ── 3. Compute net available balance from ledger (as of export date) ───
        # Equivalent to: CantImpo - CantRetornadaTemp - CantRetornada (general)
        # then CALCULA_SALDO_FECHA_EXPO (only exits on or before export_date).
        available = _net_balance_for_lot(db, import_line.id, export_date)
        if available <= 0:
            # No balance — skip this lot (equivalent to Clarion CYCLE)
            continue

        # ── 4. Build AvailableLot and attach to entry ─────────────────────────
        fin = import_line.financial
        lot = AvailableLot(
            import_item_line_id=import_line.id,
            import_invoice_id=import_invoice.id,
            part_number_id=import_line.part_number_id,
            available_qty=available,
            value_me=Decimal(str(fin.value_usd or 0)) if fin else None,
            value_mn=Decimal(str(fin.value_mxn or 0)) if fin else None,
            order_peps=_peps_order_for_lot(db, import_line.id),
        )
        entry.available_lots.append(lot)
