"""Pre-computed airport stats, updated by compute_aggregates.py"""
from sqlalchemy import Column, String, Float, Integer, DateTime
from sqlalchemy.sql import func
from core.database import Base


class Airport(Base):
    __tablename__ = "airports"

    iata = Column(String(5), primary_key=True)
    name = Column(String(100))
    city = Column(String(50))
    state = Column(String(2))
    lat = Column(Float)
    lon = Column(Float)

    total_flights = Column(Integer, default=0)
    total_departures = Column(Integer, default=0)
    total_arrivals = Column(Integer, default=0)

    avg_arr_delay = Column(Float)
    avg_dep_delay = Column(Float)
    pct_delayed = Column(Float)       # fraction of flights delayed >15min
    delay_score = Column(Float)       # pct_delayed * sqrt(total_flights)

    # delay cause totals (minutes)
    total_carrier_delay = Column(Float)
    total_weather_delay = Column(Float)
    total_nas_delay = Column(Float)
    total_late_aircraft_delay = Column(Float)

    # delay cause breakdown (%)
    carrier_pct = Column(Float)
    weather_pct = Column(Float)
    nas_pct = Column(Float)
    late_aircraft_pct = Column(Float)

    recovery_score = Column(Float)       # avg minutes recovered after late inbound
    propagation_legs = Column(Integer)   # sample size for recovery calc

    last_updated = Column(DateTime, server_default=func.now())
