bdlg2023-sms-back/app/models.py

"""
SQL Alchemy models declaration.
https://docs.sqlalchemy.org/en/14/orm/declarative_styles.html#example-two-dataclasses-with-declarative-table
Dataclass style for powerful autocompletion support.

https://alembic.sqlalchemy.org/en/latest/tutorial.html
Note, it is used by alembic migrations logic, see `alembic/env.py`

Alembic shortcuts:
# create migration
alembic revision --autogenerate -m "migration_name"

# apply all migrations
alembic upgrade head
"""

import enum
import uuid
from datetime import datetime

from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, Table
from sqlalchemy import Enum as SAEnum
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.sql import func


class Base(DeclarativeBase):
    pass


def uid_column() -> Mapped[str]:
    """Returns a postgreSQL UUID column for SQL ORM"""
    return mapped_column(UUID(as_uuid=False), primary_key=True, default=lambda _: str(uuid.uuid4()))


class GlobalRole(str, enum.Enum):
    SUPER_ADMIN = "super_admin"
    USER = "user"


class OrgRole(str, enum.Enum):
    ORG_ADMIN = "org_admin"
    RESPO_BENEVOLE = "respo_benevole"
    RESPO_COMMISSION = "respo_commission"


class User(Base):
    __tablename__ = "user_model"
    id: Mapped[UUID] = uid_column()
    email: Mapped[str] = mapped_column(String(254), nullable=False, unique=True, index=True)
    hashed_password: Mapped[str] = mapped_column(String(128), nullable=False)
    name: Mapped[str] = mapped_column(String(128), default="")
    phone_number: Mapped[str | None] = mapped_column(String(24), nullable=True)
    global_role: Mapped[GlobalRole] = mapped_column(
        SAEnum(GlobalRole, name="global_role"), default=GlobalRole.USER, nullable=False
    )

    organizations: Mapped[list["UserOrganization"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    commissions: Mapped[list["CommissionMember"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )


class Organization(Base):
    __tablename__ = "organizations"
    id: Mapped[UUID] = uid_column()
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now(), nullable=True
    )
    name: Mapped[str] = mapped_column(String(128), nullable=False, unique=True, index=True)

    projects: Mapped[list["Project"]] = relationship(
        back_populates="organization", cascade="all, delete-orphan"
    )
    memberships: Mapped[list["UserOrganization"]] = relationship(
        back_populates="organization",
        cascade="all, delete-orphan",
        passive_deletes=True,  # let the DB's ON DELETE CASCADE do the actual delete
    )


class UserOrganization(Base):
    """Many-to-many: a user can belong to several orgs, one role per org."""

    __tablename__ = "user_organizations"
    user_id: Mapped[UUID] = mapped_column(
        ForeignKey("user_model.id", ondelete="CASCADE"), primary_key=True
    )
    organization_id: Mapped[UUID] = mapped_column(
        ForeignKey("organizations.id", ondelete="CASCADE"), primary_key=True
    )
    role: Mapped[OrgRole] = mapped_column(SAEnum(OrgRole, name="org_role"), nullable=False)

    user: Mapped["User"] = relationship(back_populates="organizations")
    organization: Mapped["Organization"] = relationship(back_populates="memberships")


class Project(Base):
    __tablename__ = "projects"
    id: Mapped[UUID] = uid_column()
    organization_id: Mapped[UUID] = mapped_column(
        ForeignKey("organizations.id", ondelete="CASCADE"), nullable=False
    )
    organization: Mapped["Organization"] = relationship(back_populates="projects")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    name: Mapped[str] = mapped_column(String(128), nullable=False, unique=True, index=True)
    is_public: Mapped[bool] = mapped_column(Boolean())
    volunteers: Mapped[list["Volunteer"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )
    slots: Mapped[list["Slot"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )
    sms: Mapped[list["Sms"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )
    templates: Mapped[list["SlotTemplate"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )
    tags: Mapped[list["SlotTag"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )
    commissions: Mapped[list["Commission"]] = relationship(
        back_populates="project", cascade="delete, delete-orphan"
    )

    groups: Mapped[list["VolunteerGroup"]] = relationship(
        back_populates="project", cascade="all, delete-orphan", passive_deletes=True
    )


class Commission(Base):
    __tablename__ = "commissions"
    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="commissions")
    name: Mapped[str] = mapped_column(String(128), nullable=False)  # e.g. "Bar", "Accueil"

    templates: Mapped[list["SlotTemplate"]] = relationship(back_populates="commission")
    members: Mapped[list["CommissionMember"]] = relationship(
        back_populates="commission", cascade="all, delete-orphan"
    )

    @hybrid_property
    def contacts(self) -> list[dict]:
        """Read-only, derived from members' own profile — never stored redundantly."""
        return [{"name": m.user.name, "phone_number": m.user.phone_number} for m in self.members]


class CommissionMember(Base):
    __tablename__ = "commission_members"
    commission_id: Mapped[UUID] = mapped_column(
        ForeignKey("commissions.id", ondelete="CASCADE"), primary_key=True
    )
    user_id: Mapped[UUID] = mapped_column(
        ForeignKey("user_model.id", ondelete="CASCADE"), primary_key=True
    )

    commission: Mapped["Commission"] = relationship(back_populates="members")
    user: Mapped["User"] = relationship(back_populates="commissions")


association_table_volunteer_slot = Table(
    "association_volunteer_slot",
    Base.metadata,
    Column(
        "volunteer_id",
        ForeignKey("volunteers.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column("slot_id", ForeignKey("slots.id", ondelete="CASCADE"), primary_key=True),
)

association_table_volunteer_group = Table(
    "association_volunteer_group",
    Base.metadata,
    Column("volunteer_id", ForeignKey("volunteers.id", ondelete="CASCADE"), primary_key=True),
    Column("group_id", ForeignKey("volunteer_groups.id", ondelete="CASCADE"), primary_key=True),
)


class VolunteerGroup(Base):
    __tablename__ = "volunteer_groups"
    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship()
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    color: Mapped[str | None] = mapped_column(String(16), nullable=True)  # for UI chips later

    volunteers: Mapped[list["Volunteer"]] = relationship(
        secondary=association_table_volunteer_group, back_populates="groups"
    )

    @hybrid_property
    def volunteers_id(self) -> list[str]:
        return [v.id for v in self.volunteers]


class Volunteer(Base):
    __tablename__ = "volunteers"
    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="volunteers")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    name: Mapped[str] = mapped_column(String(128))
    surname: Mapped[str] = mapped_column(String(128))
    email: Mapped[str] = mapped_column(String(128))
    phone_number: Mapped[str] = mapped_column(String(128))
    automatic_sms: Mapped[bool] = mapped_column(Boolean(), default=False)
    slots: Mapped[list["Slot"]] = relationship(
        secondary=association_table_volunteer_slot, back_populates="volunteers"
    )
    comment: Mapped[str] = mapped_column(String(), default="")
    sms: Mapped[list["Sms"]] = relationship(back_populates="volunteer", cascade="all, delete")

    groups: Mapped[list["VolunteerGroup"]] = relationship(
        secondary=association_table_volunteer_group, back_populates="volunteers"
    )

    @hybrid_property
    def slots_id(self) -> list[str]:
        return [s.id for s in self.slots]


class Slot(Base):
    __tablename__ = "slots"

    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="slots")

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    title: Mapped[str] = mapped_column(String(128), nullable=False)

    starting_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    ending_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))

    required_volunteers: Mapped[int] = mapped_column(Integer, default=0)

    volunteers: Mapped[list[Volunteer]] = relationship(
        secondary=association_table_volunteer_slot, back_populates="slots"
    )

    template_id: Mapped[UUID | None] = mapped_column(
        ForeignKey("slot_templates.id", ondelete="SET NULL"), nullable=True
    )
    template: Mapped["SlotTemplate"] = relationship(back_populates="slots")

    @hybrid_property
    def volunteers_id(self) -> list[str]:
        return [v.id for v in self.volunteers]


association_table_template_tags = Table(
    "association_description_tag",
    Base.metadata,
    Column(
        "description_id",
        ForeignKey("slot_templates.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column("tag_id", ForeignKey("slot_tags.id", ondelete="CASCADE"), primary_key=True),
)


class SlotTag(Base):
    __tablename__ = "slot_tags"

    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="tags")

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    title: Mapped[str] = mapped_column(String(), default="")

    templates: Mapped[list["SlotTemplate"]] = relationship(
        secondary=association_table_template_tags, back_populates="tags"
    )

    @hybrid_property
    def templates_id(self) -> list[str]:
        return [s.id for s in self.templates]


class SlotTemplate(Base):
    __tablename__ = "slot_templates"

    id: Mapped[UUID] = uid_column()
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="templates")

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    title: Mapped[str] = mapped_column(String(), default="")
    description: Mapped[str] = mapped_column(String(), default="")
    place: Mapped[str] = mapped_column(String(), default="")
    commission_id: Mapped[str | None] = mapped_column(
        ForeignKey("commissions.id", ondelete="SET NULL"), nullable=True
    )
    commission: Mapped["Commission"] = relationship(back_populates="templates")
    responsible_override: Mapped[str | None] = mapped_column(String(), nullable=True, default=None)

    slots: Mapped[list[Slot]] = relationship(back_populates="template")

    tags: Mapped[list[SlotTag]] = relationship(
        secondary=association_table_template_tags, back_populates="templates"
    )

    comment: Mapped[str] = mapped_column(String(), default="")

    @hybrid_property
    def slots_id(self) -> list[str]:
        return [s.id for s in self.slots]

    @hybrid_property
    def tags_id(self) -> list[str]:
        return [s.id for s in self.tags]

    @hybrid_property
    def effective_responsible_contact(self) -> str:
        """What SMS/templates should actually display as {respo}."""
        if self.responsible_override:
            return self.responsible_override
        if self.commission is not None:
            contacts = self.commission.contacts  # [{"name":..., "phone_number":...}, ...]
            return ", ".join(
                f"{c['name']} : {c['phone_number']}" for c in contacts if c.get("phone_number")
            )
        return ""


class Sms(Base):
    __tablename__ = "sms"

    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda _: str(uuid.uuid4())
    )
    project_id: Mapped[UUID] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="sms")

    volunteer_id: Mapped[UUID] = mapped_column(
        ForeignKey("volunteers.id", ondelete="CASCADE", onupdate="CASCADE"),
        nullable=True,
    )
    volunteer: Mapped["Volunteer"] = relationship(back_populates="sms", cascade="all, delete")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=datetime.now, onupdate=func.now()
    )
    content: Mapped[str] = mapped_column(String(), nullable=False)
    phone_number: Mapped[str] = mapped_column(String(24))
    sending_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.now)
    send_time: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=True)


class ServerStatus(Base):
    __tablename__ = "server_status"
    # Use a fixed ID to ensure we only ever have one row
    id = Column(Integer, primary_key=True, default=1)
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now, onupdate=func.now()
    )
    host = Column(String, nullable=False)
    user_agent = Column(String)