-- Unified Tickets update for CSR / Team Leader / Manager workflow
-- Safe to run after importing your current database. Run once in phpMyAdmin.

ALTER TABLE service_requests
  ADD COLUMN IF NOT EXISTS ticket_source VARCHAR(80) NULL AFTER request_number,
  ADD COLUMN IF NOT EXISTS manual_ticket_number VARCHAR(80) NULL AFTER ticket_source,
  ADD COLUMN IF NOT EXISTS secondary_phone VARCHAR(30) NULL AFTER phone_number,
  ADD COLUMN IF NOT EXISTS customer_email VARCHAR(150) NULL AFTER secondary_phone,
  ADD COLUMN IF NOT EXISTS product_model_version VARCHAR(120) NULL AFTER serial_number,
  ADD COLUMN IF NOT EXISTS purchase_date DATE NULL AFTER special_request,
  ADD COLUMN IF NOT EXISTS warranty_type VARCHAR(80) NULL AFTER purchase_date,
  ADD COLUMN IF NOT EXISTS call_received_date DATE NULL AFTER warranty_type,
  ADD COLUMN IF NOT EXISTS is_fake_ticket TINYINT(1) NOT NULL DEFAULT 0 AFTER call_received_date,
  ADD COLUMN IF NOT EXISTS created_by_csr_id INT NULL AFTER is_fake_ticket,
  ADD COLUMN IF NOT EXISTS follow_up_reminder VARCHAR(255) NULL AFTER completed_date,
  ADD COLUMN IF NOT EXISTS follow_up_due_at DATETIME NULL AFTER follow_up_reminder,
  ADD COLUMN IF NOT EXISTS in_progress_started_at DATETIME NULL AFTER follow_up_due_at,
  ADD COLUMN IF NOT EXISTS last_updated_at DATETIME NULL AFTER in_progress_started_at;

UPDATE service_requests
SET ticket_source = CASE
  WHEN ticket_source = 'customer' OR ticket_source IS NULL OR ticket_source = '' THEN 'cx_online'
  WHEN ticket_source IN ('cx_online','csr_manual','chat_followup') THEN ticket_source
  ELSE ticket_source
END;

UPDATE service_requests
SET ticket_source = 'csr_manual'
WHERE created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL;

UPDATE service_requests
SET last_updated_at = COALESCE(last_updated_at, requested_at);

CREATE TABLE IF NOT EXISTS request_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  user_id INT NULL,
  user_role VARCHAR(50) NOT NULL,
  actor_name VARCHAR(150) NULL,
  actor_email VARCHAR(150) NULL,
  actor_role_label VARCHAR(80) NULL,
  action VARCHAR(100) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  notes TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_request_logs_request (request_id),
  INDEX idx_request_logs_user (user_id, user_role),
  INDEX idx_request_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE request_notes
  MODIFY COLUMN note_type ENUM('csr_note','internal','customer') NOT NULL DEFAULT 'internal';
