-- INTERLINK WhatsApp v2.8
-- Verificación de solo lectura. No muestra API keys, tokens, alias, CBU/CVU ni CUIT.

USE `interlink_crm`;

SELECT DATABASE() AS base_actual, VERSION() AS version_mariadb;

SELECT
  `migration_key`,
  `description`,
  `applied_at`
FROM `wa2_schema_migrations`
WHERE `migration_key` = 'wa2_v28_conversation_memory_security';

SELECT
  `id`,
  `ai_enabled`,
  `ai_mode`,
  `ai_model`,
  `operational_ai_refine_enabled`,
  `receipt_guard_enabled`,
  `receipt_guard_min_confidence`,
  CASE
    WHEN COALESCE(`api_key_enc`, '') = '' THEN 'sin_clave_en_base'
    WHEN `api_key_enc` LIKE 'v2:gcm:%' THEN 'cifrada_aes_gcm'
    ELSE 'legacy_base64_rotar'
  END AS estado_clave_openai
FROM `wa2_settings`
WHERE `id` = 1;

SELECT
  COUNT(*) AS decisiones_totales,
  SUM(CASE WHEN `intent` = 'unknown' THEN 1 ELSE 0 END) AS decisiones_unknown,
  ROUND(
    100 * SUM(CASE WHEN `intent` = 'unknown' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0),
    2
  ) AS porcentaje_unknown
FROM `wa2_ai_decisions`;

SELECT
  COUNT(*) AS decisiones_ultimos_7_dias,
  SUM(CASE WHEN `intent` = 'unknown' THEN 1 ELSE 0 END) AS unknown_ultimos_7_dias,
  ROUND(
    100 * SUM(CASE WHEN `intent` = 'unknown' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0),
    2
  ) AS porcentaje_unknown_ultimos_7_dias
FROM `wa2_ai_decisions`
WHERE `created_at` >= NOW() - INTERVAL 7 DAY;

SELECT
  COALESCE(NULLIF(`current_intent`, ''), '(sin intención)') AS `current_intent`,
  COALESCE(NULLIF(`current_state`, ''), '(sin estado)') AS `current_state`,
  COUNT(*) AS conversaciones
FROM `wa2_conversations`
GROUP BY `current_intent`, `current_state`
ORDER BY conversaciones DESC, `current_intent`, `current_state`;

SELECT
  COUNT(*) AS comprobantes_pendientes
FROM `wa2_payment_receipts`
WHERE `status` IN ('pending_review', 'pending', 'received');

SELECT
  COUNT(*) AS telefonos_compartidos_por_multiples_clientes
FROM (
  SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(`phone`, '')), '+', ''), ' ', ''), '-', ''), '(', ''), ')', '') AS telefono_normalizado
  FROM `customers`
  WHERE COALESCE(`is_active`, 1) = 1
    AND TRIM(COALESCE(`phone`, '')) <> ''
  GROUP BY telefono_normalizado
  HAVING COUNT(*) > 1
) AS telefonos_compartidos;

SELECT
  COUNT(*) AS conocimientos_activos_aprobados
FROM `wa2_knowledge`
WHERE `active` = 1
  AND `approval_status` = 'approved';

SELECT
  `id`,
  `conversation_id`,
  `intent`,
  `source`,
  LEFT(`message`, 120) AS mensaje,
  LEFT(`reply`, 180) AS respuesta,
  `created_at`
FROM `wa2_ai_decisions`
ORDER BY `id` DESC
LIMIT 30;
