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
"""

from typing import Optional
import uuid
from datetime import datetime

from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, String, Table
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 User(Base):
    __tablename__ = "user_model"

    id: Mapped[str] = 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)


class Project(Base):
    __tablename__ = "projects"
    id: Mapped[str] = 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()
    )
    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"
    )


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),
)


class Volunteer(Base):
    __tablename__ = "volunteers"
    id: Mapped[str] = uid_column()
    project_id: Mapped[str] = 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")

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


class Slot(Base):
    __tablename__ = "slots"

    id: Mapped[str] = uid_column()
    project_id: Mapped[str] = 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[Optional[str]] = 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[str] = uid_column()
    project_id: Mapped[str] = 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[str] = uid_column()
    project_id: Mapped[str] = 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="")
    responsible_contact: Mapped[str] = mapped_column(String(), default="")

    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]


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[str] = mapped_column(ForeignKey("projects.id", ondelete="CASCADE"))
    project: Mapped["Project"] = relationship(back_populates="sms")

    volunteer_id: Mapped[str] = 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)