SELECT 

pedidoid AS orderid,
valortotalpedido AS totalvalue,

null AS totalitems,

CASE
    WHEN CAST(situacaopedidoid AS STRING) LIKE "1" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "2" THEN "Pagamento Pendente"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "3" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "4" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "5" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "6" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "7" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "8" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "9" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "10" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "11" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "12" THEN "Cancelado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "13" THEN "Pagamento Pendente"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "14" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "15" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "16" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "17" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "18" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "19" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "20" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "21" THEN "Faturado"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "22" THEN "Pagamento Pendente"
    WHEN CAST(situacaopedidoid AS STRING) LIKE "23" THEN "Pagamento Pendente"
    ELSE CAST(situacaopedidoid AS STRING)
END as status_pedido,

null AS forma_pagamento,

"[nome_cliente]" AS conta,

data AS data_pedido,

CAST(usuario_usuarioid AS STRING) as clientname, 

CASE
    WHEN canalorigem LIKE "" THEN "Loja"
    ELSE canalorigem
END AS origin,
TIMESTAMP(dataultimaatualizacao) AS shippingestimateddatemax, 
TIMESTAMP(dataultimaatualizacao) AS shippingestimateddate, 

"[conta_bigquery]" AS hostname, 

true AS orderiscomplete, 

TIMESTAMP(dataultimaatualizacao) AS authorizeddate, 
TIMESTAMP(dataultimaatualizacao) AS shippingestimateddatemin, 
"BRL" AS currencycode, 
data AS creationdate

FROM `[conta_bigquery].traycorp_pedidos