-- =============================================================================
-- 05 — WHATSAPP: conversaciones, slots de selección, templates config
-- (Tablas heredadas del kit-agentes-whatsapp adaptadas)
-- =============================================================================

-- Historial de conversación que el agente carga cada vez
CREATE TABLE IF NOT EXISTS whatsapp_conversaciones (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    from_number VARCHAR(25) NOT NULL,
    role        ENUM('user','assistant') NOT NULL,
    content     MEDIUMTEXT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_from_created (from_number, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Slots de selección (para mapear "Opción 1/2/3" al producto/combo real)
CREATE TABLE IF NOT EXISTS whatsapp_seleccion_pending (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    from_number     VARCHAR(25) NOT NULL,
    slot            VARCHAR(8)  NOT NULL,   -- 'op1', 'op2', 'op3'
    tipo_item       ENUM('producto','combo','direccion','zona') DEFAULT 'producto',
    id_referencia   INT NOT NULL,           -- id del producto / combo / dirección
    id_tamanio      INT NULL,
    payload_json    JSON,                   -- info extra (precio snapshot, extras, etc.)
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_from_created (from_number, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Config de templates (SIDs de Twilio por friendly_name)
CREATE TABLE IF NOT EXISTS whatsapp_config (
    clave VARCHAR(64) PRIMARY KEY,
    valor TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Carrito en curso (cliente está armando el pedido pero todavía no confirmó)
CREATE TABLE IF NOT EXISTS whatsapp_carrito_pendiente (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    from_number      VARCHAR(25) NOT NULL,
    items_json       JSON NOT NULL,        -- [{id_producto, id_tamanio, cant, extras, nota}]
    id_tipo_entrega  INT NULL,
    id_direccion     INT NULL,
    id_forma_pago    INT NULL,
    cupon_codigo     VARCHAR(40) NULL,
    nota_cliente     TEXT NULL,
    subtotal_calc    DECIMAL(10,2),
    envio_calc       DECIMAL(8,2),
    total_calc       DECIMAL(10,2),
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_from (from_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Log de mensajes outbound (debug + métricas)
CREATE TABLE IF NOT EXISTS twilio_mensajes_log (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    to_number       VARCHAR(25),
    from_number     VARCHAR(25),
    canal           VARCHAR(20) DEFAULT 'whatsapp',
    body            TEXT,
    template_sid    VARCHAR(40) NULL,
    twilio_sid      VARCHAR(40),
    status          VARCHAR(20),      -- queued/sent/delivered/read/failed
    error_code      VARCHAR(20),
    error_message   TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at),
    INDEX idx_to (to_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
