002 - Multi-branch (venues)

Tanggal: 2026-08-08
DB: mpcdb (shared Postgres — mpc-be / mpc-be-pos / mpc-admin)
Ringkas: Tabel branches + branch_stocks; FK branch_id pada courts, pos_outlets, time_groups, special_days, coaches, events; seed branch Main (is_default); backfill data existing.

ALTER (jalankan di DB server)

-- Idempotent; safe to re-run.

DO $$ BEGIN
  CREATE TYPE "BranchStatus" AS ENUM ('Active', 'Inactive');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS branches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  address text,
  timezone text NOT NULL DEFAULT 'Asia/Jakarta',
  open_time text NOT NULL DEFAULT '06:00',
  close_time text NOT NULL DEFAULT '23:00',
  is_default boolean NOT NULL DEFAULT false,
  status "BranchStatus" NOT NULL DEFAULT 'Active',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  created_by uuid,
  updated_by uuid,
  deleted_by uuid
);

CREATE UNIQUE INDEX IF NOT EXISTS branches_one_default_idx
  ON branches (is_default) WHERE is_default = true AND deleted_at IS NULL;

INSERT INTO branches (code, name, address, timezone, open_time, close_time, is_default, status)
SELECT 'MAIN', 'Main', ss.address, 'Asia/Jakarta', '06:00', '23:00', true, 'Active'
FROM (SELECT address FROM store_settings WHERE deleted_at IS NULL LIMIT 1) ss
WHERE NOT EXISTS (SELECT 1 FROM branches WHERE code = 'MAIN');

INSERT INTO branches (code, name, timezone, open_time, close_time, is_default, status)
SELECT 'MAIN', 'Main', 'Asia/Jakarta', '06:00', '23:00', true, 'Active'
WHERE NOT EXISTS (SELECT 1 FROM branches WHERE code = 'MAIN');

-- courts
ALTER TABLE courts ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
UPDATE courts c SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND c.branch_id IS NULL;
CREATE INDEX IF NOT EXISTS courts_branch_id_idx ON courts (branch_id);

-- pos_outlets
ALTER TABLE pos_outlets ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
UPDATE pos_outlets o SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND o.branch_id IS NULL;
CREATE INDEX IF NOT EXISTS pos_outlets_branch_id_idx ON pos_outlets (branch_id);

-- time_groups
ALTER TABLE time_groups ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
UPDATE time_groups t SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND t.branch_id IS NULL;
CREATE INDEX IF NOT EXISTS time_groups_branch_id_idx ON time_groups (branch_id);

-- special_days
ALTER TABLE special_days ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
UPDATE special_days s SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND s.branch_id IS NULL;
CREATE INDEX IF NOT EXISTS special_days_branch_id_idx ON special_days (branch_id);

-- coaches
ALTER TABLE coaches ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
UPDATE coaches c SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND c.branch_id IS NULL;
CREATE INDEX IF NOT EXISTS coaches_branch_id_idx ON coaches (branch_id);

-- events (if table exists)
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'events') THEN
    ALTER TABLE events ADD COLUMN IF NOT EXISTS branch_id uuid REFERENCES branches(id);
    UPDATE events e SET branch_id = b.id FROM branches b WHERE b.code = 'MAIN' AND e.branch_id IS NULL;
    CREATE INDEX IF NOT EXISTS events_branch_id_idx ON events (branch_id);
  END IF;
END $$;

-- branch_stocks (catalog tetap di products; stok per branch)
CREATE TABLE IF NOT EXISTS branch_stocks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  branch_id uuid NOT NULL REFERENCES branches(id),
  product_id uuid NOT NULL REFERENCES products(id),
  stock_quantity int NOT NULL DEFAULT 0,
  reorder_level int NOT NULL DEFAULT 10,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  UNIQUE (branch_id, product_id)
);

INSERT INTO branch_stocks (branch_id, product_id, stock_quantity, reorder_level)
SELECT b.id, p.id, COALESCE(p.stock_quantity, 0), COALESCE(p.reorder_level, 10)
FROM products p
CROSS JOIN branches b
WHERE b.code = 'MAIN' AND p.deleted_at IS NULL
ON CONFLICT (branch_id, product_id) DO NOTHING;

Rollback (opsional)

DROP TABLE IF EXISTS branch_stocks;
ALTER TABLE courts DROP COLUMN IF EXISTS branch_id;
ALTER TABLE pos_outlets DROP COLUMN IF EXISTS branch_id;
ALTER TABLE time_groups DROP COLUMN IF EXISTS branch_id;
ALTER TABLE special_days DROP COLUMN IF EXISTS branch_id;
ALTER TABLE coaches DROP COLUMN IF EXISTS branch_id;
ALTER TABLE events DROP COLUMN IF EXISTS branch_id;
DROP TABLE IF EXISTS branches;
DROP TYPE IF EXISTS "BranchStatus";

Catatan

Dampak: mpc-be (default branch resolve), mpc-be-pos (outlet→branch schedule), mpc-admin (CRUD + switcher).
Member apps: omit branch_id → Main; tidak wajib diubah.
Setelah jalankan: pastikan tepat satu row is_default = true.