WITH DELAY_ID_TL AS (

   WITH SHP AS (

      SELECT DISTINCT

      SHP_SHIPMENT_ID,
      SHP_PICKING_TYPE_ID,
      SHP_LOGISTIC_CENTER_ID,
      SHP_DATE_FIRST_VISIT_ID,
      SHP_DATETIME_DELIVERED_ID

      FROM WHOWNER.BT_SHP_SHIPMENTS

      WHERE      CAST(SHP_DATE_FIRST_VISIT_ID AS DATE) >= '2022-03-17'
               AND CAST(SHP_DATE_FIRST_VISIT_ID AS DATE) <= '2022-03-17'
             AND SHP_STATUS_ID = 'delivered'
             AND SIT_SITE_ID = 'MLB'
             AND SHP_SHIPPING_MODE_ID = 'me2'

    )


    SELECT DISTINCT

    A.SHP_SHIPMENT_ID AS ID_PACOTE,
    D.TMS_SELLER_INFO_NAME AS SELLER,
    C.SHP_PICKING_TYPE_ID AS TIPO_PICKING,
    C.SHP_LOGISTIC_CENTER_ID AS ORIGEM,
    A.SHP_LG_FACILITY_ID AS DESTINO,
    RO.SHP_LG_ORIGIN_CODE AS XPT,

    CASE WHEN CAST(C.SHP_DATE_FIRST_VISIT_ID AS DATE) < CAST(B.EST_DELIVERY_DATE AS DATE) THEN 'EARLY'
         WHEN CAST(C.SHP_DATE_FIRST_VISIT_ID AS DATE) > CAST((CASE WHEN B.EST_DELIVERY_OFFSET_DATE IS NULL THEN DATETIME_ADD(B.EST_DELIVERY_DATE, INTERVAL '21' HOUR) ELSE DATETIME_ADD(B.EST_DELIVERY_OFFSET_DATE, INTERVAL '21' HOUR) END) AS DATE) THEN 'DELAY'
         ELSE 'ON_TIME'
    END AS STATUS_LT,

    DATETIME_ADD(B.EST_DELIVERY_DATE, INTERVAL '1' HOUR) AS PROMESSA_INICIAL,
    DATETIME_ADD(B.EST_DELIVERY_OFFSET_DATE, INTERVAL '1' HOUR) AS PROMESSA_FINAL,
    CASE WHEN B.EST_DELIVERY_OFFSET_DATE IS NULL THEN DATETIME_ADD(B.EST_DELIVERY_DATE, INTERVAL '21' HOUR) ELSE DATETIME_ADD(B.EST_DELIVERY_OFFSET_DATE, INTERVAL '21' HOUR) END AS PROMESSA,

    MIN(CASE WHEN A.SHP_LG_STATUS = 'ready_to_print'  THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS A_ETIQUETAR,
    MIN(CASE WHEN A.SHP_LG_STATUS = 'for_pickup'      THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS PRONTO_PARA_COLETA,
    MIN(CASE WHEN A.SHP_LG_STATUS = 'picked_up'       THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS COLETADO,
    MIN(CASE WHEN A.SHP_LG_STATUS = 'on_hub'          THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS EM_DEPOSITO,
    MIN(CASE WHEN A.SHP_LG_STATUS = 'on_way'          THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS CAMINHO_PARA_ESTACAO,
    MIN(CASE WHEN A.SHP_LG_STATUS = 'at_station'      AND A.SHP_LG_SUB_STATUS ='sorting' THEN DATETIME_ADD(CAST(A.SHP_LG_SHIPMENT_CHK_DT AS DATETIME), INTERVAL '1' HOUR)  END) AS ENTROU_NA_ESTACAO,

    DATETIME_ADD(C.SHP_DATE_FIRST_VISIT_ID,   INTERVAL '1' HOUR) AS PRIMEIRA_VISITA,
    DATETIME_ADD(C.SHP_DATETIME_DELIVERED_ID, INTERVAL '1' HOUR) AS DATA_ENTREGA,

    FROM WHOWNER.BT_SHP_LG_SHIPMENT_CHECKPOINTS A
      INNER JOIN WHOWNER.LK_SHP_ORIGINAL_PROMISE B ON B.SHP_SHIPMENT_ID = A.SHP_SHIPMENT_ID
      INNER JOIN SHP C ON C.SHP_SHIPMENT_ID = A.SHP_SHIPMENT_ID
      INNER JOIN WHOWNER.BT_TMS_TRACKING D ON D.SHP_SHIPMENT_ID = A.SHP_SHIPMENT_ID

      RIGHT JOIN WHOWNER.BT_SHP_LG_SHIPMENTS_ROUTES logs ON A.shp_shipment_id = logs.shp_shipment_id
      INNER JOIN WHOWNER.LK_SHP_COMPANIES comp ON comp.SHP_company_ID = logs.shp_company_ID
      INNER JOIN WHOWNER.LK_SHP_LG_ROUTES XPT ON XPT.shp_lg_route_id = logs.shp_lg_route_id
      LEFT JOIN WHOWNER.LK_SHP_LG_RTG_ROUTE RR ON RR.SHP_LG_ROUTE_ID = XPT.SHP_LG_PLANNED_ROUTE
      LEFT JOIN WHOWNER.LK_SHP_LG_RTG_ORDER RO ON RO.SHP_LG_ORDER_ID = RR.SHP_LG_ROUTING_ORDER_ID

     WHERE A.SHP_LG_FACILITY_ID IN ('SMS1')

   GROUP BY 1,2,3,4,5,6,7,8,9,16,17)


SELECT DISTINCT

  ID_PACOTE,

  CASE
    WHEN TIPO_PICKING = 'fulfillment'   THEN 'FBM'
    WHEN TIPO_PICKING = 'cross_docking' THEN 'XD'
    WHEN TIPO_PICKING = 'xd_drop_off'   THEN 'XD'

  END AS TIPO_PICKING,

  CASE
    WHEN DESTINO = 'SMG1'   THEN 'MG'
    WHEN DESTINO = 'SMG6'   THEN 'MG'
    WHEN DESTINO = 'SMG12'  THEN 'MG'
    WHEN DESTINO = 'SMG4'   THEN 'MGI'
    WHEN DESTINO = 'SMG7'   THEN 'MGI'
    WHEN DESTINO = 'SMG8'   THEN 'MGI'
    WHEN DESTINO = 'SMG13'  THEN 'MGI'
    WHEN DESTINO = 'SMG2'   THEN 'MGII'
    WHEN DESTINO = 'SMG3'   THEN 'MGII'
    WHEN DESTINO = 'SMG5'   THEN 'MGII'
    WHEN DESTINO = 'SMG9'   THEN 'MGII'


  END AS REGIONAL,

  ORIGEM,
  DESTINO,
  STATUS_LT,
  PROMESSA_INICIAL,
  PROMESSA_FINAL,
  PROMESSA,
  A_ETIQUETAR,
  PRONTO_PARA_COLETA,
  COLETADO,
  EM_DEPOSITO,
  CAMINHO_PARA_ESTACAO,
  ENTROU_NA_ESTACAO,
  PRIMEIRA_VISITA,
  DATA_ENTREGA,

  CASE WHEN STATUS_LT = 'DELAY'  AND CAST(PRONTO_PARA_COLETA AS DATE) >= CAST(PROMESSA AS DATE)   THEN 'No Seller'
       WHEN STATUS_LT = 'DELAY'  AND PRONTO_PARA_COLETA > COLETADO                                THEN 'No Seller'
       WHEN STATUS_LT = 'DELAY'  AND CAST(COLETADO AS DATE) >= CAST(PROMESSA AS DATE)             THEN 'Na Coleta'
       WHEN STATUS_LT = 'DELAY'  AND CAST(EM_DEPOSITO AS DATE) > CAST(COLETADO AS DATE)           THEN 'V3 / v4'
       WHEN STATUS_LT = 'DELAY'  AND CAST(CAMINHO_PARA_ESTACAO AS DATE) > CAST(PROMESSA AS DATE)  THEN 'XD / FBM'
       WHEN STATUS_LT = 'DELAY'  AND CAST(ENTROU_NA_ESTACAO AS DATE) > CAST(PROMESSA AS DATE)     THEN 'Caminho p/ Estacao'
       WHEN STATUS_LT = 'DELAY'  AND CAST(PRIMEIRA_VISITA AS DATE) > CAST(PROMESSA AS DATE)       THEN 'SVC'
  END AS STATUS_DELAY,

  CASE WHEN CAST(PRONTO_PARA_COLETA AS DATE) = CAST(COLETADO AS DATE)                            THEN 'D'
       WHEN DATE_ADD(CAST(PRONTO_PARA_COLETA AS DATE), INTERVAL 1 DAY) = CAST(COLETADO AS DATE)  THEN 'D+1'
       WHEN DATE_ADD(CAST(PRONTO_PARA_COLETA AS DATE), INTERVAL 2 DAY) = CAST(COLETADO AS DATE)  THEN 'D+2'
       WHEN DATE_ADD(CAST(PRONTO_PARA_COLETA AS DATE), INTERVAL 3 DAY) = CAST(COLETADO AS DATE)  THEN 'D+3'
       WHEN CAST(COLETADO AS DATE) > DATE_ADD(CAST(PRONTO_PARA_COLETA AS DATE), INTERVAL 3 DAY)  THEN '> D+3'
         ELSE "S/ Coleta"
  END AS COLETA_DIAS,

  -- CASE WHEN STATUS_LT = 'DELAY'    THEN 1  ELSE 0 END AS DELAY,
  -- CASE WHEN STATUS_LT = 'ON_TIME'  THEN 1  ELSE 0 END AS ON_TIME,
  -- CASE WHEN STATUS_LT = 'EARLY'    THEN 1  ELSE 0 END AS EARLY,

FROM DELAY_ID_TL