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 uuid
from datetime import datetime

from sqlalchemy import Boolean, Column, DateTime, ForeignKey, 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 posrtgreSQL 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-orphan"
    )
    slots: Mapped[list["Slot"]] = relationship(
        back_populates="project", cascade="delete-orphan"
    )
    sms: Mapped[list["Sms"]] = relationship(
        back_populates="project", cascade="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"
    )

    @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)
    description: Mapped[str] = mapped_column(String(), default="")
    starting_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    ending_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    volunteers: Mapped[list[Volunteer]] = relationship(
        secondary=association_table_volunteer_slot, back_populates="slots"
    )

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


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"))
    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, unique=True, index=True
    )
    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))