-- INTERLINK WhatsApp v2.6
-- Incidencias verificadas, tickets reales, tecnología de acceso y conocimiento estructurado.
-- Compatible con MariaDB 10.11. Seleccionar/usar la base interlink_crm.

USE `interlink_crm`;

CREATE TABLE IF NOT EXISTS `wa2_schema_migrations` (
  `migration_key` varchar(120) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `applied_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`migration_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Los datos históricos se clasifican una sola vez. Si el script se vuelve a ejecutar,
-- no sobrescribe ajustes posteriores hechos desde los paneles.
SET @wa2_v26_first_run := (
  SELECT CASE WHEN COUNT(*)=0 THEN 1 ELSE 0 END
  FROM `wa2_schema_migrations`
  WHERE `migration_key`='wa2_v26_incidents_tickets_knowledge'
);

-- 1. Tecnología del servicio. No se infiere "antena" por ausencia de ONU.
ALTER TABLE `services`
  ADD COLUMN IF NOT EXISTS `access_technology` varchar(24) NOT NULL DEFAULT 'unknown' AFTER `status`,
  ADD INDEX IF NOT EXISTS `idx_services_access_technology` (`access_technology`);

UPDATE `services`
SET `access_technology`='fiber'
WHERE (`access_technology` IS NULL OR `access_technology`='' OR `access_technology`='unknown')
  AND (
    NULLIF(TRIM(COALESCE(`onu_sn`,'')),'') IS NOT NULL OR
    NULLIF(TRIM(COALESCE(`onu_model`,'')),'') IS NOT NULL OR
    NULLIF(TRIM(COALESCE(`olt_name`,'')),'') IS NOT NULL OR
    NULLIF(TRIM(COALESCE(`pon_port`,'')),'') IS NOT NULL OR
    NULLIF(TRIM(COALESCE(`onu_id`,'')),'') IS NOT NULL
  );

-- 2. Políticas operativas del asistente.
ALTER TABLE `wa2_settings`
  ADD COLUMN IF NOT EXISTS `ticket_auto_create_enabled` tinyint(1) NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `ticket_system_user_id` int unsigned DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `incident_auto_reply_enabled` tinyint(1) NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS `operational_ai_refine_enabled` tinyint(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `knowledge_max_results` tinyint unsigned NOT NULL DEFAULT 4;

-- 3. Base de conocimiento estructurada y aprobable.
ALTER TABLE `wa2_knowledge`
  ADD COLUMN IF NOT EXISTS `intent_key` varchar(64) NOT NULL DEFAULT 'any' AFTER `category`,
  ADD COLUMN IF NOT EXISTS `access_technology` varchar(24) NOT NULL DEFAULT 'any' AFTER `visibility`,
  ADD COLUMN IF NOT EXISTS `locality` varchar(120) DEFAULT NULL AFTER `access_technology`,
  ADD COLUMN IF NOT EXISTS `keywords` text DEFAULT NULL AFTER `locality`,
  ADD COLUMN IF NOT EXISTS `allowed_action` varchar(32) NOT NULL DEFAULT 'inform' AFTER `content`,
  ADD COLUMN IF NOT EXISTS `forbidden_claims` text DEFAULT NULL AFTER `allowed_action`,
  ADD COLUMN IF NOT EXISTS `approval_status` varchar(16) NOT NULL DEFAULT 'approved' AFTER `forbidden_claims`,
  ADD COLUMN IF NOT EXISTS `version_no` int unsigned NOT NULL DEFAULT 1 AFTER `approval_status`,
  ADD COLUMN IF NOT EXISTS `valid_from` datetime DEFAULT NULL AFTER `version_no`,
  ADD COLUMN IF NOT EXISTS `valid_until` datetime DEFAULT NULL AFTER `valid_from`,
  ADD COLUMN IF NOT EXISTS `created_by_user_id` int unsigned DEFAULT NULL AFTER `valid_until`,
  ADD COLUMN IF NOT EXISTS `approved_by_user_id` int unsigned DEFAULT NULL AFTER `created_by_user_id`,
  ADD COLUMN IF NOT EXISTS `approved_at` datetime DEFAULT NULL AFTER `approved_by_user_id`,
  ADD INDEX IF NOT EXISTS `idx_wa2_knowledge_intent` (`active`,`approval_status`,`intent_key`,`access_technology`,`sort_order`),
  ADD INDEX IF NOT EXISTS `idx_wa2_knowledge_validity` (`valid_from`,`valid_until`);

-- Clasifica solamente las entradas históricas que todavía quedaron como "any".
UPDATE `wa2_knowledge`
SET `intent_key` = CASE
  WHEN LOWER(`title`) LIKE '%traslado%' OR LOWER(`title`) LIKE '%cambio de domicilio%' THEN 'service_transfer'
  WHEN LOWER(`title`) LIKE '%lentitud%' OR LOWER(`title`) LIKE '%cortes%' THEN 'support_slow'
  WHEN LOWER(`title`) LIKE '%tipo de conexion%' OR LOWER(`title`) LIKE '%tipo de conexión%' THEN 'support_general'
  WHEN `category`='soporte' THEN 'support_general'
  WHEN `category`='cobertura' THEN 'coverage'
  WHEN `category` IN ('planes','costos','promociones') THEN 'plans_costs'
  WHEN `category` IN ('formas_pago','pagos') THEN 'payment_general'
  WHEN `category`='comercial' AND LOWER(`title`) LIKE '%direccion de oficina%' THEN 'payment_general'
  WHEN `category`='comercial' THEN 'new_installation'
  ELSE 'any'
END
WHERE @wa2_v26_first_run=1
  AND (`intent_key` IS NULL OR `intent_key`='' OR `intent_key`='any');

UPDATE `wa2_knowledge`
SET `access_technology`='wireless'
WHERE @wa2_v26_first_run=1
  AND (`access_technology` IS NULL OR `access_technology`='' OR `access_technology`='any')
  AND `category`='soporte'
  AND (LOWER(`content`) LIKE '%antena%' OR LOWER(`title`) LIKE '%antena%');

UPDATE `wa2_knowledge`
SET `allowed_action` = CASE
  WHEN `intent_key` IN ('service_transfer','complaint') THEN 'create_ticket'
  WHEN `intent_key` LIKE 'support_%' THEN 'ask_one'
  ELSE 'inform'
END
WHERE @wa2_v26_first_run=1
  AND (`allowed_action` IS NULL OR `allowed_action`='' OR `allowed_action`='inform');

UPDATE `wa2_knowledge`
SET `forbidden_claims` = 'No afirmar cortes, horarios, tickets, visitas, pagos o cobertura exacta sin datos confirmados por el CRM.'
WHERE @wa2_v26_first_run=1
  AND (`forbidden_claims` IS NULL OR TRIM(`forbidden_claims`)='');

UPDATE `wa2_knowledge`
SET `approval_status`='approved',
    `approved_at`=COALESCE(`approved_at`,`created_at`)
WHERE @wa2_v26_first_run=1
  AND (`approval_status` IS NULL OR `approval_status`='' OR (`approval_status`='approved' AND `approved_at` IS NULL));

UPDATE `wa2_knowledge`
SET `active`=0
WHERE @wa2_v26_first_run=1
  AND `visibility`='internal';

-- 4. Panel de incidencias de red verificadas.
CREATE TABLE IF NOT EXISTS `wa2_network_incidents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `incident_code` varchar(32) NOT NULL,
  `title` varchar(180) NOT NULL,
  `status` varchar(24) NOT NULL DEFAULT 'investigating',
  `severity` varchar(16) NOT NULL DEFAULT 'minor',
  `scope_type` varchar(16) NOT NULL DEFAULT 'mixed',
  `public_message` text DEFAULT NULL,
  `internal_notes` text DEFAULT NULL,
  `started_at` datetime NOT NULL DEFAULT current_timestamp(),
  `eta_at` datetime DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by_user_id` int unsigned DEFAULT NULL,
  `updated_by_user_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wa2_incident_code` (`incident_code`),
  KEY `idx_wa2_incident_active` (`active`,`status`,`severity`,`started_at`),
  KEY `idx_wa2_incident_eta` (`eta_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa2_network_incident_nodes` (
  `incident_id` bigint unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`incident_id`,`node_id`),
  KEY `idx_wa2_incident_node` (`node_id`,`incident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa2_network_incident_localities` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `incident_id` bigint unsigned NOT NULL,
  `locality_label` varchar(120) NOT NULL,
  `locality_normalized` varchar(120) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wa2_incident_locality` (`incident_id`,`locality_normalized`),
  KEY `idx_wa2_incident_locality_norm` (`locality_normalized`,`incident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Vínculo entre conversación y ticket real del CRM.
CREATE TABLE IF NOT EXISTS `wa2_ticket_links` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint unsigned NOT NULL,
  `ticket_id` bigint unsigned NOT NULL,
  `customer_id` int unsigned NOT NULL,
  `service_id` int unsigned DEFAULT NULL,
  `reason_key` varchar(64) NOT NULL,
  `incident_id` bigint unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wa2_ticket_link_ticket` (`ticket_id`),
  KEY `idx_wa2_ticket_link_conversation` (`conversation_id`,`created_at`),
  KEY `idx_wa2_ticket_link_lookup` (`customer_id`,`service_id`,`reason_key`),
  KEY `idx_wa2_ticket_link_incident` (`incident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Historial de conocimiento y evaluación humana de respuestas.
CREATE TABLE IF NOT EXISTS `wa2_knowledge_versions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `knowledge_id` bigint unsigned NOT NULL,
  `version_no` int unsigned NOT NULL,
  `change_action` varchar(24) NOT NULL,
  `snapshot_json` longtext NOT NULL,
  `changed_by_user_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wa2_knowledge_version` (`knowledge_id`,`version_no`,`change_action`),
  KEY `idx_wa2_knowledge_versions_item` (`knowledge_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wa2_ai_feedback` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `decision_id` bigint unsigned NOT NULL,
  `conversation_id` bigint unsigned DEFAULT NULL,
  `rating` varchar(16) NOT NULL,
  `reason_key` varchar(48) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `corrected_reply` mediumtext DEFAULT NULL,
  `review_status` varchar(16) NOT NULL DEFAULT 'pending',
  `reviewed_by_user_id` int unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_by_user_id` int unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wa2_ai_feedback_decision` (`decision_id`),
  KEY `idx_wa2_ai_feedback_conversation` (`conversation_id`,`created_at`),
  KEY `idx_wa2_ai_feedback_rating` (`rating`,`reason_key`,`created_at`),
  KEY `idx_wa2_ai_feedback_review` (`review_status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `wa2_ai_feedback`
  ADD COLUMN IF NOT EXISTS `review_status` varchar(16) NOT NULL DEFAULT 'pending' AFTER `corrected_reply`,
  ADD COLUMN IF NOT EXISTS `reviewed_by_user_id` int unsigned DEFAULT NULL AFTER `review_status`,
  ADD COLUMN IF NOT EXISTS `reviewed_at` datetime DEFAULT NULL AFTER `reviewed_by_user_id`,
  ADD INDEX IF NOT EXISTS `idx_wa2_ai_feedback_review` (`review_status`,`created_at`);

INSERT IGNORE INTO `wa2_schema_migrations` (`migration_key`,`description`)
VALUES ('wa2_v26_incidents_tickets_knowledge','Incidencias, tickets reales, tecnología y conocimiento estructurado');
