schema / migration / 020-resto-spaces-reservations

020 - Resto: spaces, space_reservations, table mapping, VIP room bundling

  • Tanggal: 2026-08-13
  • DB: mpcdb (shared Postgres — mpc-be / mpc-be-pos / mpc-admin)
  • Ringkas: Tabel spaces (Room / TableArea + minimum spend display-only), space_reservations (reservasi by pax / room), space_tables + space_reservation_tables (mapping meja oleh kasir di POS), courts.bundled_space_id (bundling Court VIP ↔ Room), setting durasi & jam operasional resto di store_settings.

ALTER / DDL (jalankan di DB server)

-- Idempotent; safe to re-run.

-- 1) Spaces: Room (unit eksklusif) & TableArea (pool pax)
CREATE TABLE IF NOT EXISTS spaces (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  branch_id            uuid REFERENCES branches(id),
  name                 text NOT NULL,
  code                 text UNIQUE,
  space_type           text NOT NULL CHECK (space_type IN ('Room', 'TableArea')),
  capacity_pax         integer NOT NULL DEFAULT 0,
  online_capacity_pax  integer,            -- NULL = sama dengan capacity_pax; TableArea: kuota pax untuk reservasi online (sisa = buffer walk-in)
  max_party_pax        integer,            -- NULL = tanpa batas; di atas ini member diarahkan "hubungi kami"
  minimum_spend        numeric(12,2),      -- display-only; dipenuhi via pembelian FnB (kena PB1 + service di jalur existing)
  is_online_reservable boolean NOT NULL DEFAULT true,
  image_url            text,
  description          text,
  status               text NOT NULL DEFAULT 'Active',
  display_order        integer NOT NULL DEFAULT 0,
  created_at           timestamp NOT NULL DEFAULT now(),
  updated_at           timestamp NOT NULL DEFAULT now(),
  deleted_at           timestamp,
  created_by           uuid,
  updated_by           uuid,
  deleted_by           uuid
);
CREATE INDEX IF NOT EXISTS idx_spaces_branch_id ON spaces(branch_id);

-- 2) Reservasi space (member online / staff walk-in / bundling court VIP)
CREATE TABLE IF NOT EXISTS space_reservations (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  space_id               uuid NOT NULL REFERENCES spaces(id),
  customer_id            uuid REFERENCES customers(id),  -- NULL untuk walk-in staff
  guest_name             text,                           -- nama tamu walk-in
  reservation_date       date NOT NULL,
  start_time             timestamp NOT NULL,
  end_time               timestamp NOT NULL,
  pax                    integer NOT NULL,
  status                 text NOT NULL DEFAULT 'Confirmed'
                         CHECK (status IN ('Confirmed', 'Seated', 'Completed', 'Cancelled', 'NoShow')),
  source                 text NOT NULL DEFAULT 'Online'
                         CHECK (source IN ('Online', 'Staff', 'CourtBundle')),
  notes                  text,
  minimum_spend_snapshot numeric(12,2),
  booking_session_id     uuid,   -- grouping key booking court (bundling VIP); BUKAN FK
  code                   text UNIQUE,
  created_at             timestamp NOT NULL DEFAULT now(),
  updated_at             timestamp NOT NULL DEFAULT now(),
  deleted_at             timestamp,
  created_by             uuid,
  updated_by             uuid,
  deleted_by             uuid
);
CREATE INDEX IF NOT EXISTS idx_space_reservations_space_window
  ON space_reservations(space_id, start_time, end_time);
CREATE INDEX IF NOT EXISTS idx_space_reservations_date ON space_reservations(reservation_date);
CREATE INDEX IF NOT EXISTS idx_space_reservations_customer ON space_reservations(customer_id);
CREATE INDEX IF NOT EXISTS idx_space_reservations_booking_session ON space_reservations(booking_session_id);

-- 3) Meja fisik (mapping operasional kasir di POS; TIDAK dipakai availability online)
CREATE TABLE IF NOT EXISTS space_tables (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  space_id      uuid NOT NULL REFERENCES spaces(id),
  name          text NOT NULL,        -- T1, T2, ...
  capacity      integer NOT NULL DEFAULT 2,
  is_active     boolean NOT NULL DEFAULT true,
  display_order integer NOT NULL DEFAULT 0,
  code          text UNIQUE,
  created_at    timestamp NOT NULL DEFAULT now(),
  updated_at    timestamp NOT NULL DEFAULT now(),
  deleted_at    timestamp,
  created_by    uuid,
  updated_by    uuid,
  deleted_by    uuid
);
CREATE INDEX IF NOT EXISTS idx_space_tables_space_id ON space_tables(space_id);

-- 4) Join: satu reservasi bisa beberapa meja (rombongan / meja digabung)
CREATE TABLE IF NOT EXISTS space_reservation_tables (
  reservation_id uuid NOT NULL REFERENCES space_reservations(id) ON DELETE CASCADE,
  table_id       uuid NOT NULL REFERENCES space_tables(id) ON DELETE CASCADE,
  created_at     timestamp NOT NULL DEFAULT now(),
  PRIMARY KEY (reservation_id, table_id)
);
CREATE INDEX IF NOT EXISTS idx_space_reservation_tables_table ON space_reservation_tables(table_id);

-- 5) Bundling Court VIP <-> Room
ALTER TABLE courts
  ADD COLUMN IF NOT EXISTS bundled_space_id uuid REFERENCES spaces(id);
CREATE INDEX IF NOT EXISTS idx_courts_bundled_space ON courts(bundled_space_id);

-- 6) Setting resto
ALTER TABLE store_settings
  ADD COLUMN IF NOT EXISTS space_reservation_duration_minutes integer NOT NULL DEFAULT 90,
  ADD COLUMN IF NOT EXISTS resto_open_time  text NOT NULL DEFAULT '10:00',
  ADD COLUMN IF NOT EXISTS resto_close_time text NOT NULL DEFAULT '22:00';

Seed demo (idempotent)

-- Spaces (branch default)
INSERT INTO spaces (branch_id, name, code, space_type, capacity_pax, online_capacity_pax, max_party_pax, minimum_spend, is_online_reservable, description, image_url, status, display_order)
SELECT b.id, v.name, v.code, v.space_type, v.capacity_pax, v.online_capacity_pax, v.max_party_pax, v.minimum_spend, true, v.description, v.image_url, 'Active', v.display_order
FROM (SELECT id FROM branches WHERE is_default = true AND deleted_at IS NULL LIMIT 1) b,
(VALUES
  ('Indoor Dining',   'SPC-INDOOR',  'TableArea', 50, 30, 10, NULL::numeric,      'Ruang makan utama dengan suasana tenang menghadap court.', 'https://images.pexels.com/photos/67468/pexels-photo-67468.jpeg?auto=compress&cs=tinysrgb&w=1200', 1),
  ('Outdoor Terrace', 'SPC-OUTDOOR', 'TableArea', 30, 20, 10, NULL::numeric,      'Teras terbuka dengan pemandangan lapangan padel.', 'https://images.pexels.com/photos/2290753/pexels-photo-2290753.jpeg?auto=compress&cs=tinysrgb&w=1200', 2),
  ('Room VIP 1',      'SPC-VIP-1',   'Room',      12, NULL, NULL, 2000000::numeric, 'Ruang privat untuk 12 tamu dengan layanan khusus.', 'https://images.pexels.com/photos/941861/pexels-photo-941861.jpeg?auto=compress&cs=tinysrgb&w=1200', 3),
  ('Room VIP 2',      'SPC-VIP-2',   'Room',      8,  NULL, NULL, 1500000::numeric, 'Ruang privat intim untuk 8 tamu.', 'https://images.pexels.com/photos/262047/pexels-photo-262047.jpeg?auto=compress&cs=tinysrgb&w=1200', 4)
) AS v(name, code, space_type, capacity_pax, online_capacity_pax, max_party_pax, minimum_spend, description, image_url, display_order)
ON CONFLICT (code) DO NOTHING;

-- Backfill image_url bila seed lama sudah terlanjur jalan tanpa foto (idempotent)
UPDATE spaces SET image_url = 'https://images.pexels.com/photos/67468/pexels-photo-67468.jpeg?auto=compress&cs=tinysrgb&w=1200', updated_at = now() WHERE code = 'SPC-INDOOR'  AND image_url IS NULL;
UPDATE spaces SET image_url = 'https://images.pexels.com/photos/2290753/pexels-photo-2290753.jpeg?auto=compress&cs=tinysrgb&w=1200', updated_at = now() WHERE code = 'SPC-OUTDOOR' AND image_url IS NULL;
UPDATE spaces SET image_url = 'https://images.pexels.com/photos/941861/pexels-photo-941861.jpeg?auto=compress&cs=tinysrgb&w=1200', updated_at = now() WHERE code = 'SPC-VIP-1'   AND image_url IS NULL;
UPDATE spaces SET image_url = 'https://images.pexels.com/photos/262047/pexels-photo-262047.jpeg?auto=compress&cs=tinysrgb&w=1200', updated_at = now() WHERE code = 'SPC-VIP-2'   AND image_url IS NULL;

-- Meja demo Indoor (T1–T6) & Outdoor (O1–O4)
INSERT INTO space_tables (space_id, name, code, capacity, display_order)
SELECT s.id, v.name, v.code, v.capacity, v.display_order
FROM spaces s
JOIN (VALUES
  ('SPC-INDOOR',  'T1', 'TBL-T1', 4, 1), ('SPC-INDOOR', 'T2', 'TBL-T2', 4, 2),
  ('SPC-INDOOR',  'T3', 'TBL-T3', 2, 3), ('SPC-INDOOR', 'T4', 'TBL-T4', 2, 4),
  ('SPC-INDOOR',  'T5', 'TBL-T5', 6, 5), ('SPC-INDOOR', 'T6', 'TBL-T6', 10, 6),
  ('SPC-OUTDOOR', 'O1', 'TBL-O1', 4, 1), ('SPC-OUTDOOR', 'O2', 'TBL-O2', 4, 2),
  ('SPC-OUTDOOR', 'O3', 'TBL-O3', 2, 3), ('SPC-OUTDOOR', 'O4', 'TBL-O4', 6, 4)
) AS v(space_code, name, code, capacity, display_order) ON v.space_code = s.code
ON CONFLICT (code) DO NOTHING;

-- Bundling Court VIP (CRT-03) dengan Room VIP 1
UPDATE courts
SET bundled_space_id = (SELECT id FROM spaces WHERE code = 'SPC-VIP-1'),
    updated_at = now()
WHERE code = 'CRT-03' AND bundled_space_id IS NULL;

Rollback

ALTER TABLE courts DROP COLUMN IF EXISTS bundled_space_id;
ALTER TABLE store_settings
  DROP COLUMN IF EXISTS space_reservation_duration_minutes,
  DROP COLUMN IF EXISTS resto_open_time,
  DROP COLUMN IF EXISTS resto_close_time;
DROP TABLE IF EXISTS space_reservation_tables;
DROP TABLE IF EXISTS space_tables;
DROP TABLE IF EXISTS space_reservations;
DROP TABLE IF EXISTS spaces;

Catatan

  • Availability online (mpc-be): Room = eksklusif (tidak boleh ada reservasi aktif overlap, termasuk yang dibuat bundling court); TableArea = SUM(pax reservasi aktif overlap) + pax baru <= COALESCE(online_capacity_pax, capacity_pax). Enforcement di application layer dalam transaksi DB (lock per space) — tidak pakai EXCLUDE constraint karena TableArea memang boleh overlap.
  • Minimum spend display-only: reservasi Rp 0; pemenuhan lewat pembelian FnB (PB1 10% + service 5% jalur existing). Court tetap non-pajak.
  • Bundling best-effort (keputusan owner 2026-08-13): availability court TIDAK terpengaruh reservasi Room; saat checkout, reservasi CourtBundle dibuat hanya bila Room kosong (order tetap sukses tanpa Room bila penuh).
  • Status: diapply ke mpcdb production (VPS mpc-postgres) 2026-08-13.
  • Dampak app: mpc-be (endpoint spaces/reservations), mpc-admin (halaman Resto + picker bundling), mpc-be-pos + mpc-pos (reservasi harian + mapping meja), member apps (flow reservasi Resto).
  • space_tables disiapkan kompatibel untuk fase QR ordering / dine-in per meja (app terpisah, belum sesi ini).