# Backend Specification

## 1. System Summary

**System Name:** Airlines Management System

**Description:** A streamlined web application to manage core airline operations including flight scheduling, aircraft management, passenger bookings, and crew assignments.

**Purpose:** This backend provides RESTful APIs to support airline operations management, enabling administrators, flight managers, booking agents, and crew members to efficiently manage flights, aircraft, routes, bookings, and crew assignments.

## 2. Source Input Summary

**Source Files:**
- **DDL Schema:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260512_081622/schema.sql`
  - Contains 11 tables: aircraft, airports, routes, fare_classes, flights, seats, passengers, bookings, crew_members, flight_crew_assignments
- **PRD Document:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260512_081622/full_prd.md`
  - Defines 4 user roles, 10 core entities, 6 modules, and comprehensive workflows

**Key Components:**
- 10 core entities with full CRUD operations
- 10 enumeration types for status and classification fields
- 11 relationship mappings between entities
- 5 detail endpoints with eager loading specifications
- 2 batch write services for complex operations
- 6 primary workflows covering flight creation, crew assignment, booking management, and maintenance scheduling

## 3. Generation Mode

**Mode:** Strict

**Implications:**
- All entities, fields, relationships, and enumerations are derived directly from the provided DDL schema and PRD
- No additional entities or fields are introduced beyond those specified
- All API endpoints align with the workflows and business rules documented in the PRD
- Validation rules and business logic strictly follow the requirements outlined in the source documents

## 4. Backend Scope

**Technology Stack:**
- **Framework:** FastAPI (Python)
- **ORM:** SQLAlchemy
- **Database:** SQLite (development)
- **Validation:** Pydantic v2
- **Package Manager:** uv

**Architecture Pattern:**
- Service layer pattern with clear separation of concerns
- Dependency injection using FastAPI's `Depends(get_db)`
- Automatic OpenAPI documentation generation
- RESTful API design principles

**Modules:**

| Module | Entities | Description |
|--------|----------|-------------|
| aircraft_management | Aircraft, Seat | Manages aircraft fleet, seat configurations, and aircraft availability |
| airport_route_management | Airport, Route | Manages airports, routes between airports, and route details |
| flight_management | Flight, Fareclass | Manages flight scheduling, status, pricing, and fare classes |
| crew_management | Crewmember, Flightcrewassignment | Manages crew members, certifications, and flight assignments |
| booking_management | Passenger, Booking | Manages passenger information and flight bookings |

## 5. Roles

| Role | Description | Primary Responsibilities |
|------|-------------|-------------------------|
| Administrator | System administrator with full access | Manage aircraft, airports, routes, fare classes, system configuration |
| Flight Manager | Manages flight operations and scheduling | Create/update flights, assign crew, manage flight status, handle cancellations |
| Booking Agent | Handles passenger bookings and reservations | Create/modify/cancel bookings, manage passenger information, process seat changes |
| Crew Member | Airline staff assigned to flights | View assigned flights, update assignment status, manage personal information |

## 6. Entities and Fields

### 6.1 Aircraft

**Table:** `aircraft`

**Description:** Represents individual airplanes in the fleet with registration details, model information, and seating capacity.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| registration_number | str | Yes | Unique aircraft registration number |
| model | str | Yes | Aircraft model |
| manufacturer | str | Yes | Aircraft manufacturer |
| total_seats | int | Yes | Total seating capacity |
| year_manufactured | Optional[int] | No | Year the aircraft was manufactured |
| status | str | Yes | Aircraft operational status (AircraftStatus enum) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.2 Seat

**Table:** `seats`

**Description:** Represents individual seats on an aircraft with class type and position attributes.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| aircraft_id | str | Yes | Reference to aircraft |
| seat_number | str | Yes | Seat number (e.g., 1A, 2B) |
| class_type | str | Yes | Seat class type (SeatClassType enum) |
| is_window | bool | Yes | Whether seat is by window |
| is_aisle | bool | Yes | Whether seat is by aisle |
| is_exit_row | bool | Yes | Whether seat is in exit row |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.3 Airport

**Table:** `airports`

**Description:** Represents airports with code, name, city, country, and timezone information.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| code | str | Yes | IATA airport code (3 letters) |
| name | str | Yes | Airport name |
| city | str | Yes | City where airport is located |
| country | str | Yes | Country where airport is located |
| timezone | str | Yes | Airport timezone |
| latitude | Optional[float] | No | Airport latitude coordinate |
| longitude | Optional[float] | No | Airport longitude coordinate |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.4 Route

**Table:** `routes`

**Description:** Represents a flight path between two airports with distance and estimated duration.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| origin_airport_id | str | Yes | Reference to origin airport |
| destination_airport_id | str | Yes | Reference to destination airport |
| distance_km | int | Yes | Route distance in kilometers |
| estimated_duration_minutes | int | Yes | Estimated flight duration in minutes |
| status | str | Yes | Route operational status (RouteStatus enum) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.5 Fareclass

**Table:** `fare_classes`

**Description:** Represents pricing tiers with associated rules and pricing multipliers.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| name | str | Yes | Fare class name |
| class_type | str | Yes | Class type category (FareClassType enum) |
| price_multiplier | float | Yes | Price multiplier for base fare |
| baggage_allowance_kg | int | Yes | Baggage allowance in kilograms |
| cancellation_allowed | bool | Yes | Whether cancellation is allowed |
| change_fee_percentage | float | Yes | Fee percentage for changes |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.6 Flight

**Table:** `flights`

**Description:** Represents a scheduled flight instance with departure/arrival times, assigned aircraft, route, pricing, and status.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| flight_number | str | Yes | Flight number |
| route_id | str | Yes | Reference to route |
| aircraft_id | str | Yes | Reference to aircraft |
| scheduled_departure | datetime | Yes | Scheduled departure time |
| scheduled_arrival | datetime | Yes | Scheduled arrival time |
| actual_departure | Optional[datetime] | No | Actual departure time |
| actual_arrival | Optional[datetime] | No | Actual arrival time |
| base_fare | float | Yes | Base fare price |
| status | str | Yes | Flight status (FlightStatus enum) |
| gate | Optional[str] | No | Gate number |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.7 Crewmember

**Table:** `crew_members`

**Description:** Represents airline staff with personal details, role, and certification information.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| employee_number | str | Yes | Unique employee number |
| first_name | str | Yes | First name |
| last_name | str | Yes | Last name |
| email | str | Yes | Email address |
| phone | str | Yes | Phone number |
| date_of_birth | date | Yes | Date of birth |
| hire_date | date | Yes | Date hired |
| role | str | Yes | Crew member role (CrewRole enum) |
| certification_number | Optional[str] | No | Certification number |
| certification_expiry | Optional[date] | No | Certification expiry date |
| status | str | Yes | Employment status (CrewStatus enum) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.8 Flightcrewassignment

**Table:** `flight_crew_assignments`

**Description:** Represents the assignment of crew members to specific flights with their role on that flight.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| flight_id | str | Yes | Reference to flight |
| crew_member_id | str | Yes | Reference to crew member |
| assigned_role | str | Yes | Role assigned for this flight (CrewRole enum) |
| assignment_status | str | Yes | Assignment status (AssignmentStatus enum) |
| notes | Optional[str] | No | Additional notes |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `assignment_status`

### 6.9 Passenger

**Table:** `passengers`

**Description:** Represents individuals who book flights with personal and contact information.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| first_name | str | Yes | First name |
| last_name | str | Yes | Last name |
| email | str | Yes | Email address |
| phone | str | Yes | Phone number |
| date_of_birth | date | Yes | Date of birth |
| passport_number | Optional[str] | No | Passport number |
| nationality | str | Yes | Nationality |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.10 Booking

**Table:** `bookings`

**Description:** Represents a flight reservation made by a passenger with booking status, seat assignment, and payment information.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| booking_reference | str | Yes | Unique booking reference number |
| passenger_id | str | Yes | Reference to passenger |
| flight_id | str | Yes | Reference to flight |
| seat_id | Optional[str] | No | Reference to seat |
| fare_class_id | str | Yes | Reference to fare class |
| booking_status | str | Yes | Booking status (BookingStatus enum) |
| total_price | float | Yes | Total booking price |
| payment_status | str | Yes | Payment status (PaymentStatus enum) |
| payment_method | Optional[str] | No | Payment method used |
| booking_date | datetime | Yes | Date booking was made |
| special_requests | Optional[str] | No | Special requests from passenger |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `booking_status`

## 7. Enumerations

### 7.1 AircraftStatus

| Value | Label |
|-------|-------|
| Active | ACTIVE |
| Maintenance | MAINTENANCE |
| Retired | RETIRED |

### 7.2 RouteStatus

| Value | Label |
|-------|-------|
| Active | ACTIVE |
| Inactive | INACTIVE |

### 7.3 SeatClassType

| Value | Label |
|-------|-------|
| Economy | ECONOMY |
| Premium Economy | PREMIUM_ECONOMY |
| Business | BUSINESS |
| First Class | FIRST_CLASS |

### 7.4 FareClassType

| Value | Label |
|-------|-------|
| Economy | ECONOMY |
| Premium Economy | PREMIUM_ECONOMY |
| Business | BUSINESS |
| First Class | FIRST_CLASS |

### 7.5 FlightStatus

| Value | Label |
|-------|-------|
| Scheduled | SCHEDULED |
| Boarding | BOARDING |
| Departed | DEPARTED |
| Arrived | ARRIVED |
| Cancelled | CANCELLED |
| Delayed | DELAYED |

### 7.6 CrewRole

| Value | Label |
|-------|-------|
| Captain | CAPTAIN |
| First Officer | FIRST_OFFICER |
| Flight Attendant | FLIGHT_ATTENDANT |
| Purser | PURSER |

### 7.7 CrewStatus

| Value | Label |
|-------|-------|
| Active | ACTIVE |
| On Leave | ON_LEAVE |
| Inactive | INACTIVE |

### 7.8 AssignmentStatus

| Value | Label |
|-------|-------|
| Assigned | ASSIGNED |
| Confirmed | CONFIRMED |
| Completed | COMPLETED |
| Cancelled | CANCELLED |

### 7.9 BookingStatus

| Value | Label |
|-------|-------|
| Confirmed | CONFIRMED |
| Pending | PENDING |
| Cancelled | CANCELLED |
| Checked In | CHECKED_IN |
| Boarded | BOARDED |

### 7.10 PaymentStatus

| Value | Label |
|-------|-------|
| Pending | PENDING |
| Completed | COMPLETED |
| Refunded | REFUNDED |
| Failed | FAILED |

## 8. Relationships

| Relationship | Type | Navigation Required |
|--------------|------|---------------------|
| Seat → Aircraft | Many-to-One | Yes |
| Route → Airport (origin) | Many-to-One | Yes |
| Route → Airport (destination) | Many-to-One | Yes |
| Flight → Route | Many-to-One | Yes |
| Flight → Aircraft | Many-to-One | Yes |
| FlightCrewAssignment → Flight | Many-to-One | Yes |
| FlightCrewAssignment → CrewMember | Many-to-One | Yes |
| Booking → Passenger | Many-to-One | Yes |
| Booking → Flight | Many-to-One | Yes |
| Booking → Seat | Many-to-One | Yes |
| Booking → FareClass | Many-to-One | Yes |

**Inverse Relationships:**
- Aircraft has many Seats
- Aircraft has many Flights
- Airport has many Routes (as origin)
- Airport has many Routes (as destination)
- Route has many Flights
- Flight has many Bookings
- Flight has many FlightCrewAssignments
- CrewMember has many FlightCrewAssignments
- Passenger has many Bookings
- Seat has many Bookings
- FareClass has many Bookings

## 9. API Endpoints

### 9.1 Aircraft Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/aircraft` | Create a new aircraft |
| GET | `/aircraft` | List all aircraft with filtering and pagination |
| GET | `/aircraft/{id}` | Get aircraft by ID |
| GET | `/aircraft/{id}/details` | Get aircraft details with seats and flights |
| PUT | `/aircraft/{id}` | Update aircraft |
| DELETE | `/aircraft/{id}` | Delete aircraft |
| GET | `/aircraft/{id}/availability` | Get aircraft availability for date range |

### 9.2 Seat Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/seats` | Create a new seat |
| GET | `/seats` | List all seats with filtering and pagination |
| GET | `/seats/{id}` | Get seat by ID |
| PUT | `/seats/{id}` | Update seat |
| DELETE | `/seats/{id}` | Delete seat |

### 9.3 Airport Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/airports` | Create a new airport |
| GET | `/airports` | List all airports with filtering and pagination |
| GET | `/airports/{id}` | Get airport by ID |
| PUT | `/airports/{id}` | Update airport |
| DELETE | `/airports/{id}` | Delete airport |

### 9.4 Route Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/routes` | Create a new route |
| GET | `/routes` | List all routes with filtering and pagination |
| GET | `/routes/{id}` | Get route by ID |
| PUT | `/routes/{id}` | Update route |
| DELETE | `/routes/{id}` | Delete route |

### 9.5 Fare Class Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/fare-classes` | Create a new fare class |
| GET | `/fare-classes` | List all fare classes with filtering and pagination |
| GET | `/fare-classes/{id}` | Get fare class by ID |
| PUT | `/fare-classes/{id}` | Update fare class |
| DELETE | `/fare-classes/{id}` | Delete fare class |

### 9.6 Flight Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/flights` | Create a new flight |
| GET | `/flights` | List all flights with filtering and pagination |
| GET | `/flights/{id}` | Get flight by ID |
| GET | `/flights/{id}/details` | Get flight details with route, aircraft, bookings, and crew |
| PUT | `/flights/{id}` | Update flight |
| DELETE | `/flights/{id}` | Delete flight |
| GET | `/flights/search` | Search flights by origin, destination, and date |
| POST | `/flights/{id}/update-status` | Update flight status |
| POST | `/flights/{id}/cancel` | Cancel flight and all associated bookings |
| GET | `/flights/{id}/passenger-manifest` | Get passenger manifest for flight |

### 9.7 Crew Member Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/crew-members` | Create a new crew member |
| GET | `/crew-members` | List all crew members with filtering and pagination |
| GET | `/crew-members/{id}` | Get crew member by ID |
| GET | `/crew-members/{id}/details` | Get crew member details with flight assignments |
| PUT | `/crew-members/{id}` | Update crew member |
| DELETE | `/crew-members/{id}` | Delete crew member |
| GET | `/crew-members/{id}/schedule` | Get crew member schedule for date range |

### 9.8 Flight Crew Assignment Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/flight-crew-assignments` | Create a new flight crew assignment |
| GET | `/flight-crew-assignments` | List all flight crew assignments with filtering and pagination |
| GET | `/flight-crew-assignments/{id}` | Get flight crew assignment by ID |
| PUT | `/flight-crew-assignments/{id}` | Update flight crew assignment |
| DELETE | `/flight-crew-assignments/{id}` | Delete flight crew assignment |

### 9.9 Passenger Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/passengers` | Create a new passenger |
| GET | `/passengers` | List all passengers with filtering and pagination |
| GET | `/passengers/{id}` | Get passenger by ID |
| GET | `/passengers/{id}/details` | Get passenger details with booking history |
| PUT | `/passengers/{id}` | Update passenger |
| DELETE | `/passengers/{id}` | Delete passenger |

### 9.10 Booking Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| POST | `/bookings` | Create a new booking |
| GET | `/bookings` | List all bookings with filtering and pagination |
| GET | `/bookings/{id}` | Get booking by ID |
| GET | `/bookings/{id}/details` | Get booking details with passenger, flight, seat, and fare class |
| PUT | `/bookings/{id}` | Update booking |
| DELETE | `/bookings/{id}` | Delete booking |
| POST | `/bookings/{id}/cancel` | Cancel booking |
| POST | `/bookings/{id}/change-seat` | Change seat assignment |
| POST | `/bookings/{id}/change-flight` | Change flight |

### 9.11 Dashboard

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/dashboard/metrics` | Get dashboard metrics and statistics |

## 10. Workflow Logic

### 10.1 Flight Creation Workflow

**Endpoint:** `POST /flights`

**Steps:**
1. Validate route exists and is active
2. Validate aircraft exists and is active
3. Validate flight number is unique for the departure date
4. Validate scheduled_departure is in the future
5. Validate scheduled_arrival is after scheduled_departure
6. Check aircraft availability for flight duration plus 2-hour turnaround
7. Validate base_fare is greater than 0
8. Create flight with status "Scheduled"
9. Return created flight with route and aircraft details

### 10.2 Crew Assignment Workflow

**Endpoint:** `POST /flight-crew-assignments`

**Steps:**
1. Validate flight exists and status is "Scheduled"
2. Validate crew member exists and status is "Active"
3. Validate crew member certifications are valid at flight time
4. Check crew member has no overlapping flight assignments
5. Validate crew member has minimum 12-hour rest period before and after flight
6. Create assignment with status "Assigned"
7. Validate flight has required crew composition (1 Captain, 1 First Officer, 2+ Flight Attendants)
8. Return created assignment with crew member and flight details

### 10.3 Booking Creation Workflow

**Endpoint:** `POST /bookings`

**Steps:**
1. Validate or create passenger record
2. Validate flight exists and status is "Scheduled"
3. Validate flight departure is in the future
4. Validate fare class exists
5. If seat specified, validate seat exists, belongs to aircraft, and is available
6. Check total bookings do not exceed aircraft capacity
7. Calculate total_price = flight.base_fare × fare_class.price_multiplier
8. Generate unique 6-character alphanumeric booking_reference
9. Create booking with status "Confirmed" and payment_status "Pending"
10. Return created booking with full details

### 10.4 Booking Modification Workflow

**Endpoint:** `POST /bookings/{id}/change-seat` or `POST /bookings/{id}/change-flight`

**Change Seat Steps:**
1. Validate booking exists and status is "Confirmed" or "Pending"
2. Validate new seat exists and belongs to same flight's aircraft
3. Validate new seat is available
4. Update booking with new seat_id
5. Return updated booking

**Change Flight Steps:**
1. Validate booking exists and status is "Confirmed" or "Pending"
2. Validate new flight exists and status is "Scheduled"
3. Validate new flight departure is in the future
4. Calculate change fee based on fare_class.change_fee_percentage
5. Validate seat availability on new flight
6. Update booking with new flight_id and recalculated total_price
7. Return updated booking

### 10.5 Flight Status Management Workflow

**Endpoint:** `POST /flights/{id}/update-status`

**Steps:**
1. Validate flight exists
2. Validate status transition is allowed
3. If status is "Departed", set actual_departure to current timestamp
4. If status is "Arrived", set actual_arrival to current timestamp
5. If status is "Cancelled", trigger cancel_flight_and_bookings batch service
6. Update flight status
7. Return updated flight

### 10.6 Aircraft Maintenance Scheduling Workflow

**Endpoint:** `PUT /aircraft/{id}` (status change to "Maintenance")

**Steps:**
1. Validate aircraft exists
2. Validate maintenance start and end dates
3. Query all flights assigned to aircraft within maintenance period
4. For each conflicting flight:
   - Check if alternative aircraft is available
   - If available, reassign flight to alternative aircraft
   - If not available, cancel flight and all bookings
5. Update aircraft status to "Maintenance"
6. Return updated aircraft with list of affected flights

## 11. Business Logic

### 11.1 Aircraft Business Rules

1. **Unique Registration:** Aircraft registration_number must be unique across all aircraft
2. **Deletion Protection:** Aircraft cannot be deleted if it has future flight assignments
3. **Turnaround Time:** Aircraft must have minimum 2-hour turnaround time between flights
4. **Availability Calculation:** Aircraft is available if status is "Active" and no overlapping flight assignments exist within turnaround buffer

### 11.2 Airport Business Rules

1. **IATA Code Format:** Airport code must be exactly 3 uppercase letters
2. **Unique Code:** Airport IATA code must be unique across all airports

### 11.3 Route Business Rules

1. **Different Airports:** Route origin and destination airports must be different
2. **Active Routes Only:** Only routes with status "Active" can be used for new flights

### 11.4 Flight Business Rules

1. **Unique Flight Number:** Flight number must be unique for the same departure date
2. **Future Departure:** Flight departure time must be in the future at creation
3. **Logical Times:** Flight arrival time must be after departure time
4. **Aircraft Availability:** Aircraft must be available for flight duration plus turnaround time
5. **Edit Restrictions:** Flights cannot be edited after status is "Departed" or "Arrived"
6. **Status Transitions:** Valid status transitions:
   - Scheduled → Boarding, Delayed, Cancelled
   - Boarding → Departed, Delayed, Cancelled
   - Delayed → Boarding, Departed, Cancelled
   - Departed → Arrived
   - Cancelled, Arrived → No further transitions

### 11.5 Crew Assignment Business Rules

1. **Required Crew Composition:** Each flight must have at least:
   - 1 Captain
   - 1 First Officer
   - 2 Flight Attendants
2. **No Overlapping Assignments:** Crew member cannot be assigned to overlapping flights
3. **Valid Certifications:** Crew member certifications must be valid at time of flight
4. **Rest Period:** Crew member must have minimum 12-hour rest period between flight assignments
5. **Active Status:** Only crew members with status "Active" can be assigned to flights

### 11.6 Booking Business Rules

1. **Seat Uniqueness:** Seat can only be booked once per flight
2. **Scheduled Flights Only:** Bookings can only be made for flights with status "Scheduled"
3. **Future Flights Only:** Bookings cannot be made for flights in the past
4. **Automatic Cancellation:** Bookings are automatically cancelled when flight is cancelled
5. **Capacity Limit:** Total bookings for a flight cannot exceed aircraft seat capacity
6. **Seat Locking:** System supports concurrent booking operations with seat availability locking

### 11.7 Fare Class Business Rules

1. **Economy Multiplier:** Economy fare class multiplier must be 1.0
2. **Price Calculation:** Final ticket price = base_fare × fare_class.price_multiplier

### 11.8 Batch Operations

**Cancel Flight and Bookings:**
- When flight is cancelled, all associated bookings are automatically cancelled
- Booking status changed to "Cancelled"
- Payment status changed to "Refunded" if payment was "Completed"

**Assign Crew to Flight:**
- Validates crew availability for all crew members before creating any assignments
- Ensures flight meets minimum crew requirements
- Creates all assignments atomically (all succeed or all fail)

## 12. Validation Rules

### 12.1 Aircraft Validations

- `total_seats` must be greater than 0
- `year_manufactured` must be greater than 1900 if provided
- `registration_number` must be unique
- `status` must be valid AircraftStatus enum value

### 12.2 Route Validations

- `distance_km` must be greater than 0
- `estimated_duration_minutes` must be greater than 0
- `origin_airport_id` and `destination_airport_id` must be different
- `status` must be valid RouteStatus enum value

### 12.3 Flight Validations

- `base_fare` must be greater than 0
- `scheduled_departure` must be in the future at creation
- `scheduled_arrival` must be after `scheduled_departure`
- `flight_number` must be unique for the same departure date
- `status` must be valid FlightStatus enum value

### 12.4 Passenger Validations

- `email` must be in valid email format (RFC 5322)
- `passport_number` must be alphanumeric and 6-12 characters if provided
- `phone` must be valid phone number format

### 12.5 Booking Validations

- `total_price` must be greater than or equal to 0
- `booking_reference` must be unique and alphanumeric 6 characters
- `booking_status` must be valid BookingStatus enum value
- `payment_status` must be valid PaymentStatus enum value

### 12.6 Fare Class Validations

- `price_multiplier` must be greater than 0
- `baggage_allowance_kg` must be greater than or equal to 0
- `change_fee_percentage` must be between 0 and 100
- `class_type` must be valid FareClassType enum value

### 12.7 Seat Validations

- `seat_number` must be unique per aircraft
- `class_type` must be valid SeatClassType enum value
- At least one of `is_window`, `is_aisle`, or `is_exit_row` should be true

### 12.8 Crew Member Validations

- `employee_number` must be unique
- `email` must be in valid email format
- `role` must be valid CrewRole enum value
- `status` must be valid CrewStatus enum value
- `certification_expiry` must be in the future for active crew members

### 12.9 Airport Validations

- `code` must be exactly 3 uppercase letters
- `code` must be unique
- `latitude` must be between -90 and 90 if provided
- `longitude` must be between -180 and 180 if provided

## 13. Implementation Notes

### 13.1 Technology Stack

**Framework:** FastAPI
- Modern, fast (high-performance) web framework for building APIs
- Automatic interactive API documentation (Swagger UI and ReDoc)
- Built-in data validation using Pydantic
- Async support for high concurrency

**ORM:** SQLAlchemy
- Full-featured SQL toolkit and ORM
- Support for complex queries and relationships
- Database-agnostic (currently using SQLite)
- Lazy and eager loading support for relationships

**Database:** SQLite
- Lightweight, file-based database for development
- Zero configuration required
- Suitable for development and testing

**Validation:** Pydantic v2
- Data validation using Python type annotations
- Automatic JSON schema generation
- High performance with Rust core
- Clear error messages for validation failures

**Package Manager:** uv
- Fast Python package installer and resolver
- Drop-in replacement for pip and pip-tools
- Significantly faster dependency resolution

### 13.2 Architecture Patterns

**Service Layer Pattern:**
- Clear separation between API routes, business logic, and data access
- Routes handle HTTP concerns (request/response)
- Services contain business logic and orchestration
- Repositories handle database operations

**Dependency Injection:**
- Database sessions injected using `Depends(get_db)`
- Service dependencies injected into route handlers
- Promotes testability and loose coupling

**Repository Pattern:**
- Abstract database operations behind repository interfaces
- Centralized query logic
- Easier to test and mock

### 13.3 API Documentation

**Automatic OpenAPI Generation:**
- FastAPI automatically generates OpenAPI 3.0 schema
- Interactive documentation available at `/docs` (Swagger UI)
- Alternative documentation at `/redoc` (ReDoc)
- JSON schema available at `/openapi.json`

### 13.4 Error Handling

**HTTP Status Codes:**
- 200: Successful GET, PUT, PATCH
- 201: Successful POST (resource created)
- 204: Successful DELETE
- 400: Bad Request (validation errors)
- 404: Resource Not Found
- 409: Conflict (business rule violation)
- 422: Unprocessable Entity (Pydantic validation errors)
- 500: Internal Server Error

**Error Response Format:**
```json
{
  "detail": "Error message",
  "error_code": "SPECIFIC_ERROR_CODE",
  "field_errors": {
    "field_name": ["error message"]
  }
}
```

### 13.5 Pagination

**Query Parameters:**
- `skip`: Number of records to skip (default: 0)
- `limit`: Maximum number of records to return (default: 100, max: 1000)

**Response Format:**
```json
{
  "items": [...],
  "total": 1234,
  "skip": 0,
  "limit": 100
}
```

### 13.6 Filtering and Sorting

**Query Parameters:**
- Field-specific filters: `?status=Active&model=Boeing 737`
- Sorting: `?sort_by=created_at&sort_order=desc`
- Date range filters: `?start_date=2024-01-01&end_date=2024-12-31`

### 13.7 Eager Loading

**Detail Endpoints:**
- Use SQLAlchemy's `joinedload` and `selectinload` for eager loading
- Minimize N+1 query problems
- Specified eager load paths in detail endpoint specifications

### 13.8 Timestamps

**Automatic Timestamp Management:**
- `created_at`: Set automatically on record creation
- `updated_at`: Updated automatically on every record modification
- All timestamps stored in UTC
- Timezone conversion handled at API layer

### 13.9 Soft Deletes

**Implementation:**
- Entities with status fields use status-based soft deletes
- Entities without status fields may use `deleted_at` timestamp
- Deleted records excluded from default queries
- Admin endpoints can access deleted records

### 13.10 Transaction Management

**Database Transactions:**
- Each API request wrapped in database transaction
- Automatic rollback on errors
- Explicit commit on success
- Batch operations use single transaction for atomicity

## 14. Assumptions

1. **Authentication:** System operates without authentication/login pages as specified in requirements
2. **Timezone Handling:** All dates and times stored in UTC and converted to local timezone for display based on airport timezone
3. **Soft Deletes:** Soft delete used for all entities (mark as inactive rather than physical deletion)
4. **Currency:** Currency is USD by default; no multi-currency support in initial version
5. **Turnaround Time:** Minimum turnaround time between flights is 2 hours
6. **Crew Rest Period:** Minimum crew rest period is 12 hours between assignments
7. **Booking Reference:** Booking reference is auto-generated 6-character alphanumeric code
8. **Concurrent Bookings:** System supports concurrent booking operations with seat availability locking to prevent double-booking
9. **Payment Processing:** Payment processing is simulated; no actual payment gateway integration
10. **Notification System:** No email/SMS notifications in initial version; status changes logged only
11. **File Storage:** No document or file storage requirements (e.g., crew certifications, passenger documents)
12. **Audit Logging:** Basic audit trail through created_at/updated_at timestamps; no detailed change history
13. **Data Retention:** No automatic data archival or purging policies
14. **Performance:** System designed for small to medium airline operations (up to 100 aircraft, 1000 flights per day)
15. **Localization:** English language only; no internationalization support in initial version

## 15. Future Improvements

### 15.1 Database and Infrastructure

1. **Alembic Migrations:** Implement Alembic for database schema version control and migrations
2. **PostgreSQL Migration:** Migrate from SQLite to PostgreSQL for production deployment
3. **Database Indexing:** Add strategic indexes for frequently queried fields (flight_number, booking_reference, employee_number)
4. **Connection Pooling:** Implement connection pooling for better performance under load
5. **Read Replicas:** Add read replicas for scaling read-heavy operations

### 15.2 Authentication and Authorization

1. **JWT Authentication:** Implement JWT-based authentication system
2. **Role-Based Access Control (RBAC):** Implement fine-grained permissions based on user roles
3. **OAuth2 Integration:** Support OAuth2 for third-party authentication (Google, Microsoft)
4. **API Key Management:** Support API keys for external system integration
5. **Session Management:** Implement secure session management with refresh tokens

### 15.3 Testing

1. **Unit Tests:** Comprehensive unit tests for all service layer functions
2. **Integration Tests:** API endpoint integration tests with test database
3. **Load Testing:** Performance and load testing for concurrent operations
4. **Test Coverage:** Achieve minimum 80% code coverage
5. **Mocking:** Mock external dependencies for isolated testing

### 15.4 Deployment and DevOps

1. **Docker Containerization:** Create Docker images for application and database
2. **Docker Compose:** Multi-container setup for local development
3. **CI/CD Pipeline:** Automated testing and deployment pipeline
4. **Environment Configuration:** Environment-specific configuration management
5. **Health Checks:** Implement health check endpoints for monitoring

### 15.5 Monitoring and Observability

1. **Logging:** Structured logging with correlation IDs
2. **Metrics:** Application metrics (request rate, response time, error rate)
3. **Tracing:** Distributed tracing for request flow analysis
4. **Alerting:** Automated alerts for critical errors and performance issues
5. **APM Integration:** Application Performance Monitoring tool integration

### 15.6 Feature Enhancements

1. **Real-time Flight Tracking:** Integration with flight tracking APIs
2. **Email/SMS Notifications:** Automated notifications for booking confirmations and flight status changes
3. **Dynamic Pricing:** Implement demand-based dynamic pricing algorithms
4. **Multi-currency Support:** Support multiple currencies with real-time exchange rates
5. **Seat Map Visualization:** Interactive seat selection with visual seat map
6. **Loyalty Program:** Frequent flyer program with points and tier management
7. **Baggage Tracking:** Track baggage through entire journey
8. **Check-in System:** Online and kiosk check-in integration
9. **Crew Scheduling Optimization:** AI-based crew scheduling optimization
10. **Maintenance Tracking:** Comprehensive aircraft maintenance tracking and scheduling
11. **Fuel Management:** Fuel consumption tracking and cost analysis
12. **Weather Integration:** Weather data integration for flight planning
13. **Revenue Management:** Yield optimization and revenue management system
14. **Mobile API:** Optimized API endpoints for mobile applications
15. **WebSocket Support:** Real-time updates for flight status and booking changes

### 15.7 Data and Analytics

1. **Data Warehouse:** Separate analytics database for reporting
2. **Business Intelligence:** BI dashboards for operational insights
3. **Predictive Analytics:** ML models for demand forecasting and delay prediction
4. **Data Export:** Bulk data export functionality for external analysis
5. **Audit Trail:** Comprehensive audit logging for compliance

### 15.8 Security Enhancements

1. **Rate Limiting:** API rate limiting to prevent abuse
2. **Input Sanitization:** Enhanced input validation and sanitization
3. **SQL Injection Prevention:** Parameterized queries and ORM usage
4. **CORS Configuration:** Proper CORS policy configuration
5. **Security Headers:** Implement security headers (HSTS, CSP, etc.)
6. **Encryption:** Encrypt sensitive data at rest and in transit
7. **PCI Compliance:** Payment card industry compliance for payment processing
8. **GDPR Compliance:** Data privacy and GDPR compliance features

### 15.9 Performance Optimization

1. **Caching Layer:** Redis caching for frequently accessed data
2. **Query Optimization:** Optimize slow queries with proper indexing
3. **Async Operations:** Convert blocking operations to async
4. **Background Jobs:** Celery or similar for background task processing
5. **CDN Integration:** CDN for static assets and API responses

### 15.10 API Enhancements

1. **GraphQL Support:** GraphQL API alongside REST
2. **API Versioning:** Proper API versioning strategy
3. **Webhook Support:** Webhooks for event notifications
4. **Batch Operations:** Bulk create/update/delete endpoints
5. **Partial Updates:** PATCH support for partial resource updates
6. **Field Selection:** Allow clients to specify which fields to return
7. **API Documentation:** Enhanced API documentation with examples and tutorials