SELECT 

id AS orderid,
total AS totalvalue,

null AS totalitens,

CASE
    WHEN status like "%CANCELAR%" THEN "Cancelado"
    WHEN status like "%CANCELADO%" THEN "Cancelado"
    WHEN status like "%FINALIZADO%" THEN "Faturado"
    WHEN status like "%PEDIDO RECEBIDO%" THEN "Faturado"
    WHEN status like "%PENDENTE%" THEN "Pagamento Pendente"
    WHEN status like "%AGUARDANDO%" THEN "Pagamento Pendente"
    WHEN status like "%A ENVIAR%" THEN "Faturado"
    WHEN status like "%A ENVIAR VISA%" THEN "Faturado"
    WHEN status like "%A ENVIAR MASTER%" THEN "Faturado"
    WHEN status like "%ENVIADO%" THEN "Faturado"
    ELSE status
END as status_pedido, 

CASE
    WHEN payment_form LIKE "%Boleto%" THEN "Boleto" 
    WHEN payment_form LIKE "%Pix%" THEN "Pix" 
    ELSE "Cartão"
END as forma_pagamento,

"Manc Brasil" AS conta,

TIMESTAMP(date) AS data_pedido,

CAST(customer_id AS STRING) as clientname, 

point_sale AS origin, 
TIMESTAMP(shipment_date) AS shippingestimateddatemax, 
TIMESTAMP(shipment_date) AS shippingestimateddate, 

"manc_brasil" AS hostname, 

CASE
    WHEN orderstatus_type like "%open%" THEN false
    ELSE true
END AS orderiscomplete, 

TIMESTAMP(estimated_delivery_date) AS authorizeddate, 
TIMESTAMP(shipment_date) AS shippingestimateddatemin, 
"BRL" AS currencycode, 
TIMESTAMP(date) AS creationdate

FROM `manc_brasil.tray_orders`