from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Crewmember, Flightcrewassignment
from flight_management.models import Flight


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Crewmember]:
    query = db.query(Crewmember)
    
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(
            (Crewmember.first_name.ilike(search_term)) |
            (Crewmember.last_name.ilike(search_term)) |
            (Crewmember.email.ilike(search_term)) |
            (Crewmember.employee_number.ilike(search_term))
        )
    
    if filters.get("status"):
        query = query.filter(Crewmember.status == filters["status"])
    
    if filters.get("role"):
        query = query.filter(Crewmember.role == filters["role"])
    
    return query.order_by(Crewmember.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


def get_by_employee_number(db: Session, employee_number: str) -> Optional[Crewmember]:
    return db.query(Crewmember).filter(Crewmember.employee_number == employee_number).first()


def get_by_email(db: Session, email: str) -> Optional[Crewmember]:
    return db.query(Crewmember).filter(Crewmember.email == email).first()


def get_with_details(db: Session, entity_id: str) -> Optional[Crewmember]:
    return (
        db.query(Crewmember)
        .options(
            joinedload(Crewmember.flight_crew_assignments)
                .joinedload(Flightcrewassignment.flight)
                .joinedload(Flight.route)
                .joinedload("origin_airport"),
            joinedload(Crewmember.flight_crew_assignments)
                .joinedload(Flightcrewassignment.flight)
                .joinedload(Flight.route)
                .joinedload("destination_airport"),
        )
        .filter(Crewmember.id == entity_id)
        .first()
    )


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


def list_all_assignments(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Flightcrewassignment]:
    query = db.query(Flightcrewassignment)
    
    if filters.get("flight_id"):
        query = query.filter(Flightcrewassignment.flight_id == filters["flight_id"])
    
    if filters.get("crew_member_id"):
        query = query.filter(Flightcrewassignment.crew_member_id == filters["crew_member_id"])
    
    if filters.get("assignment_status"):
        query = query.filter(Flightcrewassignment.assignment_status == filters["assignment_status"])
    
    if filters.get("assigned_role"):
        query = query.filter(Flightcrewassignment.assigned_role == filters["assigned_role"])
    
    return query.order_by(Flightcrewassignment.created_at.desc()).limit(limit).offset(offset).all()


def create_assignment(db: Session, data: dict) -> Flightcrewassignment:
    assignment = Flightcrewassignment(**data)
    db.add(assignment)
    db.flush()
    return assignment


def update_assignment(db: Session, entity_id: str, data: dict) -> Optional[Flightcrewassignment]:
    assignment = get_assignment_by_id(db, entity_id)
    if not assignment:
        return None
    for key, value in data.items():
        setattr(assignment, key, value)
    db.flush()
    return assignment


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


def get_assignment_by_flight_and_crew(db: Session, flight_id: str, crew_member_id: str) -> Optional[Flightcrewassignment]:
    return (
        db.query(Flightcrewassignment)
        .filter(
            Flightcrewassignment.flight_id == flight_id,
            Flightcrewassignment.crew_member_id == crew_member_id
        )
        .first()
    )


def get_crew_assignments_for_flight(db: Session, flight_id: str) -> List[Flightcrewassignment]:
    return (
        db.query(Flightcrewassignment)
        .filter(Flightcrewassignment.flight_id == flight_id)
        .all()
    )