"""
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 ..discharges.models import DischargeDetail

from sqlalchemy import (
    BigInteger,
    CheckConstraint,
    Date,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    String,
    UniqueConstraint,
    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 api.v1.modules.a76.parts.models import Part


# ---------------------------------------------------------------------------
# Enums
# ---------------------------------------------------------------------------


class MovementType(str, Enum):
    # ── Positive (add to balance) ──────────────────────────────────────────
    ENTRY = "entry"                      # Normal import entry
    RETURN = "return"                    # Material returned to balance
    POSITIVE_ADJUSTMENT = "pos_adj"      # Physical inventory adjustment (+)
    TRANSFER_IN = "transfer_in"          # CTM/SM received (the receiving side)
    REGIME_CHANGE_IN = "regime_chg_in"   # Eg. temporary → definitive (entry side)

    # ── Negative (consume from balance) ───────────────────────────────────
    CONSUMPTION = "consumption"          # Consumed in export (most common)
    WASTE = "waste"                      # Merma de proceso
    SCRAP = "scrap"                      # Desperdicio / scrap
    DESTRUCTION = "destruction"          # Destrucción oficial ante aduana
    NEGATIVE_ADJUSTMENT = "neg_adj"      # Physical inventory adjustment (-)
    TRANSFER_OUT = "transfer_out"        # CTM/SM sent (the sending side)
    EXPIRATION = "expiration"            # Balance cancelled due to deadline
    REGIME_CHANGE_OUT = "regime_chg_out" # Eg. temporary → definitive (exit side)

    # ── Reversal (annuls a prior ENTRY — used when un-processing an invoice) ─
    # Inserting ENTRY_VOID with the same quantity as the original ENTRY leaves
    # the net balance at zero, preventing any further discharges against that
    # lot. A fresh ENTRY is created when the invoice is re-processed.
    ENTRY_VOID = "entry_void"


# Which movement types reduce the balance (sign = -1)
NEGATIVE_MOVEMENTS = {
    MovementType.CONSUMPTION,
    MovementType.WASTE,
    MovementType.SCRAP,
    MovementType.DESTRUCTION,
    MovementType.NEGATIVE_ADJUSTMENT,
    MovementType.TRANSFER_OUT,
    MovementType.EXPIRATION,
    MovementType.REGIME_CHANGE_OUT,
    MovementType.ENTRY_VOID,
}

# Which types count toward "used" (CANTUSADA in Anexo 24 report)
USED_MOVEMENTS = {
    MovementType.CONSUMPTION,
    MovementType.WASTE,
    MovementType.SCRAP,
    MovementType.DESTRUCTION,
}




# ---------------------------------------------------------------------------
# 1. BalanceMovement  (the ledger — APPEND ONLY)
#
#    One row per atomic change to a specific import lot.
#    Current balance of any lot = SUM of (signed quantity) over its rows.
#
#    import_item_line_id  → a76.item_lines.id       (the import line = the "lot")
#    import_invoice_id    → a76.invoice_header.id   (the import invoice)
#    part_number_id       → a76.parts.id            (denormalized for PEPS index)
#    source_item_line_id  → a76.item_lines.id        (export/SM/CTM line, if any)
#    source_invoice_id    → a76.invoice_header.id   (export/SM/CTM invoice, if any)
#
#    NOTE: No discharge_detail_id column. Navigate the other direction via
#          DischargeDetail.movement_id to avoid a circular FK and keep this
#          table truly append-only (no UPDATE ever needed).
# ---------------------------------------------------------------------------

class BalanceMovement(Base, TenantScopedMixin, TimestampMixin):
    """
    Core ledger table. NEVER update existing rows — only INSERT.

    Balance of a lot  = SUM(quantity) WHERE sign=+1  (entries)
                      - SUM(quantity) WHERE sign=-1  (exits)

    Append-only rule is enforced at the application layer.
    """

    __tablename__ = "balance_movement"
    __table_args__ = (
        UniqueConstraint(
            "import_item_line_id", "order_peps",
            name="uq_balance_movement_lot_peps",
        ),
        CheckConstraint("quantity > 0", name="ck_balance_movement_qty_positive"),
        # PEPS lookup: "give me available lots for this part+regime, oldest first"
        # part_number_id is denormalized here so this index is self-contained.
        Index(
            "ix_balmov_peps_lookup",
            "tenant_id", "part_number_id", "movement_type", "order_peps",
            postgresql_include=["import_item_line_id", "quantity", "value_me", "value_mn"],
        ),
        # Balance calculation per lot
        Index("ix_balmov_lot", "import_item_line_id"),
        # "What did this export consume?"
        Index("ix_balmov_source", "source_invoice_id", "source_item_line_id"),
        # Anexo 24 period reports
        Index("ix_balmov_operation_date", "tenant_id", "operation_date", "movement_type"),
        {"schema": "a24"},
    )

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

    # ── The import lot this movement belongs to ────────────────────────────
    import_invoice_id: Mapped[int] = mapped_column(
        ForeignKey("a76.invoice_header.id"),
        comment="Import invoice (cabecera de importación)",
    )
    import_item_line_id: Mapped[int] = mapped_column(
        ForeignKey("a76.item_lines.id"),
        comment="Import line item = the PEPS lot",
    )

    # ── Denormalized part reference — enables efficient PEPS index ─────────
    # Must equal import_line.part_number_id. Set on INSERT, never changed.
    part_number_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.parts.id"),
        comment="Denormalized from item_lines.part_number_id. Enables PEPS index without joins.",
    )

    # ── What kind of movement ─────────────────────────────────────────────
    movement_type: Mapped[MovementType] = mapped_column(
        String(20),
        comment="See MovementType enum. Determines sign and whether qty counts as used.",
    )

    # ── Quantity and value (always stored positive) ───────────────────────
    quantity: Mapped[Decimal] = mapped_column(
        Numeric(19, 8),
        comment="Always positive. Sign is inferred from movement_type via NEGATIVE_MOVEMENTS.",
    )
    value_me: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8), comment="USD")
    value_mn: Mapped[Optional[Decimal]] = mapped_column(Numeric(23, 8), comment="MXN")
    net_weight: Mapped[Optional[Decimal]] = mapped_column(Numeric(19, 8))

    # ── Document that caused this movement ────────────────────────────────
    # NULL for ENTRY movements (the import invoice itself is the cause)
    source_invoice_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.invoice_header.id"),
        comment="Export / SM / CTM invoice. NULL for entries.",
    )
    source_item_line_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("a76.item_lines.id"),
        comment="Specific line in the export / SM / CTM invoice.",
    )

    # ── PEPS ordering ─────────────────────────────────────────────────────
    # Simplest approach: set order_peps = id (globally monotonic).
    # Finer approach: use a per-part sequence or epoch-based value.
    order_peps: Mapped[int] = mapped_column(
        BigInteger,
        comment="PEPS order within this lot. Lower = older = consumed first.",
    )

    # ── Business date of the operation ───────────────────────────────────
    operation_date: Mapped[datetime.date] = mapped_column(
        Date, comment="Date of the actual business event, not DB insert."
    )

    notes: Mapped[Optional[str]] = mapped_column(String(300))

    # ── Relationships ─────────────────────────────────────────────────────
    import_invoice: Mapped["InvoiceHeader"] = relationship(
        foreign_keys=[import_invoice_id],
    )
    import_line: Mapped["LineItem"] = relationship(
        foreign_keys=[import_item_line_id],
    )
    part: Mapped[Optional["Part"]] = relationship(
        foreign_keys=[part_number_id],
    )
    source_invoice: Mapped[Optional["InvoiceHeader"]] = relationship(
        foreign_keys=[source_invoice_id],
    )
    source_line: Mapped[Optional["LineItem"]] = relationship(
        foreign_keys=[source_item_line_id],
    )
    # Back-reference: navigate to the detail that consumed this movement.
    # Use viewonly=True — ownership lives on DischargeDetail.movement_id.
    discharge_detail: Mapped[Optional["DischargeDetail"]] = relationship(
        back_populates="movement",
        foreign_keys="[DischargeDetail.movement_id]",
        primaryjoin="BalanceMovement.id == DischargeDetail.movement_id",
        viewonly=True,
    )





# ---------------------------------------------------------------------------
# Repository helpers (copy to your service/repository layer)
# ---------------------------------------------------------------------------
#
#
# ── Current balance of a lot ─────────────────────────────────────────────
#
# from sqlalchemy import case, func, select
#
# def current_balance(session, import_item_line_id: int):
#     sign = case(
#         (BalanceMovement.movement_type.in_(NEGATIVE_MOVEMENTS), -1),
#         else_=1,
#     )
#     affects_used = BalanceMovement.movement_type.in_(USED_MOVEMENTS)
#     return session.execute(
#         select(
#             func.sum(sign * BalanceMovement.quantity).label("current_balance"),
#             func.sum(
#                 case((affects_used, BalanceMovement.quantity), else_=0)
#             ).label("quantity_used"),
#             func.sum(
#                 case((affects_used, BalanceMovement.value_me), else_=0)
#             ).label("value_used_me"),
#         ).where(BalanceMovement.import_item_line_id == import_item_line_id)
#     ).one()
#
#
# ── PEPS resolver — call BEFORE inserting a CONSUMPTION movement ─────────
#
# def peps_lots_for(session, tenant_id, part_number_id, operation_type, qty_needed):
#     """
#     Returns import lots in FIFO order with their available balance.
#     Walk the list and consume until qty_needed is satisfied.
#     """
#     sign = case(
#         (BalanceMovement.movement_type.in_(NEGATIVE_MOVEMENTS), -1),
#         else_=1,
#     )
#     lot_balances = (
#         select(
#             BalanceMovement.import_item_line_id,
#             func.sum(sign * BalanceMovement.quantity).label("available"),
#             func.min(BalanceMovement.order_peps).label("oldest_peps"),
#         )
#         .where(
#             BalanceMovement.tenant_id     == tenant_id,
#             BalanceMovement.part_number_id == part_number_id,
#         )
#         .group_by(BalanceMovement.import_item_line_id)
#         .having(func.sum(sign * BalanceMovement.quantity) > 0)
#         .order_by("oldest_peps")
#         .subquery()
#     )
#     return session.execute(select(lot_balances)).all()
#
#
# ── Transaction flow for a new discharge ─────────────────────────────────
#
# def apply_discharge(session, export_invoice_id, lines):
#     """
#     lines = [{"export_line_id": X, "part_number_id": Y, "quantity": Z}, ...]
#
#     Two-step INSERT: movement first, then detail referencing it.
#     No UPDATE on balance_movement — design rule 1 is preserved.
#     """
#     header = DischargeHeader(source_invoice_id=export_invoice_id, ...)
#     session.add(header)
#     session.flush()  # get header.id
#
#     for line in lines:
#         lots = peps_lots_for(session, ..., line["part_number_id"], qty_needed=line["quantity"])
#         remaining = line["quantity"]
#
#         for lot in lots:
#             consume = min(lot.available, remaining)
#
#             # Step 1: insert movement
#             mov = BalanceMovement(
#                 import_item_line_id = lot.import_item_line_id,
#                 part_number_id      = line["part_number_id"],
#                 movement_type       = MovementType.CONSUMPTION,
#                 quantity            = consume,
#                 source_invoice_id   = export_invoice_id,
#                 source_item_line_id = line["export_line_id"],
#                 order_peps          = <next_sequence>,
#                 operation_date      = datetime.date.today(),
#             )
#             session.add(mov)
#             session.flush()  # get mov.id
#
#             # Step 2: insert detail referencing the movement
#             det = DischargeDetail(
#                 discharge_header_id = header.id,
#                 export_item_line_id = line["export_line_id"],
#                 import_item_line_id = lot.import_item_line_id,
#                 movement_id         = mov.id,   # NOT NULL — set immediately
#                 quantity_discharged = consume,
#             )
#             session.add(det)
#
#             remaining -= consume
#             if remaining <= 0:
#                 break
#
#     session.commit()
