# Database Spec (Final)

## Conventions

- Money fields use integer cents.
- Currency defaults to `PHP` unless overridden.
- Booking `start_at`/`end_at` persisted in UTC.
- Listing timezone defaults to `Asia/Manila`.

## Tables

### `users`

- `id`
- `name`
- `email` unique
- `email_verified_at` nullable
- `password`
- `phone` nullable
- `role` default `member` (`admin|host|member`)
- `status` default `active` (`active|suspended`)
- `remember_token`
- timestamps

### `host_profiles`

- `id`
- `user_id` unique FK -> `users` cascade
- `display_name` nullable
- `bio` nullable
- `verification_status` default `unverified`
- `verified_at` nullable
- `payout_method` nullable
- `payout_details` json nullable
- timestamps

### `listings`

- `id`
- `host_user_id` FK -> `users` restrict
- `title`
- `slug` unique
- `description` nullable
- address fields (`address_line1`, `address_line2`, `barangay`, `city`, `province`, `postal_code`, `country`)
- coordinates (`latitude`, `longitude`)
- `timezone` default `Asia/Manila`
- pricing (`hourly_rate_cents`, `daily_rate_cents`, `currency`)
- `vehicle_types` json nullable
- `height_limit_cm` nullable
- `notes` nullable
- `status` default `draft`
- `published_at`, `suspended_at` nullable
- timestamps + soft deletes
- indexes: `host_user_id`, `status`, `(city,status)`, `(latitude,longitude)`

### `listing_photos`

- `id`
- `listing_id` FK cascade
- `disk` default `public`
- `path`
- `sort_order` default `0`
- `is_cover` bool default `false`
- timestamps
- index: `listing_id`

### `amenities`

- `id`
- `name`
- `slug` unique
- `icon` nullable
- timestamps

### `amenity_listing`

- `listing_id` FK cascade
- `amenity_id` FK cascade
- PK (`listing_id`, `amenity_id`)
- index: `amenity_id`

### `availability_rules`

- `id`
- `listing_id` FK cascade
- `day_of_week` tinyint (1-7)
- `start_time`, `end_time`
- `is_active` bool default `true`
- `price_override_cents` nullable
- timestamps
- index: `(listing_id, day_of_week)`

### `availability_exceptions`

- `id`
- `listing_id` FK cascade
- `date`
- `type` default `blocked` (`blocked|available_override`)
- `start_time`, `end_time` nullable
- `reason` nullable
- timestamps
- index: `(listing_id, date)`

### `bookings`

- `id`
- `listing_id` FK restrict
- `user_id` FK restrict
- `timezone` snapshot
- `start_at` UTC datetime
- `end_at` UTC datetime
- `booked_at` timestamp
- `status` default `pending` (`pending|confirmed|cancelled|completed|expired`)
- total fields: `subtotal_cents`, `fees_cents`, `discount_cents`, `tax_cents`, `total_cents`
- `currency` default `PHP`
- `cancelled_at` nullable
- `cancellation_reason` nullable
- `confirmation_code` unique
- timestamps
- indexes: `(listing_id,start_at)`, `(listing_id,end_at)`, `user_id`, `status`

### `booking_payments`

- `id`
- `booking_id` FK cascade
- `provider`
- `provider_intent_id` nullable
- `provider_charge_id` nullable
- `amount_cents`
- `currency` default `PHP`
- `status` default `requires_payment_method`
- `captured_at`, `refunded_at` nullable
- `meta` json nullable
- timestamps
- index: `booking_id`

### `favorites`

- `user_id` FK cascade
- `listing_id` FK cascade
- `created_at`
- unique (`user_id`, `listing_id`)
- index: `listing_id`

### `reviews`

- `id`
- `booking_id` unique FK cascade
- `listing_id` FK cascade
- `reviewer_user_id` FK -> `users` cascade
- `rating` tinyint (1..5 at app validation level)
- `comment` nullable
- `published_at` nullable
- timestamps
- indexes: `listing_id`, `reviewer_user_id`

### `vehicles` (optional)

- `id`
- `user_id` FK cascade
- `nickname` nullable
- `plate_no`
- `type`
- `height_cm` nullable
- timestamps
- index: `user_id`
- unique (`user_id`, `plate_no`)

## Booking Overlap Guard

Booking creation blocks overlaps where:

- `existing.start_at < requested.end_at`
- `existing.end_at > requested.start_at`
- `existing.status IN ('pending', 'confirmed')`

Implemented using DB transaction + `lockForUpdate()` in `CreateBookingAction`.

## Legacy Deviations

- Legacy tables (`parking_spaces`, `cities`, `users_groups`, etc.) were not migrated directly because no complete canonical SQL schema existed in this repo.
- Role/group logic moved from legacy `users_groups` to `users.role` for maintainability.