-- CSR Verification Queue update
-- Run only if your hosting DB user cannot auto-add columns from the PHP helper.

ALTER TABLE service_requests
  ADD COLUMN IF NOT EXISTS verification_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved' AFTER last_updated_at,
  ADD COLUMN IF NOT EXISTS verification_reviewed_by INT NULL AFTER verification_status,
  ADD COLUMN IF NOT EXISTS verification_reviewed_at DATETIME NULL AFTER verification_reviewed_by,
  ADD COLUMN IF NOT EXISTS verification_reject_reason VARCHAR(150) NULL AFTER verification_reviewed_at,
  ADD COLUMN IF NOT EXISTS verification_notes TEXT NULL AFTER verification_reject_reason;

-- Old records and non-CX records are treated as already approved.
UPDATE service_requests
SET verification_status = 'approved'
WHERE verification_status IS NULL OR verification_status = '';


-- Approved customer requests are visible in CSR/TL/Manager Ticket Table.
-- Pending/rejected requests remain in Verification Queue only.
UPDATE service_requests
SET verification_status = 'approved'
WHERE verification_status IS NULL OR verification_status = '';
