"""
Annex 24 - Balance Management Core
SQLAlchemy v2

Plugs into the existing schema:
  invoice_header  (a76.invoice_header)   ← already exists
  item_lines      (a76.item_lines)        ← already exists

These 4 tables are ALL you need for balances:

  a24.balance_movement    ← the ledger (append-only, never UPDATE)
  a24.discharge_header    ← one discharge per export/SM/CTM event
  a24.discharge_detail    ← one row per (export line x import lot consumed)
  a24.discharge_scrap     ← mermas, desperdicios, destrucciones

Design rules:
  1. NEVER update balance_movement rows — only INSERT
  2. Balance = SUM of movements. No cached balance columns.
  3. Every discharge_detail row MUST reference a balance_movement row
  4. PEPS order is enforced via order_peps (monotonic, set on INSERT)
"""

from __future__ import annotations

import datetime
from decimal import Decimal
from enum import Enum
from typing import TYPE_CHECKING, List, Optional

from sqlalchemy import (
    BigInteger,
    CheckConstraint,
    Date,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    String,    
    text,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from api.v1.common.base_models import TenantScopedMixin, TimestampMixin
from core.database import Base

if TYPE_CHECKING:
    from api.v1.modules.a76.invoices.models import InvoiceHeader
    from api.v1.modules.a76.items.models import LineItem    
    from ..balance_movements.models import BalanceMovement


class DischargeType(str, Enum):
    TEMPORARY   = "temporary"    # SDescargaT  — export against temp import
    DEFINITIVE  = "definitive"   # SDescargaD  — export against def import
    REPAIR      = "repair"       # SDescargaR  — repair / return
    DIRECTED    = "directed"     # SDescargaM  — Directed discharge
    CTM         = "ctm"          # SDescargaCTM — between plants (same group)
    SUBASSEMBLY = "subassembly"  # SDescargaSM  — to external maquiladora
    WASTE_SCRAP = "waste_scrap"  # SDescargaMerDes — merma / desperdicio


class DischargeStatus(str, Enum):
    PENDING   = "pending"
    APPLIED   = "applied"
    PARTIAL   = "partial"
    CANCELLED = "cancelled"

# ---------------------------------------------------------------------------
# 2. DischargeHeader
#
#    One row per discharge event (export, SM batch, CTM transfer…).
#    Groups all DischargeDetail rows for the same business event.
#
#    source_invoice_id     → a76.invoice_header.id  (the export/SM/CTM invoice)
#    def_import_invoice_id → a76.invoice_header.id  (DEF discharges only)
# ---------------------------------------------------------------------------

class DischargeHeader(Base, TenantScopedMixin, TimestampMixin):
    """
    One discharge per export event.
    Groups DischargeDetail rows (one per import lot consumed).
    """

    __tablename__ = "discharge_header"
    __table_args__ = (
        Index("ix_dischdr_source",  "source_invoice_id", "status"),
        Index("ix_dischdr_date",    "tenant_id", "discharge_date", "discharge_type"),
        {"schema": "a24"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    # ── Export / SM / CTM document that triggers this discharge ───────────
    source_invoice_id: Mapped[int] = mapped_column(
        ForeignKey("a76.invoice_header.id"),
        comment="Export, SM-out or CTM-send invoice that owns this discharge.",
    )

    # ── For DEFINITIVE discharges only ────────────────────────────────────
    def_import_invoice_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.invoice_header.id"),
        comment="Populated only for discharge_type=DEFINITIVE.",
    )

    discharge_type: Mapped[DischargeType] = mapped_column(String(15))
    status: Mapped[DischargeStatus] = mapped_column(
        String(15), server_default=text("'applied'")
    )
    discharge_date: Mapped[datetime.date] = mapped_column(Date)

    # ── Control fields from legacy SDescarga* tables ──────────────────────
    reference_invoice: Mapped[Optional[str]] = mapped_column(
        String(19), comment="FACREFERENCIA — for rectifications"
    )
    discharge_subtype: Mapped[Optional[str]] = mapped_column(
        String(10), comment="TIPODESC: NORMAL, PARCIAL, REPARACION, UTILERIA"
    )
    partial_sequence: Mapped[Optional[int]] = mapped_column(
        Integer, comment="CONSECPARCIAL — for partial discharges"
    )
    sales_order: Mapped[Optional[str]] = mapped_column(String(20))   # ORDENVENTA
    ctm_section: Mapped[Optional[str]] = mapped_column(String(3))    # APARTADOCTM
    is_tooling: Mapped[bool] = mapped_column(
        server_default=text("false"), comment="PORUTILERIA"
    )
    discharge_sm: Mapped[Optional[str]] = mapped_column(String(4))   # DESCARGASM
    is_repair_update: Mapped[bool] = mapped_column(
        server_default=text("false"), comment="ACTUALREPARACION"
    )
    material_type_expo: Mapped[Optional[str]] = mapped_column(
        String(10), comment="TIPOMATEXPO"
    )

    # ── Cancellation trail ────────────────────────────────────────────────
    cancelled_by: Mapped[Optional[str]] = mapped_column(String(100))
    cancellation_reason: Mapped[Optional[str]] = mapped_column(String(300))

    # ── Relationships ─────────────────────────────────────────────────────
    source_invoice: Mapped["InvoiceHeader"] = relationship(
        foreign_keys=[source_invoice_id],
    )
    def_import_invoice: Mapped[Optional["InvoiceHeader"]] = relationship(
        foreign_keys=[def_import_invoice_id],
    )
    details: Mapped[List["DischargeDetail"]] = relationship(
        back_populates="header", cascade="all, delete-orphan"
    )
    scraps: Mapped[List["DischargeScrap"]] = relationship(
        back_populates="header", cascade="all, delete-orphan"
    )


# ---------------------------------------------------------------------------
# 3. DischargeDetail
#
#    The critical traceability link:
#      "Export line X consumed Y units from import lot Z"
#
#    One row per (export_line x import_lot) pair.
#    A single export line can span multiple rows when PEPS pulls from
#    more than one import lot.
#
#    discharge_header_id  → a24.discharge_header.id
#    export_item_line_id  → a76.item_lines.id   (the export line)
#    import_item_line_id  → a76.item_lines.id   (the import lot consumed)
#    movement_id          → a24.balance_movement.id  (the ledger row)
# ---------------------------------------------------------------------------

class DischargeDetail(Base, TenantScopedMixin, TimestampMixin):
    """
    One row per (export line x import lot consumed).

    This is the traceability record the SAT asks for:
    "Show me which import pedimento covered this export line."
    """

    __tablename__ = "discharge_detail"
    __table_args__ = (
        CheckConstraint("movement_id IS NOT NULL", name="ck_dischdet_movement_required"),
        Index("ix_dischdet_header",      "discharge_header_id"),
        Index("ix_dischdet_import_lot",  "import_item_line_id"),
        Index("ix_dischdet_export_line", "export_item_line_id"),
        Index("ix_dischdet_part",        "tenant_id", "part_number"),
        {"schema": "a24"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    discharge_header_id: Mapped[int] = mapped_column(
        ForeignKey("a24.discharge_header.id"),
    )

    # ── Export side ───────────────────────────────────────────────────────
    export_item_line_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.item_lines.id"),
        comment="NULL for waste-only discharges.",
    )
    # Denormalized for report performance
    part_number: Mapped[Optional[str]] = mapped_column(
        String(70), comment="NUMPARTE of the export line (denormalized)"
    )
    export_part_number: Mapped[Optional[str]] = mapped_column(
        String(70), comment="NUMPARTEEXPO — as it appears in the pedimento"
    )
    export_line_ref: Mapped[Optional[int]] = mapped_column(
        Integer, comment="LINEAEXPOREF — for rectification references"
    )

    # ── Import lot side (PEPS lot consumed) ───────────────────────────────
    import_item_line_id: Mapped[int] = mapped_column(
        ForeignKey("a76.item_lines.id"),
    )

    # ── Ledger row created for this consumption (NOT NULL — design rule 3) ─
    movement_id: Mapped[int] = mapped_column(
        BigInteger,
        ForeignKey("a24.balance_movement.id"),
        comment="The BalanceMovement that records this consumption. Required.",
    )

    # ── Consumed quantities and values ────────────────────────────────────
    quantity_discharged: Mapped[Decimal] = mapped_column(Numeric(19, 8))  # CANTDESC
    unit_of_measure: Mapped[Optional[str]] = mapped_column(String(5))
    value_mn: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))
    value_me: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))
    net_weight: Mapped[Optional[Decimal]] = mapped_column(Numeric(19, 8))
    gross_weight: Mapped[Optional[Decimal]] = mapped_column(Numeric(19, 8))

    # ── Tariff classification of the consumed input ───────────────────────
    tariff_fraction: Mapped[Optional[str]] = mapped_column(String(10))  # FRACCIONIMPO
    fraction_type: Mapped[Optional[str]] = mapped_column(String(7))     # TIPOFRACCION
    ad_valorem: Mapped[Optional[str]] = mapped_column(String(10))       # ADVALOREMIMPO
    country_of_origin: Mapped[Optional[str]] = mapped_column(String(3)) # PAISMERCANCIA
    sector: Mapped[Optional[str]] = mapped_column(String(8))            # SECTOR

    # ── Repair-specific ───────────────────────────────────────────────────
    original_part: Mapped[Optional[str]] = mapped_column(
        String(70), comment="PARTEORIGINAL"
    )
    equivalent_quantity: Mapped[Optional[Decimal]] = mapped_column(
        Numeric(19, 8), comment="CANTEQUIVALENTE"
    )
    equivalent_unit: Mapped[Optional[str]] = mapped_column(String(5))
    returned_quantity_sm: Mapped[Optional[Decimal]] = mapped_column(
        Numeric(19, 8), comment="CANTRETORNADASAM"
    )

    # ── Waste / scrap ─────────────────────────────────────────────────────
    waste_type: Mapped[Optional[str]] = mapped_column(
        String(1), comment="M=merma, D=desperdicio, S=scrap"
    )
    take_balance_base_pt: Mapped[Optional[str]] = mapped_column(
        String(2), comment="TOMARSALDOBASEALPT"
    )

    # ── Tax fields (from SDescargaT) ──────────────────────────────────────
    igi_amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))  # MONTOIGI
    tax_payment: Mapped[Optional[str]] = mapped_column(String(1))          # PAGOIMPUESTO
    has_certificate: Mapped[Optional[str]] = mapped_column(String(1))      # TIENECERT
    iva_mn: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))      # VALORIVAMN
    iva_me: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))      # VALORIVAME

    # ── SM origin traceability ────────────────────────────────────────────
    origin_import_invoice: Mapped[Optional[str]] = mapped_column(
        String(15), comment="FACTURAIMPO original (denorm for SM)"
    )
    procedence: Mapped[Optional[str]] = mapped_column(String(3))  # PROCEDENCIA

    # ── Relationships ─────────────────────────────────────────────────────
    header: Mapped["DischargeHeader"] = relationship(back_populates="details")
    export_line: Mapped[Optional["LineItem"]] = relationship(
        foreign_keys=[export_item_line_id],
    )
    import_line: Mapped["LineItem"] = relationship(
        foreign_keys=[import_item_line_id],
    )
    movement: Mapped["BalanceMovement"] = relationship(
        foreign_keys=[movement_id],
        back_populates="discharge_detail",
    )


# ---------------------------------------------------------------------------
# 4. DischargeScrap
#
#    Mermas, desperdicios and destrucciones.
#    Can be standalone (no export invoice) or linked to a DischargeHeader.
#    Always generates a BalanceMovement of type WASTE / SCRAP / DESTRUCTION.
#
#    Replaces: SDescargaS + SDescargaMerDes
# ---------------------------------------------------------------------------

class DischargeScrap(Base, TenantScopedMixin, TimestampMixin):
    """
    Waste / scrap / destruction records.
    Replaces SDescargaS and the waste portion of SDescargaMerDes.
    """

    __tablename__ = "discharge_scrap"
    __table_args__ = (
        Index("ix_dischscrap_header",     "discharge_header_id"),
        Index("ix_dischscrap_import_lot", "import_item_line_id"),
        Index("ix_dischscrap_date",       "tenant_id", "scrap_date"),
        {"schema": "a24"},
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    # ── Optional parent discharge ─────────────────────────────────────────
    discharge_header_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a24.discharge_header.id"),
        comment="NULL when scrap is registered independently (not tied to an export).",
    )

    # ── The import lot being scrapped ──────────────────────────────────────
    import_item_line_id: Mapped[int] = mapped_column(
        ForeignKey("a76.item_lines.id"),
    )

    # ── Ledger row recording this scrap ───────────────────────────────────
    movement_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("a24.balance_movement.id"),
    )

    # ── Type ──────────────────────────────────────────────────────────────
    scrap_type: Mapped[str] = mapped_column(
        String(1), comment="M=merma, D=desperdicio, S=scrap, X=destrucción"
    )

    # ── Finished-good export line that generated this scrap ───────────────
    finished_good_line_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.item_lines.id"),
        comment="Export line of the product whose manufacture created this scrap.",
    )
    finished_good_part: Mapped[Optional[str]] = mapped_column(String(70))

    # ── Scrap export pedimento (if scrap is exported separately) ──────────
    scrap_export_invoice_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.invoice_header.id"),
        comment="If desperdicio has its own export pedimento.",
    )

    # ── The scrapped material ─────────────────────────────────────────────
    part_number: Mapped[str] = mapped_column(String(70))
    item_class: Mapped[Optional[str]] = mapped_column(String(8))
    quantity: Mapped[Decimal] = mapped_column(Numeric(19, 8))
    unit_of_measure: Mapped[str] = mapped_column(String(5))
    value_mn: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))
    value_me: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8))
    net_weight: Mapped[Optional[Decimal]] = mapped_column(Numeric(19, 8))
    procedence: Mapped[Optional[str]] = mapped_column(String(3))
    scrap_date: Mapped[datetime.date] = mapped_column(Date)

    # ── Relationships ─────────────────────────────────────────────────────
    header: Mapped[Optional["DischargeHeader"]] = relationship(
        back_populates="scraps",
    )
    import_line: Mapped["LineItem"] = relationship(
        foreign_keys=[import_item_line_id],
    )
    finished_good_line: Mapped[Optional["LineItem"]] = relationship(
        foreign_keys=[finished_good_line_id],
    )
    scrap_export_invoice: Mapped[Optional["InvoiceHeader"]] = relationship(
        foreign_keys=[scrap_export_invoice_id],
    )
    movement: Mapped[Optional["BalanceMovement"]] = relationship(
        foreign_keys=[movement_id],
    )