# Database Schema - Kevron Suites & Apartments

## 1. Table Definitions & Schemas

### `users`
- **Purpose**: System credentials for administrators and guests.
- **Key Fields**:
  - `id` (bigint primary)
  - `email` (string unique index)
  - `password` (string hash)
- **Relationships**:
  - `hasOne` `Guest`

### `locations`
- **Purpose**: GPS positioning and contact address definitions.
- **Key Fields**:
  - `id` (bigint primary)
  - `slug` (string unique index)
  - `address` (text)

### `apartment_types`
- **Purpose**: Suite configuration definitions (1-Bed to 4-Bed options).
- **Key Fields**:
  - `id` (bigint primary)
  - `slug` (string unique index)
  - `base_price_nightly` (decimal 12,2)
- **Relationships**:
  - `hasMany` `Unit`
  - `belongsToMany` `Amenity`

### `units`
- **Purpose**: Physical room tracking (vacant, occupied).
- **Key Fields**:
  - `id` (bigint primary)
  - `apartment_type_id` (foreign index constrained)
  - `status` (string index, defaults to `'available'`)

### `bookings`
- **Purpose**: Stay duration records, reference logs, and prices.
- **Key Fields**:
  - `id` (bigint primary)
  - `booking_reference` (string unique index)
  - `guest_id` (foreign index constrained)
  - `status` (string index, defaults to `'pending'`)
  - `payment_status` (string index, defaults to `'unpaid'`)
  - `check_in_date` (date index)
  - `check_out_date` (date index)

---

## 2. Key Foreign Mappings & Relationships

```mermaid
erDiagram
    users ||--o| guests : "owns"
    guests ||--o{ bookings : "creates"
    apartment_types ||--o{ units : "categorizes"
    apartment_types ||--o{ bookings : "reserves"
    bookings ||--o{ payments : "triggers"
    bookings }o--o{ add_on_services : "includes"
```

---

## 3. Custom Constraints & Index Design
* **Double Booking Checks**: Combined indexes on `bookings(unit_id, check_in_date, check_out_date)` to accelerate conflict checks.
* **Database Cascades**: Standard `onDelete('cascade')` maps pivot tables and bookings records cleanly, while `onDelete('set null')` guards audit logs and references.