template-tutoring-admin-dashboard/backend/app/models.py

"""SQLAlchemy ORM models for the SwatiYadav tuition website.

Every public section is a table so the teacher can edit it from admin. The
public site reads these; the admin panel writes them.
"""

from __future__ import annotations

from datetime import date, datetime, timezone

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

from app.db import Base


def _now() -> datetime:
    return datetime.now(timezone.utc)


class AdminUser(Base):
    __tablename__ = "admin_user"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(80), unique=True, nullable=False)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)


class Profile(Base):
    __tablename__ = "profile"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(160), default="")
    tagline: Mapped[str] = mapped_column(String(255), default="")
    bio: Mapped[str] = mapped_column(Text, default="")
    photo_url: Mapped[str] = mapped_column(Text, default="")
    qualifications: Mapped[str] = mapped_column(Text, default="")
    experience_years: Mapped[int] = mapped_column(Integer, default=0)
    students_taught: Mapped[int] = mapped_column(Integer, default=0)
    achievements: Mapped[str] = mapped_column(Text, default="")
    hero_cta: Mapped[str] = mapped_column(String(120), default="Book a free demo")


class Subject(Base):
    __tablename__ = "subject"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(120), default="")
    level: Mapped[str] = mapped_column(String(120), default="")
    description: Mapped[str] = mapped_column(Text, default="")
    mode: Mapped[str] = mapped_column(String(60), default="Online & Offline")
    icon: Mapped[str] = mapped_column(String(20), default="📘")
    sort_order: Mapped[int] = mapped_column(Integer, default=0)


class Course(Base):
    __tablename__ = "course"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(160), default="")
    subject: Mapped[str] = mapped_column(String(120), default="")
    description: Mapped[str] = mapped_column(Text, default="")
    duration: Mapped[str] = mapped_column(String(120), default="")
    mode: Mapped[str] = mapped_column(String(60), default="Online & Offline")
    highlights: Mapped[str] = mapped_column(Text, default="")  # newline-separated
    sort_order: Mapped[int] = mapped_column(Integer, default=0)


class FeePlan(Base):
    __tablename__ = "fee_plan"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(120), default="")
    price: Mapped[str] = mapped_column(String(60), default="")
    period: Mapped[str] = mapped_column(String(60), default="per month")
    features: Mapped[str] = mapped_column(Text, default="")  # newline-separated
    highlighted: Mapped[bool] = mapped_column(Boolean, default=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)


class Testimonial(Base):
    __tablename__ = "testimonial"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    author_name: Mapped[str] = mapped_column(String(120), default="")
    relation: Mapped[str] = mapped_column(String(60), default="Student")
    quote: Mapped[str] = mapped_column(Text, default="")
    rating: Mapped[int] = mapped_column(Integer, default=5)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)


class SiteSettings(Base):
    __tablename__ = "site_settings"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    address: Mapped[str] = mapped_column(Text, default="")
    phone: Mapped[str] = mapped_column(String(60), default="")
    email: Mapped[str] = mapped_column(String(160), default="")
    hours: Mapped[str] = mapped_column(String(255), default="")
    whatsapp: Mapped[str] = mapped_column(String(160), default="")
    instagram: Mapped[str] = mapped_column(String(160), default="")
    youtube: Mapped[str] = mapped_column(String(160), default="")
    facebook: Mapped[str] = mapped_column(String(160), default="")
    footer_text: Mapped[str] = mapped_column(Text, default="")


class Review(Base):
    __tablename__ = "review"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    author_name: Mapped[str] = mapped_column(String(160), default="")
    relation: Mapped[str] = mapped_column(String(60), default="Student")
    rating: Mapped[int] = mapped_column(Integer, default=5)
    message: Mapped[str] = mapped_column(Text, default="")
    is_approved: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)


class Enquiry(Base):
    __tablename__ = "enquiry"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(160), default="")
    email: Mapped[str] = mapped_column(String(160), default="")
    phone: Mapped[str] = mapped_column(String(60), default="")
    subject_interest: Mapped[str] = mapped_column(String(160), default="")
    message: Mapped[str] = mapped_column(Text, default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    is_read: Mapped[bool] = mapped_column(Boolean, default=False)


# ---- Student management (admin-only, private) ----


class Student(Base):
    __tablename__ = "student"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(160), default="")
    phone: Mapped[str] = mapped_column(String(60), default="")
    parent_name: Mapped[str] = mapped_column(String(160), default="")
    parent_phone: Mapped[str] = mapped_column(String(60), default="")
    address: Mapped[str] = mapped_column(Text, default="")
    subject: Mapped[str] = mapped_column(String(160), default="")
    monthly_fee: Mapped[int] = mapped_column(Integer, default=0)
    start_date: Mapped[date] = mapped_column(Date, default=date.today)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    notes: Mapped[str] = mapped_column(Text, default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)

    slots: Mapped[list["ScheduleSlot"]] = relationship(
        back_populates="student",
        cascade="all, delete-orphan",
    )


class ScheduleSlot(Base):
    __tablename__ = "schedule_slot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    student_id: Mapped[int] = mapped_column(
        ForeignKey("student.id", ondelete="CASCADE"), nullable=False, index=True
    )
    weekday: Mapped[int] = mapped_column(Integer, default=0)  # 0=Mon .. 6=Sun
    start_time: Mapped[str] = mapped_column(String(5), default="")  # "HH:MM"
    end_time: Mapped[str] = mapped_column(String(5), default="")

    student: Mapped["Student"] = relationship(back_populates="slots")


class Attendance(Base):
    __tablename__ = "attendance"
    __table_args__ = (UniqueConstraint("student_id", "date", name="uq_attendance_student_date"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    student_id: Mapped[int] = mapped_column(
        ForeignKey("student.id", ondelete="CASCADE"), nullable=False, index=True
    )
    date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    status: Mapped[str] = mapped_column(String(10), default="present")  # present|absent|off
    note: Mapped[str] = mapped_column(Text, default="")  # why absent / off


class DayMark(Base):
    __tablename__ = "day_mark"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    date: Mapped[date] = mapped_column(Date, unique=True, nullable=False, index=True)
    kind: Mapped[str] = mapped_column(String(12), default="holiday")  # holiday|class_off
    note: Mapped[str] = mapped_column(String(255), default="")