"""SQLAlchemy models.

Field shapes follow Mike's real spreadsheets (see brief.md) so his data imports
without transformation.
"""

from __future__ import annotations

import enum
from datetime import datetime

from sqlalchemy import (
    BigInteger,
    Boolean,
    DateTime,
    Enum,
    ForeignKey,
    Integer,
    Numeric,
    String,
    Text,
    func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class TicketStatus(str, enum.Enum):
    reported = "reported"
    triaged = "triaged"
    scheduled = "scheduled"
    in_progress = "in_progress"
    closed = "closed"
    void = "void"


class TicketPriority(str, enum.Enum):
    low = "low"
    normal = "normal"
    high = "high"
    critical = "critical"


class UsageDirection(str, enum.Enum):
    used = "used"
    returned = "returned"


class Asset(Base):
    __tablename__ = "assets"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    equipment_no: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    description: Mapped[str] = mapped_column(String(255), index=True)
    line: Mapped[str | None] = mapped_column(String(128), default=None)
    manufacturer: Mapped[str | None] = mapped_column(String(128), default=None)


class Part(Base):
    __tablename__ = "parts"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    part_number: Mapped[str] = mapped_column(String(128), unique=True, index=True)
    part_name: Mapped[str] = mapped_column(String(255), index=True)
    description: Mapped[str | None] = mapped_column(String(255), default=None)
    uom: Mapped[str | None] = mapped_column(String(32), default=None)
    location: Mapped[str | None] = mapped_column(String(128), default=None)
    area: Mapped[str | None] = mapped_column(String(128), default=None)
    part_type: Mapped[str | None] = mapped_column(String(64), default=None)
    barcode: Mapped[str | None] = mapped_column(String(128), index=True, default=None)
    unit_cost: Mapped[float | None] = mapped_column(Numeric(12, 2), default=None)
    quantity: Mapped[int] = mapped_column(Integer, default=0)


class Technician(Base):
    """Backs the name picker. NOT an identity store - see agents.md.

    At P2 this becomes a projection of BW accounts; it holds no credential and
    grants no access. The security boundary is the id-auth gate in front.
    """

    __tablename__ = "technicians"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(128), unique=True)
    role: Mapped[str] = mapped_column(String(32), default="tech")
    active: Mapped[bool] = mapped_column(Boolean, default=True)


class Ticket(Base):
    __tablename__ = "tickets"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    # Drawn from a dedicated sequence that is never reset, cycled, or reused.
    number: Mapped[int] = mapped_column(BigInteger, unique=True, index=True)
    asset_id: Mapped[int | None] = mapped_column(ForeignKey("assets.id"), default=None)
    parent_ticket_id: Mapped[int | None] = mapped_column(
        ForeignKey("tickets.id"), default=None
    )
    description: Mapped[str] = mapped_column(Text)
    status: Mapped[TicketStatus] = mapped_column(
        Enum(TicketStatus, name="ticket_status"), default=TicketStatus.reported
    )
    priority: Mapped[TicketPriority] = mapped_column(
        Enum(TicketPriority, name="ticket_priority"), default=TicketPriority.normal
    )
    reported_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("technicians.id"), default=None
    )
    assigned_to_id: Mapped[int | None] = mapped_column(
        ForeignKey("technicians.id"), default=None
    )
    reported_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    scheduled_for: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    closed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )

    asset: Mapped[Asset | None] = relationship(lazy="joined")
    reported_by: Mapped[Technician | None] = relationship(
        lazy="joined", foreign_keys=[reported_by_id]
    )
    assigned_to: Mapped[Technician | None] = relationship(
        lazy="joined", foreign_keys=[assigned_to_id]
    )
    media: Mapped[list[TicketMedia]] = relationship(
        back_populates="ticket", cascade="all, delete-orphan"
    )


class TicketEvent(Base):
    """Append-only audit trail. This is what makes 'auditable' true."""

    __tablename__ = "ticket_events"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    ticket_id: Mapped[int] = mapped_column(ForeignKey("tickets.id"), index=True)
    kind: Mapped[str] = mapped_column(String(32))
    detail: Mapped[str | None] = mapped_column(Text, default=None)
    actor_id: Mapped[int | None] = mapped_column(
        ForeignKey("technicians.id"), default=None
    )
    at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    actor: Mapped[Technician | None] = relationship(lazy="joined")


class TicketMedia(Base):
    __tablename__ = "ticket_media"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    ticket_id: Mapped[int] = mapped_column(ForeignKey("tickets.id"), index=True)
    # Path relative to settings.media_root - never an absolute path or a URL.
    rel_path: Mapped[str] = mapped_column(String(255))
    mime: Mapped[str] = mapped_column(String(128))
    size_bytes: Mapped[int] = mapped_column(Integer)
    uploaded_by_id: Mapped[int | None] = mapped_column(
        ForeignKey("technicians.id"), default=None
    )
    uploaded_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    ticket: Mapped[Ticket] = relationship(back_populates="media")


class PartUsage(Base):
    """The item-movement report, as a table."""

    __tablename__ = "part_usage"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    ticket_id: Mapped[int] = mapped_column(ForeignKey("tickets.id"), index=True)
    part_id: Mapped[int] = mapped_column(ForeignKey("parts.id"), index=True)
    quantity: Mapped[float] = mapped_column(Numeric(12, 3))
    direction: Mapped[UsageDirection] = mapped_column(
        Enum(UsageDirection, name="usage_direction"), default=UsageDirection.used
    )
    actor_id: Mapped[int | None] = mapped_column(
        ForeignKey("technicians.id"), default=None
    )
    at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )

    part: Mapped[Part] = relationship(lazy="joined")
    ticket: Mapped[Ticket] = relationship(lazy="joined")
    actor: Mapped[Technician | None] = relationship(lazy="joined")