"""Core tables for the plan.cs.jmu.edu project.

This package is provided by the instructor and is shared by all six teams.
Do not modify it. Define your team's tables in your own package, and import
from here when you need a foreign key:

    from planner.core import Person, Student, Course

Your tables may reference anything in this module.
Your tables may not reference another team's tables.
"""

from datetime import date, datetime, timezone
from decimal import Decimal
from typing import Optional

from sqlalchemy import Index, UniqueConstraint
from sqlmodel import Field, Relationship, SQLModel


def utcnow() -> datetime:
    """Return the current time in UTC, with the time zone attached."""
    return datetime.now(timezone.utc)


# --------------------------------------------------------------------------
# Identity
# --------------------------------------------------------------------------


class Person(SQLModel, table=True):
    """Every human known to the system.

    Students and faculty are subtypes; a person may be both, and a person
    may be neither (for example, a staff member with no teaching duties).
    The email address is the login name.
    """

    person_id: Optional[int] = Field(default=None, primary_key=True)
    first_name: str
    last_name: str
    preferred_name: Optional[str] = Field(
        default=None, description="Name the person goes by, if not first_name"
    )
    email: str = Field(unique=True)
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=utcnow)

    credential: Optional["Credential"] = Relationship(
        back_populates="person", sa_relationship_kwargs={"uselist": False}
    )
    student: Optional["Student"] = Relationship(
        back_populates="person", sa_relationship_kwargs={"uselist": False}
    )
    faculty: Optional["Faculty"] = Relationship(
        back_populates="person", sa_relationship_kwargs={"uselist": False}
    )

    def __str__(self) -> str:
        return f"{self.preferred_name or self.first_name} {self.last_name}"


class Credential(SQLModel, table=True):
    """How a person proves who they are.

    This table is separate from person so that the hash never rides along
    with an ordinary person query, and so that the whole table can be
    dropped if the app later moves to single sign-on.

    Store the output of a password hashing function such as bcrypt.
    Never store a plain password, and never store a bare SHA hash.
    """

    person_id: int = Field(foreign_key="person.person_id", primary_key=True)
    password_hash: str
    updated_at: datetime = Field(default_factory=utcnow)
    must_change: bool = Field(
        default=False, description="Force a reset at the next login"
    )

    person: Person = Relationship(back_populates="credential")

    def __str__(self) -> str:
        return f"credential for person {self.person_id}"


# --------------------------------------------------------------------------
# Calendar and catalog
# --------------------------------------------------------------------------


class Term(SQLModel, table=True):
    """One academic term.

    The term_code follows the MyMadison convention: a leading 1, then two
    digits for the year, then one digit for the season (1=Spring, 5=Summer,
    8=Fall). For example, 1268 is Fall 2026.
    """

    term_code: int = Field(primary_key=True)
    year: int
    season: str = Field(description="Spring, Summer, or Fall")
    start_date: date
    end_date: date

    def __str__(self) -> str:
        return f"{self.season} {self.year}"


class CatalogYear(SQLModel, table=True):
    """One edition of the undergraduate catalog.

    A catalog year is named for the fall it takes effect, so 2026 means the
    2026-2027 catalog. A student is bound to exactly one catalog year, which
    governs the major, every minor, and general education together. Students
    may elect to move to a later catalog year, and the move applies to all
    requirements at once.
    """

    __tablename__ = "catalog_year"

    catalog_year: int = Field(primary_key=True)
    start_term: int = Field(foreign_key="term.term_code")
    description: Optional[str] = None

    def __str__(self) -> str:
        return f"{self.catalog_year}-{self.catalog_year + 1}"


class Subject(SQLModel, table=True):
    """A course prefix, such as CS or MATH."""

    code: str = Field(primary_key=True)
    name: str

    courses: list["Course"] = Relationship(back_populates="subject")

    def __str__(self) -> str:
        return self.code


class Course(SQLModel, table=True):
    """The stable identity of a course, independent of catalog year.

    Every other table that means "a course" points at course_id here.
    Anything that varies by catalog year belongs to the course catalog
    team's tables, which also reference course_id.

    The title and credits columns hold the most recent values, so that the
    other teams can display a course without joining through a catalog year.
    """

    __table_args__ = (UniqueConstraint("subject_code", "number"),)

    course_id: Optional[int] = Field(default=None, primary_key=True)
    subject_code: str = Field(foreign_key="subject.code", index=True)
    number: str = Field(description="Catalog number as text, such as 149 or 445")
    title: str
    credits: Decimal = Field(default=Decimal("3.0"), max_digits=3, decimal_places=1)
    is_active: bool = Field(default=True)

    subject: Subject = Relationship(back_populates="courses")

    def __str__(self) -> str:
        return f"{self.subject_code} {self.number}"


# --------------------------------------------------------------------------
# Subtypes of Person
# --------------------------------------------------------------------------


class Student(SQLModel, table=True):
    """A person pursuing a degree.

    The catalog_year and status columns hold current values only. The
    advising team owns the history of both: when a status changed and why,
    and when a student moved from one catalog year to another.
    """

    person_id: int = Field(foreign_key="person.person_id", primary_key=True)
    student_number: str = Field(unique=True)
    admit_term: int = Field(foreign_key="term.term_code")
    catalog_year: int = Field(foreign_key="catalog_year.catalog_year")
    status: str = Field(
        default="active",
        index=True,
        description="active, graduated, inactive, or left_major",
    )
    expected_grad_term: Optional[int] = Field(
        default=None, foreign_key="term.term_code"
    )

    person: Person = Relationship(back_populates="student")

    def __str__(self) -> str:
        return f"{self.student_number} ({self.status})"


class Faculty(SQLModel, table=True):
    """A person who teaches, advises, or leads a program.

    The three flags below are what the API checks before allowing an action.
    A person is a student because a student row exists, and a faculty member
    because this row exists, so there are no flags for those two facts.

    The is_advisor flag means the person may be assigned advisees, which is
    not the same as currently having any. The advising team owns the actual
    assignments; authorization does not depend on that team's tables.
    """

    person_id: int = Field(foreign_key="person.person_id", primary_key=True)
    program: Optional[str] = Field(default=None, description="CS, IT, or null")
    rank: Optional[str] = None
    office: Optional[str] = None
    is_advisor: bool = Field(default=False)
    is_director: bool = Field(
        default=False,
        description="Program director for the program named in the program column",
    )
    is_admin: bool = Field(
        default=False, description="Department leadership; may see all records"
    )

    person: Person = Relationship(back_populates="faculty")

    def __str__(self) -> str:
        return f"{self.person_id} ({self.program or 'unaffiliated'})"


# --------------------------------------------------------------------------
# Utilization
# --------------------------------------------------------------------------


class LoginEvent(SQLModel, table=True):
    """One row per login attempt, successful or not.

    A failed attempt may not match a known person, so person_id is nullable
    and the address that was typed is recorded separately.
    """

    __tablename__ = "login_event"

    login_id: Optional[int] = Field(default=None, primary_key=True)
    person_id: Optional[int] = Field(
        default=None, foreign_key="person.person_id", index=True
    )
    email_attempted: str
    occurred_at: datetime = Field(default_factory=utcnow, index=True)
    succeeded: bool
    ip_address: Optional[str] = None
    user_agent: Optional[str] = None

    def __str__(self) -> str:
        return f"{self.email_attempted} at {self.occurred_at}"


class ActivityEvent(SQLModel, table=True):
    """One row per API request, written by FastAPI middleware.

    There is no session table. A visit is reconstructed by grouping a
    person's events and starting a new visit whenever the gap since the
    previous event exceeds a chosen idle threshold.
    """

    __tablename__ = "activity_event"
    __table_args__ = (Index("ix_activity_person_time", "person_id", "occurred_at"),)

    activity_id: Optional[int] = Field(default=None, primary_key=True)
    person_id: int = Field(foreign_key="person.person_id")
    occurred_at: datetime = Field(default_factory=utcnow)
    method: str = Field(description="GET, POST, PATCH, or DELETE")
    path: str
    status_code: int
    duration_ms: int

    def __str__(self) -> str:
        return f"{self.method} {self.path} ({self.status_code})"