Schéma de base de données

packages/db/src/schema/
├── enums.ts                  (tous les pgEnum centralisés)
├── auth.ts                   (Better Auth : user, session, account, verification)
├── organizations.ts          (organization, member, invitation)
├── guests.ts                 (guest, guest_group)
├── rooms.ts                  (room)
├── bookings.ts               (restaurant_booking, room_service_order, laundry_order, spa_booking)
├── conversations.ts          (conversation, message)
├── tickets.ts                (ticket, ticket_comment)
├── staff.ts                  (staff_note, note_comment)
├── menus.ts                  (menu_category, menu_item)
├── integrations.ts           (integration, integration_sync_log)
├── analytics.ts              (activity_log, interaction_log, hotel_event)
└── index.ts                  (re-exports explicites)

import { pgEnum } from "drizzle-orm/pg-core";

// ─── Auth / Org ──────────────────────────────────────────────
export const memberRoleEnum = pgEnum("member_role", [
  "owner", "admin", "manager", "staff", "guest",
]);

// ─── Check-in ────────────────────────────────────────────────
export const checkInStatusEnum = pgEnum("check_in_status", [
  "pending", "invited", "completed", "arrived", "checked_out",
]);

// ─── Room ────────────────────────────────────────────────────
export const roomStatusEnum = pgEnum("room_status", [
  "available", "occupied", "cleaning", "maintenance", "reserved",
]);

export const roomTypeEnum = pgEnum("room_type", [
  "single", "double", "twin", "suite", "deluxe", "penthouse",
]);

// ─── Bookings ────────────────────────────────────────────────
export const restaurantBookingStatusEnum = pgEnum("restaurant_booking_status", [
  "requested", "confirmed", "seated", "done", "cancelled", "no_show",
]);

export const roomServiceOrderStatusEnum = pgEnum("room_service_order_status", [
  "pending", "preparing", "delivering", "delivered", "cancelled",
]);

export const laundryOrderStatusEnum = pgEnum("laundry_order_status", [
  "pending", "collected", "washing", "ready", "delivered", "cancelled",
]);

export const spaBookingStatusEnum = pgEnum("spa_booking_status", [
  "requested", "confirmed", "in_progress", "done", "cancelled",
]);

// ─── Payments ────────────────────────────────────────────────
export const paymentStatusEnum = pgEnum("payment_status", [
  "pending", "authorized", "captured", "refunded", "failed", "cancelled",
]);

// ─── Conversations ───────────────────────────────────────────
export const conversationAudienceEnum = pgEnum("conversation_audience", [
  "guest_ai", "guest_staff", "staff_internal",
]);

export const messageRoleEnum = pgEnum("message_role", [
  "user", "assistant", "staff", "system",
]);

// ─── Tickets ─────────────────────────────────────────────────
export const ticketStatusEnum = pgEnum("ticket_status", [
  "open", "in_progress", "waiting", "resolved", "closed", "cancelled",
]);

export const ticketPriorityEnum = pgEnum("ticket_priority", [
  "low", "medium", "high", "urgent",
]);

export const ticketCategoryEnum = pgEnum("ticket_category", [
  "complaint", "special_request", "maintenance", "housekeeping",
  "billing", "concierge", "other",
]);

// ─── Staff notes ─────────────────────────────────────────────
export const staffNoteStatusEnum = pgEnum("staff_note_status", [
  "pending", "in_progress", "done", "cancelled",
]);

export const serviceTypeEnum = pgEnum("service_type", [
  "restaurant", "spa", "room_service", "housekeeping", "laundry",
  "front_office", "maintenance", "meeting", "other",
]);

// ─── Integrations ────────────────────────────────────────────
export const integrationProviderEnum = pgEnum("integration_provider", [
  "mews", "opera", "cloudbeds", "protel", "apaleo", "booking", "expedia",
]);

export const integrationSyncStatusEnum = pgEnum("integration_sync_status", [
  "idle", "syncing", "error",
]);

export const reservationStateEnum = pgEnum("reservation_state", [
  "inquiry", "confirmed", "checked_in", "checked_out", "canceled",
]);

export const bookingChannelEnum = pgEnum("booking_channel", [
  "booking", "expedia", "airbnb", "hrs", "hotelbeds", "agoda",
  "direct", "mews", "other",
]);

// ─── Menus ───────────────────────────────────────────────────
export const menuCategoryTypeEnum = pgEnum("menu_category_type", [
  "breakfast", "lunch", "dinner", "drinks", "room_service",
  "spa", "laundry", "restaurant",
]);

// ─── Analytics ───────────────────────────────────────────────
export const activityTypeEnum = pgEnum("activity_type", [
  "booking_created", "booking_updated", "booking_cancelled",
  "chat_started", "chat_resolved",
  "payment_received", "payment_refunded",
  "guest_arrived", "guest_checked_out",
  "staff_login", "staff_action",
]);

export const hotelEventTypeEnum = pgEnum("hotel_event_type", [
  "hotel_event", "conference", "wedding", "meeting", "private_event",
]);

// user, session, account, verification
// → géré par @better-auth/drizzle adapter
// → on ne les modifie pas manuellement

organization:
  - id (uuid)
  - name
  - slug (unique)
  - logo_url
  - metadata (JSONB : address, timezone, currency, locale, features)
  - created_at, updated_at

member:
  - id (uuid)
  - organization_id (FK → organization, CASCADE)
  - user_id (FK → user, CASCADE)
  - role (memberRoleEnum)
  - created_at, updated_at
  UNIQUE (organization_id, user_id)

invitation:
  - id (uuid)
  - organization_id, email, role, inviter_id, token, expires_at, status

guest:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - user_id (FK → user, nullable)              # lié au signup après coup
  - guest_group_id (FK → guest_group, nullable)
  - room_id (FK → room, nullable)
  - first_name, last_name (text)
  - email (text, indexé, unique par org)
  - phone (text)
  - is_vip (boolean, default false)
  - check_in_status (checkInStatusEnum, default "pending")
  - check_in_invited_at, check_in_completed_at, check_in_arrived_at, check_out_at (timestamp)
  - check_in_date, check_out_date (date)
  - external_guest_id (text, indexé)           # ID PMS
  - external_reservation_id (text, indexé)     # ID résa PMS
  - external_source (text)                     # "mews", "opera"...
  - external_channel (bookingChannelEnum)      # "booking.com", etc.
  - notes (text)
  - deleted_at (nullable)
  - created_at, updated_at
  UNIQUE (organization_id, email)
  UNIQUE (organization_id, external_guest_id, external_source) WHERE external_guest_id IS NOT NULL

guest_group:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - name
  - contact_person, contact_email, contact_phone
  - size (int)
  - notes
  - created_at, updated_at

room:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - room_number (text, unique par org)
  - floor (int)
  - room_type (roomTypeEnum, default "double")
  - status (roomStatusEnum, default "available")
  - price_per_night (numeric(10, 2))
  - amenities (JSONB : wifi, view, jacuzzi, balcony, ...)
  - capacity (int, default 2)
  - external_room_id (text, indexé)
  - external_source (text)
  - created_at, updated_at
  UNIQUE (organization_id, room_number)

- id (uuid)
- organization_id (FK, CASCADE, INDEXED)
- guest_id (FK → guest, INDEXED, nullable si user_id direct)
- user_id (FK → user, nullable)
- status (enum spécifique)
- payment_intent_id (text)
- payment_status (paymentStatusEnum, default "pending")
- total_amount (numeric(10, 2))
- currency (text, default "EUR")
- created_at, updated_at, deleted_at

+ people_count (int)
+ date (date)
+ time (time)
+ special_request (text)
+ confirmed_at, seated_at, done_at, cancelled_at, no_show_at (timestamp)

+ items (JSONB : [{ menu_item_id, name, quantity, unit_price, options? }])
+ delivery_fee (numeric)
+ delivery_address (JSONB : { room, floor, building, instructions? })
+ preparing_at, delivering_at, delivered_at, cancelled_at (timestamp)

+ items (JSONB : [{ type, quantity, unit_price, notes? }])
+ special_request (text)
+ collected_at, washing_at, ready_at, delivered_at, cancelled_at (timestamp)

+ service_id (FK → menu_item)
+ duration_minutes (int)
+ date (date)
+ time (time)
+ therapist_id (text, nullable)
+ confirmed_at, in_progress_at, done_at, cancelled_at (timestamp)

conversation:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - audience (conversationAudienceEnum)    # guest_ai | guest_staff | staff_internal
  - guest_id (FK → guest, nullable)
  - user_id (FK → user, nullable)
  - assigned_staff_id (FK → user, nullable) # pour guest_staff escalade
  - title (text)                            # AI-generated
  - summary (text)                          # AI-generated, visible cardex
  - priority (ticketPriorityEnum, nullable)
  - is_resolved (boolean, default false)
  - resolved_at (timestamp, nullable)
  - metadata (JSONB : service_type, tags, AI cost cumul)
  - created_at, updated_at, deleted_at

message:
  - id (uuid)
  - conversation_id (FK, CASCADE, INDEXED)
  - role (messageRoleEnum)                  # user | assistant | staff | system
  - sender_user_id (FK → user, nullable)    # pour staff ou guest connecté
  - content (text)
  - metadata (JSONB : tool_calls, ai_cost, attachments)
  - sent_at (timestamp, default now)
  - read_at (timestamp, nullable)

ticket:
  - id (uuid)
  - ticket_number (text, unique par org)    # "TKT-00042"
  - organization_id (FK, CASCADE, INDEXED)
  - conversation_id (FK → conversation, nullable)  # lien vers chat escaladé
  - guest_id (FK → guest, nullable)
  - room_id (FK → room, nullable)
  - category (ticketCategoryEnum)
  - priority (ticketPriorityEnum)
  - status (ticketStatusEnum, default "open")
  - title (text)
  - description (text)
  - assigned_to_id (FK → user, nullable)
  - created_by_id (FK → user)
  - resolved_at, closed_at (timestamp, nullable)
  - deleted_at (nullable)
  - created_at, updated_at
  UNIQUE (organization_id, ticket_number)

ticket_comment:
  - id (uuid)
  - ticket_id (FK, CASCADE, INDEXED)
  - user_id (FK → user)
  - content (text)
  - is_internal (boolean)                   # visible staff only
  - created_at

staff_note:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - title (text)
  - content (text)
  - service_type (serviceTypeEnum, nullable)
  - priority (ticketPriorityEnum, default "medium")
  - status (staffNoteStatusEnum, default "pending")
  - due_date (timestamp, nullable)
  - room_id (FK, nullable)
  - guest_id (FK, nullable)
  - assigned_to_id (FK → user, nullable)
  - created_by_id (FK → user)
  - completed_at (nullable)
  - deleted_at (nullable)
  - created_at, updated_at

note_comment:
  - id (uuid)
  - note_id (FK, CASCADE)
  - user_id (FK → user)
  - action (text)                           # "status_changed", "assigned", "commented"
  - previous_value, new_value (text, nullable)
  - content (text, nullable)
  - created_at

menu_category:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - category_type (menuCategoryTypeEnum)
  - name (text)
  - description (text)
  - display_order (int)
  - is_active (boolean, default true)

menu_item:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - category_id (FK → menu_category, INDEXED)
  - name, description
  - price (numeric(10, 2))
  - currency (text)
  - image_url (text)
  - duration_minutes (int, nullable)        # pour spa
  - option_groups (JSONB : [{ name, options: [{ name, price_delta }] }])
  - allergens (JSONB : string[])
  - is_featured (boolean)
  - is_active (boolean)

integration:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - provider (integrationProviderEnum)
  - name (text)                              # "Mews - Oceania Paris"
  - credentials (JSONB encrypted)            # tokens, keys
  - platform_url (text)
  - external_property_id (text)              # "enterpriseId" chez Mews
  - config (JSONB : options spécifiques)
  - is_active (boolean, default true)
  - last_sync_at (timestamp, nullable)
  - sync_status (integrationSyncStatusEnum, default "idle")
  - sync_error (text, nullable)
  - webhook_secret (text)                    # pour HMAC
  - created_at, updated_at
  UNIQUE (organization_id, provider)

integration_sync_log:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - integration_id (FK → integration, CASCADE, INDEXED)
  - provider (text)
  - event_type (text)                        # "full_sync" | "webhook" | "manual"
  - external_entity_id (text, nullable)
  - bell_entity_type (text, nullable)        # "guest" | "room" | "reservation"
  - bell_entity_id (text, nullable)
  - action (text)                            # "created" | "updated" | "skipped" | "error"
  - error_message (text, nullable)
  - metadata (JSONB)
  - created_at

activity_log:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - activity_type (activityTypeEnum)
  - user_id (FK → user, nullable)
  - guest_id (FK → guest, nullable)
  - entity_type (text, nullable)
  - entity_id (text, nullable)
  - description (text)
  - metadata (JSONB)
  - occurred_at (timestamp, INDEXED)         # index brin temporel

interaction_log:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - interaction_type (text)
  - user_id, conversation_id (FK)
  - duration_seconds (int)
  - revenue_generated (numeric)
  - was_successful (boolean)
  - metadata (JSONB)
  - occurred_at (timestamp, INDEXED)

hotel_event:
  - id (uuid)
  - organization_id (FK, CASCADE, INDEXED)
  - title (text)
  - event_type (hotelEventTypeEnum)
  - description
  - start_date, end_date (timestamp)
  - capacity (int, nullable)
  - location (text)                          # ex: "Salon Versailles"
  - created_at, updated_at

-- Cardex : liste des guests arrivés avec leur chambre
SELECT g.*, r.room_number, r.floor
FROM guest g
LEFT JOIN room r ON r.id = g.room_id
WHERE g.organization_id = $1
  AND g.check_in_status = 'arrived'
  AND g.deleted_at IS NULL
ORDER BY g.check_in_arrived_at DESC;

-- Chats actifs staff
SELECT c.*, g.first_name, g.last_name, r.room_number
FROM conversation c
LEFT JOIN guest g ON g.id = c.guest_id
LEFT JOIN room r ON r.id = g.room_id
WHERE c.organization_id = $1
  AND c.audience IN ('guest_ai', 'guest_staff')
  AND c.is_resolved = false
ORDER BY c.updated_at DESC
LIMIT 100;

-- Revenue par canal ce mois
SELECT
  g.external_channel,
  COUNT(*) AS bookings,
  SUM(CASE
    WHEN b.payment_status = 'captured' THEN b.total_amount
    ELSE 0
  END) AS revenue
FROM guest g
JOIN room_service_order b ON b.guest_id = g.id
WHERE g.organization_id = $1
  AND b.created_at >= date_trunc('month', now())
GROUP BY g.external_channel
ORDER BY revenue DESC;