"""
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
    Refactorizada para PEPS Flexible:
    Busca automáticamente todos los lotes con saldo disponible para el número de parte 
    de cada partida a descargar.
    """
    export_date: datetime.date = (
        export_invoice.invoice_date.date()
        if hasattr(export_invoice.invoice_date, "date")
        else export_invoice.invoice_date
    )

    for entry in to_discharge:
        # Recuperar la partida de exportación original para obtener IDs precisos
        export_line = db.get(LineItem, entry.line_item_id)
        if not export_line:
            continue

        part_id = export_line.part_number_id
        class_id = export_line.class_id
        
        # 1. Buscar todos los lotes candidatos (Importaciones del mismo número de parte y clase)
        # Solo facturas procesadas y con fecha <= export_date
        candidates = (
            db.query(LineItem)
            .join(InvoiceHeader, InvoiceHeader.id == LineItem.invoice_id)
            .filter(
                LineItem.tenant_id == export_invoice.tenant_id,
                LineItem.company_id == export_invoice.company_id,
                LineItem.part_number_id == part_id,
                LineItem.class_id == class_id,
                InvoiceHeader.operation_type == "imp",
                InvoiceHeader.status != InvoiceStatus.PENDING,
                InvoiceHeader.invoice_date <= export_date
            )
            .all()
        )

        # 2. Calcular saldos para cada candidato y llenar available_lots
        found_any_balance = False
        for imp_line in candidates:
            available = _net_balance_for_lot(db, imp_line.id, export_date)
            
            if available > 0:
                found_any_balance = True
                fin = imp_line.financial
                lot = AvailableLot(
                    import_item_line_id=imp_line.id,
                    import_invoice_id=imp_line.invoice_id,
                    part_number_id=imp_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, imp_line.id),
                )
                entry.available_lots.append(lot)

        # 3. Ordenar por PEPS (FIFO)
        entry.available_lots.sort(key=lambda x: x.order_peps)

        # 4. Manejo de Errores: Solo si no se encontró ABSOLUTAMENTE NADA de saldo
        if not found_any_balance:
            errors.add_error(
                field=f"line[{entry.export_line}].quantity",
                message=f"No se encontró saldo disponible en inventario para el número de parte: {entry.part_number}",
                solution=["Verificar que existan facturas de importación procesadas con saldo."],
                code="NO_BALANCE_FOUND_ANYWHERE",
                value=entry.part_number,
            )
