app/models.py

from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, onupdate=datetime.utcnow
    )


class Admin(TimestampMixin, Base):
    __tablename__ = "admins"

    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True, index=True)
    display_name: Mapped[str] = mapped_column(String(120))
    password_hash: Mapped[str] = mapped_column(String(255))
    role: Mapped[str] = mapped_column(String(32), default="admin")
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    last_seen_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    created_activities: Mapped[list["Activity"]] = relationship(back_populates="created_by")
    reviewed_submissions: Mapped[list["Submission"]] = relationship(
        back_populates="reviewed_by",
        foreign_keys="Submission.reviewed_by_id",
    )
    assigned_submissions: Mapped[list["Submission"]] = relationship(
        back_populates="assigned_admin",
        foreign_keys="Submission.assigned_admin_id",
    )


class Group(TimestampMixin, Base):
    __tablename__ = "groups"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), unique=True)
    max_members: Mapped[int] = mapped_column(Integer, default=6)

    members: Mapped[list["User"]] = relationship(back_populates="group")
    submissions: Mapped[list["Submission"]] = relationship(back_populates="group")


class User(TimestampMixin, Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    student_id: Mapped[str] = mapped_column(String(64), unique=True, index=True)
    full_name: Mapped[str] = mapped_column(String(120))
    password_hash: Mapped[str] = mapped_column(String(255))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    group_id: Mapped[Optional[int]] = mapped_column(ForeignKey("groups.id"), nullable=True)
    last_seen_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    group: Mapped[Optional["Group"]] = relationship(back_populates="members")
    submissions: Mapped[list["Submission"]] = relationship(back_populates="user")


class Activity(TimestampMixin, Base):
    __tablename__ = "activities"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(160))
    description: Mapped[str] = mapped_column(Text, default="")
    start_at: Mapped[datetime] = mapped_column(DateTime)
    deadline_at: Mapped[datetime] = mapped_column(DateTime)
    is_visible: Mapped[bool] = mapped_column(Boolean, default=True)
    leaderboard_visible: Mapped[bool] = mapped_column(Boolean, default=True)
    clue_interval_minutes: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    created_by_id: Mapped[int] = mapped_column(ForeignKey("admins.id"))

    created_by: Mapped["Admin"] = relationship(back_populates="created_activities")
    tasks: Mapped[list["Task"]] = relationship(
        back_populates="activity",
        order_by="Task.display_order",
        cascade="all, delete-orphan",
    )


class Task(TimestampMixin, Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    activity_id: Mapped[int] = mapped_column(ForeignKey("activities.id"))
    title: Mapped[str] = mapped_column(String(160))
    description: Mapped[str] = mapped_column(Text, default="")
    display_order: Mapped[int] = mapped_column(Integer, default=1)

    image_url: Mapped[Optional[str]] = mapped_column(String(1000), nullable=True)
    image_path: Mapped[Optional[str]] = mapped_column(String(600), nullable=True)
    image_mime: Mapped[str] = mapped_column(String(120), default="image/jpeg")
    image_filename: Mapped[str] = mapped_column(String(255), default="task.jpg")

    clue_image_url: Mapped[Optional[str]] = mapped_column(String(1000), nullable=True)
    clue_image_path: Mapped[Optional[str]] = mapped_column(String(600), nullable=True)
    clue_image_mime: Mapped[Optional[str]] = mapped_column(String(120), nullable=True)
    clue_image_filename: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    clue_release_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    activity: Mapped["Activity"] = relationship(back_populates="tasks")
    submissions: Mapped[list["Submission"]] = relationship(
        back_populates="task", cascade="all, delete-orphan"
    )


class Submission(TimestampMixin, Base):
    __tablename__ = "submissions"
    __table_args__ = (
        UniqueConstraint("group_id", "task_id", name="uq_group_task_submission"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    task_id: Mapped[int] = mapped_column(ForeignKey("tasks.id"))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    group_id: Mapped[Optional[int]] = mapped_column(ForeignKey("groups.id"), nullable=True)

    stored_filename: Mapped[str] = mapped_column(String(255))
    original_filename: Mapped[str] = mapped_column(String(255))
    file_path: Mapped[str] = mapped_column(String(600))
    mime_type: Mapped[str] = mapped_column(String(120), default="image/jpeg")
    file_size: Mapped[int] = mapped_column(Integer)

    status: Mapped[str] = mapped_column(String(32), default="pending")
    feedback: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    reviewed_by_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("admins.id"), nullable=True
    )
    assigned_admin_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("admins.id"), nullable=True
    )
    assigned_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    reviewed_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    approved_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)

    task: Mapped["Task"] = relationship(back_populates="submissions")
    user: Mapped["User"] = relationship(back_populates="submissions")
    group: Mapped[Optional["Group"]] = relationship(back_populates="submissions")
    reviewed_by: Mapped[Optional["Admin"]] = relationship(
        back_populates="reviewed_submissions",
        foreign_keys=[reviewed_by_id],
    )
    assigned_admin: Mapped[Optional["Admin"]] = relationship(
        back_populates="assigned_submissions",
        foreign_keys=[assigned_admin_id],
    )