CREATE OR REPLACE VIEW `springboot-demo-484902.dataset_lucas.pedidos_unicos` AS
WITH base AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY cod_pedido
      ORDER BY data_atualizacao DESC
    ) AS rn
  FROM `springboot-demo-484902.dataset_lucas.pedidos`
)

SELECT
  /* Pedido */
  p.cod_pedido,
  p.id_cliente,
  p.single_id,
  p.sku,
  p.valor_item_pedido,
  p.valor_item_pedido_frete,
  p.qtd_itens,
  p.canal_venda,
  p.bandeira,
  p.data_pedido,
  p.data_entrega,
  p.cod_tip_frete,
  p.descricao_tipo_frete,
  p.valor_frete,
  p.flag_retira,
  p.flag_marketplace,
  p.flag_lista_casamento,
  p.id_lista_casamento,
  p.cod_motivo_cancelamento,
  p.descricao_motivo_cancelamento,

  /* Produto */
  pr.nome_sku AS nome_produto,
  pr.nome_setor_gerencial,
  pr.nome_classe_gerencial,
  pr.nome_especie_gerencial,
  pr.nome_categoria_gerencial,
  pr.nome_categoria_pai_gerencial,
  pr.nome_departamento_pai_gerencial,
  pr.nome_marca,
  pr.url_produto,

  /* Link da imagem */
  CONCAT('https://imgs.casasbahia.com.br/', p.sku, '/1xg.jpg?imwidth=500') AS link_imagem_produto,

  /* Cliente */
  c.nome_completo,
  c.nome_primeiro,
  c.telefone_cb AS telefone,
  c.email_cb AS email,

  /* CPF/CNPJ FORMATADO (11 ou 14 dígitos) E À PROVA DE EXCEL */
  CONCAT(
    '="',
    LPAD(
      REGEXP_REPLACE(c.cpfcnpj, r'[^0-9]', ''),
      CASE 
        WHEN LENGTH(REGEXP_REPLACE(c.cpfcnpj, r'[^0-9]', '')) <= 11 THEN 11
        ELSE 14
      END,
      '0'
    ),
    '"'
  ) AS cpf_cnpj,

  /* Endereço */
  c.endereco_cep AS cep,
  CONCAT(
    c.endereco_municipio, ', ',
    c.endereco_estado, ', CEP ', c.endereco_cep
  ) AS endereco_completo

FROM base p
LEFT JOIN `springboot-demo-484902.dataset_lucas.produtos` pr
  ON p.sku = pr.sku
LEFT JOIN `springboot-demo-484902.dataset_lucas.clientes` c
  ON p.single_id = c.singleid
WHERE p.rn = 1;
