import enum
from sqlalchemy import Column, String, Float, Integer, DateTime, Index
from sqlalchemy.sql import func
from core.database import Base


class DelayRisk(str, enum.Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    SEVERE = "severe"


class Route(Base):
    __tablename__ = "routes"

    id = Column(Integer, primary_key=True, autoincrement=True)
    origin = Column(String(5), nullable=False)
    dest = Column(String(5), nullable=False)

    flight_count = Column(Integer, default=0)
    avg_delay = Column(Float)
    median_delay = Column(Float)
    std_delay = Column(Float)
    pct_delayed = Column(Float)
    avg_distance = Column(Float)

    avg_carrier_delay = Column(Float)
    avg_weather_delay = Column(Float)
    avg_nas_delay = Column(Float)
    avg_late_aircraft_delay = Column(Float)

    delay_risk = Column(String(10))
    last_updated = Column(DateTime, server_default=func.now())

    __table_args__ = (
        Index("idx_routes_origin_dest", "origin", "dest", unique=True),
        Index("idx_routes_delay_risk", "delay_risk"),
    )
