"""SQLModel classes for the air database on data.cs.jmu.edu.

The air database already exists, so these classes only read it: never call
create_all() with them. They map the ten tables of postgres_air, the schema
drawn in postgres_air.svg. Every table also has an update_ts column, left out
here because nothing in class uses it. A model does not have to map every
column, only the ones you select.

Four of the tables are large, so filter on an indexed column before you join
them: booking (6 million rows), booking_leg (18 million), passenger (16
million), and boarding_pass (25 million). The others are small enough to scan.
"""

from datetime import datetime
from decimal import Decimal

from sqlalchemy import TIMESTAMP, Column, Integer
from sqlmodel import Field, SQLModel

TIMESTAMPTZ = TIMESTAMP(timezone=True)


class Aircraft(SQLModel, table=True):
    """One aircraft model, such as the Airbus 380. 12 rows."""

    code: str = Field(primary_key=True)
    model: str | None = None
    range: Decimal
    # The column is named class, a Python keyword, so the attribute is class_.
    class_: int = Field(sa_column=Column("class", Integer, nullable=False))
    velocity: Decimal


class Airport(SQLModel, table=True):
    """One airport, by its three-letter code. 692 rows."""

    airport_code: str = Field(primary_key=True)
    airport_name: str
    city: str
    airport_tz: str
    continent: str | None = None
    iso_country: str | None = None
    iso_region: str | None = None
    intnl: bool


class Flight(SQLModel, table=True):
    """One scheduled flight. 683,000 rows.

    Flight refers to Airport twice, so a join between them must always say
    which column it means: departure_airport or arrival_airport.
    """

    flight_id: int = Field(primary_key=True)
    flight_no: str
    scheduled_departure: datetime = Field(sa_type=TIMESTAMPTZ)
    scheduled_arrival: datetime = Field(sa_type=TIMESTAMPTZ)
    departure_airport: str = Field(foreign_key="airport.airport_code")
    arrival_airport: str = Field(foreign_key="airport.airport_code")
    status: str
    aircraft_code: str = Field(foreign_key="aircraft.code")
    actual_departure: datetime | None = Field(default=None, sa_type=TIMESTAMPTZ)
    actual_arrival: datetime | None = Field(default=None, sa_type=TIMESTAMPTZ)


class FrequentFlyer(SQLModel, table=True):
    """One member of the frequent flyer program."""

    __tablename__ = "frequent_flyer"

    frequent_flyer_id: int = Field(primary_key=True)
    first_name: str
    last_name: str
    title: str
    card_num: str
    level: int
    award_points: int
    email: str
    phone: str


class Account(SQLModel, table=True):
    """One customer account, which makes bookings."""

    account_id: int = Field(primary_key=True)
    login: str
    first_name: str
    last_name: str
    frequent_flyer_id: int | None = Field(
        default=None, foreign_key="frequent_flyer.frequent_flyer_id"
    )


class Phone(SQLModel, table=True):
    """One phone number of an account."""

    phone_id: int = Field(primary_key=True)
    account_id: int | None = Field(default=None, foreign_key="account.account_id")
    phone: str | None = None
    phone_type: str | None = None
    primary_phone: bool | None = None


class Booking(SQLModel, table=True):
    """One purchase of a trip, for one or more passengers. Large."""

    booking_id: int = Field(primary_key=True)
    booking_ref: str = Field(unique=True)
    booking_name: str | None = None
    account_id: int | None = Field(default=None, foreign_key="account.account_id")
    email: str
    phone: str
    price: Decimal | None = None


class BookingLeg(SQLModel, table=True):
    """One flight of a booking; leg_num orders the flights of a trip. Large."""

    __tablename__ = "booking_leg"

    booking_leg_id: int = Field(primary_key=True)
    booking_id: int = Field(foreign_key="booking.booking_id")
    flight_id: int = Field(foreign_key="flight.flight_id")
    leg_num: int | None = None
    is_returning: bool | None = None


class Passenger(SQLModel, table=True):
    """One person traveling on a booking. Large."""

    passenger_id: int = Field(primary_key=True)
    booking_id: int = Field(foreign_key="booking.booking_id")
    booking_ref: str | None = None
    passenger_no: int | None = None
    first_name: str
    last_name: str
    account_id: int | None = Field(default=None, foreign_key="account.account_id")
    age: int | None = None


class BoardingPass(SQLModel, table=True):
    """One passenger's pass for one leg of a booking. Large."""

    __tablename__ = "boarding_pass"

    pass_id: int = Field(primary_key=True)
    passenger_id: int | None = Field(default=None, foreign_key="passenger.passenger_id")
    booking_leg_id: int | None = Field(default=None, foreign_key="booking_leg.booking_leg_id")
    seat: str | None = None
    boarding_time: datetime | None = Field(default=None, sa_type=TIMESTAMPTZ)
    precheck: bool | None = None
