-- WhatsApp CRM v3 - Corrección de número reasignado / sesión vieja
-- Ejecutar sobre la base del CRM. No borra mensajes.

ALTER TABLE whatsapp_crm_conversations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE whatsapp_crm_action_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE whatsapp_messages_api CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE whatsapp_bot_sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Limpia vínculos manuales viejos cuando el cliente vinculado ya no tiene ese WhatsApp en customers.phone.
-- Esto evita mostrar saldo/facturas de un cliente anterior que antes poseía el número.
UPDATE whatsapp_crm_conversations wc
LEFT JOIN customers c ON c.id = wc.customer_id
SET wc.customer_id = NULL,
    wc.notes = CONCAT(COALESCE(wc.notes,''), '\n', NOW(), ' vinculo_limpiado_por_sql_telefono_reasignado'),
    wc.updated_at = NOW()
WHERE wc.customer_id IS NOT NULL
  AND (
      c.id IS NULL
      OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(c.phone,''), '+', ''), ' ', ''), '-', ''), '.', ''), 10)
         <> RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(wc.phone,''), '+', ''), ' ', ''), '-', ''), '.', ''), 10)
  );

-- Limpia sesiones automáticas viejas con selected_customer_id/customer_id que ya no coinciden con ese WhatsApp.
UPDATE whatsapp_bot_sessions s
LEFT JOIN customers c ON c.id = COALESCE(s.selected_customer_id, s.customer_id)
SET s.customer_id = NULL,
    s.selected_customer_id = NULL,
    s.current_flow = NULL,
    s.current_step = NULL,
    s.context_json = NULL,
    s.human_required = 0,
    s.last_message = 'sesion_limpiada_por_sql_telefono_reasignado',
    s.updated_at = NOW()
WHERE COALESCE(s.selected_customer_id, s.customer_id) IS NOT NULL
  AND (
      c.id IS NULL
      OR RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(c.phone,''), '+', ''), ' ', ''), '-', ''), '.', ''), 10)
         <> RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(s.phone,''), '+', ''), ' ', ''), '-', ''), '.', ''), 10)
  );
