-- Add the new 'view:financial-details' permission if it doesn't exist
INSERT INTO "permissions" ("id", "name", "description", "category")
SELECT gen_random_uuid(), 'view:financial-details', 'Permission to view detailed financial information in ROI reports', 'Reports'
WHERE NOT EXISTS (SELECT 1 FROM "permissions" WHERE "name" = 'view:financial-details');

-- Copy any existing 'admin' permission assignments to 'view:financial-details'
INSERT INTO "role_permissions" ("id", "roleId", "permissionId")
SELECT 
    gen_random_uuid(),
    rp."roleId",
    (SELECT "id" FROM "permissions" WHERE "name" = 'view:financial-details')
FROM "role_permissions" rp
JOIN "permissions" p ON rp."permissionId" = p."id"
WHERE p."name" = 'admin'
AND NOT EXISTS (
    SELECT 1 FROM "role_permissions" rp2
    JOIN "permissions" p2 ON rp2."permissionId" = p2."id"
    WHERE rp2."roleId" = rp."roleId"
    AND p2."name" = 'view:financial-details'
);

-- Delete the old 'admin' permission and its assignments
DELETE FROM "permissions" WHERE "name" = 'admin';