"""Reference / lookup tables: zones, areas, locations, equipment types."""
from sqlalchemy import Boolean, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base_class import Base, SoftDeleteMixin, TimestampMixin
from app.models.enums import LocationType, pg_enum


class Zone(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "zones"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False, unique=True)
    code: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, index=True)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    areas: Mapped[list["Area"]] = relationship(back_populates="zone")


class Area(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "areas"

    id: Mapped[int] = mapped_column(primary_key=True)
    zone_id: Mapped[int] = mapped_column(
        ForeignKey("zones.id", ondelete="RESTRICT"), nullable=False, index=True
    )
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    code: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, index=True)

    zone: Mapped["Zone"] = relationship(back_populates="areas")

    __table_args__ = (UniqueConstraint("zone_id", "name", name="uq_areas_zone_id_name"),)


class Location(Base, TimestampMixin, SoftDeleteMixin):
    """Physical stock-holding place (warehouse / office)."""

    __tablename__ = "locations"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False, unique=True)
    code: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, index=True)
    type: Mapped[LocationType] = mapped_column(
        pg_enum(LocationType, "location_type"),
        nullable=False,
        default=LocationType.WAREHOUSE,
    )
    address: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_default: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false", nullable=False)


class EquipmentType(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "equipment_types"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False, unique=True)
    code: Mapped[str] = mapped_column(String(20), nullable=False, unique=True, index=True)
    unit: Mapped[str] = mapped_column(String(20), nullable=False, default="pcs", server_default="pcs")
    is_serialized: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="true", nullable=False,
        doc="True for routers/ONUs tracked by serial; False for bulk items like cable/connectors.",
    )
    default_return_days: Mapped[int | None] = mapped_column(
        Integer, nullable=True, doc="Default days before a technician-issued item is overdue."
    )
    reorder_level: Mapped[int] = mapped_column(Integer, default=0, server_default="0", nullable=False)