BEGIN;

WITH permissions_to_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr_self_service', 'hr.self_service.feedback.create', 'Submit Employee Feedback', 'Allows an employee to submit private or anonymous feedback to another current employee.'),
        ('hr_self_service', 'hr.self_service.grievance.create', 'Submit Own Grievance', 'Allows an employee to confidentially submit a grievance about their own employment experience.')
), inserted 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 p WHERE p.permission_code = seed.permission_code)
    RETURNING id, permission_code
), permissions AS (
    SELECT id, permission_code FROM inserted
    UNION
    SELECT p.id, p.permission_code FROM iam.permissions p
    JOIN permissions_to_seed seed ON seed.permission_code = p.permission_code
), employee_roles AS (
    SELECT id FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN ('employee_self_service', 'employee-self-service')
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM employee_roles r CROSS JOIN permissions p
WHERE NOT EXISTS (
    SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
);

INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM iam.roles r
JOIN iam.permissions p ON p.permission_code = 'hr.employee_relations.view'
WHERE COALESCE(r.is_active, TRUE) = TRUE
  AND LOWER(REPLACE(COALESCE(r.code, r.name, ''), ' ', '_')) IN ('line_manager', 'line-manager')
  AND NOT EXISTS (SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id);

COMMIT;