-- Hammerhead Workspace - Patch 8: De-duplicate roles
-- A previous patch inserted a role checking by `code`, but an existing role
-- with a different code already had the same display `name` (e.g. "Director"),
-- resulting in two rows with the same name. This keeps the OLDEST row (lowest id)
-- per duplicated name, re-points any user_roles/role_permissions that were
-- assigned to the newer duplicate over to the kept row, then removes the duplicate.
-- Safe to re-run (no-op if there are no duplicates left).

-- 1. Build a temp map of duplicate-name groups: keep_id = MIN(id), dupe_id = any others
DROP TEMPORARY TABLE IF EXISTS `_role_dupes`;
CREATE TEMPORARY TABLE `_role_dupes` AS
SELECT r.id AS dupe_id, keep.keep_id
FROM roles r
JOIN (
    SELECT name, MIN(id) AS keep_id
    FROM roles
    GROUP BY name
    HAVING COUNT(*) > 1
) keep ON keep.name = r.name
WHERE r.id <> keep.keep_id;

-- 2. Re-point any user_roles pointing at the duplicate role to the kept role
UPDATE user_roles ur
JOIN `_role_dupes` d ON d.dupe_id = ur.role_id
SET ur.role_id = d.keep_id;

-- 3. Re-point any role_permissions pointing at the duplicate role to the kept role
--    (ignore if it would create a duplicate primary key - keep the kept role's existing grants)
DROP TEMPORARY TABLE IF EXISTS `_rp_conflicts`;
CREATE TEMPORARY TABLE `_rp_conflicts` AS
SELECT rp.role_id, rp.permission_id
FROM role_permissions rp
JOIN `_role_dupes` d ON d.dupe_id = rp.role_id
JOIN role_permissions rp2 ON rp2.role_id = d.keep_id AND rp2.permission_id = rp.permission_id;

DELETE rp FROM role_permissions rp
JOIN `_rp_conflicts` c ON c.role_id = rp.role_id AND c.permission_id = rp.permission_id;

DROP TEMPORARY TABLE IF EXISTS `_rp_conflicts`;

UPDATE role_permissions rp
JOIN `_role_dupes` d ON d.dupe_id = rp.role_id
SET rp.role_id = d.keep_id;

-- 4. Delete the now-unreferenced duplicate role rows
DELETE r FROM roles r
JOIN `_role_dupes` d ON d.dupe_id = r.id;

DROP TEMPORARY TABLE IF EXISTS `_role_dupes`;

-- 5. Add the two newly requested roles (safe if they already exist)
INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'cfo', 'Chief Financial Officer (CFO)', COALESCE((SELECT level FROM roles WHERE code='ceo' LIMIT 1), 1) + 1, 'Senior financial executive.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Chief Financial Officer (CFO)');

INSERT INTO `roles` (`code`, `name`, `level`, `description`)
SELECT 'vice_president', 'Vice President', COALESCE((SELECT level FROM roles WHERE code='ceo' LIMIT 1), 1) + 1, 'Senior executive overseeing departments.'
WHERE NOT EXISTS (SELECT 1 FROM `roles` WHERE `name` = 'Vice President');

-- 6. Prevent this from happening again - enforce unique role names going forward
ALTER TABLE `roles` ADD UNIQUE KEY IF NOT EXISTS `uq_roles_name` (`name`);