013 - staff_coaches rename + outlet assignments

• Tanggal: 2026-08-10
• DB: mpcdb (PostgreSQL)
• Ringkas: Rename coaches → staff_coaches; user_id → staff_id; create staff_outlet_assignments; ensure every coach has a staff row.

ALTER (jalankan di DB server)

-- Rename coaches table
ALTER TABLE IF EXISTS coaches RENAME TO staff_coaches;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='staff_coaches' AND column_name='user_id'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='staff_coaches' AND column_name='staff_id'
  ) THEN
    ALTER TABLE staff_coaches RENAME COLUMN user_id TO staff_id;
  END IF;
END $$;

-- Ensure staff rows for coaches missing staff_id
INSERT INTO staff (id, name, email, password_hash, role, status, created_at, updated_at, code)
SELECT gen_random_uuid(),
       sc.name,
       COALESCE(NULLIF(sc.email, ''), 'coach-' || sc.id::text || '@metropolepadel.local'),
       '', -- no login until invited
       'Coach',
       'Inactive',
       NOW(),
       NOW(),
       'STF-COACH-' || substr(sc.id::text, 1, 8)
FROM staff_coaches sc
WHERE sc.staff_id IS NULL
  AND sc.deleted_at IS NULL
ON CONFLICT (email) DO NOTHING;

UPDATE staff_coaches sc
SET staff_id = s.id
FROM staff s
WHERE sc.staff_id IS NULL
  AND (
    (sc.email IS NOT NULL AND sc.email <> '' AND lower(s.email) = lower(sc.email))
    OR s.code = 'STF-COACH-' || substr(sc.id::text, 1, 8)
  );

-- Prefer unique staff_id for active coaches
CREATE UNIQUE INDEX IF NOT EXISTS idx_staff_coaches_staff_id
  ON staff_coaches(staff_id)
  WHERE staff_id IS NOT NULL AND deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS staff_outlet_assignments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  staff_id uuid NOT NULL REFERENCES staff(id),
  outlet_id uuid NOT NULL REFERENCES pos_outlets(id),
  is_primary boolean NOT NULL DEFAULT false,
  code text UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  deleted_at timestamptz,
  created_by uuid,
  updated_by uuid,
  deleted_by uuid,
  UNIQUE (staff_id, outlet_id)
);

-- Seed: assign all Active staff to default outlet
INSERT INTO staff_outlet_assignments (id, staff_id, outlet_id, is_primary, code, created_at, updated_at)
SELECT gen_random_uuid(), s.id, o.id, true, 'SOA-' || substr(s.id::text, 1, 8), NOW(), NOW()
FROM staff s
CROSS JOIN LATERAL (
  SELECT id FROM pos_outlets WHERE deleted_at IS NULL ORDER BY code LIMIT 1
) o
WHERE s.deleted_at IS NULL
  AND NOT EXISTS (
    SELECT 1 FROM staff_outlet_assignments a
    WHERE a.staff_id = s.id AND a.outlet_id = o.id AND a.deleted_at IS NULL
  );

Rollback

DROP TABLE IF EXISTS staff_outlet_assignments;
ALTER TABLE staff_coaches RENAME COLUMN staff_id TO user_id;
ALTER TABLE staff_coaches RENAME TO coaches;

Catatan

• Member API path /coaches may remain; storage table is staff_coaches.
• POS login should require assignment matching device outlet.