from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from datetime import datetime, date
from .models import Flight, Fareclass


# Fareclass repository functions
def get_by_id(db: Session, entity_id: str) -> Optional[Fareclass]:
    return db.query(Fareclass).filter(Fareclass.id == entity_id).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Fareclass]:
    query = db.query(Fareclass)
    
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(Fareclass.name.ilike(search_term))
    
    if filters.get("class_type"):
        query = query.filter(Fareclass.class_type == filters["class_type"])
    
    return query.order_by(Fareclass.created_at.desc()).limit(limit).offset(offset).all()


def create(db: Session, data: dict) -> Fareclass:
    fareclass = Fareclass(**data)
    db.add(fareclass)
    db.flush()
    return fareclass


def update(db: Session, entity_id: str, data: dict) -> Optional[Fareclass]:
    fareclass = get_by_id(db, entity_id)
    if not fareclass:
        return None
    for key, value in data.items():
        setattr(fareclass, key, value)
    db.flush()
    return fareclass


def delete(db: Session, entity_id: str) -> bool:
    fareclass = get_by_id(db, entity_id)
    if not fareclass:
        return False
    db.delete(fareclass)
    db.flush()
    return True


def get_fareclass_by_name(db: Session, name: str) -> Optional[Fareclass]:
    return db.query(Fareclass).filter(Fareclass.name == name).first()


# Flight repository functions
def get_flight_by_id(db: Session, entity_id: str) -> Optional[Flight]:
    return db.query(Flight).filter(Flight.id == entity_id).first()


def list_flights(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Flight]:
    query = db.query(Flight)
    
    if filters.get("status"):
        query = query.filter(Flight.status == filters["status"])
    
    if filters.get("aircraft_id"):
        query = query.filter(Flight.aircraft_id == filters["aircraft_id"])
    
    if filters.get("route_id"):
        query = query.filter(Flight.route_id == filters["route_id"])
    
    if filters.get("date"):
        start_of_day = datetime.combine(filters["date"], datetime.min.time())
        end_of_day = datetime.combine(filters["date"], datetime.max.time())
        query = query.filter(Flight.scheduled_departure >= start_of_day, Flight.scheduled_departure <= end_of_day)
    
    return query.order_by(Flight.scheduled_departure.desc()).limit(limit).offset(offset).all()


def create_flight(db: Session, data: dict) -> Flight:
    flight = Flight(**data)
    db.add(flight)
    db.flush()
    return flight


def update_flight(db: Session, entity_id: str, data: dict) -> Optional[Flight]:
    flight = get_flight_by_id(db, entity_id)
    if not flight:
        return None
    for key, value in data.items():
        setattr(flight, key, value)
    db.flush()
    return flight


def delete_flight(db: Session, entity_id: str) -> bool:
    flight = get_flight_by_id(db, entity_id)
    if not flight:
        return False
    db.delete(flight)
    db.flush()
    return True


def get_flight_with_details(db: Session, flight_id: str) -> Optional[Flight]:
    return (
        db.query(Flight)
        .options(
            joinedload(Flight.route)
                .joinedload("origin_airport"),
            joinedload(Flight.route)
                .joinedload("destination_airport"),
            joinedload(Flight.aircraft),
            subqueryload(Flight.bookings),
            subqueryload(Flight.flight_crew_assignments)
                .joinedload("crew_member"),
        )
        .filter(Flight.id == flight_id)
        .first()
    )


def search_flights(db: Session, origin_code: str, destination_code: str, flight_date: date) -> List[Flight]:
    from airport_route_management.models import Route, Airport
    
    start_of_day = datetime.combine(flight_date, datetime.min.time())
    end_of_day = datetime.combine(flight_date, datetime.max.time())
    
    return (
        db.query(Flight)
        .join(Route, Flight.route_id == Route.id)
        .join(Airport, Route.origin_airport_id == Airport.id)
        .filter(
            Airport.code == origin_code,
            Flight.scheduled_departure >= start_of_day,
            Flight.scheduled_departure <= end_of_day,
            Flight.status == "Scheduled"
        )
        .join(Airport, Route.destination_airport_id == Airport.id)
        .filter(Airport.code == destination_code)
        .options(
            joinedload(Flight.route)
                .joinedload("origin_airport"),
            joinedload(Flight.route)
                .joinedload("destination_airport"),
            joinedload(Flight.aircraft)
        )
        .order_by(Flight.scheduled_departure)
        .all()
    )


def get_flights_by_aircraft(db: Session, aircraft_id: str, start_date: datetime, end_date: datetime) -> List[Flight]:
    return (
        db.query(Flight)
        .filter(
            Flight.aircraft_id == aircraft_id,
            Flight.scheduled_departure >= start_date,
            Flight.scheduled_departure <= end_date,
            Flight.status.in_(["Scheduled", "Boarding", "Departed"])
        )
        .order_by(Flight.scheduled_departure)
        .all()
    )