get google merchant xml sql.sql

with first_variant_in_set as (
  select * from product_detail where id in 
  (
    select (array_agg(id))[1]
    from 
      (
        select id, product_id from product_detail
        order by current_stock desc
      )A
    group by product_id
  )
),
first_image_in_set as (
    select (array_agg(media_id))[1] media_id, id as product_id
    from 
      (
        select unnest(p.media_ids) media_id, p.id from product p
      )A
    group by id
)
, items as (
select
  p.reference_number as "g:id",
  'Foraged' as "g:brand",
  'new' as "g:condition",
  name as "g:title",
  p.description as "g:description",
  (
    case when length(m.path) >0 
      then concat('https://foraged-backend-prod-file-repository.s3.amazonaws.com/', m.path)
      else null
    end
  )as "g:image_link",
  (concat(
    'https://www.foraged.com/products/',
     p.slug,
    '?utm_source=Google Shopping&utm_campaign=Google Merchant Center 2&utm_medium=organic&utm_term=',
    p.reference_number
  )) as "g:link",
  '422' as "g:google_product_category",
  'Food, Beverages & Tobacco > Food Items' "g:product_type",
  (
    case when v.current_stock > 0 then 'in_stock' else 'out_of_stock' end
  ) as "g:availability",
  v.price || ' USD' as "g:price",
  v.weight || ' lb' as "g:shipping_weight",
  v.length || ' in' as "g:shipping_length",
  v.width  || ' in'  as "g:shipping_width",
  v.height || ' in'  as "g:shipping_height",
  'no'  as "g:identifier_exists"
from
  product p
  inner join first_variant_in_set v on v.product_id = p.id
  left join first_image_in_set img on img.product_id = p.id
  left join media m on m.id = img.media_id
  where price >0
)
--select * from items
insert into temp_dump_google_xml (json_dump)
select jsonb_agg(row_to_json(A)) from items A