"""Nov 05: select() practice on two schemas, air and the project core.

The air queries are written for you. Problems 1 and 2 have the same shape
(join, group, count), and so do problems 3 and 4 (find the rows that have no
match). Read each air query, then write the core query with the same shape.
Each docstring says what the answer should look like, so you can check your
own work. Nothing is submitted.

Run every problem, or only one:

    python practice.py
    python practice.py 3

The air classes are in air_models.py, next to this file. The core classes are
in src/planner/core/models.py in the planner repository.
"""

import sys
from datetime import date

# Some of these are unused until you finish the TODOs.
from air_models import Aircraft, Airport, Flight
from planner.core import Course, Student, Subject, Term
from sqlalchemy import exists, func, text
from sqlmodel import Session, SQLModel, create_engine, select

USERNAME = "your_username"  # TODO: your eID, as in your DATABASE_URL

air = create_engine(f"postgresql+psycopg://{USERNAME}@data.cs.jmu.edu/air")
core = create_engine(f"postgresql+psycopg://{USERNAME}@data.cs.jmu.edu/sec1")


def example(session: Session) -> list:
    """Average velocity of the aircraft leaving Barcelona (BCN).

    Tuesday's SQL, from air_demo.sql:

        SELECT avg(velocity)
        FROM aircraft AS a
          JOIN flight AS f ON a.code = f.aircraft_code
        WHERE f.departure_airport = 'BCN';

    Answer: one row, about 844.59. Run this one first: if it works, both of
    your connections are set up.
    """
    statement = (
        select(func.avg(Aircraft.velocity))
        .join(Flight, Flight.aircraft_code == Aircraft.code)
        .where(Flight.departure_airport == "BCN")
    )
    return session.exec(statement).all()


def busiest_airports(session: Session) -> list:
    """AIR. The five airports with the most departing flights.

    Show the airport code, name, city, and number of departures, most first.
    Flight refers to Airport twice, so the join says which column the join is on.

    Answer: 5 rows, starting with
    ('ATL', 'Hartsfield Jackson Atlanta International Airport', 'ATLANTA', 14820)
    """
    departures = func.count(Flight.flight_id).label("departures")
    statement = (
        select(Airport.airport_code, Airport.airport_name, Airport.city, departures)
        .join(Flight, Flight.departure_airport == Airport.airport_code)
        .group_by(Airport.airport_code)
        .order_by(departures.desc())
        .limit(5)
    )
    return session.exec(statement).all()


def admitted_by_term(session: Session) -> list:
    """CORE. How many students were admitted in each term?

    Show the term code, season, year, and number of students, in term order.
    Look in the Student and Term classes for the columns to join on.

    Answer: 7 rows, starting with (1238, <TermSeason.Fall: 'Fall'>, 2023, 60)
    """
    return []  # TODO


def airports_never_departed(session: Session) -> list:
    """AIR. Airports that no flight departs from, as Airport objects by code.

    Uses ~exists(), as on the "Subqueries in WHERE" slide.

    Answer: 45 airports, starting with ASF in ASTRAKHAN.
    """
    never = ~exists().where(Flight.departure_airport == Airport.airport_code)
    statement = select(Airport).where(never).order_by(Airport.airport_code)
    return session.exec(statement).all()


def subjects_few_courses(session: Session) -> list:
    """CORE. Subjects with fewer than 3 active courses, including those with none.

    Show the subject code, name, and number of active courses, fewest first,
    then by code. A subject with no active courses still has a row.

    Answer: 16 rows, starting with ('FL', 'Foreign Language', 0). If you get
    15, look at where the is_active condition went (Outer Join slide).
    """
    return []  # TODO


class Departure(SQLModel):
    """One row of the materialized view flight_departure (not a table)."""

    flight_id: int
    departure_airport: str
    departure_date: date
    num_passengers: int


def fullest_flights(session: Session, airport: str = "IAD") -> list[Departure]:
    """AIR. The five flights from an airport with the most passengers.

    SQLModel has no table class for a view, so the SQL is written with
    text(), the airport is bound as :airport, and each row becomes a
    Departure object, as on the "text() With Parameters" slide.

    Answer for IAD: 5 rows, starting with
    Departure(flight_id=434171, departure_airport='IAD',
              departure_date=datetime.date(2023, 9, 20), num_passengers=600)
    """
    sql = text(
        "SELECT * FROM flight_departure WHERE departure_airport = :airport "
        "ORDER BY num_passengers DESC, flight_id LIMIT 5"
    )
    rows = session.exec(sql, params={"airport": airport}).mappings()
    return [Departure.model_validate(row) for row in rows]


PROBLEMS = [
    (air, example),
    (air, busiest_airports),
    (core, admitted_by_term),
    (air, airports_never_departed),
    (core, subjects_few_courses),
    (air, fullest_flights),
]

if __name__ == "__main__":
    numbers = [int(arg) for arg in sys.argv[1:]] or range(len(PROBLEMS))
    for number in numbers:
        engine, problem = PROBLEMS[number]
        with Session(engine) as session:
            rows = problem(session)
        print(f"\n{number}. {problem.__name__}: {len(rows)} rows")
        for row in rows[:10]:
            print("  ", row)
