para migracion de clientes:
insert into crm.clientes
SELECT
    NULL,
    c.idcliente,
    c.nombre,
    c.apellidos,
    c.rfc,
    c.clave_sat,
    '' AS archivo_opinion,
    '' as archivo_cedula_fiscal,
    '' AS archivo_cer,
    '' AS archivo_key,
    c.password_sat AS clave_key,
    '' AS asesor,
    'Activo',
    CASE c.status
        WHEN 1 THEN 'Sin status'
        WHEN 2 THEN 'Posible pago'
        WHEN 3 THEN 'Pendiente'
        WHEN 4 THEN 'Pre cierre'            -- Mapeo a 'Pre cierre' (con espacio)
        WHEN 5 THEN 'Autoriza cierre'
        WHEN 6 THEN 'Conta terminada'
        WHEN 7 THEN 'Anuales'
        WHEN 8 THEN 'Iniciar contabilidad'
        WHEN 9 THEN 'Deudores'
        WHEN 10 THEN 'Sellos'
        WHEN 11 THEN 'Sin Contraseña'        -- Mapeo a 'Sin Contraseña' (con mayúscula)
        WHEN 12 THEN 'Sin Atender'
        ELSE 'Sin status'                    -- Valor por defecto si el número no coincide
    END AS estatus_contable, now(), now()
FROM 
    altosco1_lavisegu_altoscontables.clientes as c;


********************** posterior actualizar el asesor
UPDATE
    crm.clientes AS cc
INNER JOIN
    altosco1_lavisegu_altoscontables.clientes AS c
    ON cc.celular = c.idcliente  -- Asumiendo que esta es la clave de unión correcta (del SELECT anterior)
SET
    cc.asesor = CASE c.asesor
                    WHEN '3318582873' THEN '2'
                    WHEN '332931885' THEN '2'
                    WHEN '3481143947' THEN '3'
                    WHEN '3481244299' THEN '4'
                    ELSE '2'
                END
WHERE
    c.asesor != '';




**************************para comentarios:
insert into crm.cliente_comentarios
SELECT null, c.comentarios, case c.usuariocomenta when 'Francisco Arellano' then 2 when '3481143947' then 3 when '3481244299' then 4 else '2' end as 'id_usuario', cc.id as 'id_cliente', c.fecha,now(), now(), 'Activo' FROM altosco1_lavisegu_altoscontables.comentarios as c inner join crm.clientes as cc on cc.celular=c.idcliente;


-- ============================================
-- SCRIPT DE MIGRACIÓN WHATSAPP
-- De: altosco1_lavisegu_altoscontables.whatsApp
-- A: whatsapp_mensajes + whatsapp_metadata
-- ============================================

-- Paso 1: Insertar en tabla principal (whatsapp_mensajes)
INSERT INTO whatsapp_mensajes (idwa, body, fromMe, idcliente, fecha, visto)
SELECT 
    idwa,
    IFNULL(body, '') as body,
    CASE 
        WHEN fromMe = '1' OR fromMe = 'true' THEN 1 
        ELSE 0 
    END as fromMe,
     b.id idcliente,
    fecha as fecha,
    visto
FROM altosco1_lavisegu_altoscontables.whatsApp as a
inner join crm.clientes as b on b.celular=a.idcliente;
ORDER BY idwa;

-- Verificar inserción en mensajes
SELECT 
    COUNT(*) as total_mensajes,
    MIN(fecha) as primer_mensaje,
    MAX(fecha) as ultimo_mensaje,
    COUNT(DISTINCT idcliente) as total_clientes
FROM whatsapp_mensajes;

-- Paso 2: Insertar en tabla metadata (whatsapp_metadata)
INSERT INTO whatsapp_metadata (
    idwa, id, senderName, author, chatId, messageNumber, 
    chatName, ack, type, media, isForwarded, isMentioned, 
    quotedMsg, mentionedIds, fecha
)
SELECT 
    idwa,
    IFNULL(id, '') as id,
    IFNULL(senderName, '') as senderName,
    IFNULL(author, '') as author,
    IFNULL(chatId, '') as chatId,
    IFNULL(messageNumber, 0) as messageNumber,
    IFNULL(chatName, '') as chatName,
    '' as ack,
    'chat' as type,
    '' as media,
    0 as isForwarded,
    0 as isMentioned,
    '{}' as quotedMsg,
    '[]' as mentionedIds,
    fecha
FROM altosco1_lavisegu_altoscontables.whatsApp as a
inner join crm.clientes as b on b.celular=a.idcliente;
ORDER BY idwa;

-- Verificar inserción en metadata
SELECT 
    COUNT(*) as total_metadata,
    COUNT(DISTINCT chatId) as chats_unicos
FROM whatsapp_metadata;

-- Paso 3: Verificar integridad de datos
-- Comparar conteos
SELECT 
    'Tabla Original' as tabla, COUNT(*) as registros 
FROM altosco1_lavisegu_altoscontables.whatsApp
UNION ALL
SELECT 
    'whatsapp_mensajes' as tabla, COUNT(*) as registros 
FROM whatsapp_mensajes
UNION ALL
SELECT 
    'whatsapp_metadata' as tabla, COUNT(*) as registros 
FROM whatsapp_metadata;

-- Verificar que idwa coincidan
SELECT 'Mensajes sin metadata' as problema, COUNT(*) as cantidad
FROM whatsapp_mensajes m
LEFT JOIN whatsapp_metadata meta ON m.idwa = meta.idwa
WHERE meta.idwa IS NULL

UNION ALL

SELECT 'Metadata sin mensaje' as problema, COUNT(*) as cantidad
FROM whatsapp_metadata meta
LEFT JOIN whatsapp_mensajes m ON meta.idwa = m.idwa
WHERE m.idwa IS NULL;

-- Paso 4: Ver distribución por particiones
SELECT 
    PARTITION_NAME, 
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) as 'Size_MB'
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('whatsapp_mensajes', 'whatsapp_metadata')
ORDER BY TABLE_NAME, PARTITION_NAME;

-- Paso 5: Verificar algunos registros de prueba
SELECT 
    m.idwa,
    m.body,
    m.fromMe,
    m.idcliente,
    m.fecha,
    m.visto,
    meta.senderName,
    meta.chatId
FROM whatsapp_mensajes m
INNER JOIN whatsapp_metadata meta ON m.idwa = meta.idwa
LIMIT 10;


insert into crm.whatsapp_mensajes_media
SELECT null, w.idwa, m.url, m.local, m.tipo,now(),now() FROM media as m inner join whatsApp as w on w.id=m.idmensaje
where m.url !=''

insert into crm.msat
SELECT a.idcontabilidad, a.ingreso, a.gastos, a.isr, a.iva, a.observaciones, a.mes, a.anio, a.status, b.id, a.nomes, a.fecha, a.referenciado, a.diot, now() 
FROM altosco1_lavisegu_altoscontables.contabilidad as a inner join crm.clientes as b on b.celular=a.idcliente;



insert into crm.cliente_pagoscontables
SELECT a.idpagoc, a.fecha, a.cantidad, a.comentario, b.id, a.vouch, 'Activo', now(), now() FROM altosco1_lavisegu_altoscontables.pagoscontables as a inner join crm.clientes as b on b.celular=a.idcliente;

insert into crm.textos_predeterminados
SELECT null, texto, imagen, 'Activo',null,null FROM altosco1_lavisegu_altoscontables.textopredeterminado
where status=1;

insert into crm.textos_predeterminados
SELECT null, texto, imagen, 'Inactivo',null,null FROM altosco1_lavisegu_altoscontables.textopredeterminado
where status=0;


insert into crm.datos_contabilidades
SELECT null, b.id, a.datos, a.anio, a.mes, now(), now() FROM altosco1_lavisegu_altoscontables.datos_contabilidades as a inner join crm.clientes as b on b.celular=a.idcliente;