"""Requisitions — staff request a purchase, the admin approves or rejects, then
marks the order completed once the goods arrive. A rejected request can be
re-submitted; an approved order is only ever completed, never reopened."""
from datetime import datetime

from sqlalchemy import (
    CheckConstraint,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    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 RequisitionStatus, pg_enum


class Requisition(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "requisitions"

    id: Mapped[int] = mapped_column(primary_key=True)
    req_no: Mapped[str] = mapped_column(String(30), nullable=False, index=True)
    status: Mapped[RequisitionStatus] = mapped_column(
        pg_enum(RequisitionStatus, "requisition_status"),
        nullable=False,
        default=RequisitionStatus.PENDING,
        server_default=RequisitionStatus.PENDING.value,
        index=True,
    )
    #: What the order is for — "restock office", "new connection materials", etc.
    purpose: Mapped[str] = mapped_column(Text, nullable=False)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    requested_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True
    )
    requested_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, index=True
    )

    approved_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    rejected_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    rejected_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    created_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )

    items: Mapped[list["RequisitionItem"]] = relationship(
        back_populates="requisition",
        cascade="all, delete-orphan",
        order_by="RequisitionItem.id",
    )
    status_history: Mapped[list["RequisitionStatusHistory"]] = relationship(
        back_populates="requisition",
        cascade="all, delete-orphan",
        order_by="RequisitionStatusHistory.changed_at",
    )
    requested_by = relationship("User", foreign_keys=[requested_by_id], lazy="joined")
    approved_by = relationship("User", foreign_keys=[approved_by_id], lazy="joined")
    rejected_by = relationship("User", foreign_keys=[rejected_by_id], lazy="joined")
    completed_by = relationship("User", foreign_keys=[completed_by_id], lazy="joined")
    created_by = relationship("User", foreign_keys=[created_by_id], lazy="joined")

    __table_args__ = (
        Index(
            "uq_requisitions_req_no_active",
            "req_no",
            unique=True,
            postgresql_where=text("is_deleted = false"),
        ),
        Index("ix_requisitions_status_requested_at", "status", "requested_at"),
    )


class RequisitionItem(Base):
    __tablename__ = "requisition_items"

    id: Mapped[int] = mapped_column(primary_key=True)
    requisition_id: Mapped[int] = mapped_column(
        ForeignKey("requisitions.id", ondelete="CASCADE"), nullable=False, index=True
    )
    item_name: Mapped[str] = mapped_column(String(200), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False)
    unit: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pcs")

    requisition: Mapped["Requisition"] = relationship(back_populates="items")

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


class RequisitionStatusHistory(Base):
    """Append-only trail of who moved the order, from where, to where, when."""

    __tablename__ = "requisition_status_history"

    id: Mapped[int] = mapped_column(primary_key=True)
    requisition_id: Mapped[int] = mapped_column(
        ForeignKey("requisitions.id", ondelete="CASCADE"), nullable=False, index=True
    )
    from_status: Mapped[RequisitionStatus | None] = mapped_column(
        pg_enum(RequisitionStatus, "requisition_status", create_type=False),
        nullable=True,
    )
    to_status: Mapped[RequisitionStatus] = mapped_column(
        pg_enum(RequisitionStatus, "requisition_status", create_type=False),
        nullable=False,
    )
    changed_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    changed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, index=True
    )
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    requisition: Mapped["Requisition"] = relationship(back_populates="status_history")
    changed_by = relationship("User", lazy="joined")