
-- INTERLINK WhatsApp v2.11 - Derivación humana + settings + menú

ALTER TABLE wa2_conversations
  ADD COLUMN IF NOT EXISTS ai_paused TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS assigned_to VARCHAR(80) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS operator_note TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS last_read_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS priority VARCHAR(16) NOT NULL DEFAULT 'normal',
  ADD COLUMN IF NOT EXISTS human_required TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS human_reason VARCHAR(160) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS human_requested_at DATETIME DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS human_notified_at DATETIME DEFAULT NULL;

CREATE TABLE IF NOT EXISTS wa2_handoff_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(32) NOT NULL,
  customer_id BIGINT UNSIGNED DEFAULT NULL,
  priority VARCHAR(16) NOT NULL DEFAULT 'normal',
  reason VARCHAR(160) DEFAULT NULL,
  summary TEXT DEFAULT NULL,
  internal_to VARCHAR(32) DEFAULT NULL,
  internal_message_id VARCHAR(128) DEFAULT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'pending',
  error_text TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notified_at DATETIME DEFAULT NULL,
  resolved_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_handoff_conversation (conversation_id),
  KEY idx_handoff_status (status),
  KEY idx_handoff_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE wa2_settings
  ADD COLUMN IF NOT EXISTS support_human_phone VARCHAR(32) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS support_handoff_enabled TINYINT(1) NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS support_handoff_ack_message TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS support_handoff_alert_template TEXT DEFAULT NULL;

UPDATE wa2_settings
SET
  support_human_phone =
    CASE
      WHEN support_human_phone IS NULL OR support_human_phone = ''
      THEN _utf8mb4'5493876834044' COLLATE utf8mb4_unicode_ci
      ELSE support_human_phone
    END,

  support_handoff_enabled =
    CASE
      WHEN support_handoff_enabled IS NULL
      THEN 1
      ELSE support_handoff_enabled
    END,

  support_handoff_ack_message =
    CASE
      WHEN support_handoff_ack_message IS NULL OR support_handoff_ack_message = ''
      THEN _utf8mb4'Entiendo. Voy a derivar tu consulta a un asistente para que revise tu caso personalmente. Te van a responder por este mismo WhatsApp.' COLLATE utf8mb4_unicode_ci
      ELSE support_handoff_ack_message
    END,

  support_handoff_alert_template =
    CASE
      WHEN support_handoff_alert_template IS NULL OR support_handoff_alert_template = ''
      THEN _utf8mb4'🔴 INTERLINK - Derivación humana

Prioridad: {priority}
Motivo: {reason}
Cliente: {customer_name}
Tel: {phone}
Último mensaje: {last_message}

Abrir CRM:
{crm_url}' COLLATE utf8mb4_unicode_ci
      ELSE support_handoff_alert_template
    END
WHERE id=1;
