chatkit/backend/app/models.py

"""SQLAlchemy ORM models for ClassLens.

All tables are isolated in a dedicated schema (``classlens`` on Supabase) so they
never collide with other apps sharing the same Postgres instance.
"""

from __future__ import annotations

from datetime import datetime, date
from typing import Optional

from sqlalchemy import (
    JSON,
    Boolean,
    Date,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    MetaData,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from .db import active_schema

SCHEMA = active_schema()


def _fk(target: str) -> str:
    """Schema-qualified FK target, e.g. 'classlens.teachers.id' or 'teachers.id'."""
    return f"{SCHEMA}.{target}" if SCHEMA else target


class Base(DeclarativeBase):
    metadata = MetaData(schema=SCHEMA)


class Teacher(Base):
    __tablename__ = "teachers"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False, index=True)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    display_name: Mapped[str] = mapped_column(String(255), default="")
    full_name: Mapped[str] = mapped_column(String(255), default="")
    school: Mapped[str] = mapped_column(String(255), default="")
    is_admin: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    last_login: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class Student(Base):
    __tablename__ = "students"
    __table_args__ = (
        UniqueConstraint("teacher_id", "normalized_name", name="uq_student_teacher_name"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    normalized_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    external_id: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
    email: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    # Cached cross-quiz AI synthesis (see generate_cross_quiz_synthesis). Refreshed
    # whenever a new single-quiz report is generated for this student, so the
    # dashboard can read it instantly instead of calling the LLM on every view.
    ai_summary: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    ai_focus_areas: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    ai_study_recommendations: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    ai_summary_updated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class Quiz(Base):
    __tablename__ = "quizzes"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
    title: Mapped[str] = mapped_column(String(512), default="Untitled Session")
    subject: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    quiz_date: Mapped[Optional[date]] = mapped_column(Date, nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="draft")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class PracticeQuiz(Base):
    """An individualized practice quiz generated for one student from the
    question bank, shareable via a link and completed student-side (no
    login) in a one-question-per-page flow. Single attempt: `status` moves
    draft -> sent -> completed and stays there.
    """

    __tablename__ = "practice_quizzes"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
    student_id: Mapped[int] = mapped_column(ForeignKey(_fk("students.id"), ondelete="CASCADE"), index=True)
    title: Mapped[str] = mapped_column(String(255), default="Practice Quiz")
    share_token: Mapped[str] = mapped_column(String(64), unique=True, index=True, nullable=False)
    questions: Mapped[list] = mapped_column(JSON, nullable=False, default=list)
    feed_to_dashboard: Mapped[bool] = mapped_column(Boolean, default=True)
    status: Mapped[str] = mapped_column(String(16), default="draft")
    answers: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
    score: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
    correct_count: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class RawFile(Base):
    __tablename__ = "raw_files"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
    data_type: Mapped[str] = mapped_column(String(64), nullable=False)
    file_name: Mapped[str] = mapped_column(String(512), default="")
    content_type: Mapped[str] = mapped_column(String(128), default="")
    size_bytes: Mapped[int] = mapped_column(Integer, default=0)
    storage_path: Mapped[str] = mapped_column(String(1024), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class AnswerGrid(Base):
    """Confirmed answer grid for a quiz — one row per quiz."""

    __tablename__ = "answer_grids"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[int] = mapped_column(
        ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), unique=True, index=True
    )
    grid_json: Mapped[dict] = mapped_column(JSON, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class ParsedData(Base):
    """Per-question analytics row — one row per question per quiz."""

    __tablename__ = "parsed_data"
    __table_args__ = (
        UniqueConstraint("quiz_id", "question_num", name="uq_parsed_data_quiz_question"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
    question_num: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    question_str: Mapped[str] = mapped_column(Text, nullable=False, default="")
    options: Mapped[list] = mapped_column(JSON, nullable=False, default=list)
    answer: Mapped[str] = mapped_column(String(10), nullable=False, default="")
    main_category: Mapped[str] = mapped_column(String(64), nullable=False, default="")
    tags: Mapped[list] = mapped_column(JSON, nullable=False, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class StudentResult(Base):
    """One student's outcome on one quiz — the backbone for cross-quiz analytics."""

    __tablename__ = "student_results"
    __table_args__ = (
        UniqueConstraint("quiz_id", "student_id", name="uq_result_quiz_student"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
    student_id: Mapped[int] = mapped_column(ForeignKey(_fk("students.id"), ondelete="CASCADE"), index=True)
    score: Mapped[float] = mapped_column(Float, default=0.0)
    weaknesses: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    study_recommendations: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class Prompt(Base):
    __tablename__ = "prompts"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    is_default: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class QuestionBank(Base):
    """Shared pool of categorized questions accumulated from all uploaded exams."""

    __tablename__ = "question_bank"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey(_fk("quizzes.id"), ondelete="SET NULL"), nullable=True, index=True
    )
    teacher_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey(_fk("teachers.id"), ondelete="SET NULL"), nullable=True, index=True
    )
    question_text: Mapped[str] = mapped_column(Text, nullable=False)
    answer: Mapped[str] = mapped_column(String(10), default="")
    main_category: Mapped[str] = mapped_column(String(64), nullable=False, default="", index=True)
    tags: Mapped[list] = mapped_column(JSON, nullable=False, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class Report(Base):
    __tablename__ = "reports"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
    student_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey(_fk("students.id"), ondelete="SET NULL"), nullable=True, index=True
    )
    prompt_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey(_fk("prompts.id"), ondelete="SET NULL"), nullable=True
    )
    html_content: Mapped[str] = mapped_column(Text, nullable=False)
    model: Mapped[str] = mapped_column(String(64), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())