-- INTERLINK WhatsApp v2.6 - Verificación de solo lectura
-- Puede ejecutarse desde phpMyAdmin después de la migración.
USE `interlink_crm`;

SELECT DATABASE() AS base_actual;

-- 1) Tablas requeridas.
SELECT
  req.table_name,
  CASE WHEN t.table_name IS NULL THEN 'FALTA' ELSE 'OK' END AS estado
FROM (
  SELECT 'wa2_network_incidents' AS table_name UNION ALL
  SELECT 'wa2_network_incident_nodes' UNION ALL
  SELECT 'wa2_network_incident_localities' UNION ALL
  SELECT 'wa2_ticket_links' UNION ALL
  SELECT 'wa2_knowledge_versions' UNION ALL
  SELECT 'wa2_ai_feedback' UNION ALL
  SELECT 'wa2_schema_migrations'
) req
LEFT JOIN information_schema.tables t
  ON t.table_schema=DATABASE() AND t.table_name=req.table_name
ORDER BY req.table_name;

-- 2) Columnas e índices requeridos.
SELECT
  req.table_name,
  req.column_name,
  CASE WHEN c.column_name IS NULL THEN 'FALTA' ELSE 'OK' END AS estado,
  c.column_type,
  c.is_nullable,
  c.column_default
FROM (
  SELECT 'services' AS table_name,'access_technology' AS column_name UNION ALL
  SELECT 'wa2_settings','ticket_auto_create_enabled' UNION ALL
  SELECT 'wa2_settings','ticket_system_user_id' UNION ALL
  SELECT 'wa2_settings','incident_auto_reply_enabled' UNION ALL
  SELECT 'wa2_settings','operational_ai_refine_enabled' UNION ALL
  SELECT 'wa2_settings','knowledge_max_results' UNION ALL
  SELECT 'wa2_knowledge','intent_key' UNION ALL
  SELECT 'wa2_knowledge','access_technology' UNION ALL
  SELECT 'wa2_knowledge','locality' UNION ALL
  SELECT 'wa2_knowledge','keywords' UNION ALL
  SELECT 'wa2_knowledge','allowed_action' UNION ALL
  SELECT 'wa2_knowledge','forbidden_claims' UNION ALL
  SELECT 'wa2_knowledge','approval_status' UNION ALL
  SELECT 'wa2_knowledge','version_no' UNION ALL
  SELECT 'wa2_knowledge','valid_from' UNION ALL
  SELECT 'wa2_knowledge','valid_until' UNION ALL
  SELECT 'wa2_knowledge','approved_at'
) req
LEFT JOIN information_schema.columns c
  ON c.table_schema=DATABASE()
 AND c.table_name=req.table_name
 AND c.column_name=req.column_name
ORDER BY req.table_name,req.column_name;

SELECT
  req.table_name,
  req.index_name,
  CASE WHEN s.index_name IS NULL THEN 'FALTA' ELSE 'OK' END AS estado
FROM (
  SELECT 'services' AS table_name,'idx_services_access_technology' AS index_name UNION ALL
  SELECT 'wa2_knowledge','idx_wa2_knowledge_intent' UNION ALL
  SELECT 'wa2_knowledge','idx_wa2_knowledge_validity' UNION ALL
  SELECT 'wa2_network_incidents','idx_wa2_incident_active' UNION ALL
  SELECT 'wa2_ticket_links','idx_wa2_ticket_link_lookup' UNION ALL
  SELECT 'wa2_ai_feedback','idx_wa2_ai_feedback_review'
) req
LEFT JOIN (
  SELECT DISTINCT table_name,index_name
  FROM information_schema.statistics
  WHERE table_schema=DATABASE()
) s ON s.table_name=req.table_name AND s.index_name=req.index_name
ORDER BY req.table_name,req.index_name;

-- 3) Configuración operativa. Se ejecuta de forma segura aunque falte una columna.
SET @wa2_has_settings := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema=DATABASE() AND table_name='wa2_settings'
    AND column_name IN ('ticket_auto_create_enabled','ticket_system_user_id','incident_auto_reply_enabled','operational_ai_refine_enabled','knowledge_max_results')
);
SET @wa2_sql := IF(@wa2_has_settings=5,
  'SELECT id,ai_enabled,ai_mode,ticket_auto_create_enabled,ticket_system_user_id,incident_auto_reply_enabled,operational_ai_refine_enabled,knowledge_max_results FROM wa2_settings WHERE id=1',
  "SELECT 'FALTAN COLUMNAS EN wa2_settings' AS advertencia"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 4) Validación del usuario usado para tickets automáticos.
SET @wa2_sql := IF(@wa2_has_settings=5,
  "SELECT s.ticket_system_user_id,u.username,u.full_name,u.is_active,CASE WHEN s.ticket_system_user_id IS NULL THEN 'PENDIENTE_CONFIGURAR' WHEN u.id IS NULL OR u.is_active<>1 THEN 'USUARIO_INVALIDO' ELSE 'OK' END AS estado FROM wa2_settings s LEFT JOIN users u ON u.id=s.ticket_system_user_id WHERE s.id=1",
  "SELECT 'NO DISPONIBLE' AS estado"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 5) Distribución de tecnología. unknown debe revisarse desde el panel.
SET @wa2_has_tech := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema=DATABASE() AND table_name='services' AND column_name='access_technology'
);
SET @wa2_sql := IF(@wa2_has_tech=1,
  'SELECT access_technology,COUNT(*) AS servicios FROM services GROUP BY access_technology ORDER BY servicios DESC,access_technology',
  "SELECT 'FALTA services.access_technology' AS advertencia"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 6) Calidad de la base de conocimiento.
SET @wa2_has_knowledge := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema=DATABASE() AND table_name='wa2_knowledge'
    AND column_name IN ('intent_key','access_technology','approval_status','valid_from','valid_until')
);
SET @wa2_sql := IF(@wa2_has_knowledge=5,
  "SELECT intent_key,access_technology,approval_status,active,COUNT(*) AS conocimientos FROM wa2_knowledge GROUP BY intent_key,access_technology,approval_status,active ORDER BY intent_key,access_technology,approval_status,active",
  "SELECT 'FALTAN COLUMNAS EN wa2_knowledge' AS advertencia"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

SET @wa2_sql := IF(@wa2_has_knowledge=5,
  "SELECT COUNT(*) AS conocimientos_activos_invalidos FROM wa2_knowledge WHERE active=1 AND (approval_status<>'approved' OR visibility='internal' OR (valid_from IS NOT NULL AND valid_from>NOW()) OR (valid_until IS NOT NULL AND valid_until<NOW()))",
  "SELECT NULL AS conocimientos_activos_invalidos"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 7) Incidencias activas inválidas: debe dar 0.
SET @wa2_has_incidents := (
  SELECT COUNT(*) FROM information_schema.tables
  WHERE table_schema=DATABASE() AND table_name IN ('wa2_network_incidents','wa2_network_incident_nodes','wa2_network_incident_localities')
);
SET @wa2_sql := IF(@wa2_has_incidents=3,
  "SELECT COUNT(*) AS incidencias_activas_sin_alcance FROM wa2_network_incidents i WHERE i.active=1 AND i.status<>'resolved' AND i.scope_type<>'global' AND NOT EXISTS (SELECT 1 FROM wa2_network_incident_nodes n WHERE n.incident_id=i.id) AND NOT EXISTS (SELECT 1 FROM wa2_network_incident_localities l WHERE l.incident_id=i.id)",
  "SELECT NULL AS incidencias_activas_sin_alcance"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

SET @wa2_sql := IF(@wa2_has_incidents=3,
  "SELECT id,incident_code,title,status,severity,scope_type,started_at,eta_at,active FROM wa2_network_incidents WHERE active=1 AND status<>'resolved' ORDER BY FIELD(severity,'critical','major','minor','info'),started_at DESC",
  "SELECT 'NO DISPONIBLE' AS incidencias_activas"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 8) Integridad de vínculos a tickets y feedback.
SET @wa2_has_ticket_links := (
  SELECT COUNT(*) FROM information_schema.tables
  WHERE table_schema=DATABASE() AND table_name='wa2_ticket_links'
);
SET @wa2_sql := IF(@wa2_has_ticket_links=1,
  "SELECT SUM(t.id IS NULL) AS links_sin_ticket,SUM(c.id IS NULL) AS links_sin_conversacion,SUM(cu.id IS NULL) AS links_sin_cliente FROM wa2_ticket_links l LEFT JOIN tickets t ON t.id=l.ticket_id LEFT JOIN wa2_conversations c ON c.id=l.conversation_id LEFT JOIN customers cu ON cu.id=l.customer_id",
  "SELECT NULL AS links_sin_ticket,NULL AS links_sin_conversacion,NULL AS links_sin_cliente"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

SET @wa2_has_feedback := (
  SELECT COUNT(*) FROM information_schema.tables
  WHERE table_schema=DATABASE() AND table_name='wa2_ai_feedback'
);
SET @wa2_sql := IF(@wa2_has_feedback=1,
  "SELECT rating,review_status,COUNT(*) AS cantidad FROM wa2_ai_feedback GROUP BY rating,review_status ORDER BY rating,review_status",
  "SELECT 'NO DISPONIBLE' AS feedback"
);
PREPARE wa2_stmt FROM @wa2_sql; EXECUTE wa2_stmt; DEALLOCATE PREPARE wa2_stmt;

-- 9) Registro de migración.
SELECT migration_key,description,applied_at
FROM wa2_schema_migrations
WHERE migration_key='wa2_v26_incidents_tickets_knowledge';
