BEGIN;

WITH inserted AS (
    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT 'hr-payroll', 'hr.payroll.exchange_rates.manage', 'Manage Payroll Exchange Rates', 'Allows creating, updating, deactivating, and removing exchange rates used for payroll currency conversion.'
    WHERE NOT EXISTS (
        SELECT 1 FROM iam.permissions WHERE permission_code = 'hr.payroll.exchange_rates.manage'
    )
    RETURNING id
), permission AS (
    SELECT id FROM inserted
    UNION
    SELECT id FROM iam.permissions WHERE permission_code = 'hr.payroll.exchange_rates.manage'
), eligible_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN (
          'hr_finance', 'hr-finance',
          'hr_operations', 'hr-operations',
          'tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator'
      )
)
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;