-- 004_roles_developer.sql
-- Adds the `developer` role and re-roles existing platform-staff users.
-- Hierarchy: developer > maintainer > owner (tenant admin) > subscriber > operator
-- Re-runnable. The migrate.php tracker prevents double-application.

ALTER TABLE users
    MODIFY COLUMN role ENUM('developer','maintainer','owner','subscriber','operator')
    NOT NULL DEFAULT 'subscriber';

-- Promote any legacy `owner` accounts that live in the platform org to `developer`.
-- Tenant-side `owner` users (organisations other than the platform org) keep their
-- role  -  under the new model `owner` IS the tenant admin role.
UPDATE users u
JOIN organizations o ON o.id = u.org_id
SET u.role = 'developer'
WHERE u.role = 'owner'
  AND o.name = 'B-connex Internal';

-- A safety net for installs without a Platform org row: the seeded admin@/owner@
-- accounts always belong to the platform org by convention. If neither survives,
-- ensure at least the historical admin@b-connex.local mailbox is a developer.
UPDATE users SET role = 'developer'
WHERE email IN ('admin@b-connex.local', 'owner@b-connex.local')
  AND role IN ('owner','maintainer');
