-- Migração 003: Triggers, Procedures e Views
-- Versão: 1.0  |  Data: 2024-01-15

/* ===========================
   TRIGGER updated_at
   =========================== */
DROP TRIGGER IF EXISTS tr_tickets_updated_at;
CREATE TRIGGER tr_tickets_updated_at
BEFORE UPDATE ON tickets
FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END;

/* ===========================
   PROCEDURE recompute_sla
   =========================== */
DROP PROCEDURE IF EXISTS recompute_sla;
CREATE PROCEDURE recompute_sla(IN p_ticket_id INT)
BEGIN
  DECLARE v_sla_policy_id INT;
  DECLARE v_calendar_id INT;
  DECLARE v_primeira_resposta_horas INT;
  DECLARE v_resolucao_horas INT;
  DECLARE v_created_at TIMESTAMP;
  DECLARE v_status_pausa_sla BOOLEAN;

  -- Política de SLA do ticket
  SELECT
      sp.id,
      sp.calendar_id,
      sp.primeira_resposta_horas,
      sp.resolucao_horas,
      t.created_at
  INTO
      v_sla_policy_id,
      v_calendar_id,
      v_primeira_resposta_horas,
      v_resolucao_horas,
      v_created_at
  FROM tickets t
  JOIN sla_policies sp
    ON sp.departamento_id = t.departamento_id
   AND sp.priority_id     = t.priority_id
  WHERE t.id = p_ticket_id
    AND sp.ativo = TRUE
  LIMIT 1;

  -- Status atual pausa SLA?
  SELECT ts.pausa_sla
    INTO v_status_pausa_sla
  FROM tickets t
  JOIN ticket_statuses ts ON ts.id = t.status_id
  WHERE t.id = p_ticket_id;

  -- (Re)grava timers de SLA
  INSERT INTO sla_timers (ticket_id, sla_policy_id, primeira_resposta_due, resolucao_due)
  VALUES (
      p_ticket_id,
      v_sla_policy_id,
      DATE_ADD(v_created_at, INTERVAL v_primeira_resposta_horas HOUR),
      DATE_ADD(v_created_at, INTERVAL v_resolucao_horas HOUR)
  )
  ON DUPLICATE KEY UPDATE
      sla_policy_id = VALUES(sla_policy_id),
      primeira_resposta_due = VALUES(primeira_resposta_due),
      resolucao_due = VALUES(resolucao_due),
      pausado_em = IF(
         v_status_pausa_sla AND pausado_em IS NULL, NOW(),
         IF( NOT v_status_pausa_sla AND pausado_em IS NOT NULL, NULL, pausado_em )
      );
END;

/* ===========================
   VIEW: tickets full
   =========================== */
CREATE OR REPLACE VIEW vw_tickets_full AS
SELECT 
    t.id,
    t.numero,
    t.titulo,
    t.descricao,
    t.requester_id,
    t.assignee_id,
    d.nome AS departamento_nome,
    ts.nome AS status_nome,
    ts.cor  AS status_cor,
    tp.nome AS priority_nome,
    tp.nivel AS priority_nivel,
    tp.cor AS priority_cor,
    t.sla_due_at,
    t.first_response_due_at,
    t.first_response_at,
    t.resolved_at,
    t.closed_at,
    t.created_at,
    t.updated_at,
    CASE
      WHEN t.sla_due_at IS NOT NULL
       AND t.sla_due_at < NOW()
       AND ts.nome NOT IN ('Resolvido','Fechado','Cancelado')
      THEN TRUE ELSE FALSE
    END AS sla_vencido,
    CASE
      WHEN t.first_response_due_at IS NOT NULL
       AND t.first_response_due_at < NOW()
       AND t.first_response_at IS NULL
      THEN TRUE ELSE FALSE
    END AS primeira_resposta_vencida
FROM tickets t
JOIN departamentos   d  ON d.id  = t.departamento_id
JOIN ticket_statuses ts ON ts.id = t.status_id
JOIN ticket_priorities tp ON tp.id = t.priority_id;

/* ===========================
   VIEW: First Response Time
   =========================== */
CREATE OR REPLACE VIEW vw_first_response_time AS
SELECT 
    d.nome AS departamento,
    tp.nome AS prioridade,
    AVG(TIMESTAMPDIFF(MINUTE, t.created_at, t.first_response_at)) AS frt_minutos,
    COUNT(*) AS total_tickets,
    SUM(CASE WHEN t.first_response_at <= st.primeira_resposta_due THEN 1 ELSE 0 END) AS dentro_sla,
    ROUND(
      SUM(CASE WHEN t.first_response_at <= st.primeira_resposta_due THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
      2
    ) AS percentual_sla
FROM tickets t
JOIN departamentos d   ON d.id  = t.departamento_id
JOIN ticket_priorities tp ON tp.id = t.priority_id
LEFT JOIN sla_timers st   ON st.ticket_id = t.id
WHERE t.first_response_at IS NOT NULL
GROUP BY d.id, tp.id;

/* ===========================
   VIEW: Resolution Time
   =========================== */
CREATE OR REPLACE VIEW vw_resolution_time AS
SELECT 
    d.nome AS departamento,
    tp.nome AS prioridade,
    AVG(TIMESTAMPDIFF(HOUR, t.created_at, t.resolved_at)) AS resolucao_horas,
    COUNT(*) AS total_resolvidos,
    SUM(CASE WHEN t.resolved_at <= st.resolucao_due THEN 1 ELSE 0 END) AS dentro_sla_resolucao,
    ROUND(
      SUM(CASE WHEN t.resolved_at <= st.resolucao_due THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
      2
    ) AS percentual_sla_resolucao
FROM tickets t
JOIN departamentos d   ON d.id  = t.departamento_id
JOIN ticket_priorities tp ON tp.id = t.priority_id
LEFT JOIN sla_timers st   ON st.ticket_id = t.id
WHERE t.resolved_at IS NOT NULL
GROUP BY d.id, tp.id;

/* ===========================
   Migration log
   =========================== */
INSERT IGNORE INTO migration_log (version, description)
VALUES ('003', 'Criação de triggers, procedures e views');
