"""The single `user` table for every role. `mr_profile` and `area_manager_profile` were merged in (report §13.2): both would have held the same columns as `user`, and the only MR-specific payload is `headquarters_id`. """ import uuid from sqlalchemy import Boolean, CheckConstraint, Enum, ForeignKey, String, UniqueConstraint from sqlalchemy.dialects.postgresql import UUID as PGUUID from sqlalchemy.orm import Mapped, mapped_column, relationship from app.common.enums import Role from app.database.base import Base, TimestampMixin, UUIDMixin class User(Base, UUIDMixin, TimestampMixin): __tablename__ = "user" __table_args__ = ( UniqueConstraint("company_id", "email", name="uq_user_company_email"), UniqueConstraint("company_id", "employee_code", name="uq_user_company_empcode"), # An MR must have a headquarters; nobody else may have one (R4, R8). CheckConstraint( "(role IN ('MR', 'SFE') AND headquarters_id IS NOT NULL) " "OR (role NOT IN ('MR', 'SFE') AND headquarters_id IS NULL)", name="ck_user_mr_requires_hq", ), ) company_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("company.id", ondelete="RESTRICT"), nullable=False ) email: Mapped[str] = mapped_column(String(255), nullable=False) password_hash: Mapped[str] = mapped_column(String(255), nullable=False) full_name: Mapped[str] = mapped_column(String(200), nullable=False) employee_code: Mapped[str] = mapped_column(String(50), nullable=False) phone: Mapped[str | None] = mapped_column(String(20)) role: Mapped[Role] = mapped_column( Enum(Role, name="role_enum", native_enum=False, length=10), nullable=False ) #: MR only — the one headquarters this MR belongs to (R4). headquarters_id: Mapped[uuid.UUID | None] = mapped_column( PGUUID(as_uuid=True), ForeignKey("headquarters.id", ondelete="RESTRICT") ) #: Checked on EVERY request, not only at login (SEC-5). is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False) #: The one protected owner account. Nobody — not even itself — can edit, deactivate, #: re-role or delete it through the API; a DB trigger backs that up. It acts as an #: ADMIN everywhere (role is ADMIN), and is created only by `scripts/create_superadmin.py`. is_superadmin: Mapped[bool] = mapped_column( Boolean, default=False, server_default="false", nullable=False ) failed_login_attempts: Mapped[int] = mapped_column(default=0, nullable=False) headquarters: Mapped["Headquarters | None"] = relationship() # noqa: F821 #: SFE only — the other headquarters (districts) covered besides the primary one. extra_headquarters: Mapped[list["UserExtraHeadquarters"]] = relationship( lazy="selectin", cascade="all, delete-orphan" ) @property def extra_headquarter_ids(self) -> list[uuid.UUID]: return [e.headquarters_id for e in self.extra_headquarters] @property def covered_headquarter_ids(self) -> set[uuid.UUID]: """Every headquarters this field user works in: the primary one plus the extra ones.""" ids = set(self.extra_headquarter_ids) if self.headquarters_id: ids.add(self.headquarters_id) return ids class UserExtraHeadquarters(Base): """An SFE covers more than one district: each extra headquarters is one row here.""" __tablename__ = "user_extra_headquarters" user_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("user.id", ondelete="CASCADE"), primary_key=True ) headquarters_id: Mapped[uuid.UUID] = mapped_column( PGUUID(as_uuid=True), ForeignKey("headquarters.id", ondelete="RESTRICT"), primary_key=True )