USE cashless_db;

START TRANSACTION;

UPDATE users u
JOIN roles r ON r.id=u.role_id
SET u.status='disabled'
WHERE r.name IN ('cashier','staff');

UPDATE roles
SET description='Obsolete role; associated accounts are disabled and cannot sign in'
WHERE name IN ('cashier','staff');

UPDATE payments
SET status='pending_verification'
WHERE method='gcash'
  AND status='pending'
  AND receipt_path IS NOT NULL
  AND receipt_path<>'';

ALTER TABLE order_status_history
  MODIFY actor_type ENUM('system','staff','admin','customer') NOT NULL DEFAULT 'system';

DELETE FROM roles
WHERE name IN ('cashier','staff')
  AND NOT EXISTS (SELECT 1 FROM users WHERE users.role_id=roles.id);

COMMIT;