'''
perguntations/models.py
SQLAlchemy ORM
'''

from typing import List

from sqlalchemy import ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, relationship, mapped_column


class Base(DeclarativeBase):
    pass


# [Student] ---1:N--- [Test] ---1:N--- [Question]

# ----------------------------------------------------------------------------
class Student(Base):
    __tablename__ = 'students'

    id = mapped_column(String, primary_key=True)
    name: Mapped[str]
    password: Mapped[str]
    # ---
    tests: Mapped[List['Test']] = relationship(back_populates='student')

# ----------------------------------------------------------------------------
class Test(Base):
    __tablename__ = 'tests'

    id = mapped_column(Integer, primary_key=True)  # auto_increment
    ref: Mapped[str]
    title: Mapped[str]
    grade: Mapped[float]
    state: Mapped[str]  # ACTIVE, SUBMITTED, CORRECTED, QUIT, NULL
    comment: Mapped[str]
    starttime: Mapped[str]
    finishtime: Mapped[str]
    filename: Mapped[str]
    student_id = mapped_column(String, ForeignKey('students.id'))
    # ---
    student: Mapped['Student'] = relationship(back_populates='tests')
    questions: Mapped[List['Question']] = relationship(back_populates='test')

# ---------------------------------------------------------------------------
class Question(Base):
    __tablename__ = 'questions'

    id = mapped_column(Integer, primary_key=True)  # auto_increment
    number: Mapped[int] # question number (ref may be repeated in the same test)
    ref: Mapped[str]
    grade: Mapped[float]
    comment: Mapped[str]
    starttime: Mapped[str]
    finishtime: Mapped[str]
    test_id = mapped_column(String, ForeignKey('tests.id'))
    # ---
    test: Mapped['Test'] = relationship(back_populates='questions')