
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_phone VARCHAR(20) NOT NULL,
  customer_name VARCHAR(120),
  platform ENUM('facebook','instagram','tiktok_organico','tiktok_ads','web') NOT NULL,
  window_type ENUM('24h','72h') NOT NULL,
  window_expires_at DATETIME NOT NULL,
  destino ENUM('local','provincia') NULL, -- 'local' = ciudad base del negocio (configurable), 'provincia' = el resto
  stage VARCHAR(60) NOT NULL DEFAULT 'inicio',
  status ENUM('activa','cerrada_venta','vencida_sin_cierre','recuperada','perdida') NOT NULL DEFAULT 'activa',
  needs_human BOOLEAN NOT NULL DEFAULT FALSE,
  human_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  direction ENUM('entrante','saliente') NOT NULL,
  body TEXT,
  message_type ENUM('texto','imagen','audio','video','documento') NOT NULL DEFAULT 'texto',
  detected_category VARCHAR(60) NULL, -- ej: objecion_precio, objecion_confianza, voucher, fuera_de_guion
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  product_name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  upsell_applied VARCHAR(150) NULL,
  destino ENUM('local','provincia') NOT NULL, -- 'local' = ciudad base del negocio, 'provincia' = el resto
  voucher_recibido BOOLEAN NOT NULL DEFAULT FALSE,
  voucher_confirmado_manual BOOLEAN NOT NULL DEFAULT FALSE,
  shalom_deadline DATETIME NULL,
  shalom_retirado BOOLEAN NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);

CREATE TABLE IF NOT EXISTS recovery_blocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category VARCHAR(60) NOT NULL, -- se_quedo_en_voucher, no_respondio, pregunto_precio, pidio_descuento
  conversation_id INT NOT NULL,
  sent BOOLEAN NOT NULL DEFAULT FALSE,
  sent_at DATETIME NULL,
  cost_estimate DECIMAL(6,2) NULL,
  result ENUM('pendiente','recuperada','sin_respuesta') NOT NULL DEFAULT 'pendiente',
  FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);
