select sum(pre_tax_price_set_shop_money_amount) as pre_tax_price_set_shop_money_amount
from shopify.order o
join shopify.order_line_item oli on o.id = oli.order_id
where date(o.processed_at at time zone 'america/chicago') = '2022-01-27'
  and order_status_url not like 'https://dev-preview%'
  and fulfillment_service <> 'gift_card';

select
           sum(total_tax_set_shop_money_amount) as total_tax_set_shop_money_amount
   from shopify.refund r
   join shopify.refund_line_item rli on rli.refund_id = r.id
   where date(r.processed_at at time zone 'america/chicago') = '2022-01-27'
;

select sum(total_tax_set_shop_money_amount) as total_tax_set_shop_money_amount,
       sum(total_shipping_price_set_shop_money_amount) as total_shipping_price_set_shop_money_amount,
       sum(total_discounts_set_shop_money_amount) as total_discounts_set_shop_money_amount
from shopify.order o
where date(o.processed_at at time zone 'america/chicago') = '2022-01-27'
  and order_status_url not like 'https://dev-preview%'
tonacatecuhtli-prod-> ;

select sum(pre_tax_price_set_shop_money_amount) as pre_tax_price_set_shop_money_amount
from shopify.order o
join shopify.order_line_item oli on o.id = oli.order_id
where date(o.processed_at at time zone 'america/chicago') = '2022-01-27'
  and order_status_url not like 'https://dev-preview%'
  and fulfillment_service = 'gift_card';

with r as (
select r.id,
         ot.kind,
         ot.gateway,
         sum(amount) as amount
  from shopify.refund r
  join (select distinct refund_id, order_transaction_id from shopify.refund_order_transaction) rot on rot.refund_id = r.id
  join shopify.order_transaction ot on rot.order_transaction_id = ot.id
  join shopify.order o on o.id = ot.order_id
  where date(r.processed_at at time zone 'america/chicago') = '2022-01-27'
  group by r.id, ot.kind, ot.gateway
),
rli as (
select r.id,
       r.kind,
       r.gateway,
       sum(subtotal_set_shop_money_amount) as subtotal,
       sum(total_tax_set_shop_money_amount) as total_tax
from r
join shopify.refund_line_item rli on rli.refund_id = r.id
group by r.id, r.kind, r.gateway
)
select r.kind,
       r.gateway,
       sum(amount),
       sum(subtotal) as subtotal,
       sum(total_tax) as total_tax
from r
join rli using (id, kind, gateway)
group by kind, rollup(gateway)
;