# Backend Specification

## 1. System Summary

**System Name:** Tourism Management System

**Description:** A comprehensive platform for managing tour packages, bookings, customer relationships, service providers, and financial operations in the tourism industry. The system supports multiple user roles including super admins, tour managers, booking agents, tour guides, service providers, customers, and finance managers. It handles the complete lifecycle of tourism operations from tour package creation and scheduling to booking management, payment processing, cancellation handling, review management, and financial reconciliation.

**Core Capabilities:**
- User management with role-based access control across 7 distinct roles
- Tour catalog management with destinations, itineraries, categories, and seasonal pricing
- Booking management with traveler information and resource assignments
- Service provider management for hotels, transportation, and other services
- Payment processing with multiple payment methods and invoice generation
- Review and rating system for tour packages and guides
- Cancellation request handling with refund calculations
- Financial management including expenses and agent commissions
- Multi-channel notification system
- Document management for bookings and customers

## 2. Source Input Summary

**DDL Schema:** Comprehensive database schema with 40+ tables covering users, customers, tour packages, bookings, payments, service providers, reviews, and financial management. The schema includes proper foreign key relationships, indexes, and constraints to ensure data integrity.

**PRD Document:** Detailed Product Requirements Document with 18 modules, 7 user roles, and comprehensive workflows for booking, payment, cancellation, and tour execution. The PRD includes detailed business rules, validation requirements, and user stories for each role.

**Key Source Artifacts:**
- Database schema with normalized table structures
- Entity relationship definitions
- Business workflow specifications
- Role-based permission requirements
- Validation and business rule definitions

## 3. Generation Mode

**Mode:** Strict

**Interpretation:** The backend implementation strictly adheres to the provided DDL schema and PRD specifications. All entities, fields, relationships, and business rules are implemented exactly as defined in the source documents. No additional features or modifications are introduced beyond what is explicitly specified. Field types, constraints, and relationships match the DDL schema precisely.

## 4. Backend Scope

**Technology Stack:**
- **Framework:** FastAPI (Python 3.11+)
- **ORM:** SQLAlchemy 2.0+ with declarative models
- **Database:** SQLite (development), PostgreSQL-ready
- **Validation:** Pydantic v2 for request/response schemas
- **Package Manager:** uv for dependency management
- **API Documentation:** Automatic OpenAPI/Swagger documentation via FastAPI

**Architecture Pattern:**
- Service layer pattern separating business logic from API routes
- Repository pattern for data access abstraction
- Dependency injection using FastAPI's Depends system
- Clear separation of concerns: routes → services → repositories → models

**Module Organization:**
The backend is organized into 10 functional modules:
1. User Management
2. Tour Catalog
3. Booking Management
4. Service Provider Management
5. Payment Management
6. Review Management
7. Cancellation Management
8. Financial Management
9. Notification Management
10. Document Management

## 5. Roles

The system supports 7 distinct user roles with specific permissions and capabilities:

| Role | Code | Description | Key Permissions |
|------|------|-------------|-----------------|
| Super Admin | `super_admin` | System administrator with full access | All system operations, user management, system configuration |
| Tour Manager | `tour_manager` | Manages tour packages and schedules | Create/edit tour packages, manage schedules, assign resources, approve cancellations |
| Booking Agent | `booking_agent` | Handles customer bookings | Create bookings, process payments, manage customer inquiries, earn commissions |
| Tour Guide | `tour_guide` | Conducts tours and manages tour execution | View assigned tours, submit expenses, update tour status, interact with travelers |
| Service Provider | `service_provider` | Provides hotels, transportation, and other services | Manage service offerings, confirm bookings, update availability |
| Customer | `customer` | Books and participates in tours | Search tours, make bookings, submit reviews, manage profile, request cancellations |
| Finance Manager | `finance_manager` | Manages financial operations | Approve expenses, process commissions, generate financial reports, manage invoices |

## 6. Entities and Fields

### 6.1 User Management Module

#### User
**Table:** `users`  
**Description:** System user with authentication credentials and role assignment

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| email | str | Yes | User email address (unique) |
| password_hash | str | Yes | Hashed password |
| role | str | Yes | User role (enum: super_admin, tour_manager, booking_agent, tour_guide, service_provider, customer, finance_manager) |
| first_name | str | Yes | User first name |
| last_name | str | Yes | User last name |
| phone | Optional[str] | No | Contact phone number |
| is_active | bool | Yes | Account active status (default: true) |
| last_login_at | Optional[datetime] | No | Last login timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Customer
**Table:** `customers`  
**Description:** Extended profile for customer users with travel and loyalty information

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| user_id | str | Yes | Reference to users table (FK) |
| date_of_birth | Optional[date] | No | Customer date of birth |
| nationality | Optional[str] | No | Customer nationality |
| passport_number | Optional[str] | No | Passport number |
| passport_expiry | Optional[date] | No | Passport expiry date |
| emergency_contact_name | Optional[str] | No | Emergency contact name |
| emergency_contact_phone | Optional[str] | No | Emergency contact phone |
| address_line1 | Optional[str] | No | Address line 1 |
| address_line2 | Optional[str] | No | Address line 2 |
| city | Optional[str] | No | City |
| state | Optional[str] | No | State or province |
| country | Optional[str] | No | Country |
| postal_code | Optional[str] | No | Postal code |
| loyalty_status | Optional[str] | No | Loyalty tier (enum: bronze, silver, gold, platinum) |
| loyalty_points | int | Yes | Accumulated loyalty points (default: 0) |
| preferences | Optional[str] | No | Customer preferences and notes (JSON text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `loyalty_status`

#### Tourguide
**Table:** `tour_guides`  
**Description:** Extended profile for tour guide users with certifications and availability

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| user_id | str | Yes | Reference to users table (FK) |
| languages_spoken | list | Yes | Array of languages spoken (JSON array) |
| specializations | Optional[list] | No | Array of specialization areas (JSON array) |
| certifications | Optional[str] | No | Certifications and licenses (text) |
| license_number | Optional[str] | No | License number |
| license_expiry | Optional[date] | No | License expiry date |
| years_of_experience | Optional[int] | No | Years of experience |
| bio | Optional[str] | No | Guide biography (text) |
| rating | Optional[float] | No | Average rating (0-5) |
| total_tours_conducted | int | Yes | Total tours conducted (default: 0) |
| is_available | bool | Yes | Availability status (default: true) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_available`

#### Serviceprovider
**Table:** `service_providers`  
**Description:** Extended profile for service provider users (hotels, transportation, etc.)

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| user_id | str | Yes | Reference to users table (FK) |
| company_name | str | Yes | Company name |
| service_type | str | Yes | Service type (enum: hotel, transportation, restaurant, activity, insurance, other) |
| registration_number | Optional[str] | No | Business registration number |
| tax_id | Optional[str] | No | Tax identification number |
| contact_person | Optional[str] | No | Contact person name |
| contact_email | Optional[str] | No | Contact email |
| contact_phone | Optional[str] | No | Contact phone |
| address_line1 | Optional[str] | No | Address line 1 |
| address_line2 | Optional[str] | No | Address line 2 |
| city | Optional[str] | No | City |
| state | Optional[str] | No | State or province |
| country | Optional[str] | No | Country |
| postal_code | Optional[str] | No | Postal code |
| contract_start_date | Optional[date] | No | Contract start date |
| contract_end_date | Optional[date] | No | Contract end date |
| payment_terms | Optional[str] | No | Payment terms (text) |
| rating | Optional[float] | No | Average rating (0-5) |
| is_verified | bool | Yes | Verification status (default: false) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_verified`

### 6.2 Tour Catalog Module

#### Category
**Table:** `categories`  
**Description:** Tour package categories for classification and filtering

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| name | str | Yes | Category name (unique) |
| slug | str | Yes | URL-friendly slug (unique) |
| description | Optional[str] | No | Category description (text) |
| icon_url | Optional[str] | No | Icon image URL |
| is_active | bool | Yes | Active status (default: true) |
| display_order | Optional[int] | No | Display order |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Destination
**Table:** `destinations`  
**Description:** Geographic destinations featured in tour packages

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| name | str | Yes | Destination name |
| slug | str | Yes | URL-friendly slug (unique) |
| country | str | Yes | Country |
| state | Optional[str] | No | State or province |
| city | Optional[str] | No | City |
| description | Optional[str] | No | Destination description (text) |
| attractions | Optional[str] | No | Main attractions (text) |
| best_time_to_visit | Optional[str] | No | Best time to visit |
| climate_info | Optional[str] | No | Climate information (text) |
| latitude | Optional[float] | No | Latitude coordinate |
| longitude | Optional[float] | No | Longitude coordinate |
| image_url | Optional[str] | No | Destination image URL |
| is_active | bool | Yes | Active status (default: true) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Season
**Table:** `seasons`  
**Description:** Seasonal periods affecting pricing and availability

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| name | str | Yes | Season name |
| season_type | str | Yes | Season type (enum: peak, off_peak, shoulder) |
| start_date | date | Yes | Season start date |
| end_date | date | Yes | Season end date |
| price_multiplier | float | Yes | Price multiplier for season (default: 1.0) |
| description | Optional[str] | No | Season description (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Amenity
**Table:** `amenities`  
**Description:** Facilities and services available in packages or accommodations

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| name | str | Yes | Amenity name (unique) |
| description | Optional[str] | No | Amenity description (text) |
| icon_url | Optional[str] | No | Icon image URL |
| amenity_type | str | Yes | Amenity type (enum: accommodation, transport, tour, general) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Tourpackage
**Table:** `tour_packages`  
**Description:** Tour package offerings with pricing, itinerary, and availability

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| category_id | str | Yes | Reference to categories table (FK) |
| name | str | Yes | Package name |
| slug | str | Yes | URL-friendly slug (unique) |
| description | Optional[str] | No | Package description (text) |
| duration_days | int | Yes | Duration in days |
| duration_nights | int | Yes | Duration in nights |
| difficulty_level | Optional[str] | No | Difficulty level (enum: easy, moderate, challenging, difficult) |
| min_age | Optional[int] | No | Minimum age requirement |
| max_age | Optional[int] | No | Maximum age requirement |
| min_group_size | int | Yes | Minimum group size |
| max_group_size | int | Yes | Maximum group size |
| base_price | float | Yes | Base price per person |
| child_price | Optional[float] | No | Child price per person |
| single_supplement | Optional[float] | No | Single occupancy supplement |
| inclusions | Optional[str] | No | Package inclusions (text) |
| exclusions | Optional[str] | No | Package exclusions (text) |
| terms_and_conditions | Optional[str] | No | Terms and conditions (text) |
| cancellation_policy | Optional[str] | No | Cancellation policy (text) |
| is_active | bool | Yes | Active status (default: true) |
| is_featured | bool | Yes | Featured status (default: false) |
| rating | Optional[float] | No | Average rating (0-5) |
| total_reviews | int | Yes | Total number of reviews (default: 0) |
| image_url | Optional[str] | No | Package image URL |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Tourpackagedestination
**Table:** `tour_package_destinations`  
**Description:** Many-to-many relationship between tour packages and destinations

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_package_id | str | Yes | Reference to tour_packages table (FK) |
| destination_id | str | Yes | Reference to destinations table (FK) |
| visit_order | int | Yes | Order of visit in itinerary |
| duration_hours | Optional[int] | No | Duration at destination in hours |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Tourpackageamenity
**Table:** `tour_package_amenities`  
**Description:** Many-to-many relationship between tour packages and amenities

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_package_id | str | Yes | Reference to tour_packages table (FK) |
| amenity_id | str | Yes | Reference to amenities table (FK) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Itinerary
**Table:** `itineraries`  
**Description:** Day-by-day itinerary for tour packages

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_package_id | str | Yes | Reference to tour_packages table (FK) |
| day_number | int | Yes | Day number in itinerary |
| title | str | Yes | Day title |
| description | Optional[str] | No | Day description (text) |
| activities | Optional[str] | No | Activities for the day (text) |
| meals_included | Optional[str] | No | Meals included |
| accommodation_type | Optional[str] | No | Accommodation type |
| start_time | Optional[time] | No | Start time |
| end_time | Optional[time] | No | End time |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.3 Service Provider Management Module

#### Hotel
**Table:** `hotels`  
**Description:** Hotel accommodation facilities managed by service providers

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| service_provider_id | str | Yes | Reference to service_providers table (FK) |
| name | str | Yes | Hotel name |
| star_rating | Optional[int] | No | Star rating (1-5) |
| address_line1 | Optional[str] | No | Address line 1 |
| address_line2 | Optional[str] | No | Address line 2 |
| city | Optional[str] | No | City |
| state | Optional[str] | No | State or province |
| country | Optional[str] | No | Country |
| postal_code | Optional[str] | No | Postal code |
| latitude | Optional[float] | No | Latitude coordinate |
| longitude | Optional[float] | No | Longitude coordinate |
| phone | Optional[str] | No | Contact phone |
| email | Optional[str] | No | Contact email |
| website | Optional[str] | No | Website URL |
| check_in_time | Optional[time] | No | Check-in time |
| check_out_time | Optional[time] | No | Check-out time |
| description | Optional[str] | No | Hotel description (text) |
| policies | Optional[str] | No | Hotel policies (text) |
| is_active | bool | Yes | Active status (default: true) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Hotelamenity
**Table:** `hotel_amenities`  
**Description:** Many-to-many relationship between hotels and amenities

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| hotel_id | str | Yes | Reference to hotels table (FK) |
| amenity_id | str | Yes | Reference to amenities table (FK) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Roomtype
**Table:** `room_types`  
**Description:** Room types available at hotels with pricing and capacity

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| hotel_id | str | Yes | Reference to hotels table (FK) |
| name | str | Yes | Room type name |
| description | Optional[str] | No | Room type description (text) |
| max_occupancy | int | Yes | Maximum occupancy |
| bed_configuration | Optional[str] | No | Bed configuration |
| room_size_sqm | Optional[float] | No | Room size in square meters |
| price_per_night | float | Yes | Price per night |
| total_rooms | int | Yes | Total rooms available |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

#### Transportation
**Table:** `transportations`  
**Description:** Transportation services managed by service providers

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| service_provider_id | str | Yes | Reference to service_providers table (FK) |
| transport_type | str | Yes | Transport type (enum: bus, flight, train, car, boat, other) |
| vehicle_name | Optional[str] | No | Vehicle name |
| vehicle_number | Optional[str] | No | Vehicle number |
| capacity | int | Yes | Passenger capacity |
| amenities | Optional[str] | No | Vehicle amenities (text) |
| is_active | bool | Yes | Active status (default: true) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

### 6.4 Booking Management Module

#### Tourschedule
**Table:** `tour_schedules`  
**Description:** Specific instances of tour packages with departure dates and resource assignments

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_package_id | str | Yes | Reference to tour_packages table (FK) |
| tour_guide_id | Optional[str] | No | Reference to tour_guides table (FK) |
| departure_date | date | Yes | Departure date |
| return_date | date | Yes | Return date |
| available_slots | int | Yes | Available slots |
| booked_slots | int | Yes | Booked slots (default: 0) |
| status | str | Yes | Schedule status (enum: scheduled, confirmed, in_progress, completed, cancelled) |
| meeting_point | Optional[str] | No | Meeting point location |
| meeting_time | Optional[time] | No | Meeting time |
| special_instructions | Optional[str] | No | Special instructions (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

#### Tourschedulehotel
**Table:** `tour_schedule_hotels`  
**Description:** Hotel bookings for tour schedules

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_schedule_id | str | Yes | Reference to tour_schedules table (FK) |
| hotel_id | str | Yes | Reference to hotels table (FK) |
| room_type_id | Optional[str] | No | Reference to room_types table (FK) |
| check_in_date | date | Yes | Check-in date |
| check_out_date | date | Yes | Check-out date |
| rooms_booked | int | Yes | Number of rooms booked |
| total_cost | float | Yes | Total cost |
| booking_status | str | Yes | Booking status (enum: pending, confirmed, cancelled) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `booking_status`

#### Tourscheduletransportation
**Table:** `tour_schedule_transportations`  
**Description:** Transportation bookings for tour schedules

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_schedule_id | str | Yes | Reference to tour_schedules table (FK) |
| transportation_id | str | Yes | Reference to transportations table (FK) |
| departure_location | str | Yes | Departure location |
| arrival_location | str | Yes | Arrival location |
| departure_datetime | datetime | Yes | Departure datetime |
| arrival_datetime | datetime | Yes | Arrival datetime |
| seats_booked | int | Yes | Number of seats booked |
| total_cost | float | Yes | Total cost |
| booking_status | str | Yes | Booking status (enum: pending, confirmed, cancelled) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `booking_status`

#### Booking
**Table:** `bookings`  
**Description:** Customer bookings for tour schedules

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_number | str | Yes | Booking number (unique, auto-generated) |
| customer_id | str | Yes | Reference to customers table (FK) |
| tour_schedule_id | str | Yes | Reference to tour_schedules table (FK) |
| booking_agent_id | Optional[str] | No | Reference to users table (FK) |
| discount_id | Optional[str] | No | Reference to discounts table (FK) |
| booking_date | datetime | Yes | Booking date |
| number_of_adults | int | Yes | Number of adults (default: 1) |
| number_of_children | int | Yes | Number of children (default: 0) |
| number_of_infants | int | Yes | Number of infants (default: 0) |
| subtotal | float | Yes | Subtotal amount |
| discount_amount | float | Yes | Discount amount (default: 0) |
| tax_amount | float | Yes | Tax amount (default: 0) |
| total_amount | float | Yes | Total amount |
| amount_paid | float | Yes | Amount paid (default: 0) |
| amount_due | float | Yes | Amount due |
| booking_status | str | Yes | Booking status (enum: pending, confirmed, cancelled, completed, refunded) |
| payment_status | str | Yes | Payment status (enum: unpaid, partial, paid, refunded) |
| special_requests | Optional[str] | No | Special requests (text) |
| cancellation_reason | Optional[str] | No | Cancellation reason (text) |
| cancelled_at | Optional[datetime] | No | Cancellation timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `booking_status`

#### Traveler
**Table:** `travelers`  
**Description:** Individual travelers in a booking

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| traveler_type | str | Yes | Traveler type (enum: adult, child, infant) |
| first_name | str | Yes | First name |
| last_name | str | Yes | Last name |
| date_of_birth | Optional[date] | No | Date of birth |
| gender | Optional[str] | No | Gender (enum: male, female, other) |
| nationality | Optional[str] | No | Nationality |
| passport_number | Optional[str] | No | Passport number |
| passport_expiry | Optional[date] | No | Passport expiry date |
| special_requirements | Optional[str] | No | Special requirements (text) |
| meal_preference | Optional[str] | No | Meal preference |
| is_primary | bool | Yes | Primary traveler flag (default: false) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.5 Payment Management Module

#### Discount
**Table:** `discounts`  
**Description:** Discount codes and promotions for tour packages

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| code | str | Yes | Discount code (unique) |
| name | str | Yes | Discount name |
| description | Optional[str] | No | Discount description (text) |
| discount_type | str | Yes | Discount type (enum: percentage, fixed_amount, early_bird, group, loyalty) |
| discount_value | float | Yes | Discount value |
| min_booking_amount | Optional[float] | No | Minimum booking amount |
| max_discount_amount | Optional[float] | No | Maximum discount amount |
| min_travelers | Optional[int] | No | Minimum travelers required |
| applicable_tour_package_id | Optional[str] | No | Reference to tour_packages table (FK) |
| valid_from | date | Yes | Valid from date |
| valid_to | date | Yes | Valid to date |
| usage_limit | Optional[int] | No | Usage limit |
| usage_count | int | Yes | Usage count (default: 0) |
| is_active | bool | Yes | Active status (default: true) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

#### Payment
**Table:** `payments`  
**Description:** Payment transactions for bookings

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| payment_method | str | Yes | Payment method (enum: credit_card, debit_card, bank_transfer, cash, paypal, stripe, other) |
| transaction_id | Optional[str] | No | Transaction ID from payment gateway |
| amount | float | Yes | Payment amount |
| currency | str | Yes | Currency code (default: USD) |
| payment_status | str | Yes | Payment status (enum: pending, processing, completed, failed, refunded) |
| payment_date | Optional[datetime] | No | Payment date |
| payment_gateway | Optional[str] | No | Payment gateway |
| gateway_response | Optional[str] | No | Gateway response (JSON text) |
| notes | Optional[str] | No | Payment notes (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `payment_status`

#### Invoice
**Table:** `invoices`  
**Description:** Invoices for bookings

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| invoice_number | str | Yes | Invoice number (unique, auto-generated) |
| invoice_date | date | Yes | Invoice date |
| due_date | Optional[date] | No | Due date |
| subtotal | float | Yes | Subtotal amount |
| tax_amount | float | Yes | Tax amount |
| discount_amount | float | Yes | Discount amount |
| total_amount | float | Yes | Total amount |
| notes | Optional[str] | No | Invoice notes (text) |
| terms | Optional[str] | No | Payment terms (text) |
| invoice_status | str | Yes | Invoice status (enum: draft, sent, paid, overdue, cancelled) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `invoice_status`

#### Invoicelineitem
**Table:** `invoice_line_items`  
**Description:** Line items for invoices

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| invoice_id | str | Yes | Reference to invoices table (FK) |
| description | str | Yes | Line item description |
| quantity | int | Yes | Quantity |
| unit_price | float | Yes | Unit price |
| total_price | float | Yes | Total price (quantity × unit_price) |
| item_order | int | Yes | Display order |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

### 6.6 Review Management Module

#### Review
**Table:** `reviews`  
**Description:** Customer reviews and ratings for tour packages

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| customer_id | str | Yes | Reference to customers table (FK) |
| tour_package_id | str | Yes | Reference to tour_packages table (FK) |
| tour_guide_id | Optional[str] | No | Reference to tour_guides table (FK) |
| overall_rating | int | Yes | Overall rating (1-5) |
| guide_rating | Optional[int] | No | Guide rating (1-5) |
| accommodation_rating | Optional[int] | No | Accommodation rating (1-5) |
| transportation_rating | Optional[int] | No | Transportation rating (1-5) |
| value_for_money_rating | Optional[int] | No | Value for money rating (1-5) |
| title | Optional[str] | No | Review title |
| comment | Optional[str] | No | Review comment (text) |
| photos | Optional[list] | No | Array of photo URLs (JSON array) |
| is_verified | bool | Yes | Verified booking flag (default: true) |
| is_published | bool | Yes | Published status (default: false) |
| management_response | Optional[str] | No | Management response (text) |
| response_date | Optional[datetime] | No | Response date |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_published`

### 6.7 Cancellation Management Module

#### Cancellationrequest
**Table:** `cancellation_requests`  
**Description:** Customer cancellation requests for bookings

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| customer_id | str | Yes | Reference to customers table (FK) |
| reason | str | Yes | Cancellation reason (text) |
| requested_date | datetime | Yes | Request date |
| cancellation_fee | float | Yes | Cancellation fee |
| refund_amount | float | Yes | Refund amount |
| status | str | Yes | Request status (enum: pending, approved, rejected, processed) |
| processed_by | Optional[str] | No | Reference to users table (FK) |
| processed_date | Optional[datetime] | No | Processing date |
| admin_notes | Optional[str] | No | Admin notes (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.8 Financial Management Module

#### Expense
**Table:** `expenses`  
**Description:** Tour-related expenses and operational costs

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| tour_schedule_id | Optional[str] | No | Reference to tour_schedules table (FK) |
| category | str | Yes | Expense category |
| description | str | Yes | Expense description |
| amount | float | Yes | Expense amount |
| currency | str | Yes | Currency code (default: USD) |
| vendor_name | Optional[str] | No | Vendor name |
| expense_date | date | Yes | Expense date |
| payment_method | Optional[str] | No | Payment method |
| receipt_url | Optional[str] | No | Receipt URL |
| status | str | Yes | Expense status (enum: pending, approved, rejected, paid) |
| submitted_by | str | Yes | Reference to users table (FK) |
| approved_by | Optional[str] | No | Reference to users table (FK) |
| approval_date | Optional[datetime] | No | Approval date |
| notes | Optional[str] | No | Expense notes (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

#### Commission
**Table:** `commissions`  
**Description:** Agent commissions for bookings

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | str | Yes | Reference to bookings table (FK) |
| booking_agent_id | str | Yes | Reference to users table (FK) |
| booking_amount | float | Yes | Booking amount |
| commission_rate | float | Yes | Commission rate percentage |
| commission_amount | float | Yes | Commission amount |
| status | str | Yes | Commission status (enum: pending, approved, paid) |
| payment_date | Optional[date] | No | Payment date |
| notes | Optional[str] | No | Commission notes (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

### 6.9 Notification Management Module

#### Notification
**Table:** `notifications`  
**Description:** System notifications for users

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| user_id | str | Yes | Reference to users table (FK) |
| notification_type | str | Yes | Notification type (enum: booking, payment, cancellation, reminder, promotion, system, review) |
| title | str | Yes | Notification title |
| message | str | Yes | Notification message (text) |
| link_url | Optional[str] | No | Link URL |
| is_read | bool | Yes | Read status (default: false) |
| read_at | Optional[datetime] | No | Read timestamp |
| delivery_method | str | Yes | Delivery method (enum: in_app, email, sms, push) |
| delivery_status | str | Yes | Delivery status (enum: pending, sent, failed) |
| sent_at | Optional[datetime] | No | Sent timestamp |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_read`

### 6.10 Document Management Module

#### Document
**Table:** `documents`  
**Description:** Documents associated with bookings and customers

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier (UUID) |
| booking_id | Optional[str] | No | Reference to bookings table (FK) |
| customer_id | Optional[str] | No | Reference to customers table (FK) |
| document_type | str | Yes | Document type (enum: voucher, ticket, insurance, passport, visa, itinerary, invoice, receipt, other) |
| file_name | str | Yes | File name |
| file_url | str | Yes | File URL |
| file_size_kb | Optional[int] | No | File size in KB |
| mime_type | Optional[str] | No | MIME type |
| uploaded_by | str | Yes | Reference to users table (FK) |
| description | Optional[str] | No | Document description (text) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

## 7. Enumerations

### UserRole
```python
class UserRole(str, Enum):
    SUPER_ADMIN = "super_admin"
    TOUR_MANAGER = "tour_manager"
    BOOKING_AGENT = "booking_agent"
    TOUR_GUIDE = "tour_guide"
    SERVICE_PROVIDER = "service_provider"
    CUSTOMER = "customer"
    FINANCE_MANAGER = "finance_manager"
```

### LoyaltyStatus
```python
class LoyaltyStatus(str, Enum):
    BRONZE = "bronze"
    SILVER = "silver"
    GOLD = "gold"
    PLATINUM = "platinum"
```

### ServiceType
```python
class ServiceType(str, Enum):
    HOTEL = "hotel"
    TRANSPORTATION = "transportation"
    RESTAURANT = "restaurant"
    ACTIVITY = "activity"
    INSURANCE = "insurance"
    OTHER = "other"
```

### SeasonType
```python
class SeasonType(str, Enum):
    PEAK = "peak"
    OFF_PEAK = "off_peak"
    SHOULDER = "shoulder"
```

### AmenityType
```python
class AmenityType(str, Enum):
    ACCOMMODATION = "accommodation"
    TRANSPORT = "transport"
    TOUR = "tour"
    GENERAL = "general"
```

### DifficultyLevel
```python
class DifficultyLevel(str, Enum):
    EASY = "easy"
    MODERATE = "moderate"
    CHALLENGING = "challenging"
    DIFFICULT = "difficult"
```

### TransportType
```python
class TransportType(str, Enum):
    BUS = "bus"
    FLIGHT = "flight"
    TRAIN = "train"
    CAR = "car"
    BOAT = "boat"
    OTHER = "other"
```

### TourScheduleStatus
```python
class TourScheduleStatus(str, Enum):
    SCHEDULED = "scheduled"
    CONFIRMED = "confirmed"
    IN_PROGRESS = "in_progress"
    COMPLETED = "completed"
    CANCELLED = "cancelled"
```

### ResourceBookingStatus
```python
class ResourceBookingStatus(str, Enum):
    PENDING = "pending"
    CONFIRMED = "confirmed"
    CANCELLED = "cancelled"
```

### DiscountType
```python
class DiscountType(str, Enum):
    PERCENTAGE = "percentage"
    FIXED_AMOUNT = "fixed_amount"
    EARLY_BIRD = "early_bird"
    GROUP = "group"
    LOYALTY = "loyalty"
```

### BookingStatus
```python
class BookingStatus(str, Enum):
    PENDING = "pending"
    CONFIRMED = "confirmed"
    CANCELLED = "cancelled"
    COMPLETED = "completed"
    REFUNDED = "refunded"
```

### PaymentStatus
```python
class PaymentStatus(str, Enum):
    UNPAID = "unpaid"
    PARTIAL = "partial"
    PAID = "paid"
    REFUNDED = "refunded"
```

### TravelerType
```python
class TravelerType(str, Enum):
    ADULT = "adult"
    CHILD = "child"
    INFANT = "infant"
```

### Gender
```python
class Gender(str, Enum):
    MALE = "male"
    FEMALE = "female"
    OTHER = "other"
```

### PaymentMethod
```python
class PaymentMethod(str, Enum):
    CREDIT_CARD = "credit_card"
    DEBIT_CARD = "debit_card"
    BANK_TRANSFER = "bank_transfer"
    CASH = "cash"
    PAYPAL = "paypal"
    STRIPE = "stripe"
    OTHER = "other"
```

### TransactionStatus
```python
class TransactionStatus(str, Enum):
    PENDING = "pending"
    PROCESSING = "processing"
    COMPLETED = "completed"
    FAILED = "failed"
    REFUNDED = "refunded"
```

### InvoiceStatus
```python
class InvoiceStatus(str, Enum):
    DRAFT = "draft"
    SENT = "sent"
    PAID = "paid"
    OVERDUE = "overdue"
    CANCELLED = "cancelled"
```

### CancellationRequestStatus
```python
class CancellationRequestStatus(str, Enum):
    PENDING = "pending"
    APPROVED = "approved"
    REJECTED = "rejected"
    PROCESSED = "processed"
```

### ExpenseStatus
```python
class ExpenseStatus(str, Enum):
    PENDING = "pending"
    APPROVED = "approved"
    REJECTED = "rejected"
    PAID = "paid"
```

### CommissionStatus
```python
class CommissionStatus(str, Enum):
    PENDING = "pending"
    APPROVED = "approved"
    PAID = "paid"
```

### NotificationType
```python
class NotificationType(str, Enum):
    BOOKING = "booking"
    PAYMENT = "payment"
    CANCELLATION = "cancellation"
    REMINDER = "reminder"
    PROMOTION = "promotion"
    SYSTEM = "system"
    REVIEW = "review"
```

### DeliveryMethod
```python
class DeliveryMethod(str, Enum):
    IN_APP = "in_app"
    EMAIL = "email"
    SMS = "sms"
    PUSH = "push"
```

### DeliveryStatus
```python
class DeliveryStatus(str, Enum):
    PENDING = "pending"
    SENT = "sent"
    FAILED = "failed"
```

### DocumentType
```python
class DocumentType(str, Enum):
    VOUCHER = "voucher"
    TICKET = "ticket"
    INSURANCE = "insurance"
    PASSPORT = "passport"
    VISA = "visa"
    ITINERARY = "itinerary"
    INVOICE = "invoice"
    RECEIPT = "receipt"
    OTHER = "other"
```

## 8. Relationships

### User Relationships
- User → Customer (one-to-one, bidirectional)
- User → Tourguide (one-to-one, bidirectional)
- User → Serviceprovider (one-to-one, bidirectional)
- User → Booking (one-to-many as booking_agent, unidirectional)
- User → Expense (one-to-many as submitted_by, unidirectional)
- User → Expense (one-to-many as approved_by, unidirectional)
- User → Commission (one-to-many as booking_agent, unidirectional)
- User → Notification (one-to-many, unidirectional)
- User → Document (one-to-many as uploaded_by, unidirectional)
- User → Cancellationrequest (one-to-many as processed_by, unidirectional)

### Customer Relationships
- Customer → User (one-to-one, bidirectional)
- Customer → Booking (one-to-many, bidirectional)
- Customer → Review (one-to-many, bidirectional)
- Customer → Cancellationrequest (one-to-many, bidirectional)
- Customer → Document (one-to-many, unidirectional)

### Tourguide Relationships
- Tourguide → User (one-to-one, bidirectional)
- Tourguide → Tourschedule (one-to-many, bidirectional)
- Tourguide → Review (one-to-many, bidirectional)

### Serviceprovider Relationships
- Serviceprovider → User (one-to-one, bidirectional)
- Serviceprovider → Hotel (one-to-many, bidirectional)
- Serviceprovider → Transportation (one-to-many, bidirectional)

### Category Relationships
- Category → Tourpackage (one-to-many, bidirectional)

### Destination Relationships
- Destination → Tourpackagedestination (one-to-many, bidirectional)

### Amenity Relationships
- Amenity → Tourpackageamenity (one-to-many, bidirectional)
- Amenity → Hotelamenity (one-to-many, bidirectional)

### Tourpackage Relationships
- Tourpackage → Category (many-to-one, bidirectional)
- Tourpackage → Itinerary (one-to-many, bidirectional)
- Tourpackage → Tourpackagedestination (one-to-many, bidirectional)
- Tourpackage → Tourpackageamenity (one-to-many, bidirectional)
- Tourpackage → Tourschedule (one-to-many, bidirectional)
- Tourpackage → Review (one-to-many, bidirectional)
- Tourpackage → Discount (one-to-many, unidirectional)

### Tourpackagedestination Relationships
- Tourpackagedestination → Tourpackage (many-to-one, unidirectional)
- Tourpackagedestination → Destination (many-to-one, bidirectional)

### Tourpackageamenity Relationships
- Tourpackageamenity → Tourpackage (many-to-one, unidirectional)
- Tourpackageamenity → Amenity (many-to-one, bidirectional)

### Itinerary Relationships
- Itinerary → Tourpackage (many-to-one, unidirectional)

### Hotel Relationships
- Hotel → Serviceprovider (many-to-one, bidirectional)
- Hotel → Hotelamenity (one-to-many, bidirectional)
- Hotel → Roomtype (one-to-many, bidirectional)
- Hotel → Tourschedulehotel (one-to-many, unidirectional)

### Hotelamenity Relationships
- Hotelamenity → Hotel (many-to-one, unidirectional)
- Hotelamenity → Amenity (many-to-one, bidirectional)

### Roomtype Relationships
- Roomtype → Hotel (many-to-one, unidirectional)
- Roomtype → Tourschedulehotel (one-to-many, bidirectional)

### Transportation Relationships
- Transportation → Serviceprovider (many-to-one, bidirectional)
- Transportation → Tourscheduletransportation (one-to-many, unidirectional)

### Tourschedule Relationships
- Tourschedule → Tourpackage (many-to-one, bidirectional)
- Tourschedule → Tourguide (many-to-one, bidirectional)
- Tourschedule → Tourschedulehotel (one-to-many, bidirectional)
- Tourschedule → Tourscheduletransportation (one-to-many, bidirectional)
- Tourschedule → Booking (one-to-many, bidirectional)
- Tourschedule → Expense (one-to-many, unidirectional)

### Tourschedulehotel Relationships
- Tourschedulehotel → Tourschedule (many-to-one, unidirectional)
- Tourschedulehotel → Hotel (many-to-one, bidirectional)
- Tourschedulehotel → Roomtype (many-to-one, bidirectional)

### Tourscheduletransportation Relationships
- Tourscheduletransportation → Tourschedule (many-to-one, unidirectional)
- Tourscheduletransportation → Transportation (many-to-one, bidirectional)

### Discount Relationships
- Discount → Tourpackage (many-to-one, bidirectional)
- Discount → Booking (one-to-many, bidirectional)

### Booking Relationships
- Booking → Customer (many-to-one, bidirectional)
- Booking → Tourschedule (many-to-one, bidirectional)
- Booking → User (many-to-one as booking_agent, bidirectional)
- Booking → Discount (many-to-one, bidirectional)
- Booking → Traveler (one-to-many, bidirectional)
- Booking → Payment (one-to-many, bidirectional)
- Booking → Invoice (one-to-one, bidirectional)
- Booking → Cancellationrequest (one-to-one, bidirectional)
- Booking → Document (one-to-many, unidirectional)
- Booking → Review (one-to-many, unidirectional)
- Booking → Commission (one-to-many, unidirectional)

### Traveler Relationships
- Traveler → Booking (many-to-one, unidirectional)

### Payment Relationships
- Payment → Booking (many-to-one, unidirectional)

### Invoice Relationships
- Invoice → Booking (one-to-one, unidirectional)
- Invoice → Invoicelineitem (one-to-many, bidirectional)

### Invoicelineitem Relationships
- Invoicelineitem → Invoice (many-to-one, unidirectional)

### Review Relationships
- Review → Booking (many-to-one, unidirectional)
- Review → Customer (many-to-one, bidirectional)
- Review → Tourpackage (many-to-one, bidirectional)
- Review → Tourguide (many-to-one, bidirectional)

### Cancellationrequest Relationships
- Cancellationrequest → Booking (one-to-one, unidirectional)
- Cancellationrequest → Customer (many-to-one, bidirectional)
- Cancellationrequest → User (many-to-one as processed_by, bidirectional)

### Expense Relationships
- Expense → Tourschedule (many-to-one, bidirectional)
- Expense → User (many-to-one as submitted_by, bidirectional)
- Expense → User (many-to-one as approved_by, bidirectional)

### Commission Relationships
- Commission → Booking (many-to-one, bidirectional)
- Commission → User (many-to-one as booking_agent, bidirectional)

### Notification Relationships
- Notification → User (many-to-one, unidirectional)

### Document Relationships
- Document → Booking (many-to-one, unidirectional)
- Document → Customer (many-to-one, unidirectional)
- Document → User (many-to-one as uploaded_by, bidirectional)

## 9. API Endpoints

### 9.1 Authentication Endpoints
- `POST /auth/register` - Register new user account
- `POST /auth/login` - User login with credentials
- `POST /auth/logout` - User logout
- `POST /auth/reset-password` - Reset user password

### 9.2 User Management Endpoints
- `POST /users` - Create new user
- `GET /users` - List all users (with pagination and filters)
- `GET /users/{id}` - Get user by ID
- `PUT /users/{id}` - Update user
- `DELETE /users/{id}` - Delete user (soft delete)

### 9.3 Customer Management Endpoints
- `POST /customers` - Create customer profile
- `GET /customers` - List all customers (with pagination and filters)
- `GET /customers/{id}` - Get customer by ID
- `PUT /customers/{id}` - Update customer profile
- `DELETE /customers/{id}` - Delete customer (soft delete)

### 9.4 Tour Guide Management Endpoints
- `POST /tour-guides` - Create tour guide profile
- `GET /tour-guides` - List all tour guides (with pagination and filters)
- `GET /tour-guides/{id}` - Get tour guide by ID
- `PUT /tour-guides/{id}` - Update tour guide profile
- `DELETE /tour-guides/{id}` - Delete tour guide (soft delete)

### 9.5 Service Provider Management Endpoints
- `POST /service-providers` - Create service provider profile
- `GET /service-providers` - List all service providers (with pagination and filters)
- `GET /service-providers/{id}` - Get service provider by ID
- `PUT /service-providers/{id}` - Update service provider profile
- `DELETE /service-providers/{id}` - Delete service provider (soft delete)

### 9.6 Category Management Endpoints
- `POST /categories` - Create category
- `GET /categories` - List all categories (with pagination and filters)
- `GET /categories/{id}` - Get category by ID
- `PUT /categories/{id}` - Update category
- `DELETE /categories/{id}` - Delete category (soft delete)

### 9.7 Destination Management Endpoints
- `POST /destinations` - Create destination
- `GET /destinations` - List all destinations (with pagination and filters)
- `GET /destinations/{id}` - Get destination by ID
- `PUT /destinations/{id}` - Update destination
- `DELETE /destinations/{id}` - Delete destination (soft delete)

### 9.8 Season Management Endpoints
- `POST /seasons` - Create season
- `GET /seasons` - List all seasons (with pagination and filters)
- `GET /seasons/{id}` - Get season by ID
- `PUT /seasons/{id}` - Update season
- `DELETE /seasons/{id}` - Delete season (soft delete)

### 9.9 Amenity Management Endpoints
- `POST /amenities` - Create amenity
- `GET /amenities` - List all amenities (with pagination and filters)
- `GET /amenities/{id}` - Get amenity by ID
- `PUT /amenities/{id}` - Update amenity
- `DELETE /amenities/{id}` - Delete amenity (soft delete)

### 9.10 Tour Package Management Endpoints
- `POST /tour-packages` - Create tour package
- `GET /tour-packages` - List all tour packages (with pagination and filters)
- `GET /tour-packages/{id}` - Get tour package by ID
- `GET /tour-packages/{id}/details` - Get tour package with full details (eager loaded)
- `GET /tour-packages/{id}/availability` - Check tour package availability
- `GET /tour-packages/search` - Search tour packages with filters
- `PUT /tour-packages/{id}` - Update tour package
- `DELETE /tour-packages/{id}` - Delete tour package (soft delete)

### 9.11 Itinerary Management Endpoints
- `POST /itineraries` - Create itinerary
- `GET /itineraries` - List all itineraries (with pagination and filters)
- `GET /itineraries/{id}` - Get itinerary by ID
- `PUT /itineraries/{id}` - Update itinerary
- `DELETE /itineraries/{id}` - Delete itinerary (soft delete)

### 9.12 Hotel Management Endpoints
- `POST /hotels` - Create hotel
- `GET /hotels` - List all hotels (with pagination and filters)
- `GET /hotels/{id}` - Get hotel by ID
- `GET /hotels/{id}/details` - Get hotel with full details (eager loaded)
- `PUT /hotels/{id}` - Update hotel
- `DELETE /hotels/{id}` - Delete hotel (soft delete)

### 9.13 Room Type Management Endpoints
- `POST /room-types` - Create room type
- `GET /room-types` - List all room types (with pagination and filters)
- `GET /room-types/{id}` - Get room type by ID
- `PUT /room-types/{id}` - Update room type
- `DELETE /room-types/{id}` - Delete room type (soft delete)

### 9.14 Transportation Management Endpoints
- `POST /transportations` - Create transportation
- `GET /transportations` - List all transportations (with pagination and filters)
- `GET /transportations/{id}` - Get transportation by ID
- `PUT /transportations/{id}` - Update transportation
- `DELETE /transportations/{id}` - Delete transportation (soft delete)

### 9.15 Tour Schedule Management Endpoints
- `POST /tour-schedules` - Create tour schedule
- `GET /tour-schedules` - List all tour schedules (with pagination and filters)
- `GET /tour-schedules/{id}` - Get tour schedule by ID
- `GET /tour-schedules/{id}/details` - Get tour schedule with full details (eager loaded)
- `POST /tour-schedules/{id}/assign-guide` - Assign tour guide to schedule
- `POST /tour-schedules/{id}/assign-resources` - Assign hotels and transportation to schedule
- `POST /tour-schedules/{id}/complete` - Mark tour schedule as completed
- `PUT /tour-schedules/{id}` - Update tour schedule
- `DELETE /tour-schedules/{id}` - Delete tour schedule (soft delete)

### 9.16 Discount Management Endpoints
- `POST /discounts` - Create discount
- `GET /discounts` - List all discounts (with pagination and filters)
- `GET /discounts/{id}` - Get discount by ID
- `PUT /discounts/{id}` - Update discount
- `DELETE /discounts/{id}` - Delete discount (soft delete)

### 9.17 Booking Management Endpoints
- `POST /bookings` - Create booking
- `GET /bookings` - List all bookings (with pagination and filters)
- `GET /bookings/{id}` - Get booking by ID
- `GET /bookings/{id}/details` - Get booking with full details (eager loaded)
- `GET /bookings/{id}/invoice` - Get booking invoice
- `POST /bookings/{id}/confirm` - Confirm booking
- `POST /bookings/{id}/cancel` - Cancel booking
- `POST /bookings/{id}/modify` - Modify booking
- `PUT /bookings/{id}` - Update booking
- `DELETE /bookings/{id}` - Delete booking (soft delete)

### 9.18 Traveler Management Endpoints
- `POST /travelers` - Create traveler
- `GET /travelers` - List all travelers (with pagination and filters)
- `GET /travelers/{id}` - Get traveler by ID
- `PUT /travelers/{id}` - Update traveler
- `DELETE /travelers/{id}` - Delete traveler (soft delete)

### 9.19 Payment Management Endpoints
- `POST /payments` - Create payment
- `POST /payments/process` - Process payment transaction
- `POST /payments/{id}/refund` - Process payment refund
- `GET /payments` - List all payments (with pagination and filters)
- `GET /payments/{id}` - Get payment by ID
- `PUT /payments/{id}` - Update payment
- `DELETE /payments/{id}` - Delete payment (soft delete)

### 9.20 Invoice Management Endpoints
- `POST /invoices` - Create invoice
- `GET /invoices` - List all invoices (with pagination and filters)
- `GET /invoices/{id}` - Get invoice by ID
- `GET /invoices/{id}/details` - Get invoice with full details (eager loaded)
- `PUT /invoices/{id}` - Update invoice
- `DELETE /invoices/{id}` - Delete invoice (soft delete)

### 9.21 Review Management Endpoints
- `POST /reviews` - Create review
- `GET /reviews` - List all reviews (with pagination and filters)
- `GET /reviews/{id}` - Get review by ID
- `GET /reviews/{id}/details` - Get review with full details (eager loaded)
- `POST /reviews/{id}/publish` - Publish review
- `POST /reviews/{id}/respond` - Add management response to review
- `PUT /reviews/{id}` - Update review
- `DELETE /reviews/{id}` - Delete review (soft delete)

### 9.22 Cancellation Request Management Endpoints
- `POST /cancellation-requests` - Create cancellation request
- `GET /cancellation-requests` - List all cancellation requests (with pagination and filters)
- `GET /cancellation-requests/{id}` - Get cancellation request by ID
- `POST /cancellation-requests/{id}/approve` - Approve cancellation request
- `POST /cancellation-requests/{id}/reject` - Reject cancellation request
- `PUT /cancellation-requests/{id}` - Update cancellation request
- `DELETE /cancellation-requests/{id}` - Delete cancellation request (soft delete)

### 9.23 Expense Management Endpoints
- `POST /expenses` - Create expense
- `GET /expenses` - List all expenses (with pagination and filters)
- `GET /expenses/{id}` - Get expense by ID
- `POST /expenses/{id}/approve` - Approve expense
- `POST /expenses/{id}/reject` - Reject expense
- `PUT /expenses/{id}` - Update expense
- `DELETE /expenses/{id}` - Delete expense (soft delete)

### 9.24 Commission Management Endpoints
- `POST /commissions` - Create commission
- `GET /commissions` - List all commissions (with pagination and filters)
- `GET /commissions/{id}` - Get commission by ID
- `POST /commissions/{id}/approve` - Approve commission
- `POST /commissions/{id}/pay` - Mark commission as paid
- `PUT /commissions/{id}` - Update commission
- `DELETE /commissions/{id}` - Delete commission (soft delete)

### 9.25 Notification Management Endpoints
- `POST /notifications` - Create notification
- `GET /notifications` - List all notifications (with pagination and filters)
- `GET /notifications/{id}` - Get notification by ID
- `POST /notifications/{id}/mark-read` - Mark notification as read
- `PUT /notifications/{id}` - Update notification
- `DELETE /notifications/{id}` - Delete notification (soft delete)

### 9.26 Document Management Endpoints
- `POST /documents` - Create document
- `POST /documents/upload` - Upload document file
- `GET /documents` - List all documents (with pagination and filters)
- `GET /documents/{id}` - Get document by ID
- `PUT /documents/{id}` - Update document
- `DELETE /documents/{id}` - Delete document (soft delete)

### 9.27 Reporting Endpoints
- `GET /reports/bookings` - Generate bookings report
- `GET /reports/revenue` - Generate revenue report
- `GET /reports/commissions` - Generate commissions report
- `GET /reports/expenses` - Generate expenses report

## 10. Workflow Logic

### 10.1 Customer Booking Workflow
1. **Search Tours**: Customer searches for tour packages using filters (destination, dates, category, price range)
2. **View Details**: Customer views tour package details including itinerary, destinations, amenities, and reviews
3. **Check Availability**: System checks tour schedule availability for selected dates
4. **Create Booking**: Customer creates booking with traveler details (adults, children, infants)
5. **Apply Discount**: System validates and applies discount code if provided
6. **Calculate Pricing**: System calculates subtotal, discount, tax, and total amount
7. **Process Payment**: Customer makes payment (minimum 30% deposit required)
8. **Confirm Booking**: System confirms booking, updates tour schedule capacity, generates invoice, and sends confirmation notification
9. **Update Loyalty Points**: System awards loyalty points to customer based on booking amount

### 10.2 Tour Schedule Creation Workflow
1. **Create Schedule**: Tour manager creates tour schedule for a tour package with departure and return dates
2. **Set Capacity**: Tour manager sets available slots based on tour package constraints
3. **Assign Guide**: Tour manager assigns tour guide to the schedule
4. **Assign Hotels**: Tour manager assigns hotels and room types for each night
5. **Assign Transportation**: Tour manager assigns transportation for inter-city travel
6. **Confirm Resources**: Service providers confirm hotel and transportation bookings
7. **Publish Schedule**: Tour manager publishes schedule making it available for bookings
8. **Monitor Bookings**: System tracks booked slots and updates availability in real-time

### 10.3 Booking Agent Workflow
1. **Customer Inquiry**: Booking agent receives customer inquiry via phone/email
2. **Search Tours**: Agent searches for suitable tour packages based on customer requirements
3. **Create Booking**: Agent creates booking on behalf of customer
4. **Process Payment**: Agent processes payment using customer's payment method
5. **Send Confirmation**: Agent sends booking confirmation to customer
6. **Earn Commission**: System calculates and records agent commission (10% of booking value)
7. **Follow-up**: Agent follows up with customer for full payment before departure

### 10.4 Cancellation Request Workflow
1. **Submit Request**: Customer submits cancellation request with reason
2. **Calculate Refund**: System calculates cancellation fee and refund amount based on cancellation policy
   - More than 30 days: 100% refund minus processing fee
   - 15-30 days: 50% refund
   - 7-14 days: 25% refund
   - Less than 7 days: No refund
3. **Review Request**: Tour manager reviews cancellation request
4. **Approve/Reject**: Tour manager approves or rejects request with notes
5. **Process Refund**: If approved, finance manager processes refund payment
6. **Update Booking**: System updates booking status to cancelled
7. **Release Capacity**: System releases tour schedule capacity
8. **Send Notification**: System sends cancellation confirmation to customer

### 10.5 Tour Completion Workflow
1. **Complete Tour**: Tour guide marks tour schedule as completed
2. **Update Bookings**: System updates all bookings for the schedule to completed status
3. **Trigger Reviews**: System sends review request notifications to all customers
4. **Calculate Commissions**: System calculates and approves commissions for booking agents
5. **Process Expenses**: Tour guide submits expenses for the tour
6. **Approve Expenses**: Finance manager reviews and approves expenses
7. **Update Statistics**: System updates tour guide statistics (total tours conducted, rating)

### 10.6 Review Submission Workflow
1. **Submit Review**: Customer submits review within 30 days of tour completion
2. **Verify Booking**: System verifies customer has completed the tour
3. **Moderate Review**: Tour manager reviews and moderates the review
4. **Publish Review**: Tour manager publishes approved review
5. **Update Ratings**: System updates tour package and tour guide ratings
6. **Management Response**: Tour manager can add management response to review
7. **Send Notification**: System notifies customer when review is published

### 10.7 Expense Approval Workflow
1. **Submit Expense**: Tour guide submits expense with receipt and details
2. **Review Expense**: Finance manager reviews expense submission
3. **Approve/Reject**: Finance manager approves or rejects expense with notes
4. **Process Payment**: If approved, finance manager processes payment to tour guide
5. **Update Records**: System updates expense status and tour schedule financials
6. **Send Notification**: System notifies tour guide of approval/rejection

### 10.8 Commission Payment Workflow
1. **Calculate Commission**: System calculates commission when booking is confirmed
2. **Approve Commission**: Finance manager reviews and approves commission
3. **Process Payment**: Finance manager processes commission payment to booking agent
4. **Update Status**: System updates commission status to paid
5. **Send Notification**: System notifies booking agent of payment
6. **Generate Report**: System includes commission in financial reports

### 10.9 Service Provider Confirmation Workflow
1. **Receive Request**: Service provider receives hotel/transportation booking request
2. **Check Availability**: Service provider checks availability for requested dates
3. **Confirm Booking**: Service provider confirms booking within 24 hours
4. **Update Status**: System updates booking status to confirmed
5. **Send Notification**: System notifies tour manager of confirmation
6. **Track Performance**: System tracks service provider response time and reliability

### 10.10 Automated Notification Workflow
1. **Booking Confirmation**: Send confirmation email/SMS immediately after booking
2. **Payment Reminder**: Send reminder 30 days before departure for full payment
3. **Tour Reminder**: Send reminder 7 days before departure with meeting details
4. **Document Reminder**: Send reminder to upload required documents (passport, visa)
5. **Review Request**: Send review request 1 day after tour completion
6. **Promotion Alerts**: Send promotional notifications for new tours and discounts

## 11. Business Logic

### 11.1 Booking Business Rules
- **Minimum Lead Time**: Bookings must be made at least 48 hours before departure date
- **Booking Expiration**: Pending bookings expire after 24 hours if payment not received
- **Minimum Travelers**: Tour requires minimum 4 travelers to proceed
- **Maximum Capacity**: Bookings cannot exceed tour schedule available slots
- **Guide Ratio**: Maximum guide-to-traveler ratio is 1:20
- **Age Restrictions**: Bookings must comply with tour package min/max age requirements

### 11.2 Pricing Business Rules
- **Child Pricing**: Child price is 70% of adult base price
- **Infant Pricing**: Infant price is 10% of adult base price
- **Single Supplement**: Additional charge for single occupancy rooms
- **Seasonal Pricing**: Base price multiplied by season price multiplier
- **Group Discount**: Automatic discount for groups of 10+ travelers
- **Early Bird Discount**: Discount for bookings made 60+ days in advance
- **Loyalty Discount**: Discount based on customer loyalty tier

### 11.3 Payment Business Rules
- **Deposit Requirement**: Minimum 30% deposit required at booking
- **Full Payment Deadline**: Full payment due 30 days before departure
- **Payment Methods**: Support multiple payment methods (credit card, bank transfer, PayPal, Stripe)
- **Currency**: All transactions in USD unless specified
- **Refund Processing**: Refunds processed within 7-10 business days
- **Commission Calculation**: Agent commission is 10% of total booking amount

### 11.4 Cancellation Business Rules
- **Free Cancellation**: 100% refund (minus processing fee) for cancellations 30+ days before departure
- **Partial Refund**: 50% refund for cancellations 15-30 days before departure
- **Limited Refund**: 25% refund for cancellations 7-14 days before departure
- **No Refund**: No refund for cancellations less than 7 days before departure
- **Processing Fee**: Fixed processing fee of $50 for all cancellations
- **Approval Required**: All cancellation requests require tour manager approval

### 11.5 Discount Business Rules
- **Single Discount**: Only one discount code per booking
- **Validity Period**: Discount must be valid for booking date
- **Minimum Amount**: Booking must meet minimum amount requirement
- **Maximum Discount**: Discount cannot exceed maximum discount amount
- **Usage Limit**: Discount code has usage limit across all bookings
- **Package Specific**: Some discounts apply only to specific tour packages
- **Automatic Application**: Group and loyalty discounts applied automatically

### 11.6 Review Business Rules
- **Eligibility**: Only customers who completed the tour can submit reviews
- **Time Limit**: Reviews must be submitted within 30 days of tour completion
- **Rating Range**: All ratings must be between 1 and 5
- **Moderation**: All reviews require moderation before publishing
- **Verification**: Reviews marked as verified if from confirmed booking
- **Management Response**: Tour manager can respond to reviews once
- **Photo Upload**: Maximum 5 photos per review

### 11.7 Loyalty Program Business Rules
- **Points Earning**: Earn 1 point per $1 spent on bookings
- **Bronze Tier**: 0-999 points, 0% discount
- **Silver Tier**: 1000-2499 points, 5% discount
- **Gold Tier**: 2500-4999 points, 10% discount
- **Platinum Tier**: 5000+ points, 15% discount
- **Points Expiry**: Points expire after 2 years of inactivity
- **Tier Benefits**: Higher tiers get priority booking and exclusive offers

### 11.8 Resource Assignment Business Rules
- **Hotel Capacity**: Hotel rooms must accommodate all travelers
- **Transportation Capacity**: Transportation must have sufficient seats
- **Confirmation Deadline**: Service providers must confirm within 24 hours
- **Backup Resources**: Tour manager should assign backup resources
- **Cost Tracking**: All resource costs tracked for financial reporting
- **Quality Standards**: Only verified service providers can be assigned

### 11.9 Financial Business Rules
- **Expense Approval**: All expenses require finance manager approval
- **Receipt Required**: Expenses over $100 require receipt upload
- **Commission Approval**: Commissions approved after booking completion
- **Payment Terms**: Service providers paid within 30 days of service delivery
- **Tax Calculation**: Applicable taxes calculated based on customer location
- **Invoice Generation**: Invoice generated automatically upon booking confirmation

### 11.10 Notification Business Rules
- **Multi-Channel**: Notifications sent via in-app, email, and SMS
- **Delivery Priority**: Critical notifications (payment, cancellation) sent immediately
- **Batch Processing**: Promotional notifications sent in batches
- **Opt-Out**: Users can opt-out of promotional notifications
- **Retry Logic**: Failed notifications retried up to 3 times
- **Read Tracking**: Track notification read status for analytics

## 12. Validation Rules

### 12.1 User Validation
- Email must be unique and valid format (RFC 5322)
- Password minimum 8 characters with uppercase, lowercase, number, and special character
- Phone number must be valid format (E.164)
- Role must be one of the defined enum values
- First name and last name required and non-empty

### 12.2 Customer Validation
- Date of birth must be in the past
- Passport expiry date must be at least 6 months after tour return date
- Nationality must be valid ISO country code
- Postal code must match country format
- Emergency contact phone must be valid format

### 12.3 Tour Package Validation
- Duration days must be greater than 0
- Duration nights must be less than duration days
- Min group size must be less than max group size
- Base price must be greater than 0
- Child price must be less than base price
- Slug must be unique and URL-friendly
- Min age must be less than max age if both specified

### 12.4 Tour Schedule Validation
- Departure date must be after current date
- Return date must be after departure date
- Available slots must be greater than 0
- Booked slots must be less than or equal to available slots
- Meeting time must be valid time format
- Tour guide must be available for the dates

### 12.5 Booking Validation
- Number of travelers must be within tour package limits
- Booking date must be at least 48 hours before departure
- Total amount must match calculated amount (subtotal - discount + tax)
- Amount paid must be at least 30% of total amount
- Amount due must equal total amount minus amount paid
- Discount code must be valid and not expired

### 12.6 Payment Validation
- Payment amount must be greater than 0
- Payment amount must not exceed booking amount due
- Currency must be valid ISO currency code
- Transaction ID must be unique if provided
- Payment method must be one of the defined enum values

### 12.7 Review Validation
- Overall rating must be between 1 and 5
- All specific ratings must be between 1 and 5 if provided
- Review can only be submitted for completed bookings
- Review must be submitted within 30 days of tour completion
- Customer can submit only one review per booking

### 12.8 Discount Validation
- Discount code must be unique
- Valid from date must be before valid to date
- Discount value must be greater than 0
- For percentage discounts, value must be between 0 and 100
- Usage count must not exceed usage limit
- Booking amount must meet minimum requirement

### 12.9 Cancellation Request Validation
- Cancellation request can only be submitted for confirmed bookings
- Refund amount must be less than or equal to amount paid
- Cancellation fee must be greater than or equal to 0
- Reason must be non-empty
- Request cannot be submitted for completed tours

### 12.10 Document Validation
- File name must be non-empty
- File URL must be valid URL format
- File size must not exceed 10MB
- MIME type must be allowed (PDF, JPG, PNG, DOCX)
- Document type must be one of the defined enum values
- Either booking_id or customer_id must be provided

### 12.11 Hotel Validation
- Star rating must be between 1 and 5 if provided
- Check-in time must be before check-out time
- Latitude must be between -90 and 90
- Longitude must be between -180 and 180
- Email must be valid format if provided

### 12.12 Room Type Validation
- Max occupancy must be greater than 0
- Price per night must be greater than 0
- Total rooms must be greater than 0
- Room size must be greater than 0 if provided

### 12.13 Transportation Validation
- Capacity must be greater than 0
- Transport type must be one of the defined enum values
- Vehicle number must be unique if provided

### 12.14 Expense Validation
- Amount must be greater than 0
- Expense date must not be in the future
- Currency must be valid ISO currency code
- Receipt URL required for expenses over $100
- Category must be non-empty

### 12.15 Commission Validation
- Booking amount must be greater than 0
- Commission rate must be between 0 and 100
- Commission amount must equal booking amount × commission rate / 100
- Commission can only be created for confirmed bookings

## 13. Implementation Notes

### 13.1 Technology Stack
- **Framework**: FastAPI (latest stable version) for high-performance async API development
- **ORM**: SQLAlchemy 2.0+ with declarative models for database operations
- **Database**: SQLite for development, PostgreSQL-ready for production
- **Validation**: Pydantic v2 for request/response schema validation and serialization
- **Package Manager**: uv for fast and reliable dependency management
- **Documentation**: Automatic OpenAPI/Swagger documentation via FastAPI

### 13.2 Architecture Patterns
- **Service Layer Pattern**: Business logic separated into service classes
- **Repository Pattern**: Data access abstraction through repository classes
- **Dependency Injection**: FastAPI's `Depends()` system for database session injection
- **DTO Pattern**: Pydantic schemas for data transfer between layers
- **Factory Pattern**: Model factories for test data generation

### 13.3 Project Structure
```
app/
├── api/
│   ├── v1/
│   │   ├── endpoints/
│   │   │   ├── auth.py
│   │   │   ├── users.py
│   │   │   ├── bookings.py
│   │   │   ├── tour_packages.py
│   │   │   └── ...
│   │   └── router.py
│   └── deps.py
├── core/
│   ├── config.py
│   ├── security.py
│   └── database.py
├── models/
│   ├── user.py
│   ├── booking.py
│   ├── tour_package.py
│   └── ...
├── schemas/
│   ├── user.py
│   ├── booking.py
│   ├── tour_package.py
│   └── ...
├── services/
│   ├── user_service.py
│   ├── booking_service.py
│   ├── tour_package_service.py
│   └── ...
├── repositories/
│   ├── user_repository.py
│   ├── booking_repository.py
│   ├── tour_package_repository.py
│   └── ...
└── main.py
```

### 13.4 Database Session Management
- Database sessions managed using FastAPI's dependency injection
- `get_db()` dependency function yields SQLAlchemy session
- Automatic session cleanup and transaction management
- Connection pooling configured for optimal performance

### 13.5 Error Handling
- Custom exception classes for business logic errors
- Global exception handlers for consistent error responses
- HTTP status codes follow REST conventions
- Detailed error messages for validation failures
- Logging of all errors for debugging and monitoring

### 13.6 API Versioning
- API versioned using URL prefix (e.g., `/api/v1/`)
- Version 1 implements all specified endpoints
- Future versions can coexist with backward compatibility

### 13.7 Pagination and Filtering
- List endpoints support pagination with `skip` and `limit` parameters
- Filtering supported through query parameters
- Sorting supported with `sort_by` and `order` parameters
- Total count returned in response headers

### 13.8 Eager Loading
- Detail endpoints use SQLAlchemy's `joinedload` for eager loading
- Reduces N+1 query problems
- Optimized queries for complex relationships
- Configurable loading strategies per endpoint

### 13.9 Soft Delete
- Most entities use soft delete (is_deleted flag)
- Maintains data integrity and audit trail
- Deleted records excluded from default queries
- Admin endpoints can access deleted records

### 13.10 Automatic Timestamps
- `created_at` and `updated_at` fields managed automatically
- SQLAlchemy event listeners update timestamps
- Timezone-aware timestamps stored in UTC

### 13.11 UUID Primary Keys
- All entities use UUID (v4) as primary keys
- Generated automatically on creation
- Provides better security and scalability than sequential IDs

### 13.12 OpenAPI Documentation
- Automatic OpenAPI schema generation
- Interactive Swagger UI at `/docs`
- ReDoc documentation at `/redoc`
- Comprehensive endpoint descriptions and examples

## 14. Assumptions

### 14.1 Data Assumptions
- All monetary values stored in decimal format with 2 decimal places
- Default currency is USD unless specified otherwise
- Timestamps stored in UTC timezone
- Date formats follow ISO 8601 standard
- Phone numbers stored in E.164 format

### 14.2 Authentication Assumptions
- User authentication uses JWT tokens
- Token expiration set to 24 hours
- Refresh tokens supported for extended sessions
- Password hashing uses bcrypt algorithm
- Session management handled by client

### 14.3 File Storage Assumptions
- File uploads stored in cloud storage (S3-compatible)
- Maximum file size is 10MB
- Supported file types: PDF, JPG, PNG, DOCX
- Files organized by entity type and ID
- CDN used for file delivery

### 14.4 Email Assumptions
- Email notifications sent asynchronously via message queue
- Email templates use HTML format
- SMTP or email service provider (SendGrid, AWS SES) integration
- Bounce and complaint handling implemented
- Unsubscribe links included in promotional emails

### 14.5 Payment Gateway Assumptions
- Payment gateway integration supports multiple providers
- PCI DSS compliance for credit card handling
- Webhook support for payment status updates
- Idempotency keys used for payment requests
- Payment reconciliation automated

### 14.6 Multi-Tenancy Assumptions
- System supports multi-tenancy for different tourism companies
- Tenant isolation at database level
- Tenant identified by subdomain or API key
- Shared infrastructure with data separation
- Tenant-specific configuration supported

### 14.7 Performance Assumptions
- API response time target: < 200ms for simple queries
- Database query optimization with proper indexes
- Caching layer (Redis) for frequently accessed data
- Rate limiting implemented to prevent abuse
- Load balancing for horizontal scaling

### 14.8 Security Assumptions
- HTTPS required for all API endpoints
- CORS configured for allowed origins
- SQL injection prevention through parameterized queries
- XSS prevention through input sanitization
- CSRF protection for state-changing operations

### 14.9 Audit Trail Assumptions
- All critical operations logged for audit
- User actions tracked with timestamp and IP address
- Audit logs stored separately from application data
- Retention period of 7 years for compliance
- Audit logs immutable and tamper-proof

### 14.10 Internationalization Assumptions
- System supports multiple languages for customer-facing content
- Default language is English
- Currency conversion supported for international customers
- Date and time formats localized based on user preference
- Translation management through external service

## 15. Future Improvements

### 15.1 Database Improvements
- **Alembic Migrations**: Implement Alembic for database schema migrations and version control
- **PostgreSQL Migration**: Migrate from SQLite to PostgreSQL for production deployment
- **Read Replicas**: Configure read replicas for improved query performance
- **Database Sharding**: Implement sharding strategy for horizontal scalability
- **Full-Text Search**: Add PostgreSQL full-text search for tour packages and destinations

### 15.2 Authentication & Authorization
- **JWT Authentication**: Implement JWT-based authentication with access and refresh tokens
- **Role-Based Access Control (RBAC)**: Implement comprehensive RBAC system with granular permissions
- **OAuth2 Integration**: Add OAuth2 support for social login (Google, Facebook)
- **Two-Factor Authentication**: Implement 2FA for enhanced security
- **API Key Management**: Add API key authentication for third-party integrations

### 15.3 Testing
- **Unit Tests**: Comprehensive unit tests for all service layer functions
- **Integration Tests**: Integration tests for API endpoints
- **End-to-End Tests**: E2E tests for critical user workflows
- **Load Testing**: Performance and load testing with tools like Locust
- **Test Coverage**: Achieve 80%+ code coverage

### 15.4 DevOps & Deployment
- **Docker Containerization**: Containerize application with Docker
- **Docker Compose**: Multi-container setup for local development
- **CI/CD Pipeline**: Automated testing and deployment pipeline
- **Kubernetes Deployment**: Production deployment on Kubernetes
- **Infrastructure as Code**: Terraform or CloudFormation for infrastructure management

### 15.5 Monitoring & Observability
- **Application Monitoring**: Implement APM with tools like New Relic or Datadog
- **Logging**: Centralized logging with ELK stack or CloudWatch
- **Metrics**: Prometheus and Grafana for metrics visualization
- **Distributed Tracing**: OpenTelemetry for distributed tracing
- **Health Checks**: Comprehensive health check endpoints

### 15.6 Performance Optimization
- **Caching Layer**: Redis caching for frequently accessed data
- **Query Optimization**: Database query optimization and indexing
- **Async Processing**: Celery for background task processing
- **CDN Integration**: CloudFront or Cloudflare for static asset delivery
- **Database Connection Pooling**: Optimize connection pool configuration

### 15.7 Feature Enhancements
- **Real-Time Chat**: WebSocket-based chat between customers and agents
- **Mobile App API**: Optimized API endpoints for mobile applications
- **Flight/Hotel API Integration**: Integration with third-party booking APIs
- **Dynamic Pricing**: AI-powered dynamic pricing based on demand
- **Recommendation Engine**: ML-based tour recommendations
- **Multi-Language Support**: Comprehensive i18n for global customers
- **Advanced Loyalty Program**: Gamification and tier-based benefits
- **Referral Program**: Customer referral system with rewards
- **Social Media Integration**: Share tours on social platforms
- **Advanced Analytics**: Business intelligence dashboard
- **Automated Scheduling**: AI-powered tour guide scheduling optimization
- **Accounting Integration**: QuickBooks and Xero integration
- **Sentiment Analysis**: AI-powered review sentiment analysis
- **Predictive Analytics**: Booking trend prediction
- **Blockchain Documents**: Secure document storage on blockchain

### 15.8 API Enhancements
- **GraphQL API**: Alternative GraphQL API for flexible queries
- **Webhook Support**: Webhooks for event notifications
- **Batch Operations**: Bulk create/update/delete endpoints
- **API Rate Limiting**: Advanced rate limiting with tiered plans
- **API Documentation**: Enhanced documentation with code examples

### 15.9 Security Enhancements
- **Security Audit**: Regular security audits and penetration testing
- **Encryption at Rest**: Database encryption at rest
- **Secrets Management**: Vault or AWS Secrets Manager for secrets
- **DDoS Protection**: CloudFlare or AWS Shield for DDoS protection
- **Compliance**: GDPR, PCI DSS, and SOC 2 compliance

### 15.10 Business Intelligence
- **Data Warehouse**: Separate data warehouse for analytics
- **ETL Pipeline**: Automated ETL for data processing
- **Reporting Dashboard**: Executive dashboard with KPIs
- **Customer Analytics**: Customer behavior and segmentation analysis
- **Revenue Forecasting**: ML-based revenue forecasting