# Backend Specification

## 1. System Summary

**System Name:** Bus Booking System

**Description:** A real-time seat reservation platform for bus travel with temporary reservation locks, concurrent booking protection, and comprehensive admin monitoring.

**Core Capabilities:**
- User authentication and role-based access control (guest, customer, admin)
- Bus route and schedule management with dynamic seat inventory
- Temporary seat reservations with 15-minute automatic expiry
- Concurrent booking protection using database-level locking
- Payment processing with gateway integration
- Ticket generation with QR codes
- Booking cancellation with automated refund calculation
- Real-time seat availability updates via WebSocket
- Comprehensive activity logging and audit trail
- Admin dashboard with reservation monitoring and manual release

**Key Features:**
- 15-minute reservation lock with automatic release
- Maximum 6 seats per transaction
- Concurrent booking prevention using SELECT FOR UPDATE
- Background job for reservation expiry (runs every 30 seconds)
- Real-time WebSocket updates for seat availability
- Refund policy: 100% (>24h), 50% (2-24h), 0% (<2h before departure)
- Comprehensive audit logging for all critical operations

## 2. Source Input Summary

**DDL Schema:** 13 tables (users, routes, buses, schedules, seats, reservations, reservation_seats, bookings, booking_seats, payments, tickets, cancellations, activity_logs)

**PRD Document:** Comprehensive workflows for reservation timeout, concurrent booking, payment processing, and real-time seat updates via WebSocket

**Source Files:**
- DDL: `/Users/braintip/Desktop/dpg/pipeline/step-02-prd-generation/output/20260512_094436/schema.sql`
- PRD: `/Users/braintip/Desktop/dpg/pipeline/step-02-prd-generation/output/20260512_094436/full_prd.md`

## 3. Generation Mode

**Mode:** Strict

All entities, fields, relationships, and business rules are derived directly from the provided DDL schema and PRD document. No additional features or modifications have been introduced beyond what is explicitly specified in the source materials.

## 4. Backend Scope

### Modules

| Module | Entities | Description |
|--------|----------|-------------|
| user_management | User | User authentication, registration, profile management, and role-based access control |
| route_management | Route | Bus route definitions with origin, destination, distance, and duration |
| bus_management | Bus | Bus fleet management with seat configurations, types, and amenities |
| schedule_management | Schedule, Seat | Bus schedule creation, seat inventory generation, and availability tracking |
| reservation_management | Reservation, Reservationseat | Temporary seat reservations with 15-minute expiry locks and automatic release |
| booking_management | Booking, Bookingseat, Ticket | Confirmed bookings, ticket generation, and booking history |
| payment_management | Payment | Payment processing, gateway integration, and transaction tracking |
| cancellation_management | Cancellation | Booking cancellations, refund calculations, and seat release |
| activity_logging | Activitylog | Comprehensive audit trail for all system activities and user actions |

### Technology Stack

- **Framework:** FastAPI
- **ORM:** SQLAlchemy
- **Database:** SQLite (development), PostgreSQL (production)
- **Validation:** Pydantic v2
- **Authentication:** JWT tokens
- **Real-time:** WebSocket
- **Background Jobs:** Scheduled tasks for reservation expiry
- **Package Manager:** uv

## 5. Roles

| Role | Description | Access Level |
|------|-------------|--------------|
| guest | Unauthenticated user | Can search schedules and view seat availability |
| customer | Registered user | Can create reservations, make bookings, view booking history, cancel bookings |
| admin | System administrator | Full access to all resources, can manage routes/buses/schedules, monitor reservations, manually release stuck reservations, view activity logs |

## 6. Entities and Fields

### User

**Table:** `users`

**Description:** System user with authentication credentials and profile information

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique user identifier |
| email | str | Yes | User email address (unique) |
| password_hash | str | Yes | Hashed password using bcrypt |
| first_name | str | Yes | User first name |
| last_name | str | Yes | User last name |
| phone_number | str | Yes | User phone number |
| role | str | Yes | User role (guest, customer, admin) |
| is_active | bool | Yes | Account active status |
| created_at | datetime | Yes | Account creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

---

### Route

**Table:** `routes`

**Description:** Bus travel route between origin and destination cities

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique route identifier |
| origin_city | str | Yes | Departure city |
| destination_city | str | Yes | Arrival city |
| distance_km | float | Yes | Route distance in kilometers |
| estimated_duration_minutes | int | Yes | Estimated travel duration in minutes |
| is_active | bool | Yes | Route active status |
| created_at | datetime | Yes | Route creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

---

### Bus

**Table:** `buses`

**Description:** Physical bus vehicle with seating configuration and amenities

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique bus identifier |
| bus_number | str | Yes | Bus registration number (unique) |
| bus_type | str | Yes | Bus type (standard, deluxe, sleeper, semi_sleeper) |
| total_seats | int | Yes | Total seat capacity |
| seat_layout_config | dict | Yes | JSON seat layout configuration |
| amenities | Optional[dict] | No | JSON amenities list |
| is_active | bool | Yes | Bus active status |
| created_at | datetime | Yes | Bus creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `is_active`

---

### Schedule

**Table:** `schedules`

**Description:** Specific bus trip on a route at a particular date and time

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique schedule identifier |
| bus_id | str | Yes | Foreign key to buses table |
| route_id | str | Yes | Foreign key to routes table |
| departure_datetime | datetime | Yes | Scheduled departure date and time |
| arrival_datetime | datetime | Yes | Scheduled arrival date and time |
| base_price | float | Yes | Base ticket price |
| available_seats | int | Yes | Current available seat count |
| status | str | Yes | Schedule status (scheduled, in_transit, completed, cancelled) |
| created_at | datetime | Yes | Schedule creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

---

### Seat

**Table:** `seats`

**Description:** Individual seat within a bus schedule instance

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique seat identifier |
| schedule_id | str | Yes | Foreign key to schedules table |
| seat_number | str | Yes | Seat number within bus |
| seat_type | str | Yes | Seat type (standard, premium, sleeper) |
| price_modifier | float | Yes | Price adjustment for seat type |
| status | str | Yes | Seat status (available, reserved, booked) |
| created_at | datetime | Yes | Seat creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

---

### Reservation

**Table:** `reservations`

**Description:** Temporary seat hold with 15-minute expiration timer before payment

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique reservation identifier |
| user_id | str | Yes | Foreign key to users table |
| schedule_id | str | Yes | Foreign key to schedules table |
| reservation_datetime | datetime | Yes | Reservation creation timestamp |
| expiry_datetime | datetime | Yes | Reservation expiration timestamp (15 minutes) |
| status | str | Yes | Reservation status (active, expired, confirmed, cancelled) |
| total_amount | float | Yes | Total reservation amount |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

---

### Reservationseat

**Table:** `reservation_seats`

**Description:** Junction table linking reservations to seats (many-to-many)

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique junction record identifier |
| reservation_id | str | Yes | Foreign key to reservations table |
| seat_id | str | Yes | Foreign key to seats table |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

---

### Booking

**Table:** `bookings`

**Description:** Confirmed ticket after successful payment

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique booking identifier |
| reservation_id | str | Yes | Foreign key to reservations table |
| user_id | str | Yes | Foreign key to users table |
| schedule_id | str | Yes | Foreign key to schedules table |
| booking_datetime | datetime | Yes | Booking confirmation timestamp |
| total_amount | float | Yes | Total booking amount |
| payment_status | str | Yes | Payment status (pending, completed, failed, refunded) |
| booking_reference | str | Yes | Unique booking reference number |
| status | str | Yes | Booking status (confirmed, cancelled, completed) |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

---

### Bookingseat

**Table:** `booking_seats`

**Description:** Junction table linking bookings to seats with seat number snapshot

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique junction record identifier |
| booking_id | str | Yes | Foreign key to bookings table |
| seat_id | str | Yes | Foreign key to seats table |
| seat_number | str | Yes | Seat number snapshot at booking time |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

---

### Payment

**Table:** `payments`

**Description:** Payment transaction record linked to booking

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique payment identifier |
| booking_id | str | Yes | Foreign key to bookings table |
| user_id | str | Yes | Foreign key to users table |
| amount | float | Yes | Payment amount |
| payment_method | str | Yes | Payment method (credit_card, debit_card, upi, net_banking, wallet) |
| payment_gateway_reference | Optional[str] | No | Payment gateway transaction reference |
| transaction_datetime | datetime | Yes | Payment transaction timestamp |
| status | str | Yes | Payment status (pending, completed, failed, refunded) |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `status`

---

### Ticket

**Table:** `tickets`

**Description:** Generated ticket document for confirmed booking

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique ticket identifier |
| booking_id | str | Yes | Foreign key to bookings table |
| ticket_number | str | Yes | Unique ticket number |
| qr_code | str | Yes | QR code for ticket verification |
| issue_datetime | datetime | Yes | Ticket issue timestamp |
| validity_status | str | Yes | Ticket validity status (valid, used, cancelled) |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `validity_status`

---

### Cancellation

**Table:** `cancellations`

**Description:** Booking cancellation record with refund information

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique cancellation identifier |
| booking_id | str | Yes | Foreign key to bookings table |
| user_id | str | Yes | Foreign key to users table |
| cancellation_datetime | datetime | Yes | Cancellation timestamp |
| reason | Optional[str] | No | Cancellation reason |
| refund_amount | float | Yes | Calculated refund amount |
| refund_status | str | Yes | Refund processing status (pending, processed, failed) |
| processed_at | Optional[datetime] | No | Refund processing timestamp |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** `refund_status`

---

### Activitylog

**Table:** `activity_logs`

**Description:** Comprehensive audit trail of all system activities

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique log identifier |
| entity_type | str | Yes | Type of entity being logged |
| entity_id | str | Yes | ID of entity being logged |
| user_id | Optional[str] | No | Foreign key to users table (nullable) |
| action_type | str | Yes | Type of action performed |
| details | Optional[dict] | No | JSON details of the action |
| ip_address | Optional[str] | No | IP address of the user |
| timestamp | datetime | Yes | Action timestamp |
| created_at | datetime | Yes | Record creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

## 7. Enumerations

### UserRole

| Value | Label |
|-------|-------|
| guest | GUEST |
| customer | CUSTOMER |
| admin | ADMIN |

---

### BusType

| Value | Label |
|-------|-------|
| standard | STANDARD |
| deluxe | DELUXE |
| sleeper | SLEEPER |
| semi_sleeper | SEMI_SLEEPER |

---

### ScheduleStatus

| Value | Label |
|-------|-------|
| scheduled | SCHEDULED |
| in_transit | IN_TRANSIT |
| completed | COMPLETED |
| cancelled | CANCELLED |

---

### SeatType

| Value | Label |
|-------|-------|
| standard | STANDARD |
| premium | PREMIUM |
| sleeper | SLEEPER |

---

### SeatStatus

| Value | Label |
|-------|-------|
| available | AVAILABLE |
| reserved | RESERVED |
| booked | BOOKED |

---

### ReservationStatus

| Value | Label |
|-------|-------|
| active | ACTIVE |
| expired | EXPIRED |
| confirmed | CONFIRMED |
| cancelled | CANCELLED |

---

### BookingStatus

| Value | Label |
|-------|-------|
| confirmed | CONFIRMED |
| cancelled | CANCELLED |
| completed | COMPLETED |

---

### PaymentStatus

| Value | Label |
|-------|-------|
| pending | PENDING |
| completed | COMPLETED |
| failed | FAILED |
| refunded | REFUNDED |

---

### PaymentMethod

| Value | Label |
|-------|-------|
| credit_card | CREDIT_CARD |
| debit_card | DEBIT_CARD |
| upi | UPI |
| net_banking | NET_BANKING |
| wallet | WALLET |

---

### TicketValidityStatus

| Value | Label |
|-------|-------|
| valid | VALID |
| used | USED |
| cancelled | CANCELLED |

---

### RefundStatus

| Value | Label |
|-------|-------|
| pending | PENDING |
| processed | PROCESSED |
| failed | FAILED |

## 8. Relationships

| Relationship | Type | Navigation Required |
|--------------|------|---------------------|
| Schedule → Bus | Many-to-One | Yes |
| Schedule → Route | Many-to-One | Yes |
| Seat → Schedule | Many-to-One | Yes |
| Reservation → User | Many-to-One | Yes |
| Reservation → Schedule | Many-to-One | Yes |
| Reservationseat → Reservation | Many-to-One | Yes |
| Reservationseat → Seat | Many-to-One | Yes |
| Booking → Reservation | Many-to-One | Yes |
| Booking → User | Many-to-One | Yes |
| Booking → Schedule | Many-to-One | Yes |
| Bookingseat → Booking | Many-to-One | Yes |
| Bookingseat → Seat | Many-to-One | Yes |
| Payment → Booking | Many-to-One | Yes |
| Payment → User | Many-to-One | Yes |
| Ticket → Booking | One-to-One | Yes |
| Cancellation → Booking | One-to-One | Yes |
| Cancellation → User | Many-to-One | Yes |
| Activitylog → User | Many-to-One (nullable) | No |

### Relationship Details

**Schedule Relationships:**
- Each Schedule belongs to one Bus
- Each Schedule belongs to one Route
- Each Schedule has many Seats

**Reservation Relationships:**
- Each Reservation belongs to one User
- Each Reservation belongs to one Schedule
- Each Reservation has many Reservationseats
- Each Reservationseat links one Reservation to one Seat

**Booking Relationships:**
- Each Booking belongs to one Reservation
- Each Booking belongs to one User
- Each Booking belongs to one Schedule
- Each Booking has many Bookingseats
- Each Bookingseat links one Booking to one Seat
- Each Booking has one Ticket
- Each Booking has many Payments
- Each Booking may have one Cancellation

**Payment Relationships:**
- Each Payment belongs to one Booking
- Each Payment belongs to one User

**Cancellation Relationships:**
- Each Cancellation belongs to one Booking
- Each Cancellation belongs to one User

**Activity Log Relationships:**
- Each Activitylog may belong to one User (nullable for system actions)

## 9. API Endpoints

### User Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/users` | Create new user | No | - |
| GET | `/users` | List all users | Yes | admin |
| GET | `/users/{id}` | Get user by ID | Yes | customer, admin |
| PUT | `/users/{id}` | Update user | Yes | customer, admin |
| DELETE | `/users/{id}` | Delete user | Yes | admin |
| POST | `/users/register` | User registration | No | - |
| POST | `/users/login` | User login | No | - |
| GET | `/users/{id}/profile` | Get user profile | Yes | customer, admin |
| PUT | `/users/{id}/profile` | Update user profile | Yes | customer, admin |

---

### Route Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/routes` | Create new route | Yes | admin |
| GET | `/routes` | List all routes | No | - |
| GET | `/routes/{id}` | Get route by ID | No | - |
| PUT | `/routes/{id}` | Update route | Yes | admin |
| DELETE | `/routes/{id}` | Delete route | Yes | admin |

---

### Bus Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/buses` | Create new bus | Yes | admin |
| GET | `/buses` | List all buses | Yes | admin |
| GET | `/buses/{id}` | Get bus by ID | Yes | admin |
| PUT | `/buses/{id}` | Update bus | Yes | admin |
| DELETE | `/buses/{id}` | Delete bus | Yes | admin |

---

### Schedule Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/schedules` | Create new schedule | Yes | admin |
| GET | `/schedules` | List all schedules | No | - |
| GET | `/schedules/{id}` | Get schedule by ID | No | - |
| PUT | `/schedules/{id}` | Update schedule | Yes | admin |
| DELETE | `/schedules/{id}` | Delete schedule | Yes | admin |
| POST | `/schedules/{id}/generate-seats` | Generate seat inventory for schedule | Yes | admin |
| GET | `/schedules/search` | Search schedules by origin, destination, date | No | - |
| GET | `/schedules/{id}/seats` | Get all seats for a schedule | No | - |
| GET | `/schedules/{id}/details` | Get schedule with bus, route, and seat details | No | - |

---

### Seat Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/seats` | Create new seat | Yes | admin |
| GET | `/seats` | List all seats | Yes | admin |
| GET | `/seats/{id}` | Get seat by ID | No | - |
| PUT | `/seats/{id}` | Update seat | Yes | admin |
| DELETE | `/seats/{id}` | Delete seat | Yes | admin |

---

### Reservation Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/reservations/create` | Create new reservation with seat locks | Yes | customer |
| GET | `/reservations/{id}` | Get reservation by ID | Yes | customer, admin |
| DELETE | `/reservations/{id}` | Cancel reservation and release seats | Yes | customer, admin |
| GET | `/reservations/active` | Get all active reservations for current user | Yes | customer |
| GET | `/reservations/{id}/details` | Get reservation with user, schedule, and seat details | Yes | customer, admin |

---

### Booking Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/bookings/create` | Create booking from reservation after payment | Yes | customer |
| GET | `/bookings/{id}` | Get booking by ID | Yes | customer, admin |
| GET | `/bookings/history` | Get booking history for current user | Yes | customer |
| POST | `/bookings/{id}/cancel` | Cancel booking and initiate refund | Yes | customer, admin |
| GET | `/bookings/{id}/details` | Get booking with user, schedule, seats, ticket, and payment details | Yes | customer, admin |

---

### Payment Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| POST | `/payments/initiate` | Initiate payment for reservation | Yes | customer |
| POST | `/payments/webhook` | Payment gateway webhook callback | No | - |
| GET | `/payments/{id}` | Get payment by ID | Yes | customer, admin |

---

### Ticket Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| GET | `/tickets/{id}` | Get ticket by ID | Yes | customer, admin |
| GET | `/tickets/{id}/download` | Download ticket as PDF | Yes | customer, admin |

---

### Cancellation Management

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| GET | `/cancellations/{id}` | Get cancellation by ID | Yes | customer, admin |
| GET | `/cancellations/{id}/details` | Get cancellation with booking and user details | Yes | customer, admin |

---

### Activity Logging

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| GET | `/activity-logs` | List activity logs with filtering | Yes | admin |

---

### Admin Dashboard

| Method | Endpoint | Description | Auth Required | Role |
|--------|----------|-------------|---------------|------|
| GET | `/admin/dashboard/metrics` | Get dashboard metrics (bookings, revenue, etc.) | Yes | admin |
| GET | `/admin/reservations/active` | Get all active reservations across system | Yes | admin |
| POST | `/admin/reservations/{id}/release` | Manually release stuck reservation | Yes | admin |

---

### WebSocket Endpoints

| Endpoint | Description | Auth Required |
|----------|-------------|---------------|
| WS `/ws/schedules/{schedule_id}/seats` | Real-time seat availability updates | No |

## 10. Workflow Logic

### User Registration and Authentication

**User Registration:**
1. Validate email format and uniqueness
2. Validate password strength (min 8 chars, uppercase, lowercase, number)
3. Hash password using bcrypt
4. Create user record with role 'customer'
5. Set is_active to true
6. Log registration activity
7. Return user profile (without password_hash)

**User Login:**
1. Validate email and password
2. Check if user is_active
3. Generate JWT token with 30-minute expiry
4. Log login activity
5. Return JWT token and user profile

---

### Schedule Search and Seat Selection

**Schedule Search:**
1. Accept origin_city, destination_city, and departure_date as query parameters
2. Query schedules with matching route and date
3. Filter by status = 'scheduled'
4. Include bus and route details
5. Return list of available schedules with seat counts

**Seat Selection:**
1. Establish WebSocket connection for real-time updates
2. Fetch schedule with all seats
3. Display seat layout based on bus.seat_layout_config
4. Show seat status (available, reserved, booked)
5. Allow selection of up to 6 available seats
6. Broadcast seat status changes to all connected clients

---

### Reservation Creation with Seat Locking

**Create Reservation:**
1. Validate user has no active reservation for this schedule
2. Validate selected seats (max 6, all must be available)
3. Begin database transaction with SELECT FOR UPDATE on seats
4. Check seat status = 'available' (concurrent booking protection)
5. Calculate total_amount (base_price + seat price_modifiers)
6. Create reservation record with:
   - reservation_datetime = current timestamp
   - expiry_datetime = current timestamp + 15 minutes
   - status = 'active'
7. Create reservation_seats junction records
8. Update seat status to 'reserved'
9. Update schedule.available_seats count
10. Commit transaction
11. Log reservation creation activity
12. Broadcast seat status update via WebSocket
13. Return reservation details with expiry countdown

---

### Reservation Timeout and Automatic Release

**Background Job (runs every 30 seconds):**
1. Query all reservations where:
   - status = 'active'
   - expiry_datetime <= current timestamp
2. For each expired reservation:
   - Begin database transaction
   - Update reservation status to 'expired'
   - Fetch all reservation_seats for this reservation
   - Update all linked seats status to 'available'
   - Update schedule.available_seats count
   - Log expiration activity
   - Commit transaction
   - Broadcast seat status update via WebSocket

---

### Payment Processing and Booking Confirmation

**Initiate Payment:**
1. Validate reservation exists and status = 'active'
2. Validate reservation not expired
3. Validate payment amount matches reservation.total_amount
4. Create payment record with status = 'pending'
5. Call payment gateway API with:
   - amount
   - payment_method
   - callback URL for webhook
6. Return payment gateway redirect URL

**Payment Webhook:**
1. Receive payment gateway callback
2. Validate payment_gateway_reference
3. Update payment status based on gateway response
4. If payment successful:
   - Begin database transaction
   - Create booking record with:
     - booking_reference (unique alphanumeric 8-12 chars)
     - status = 'confirmed'
     - payment_status = 'completed'
   - Update reservation status to 'confirmed'
   - Fetch all reservation_seats
   - Create booking_seats records with seat_number snapshot
   - Update all linked seats status to 'booked'
   - Generate ticket with:
     - ticket_number (unique)
     - qr_code (encoded booking details)
     - validity_status = 'valid'
   - Log booking confirmation activity
   - Commit transaction
   - Send email notification with ticket
   - Broadcast seat status update via WebSocket
5. If payment failed:
   - Update payment status to 'failed'
   - Keep reservation active (user can retry within expiry window)

---

### Booking Cancellation and Refund

**Cancel Booking:**
1. Validate booking exists and status = 'confirmed'
2. Calculate time until departure
3. Validate cancellation allowed (>2 hours before departure)
4. Calculate refund amount based on policy:
   - 100% if >24 hours before departure
   - 50% if 2-24 hours before departure
   - 0% if <2 hours before departure
5. Begin database transaction
6. Create cancellation record with:
   - cancellation_datetime = current timestamp
   - refund_amount (calculated)
   - refund_status = 'pending'
7. Update booking status to 'cancelled'
8. Update booking payment_status to 'refunded'
9. Update ticket validity_status to 'cancelled'
10. Fetch all booking_seats
11. Update all linked seats status to 'available'
12. Update schedule.available_seats count
13. Log cancellation activity
14. Commit transaction
15. Initiate refund with payment gateway
16. Update cancellation refund_status to 'processed'
17. Broadcast seat status update via WebSocket
18. Send cancellation confirmation email

---

### Seat Inventory Generation

**Generate Seats for Schedule:**
1. Validate schedule exists
2. Fetch bus with seat_layout_config
3. Parse seat_layout_config JSON
4. For each seat in layout:
   - Create seat record with:
     - schedule_id
     - seat_number (from layout)
     - seat_type (from layout)
     - price_modifier (from layout)
     - status = 'available'
5. Update schedule.available_seats = bus.total_seats
6. Log seat generation activity
7. Return created seats count

---

### Admin Reservation Monitoring

**View Active Reservations:**
1. Query all reservations with status = 'active'
2. Include user, schedule, bus, route details
3. Calculate remaining time until expiry
4. Return list with expiry warnings

**Manual Reservation Release:**
1. Validate reservation exists
2. Begin database transaction
3. Update reservation status to 'cancelled'
4. Fetch all reservation_seats
5. Update all linked seats status to 'available'
6. Update schedule.available_seats count
7. Log manual release activity with admin user_id
8. Commit transaction
9. Broadcast seat status update via WebSocket

## 11. Business Logic

### Reservation Rules

1. **Reservation Lock Duration:** Exactly 15 minutes from creation time
2. **Maximum Seats Per Transaction:** 6 seats
3. **One Active Reservation Per Schedule:** User can have only one active reservation per schedule at a time
4. **Automatic Seat Release:** Expired reservations automatically release seats without user intervention
5. **Reservation Expiry Check:** Background job runs every 30 seconds to check and expire reservations

### Booking Rules

1. **Booking Creation:** Can only be created from a valid, non-expired reservation
2. **Payment Requirement:** Booking requires successful payment confirmation
3. **Payment Amount Validation:** Payment amount must match reservation total_amount
4. **Payment Window:** Payment must be completed within reservation window (15 minutes)
5. **Booking Reference:** Unique alphanumeric 8-12 characters

### Cancellation Rules

1. **Cancellation Window:** Bookings can be cancelled up to 2 hours before departure time
2. **Refund Policy:**
   - 100% refund if >24 hours before departure
   - 50% refund if 2-24 hours before departure
   - 0% refund if <2 hours before departure
3. **Immediate Seat Release:** Cancelled seats become immediately available for rebooking
4. **Refund Amount Cap:** Refund amount cannot exceed booking total_amount

### Seat Management Rules

1. **Seat Status Transitions:** available → reserved → booked OR available → reserved → available (on expiry)
2. **Reserved Seat Protection:** Reserved seats cannot be selected by other users
3. **Concurrent Booking Prevention:** Uses database-level locking (SELECT FOR UPDATE) to prevent double booking
4. **Seat Number Uniqueness:** Seat numbers must be unique within a schedule

### Schedule Rules

1. **Future Departure:** Schedule departure time must be in the future
2. **Arrival After Departure:** Arrival time must be after departure time
3. **Available Seats Tracking:** Available_seats in schedule cannot be negative
4. **Seat Count Consistency:** Total_seats in bus must match seat_layout_config count

### Admin Rules

1. **Manual Release Authority:** Admin can manually release reservations in exceptional cases
2. **Admin Action Logging:** All admin actions are logged with admin user_id
3. **Full System Access:** Admin has read/write access to all resources

## 12. Validation Rules

### User Validation

- **Email:** Must be unique and valid format (RFC 5322)
- **Password:** Minimum 8 characters with at least one uppercase, one lowercase, and one number
- **Phone Number:** Valid format (10-15 digits)
- **Role:** Must be one of: guest, customer, admin

### Schedule Validation

- **Departure DateTime:** Must be in the future
- **Arrival DateTime:** Must be after departure_datetime
- **Base Price:** Must be positive number
- **Available Seats:** Cannot be negative, cannot exceed bus.total_seats

### Seat Validation

- **Seat Number:** Must be unique within a schedule
- **Price Modifier:** Must be non-negative number
- **Status:** Must be one of: available, reserved, booked

### Reservation Validation

- **Expiry DateTime:** Must be exactly 15 minutes after reservation_datetime
- **Total Amount:** Must be positive number
- **Seat Count:** Maximum 6 seats per reservation
- **User Limit:** User can have only one active reservation per schedule

### Booking Validation

- **Booking Reference:** Must be alphanumeric and 8-12 characters
- **Total Amount:** Must match reservation total_amount
- **Payment Status:** Must be one of: pending, completed, failed, refunded
- **Status:** Must be one of: confirmed, cancelled, completed

### Payment Validation

- **Amount:** Must be positive number
- **Payment Method:** Must be one of: credit_card, debit_card, upi, net_banking, wallet
- **Transaction DateTime:** Must be valid timestamp

### Cancellation Validation

- **Cancellation Window:** Must be >2 hours before departure
- **Refund Amount:** Cannot exceed booking total_amount
- **Refund Status:** Must be one of: pending, processed, failed

### General Validation

- **Dates:** Must be valid and in ISO 8601 format
- **UUIDs:** All ID fields must be valid UUID format
- **JSON Fields:** seat_layout_config, amenities, details must be valid JSON

## 13. Implementation Notes

### Technology Stack

- **Framework:** FastAPI for high-performance async API development
- **ORM:** SQLAlchemy ORM for database abstraction and relationship management
- **Database:** SQLite for development, PostgreSQL recommended for production
- **Validation:** Pydantic v2 for request/response validation and serialization
- **Architecture:** Service layer pattern for business logic separation
- **Dependency Injection:** FastAPI Depends(get_db) for database session management
- **Package Manager:** uv for fast, reliable Python package management
- **Documentation:** Automatic OpenAPI (Swagger) documentation via FastAPI

### Project Structure

```
app/
├── main.py                 # FastAPI application entry point
├── config.py               # Configuration settings
├── database.py             # Database connection and session management
├── models/                 # SQLAlchemy ORM models
│   ├── user.py
│   ├── route.py
│   ├── bus.py
│   ├── schedule.py
│   ├── seat.py
│   ├── reservation.py
│   ├── booking.py
│   ├── payment.py
│   ├── ticket.py
│   ├── cancellation.py
│   └── activity_log.py
├── schemas/                # Pydantic schemas for validation
│   ├── user.py
│   ├── route.py
│   ├── bus.py
│   ├── schedule.py
│   ├── seat.py
│   ├── reservation.py
│   ├── booking.py
│   ├── payment.py
│   ├── ticket.py
│   └── cancellation.py
├── services/               # Business logic layer
│   ├── user_service.py
│   ├── schedule_service.py
│   ├── reservation_service.py
│   ├── booking_service.py
│   ├── payment_service.py
│   ├── cancellation_service.py
│   └── activity_log_service.py
├── routers/                # API route handlers
│   ├── users.py
│   ├── routes.py
│   ├── buses.py
│   ├── schedules.py
│   ├── seats.py
│   ├── reservations.py
│   ├── bookings.py
│   ├── payments.py
│   ├── tickets.py
│   ├── cancellations.py
│   ├── activity_logs.py
│   └── admin.py
├── background/             # Background jobs
│   └── reservation_expiry.py
├── websocket/              # WebSocket handlers
│   └── seat_updates.py
└── utils/                  # Utility functions
    ├── auth.py             # JWT token generation/validation
    ├── security.py         # Password hashing
    └── qr_code.py          # QR code generation
```

### Key Implementation Details

**Database Session Management:**
- Use FastAPI dependency injection with `Depends(get_db)`
- Ensure proper session cleanup with try/finally blocks
- Use context managers for transaction management

**Concurrent Booking Protection:**
- Implement SELECT FOR UPDATE in reservation creation
- Use database-level row locking to prevent race conditions
- Handle deadlock exceptions with retry logic

**Background Jobs:**
- Implement reservation expiry job using APScheduler or similar
- Run every 30 seconds to check and expire reservations
- Use separate database session for background tasks

**WebSocket Implementation:**
- Maintain connection pool for active WebSocket clients
- Broadcast seat updates to all clients viewing specific schedule
- Handle connection lifecycle (connect, disconnect, error)

**Activity Logging:**
- Log all critical operations (reservation, payment, booking, cancellation, expiry)
- Include user_id, entity_type, entity_id, action_type, and details
- Capture IP address for security audit

**Error Handling:**
- Use FastAPI HTTPException for API errors
- Return appropriate HTTP status codes (400, 401, 403, 404, 409, 500)
- Include detailed error messages in response

**API Documentation:**
- Leverage FastAPI automatic OpenAPI documentation
- Add detailed descriptions to all endpoints
- Include request/response examples in docstrings

## 14. Assumptions

### Infrastructure

1. **Database:** System uses PostgreSQL database with UUID primary keys
2. **Authentication:** JWT tokens are used for authentication with 30-minute session timeout
3. **WebSocket:** WebSocket connections are established on seat selection page for real-time updates
4. **Background Jobs:** Background job for reservation expiry runs every 30 seconds
5. **Caching:** Redis is used for caching frequently accessed data (routes, schedules)
6. **Transaction Isolation:** Database uses READ COMMITTED isolation level minimum for transactions
7. **Locking Strategy:** Pessimistic locking (SELECT FOR UPDATE) is used for concurrent booking prevention

### External Integrations

1. **Payment Gateway:** Payment gateway integration uses webhook callbacks for payment confirmation
2. **Email Service:** Email notifications are sent via third-party service (SendGrid/AWS SES)
3. **File Storage:** File storage for tickets uses cloud storage (AWS S3 or similar)
4. **QR Code Generation:** QR codes are generated using standard QR code library and stored as text

### Data Management

1. **Activity Logs:** Activity logs are retained for audit purposes with no automatic deletion
2. **Soft Delete:** Soft delete is used for critical entities to maintain referential integrity
3. **Data Retention:** No automatic data purging; manual archival process for old records

### Security

1. **Password Hashing:** Passwords are hashed using bcrypt with appropriate salt rounds
2. **Token Security:** JWT tokens include user_id and role claims
3. **HTTPS:** All API communication occurs over HTTPS in production
4. **CORS:** CORS is configured to allow specific frontend origins

### Business Operations

1. **Time Zone:** All timestamps are stored in UTC
2. **Currency:** All monetary values are in single currency (no multi-currency support)
3. **Refund Processing:** Refunds are initiated automatically but may require manual gateway confirmation
4. **Seat Layout:** Seat layout configuration is defined per bus and remains static

## 15. Future Improvements

### Phase 1: Enhanced Features

1. **Alembic Migrations:** Implement Alembic for database schema version control and migrations
2. **PostgreSQL Migration:** Full migration from SQLite to PostgreSQL for production
3. **JWT Authentication:** Complete JWT-based authentication with refresh tokens
4. **Role-Based Access Control (RBAC):** Fine-grained permission system beyond basic roles
5. **Comprehensive Testing:** Unit tests, integration tests, and end-to-end tests with pytest
6. **Docker Containerization:** Dockerize application with docker-compose for easy deployment

### Phase 2: Notifications and Communication

1. **SMS Notifications:** Implement SMS notifications for booking confirmations and reminders
2. **Push Notifications:** Add support for mobile app push notifications
3. **Email Templates:** Rich HTML email templates for all notifications
4. **Notification Preferences:** User-configurable notification preferences

### Phase 3: Advanced Booking Features

1. **Recurring Schedules:** Support for recurring schedules with bulk creation
2. **Dynamic Pricing:** Implement dynamic pricing based on demand and seat occupancy
3. **Partial Cancellations:** Support for canceling some seats, not entire booking
4. **Seat Preferences:** Seat preference selection (window, aisle, front, back)
5. **Group Bookings:** Support for group bookings with discount codes
6. **Waiting List:** Implement waiting list for fully booked schedules

### Phase 4: Route and Schedule Enhancements

1. **Multi-City Routes:** Support for multi-city routes with intermediate stops
2. **Route Optimization:** Route planning and optimization tools
3. **Schedule Templates:** Reusable schedule templates for recurring trips

### Phase 5: Customer Engagement

1. **Loyalty Program:** Implement loyalty program with points and rewards
2. **Referral System:** Customer referral program with incentives
3. **Chatbot Support:** AI-powered chatbot for customer support
4. **Review System:** Customer reviews and ratings for routes and buses

### Phase 6: Enterprise Features

1. **Corporate Accounts:** Support for corporate accounts with bulk booking capabilities
2. **API for Partners:** Public API for third-party integrations
3. **White-Label Solution:** White-label platform for bus operators
4. **Multi-Tenant Architecture:** Support for multiple bus operators on single platform

### Phase 7: Analytics and Reporting

1. **Advanced Analytics:** Advanced analytics dashboard for admin with revenue reports
2. **Business Intelligence:** BI integration for data analysis
3. **Predictive Analytics:** ML-based demand forecasting and pricing optimization
4. **Custom Reports:** Customizable report generation for operators

### Phase 8: Payment and Financial

1. **Multiple Payment Gateways:** Support for multiple payment gateways with fallback
2. **Automated Refunds:** Fully automated refund processing without manual intervention
3. **Split Payments:** Support for split payments across multiple payment methods
4. **Invoicing:** Automated invoice generation and GST compliance

### Phase 9: Operations and Maintenance

1. **Health Monitoring:** Application health monitoring and alerting
2. **Performance Optimization:** Query optimization and caching strategies
3. **Load Balancing:** Horizontal scaling with load balancing
4. **Disaster Recovery:** Backup and disaster recovery procedures
5. **Audit Trail Enhancement:** Enhanced audit trail with change history tracking

### Phase 10: Mobile and Accessibility

1. **Mobile App:** Native mobile applications for iOS and Android
2. **Progressive Web App:** PWA for offline booking capabilities
3. **Accessibility Compliance:** WCAG 2.1 AA compliance for accessibility
4. **Multi-Language Support:** Internationalization and localization