from sqlalchemy import Column, Integer, String, Float, Date, Index
from core.database import Base


class Flight(Base):
    __tablename__ = "flights"

    id = Column(Integer, primary_key=True, autoincrement=True)

    year = Column(Integer, nullable=False)
    month = Column(Integer, nullable=False)
    fl_date = Column(Date, nullable=False)

    carrier = Column(String(5))
    flight_num = Column(String(10))
    tail_number = Column(String(10))

    origin = Column(String(5), nullable=False)
    dest = Column(String(5), nullable=False)

    crs_dep_time = Column(Integer)  # scheduled departure, HHMM format (e.g. 1430)

    arr_delay = Column(Float)
    dep_delay = Column(Float)
    arr_del15 = Column(Integer)  # 1 if delayed >15min

    # delay cause fields only populated when arr_del15 = 1
    carrier_delay = Column(Float)
    weather_delay = Column(Float)
    nas_delay = Column(Float)
    security_delay = Column(Float)
    late_aircraft_delay = Column(Float)

    cancelled = Column(Integer, default=0)
    diverted = Column(Integer, default=0)
    distance = Column(Float)

    __table_args__ = (
        Index("idx_origin", "origin"),
        Index("idx_dest", "dest"),
        Index("idx_origin_dest", "origin", "dest"),
        Index("idx_year_month", "year", "month"),
    )
