data/databases/models.py

"""
SQLAlchemy ORM models for the university course management database.

This module defines all tables using SQLAlchemy declarative syntax with proper
relationships and data types.
"""

from datetime import datetime
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()


class Address(Base):
    """Address information for people."""

    __tablename__ = "Addresses"

    address_id = Column(Integer, primary_key=True, autoincrement=True)
    line_1 = Column(String(255), nullable=False)
    line_2 = Column(String(255))
    city = Column(String(100))
    zip_postcode = Column(String(20))
    state_province_county = Column(String(100))
    country = Column(String(100))

    # Relationships
    people_addresses = relationship("PersonAddress", back_populates="address")


class Person(Base):
    """Person information."""

    __tablename__ = "People"

    person_id = Column(Integer, primary_key=True, autoincrement=True)
    first_name = Column(String(100), nullable=False)
    middle_name = Column(String(100))
    last_name = Column(String(100), nullable=False)
    cell_mobile_number = Column(String(20))
    email_address = Column(String(255))
    login_name = Column(String(100), unique=True)
    password = Column(String(255))

    # Relationships
    people_addresses = relationship("PersonAddress", back_populates="person")


class Student(Base):
    """Student information."""

    __tablename__ = "Students"

    student_id = Column(Integer, primary_key=True, autoincrement=True)
    student_details = Column(String(500))

    # Relationships
    course_registrations = relationship(
        "StudentCourseRegistration", back_populates="student"
    )
    course_attendance = relationship(
        "StudentCourseAttendance", back_populates="student"
    )


class Course(Base):
    """Course information."""

    __tablename__ = "Courses"

    course_id = Column(String(50), primary_key=True)
    course_name = Column(String(200), nullable=False)
    course_description = Column(String(500))
    other_details = Column(String(500))

    # Relationships
    course_registrations = relationship(
        "StudentCourseRegistration", back_populates="course"
    )
    course_attendance = relationship("StudentCourseAttendance", back_populates="course")


class PersonAddress(Base):
    """Link between people and their addresses with date ranges."""

    __tablename__ = "People_Addresses"

    person_address_id = Column(Integer, primary_key=True, autoincrement=True)
    person_id = Column(Integer, ForeignKey("People.person_id"), nullable=False)
    address_id = Column(Integer, ForeignKey("Addresses.address_id"), nullable=False)
    date_from = Column(DateTime)
    date_to = Column(DateTime)

    # Relationships
    person = relationship("Person", back_populates="people_addresses")
    address = relationship("Address", back_populates="people_addresses")


class StudentCourseRegistration(Base):
    """Student registration for courses."""

    __tablename__ = "Student_Course_Registrations"

    student_id = Column(Integer, ForeignKey("Students.student_id"), primary_key=True)
    course_id = Column(String(50), ForeignKey("Courses.course_id"), primary_key=True)
    registration_date = Column(DateTime, default=datetime.utcnow)

    # Relationships
    student = relationship("Student", back_populates="course_registrations")
    course = relationship("Course", back_populates="course_registrations")


class StudentCourseAttendance(Base):
    """Student attendance records for courses."""

    __tablename__ = "Student_Course_Attendance"

    student_id = Column(Integer, ForeignKey("Students.student_id"), primary_key=True)
    course_id = Column(String(50), ForeignKey("Courses.course_id"), primary_key=True)
    date_of_attendance = Column(DateTime, primary_key=True)

    # Relationships
    student = relationship("Student", back_populates="course_attendance")
    course = relationship("Course", back_populates="course_attendance")


class Candidate(Base):
    """Candidate information."""

    __tablename__ = "Candidates"

    candidate_id = Column(Integer, primary_key=True, autoincrement=True)
    candidate_details = Column(String(500))

    # Relationships
    assessments = relationship("CandidateAssessment", back_populates="candidate")


class CandidateAssessment(Base):
    """Assessment records for candidates."""

    __tablename__ = "Candidate_Assessments"

    candidate_id = Column(
        Integer, ForeignKey("Candidates.candidate_id"), primary_key=True
    )
    qualification = Column(String(200), primary_key=True)
    assessment_date = Column(DateTime, primary_key=True)
    asessment_outcome_code = Column(String(50))

    # Relationships
    candidate = relationship("Candidate", back_populates="assessments")