BEGIN;

WITH permissions_to_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr_performance', 'hr.performance.view', 'View Performance Management', 'Allows viewing performance cycles, reviews, KPIs, calibration evidence, and employee feedback.'),
        ('hr_performance', 'hr.performance.create', 'Create Performance Records', 'Allows creating performance cycles, reviews, KPIs, and feedback records.'),
        ('hr_performance', 'hr.performance.update', 'Update Performance Records', 'Allows updating performance records and running review workflow actions.'),
        ('hr_performance', 'hr.performance.delete', 'Delete Performance Records', 'Allows removing unlocked performance records.'),
        ('hr_performance', 'hr.performance.calibrate', 'Calibrate Performance Reviews', 'Allows calibration of performance scores before approval.'),
        ('hr_performance', 'hr.performance.approve', 'Approve Performance Reviews', 'Allows approving and locking calibrated performance reviews.')
),
inserted_permissions AS (
    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT module_code, permission_code, permission_name, description
    FROM permissions_to_seed seed
    WHERE NOT EXISTS (
        SELECT 1
        FROM iam.permissions existing
        WHERE existing.permission_code = seed.permission_code
    )
    RETURNING id, permission_code
),
all_permissions AS (
    SELECT id, permission_code
    FROM inserted_permissions
    UNION
    SELECT p.id, p.permission_code
    FROM iam.permissions p
    INNER JOIN permissions_to_seed seed ON seed.permission_code = p.permission_code
),
privileged_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN (
          'admin',
          'administrator',
          'super_admin',
          'super-admin',
          'owner',
          'tenant_owner',
          'tenant-owner',
          'hr_admin',
          'hr_manager',
          'human_resources_manager'
      )
),
manager_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN (
          'manager',
          'line_manager',
          'department_manager',
          'hr_manager',
          'human_resources_manager'
      )
)
INSERT INTO iam.role_permissions (role_id, permission_id, created_at)
SELECT role_id, permission_id, CURRENT_TIMESTAMP
FROM (
    SELECT pr.id AS role_id, ap.id AS permission_id
    FROM privileged_roles pr
    CROSS JOIN all_permissions ap

    UNION

    SELECT mr.id AS role_id, ap.id AS permission_id
    FROM manager_roles mr
    INNER JOIN all_permissions ap
        ON ap.permission_code IN (
            'hr.performance.view',
            'hr.performance.create',
            'hr.performance.update'
        )
) grants
WHERE NOT EXISTS (
    SELECT 1
    FROM iam.role_permissions existing
    WHERE existing.role_id = grants.role_id
      AND existing.permission_id = grants.permission_id
);

COMMIT;