005 - Three courts + display_order parity

• Tanggal: 2026-08-09
• DB: mpcdb (shared Postgres — mpc-be / mpc-be-pos / mpc-admin)
• Ringkas: Hanya 3 court aktif (Court 1–3); display_order 1–3; Court 3 = kategori VIP; court lain soft-retire agar urutan mobile & POS sama.

ALTER (jalankan di DB server)

-- Idempotent; safe to re-run.
-- Requires item_categories CAT-CRT-VIP and CAT-CRT-REG (from seed_categories).

DO $$
DECLARE
  v_reg uuid;
  v_vip uuid;
  v_branch uuid;
BEGIN
  SELECT id INTO v_reg FROM item_categories WHERE code = 'CAT-CRT-REG' LIMIT 1;
  SELECT id INTO v_vip FROM item_categories WHERE code = 'CAT-CRT-VIP' LIMIT 1;

  IF v_reg IS NULL OR v_vip IS NULL THEN
    RAISE EXCEPTION 'Missing court categories CAT-CRT-REG / CAT-CRT-VIP — run category seed first';
  END IF;

  SELECT id INTO v_branch FROM branches WHERE code = 'MAIN' AND deleted_at IS NULL LIMIT 1;

  -- Court 1 — Regular, display_order 1
  INSERT INTO courts (id, code, name, type, base_price_per_hour, status, category_id, display_order, branch_id, created_at)
  VALUES (gen_random_uuid(), 'CRT-01', 'Court 1', 'Indoor', 250000, 'Active', v_reg, 1, v_branch, now())
  ON CONFLICT (code) DO UPDATE SET
    name = EXCLUDED.name,
    type = 'Indoor',
    status = 'Active',
    category_id = v_reg,
    display_order = 1,
    deleted_at = NULL,
    deleted_by = NULL,
    updated_at = now(),
    branch_id = COALESCE(courts.branch_id, v_branch);

  -- Court 2 — Regular, display_order 2
  INSERT INTO courts (id, code, name, type, base_price_per_hour, status, category_id, display_order, branch_id, created_at)
  VALUES (gen_random_uuid(), 'CRT-02', 'Court 2', 'Indoor', 250000, 'Active', v_reg, 2, v_branch, now())
  ON CONFLICT (code) DO UPDATE SET
    name = EXCLUDED.name,
    type = 'Indoor',
    status = 'Active',
    category_id = v_reg,
    display_order = 2,
    deleted_at = NULL,
    deleted_by = NULL,
    updated_at = now(),
    branch_id = COALESCE(courts.branch_id, v_branch);

  -- Court 3 — VIP, display_order 3
  INSERT INTO courts (id, code, name, type, base_price_per_hour, status, category_id, display_order, branch_id, created_at)
  VALUES (gen_random_uuid(), 'CRT-03', 'Court 3', 'Indoor', 350000, 'Active', v_vip, 3, v_branch, now())
  ON CONFLICT (code) DO UPDATE SET
    name = EXCLUDED.name,
    type = 'Indoor',
    status = 'Active',
    category_id = v_vip,
    display_order = 3,
    deleted_at = NULL,
    deleted_by = NULL,
    updated_at = now(),
    branch_id = COALESCE(courts.branch_id, v_branch);

  -- Soft-retire every other court (keep FK/history)
  UPDATE courts
  SET
    status = 'Inactive',
    deleted_at = COALESCE(deleted_at, now()),
    updated_at = now()
  WHERE (code IS NULL OR code NOT IN ('CRT-01', 'CRT-02', 'CRT-03'))
    AND (deleted_at IS NULL OR status <> 'Inactive');
END $$;

Verification

SELECT c.code, c.name, c.display_order, c.status, c.deleted_at IS NOT NULL AS soft_deleted, ic.code AS category_code, ic.name AS category_name
FROM courts c
LEFT JOIN item_categories ic ON ic.id = c.category_id
ORDER BY c.display_order NULLS LAST, c.code;

Expected active rows:

Rollback (opsional)

-- Tidak mengembalikan court yang sudah soft-deleted secara otomatis.
-- Restore manual per code, contoh Court 4:
-- UPDATE courts SET status = 'Active', deleted_at = NULL, display_order = 4 WHERE code = 'CRT-04';

Catatan

• mpc-be sudah ORDER BY display_order ASC (member mobile/web).
• mpc-be-pos harus ORDER BY display_order ASC (bukan name ASC) agar POS schedule selaras.
• Soft-delete mempertahankan booking history; court retired tidak bookable.