-- Idempotent update: consolidate legacy audit permission keys to audit.* namespace.

INSERT INTO `permissions` (`key`, `description`, `active`, `is_system`)
VALUES
  ('audit.api.view', 'Can view API audit logs', 1, 1),
  ('audit.api.purge', 'Can purge API audit logs', 1, 1),
  ('audit.system.view', 'Can view system audit logs', 1, 1),
  ('audit.system.purge', 'Can purge system audit logs', 1, 1),
  ('audit.imports.view', 'Can view import audit logs', 1, 1),
  ('audit.imports.purge', 'Can purge import audit logs', 1, 1),
  ('audit.user_lifecycle.view', 'Can view user lifecycle audit logs', 1, 1),
  ('audit.user_lifecycle.restore', 'Can restore users from lifecycle audit', 1, 1)
ON DUPLICATE KEY UPDATE
  `description` = VALUES(`description`),
  `active` = VALUES(`active`),
  `is_system` = VALUES(`is_system`);

-- Preserve existing role grants by copying role_permissions from legacy keys.
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
SELECT DISTINCT rp.`role_id`, p_new.`id`, NOW()
FROM `role_permissions` rp
JOIN `permissions` p_old ON p_old.`id` = rp.`permission_id`
JOIN `permissions` p_new ON p_new.`key` = (
  CASE p_old.`key`
    WHEN 'api_audit.view' THEN 'audit.api.view'
    WHEN 'system_audit.view' THEN 'audit.system.view'
    WHEN 'system_audit.purge' THEN 'audit.system.purge'
    WHEN 'imports.audit.view' THEN 'audit.imports.view'
    WHEN 'user_lifecycle_audit.view' THEN 'audit.user_lifecycle.view'
    WHEN 'users.lifecycle_restore' THEN 'audit.user_lifecycle.restore'
    ELSE NULL
  END
)
WHERE p_old.`key` IN (
  'api_audit.view',
  'system_audit.view',
  'system_audit.purge',
  'imports.audit.view',
  'user_lifecycle_audit.view',
  'users.lifecycle_restore'
)
ON DUPLICATE KEY UPDATE
  `created` = `role_permissions`.`created`;

-- Keep admin baseline complete for purge/restore actions.
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
SELECT r.`id`, p.`id`, NOW()
FROM `roles` r
JOIN `permissions` p ON p.`key` IN (
  'audit.api.purge',
  'audit.imports.purge',
  'audit.system.purge',
  'audit.user_lifecycle.restore'
)
WHERE r.`description` IN ('Admin', 'Administrator') OR r.`id` = 1
ON DUPLICATE KEY UPDATE
  `created` = `role_permissions`.`created`;

-- Legacy keys are retained for history but deactivated to avoid future drift.
UPDATE `permissions`
SET
  `active` = 0,
  `is_system` = 1
WHERE `key` IN (
  'api_audit.view',
  'system_audit.view',
  'system_audit.purge',
  'imports.audit.view',
  'user_lifecycle_audit.view',
  'users.lifecycle_restore'
);
