BEGIN;

ALTER TABLE IF EXISTS hr_payroll.statutory_contribution_records
    ADD COLUMN IF NOT EXISTS payroll_run_item_id bigint,
    ADD COLUMN IF NOT EXISTS rule_code varchar(50),
    ADD COLUMN IF NOT EXISTS rule_name varchar(150),
    ADD COLUMN IF NOT EXISTS period_start date,
    ADD COLUMN IF NOT EXISTS period_end date,
    ADD COLUMN IF NOT EXISTS calculation_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb;

CREATE INDEX IF NOT EXISTS idx_hr_statutory_contributions_run
    ON hr_payroll.statutory_contribution_records (tenant_id, payroll_run_id, employee_id);

CREATE INDEX IF NOT EXISTS idx_hr_statutory_rules_effective
    ON hr_payroll.statutory_deduction_rules (tenant_id, country_code, rule_type, effective_date, expires_at)
    WHERE COALESCE(is_deleted, FALSE) = FALSE;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT v.module_code, v.permission_code, v.permission_name, v.description
FROM (
    VALUES
        ('hr-payroll', 'hr.payroll.statutory_contributions.view', 'View Statutory Contributions', 'Allows viewing payroll statutory contribution evidence.'),
        ('hr-payroll', 'hr.payroll.statutory_contributions.manage', 'Manage Statutory Contributions', 'Allows regenerating payroll statutory contribution evidence.')
) AS v(module_code, permission_code, permission_name, description)
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions p WHERE p.permission_code = v.permission_code
);

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 IN (
    'hr.payroll.statutory_contributions.view',
    'hr.payroll.statutory_contributions.manage'
)
WHERE LOWER(COALESCE(r.code, '')) IN (
    'tenant_owner',
    'tenant-owner',
    'owner',
    'admin',
    'administrator',
    'hr_manager',
    'payroll_manager'
)
AND NOT EXISTS (
    SELECT 1
    FROM iam.role_permissions rp
    WHERE rp.role_id = r.id
      AND rp.permission_id = p.id
);

COMMIT;