ALTER TABLE redemption_orders
  ADD COLUMN cost_points INT NOT NULL DEFAULT 0 AFTER status,
  ADD COLUMN voucher_code VARCHAR(60) NULL UNIQUE AFTER cost_points,
  ADD COLUMN voucher_status VARCHAR(40) NOT NULL DEFAULT 'not_issued' AFTER voucher_code,
  ADD COLUMN reviewed_by VARCHAR(40) NULL AFTER created_at,
  ADD COLUMN reviewed_at VARCHAR(40) NULL AFTER reviewed_by,
  ADD COLUMN rejected_reason VARCHAR(255) NULL AFTER reviewed_at,
  ADD COLUMN expire_at VARCHAR(40) NULL AFTER rejected_reason,
  ADD COLUMN used_at VARCHAR(40) NULL AFTER expire_at,
  ADD COLUMN used_by VARCHAR(40) NULL AFTER used_at,
  ADD COLUMN verify_note VARCHAR(255) NULL AFTER used_by,
  ADD INDEX idx_redemption_status (status),
  ADD INDEX idx_redemption_voucher (voucher_code);

UPDATE redemption_orders ro
LEFT JOIN rewards r ON r.id = ro.item_id
SET ro.cost_points = COALESCE(NULLIF(ro.cost_points, 0), r.cost, 0),
    ro.voucher_status = CASE
      WHEN ro.status = 'approved' THEN 'issued'
      WHEN ro.status = 'rejected' THEN 'void'
      ELSE ro.voucher_status
    END;

UPDATE roles
SET permissions = JSON_ARRAY(
  'dashboard:view',
  'system:user:view',
  'system:role:view',
  'leads:view',
  'leads:advance',
  'points:view',
  'points:redemption:view',
  'points:redemption:review',
  'points:voucher:verify',
  'courses:view',
  'courses:add',
  'courses:edit',
  'statistics:view'
)
WHERE role = 'campus_admin';

UPDATE roles
SET permissions = JSON_ARRAY('dashboard:view', 'leads:view', 'leads:advance', 'courses:view', 'points:view', 'points:redemption:view')
WHERE role = 'advisor';

UPDATE roles
SET permissions = JSON_ARRAY(
  'dashboard:view',
  'points:view',
  'points:adjust',
  'points:reward',
  'points:redemption:view',
  'points:redemption:review',
  'points:voucher:verify',
  'statistics:view'
)
WHERE role = 'finance';