from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Aircraft, Seat


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Aircraft]:
    query = db.query(Aircraft)
    
    if "status" in filters and filters["status"]:
        query = query.filter(Aircraft.status == filters["status"])
    
    if "search" in filters and filters["search"]:
        search_term = f"%{filters['search']}%"
        query = query.filter(
            (Aircraft.registration_number.ilike(search_term)) |
            (Aircraft.model.ilike(search_term)) |
            (Aircraft.manufacturer.ilike(search_term))
        )
    
    return query.order_by(Aircraft.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


def get_by_registration_number(db: Session, registration_number: str) -> Optional[Aircraft]:
    return db.query(Aircraft).filter(Aircraft.registration_number == registration_number).first()


def get_with_details(db: Session, aircraft_id: str) -> Optional[Aircraft]:
    return (
        db.query(Aircraft)
        .options(
            subqueryload(Aircraft.seats),
            subqueryload(Aircraft.flights)
        )
        .filter(Aircraft.id == aircraft_id)
        .first()
    )


def get_seat_by_id(db: Session, seat_id: str) -> Optional[Seat]:
    return db.query(Seat).filter(Seat.id == seat_id).first()


def list_seats(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Seat]:
    query = db.query(Seat)
    
    if "aircraft_id" in filters and filters["aircraft_id"]:
        query = query.filter(Seat.aircraft_id == filters["aircraft_id"])
    
    if "class_type" in filters and filters["class_type"]:
        query = query.filter(Seat.class_type == filters["class_type"])
    
    return query.order_by(Seat.seat_number).limit(limit).offset(offset).all()


def create_seat(db: Session, data: dict) -> Seat:
    seat = Seat(**data)
    db.add(seat)
    db.flush()
    return seat


def update_seat(db: Session, seat_id: str, data: dict) -> Optional[Seat]:
    seat = get_seat_by_id(db, seat_id)
    if not seat:
        return None
    
    for key, value in data.items():
        setattr(seat, key, value)
    
    db.flush()
    return seat


def delete_seat(db: Session, seat_id: str) -> bool:
    seat = get_seat_by_id(db, seat_id)
    if not seat:
        return False
    
    db.delete(seat)
    db.flush()
    return True


def get_seat_by_aircraft_and_number(db: Session, aircraft_id: str, seat_number: str) -> Optional[Seat]:
    return db.query(Seat).filter(
        Seat.aircraft_id == aircraft_id,
        Seat.seat_number == seat_number
    ).first()