-- =============================================================================
-- 03-member-access-role.sql
-- Migration: MemberAccessRole1780910000000
-- Requires: 00b-cpanel-helpers.sql, 02-access-control-schema.sql
-- =============================================================================

ALTER TABLE `members`
  ADD `access_role_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;

CALL cp_safe_exec('ALTER TABLE `members` ADD CONSTRAINT `FK_members_access_role` FOREIGN KEY (`access_role_id`) REFERENCES `access_roles`(`id`) ON DELETE SET NULL ON UPDATE NO ACTION');

INSERT INTO migrations (timestamp, name)
SELECT 1780910000000, 'MemberAccessRole1780910000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'MemberAccessRole1780910000000'
);

SELECT COUNT(*) AS members_missing_access_role_id
FROM members
WHERE access_role_id IS NULL;
