
-- 2026-06-12 CSR/ER/WebRTC/API update
-- Purpose:
-- 1) Distinguish tickets created in PHP/CSR/CX from TypeScript/ER tickets.
-- 2) Keep CSR ticket tab as a view-only table with Message/Call only for app-linked tickets.
-- 3) Add WebRTC call-room support on top of the existing call_requests queue.
-- 4) Add API client token storage for ER/TypeScript REST API access.

ALTER TABLE service_requests
  ADD COLUMN IF NOT EXISTS source_system VARCHAR(40) NULL AFTER ticket_source,
  ADD COLUMN IF NOT EXISTS origin_type VARCHAR(80) NULL AFTER source_system,
  ADD COLUMN IF NOT EXISTS er_ticket_id VARCHAR(120) NULL AFTER origin_type,
  ADD COLUMN IF NOT EXISTS sync_status ENUM('local_only','pending_er_sync','synced_to_er','er_imported','sync_failed') NOT NULL DEFAULT 'local_only' AFTER er_ticket_id,
  ADD COLUMN IF NOT EXISTS sync_error TEXT NULL AFTER sync_status,
  ADD COLUMN IF NOT EXISTS last_synced_at DATETIME NULL AFTER sync_error;

UPDATE service_requests
SET
  source_system = CASE
    WHEN ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'typescript_er'
    WHEN ticket_source = 'csr_manual' OR created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL THEN 'php_csr'
    ELSE 'php_cx'
  END,
  origin_type = CASE
    WHEN ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'ER Ticket'
    WHEN ticket_source = 'csr_manual' OR created_by_csr_id IS NOT NULL OR manual_ticket_number IS NOT NULL THEN 'Manual Ticket'
    WHEN ticket_source IN ('cx_online','customer','') OR ticket_source IS NULL THEN 'Customer App'
    ELSE REPLACE(ticket_source, '_', ' ')
  END,
  sync_status = CASE
    WHEN ticket_source IN ('er_typescript','er_imported','typescript_er') THEN 'er_imported'
    WHEN sync_status IS NULL OR sync_status = '' THEN 'local_only'
    ELSE sync_status
  END
WHERE source_system IS NULL OR source_system = '' OR origin_type IS NULL OR origin_type = '';

ALTER TABLE call_requests
  ADD COLUMN IF NOT EXISTS room_token VARCHAR(128) NULL AFTER provider_call_sid,
  ADD COLUMN IF NOT EXISTS call_mode ENUM('external_phone','webrtc') NOT NULL DEFAULT 'external_phone' AFTER room_token,
  ADD COLUMN IF NOT EXISTS customer_email VARCHAR(150) NULL AFTER customer_name,
  ADD COLUMN IF NOT EXISTS call_reason VARCHAR(255) NULL AFTER notes;

CREATE UNIQUE INDEX IF NOT EXISTS idx_call_requests_room_token ON call_requests(room_token);
CREATE INDEX IF NOT EXISTS idx_call_requests_mode_status ON call_requests(call_mode, status);

CREATE TABLE IF NOT EXISTS call_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  call_request_id INT NULL,
  request_id INT NULL,
  customer_id INT NULL,
  customer_email VARCHAR(150) NULL,
  csr_id INT NULL,
  result ENUM('connected','missed','cancelled','failed','ended') NOT NULL DEFAULT 'ended',
  duration_seconds INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_call_logs_call_request_id (call_request_id),
  INDEX idx_call_logs_request_id (request_id),
  INDEX idx_call_logs_customer_id (customer_id),
  INDEX idx_call_logs_csr_id (csr_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_name VARCHAR(120) NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  permissions JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_api_clients_name (client_name),
  INDEX idx_api_clients_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create your first API token using api/v1/create_client_token.php, then delete that setup file from production.
