
-- Borra tabla si existe

-- 1. Genera las últimas 4 tablas historial
SET @ultimos_historiales = (
  SELECT GROUP_CONCAT(CONCAT('`', table_name, '`') ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC SEPARATOR ',')
  FROM (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name LIKE 'chatbot_log_pasos_historial%'
    ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC
    LIMIT 4
  ) AS ultimas
);

-- 2. Une tablas principales + últimos historiales
SET @tablas_union_log_total = CONCAT('`chatbot_log_pasos`, `chatbot_log_pasos_andap`, `chatbot_log_pasos_didi`, ', @ultimos_historiales);

-- 3. Construye el CREATE TABLE
SET @query_log_total = CONCAT('CREATE TABLE `chatbot_log_pasos_total` (
  `id_log` int(11) NOT NULL AUTO_INCREMENT,
  `id_chatbot` int(11) NOT NULL,
  `sesion` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `rsocial` enum(\'\',\'whatsapp\',\'web\',\'telegram\',\'facebook\',\'instagram\') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `conversacion` text NOT NULL,
  `proceso` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `quien_ejecuta_proceso` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `fecha_sistema` datetime NOT NULL,
  PRIMARY KEY (`id_log`),
  KEY `proceso` (`proceso`),
  KEY `id_log` (`id_log`),
  KEY `quien_ejecuta_proceso` (`quien_ejecuta_proceso`),
  KEY `sesion` (`sesion`),
  KEY `rsocial` (`rsocial`),
  KEY `id_chatbot` (`id_chatbot`,`sesion`,`proceso`,`quien_ejecuta_proceso`,`fecha_sistema`) USING BTREE,
  KEY `idx_optimo` (`id_chatbot`,`sesion`,`proceso`,`id_log`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci UNION=(', @tablas_union_log_total, ');');

-- 4. Ejecuta
PREPARE stmt2 FROM @query_log_total;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;


-- *******************************
-- Segunda tabla chatbot_log_pasos_solo_historial
-- *******************************


-- 1. Genera las últimas 4 tablas historial
SET @ultimos_historiales = (
  SELECT GROUP_CONCAT(CONCAT('`', table_name, '`') ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC SEPARATOR ',')
  FROM (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name LIKE 'chatbot_log_pasos_historial%'
    ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC
    LIMIT 10
  ) AS ultimas
);

-- 2. Une tablas principales + últimos historiales
SET @tablas_union_log_total = CONCAT(' `chatbot_log_pasos_andap`, `chatbot_log_pasos_didi`, ', @ultimos_historiales);

-- 3. Construye el CREATE TABLE
SET @query_log_total = CONCAT('CREATE TABLE `chatbot_log_pasos_solo_historial` (
  `id_log` int(11) NOT NULL AUTO_INCREMENT,
  `id_chatbot` int(11) NOT NULL,
  `sesion` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `rsocial` enum(\'\',\'whatsapp\',\'web\',\'telegram\',\'facebook\',\'instagram\') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `conversacion` text NOT NULL,
  `proceso` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `quien_ejecuta_proceso` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `fecha_sistema` datetime NOT NULL,
  PRIMARY KEY (`id_log`),
  KEY `proceso` (`proceso`),
  KEY `id_log` (`id_log`),
  KEY `quien_ejecuta_proceso` (`quien_ejecuta_proceso`),
  KEY `sesion` (`sesion`),
  KEY `rsocial` (`rsocial`),
  KEY `id_chatbot` (`id_chatbot`,`sesion`,`proceso`,`quien_ejecuta_proceso`,`fecha_sistema`) USING BTREE,
  KEY `idx_optimo` (`id_chatbot`,`sesion`,`proceso`,`id_log`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci UNION=(', @tablas_union_log_total, ');');

-- 4. Ejecuta
PREPARE stmt2 FROM @query_log_total;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;



-- *******************************
--  tabla bot_captura_datos_solo_historial
-- *******************************

-- Obtener las últimas 4 tablas 'bot_captura_datos_historial%'
SET @tablas_union_historial = (
  SELECT GROUP_CONCAT(CONCAT('`', table_name, '`') ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC SEPARATOR ',')
  FROM (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name LIKE 'bot_captura_datos_historial%'
    ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC
    LIMIT 10
  ) AS ultimas
);

-- Crear tabla con las últimas 4
SET @query_historial = CONCAT('CREATE TABLE `bot_captura_datos_solo_historial` (
  `id_captura` int(11) NOT NULL AUTO_INCREMENT,
  `id_bot` int(11) NOT NULL,
  `bot_set_variable_id` int(11) NOT NULL,
  `sesion` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `info_entregada` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `info_capturada` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `pivote` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `fecha_sistema` datetime NOT NULL,
  PRIMARY KEY (`id_captura`),
  KEY `id_bot` (`id_bot`),
  KEY `bot_set_variable_id` (`bot_set_variable_id`),
  KEY `sesion` (`sesion`),
  KEY `pivote` (`pivote`),
  KEY `fecha_sistema` (`fecha_sistema`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci UNION=(', @tablas_union_historial, ');');

-- Ejecutar tabla
PREPARE stmt_historial FROM @query_historial;
EXECUTE stmt_historial;
DEALLOCATE PREPARE stmt_historial;




-- *******************************
--  tabla bot_captura_datos_virtual
-- *******************************

-- Obtener las últimas 4 tablas 'bot_captura_datos_historial%'
SET @ultimos_historiales = (
  SELECT GROUP_CONCAT(CONCAT('`', table_name, '`') ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC SEPARATOR ',')
  FROM (
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
      AND table_name LIKE 'bot_captura_datos_historial%'
    ORDER BY CAST(SUBSTRING_INDEX(table_name, 'historial', -1) AS UNSIGNED) DESC
    LIMIT 4
  ) AS ultimas
);

-- Agregar la tabla bot_captura_datos al principio del UNION
SET @tablas_union_virtual = CONCAT('`bot_captura_datos`,', @ultimos_historiales);

-- Crear la tabla virtual con las últimas 4 historiales + principal
SET @query_virtual = CONCAT('CREATE TABLE `bot_captura_datos_virtual` (
  `id_captura` int(11) NOT NULL AUTO_INCREMENT,
  `id_bot` int(11) NOT NULL,
  `bot_set_variable_id` int(11) NOT NULL,
  `sesion` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `info_entregada` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `info_capturada` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `pivote` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `fecha_sistema` datetime NOT NULL,
  PRIMARY KEY (`id_captura`),
  KEY `id_bot` (`id_bot`),
  KEY `bot_set_variable_id` (`bot_set_variable_id`),
  KEY `sesion` (`sesion`),
  KEY `pivote` (`pivote`),
  KEY `fecha_sistema` (`fecha_sistema`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci UNION=(', @tablas_union_virtual, ');');

-- Ejecutar creación
PREPARE stmt_virtual FROM @query_virtual;
EXECUTE stmt_virtual;
DEALLOCATE PREPARE stmt_virtual;

