#!/bin/bash


echo -e " \n----------- Consignement Metabase Dashboard Code --------------\n"

echo "
--------------------------Consignement Business metrics--------------------------

drop table if exists OM_Order_item_Cogs_Promo_dump_Consignment
create table OM_Order_item_Cogs_Promo_dump_Consignment
as 
select distinct transaction_date,brand_name,external_id as order_id,a.sku,quantity,cogs as "Cogs Per Unit Applied",(cogs*quantity) as "Total Cogs",Shipping_province,
case when b.cogs_promo notnull then 'Promo Cogs' when b.cogs_non_promo notnull then 'Non Promo Cogs' end as "Cogs Type"
from public.GM_dashboard a 
left join public.sku_cogs_audit b on a.sku=b.sku and cast(a.transaction_date as date)=cast(b.created_at as date)

 " > /home/ec2-user/cronjob/consignement/consignement_code.sql

psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z"-f /home/ec2-user/cronjob/new_users/consignement/consignement_code.sql > consignement_code.log