# Database Schema & Data Dictionary

The Calian Hotel database is designed for normalized relational integrity, high concurrency, and complete financial and operational auditing.

---

## 1. Core Tables Entity-Relationship Summary

```text
hotel_settings
users ───────────┐
roles ───────────┼─► role_user
permissions ─────┴─► permission_role

room_types ──────┬─► physical_rooms ──────► room_status_history
                 ├─► rate_plans ──────────► rate_plan_rules
                 ├─► room_type_amenities
                 └─► room_type_images

guests ──────────┬─► bookings ────────────┬─► booking_rooms ───► physical_rooms
                 │                        ├─► booking_add_ons
                 │                        ├─► guest_folios ────► folio_items
                 │                        └─► payments ────────► payment_logs
                 └─► corporate_accounts

dining_outlets ──┬─► menu_categories ─────► menu_items
                 └─► table_reservations

spa_categories ──► spa_treatments ────────► spa_appointments ──► therapists

event_venues ────► event_proposals ───────► event_quotation_items

housekeeping_tasks
maintenance_tickets
cashier_shifts ──► cashier_shift_transactions
audit_logs
leads / marketing_campaigns / newsletter_subscribers
cms_pages / journal_posts / testimonials
```

---

## 2. Table Definitions

### 2.1 `hotel_settings`
- `id` (bigint, pk)
- `key` (string, unique, index)
- `value` (longText)
- `group` (string, default 'general') - `branding`, `booking`, `pesapal`, `taxes`, `notifications`

### 2.2 `room_types`
- `id` (bigint, pk)
- `name` (string) - e.g. "Deluxe Executive", "Junior Suite"
- `slug` (string, unique)
- `short_description` (text)
- `description` (longText)
- `base_price_usd` (decimal 10,2)
- `base_price_ugx` (decimal 14,2)
- `size_sqm` (integer)
- `max_adults` (integer)
- `max_children` (integer)
- `max_occupancy` (integer)
- `bed_type` (string) - "King Bed", "Queen Bed", "Twin Beds"
- `view_type` (string) - "Kampala Skyline View", "Garden View", "City View"
- `featured_image` (string, nullable)
- `gallery` (json, nullable)
- `amenities` (json, nullable)
- `is_active` (boolean, default true)
- `sort_order` (integer, default 0)

### 2.3 `physical_rooms`
- `id` (bigint, pk)
- `room_type_id` (foreignId -> room_types.id)
- `room_number` (string, unique) - e.g. "101", "204", "PH1"
- `floor` (integer)
- `operational_status` (string) - `Available`, `Reserved`, `Occupied`, `Out of Order`, `Maintenance`
- `housekeeping_status` (string) - `Dirty`, `Assigned`, `Cleaning`, `Clean`, `Inspected`, `Ready`
- `notes` (text, nullable)
- `is_accessible` (boolean, default false)

### 2.4 `bookings`
- `id` (bigint, pk)
- `booking_reference` (string, unique, index) - e.g. "CAL-20260826-9481"
- `guest_id` (foreignId -> guests.id)
- `room_type_id` (foreignId -> room_types.id)
- `physical_room_id` (foreignId -> physical_rooms.id, nullable)
- `check_in_date` (date)
- `check_out_date` (date)
- `adults` (integer, default 1)
- `children` (integer, default 0)
- `rate_plan_id` (foreignId -> rate_plans.id, nullable)
- `promo_code_id` (foreignId -> promo_codes.id, nullable)
- `corporate_account_id` (foreignId -> corporate_accounts.id, nullable)
- `status` (string) - `Inquiry`, `Tentative`, `Confirmed`, `Guaranteed`, `Checked In`, `Checked Out`, `Cancelled`, `No-show`
- `payment_status` (string) - `Pending`, `Partial`, `Paid`, `Refunded`, `Complimentary`
- `booking_source` (string) - `Website`, `Walk-in`, `Phone`, `WhatsApp`, `Corporate`, `OTA`
- `currency` (string, default 'USD')
- `exchange_rate` (decimal 10,4, default 1.0)
- `room_total` (decimal 12,2)
- `tax_total` (decimal 12,2)
- `service_charge_total` (decimal 12,2)
- `discount_total` (decimal 12,2, default 0)
- `grand_total` (decimal 12,2)
- `paid_total` (decimal 12,2, default 0)
- `balance_due` (decimal 12,2)
- `special_requests` (text, nullable)
- `flight_details` (json, nullable)
- `checked_in_at` (datetime, nullable)
- `checked_out_at` (datetime, nullable)
- `checked_in_by` (foreignId -> users.id, nullable)
- `checked_out_by` (foreignId -> users.id, nullable)

### 2.5 `guest_folios` & `folio_items`
- `guest_folios`: `id`, `booking_id`, `guest_id`, `status` (`Open`, `Settled`, `Closed`), `total_charges`, `total_payments`, `balance`
- `folio_items`: `id`, `guest_folio_id`, `category` (`Room`, `Restaurant`, `Bar`, `Spa`, `Laundry`, `Minibar`, `Transfer`, `Adjustment`, `Damage`), `description`, `amount`, `currency`, `quantity`, `posted_by`, `created_at`

### 2.6 `payments` & `pesapal_transactions`
- `payments`: `id`, `booking_id`, `guest_folio_id`, `amount`, `currency`, `payment_method` (`Pesapal`, `Cash`, `Mobile Money`, `Bank Transfer`, `Card Physical`, `Corporate Credit`), `reference`, `status` (`Pending`, `Completed`, `Failed`, `Refunded`), `cashier_shift_id`, `recorded_by`, `created_at`
- `pesapal_transactions`: `id`, `payment_id`, `booking_id`, `order_tracking_id`, `merchant_reference`, `pesapal_status`, `amount`, `currency`, `raw_callback_payload`, `is_verified`

### 2.7 Additional Tables
- `cashier_shifts`: Tracks shift start float, payment entries, closing count, supervisor verification.
- `housekeeping_tasks`: Work order items, assigned attendants, priority, notes.
- `maintenance_tickets`: Maintenance logs, priority, technician, out-of-order flags.
- `dining_outlets`, `menu_categories`, `menu_items`, `table_reservations`.
- `spa_categories`, `spa_treatments`, `spa_appointments`, `therapists`.
- `event_venues`, `event_proposals`, `event_quotation_items`.
- `transfers`: Airport transfer pickups, flight info, driver, status.
- `audit_logs`: Detailed tracking of sensitive edits (rates, cancellations, folios, settings).