SELECT
  tms.country,
  tms.payload__connectivity AS connectivity,
  tms.payload__equipment_type AS equipment_type,
  tms.payload__terminal_brand AS terminal_brand,
  tms.payload__terminal_model AS terminal_model,
  tms.payload__serial_number AS serial_number,
  tms.payload__terminal_code AS terminal_code,
  -- tms.payload__updated_at__date as update_date,
  from_unixtime( (tms.payload__updated_at__date) / 1000, "dd/MM/yyyy") update_date,
  from_unixtime( (tms.payload__activated_date__date) / 1000, "dd/MM/yyyy") activated_date,
  from_unixtime( (tms_j.payload__terminal_activated_date__date) / 1000, "dd/MM/yyyy") activated_date_2,
 
 
 
  tms.payload__seller_code AS seller_code,
  tms.payload__situation__status AS situation_status,
 
  MAX(tms_j.payload__last_version) AS pos_version
 
FROM DELTA.`/pnms/stage/event/payment_channel/terminal_activation_ms/terminal/current` AS tms
INNER JOIN
  DELTA.`/pnms/stage/event/payment_channel/terminal_journey_ms/terminal_code/current` AS tms_j ON tms.payload__serial_number = tms_j.payload__serial_number
 
WHERE
  tms.country = 'CL' AND
  tms.payload__equipment_type = 'smart_pos' AND
  -- tms.payload__situation__status = 'activated'
  tms.payload__situation__status = 'activated'
 
GROUP BY
  tms.country,
  tms.payload__connectivity,
  tms.payload__equipment_type,
  tms.payload__terminal_brand,
  tms.payload__terminal_model,
  tms.payload__serial_number,
  tms.payload__terminal_code,
 
  tms.payload__seller_code,
  tms.payload__situation__status,
  tms.payload__updated_at__date,
  tms.payload__activated_date__date,
  tms_j.payload__terminal_activated_date__date
  ;
 POR QUE A QUERY ACIMA ME TRAS UM SERIAL NUMBER A MAIS DO QUE ESSA DE BAIXO?WITH ActiveSellers AS (
    SELECT COUNT(DISTINCT seller.payload__seller_code) AS total_active_sellers
    FROM delta.`/pnms/stage/event/onboarding/seller_ms/seller/current/` AS seller
    JOIN delta.`/pnms/stage/event/payment_channel/terminal_activation_ms/terminal/current` AS act
    ON seller.payload__seller_code = act.payload__seller_code
    AND seller.country = act.country
    AND seller.tenant = act.tenant
    WHERE act.country = 'CL'
    AND act.payload__equipment_type = 'smart_pos'
    AND act.payload__situation__status = 'activated'
),

UniqueSerialNumbers AS (
    SELECT COUNT(DISTINCT payload__serial_number) AS total_unique_serial_numbers
    FROM delta.`/pnms/stage/event/payment_channel/terminal_activation_ms/terminal/current`
    WHERE country = 'CL'
    AND payload__equipment_type = 'smart_pos'
    AND payload__situation__status = 'activated'
)

SELECT 
    (SELECT total_active_sellers FROM ActiveSellers) AS total_clientes_ativos,
    (SELECT total_unique_serial_numbers FROM UniqueSerialNumbers) AS total_terminais_instalados,
    COUNT(DISTINCT pay.payload__terminal__terminal_code) AS total_terminais_com_transacoes,
    COUNT(*) AS total_transacoes_aprovadas,
    SUM(nam.payload__transaction__amount) AS total_aprovado_euro,
    SUM(nam.payload__transaction__mdr_amount) AS total_mdr_euro
FROM delta.`/pnms/stage/event/onboarding/seller_ms/seller/current/` AS seller
JOIN delta.`/pnms/stage/event/payment_channel/terminal_activation_ms/terminal/current` AS act
    ON seller.payload__seller_code = act.payload__seller_code
    AND seller.country = act.country
    AND seller.tenant = act.tenant
    AND act.payload__situation__status = 'activated'
LEFT JOIN delta.`/pnms/stage/event/payment/payment_ms/payment/current` AS pay
    ON pay.country = act.country
    AND pay.tenant = act.tenant
    AND pay.payload__seller_code = act.payload__seller_code
    AND pay.payload__terminal__terminal_code = act.payload__terminal_code
LEFT JOIN delta.`/pnms/stage/event/nam/nam_financial_movements_ms/movements/current` AS nam
    ON pay.payload__payment_id = nam.payload__transaction__payment_id
WHERE act.country = 'CL'
   AND MONTH(pay.timestamp) = 10
    AND act.payload__equipment_type = 'smart_pos'
  AND pay.payload__reason_message = 'Approved';