from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from typing import List, Optional
from datetime import date, datetime, timedelta
from . import repository
from .schema import (
    CrewmemberCreate,
    CrewmemberUpdate,
    CrewmemberResponse,
    CrewmemberDetailResponse,
    FlightcrewassignmentCreate,
    FlightcrewassignmentUpdate,
    FlightcrewassignmentResponse,
)
from flight_management.models import Flight


def _get_or_raise(db: Session, entity_id: str, repo_module) -> any:
    entity = repo_module.get_by_id(db, entity_id)
    if not entity:
        raise HTTPException(status_code=404, detail=f"Entity with id {entity_id} not found")
    return entity


# Crewmember handlers
def create_crew_member(db: Session, data: CrewmemberCreate) -> CrewmemberResponse:
    existing_employee = repository.get_by_employee_number(db, data.employee_number)
    if existing_employee:
        raise HTTPException(
            status_code=409,
            detail=f"Crew member with employee number {data.employee_number} already exists"
        )
    
    existing_email = repository.get_by_email(db, data.email)
    if existing_email:
        raise HTTPException(
            status_code=409,
            detail=f"Crew member with email {data.email} already exists"
        )
    
    try:
        crew_member = repository.create(db, data.model_dump())
        db.commit()
        db.refresh(crew_member)
        return CrewmemberResponse.model_validate(crew_member)
    except Exception:
        db.rollback()
        raise


def list_crew_members(
    db: Session,
    limit: int,
    offset: int,
    search: Optional[str] = None,
    status: Optional[str] = None,
    role: Optional[str] = None,
) -> List[CrewmemberResponse]:
    filters = {}
    if search:
        filters["search"] = search
    if status:
        filters["status"] = status
    if role:
        filters["role"] = role
    
    crew_members = repository.list_all(db, limit, offset, **filters)
    return [CrewmemberResponse.model_validate(cm) for cm in crew_members]


def get_crew_member(db: Session, crew_member_id: str) -> CrewmemberResponse:
    crew_member = _get_or_raise(db, crew_member_id, repository)
    return CrewmemberResponse.model_validate(crew_member)


def update_crew_member(db: Session, crew_member_id: str, data: CrewmemberUpdate) -> CrewmemberResponse:
    crew_member = _get_or_raise(db, crew_member_id, repository)
    
    update_data = data.model_dump(exclude_unset=True)
    
    if "employee_number" in update_data and update_data["employee_number"] != crew_member.employee_number:
        existing = repository.get_by_employee_number(db, update_data["employee_number"])
        if existing:
            raise HTTPException(
                status_code=409,
                detail=f"Crew member with employee number {update_data['employee_number']} already exists"
            )
    
    if "email" in update_data and update_data["email"] != crew_member.email:
        existing = repository.get_by_email(db, update_data["email"])
        if existing:
            raise HTTPException(
                status_code=409,
                detail=f"Crew member with email {update_data['email']} already exists"
            )
    
    try:
        updated_crew_member = repository.update(db, crew_member_id, update_data)
        db.commit()
        db.refresh(updated_crew_member)
        return CrewmemberResponse.model_validate(updated_crew_member)
    except Exception:
        db.rollback()
        raise


def delete_crew_member(db: Session, crew_member_id: str) -> dict:
    crew_member = _get_or_raise(db, crew_member_id, repository)
    
    active_assignments = [
        a for a in crew_member.flight_crew_assignments
        if a.assignment_status in ["Assigned", "Confirmed"]
    ]
    if active_assignments:
        raise HTTPException(
            status_code=400,
            detail="Cannot delete crew member with active flight assignments"
        )
    
    try:
        repository.delete(db, crew_member_id)
        db.commit()
        return {"message": "Crew member deleted successfully"}
    except Exception:
        db.rollback()
        raise


def get_crew_member_details(db: Session, crew_member_id: str) -> CrewmemberDetailResponse:
    crew_member = repository.get_with_details(db, crew_member_id)
    if not crew_member:
        raise HTTPException(status_code=404, detail=f"Crew member with id {crew_member_id} not found")
    return CrewmemberDetailResponse.model_validate(crew_member)


def get_crew_member_schedule(
    db: Session,
    crew_member_id: str,
    start_date: date,
    end_date: date
) -> List[FlightcrewassignmentResponse]:
    crew_member = _get_or_raise(db, crew_member_id, repository)
    
    if start_date > end_date:
        raise HTTPException(
            status_code=400,
            detail="Start date must be before or equal to end date"
        )
    
    start_datetime = datetime.combine(start_date, datetime.min.time())
    end_datetime = datetime.combine(end_date, datetime.max.time())
    
    assignments = (
        db.query(repository.Flightcrewassignment)
        .join(Flight)
        .filter(
            repository.Flightcrewassignment.crew_member_id == crew_member_id,
            Flight.scheduled_departure >= start_datetime,
            Flight.scheduled_departure <= end_datetime
        )
        .order_by(Flight.scheduled_departure)
        .all()
    )
    
    return [FlightcrewassignmentResponse.model_validate(a) for a in assignments]


# Flightcrewassignment handlers
def create_flight_crew_assignment(db: Session, data: FlightcrewassignmentCreate) -> FlightcrewassignmentResponse:
    flight = db.query(Flight).filter(Flight.id == data.flight_id).first()
    if not flight:
        raise HTTPException(status_code=404, detail=f"Flight with id {data.flight_id} not found")
    
    crew_member = repository.get_by_id(db, data.crew_member_id)
    if not crew_member:
        raise HTTPException(status_code=404, detail=f"Crew member with id {data.crew_member_id} not found")
    
    if crew_member.status != "Active":
        raise HTTPException(
            status_code=400,
            detail=f"Crew member is not active (current status: {crew_member.status})"
        )
    
    if crew_member.certification_expiry and crew_member.certification_expiry < flight.scheduled_departure.date():
        raise HTTPException(
            status_code=400,
            detail="Crew member certification will be expired at time of flight"
        )
    
    existing = repository.get_assignment_by_flight_and_crew(db, data.flight_id, data.crew_member_id)
    if existing:
        raise HTTPException(
            status_code=409,
            detail="Crew member is already assigned to this flight"
        )
    
    rest_period_start = flight.scheduled_departure - timedelta(hours=12)
    rest_period_end = flight.scheduled_arrival + timedelta(hours=12) if flight.scheduled_arrival else flight.scheduled_departure + timedelta(hours=24)
    
    overlapping = (
        db.query(repository.Flightcrewassignment)
        .join(Flight)
        .filter(
            repository.Flightcrewassignment.crew_member_id == data.crew_member_id,
            repository.Flightcrewassignment.assignment_status.in_(["Assigned", "Confirmed"]),
            Flight.scheduled_departure < rest_period_end,
            Flight.scheduled_arrival > rest_period_start
        )
        .first()
    )
    
    if overlapping:
        raise HTTPException(
            status_code=409,
            detail="Crew member has overlapping flight assignment or insufficient rest period"
        )
    
    try:
        assignment = repository.create_assignment(db, data.model_dump())
        db.commit()
        db.refresh(assignment)
        return FlightcrewassignmentResponse.model_validate(assignment)
    except Exception:
        db.rollback()
        raise


def list_flight_crew_assignments(
    db: Session,
    limit: int,
    offset: int,
    flight_id: Optional[str] = None,
    crew_member_id: Optional[str] = None,
    assignment_status: Optional[str] = None,
    assigned_role: Optional[str] = None,
) -> List[FlightcrewassignmentResponse]:
    filters = {}
    if flight_id:
        filters["flight_id"] = flight_id
    if crew_member_id:
        filters["crew_member_id"] = crew_member_id
    if assignment_status:
        filters["assignment_status"] = assignment_status
    if assigned_role:
        filters["assigned_role"] = assigned_role
    
    assignments = repository.list_all_assignments(db, limit, offset, **filters)
    return [FlightcrewassignmentResponse.model_validate(a) for a in assignments]


def get_flight_crew_assignment(db: Session, assignment_id: str) -> FlightcrewassignmentResponse:
    assignment = repository.get_assignment_by_id(db, assignment_id)
    if not assignment:
        raise HTTPException(status_code=404, detail=f"Assignment with id {assignment_id} not found")
    return FlightcrewassignmentResponse.model_validate(assignment)


def update_flight_crew_assignment(
    db: Session,
    assignment_id: str,
    data: FlightcrewassignmentUpdate
) -> FlightcrewassignmentResponse:
    assignment = repository.get_assignment_by_id(db, assignment_id)
    if not assignment:
        raise HTTPException(status_code=404, detail=f"Assignment with id {assignment_id} not found")
    
    update_data = data.model_dump(exclude_unset=True)
    
    if "crew_member_id" in update_data and update_data["crew_member_id"] != assignment.crew_member_id:
        crew_member = repository.get_by_id(db, update_data["crew_member_id"])
        if not crew_member:
            raise HTTPException(status_code=404, detail=f"Crew member with id {update_data['crew_member_id']} not found")
        
        if crew_member.status != "Active":
            raise HTTPException(
                status_code=400,
                detail=f"Crew member is not active (current status: {crew_member.status})"
            )
    
    try:
        updated_assignment = repository.update_assignment(db, assignment_id, update_data)
        db.commit()
        db.refresh(updated_assignment)
        return FlightcrewassignmentResponse.model_validate(updated_assignment)
    except Exception:
        db.rollback()
        raise


def delete_flight_crew_assignment(db: Session, assignment_id: str) -> dict:
    assignment = repository.get_assignment_by_id(db, assignment_id)
    if not assignment:
        raise HTTPException(status_code=404, detail=f"Assignment with id {assignment_id} not found")
    
    if assignment.assignment_status == "Completed":
        raise HTTPException(
            status_code=400,
            detail="Cannot delete completed assignment"
        )
    
    try:
        repository.delete_assignment(db, assignment_id)
        db.commit()
        return {"message": "Flight crew assignment deleted successfully"}
    except Exception:
        db.rollback()
        raise