#!/bin/bash

echo "
                   
--------------------------Rdash ------------------------- 
                  
drop table if exists om_mongo_brand_category;

create table om_mongo_brand_category
as
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 0), 'name') as actual_category_name
from public.mongo_baseproducts 
union 
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 1), 'name') as actual_category_name
from public.mongo_baseproducts mo 
union
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 2), 'name') as actual_category_name
from public.mongo_baseproducts mo 
union
select rsellviewid,
json_extract_path_text(brand, 'name') as Brand_name,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'hasChild') as has_child,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'id') as actual_categoryid,
json_extract_path_text(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replace(categorypath, chr(39), ''), 3), 'name') as actual_category_name
from public.mongo_baseproducts mo;

drop table if exists om_mongo_Order_item_details;

create table om_mongo_Order_item_details
as
select orderid,orderitemid,quantity,originalprice,rsellviewid,productsource,sku
from public.mongo_orderitems mo
order by orderid ;

drop table if exists om_mongo_Order_details;

create table om_mongo_Order_details
as
select createdat,
orderid,
json_extract_path_text(storeinfo, 'name') as store,
json_extract_path_text(storeinfo, 'storeOwner') as sellername,
json_extract_path_text(storeinfo, 'storeOwnerPhone') as Sellerphone,
storename,
marketplaceorderid,
totalamount ,orderStatus
from public.mongo_orders;


drop table if exists om_mongo_orders_summary_metabase;

create table om_mongo_orders_summary_metabase
as
select distinct mod.*,moid.orderitemid,moid.quantity,moid.originalprice,moid.rsellviewid,moid.productsource,moid.sku,
mbc.brand_name,mbc.has_child,
mbc.actual_categoryid,mbc.actual_category_name,row_number() over (partition by mod.orderid,orderitemid,sku order by originalprice desc) as R
from public.om_mongo_Order_details mod
left join om_mongo_Order_item_details moid on mod.orderid=moid.orderid
left join om_mongo_brand_category mbc on moid.rsellviewid=mbc.rsellviewid;

drop table if exists OM_Mongo_orders_dump_metabase;

create table OM_Mongo_orders_dump_metabase
as
select orderid,status,shippingamount,paymenttype,createdat,isarchived,invoicedata,estimatedeliverydate,delivereddate,pickupdonedate,actualshippingdate,
daystoship,currency,cancelreason,cancelby,buyercancelreason,orderupdatedat,ordercreatedat,totalamount,orderstatus,paymenttime,paymentstatus,storename,marketplacestoreid,
marketplaceorderid,sellerbusinessprostoreid,sellerbusinessproid,sellerraenaemail,sellerraenaphonenumber,sellerraenausername,sellerraenauserid,fulfillmentstatus,
marketplaceid,storeid,orderref,
json_extract_path_text(storeinfo, 'storeOwner') as sellername,
json_extract_path_text(storeinfo, 'storeOwnerPhone') as Sellerphone
from public.mongo_orders;

                  
                 
         " > /home/ec2-user/cronjob/rdash/mongo_rdash360.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/rdash/mongo_rdash360.sql  > mongo_rdash360.log