017 - Matchplay participants & games

• Tanggal: 2026-08-11
• DB: mpcdb
• Ringkas: Extend matches (host, is_double, court) + tabel roster match_participants + pairing/skor match_games.

ALTER (jalankan di DB server)

-- Extend matches
ALTER TABLE matches
  ADD COLUMN IF NOT EXISTS host_customer_id uuid,
  ADD COLUMN IF NOT EXISTS is_double boolean,
  ADD COLUMN IF NOT EXISTS court_id uuid;

CREATE INDEX IF NOT EXISTS idx_matches_host_customer_id ON matches (host_customer_id);
CREATE INDEX IF NOT EXISTS idx_matches_status ON matches (status);

-- Roster
CREATE TABLE IF NOT EXISTS match_participants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches(id),
  customer_id uuid REFERENCES customers(id),
  display_name text NOT NULL,
  role text NOT NULL DEFAULT 'player',
  points int NOT NULL DEFAULT 0,
  joined_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);

CREATE INDEX IF NOT EXISTS idx_match_participants_match_id ON match_participants (match_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_match_customer_unique
  ON match_participants (match_id, customer_id)
  WHERE customer_id IS NOT NULL AND deleted_at IS NULL;

-- Games / pairings
CREATE TABLE IF NOT EXISTS match_games (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  match_id uuid NOT NULL REFERENCES matches(id),
  sequence int NOT NULL,
  team_a text[] NOT NULL DEFAULT '{}',
  team_b text[] NOT NULL DEFAULT '{}',
  score_a int NOT NULL DEFAULT 0,
  score_b int NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'Pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);

CREATE INDEX IF NOT EXISTS idx_match_games_match_id ON match_games (match_id);

Rollback (opsional)

DROP TABLE IF EXISTS match_games;
DROP TABLE IF EXISTS match_participants;
ALTER TABLE matches
  DROP COLUMN IF EXISTS host_customer_id,
  DROP COLUMN IF EXISTS is_double,
  DROP COLUMN IF EXISTS court_id;

Catatan

• Dampak: mpc-be matchplay API; member apps (Android/iOS/Web); Prisma admin sync.
• mpc-be-pos / POS / owner / ksr-sync: tidak terdampak.