BEGIN;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
VALUES ('hr-payroll', 'hr.payroll.exports.approve', 'Approve Payroll Exports', 'Approve or reject payroll export requests created by another user.')
ON CONFLICT (permission_code) DO UPDATE
SET permission_name = EXCLUDED.permission_name,
    description = EXCLUDED.description;

WITH permission AS (
    SELECT id
    FROM iam.permissions
    WHERE permission_code = 'hr.payroll.exports.approve'
), eligible_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(REPLACE(COALESCE(code, name, ''), ' ', '_'), '-', '_')) IN (
          'hr_finance',
          'hr_payroll_accountant',
          'payroll_accountant'
      )
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM eligible_roles role
CROSS JOIN permission
WHERE NOT EXISTS (
    SELECT 1
    FROM iam.role_permissions existing
    WHERE existing.role_id = role.id
      AND existing.permission_id = permission.id
);

COMMIT;