"""
REGISTRA_DESCARGA_SERIES
Marks each import serie that is being discharged by this export invoice by
setting ``discharge = True`` (Clarion: ``SerImp:SerieExpo = 1``).

The routine iterates over every export serie with ``discharge = True``,
resolves the corresponding import serie (via ``import_serie_row`` or serial
number lookup), and flags it as exported so it cannot be discharged again.

Clarion mapping
---------------
QueueSeries records (SerDes)  → export Serie rows with discharge=True,
                                 grouped by DownloadEntry
SerDes:ConsectivoImpo         → import InvoiceHeader.id  (via invoice_number)
SerDes:LineaImpo              → import LineItem.line_number
SerDes:Renglon                → Serie.row on the import side
SerDes:Procedencia            → entry.origin_procedure ('TEM' | 'DEF')
SerImp/SerDef:SerieExpo = 1   → import_serie.discharge = True

Legacy equivalent
-----------------
Sort(QueueSeries, -Procedencia, ConsectivoImpo, LineaImpo)
Loop: GET import serie by (Consecutivo, LineaImpo, Renglon) → set SerieExpo=1
"""

from typing import List, Optional

from sqlalchemy import select
from sqlalchemy.orm import Session, joinedload

from api.v1.modules.a76.invoices.models import InvoiceHeader
from api.v1.modules.a76.items.models import LineItem
from api.v1.modules.a76.items.series.models import Serie
from .discharge_types import 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_id(
    db: Session,
    invoice_id: int,
    line_number: int,
    tenant_id: int,
    company_id: int,
) -> Optional[int]:
    return db.execute(
        select(LineItem.id).where(
            LineItem.invoice_id == invoice_id,
            LineItem.line_number == line_number,
            LineItem.tenant_id == tenant_id,
            LineItem.company_id == company_id,
        )
    ).scalar_one_or_none()


def _fetch_import_serie(
    db: Session,
    import_line_id: int,
    row: int,
) -> Optional[Serie]:
    """Fetch the import serie by (line_item_id, row) — equiv. TryFetch PKConsec_Lin_Ren."""
    return db.execute(
        select(Serie).where(
            Serie.line_item_id == import_line_id,
            Serie.row == row,
        )
    ).scalar_one_or_none()


def _resolve_import_serie_row(
    db: Session,
    import_line_id: int,
    serial_number: str,
) -> Optional[int]:
    """
    Fallback: find the import serie row by matching serial_number when
    export_serie.serie_row is not set.
    """
    return db.execute(
        select(Serie.row).where(
            Serie.line_item_id == import_line_id,
            Serie.serial_numbers == serial_number,
        )
    ).scalar_one_or_none()


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

def register_discharge_series(
    db: Session,
    export_invoice: InvoiceHeader,
    to_discharge: List[DownloadEntry],
) -> None:
    """
    REGISTRA_DESCARGA_SERIES
    For every export serie with ``discharge = True`` on each entry in
    ``to_discharge``, locates the matching import serie and marks it as
    discharged (``discharge = True``).

    Parameters
    ----------
    db              : active SQLAlchemy session
    export_invoice  : the export invoice being processed
    to_discharge    : list of DownloadEntry records (QueADescargar)
    """
    # Sort mirrors Clarion: Sort(QueueSeries, -Procedencia, ConsectivoImpo, LineaImpo)
    # Descending procedencia puts 'TEM' before 'DEF' (T > D alphabetically)
    sorted_entries = sorted(
        to_discharge,
        key=lambda e: (e.origin_procedure, e.import_invoice, e.import_line),
        reverse=True,
    )

    for entry in sorted_entries:
        if not entry.import_invoice or entry.import_line == 0:
            continue

        # ── Resolve import invoice and line ───────────────────────────────────
        import_invoice = _fetch_import_invoice(db, entry.import_invoice, export_invoice)
        if import_invoice is None:
            continue

        import_line_id = _fetch_import_line_id(
            db,
            import_invoice.id,
            entry.import_line,
            export_invoice.tenant_id,
            export_invoice.company_id,
        )
        if import_line_id is None:
            continue

        # ── Fetch all export series for this discharge line ───────────────────
        export_series: List[Serie] = (
            db.execute(
                select(Serie).where(
                    Serie.line_item_id == entry.line_item_id,
                    Serie.discharge == True,  # noqa: E712
                )
            )
            .scalars()
            .all()
        )

        for export_serie in export_series:
            # Resolve which row in the import series table this corresponds to
            import_row = export_serie.serie_row
            if import_row is None:
                import_row = _resolve_import_serie_row(
                    db, import_line_id, export_serie.serial_numbers or ""
                )

            if import_row is None:
                continue

            import_serie = _fetch_import_serie(db, import_line_id, import_row)
            if import_serie is None:
                continue

            # SerImp:SerieExpo = 1 (or SerDef:SerieExpo = 1)
            import_serie.discharge = True
