-- Tabla de contactos con thread del Assistant
CREATE TABLE IF NOT EXISTS whatsapp_contacts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(20) UNIQUE,
  assistant_thread_id VARCHAR(100),
  nombre VARCHAR(100),
  estado ENUM('activo', 'bloqueado') DEFAULT 'activo',
  creado DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de análisis de leads con clasificación por IA
CREATE TABLE IF NOT EXISTS lead_analysis (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT,
  analysis_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  interest_level VARCHAR(100),
  stage VARCHAR(100),
  summary TEXT,
  raw_prompt TEXT,
  raw_response TEXT
);

-- Agrega columnas a la tabla 'message' si no existen
ALTER TABLE message
  ADD COLUMN IF NOT EXISTS response_status ENUM('pendiente', 'procesando', 'completado', 'fallado') DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS analyzed_for_interest BOOLEAN DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS assistant_response TEXT,
  ADD COLUMN IF NOT EXISTS assistant_thread_id VARCHAR(100);

-- Índices para optimizar los queries de los workers
CREATE INDEX IF NOT EXISTS idx_response_status ON message(response_status, direction, channel, created_at);
CREATE INDEX IF NOT EXISTS idx_analyzed_interest ON message(analyzed_for_interest, direction, channel, created_at);
