backend-php/migrations/20260126_add_support_role.sql

BEGIN;

INSERT INTO "role" (role_id, company_id, role_name, role_permission, role_flag)
SELECT
  2,
  t.company_id,
  'support',
  '{}'::jsonb,
  'a'
FROM (
  SELECT r.company_id, 1 AS ord
  FROM "role" r
  WHERE r.role_name = 'Admin'

  UNION ALL

  SELECT c.company_id, 2 AS ord
  FROM "company" c
) t
ORDER BY t.ord
LIMIT 1
ON CONFLICT (role_id) DO UPDATE
SET company_id = EXCLUDED.company_id,
    role_name = EXCLUDED.role_name,
    role_permission = EXCLUDED.role_permission,
    role_flag = EXCLUDED.role_flag;

SELECT setval(
  pg_get_serial_sequence('"role"', 'role_id'),
  GREATEST((SELECT COALESCE(MAX(role_id), 0) FROM "role"), 1)
);

COMMIT;