BEGIN;

WITH permission_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr-payroll', 'hr.view', 'Access HR and Payroll', 'Allows access to the HR and Payroll module when a more specific permission also permits the requested operation.'),
        ('hr-payroll', 'hr.attendance.approve', 'Approve Attendance', 'Allows attendance review, adjustment, and approval decisions.'),
        ('hr-payroll', 'hr.performance.approve', 'Approve Performance Reviews', 'Allows calibration, approval, and final completion of performance reviews.'),
        ('hr-payroll', 'hr.onboarding.complete', 'Complete Employee Onboarding', 'Allows final completion of an onboarding case after all mandatory gates pass.'),
        ('hr-payroll', 'hr.lifecycle.clearance.approve', 'Approve Final Clearance', 'Allows final approval of employee clearance after all departmental gates pass.'),
        ('hr-payroll', 'hr.lifecycle.contract_renewal.approve', 'Decide Contract Renewals', 'Allows approval or rejection of employee contract renewals.'),
        ('hr-payroll', 'hr.lifecycle.exit.approve', 'Complete Exit Interviews', 'Allows controlled completion of employee exit interviews.'),
        ('hr-payroll', 'hr.lifecycle.retirement.approve', 'Approve Retirement Plans', 'Allows approval and completion of retirement plans.'),
        ('hr-payroll', 'hr.workflow.escalations.manage', 'Manage HR Workflow Escalations', 'Allows reviewing and resolving HR workflow escalations.'),
        ('hr-payroll', 'hr.privacy.view', 'View Privacy Records', 'Allows viewing employee privacy, consent, retention, and data-request records.'),
        ('hr-payroll', 'hr.privacy.requests.manage', 'Manage Privacy Requests', 'Allows recording and processing employee data requests before a final decision.'),
        ('hr-payroll', 'hr.privacy.requests.approve', 'Decide Privacy Requests', 'Allows approval, rejection, and completion decisions for employee data requests.'),
        ('hr-payroll', 'hr.privacy.retention.manage', 'Manage Retention Policies', 'Allows creating and maintaining employee-data retention policies.'),
        ('hr-payroll', 'hr.privacy.deletion.execute', 'Execute Privacy Deletion', 'Allows completion of approved deletion and right-to-be-forgotten requests.'),
        ('hr-payroll', 'hr.privacy.audit.view', 'View Privacy Audit', 'Allows viewing the privacy and compliance audit trail.'),
        ('hr-payroll', 'hr.privacy.export', 'Export Privacy Evidence', 'Allows exporting authorized privacy and compliance evidence.'),
        ('hr-payroll', 'hr.payroll.audit.export', 'Export Payroll Audit Evidence', 'Allows server-side export of the complete filtered payroll audit ledger.')
), inserted_permissions AS (
    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT module_code, permission_code, permission_name, description
    FROM permission_seed seed
    WHERE NOT EXISTS (SELECT 1 FROM iam.permissions p WHERE p.permission_code = seed.permission_code)
    RETURNING id
)
SELECT COUNT(*) FROM inserted_permissions;

WITH role_seed(code, name, description) AS (
    VALUES
        ('hr_operations', 'HR Operations / Recruiter', 'Prepares HR, recruitment, onboarding, attendance, lifecycle, training, and performance records without approval authority.'),
        ('line_manager', 'Line / Hiring Manager', 'Reviews assigned employees, recruitment activity, leave, attendance, and performance without HR administration access.'),
        ('hr_approver', 'HR Approver', 'Independently approves recruitment, onboarding completion, attendance, lifecycle, performance, and HR exceptions.'),
        ('payroll_operator', 'Payroll Operator', 'Configures payroll, captures inputs, validates records, and prepares payroll runs without approval or payment authority.'),
        ('payroll_approver', 'Payroll Approver', 'Independently reviews and approves prepared payroll and statutory filings.'),
        ('payment_authorizer', 'Payroll Payment Authorizer', 'Authorizes approved payroll payments without preparation or approval permissions.'),
        ('hr_finance', 'HR Payroll Accountant', 'Reviews payroll accounting evidence and posts approved payroll to accounting.'),
        ('privacy_officer', 'HR Privacy / Compliance Officer', 'Manages privacy requests, retention, deletion controls, and privacy audit evidence.'),
        ('employee_self_service', 'Employee Self Service', 'Provides employee-only access to personal HR self-service workflows.'),
        ('hr_read_only', 'HR Read Only', 'Provides read-only access to non-sensitive HR operational records for negative permission testing.')
)
INSERT INTO iam.roles (tenant_id, name, code, description, is_system_role, is_active, created_at, updated_at)
SELECT t.id, rs.name, rs.code, rs.description, FALSE, TRUE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM platform.tenants t
CROSS JOIN role_seed rs
WHERE NOT EXISTS (
    SELECT 1 FROM iam.roles r WHERE r.tenant_id = t.id AND LOWER(r.code) = LOWER(rs.code)
);

WITH role_permission_seed(role_code, permission_code) AS (
    VALUES
        ('hr_operations','hr.view'), ('hr_operations','hr.employees.view'), ('hr_operations','hr.employees.create'), ('hr_operations','hr.employees.update'),
        ('hr_operations','hr.departments.view'), ('hr_operations','hr.departments.create'), ('hr_operations','hr.departments.update'),
        ('hr_operations','hr.positions.view'), ('hr_operations','hr.positions.create'), ('hr_operations','hr.positions.update'),
        ('hr_operations','hr.recruitment.view'), ('hr_operations','hr.recruitment.create'), ('hr_operations','hr.recruitment.update'), ('hr_operations','hr.recruitment.stage'),
        ('hr_operations','hr.onboarding.view'), ('hr_operations','hr.onboarding.create'), ('hr_operations','hr.onboarding.update'),
        ('hr_operations','hr.attendance.view'), ('hr_operations','hr.attendance.update'), ('hr_operations','hr.leave.view'), ('hr_operations','hr.leave.create'), ('hr_operations','hr.leave.update'),
        ('hr_operations','hr.lifecycle.view'), ('hr_operations','hr.lifecycle.create'), ('hr_operations','hr.lifecycle.update'),
        ('hr_operations','hr.performance.view'), ('hr_operations','hr.performance.create'), ('hr_operations','hr.performance.update'),
        ('hr_operations','hr.training.view'), ('hr_operations','hr.training.create'), ('hr_operations','hr.training.update'),
        ('hr_operations','hr.employee_relations.view'), ('hr_operations','hr.employee_relations.create'), ('hr_operations','hr.employee_relations.update'),
        ('hr_operations','hr.reports.view'), ('hr_operations','hr.reports.create'), ('hr_operations','hr.reports.update'), ('hr_operations','hr.reports.execute'),

        ('line_manager','hr.view'), ('line_manager','hr.employees.view'), ('line_manager','hr.recruitment.view'), ('line_manager','hr.onboarding.view'),
        ('line_manager','hr.attendance.view'), ('line_manager','hr.leave.view'), ('line_manager','hr.performance.view'), ('line_manager','hr.performance.update'),
        ('line_manager','hr.self_service.manager.view'), ('line_manager','hr.self_service.manager.approve'),

        ('hr_approver','hr.view'), ('hr_approver','hr.employees.view'), ('hr_approver','hr.recruitment.view'), ('hr_approver','hr.recruitment.approve'), ('hr_approver','hr.recruitment.offer.approve'),
        ('hr_approver','hr.onboarding.view'), ('hr_approver','hr.onboarding.complete'), ('hr_approver','hr.attendance.view'), ('hr_approver','hr.attendance.approve'),
        ('hr_approver','hr.leave.view'), ('hr_approver','hr.leave.approve'), ('hr_approver','hr.lifecycle.view'), ('hr_approver','hr.lifecycle.approve'), ('hr_approver','hr.lifecycle.apply'),
        ('hr_approver','hr.lifecycle.clearance.approve'), ('hr_approver','hr.lifecycle.contract_renewal.approve'), ('hr_approver','hr.lifecycle.exit.approve'), ('hr_approver','hr.lifecycle.retirement.approve'),
        ('hr_approver','hr.performance.view'), ('hr_approver','hr.performance.approve'), ('hr_approver','hr.employee_relations.view'), ('hr_approver','hr.employee_relations.approve'),
        ('hr_approver','hr.workflow.escalations.manage'),

        ('payroll_operator','hr.view'), ('payroll_operator','hr.payroll.view'), ('payroll_operator','hr.payroll.update'), ('payroll_operator','hr.payroll.generate_items'),
        ('payroll_operator','hr.payroll.exports.view'), ('payroll_operator','hr.payroll.exports.request'), ('payroll_operator','hr.payroll.exports.generate'),
        ('payroll_operator','hr.statutory.filings.view'), ('payroll_operator','hr.statutory.filings.create'), ('payroll_operator','hr.statutory.filings.submit'), ('payroll_operator','hr.payroll.audit.view'),

        ('payroll_approver','hr.view'), ('payroll_approver','hr.payroll.view'), ('payroll_approver','hr.payroll.approve'),
        ('payroll_approver','hr.payroll.exports.view'), ('payroll_approver','hr.payroll.exports.approve'),
        ('payroll_approver','hr.statutory.filings.view'), ('payroll_approver','hr.statutory.filings.approve'), ('payroll_approver','hr.payroll.audit.view'),

        ('payment_authorizer','hr.view'), ('payment_authorizer','hr.payroll.view'), ('payment_authorizer','hr.payroll.pay'),
        ('payment_authorizer','hr.statutory.filings.view'), ('payment_authorizer','hr.statutory.filings.pay'), ('payment_authorizer','hr.payroll.audit.view'),

        ('hr_finance','hr.view'), ('hr_finance','hr.payroll.view'), ('hr_finance','hr.payroll.post_accounting'), ('hr_finance','hr.payroll.audit.view'),
        ('hr_finance','hr.payroll.exports.view'), ('hr_finance','hr.payroll.exports.download'), ('hr_finance','hr.statutory.filings.view'),

        ('privacy_officer','hr.view'), ('privacy_officer','hr.privacy.view'), ('privacy_officer','hr.privacy.requests.manage'), ('privacy_officer','hr.privacy.requests.approve'),
        ('privacy_officer','hr.privacy.retention.manage'), ('privacy_officer','hr.privacy.deletion.execute'), ('privacy_officer','hr.privacy.audit.view'), ('privacy_officer','hr.privacy.export'),

        ('employee_self_service','hr.view'), ('employee_self_service','hr.self_service.view'), ('employee_self_service','hr.self_service.profile_change.create'),
        ('employee_self_service','hr.leave.view'), ('employee_self_service','hr.leave.create'),

        ('hr_read_only','hr.view'), ('hr_read_only','hr.employees.view'), ('hr_read_only','hr.departments.view'), ('hr_read_only','hr.positions.view'),
        ('hr_read_only','hr.recruitment.view'), ('hr_read_only','hr.onboarding.view'), ('hr_read_only','hr.attendance.view'), ('hr_read_only','hr.leave.view'),
        ('hr_read_only','hr.lifecycle.view'), ('hr_read_only','hr.performance.view'), ('hr_read_only','hr.training.view'), ('hr_read_only','hr.employee_relations.view'), ('hr_read_only','hr.reports.view')
), eligible_roles AS (
    SELECT r.id, r.tenant_id, LOWER(r.code) AS role_code
    FROM iam.roles r
    WHERE COALESCE(r.is_active, TRUE) = TRUE
), grants AS (
    SELECT r.id AS role_id, p.id AS permission_id
    FROM eligible_roles r
    JOIN role_permission_seed seed ON seed.role_code = r.role_code
    JOIN iam.permissions p ON p.permission_code = seed.permission_code
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT role_id, permission_id
FROM grants g
WHERE NOT EXISTS (
    SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = g.role_id AND rp.permission_id = g.permission_id
);

WITH privileged_codes(code) AS (
    VALUES ('tenant_owner'), ('tenant-owner'), ('owner'), ('admin'), ('administrator'), ('hr_admin'), ('hr-admin'), ('hr_manager'), ('human_resources_manager')
), new_permissions AS (
    SELECT id FROM iam.permissions
    WHERE permission_code IN (
        'hr.view', 'hr.attendance.approve', 'hr.performance.approve', 'hr.onboarding.complete',
        'hr.lifecycle.clearance.approve', 'hr.lifecycle.contract_renewal.approve', 'hr.lifecycle.exit.approve', 'hr.lifecycle.retirement.approve',
        'hr.workflow.escalations.manage', 'hr.privacy.view', 'hr.privacy.requests.manage', 'hr.privacy.requests.approve',
        'hr.privacy.retention.manage', 'hr.privacy.deletion.execute', 'hr.privacy.audit.view', 'hr.privacy.export', 'hr.payroll.audit.export'
    )
), grants AS (
    SELECT r.id AS role_id, p.id AS permission_id
    FROM iam.roles r
    CROSS JOIN new_permissions p
    WHERE COALESCE(r.is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(r.code, r.name, ''), ' ', '_')) IN (SELECT code FROM privileged_codes)
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT role_id, permission_id FROM grants g
WHERE NOT EXISTS (SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = g.role_id AND rp.permission_id = g.permission_id);

COMMIT;