-- Idempotent update: introduce assignable-roles mapping for privilege-escalation prevention.
-- Each role defines which other roles its members may assign to users.

CREATE TABLE IF NOT EXISTS `role_assignable_roles` (
  `role_id` INT UNSIGNED NOT NULL,
  `assignable_role_id` INT UNSIGNED NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`, `assignable_role_id`),
  KEY `idx_rar_assignable` (`assignable_role_id`),
  CONSTRAINT `fk_rar_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rar_assignable` FOREIGN KEY (`assignable_role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permission: bypass assignable-roles check (super-admin)
INSERT INTO `permissions` (`key`, `description`, `active`, `is_system`)
VALUES ('roles.assign_all', 'permission.roles.assign_all', 1, 1)
ON DUPLICATE KEY UPDATE `key` = `key`;

-- Grant roles.assign_all to the ADMIN role
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.`key` = 'roles.assign_all'
WHERE r.code = 'ADMIN';

-- Seed: ADMIN role can assign every existing role
INSERT IGNORE INTO `role_assignable_roles` (`role_id`, `assignable_role_id`)
SELECT admin_role.id, all_roles.id
FROM roles admin_role
CROSS JOIN roles all_roles
WHERE admin_role.code = 'ADMIN';