"""Module 2 — Equipment inventory, distribution, collection, stock ledger."""
from datetime import date, datetime

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

from app.db.base_class import Base, SoftDeleteMixin, TimestampMixin
from app.models.enums import (
    DistributionStatus,
    EquipmentCondition,
    EquipmentItemStatus,
    HolderType,
    StockMovementType,
    pg_enum,
)


class EquipmentItem(Base, TimestampMixin, SoftDeleteMixin):
    """One inventory row. Serialized items have quantity 1; bulk items carry a quantity."""

    __tablename__ = "equipment_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    equipment_type_id: Mapped[int] = mapped_column(
        ForeignKey("equipment_types.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    model: Mapped[str | None] = mapped_column(String(120), nullable=True)
    serial_number: Mapped[str | None] = mapped_column(
        String(120), nullable=True, index=True
    )
    condition: Mapped[EquipmentCondition] = mapped_column(
        pg_enum(EquipmentCondition, "equipment_condition"),
        nullable=False,
        default=EquipmentCondition.NEW,
        server_default=EquipmentCondition.NEW.value,
        index=True,
    )
    quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1, server_default="1")

    # --- Current holder (exactly one of the three targets) ---
    holder_type: Mapped[HolderType] = mapped_column(
        pg_enum(HolderType, "holder_type"),
        nullable=False,
        default=HolderType.WAREHOUSE,
        server_default=HolderType.WAREHOUSE.value,
        index=True,
    )
    location_id: Mapped[int | None] = mapped_column(
        ForeignKey("locations.id", ondelete="RESTRICT"), nullable=True, index=True
    )
    holder_user_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True
    )
    holder_customer_id: Mapped[int | None] = mapped_column(
        ForeignKey("customers.id", ondelete="SET NULL"), nullable=True, index=True
    )

    status: Mapped[EquipmentItemStatus] = mapped_column(
        pg_enum(EquipmentItemStatus, "equipment_item_status"),
        nullable=False,
        default=EquipmentItemStatus.IN_STOCK,
        server_default=EquipmentItemStatus.IN_STOCK.value,
        index=True,
    )
    purchase_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    unit_cost: Mapped[float | None] = mapped_column(Numeric(10, 2), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    equipment_type = relationship("EquipmentType", lazy="joined")
    location = relationship("Location", lazy="joined")
    holder_user = relationship("User", foreign_keys=[holder_user_id], lazy="joined")
    holder_customer = relationship("Customer", foreign_keys=[holder_customer_id], lazy="joined")
    distributions = relationship("EquipmentDistribution", back_populates="item")

    __table_args__ = (
        CheckConstraint("quantity >= 0", name="quantity_non_negative"),
        CheckConstraint(
            "(holder_type = 'warehouse' AND location_id IS NOT NULL "
            " AND holder_user_id IS NULL AND holder_customer_id IS NULL) OR "
            "(holder_type = 'technician' AND holder_user_id IS NOT NULL "
            " AND holder_customer_id IS NULL) OR "
            "(holder_type = 'customer' AND holder_customer_id IS NOT NULL "
            " AND holder_user_id IS NULL)",
            name="holder_target_consistent",
        ),
        Index(
            "uq_equipment_items_serial_active",
            "serial_number",
            unique=True,
            postgresql_where=text("is_deleted = false AND serial_number IS NOT NULL"),
        ),
        Index("ix_equipment_items_type_status", "equipment_type_id", "status"),
        Index("ix_equipment_items_type_location", "equipment_type_id", "location_id"),
    )


class EquipmentDistribution(Base, TimestampMixin, SoftDeleteMixin):
    """Issue log: equipment handed to a technician or installed at a customer."""

    __tablename__ = "equipment_distributions"

    id: Mapped[int] = mapped_column(primary_key=True)
    equipment_item_id: Mapped[int] = mapped_column(
        ForeignKey("equipment_items.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1, server_default="1")
    returned_quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0")

    issued_to_type: Mapped[HolderType] = mapped_column(
        pg_enum(HolderType, "holder_type", create_type=False),
        nullable=False,
    )
    issued_to_user_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True
    )
    issued_to_customer_id: Mapped[int | None] = mapped_column(
        ForeignKey("customers.id", ondelete="SET NULL"), nullable=True, index=True
    )

    # What the issue relates to
    connection_application_id: Mapped[int | None] = mapped_column(
        ForeignKey("connection_applications.id", ondelete="SET NULL"), nullable=True, index=True
    )
    connection_id: Mapped[int | None] = mapped_column(
        ForeignKey("connections.id", ondelete="SET NULL"), nullable=True, index=True
    )
    from_location_id: Mapped[int | None] = mapped_column(
        ForeignKey("locations.id", ondelete="SET NULL"), nullable=True
    )

    issued_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    issued_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    expected_return_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    status: Mapped[DistributionStatus] = mapped_column(
        pg_enum(DistributionStatus, "distribution_status"),
        nullable=False,
        default=DistributionStatus.ISSUED,
        server_default=DistributionStatus.ISSUED.value,
        index=True,
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    item = relationship("EquipmentItem", back_populates="distributions", lazy="joined")
    issued_to_user = relationship("User", foreign_keys=[issued_to_user_id], lazy="joined")
    issued_to_customer = relationship("Customer", foreign_keys=[issued_to_customer_id], lazy="joined")
    issued_by = relationship("User", foreign_keys=[issued_by_id], lazy="joined")
    from_location = relationship("Location", lazy="joined")
    application = relationship("ConnectionApplication", lazy="joined")
    connection = relationship("Connection", lazy="joined")
    collections = relationship(
        "EquipmentCollection", back_populates="distribution", cascade="all, delete-orphan"
    )

    __table_args__ = (
        CheckConstraint("quantity > 0", name="quantity_positive"),
        CheckConstraint(
            "returned_quantity >= 0 AND returned_quantity <= quantity",
            name="returned_within_issued",
        ),
        CheckConstraint(
            "(issued_to_type = 'technician' AND issued_to_user_id IS NOT NULL) OR "
            "(issued_to_type = 'customer' AND issued_to_customer_id IS NOT NULL)",
            name="issue_target_consistent",
        ),
        Index("ix_distributions_status_expected_return", "status", "expected_return_date"),
    )

    @property
    def is_overdue(self) -> bool:
        from app.core.time import today_local

        return (
            self.expected_return_date is not None
            and self.status in (DistributionStatus.ISSUED, DistributionStatus.PARTIALLY_RETURNED)
            and self.expected_return_date < today_local()
        )

    @property
    def outstanding_quantity(self) -> int:
        """Units still in the holder's hands."""
        return self.quantity - self.returned_quantity

    @property
    def days_overdue(self) -> int:
        from app.core.time import today_local

        if not self.is_overdue:
            return 0
        return (today_local() - self.expected_return_date).days


class EquipmentCollection(Base, TimestampMixin, SoftDeleteMixin):
    """Return log: equipment collected back, with its condition on return."""

    __tablename__ = "equipment_collections"

    id: Mapped[int] = mapped_column(primary_key=True)
    distribution_id: Mapped[int | None] = mapped_column(
        ForeignKey("equipment_distributions.id", ondelete="SET NULL"), nullable=True, index=True
    )
    equipment_item_id: Mapped[int] = mapped_column(
        ForeignKey("equipment_items.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1, server_default="1")
    condition_on_return: Mapped[EquipmentCondition] = mapped_column(
        pg_enum(EquipmentCondition, "equipment_condition", create_type=False),
        nullable=False,
    )
    collected_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    collected_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    collected_from_user_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    collected_from_customer_id: Mapped[int | None] = mapped_column(
        ForeignKey("customers.id", ondelete="SET NULL"), nullable=True
    )
    returned_to_location_id: Mapped[int] = mapped_column(
        ForeignKey("locations.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    distribution = relationship("EquipmentDistribution", back_populates="collections")
    item = relationship("EquipmentItem", lazy="joined")
    collected_by = relationship("User", foreign_keys=[collected_by_id], lazy="joined")
    returned_to_location = relationship("Location", lazy="joined")

    __table_args__ = (CheckConstraint("quantity > 0", name="quantity_positive"),)


class EquipmentStockMovement(Base):
    """Append-only ledger — powers stock-movement history and charts."""

    __tablename__ = "equipment_stock_movements"

    id: Mapped[int] = mapped_column(primary_key=True)
    equipment_type_id: Mapped[int] = mapped_column(
        ForeignKey("equipment_types.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    equipment_item_id: Mapped[int | None] = mapped_column(
        ForeignKey("equipment_items.id", ondelete="SET NULL"), nullable=True, index=True
    )
    location_id: Mapped[int | None] = mapped_column(
        ForeignKey("locations.id", ondelete="SET NULL"), nullable=True, index=True
    )
    movement_type: Mapped[StockMovementType] = mapped_column(
        pg_enum(StockMovementType, "stock_movement_type"),
        nullable=False,
        index=True,
    )
    quantity_delta: Mapped[int] = mapped_column(
        Integer, nullable=False, doc="Signed change in on-hand stock at location_id."
    )
    distribution_id: Mapped[int | None] = mapped_column(
        ForeignKey("equipment_distributions.id", ondelete="SET NULL"), nullable=True
    )
    collection_id: Mapped[int | None] = mapped_column(
        ForeignKey("equipment_collections.id", ondelete="SET NULL"), nullable=True
    )
    moved_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    created_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    equipment_type = relationship("EquipmentType", lazy="joined")
    location = relationship("Location", lazy="joined")

    __table_args__ = (
        Index("ix_stock_movements_type_moved_at", "equipment_type_id", "moved_at"),
    )