-- Migración legacy -> DEMO_TO para Tickets-Etiquetas v2
-- Ejecutar en la MISMA BDD que la tabla `tickets` (string.php: $db_ticketsHELP = conexión tickets.php).
-- Override PDO solo si usas include/cfg/tickets_demo_to_bd.php con otra base.
-- Recomendado: respaldo previo de tablas involucradas.

START TRANSACTION;

-- 1) Catálogo DEMO_TO desde tickets alegacy (evita duplicar por legacy_ticket_id)
INSERT INTO DEMO_TO_tickets_catalogo
  (estado, autor, legacy_ticket_id, cliente_id, titulo, descripcion, tipo, prioridad, fecha_ticket, fecha_vencimiento, extra_json)
SELECT
  1 AS estado,
  'migracion_legacy' AS autor,
  t.idTicket AS legacy_ticket_id,
  t.ticket_cliente AS cliente_id,
  COALESCE(NULLIF(TRIM(t.ticket_titulo), ''), CONCAT('Ticket #', t.idTicket)) AS titulo,
  t.ticket_desc AS descripcion,
  t.ticket_tipo AS tipo,
  CASE
    WHEN t.ticket_estado IN ('Nuevo', 'Revisar', 'Agendar', 'Agendado') THEN 'MEDIA'
    WHEN t.ticket_estado IN ('Finalizado', 'Cerrado') THEN 'BAJA'
    ELSE 'MEDIA'
  END AS prioridad,
  FROM_UNIXTIME(NULLIF(t.ticket_time, 0)) AS fecha_ticket,
  FROM_UNIXTIME(NULLIF(t.ticket_timeVencimiento, 0)) AS fecha_vencimiento,
  JSON_OBJECT(
    'ticket_estado', t.ticket_estado,
    'ticket_timeAgenda', t.ticket_timeAgenda,
    'ticket_idTicketMadre', t.ticket_idTicketMadre
  ) AS extra_json
FROM tickets t
WHERE NOT EXISTS (
  SELECT 1
  FROM DEMO_TO_tickets_catalogo dc
  WHERE dc.legacy_ticket_id = t.idTicket
);

-- 2) Relación ticket-etiqueta DEMO_TO desdea CSV legacy ticket_etiquetas
--    Se usa splitter por posiaciones (1..40); ajustar si hay más etiquetas por ticket.
INSERT IGNORE INTO DEMO_TO_tickets_etiquetas_rel
  (estado, autor, ticket_id, etiqueta_id, origen)
SELECT
  1 AS estado,
  'migracion_legacy' AS autor,
  dc.id AS ticket_id,
  CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.ticket_etiquetas, ',', n.n), ',', -1)) AS UNSIGNED) AS etiqueta_id,
  'legacy_csv' AS origen
FROM tickets t
INNER JOIN DEMO_TO_tickets_catalogo dc
  ON dc.legacy_ticket_id = t.idTicket
INNER JOIN (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
  UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
  UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
  UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
  UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35
  UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40
) n
  ON n.n <= 1 + LENGTH(COALESCE(t.ticket_etiquetas, '')) - LENGTH(REPLACE(COALESCE(t.ticket_etiquetas, ''), ',', ''))
INNER JOIN DEMO_TO_tickets_etiquetas e
  ON e.id = CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.ticket_etiquetas, ',', n.n), ',', -1)) AS UNSIGNED)
WHERE COALESCE(TRIM(t.ticket_etiquetas), '') <> '';

-- 3) Evento de auditoría por tickets migrados
INSERT INTO DEMO_TO_tickets_eventos
  (estado, autor, ticket_id, evento, detalle_json)
SELECT
  1 AS estado,
  'migracion_legacy' AS autor,
  dc.id AS ticket_id,
  'migracion_inicial' AS evento,
  JSON_OBJECT('legacy_ticket_id', dc.legacy_ticket_id) AS detalle_json
FROM DEMO_TO_tickets_catalogo dc
WHERE dc.legacy_ticket_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM DEMO_TO_tickets_eventos ev
    WHERE ev.ticket_id = dc.id
      AND ev.evento = 'migracion_inicial'
  );

COMMIT;

-- Validación rápida (post migración)
-- SELECT COUNT(*) AS total_catalogo FROM DEMO_TO_tickets_catalogo;
-- SELECT COUNT(*) AS total_rel FROM DEMO_TO_tickets_etiquetas_rel;
-- SELECT COUNT(*) AS total_eventos_migracion FROM DEMO_TO_tickets_eventos WHERE evento = 'migracion_inicial';