from sqlalchemy import (
    CheckConstraint,
    Column,
    Date,
    ForeignKey,
    Index,
    Integer,
    String,
    Time,
    UniqueConstraint,
)
from sqlalchemy.orm import relationship

from .db import Base


class Room(Base):
    __tablename__ = "rooms"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False, unique=True)
    capacity = Column(Integer, nullable=False)

    classes = relationship("Class", back_populates="room", lazy="select")

    __table_args__ = (
        CheckConstraint("capacity > 0", name="ck_rooms_positive_capacity"),
    )


class Instructor(Base):
    __tablename__ = "instructors"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)

    classes = relationship("Class", back_populates="instructor", lazy="select")


class Student(Base):
    __tablename__ = "students"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), nullable=False)

    enrollments = relationship("ClassEnrollment", back_populates="student", lazy="select")


class Class(Base):
    __tablename__ = "classes"

    id = Column(Integer, primary_key=True, index=True)
    date = Column(Date, nullable=False, index=True)
    start_time = Column(Time, nullable=False)
    end_time = Column(Time, nullable=False)
    course_name = Column(String(200), nullable=False, index=True)
    chapter_topic = Column(String(200), nullable=False)
    room_id = Column(Integer, ForeignKey("rooms.id"), nullable=False, index=True)
    instructor_id = Column(Integer, ForeignKey("instructors.id"), nullable=False, index=True)

    room = relationship("Room", back_populates="classes", lazy="joined")
    instructor = relationship("Instructor", back_populates="classes", lazy="joined")
    enrollments = relationship(
        "ClassEnrollment", 
        back_populates="class_", 
        cascade="all, delete-orphan",
        lazy="selectin",
    )

    __table_args__ = (
        Index("ix_classes_date_room", "date", "room_id"),
        Index("ix_classes_date_instructor", "date", "instructor_id"),
        Index("ix_classes_date_times", "date", "start_time", "end_time"),
        Index("ix_classes_date_start_end_room_instructor", 
              "date", "start_time", "end_time", "room_id", "instructor_id"),
        CheckConstraint("end_time > start_time", name="ck_classes_valid_time_range"),
    )


class ClassEnrollment(Base):
    __tablename__ = "class_enrollments"

    id = Column(Integer, primary_key=True, index=True)
    class_id = Column(Integer, ForeignKey("classes.id", ondelete="CASCADE"), nullable=False, index=True)
    student_id = Column(Integer, ForeignKey("students.id", ondelete="CASCADE"), nullable=False, index=True)

    class_ = relationship("Class", back_populates="enrollments", lazy="joined")
    student = relationship("Student", back_populates="enrollments", lazy="joined")

    __table_args__ = (
        UniqueConstraint("class_id", "student_id", name="uq_enrollment_class_student"),
        Index("ix_enrollments_student_class", "student_id", "class_id"),
        Index("ix_enrollments_class_student", "class_id", "student_id"),
    )