-- Último estado por mensaje (status + timestamp del último)
CREATE OR REPLACE VIEW vw_last_status AS
SELECT 
  ms.message_id,
  SUBSTRING_INDEX(GROUP_CONCAT(ms.status ORDER BY ms.ts DESC), ',', 1)      AS last_status,
  SUBSTRING_INDEX(GROUP_CONCAT(DATE_FORMAT(ms.ts, '%Y-%m-%d %H:%i:%s') 
                               ORDER BY ms.ts DESC), ',', 1)                 AS last_status_at
FROM message_status ms
GROUP BY ms.message_id;

-- Tiempos por estado (primera vez que ocurrió cada hito)
CREATE OR REPLACE VIEW vw_status_times AS
SELECT 
  message_id,
  MIN(CASE WHEN status='sent'      THEN ts END) AS sent_at,
  MIN(CASE WHEN status='delivered' THEN ts END) AS delivered_at,
  MIN(CASE WHEN status='read'      THEN ts END) AS read_at,
  MIN(CASE WHEN status='failed'    THEN ts END) AS failed_at
FROM message_status
GROUP BY message_id;

-- DETALLE (para exportar CSV)
-- Muestra 1 fila por mensaje con estado final, tiempos y, si existe, info de billing
SELECT
  m.id,
  m.wa_message_id,
  m.to_e164           AS numero,
  m.template_name,
  m.category,
  m.sent_at           AS envio_creado_en,
  st.delivered_at,
  st.read_at,
  ls.last_status,
  ls.last_status_at,
  COALESCE(be.billable, 0)      AS billable,
  be.unit_price,
  be.currency,
  be.market,
  be.ts               AS billed_at
FROM messages m
LEFT JOIN vw_last_status  ls ON ls.message_id = m.id
LEFT JOIN vw_status_times st ON st.message_id = m.id
LEFT JOIN billing_events  be ON be.message_id = m.id
ORDER BY m.id DESC
LIMIT 200;

-- RESUMEN por fecha/plantilla/categoría (para panel y facturación)
SELECT 
  DATE(m.sent_at)                          AS fecha,
  m.template_name,
  m.category,
  COUNT(*)                                 AS enviados,
  SUM(ls.last_status='delivered')          AS entregados,
  SUM(ls.last_status='read')               AS leidos,
  SUM(ls.last_status='failed')             AS fallidos,
  SUM(COALESCE(be.billable,0))             AS cobrables,
  ROUND(SUM(COALESCE(be.unit_price,0)),4)  AS costo_total,
  MAX(be.currency)                         AS moneda
FROM messages m
LEFT JOIN vw_last_status ls ON ls.message_id = m.id
LEFT JOIN billing_events be ON be.message_id = m.id
GROUP BY 1,2,3
ORDER BY fecha DESC, template_name;

-- (Opcional) TOP ERRORES para diagnóstico
SELECT 
  ms.error_code, ms.error_title, COUNT(*) AS ocurrencias
FROM message_status ms
WHERE ms.status='failed'
GROUP BY ms.error_code, ms.error_title
ORDER BY ocurrencias DESC;

-- (Opcional) TOP números con más fallos
SELECT  
  m.to_e164, COUNT(*) AS ocurrencias
FROM messages m
LEFT JOIN message_status ms ON ms.message_id = m.id
WHERE ms.status = 'failed'
GROUP BY m.to_e164
ORDER BY ocurrencias DESC;