-- INTERLINK WhatsApp CRM v2.1 - esquema paralelo seguro
-- Ejecutar sobre base interlink_crm. No modifica tablas viejas whatsapp_*.

CREATE TABLE IF NOT EXISTS wa2_conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  normalized_phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  customer_validated TINYINT(1) NOT NULL DEFAULT 0,
  current_intent VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  current_state VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  context_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  last_inbound_at DATETIME DEFAULT NULL,
  last_outbound_at DATETIME DEFAULT NULL,
  status VARCHAR(24) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wa2_phone (normalized_phone),
  KEY idx_wa2_customer (customer_id),
  KEY idx_wa2_status (status),
  KEY idx_wa2_last_inbound (last_inbound_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa2_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED DEFAULT NULL,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  normalized_phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  direction ENUM('in','out') COLLATE utf8mb4_unicode_ci NOT NULL,
  message_type VARCHAR(24) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text',
  body MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  wa_message_id VARCHAR(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  media_id VARCHAR(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  local_file VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  meta_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wa2_msg_phone (normalized_phone, created_at),
  KEY idx_wa2_msg_conv (conversation_id, created_at),
  KEY idx_wa2_wa_id (wa_message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa2_cases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED DEFAULT NULL,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  normalized_phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  case_type VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  status VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  title VARCHAR(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  person_name VARCHAR(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  locality VARCHAR(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  neighborhood VARCHAR(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  address VARCHAR(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  google_maps_url TEXT COLLATE utf8mb4_unicode_ci,
  notes MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  payload_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  closed_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_wa2_cases_phone (normalized_phone, status),
  KEY idx_wa2_cases_type (case_type, status),
  KEY idx_wa2_cases_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa2_payment_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED DEFAULT NULL,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  normalized_phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  customer_id INT UNSIGNED DEFAULT NULL,
  suggested_customer_id INT UNSIGNED DEFAULT NULL,
  status VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending_review',
  media_type VARCHAR(24) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  media_id VARCHAR(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  local_file VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  amount_detected DECIMAL(18,2) DEFAULT NULL,
  paid_at_detected DATETIME DEFAULT NULL,
  reference_detected VARCHAR(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  confidence DECIMAL(5,2) DEFAULT NULL,
  analysis_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_wa2_receipts_phone (normalized_phone, status),
  KEY idx_wa2_receipts_customer (customer_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa2_knowledge (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  visibility ENUM('public','validated_customer','internal') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'public',
  title VARCHAR(160) COLLATE utf8mb4_unicode_ci NOT NULL,
  content MEDIUMTEXT COLLATE utf8mb4_unicode_ci NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_wa2_knowledge (active, visibility, category, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa2_ai_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED DEFAULT NULL,
  phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  message MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  intent VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  confidence DECIMAL(5,2) DEFAULT NULL,
  handled TINYINT(1) NOT NULL DEFAULT 0,
  source VARCHAR(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  reply MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  decision_json MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wa2_ai_phone (phone, created_at),
  KEY idx_wa2_ai_intent (intent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO wa2_knowledge (category, visibility, title, content, sort_order)
SELECT 'cobertura', 'public', 'Zonas de cobertura habitual', 'Trabajamos habitualmente en Apolinario Saravia, Luis Burela, Mollinedo, zona de Las Palmas, El Bordo y Las Flacas. La disponibilidad exacta se confirma según domicilio o ubicación.', 10
WHERE NOT EXISTS (SELECT 1 FROM wa2_knowledge WHERE category='cobertura' AND title='Zonas de cobertura habitual');

INSERT INTO wa2_knowledge (category, visibility, title, content, sort_order)
SELECT 'comercial', 'public', 'Regla comercial inicial', 'Para una primera consulta comercial no pedir demasiados datos. Se puede atender con nombre completo y localidad/barrio. Domicilio exacto o ubicación de Google Maps son opcionales para avanzar con cobertura.', 20
WHERE NOT EXISTS (SELECT 1 FROM wa2_knowledge WHERE category='comercial' AND title='Regla comercial inicial');
