BEGIN;

ALTER TABLE IF EXISTS hr_payroll.recruitment_offers
    ADD COLUMN IF NOT EXISTS readiness_override_used boolean NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS readiness_override_reason text,
    ADD COLUMN IF NOT EXISTS readiness_override_by uuid,
    ADD COLUMN IF NOT EXISTS readiness_override_at timestamp,
    ADD COLUMN IF NOT EXISTS readiness_blockers_snapshot text,
    ADD COLUMN IF NOT EXISTS workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb;

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
SELECT 'hr-payroll', 'hr.recruitment.offer.override_readiness', 'Override Offer Readiness Checks', 'Override recruitment offer readiness blockers with a required audit reason.'
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions WHERE permission_code = 'hr.recruitment.offer.override_readiness'
);

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

COMMIT;