#!/bin/bash


echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n"

backDay=$1

echo $backDay

echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n"
reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d")
echo 'reportDate'=$reportDate

echo "

DROP TABLE IF EXISTS public.manual_bundle_sku_data ;


CREATE TABLE public.manual_bundle_sku_data AS
SELECT upper(bundle_sku) Parent_sku ,
       Upper(A.sku) child_sku ,
       A.quantity child_quantity,
       'Bundle' parent_sku_class,
                cast(B.retail_price AS decimal(22,2)) bundle_retail_price,
                cast(json_extract_path_text(C.tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',TRUE) AS decimal(22,2)) bronze_price,
                cast(json_extract_path_text(C.tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',TRUE) AS decimal(22,2)) silver_price,
                cast(json_extract_path_text(C.tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) AS decimal(22,2)) gold_price,
                cast(C.retail_price AS decimal(22,2)) child_retail_price
FROM bundle_data_manual_new A
INNER  JOIN raena_catalog_management.product B ON upper(A.bundle_sku) = upper(B.sku)
INNER  JOIN raena_catalog_management.product C ON upper(A.sku) = upper(C.sku);


DROP TABLE IF EXISTS public.order_level_data;


CREATE TABLE public.order_level_data AS
SELECT distinct external_id,
       transaction_date AS transaction_date,
       discount_amount,
       shipping_cost,
       A.coupon_code,
       reseller_tier_name,
       CASE
           WHEN flash_sale_id IS NOT NULL THEN 'Flash'
       END Product_type,
       order_loyalty_discount,
       total_amount ,
       total_retail_price ,
       tier_discount,
       total_dynamic_and_tier_price,
       payment_amount,
       coupon_applied_on
FROM
  (SELECT A.id AS external_id ,
          (A.created_at) AS transaction_date ,
          A.discount_amount,
          applied_shipping_amount shipping_cost,
          A.coupon_code,
          json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
          flash_sale_id,
          loyalty_discount AS order_loyalty_discount,
          total_amount ,
          total_retail_price ,
          tier_discount,
          total_dynamic_and_tier_price,
          payment_amount
   FROM raena_order_management.order A
   WHERE payment_status='Paid'
     AND cast(A.created_at AS date) >='$reportDate'
     AND is_campaign = 'false') A
LEFT JOIN raena_order_management.discount_coupon C ON A.coupon_code = C.coupon_code;


DROP TABLE IF EXISTS public.base_netsuite_stage1_V1;


CREATE TABLE public.base_netsuite_stage1_V1 AS
SELECT DISTINCT transaction_date,
                A.external_id,
                CASE
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_sku
                    WHEN B.id = F.sales_sub_order_id THEN F.sku
                    ELSE B.parent_sku
                END sku,
                 B.parent_sku parent_sku,
                CASE
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_quantity*B.quantity
                    WHEN B.id = F.sales_sub_order_id THEN F.quantity
                    ELSE B.quantity
                END quantity,
                B.quantity parent_quantity,
                CASE
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar) THEN D.child_retail_price
                    WHEN B.id = F.sales_sub_order_id THEN F.retail_price
                    ELSE B.retail_price
                END retail_price,
                B.retail_price parent_retail_price,
                CASE
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'BRONZE' THEN bronze_price
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'SILVER' THEN silver_price
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'GOLD' THEN gold_price
                    when  B.id = F.sales_sub_order_id THEN F.effective_wholesale_price    
                    ELSE B.effective_wholesale_price
                END effective_wholesale_price,
                B.effective_wholesale_price parent_wholesale_price,
                CASE
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'BRONZE' THEN bronze_price
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'SILVER' THEN silver_price
                    WHEN cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
                         AND reseller_tier_name = 'GOLD' THEN gold_price
                    when  B.id = F.sales_sub_order_id THEN F.actual_wholesale_price    
                    ELSE B.actual_wholesale_price
                END actual_wholesale_price,
                B.actual_wholesale_price parent_actual_wholesale_price,
                CASE
                    WHEN B.id = F.sales_sub_order_id THEN F.coupon_discount
                    ELSE B.coupon_discount
                END discount_price,
                coupon_applied_on,
                A.discount_amount,
                0 Dynamic_price ,
                0 parent_dynamic_price,
                case when  B.id = F.sales_sub_order_id THEN F.payment_amount
                    else B.payment_amount end payment_price,
                A.payment_amount,
                B.product_class,
                coalesce(F.product_class,B.product_class) parent_product_class,
                CASE
                    WHEN A.product_type = 'Flash'
                         AND B.parent_sku LIKE 'BAZ%' THEN 'Flash Bundle'
                    WHEN A.product_type = 'Flash'
                         AND B.parent_sku NOT LIKE 'BAZ%' THEN 'Flash'
                    WHEN B.parent_sku LIKE 'BAZ%' THEN 'Bundle'
                    ELSE 'Regular'
                END AS product_type_class,
                B.id  sales_sub_order_id,
                A.order_loyalty_discount,
                B.loyalty_discount,
                reseller_tier_name,
                price_type
FROM public.order_level_data A
LEFT JOIN raena_order_management.sales_sub_order B ON A.external_id = B.order_id
LEFT JOIN public.manual_bundle_sku_data D ON cast(B.parent_sku AS varchar) = cast(D.parent_sku AS varchar)
LEFT JOIN
  (SELECT id ,
          sales_sub_order_id ,
          retail_price,
          Sku ,
          quantity,
          effective_wholesale_price,
          actual_wholesale_price,
          coupon_discount,
          payment_amount,
          product_class,
          loyalty_discount
   FROM raena_order_management.sales_sub_order_parent_child ssopc
   WHERE product_class = 'Bundle'
     AND cast(created_at AS date)>='$reportDate') F ON B.id = F.sales_sub_order_id
ORDER BY 1,2;

DROP TABLE IF EXISTS public.base_netsuite_stage2_v1 ;

CREATE TABLE public.base_netsuite_stage2_v1 AS
SELECT transaction_date,
       A.external_id,
       A.sku,
       parent_sku,
       quantity,
       parent_quantity,
       retail_price,
       parent_retail_price,
       effective_wholesale_price,
       parent_wholesale_price,
       actual_wholesale_price,
       discount_price,
       coupon_applied_on,
       discount_amount,
       dynamic_price,
       parent_dynamic_price,
       payment_price,
       payment_amount,
       product_class,
       parent_product_class,
       product_type_class,
       sales_sub_order_id,
       CASE
           WHEN cast(transaction_date AS date)< '2022-02-28'
                AND A.external_id = B.external_id THEN B.final_loyalty_point
           ELSE A.loyalty_discount
       END loyalty_discount,
       price_type
FROM public.base_netsuite_stage1_V1 A
LEFT JOIN
  (SELECT external_id ,
          sku ,
          final_loyalty_point
   FROM public.final_loyalty_point) B ON A.external_id = B.external_id
AND A.sku = B.sku;


DROP TABLE IF EXISTS public.base_netsuite_stage3_v1 ;

CREATE TABLE public.base_netsuite_stage3_v1 AS
SELECT A.* ,
       CASE
           WHEN dynamic_price>0 THEN dynamic_price
           ELSE effective_wholesale_price
       END final_wholesale_price,
       CASE
           WHEN coupon_applied_on <> 'Cart'
                AND discount_price>0 THEN retail_price*quantity*discount_amount/sum(CASE WHEN discount_price>0 THEN retail_price*quantity END)over(partition BY A.external_id)
           WHEN coupon_applied_on <> 'Cart'
                AND discount_price=0 THEN 0
           WHEN coupon_applied_on ='Cart' 
                AND discount_price>0 THEN discount_price  --retail_price*quantity*discount_amount/sum(retail_price*quantity)over(partition BY A.external_id) 
             WHEN coupon_applied_on ='Cart'    
                AND discount_price=0 THEN retail_price*quantity*discount_amount/sum(retail_price*quantity)over(partition BY A.external_id)
           ELSE discount_price
       END Final_discount ,
       retail_price*quantity-coalesce(actual_wholesale_price,effective_wholesale_price)*quantity AS seller_margin,
     case when product_type_class in ('Flash Bundle','Flash') then 'Flash'
     when coupon_applied_on ='Cart' and price_type ='NEGOTIATED_PRICE'
     then 'Negotiated'
     when coupon_applied_on = 'Shipping Fee' then 'Shipping'
     when coupon_applied_on is not null then 'Brand'
     end  discount_type
FROM public.base_netsuite_stage2_v1 A ;

DROP TABLE IF EXISTS public.base_netsuite_stage4_v1 ;


CREATE TABLE public.base_netsuite_stage4_v1 AS
SELECT A.* ,
       CASE
           WHEN (parent_product_class= 'Bundle'
                 OR product_type_class in('Bundle','Flash Bundle'))
                AND A.external_id = B.external_id
                AND A.parent_sku = B.parent_sku then (effective_wholesale_price*quantity)-((cast(final_wholesale_price AS decimal(22,2))*quantity*(parent_wholesale_price*parent_quantity))/ttl_wholesale_price)
       END AS additional_discount,
       CASE
           WHEN final_discount >0 THEN cast(final_discount AS decimal(22,2))-cast(seller_margin AS decimal(22,2))
       END effective_coupon_discount
FROM public.base_netsuite_stage3_v1 A
LEFT JOIN
  (SELECT external_id ,
          parent_sku ,
          round(sum(retail_price*quantity))ttl_retail_price,
          round(sum(final_wholesale_price*quantity))ttl_wholesale_price
   FROM public.base_netsuite_stage3_v1
   WHERE parent_product_class= 'Bundle'
     OR product_type_class in('Bundle','Flash Bundle')
   GROUP BY external_id ,
            parent_sku) B ON A.external_id = B.external_id
AND A.parent_sku = B.parent_sku;

--Alter table public.base_netsuite_final
--add columns discount_type varchar,promo_amount decimal(22,2),actual_wholesale_price decimal(22,2);


DELETE 
FROM public.base_netsuite_final
WHERE external_id IN
    (SELECT DISTINCT external_id
     FROM public.order_level_data);
    

INSERT INTO public.base_netsuite_final
SELECT A.external_id,
       A.transaction_date+interval'7 Hours' transaction_date  ,
       A.discount_amount order_discount_amount,
       A.shipping_cost ,
       coupon_code ,
       A.coupon_applied_on ,
       reseller_tier_name tier,
       B.sku product_sku,
       B.quantity ,
       retail_price,
       B.seller_margin/B.quantity seller_margin,
       ((coalesce(retail_price,0)*quantity) -coalesce(seller_margin,0) -coalesce(effective_coupon_discount,0)-coalesce((case when final_discount >0 then 0 else actual_wholesale_price*quantity - effective_wholesale_price*quantity end ),0) -(coalesce(additional_discount,0)) -coalesce(loyalty_discount,0))/quantity discounted_price,
       cast(final_discount AS decimal(22,2))/quantity coupon_discount,
       effective_coupon_discount/quantity effective_coupon_discount ,
       loyalty_discount/quantity loyalty_discount,
       coalesce(additional_discount,0)/quantity additional_discount,
       CASE
           WHEN (parent_product_class = 'Bundle'
                 AND product_type_class = 'Flash')
                OR product_type_class = 'Flash Bundle' THEN 'Flash Bundle'
           WHEN (parent_product_class = 'Flash'
                 OR product_type_class = 'Flash') THEN 'Flash'
           WHEN (parent_product_class = 'Bundle'
                 OR product_type_class = 'Bundle')THEN 'Bundle'
           ELSE 'Regular'
       END item_type,0 diff,
       discount_type,
       case when final_discount >0 then 0 else actual_wholesale_price*quantity - effective_wholesale_price*quantity end  promo_amount,
       actual_wholesale_price,
       effective_wholesale_price
FROM public.order_level_data A
LEFT  JOIN public.base_netsuite_stage4_v1 B ON A.external_id = B.external_id;

DROP TABLE IF EXISTS public.gm_dimensions_stage1;

CREATE TABLE public.gm_dimensions_stage1 AS
SELECT A.id AS external_id ,
       shipping_province,
       reseller_id ,
       json_extract_path_text(A.reseller_info,'name',TRUE) reseller_name,
       json_extract_path_text(A.reseller_info,'email',TRUE) reseller_email,
       json_extract_path_text(A.reseller_info,'mobile',TRUE) reseller_mobile,
       discount_type,
       json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
       C.sku ,
       shipping_to,
       C.name sku_name,
       brand_name ,
       product_type ,
       category_name,
       order_placed_by
FROM raena_order_management.order A
LEFT JOIN raena_order_management.discount_coupon B ON A.coupon_code = B.coupon_code
LEFT JOIN raena_order_management.order_item C ON A.id = C.order_id
WHERE payment_status ='Paid'
  AND A.is_campaign = 'false'
  AND cast(A.created_at AS date) >='$reportDate';

DELETE
FROM public.GM_dashboard
WHERE external_id IN
    (SELECT DISTINCT external_id
     FROM public.gm_dimensions_stage1);

INSERT INTO public.GM_dashboard
SELECT A.external_id ,
       B.transaction_date AS transaction_date,
       B.order_discount_amount ,
       B.shipping_cost ,
       A.shipping_province,
       B.coupon_code ,
       A.discount_type ,
       B.coupon_applied_on ,
       A.brand_name,
       A.category_name ,
       A.product_type ,
       A.sku sku,
       A.sku_name ,
       A.reseller_name ,
       A.reseller_email ,
       A.reseller_mobile ,
       reseller_tier_name tier_name,
       reseller_id ,
       B.quantity ,
       B.retail_price ,
       B.seller_margin ,
       B.discounted_price ,
       B.additional_discount ,
       B.loyalty_discount ,
       B.effective_coupon_discount,
       B.item_type ,
       order_placed_by ,
       min(CASE WHEN A.sku = C.sku AND cast(transaction_date AS date) = Cast(created_at AS date) THEN coalesce(cogs_promo,cogs_non_promo) END) cogs,
       shipping_to order_recipient ,
       CASE
           WHEN brand_name IN ('LUXCRIME',
                               'SKINTIFIC',
                               'TRUEVE',
                               'SANIYE',
                               'BEAUDELAB',
                               'BRASOV',
                               'FACE REPUBLIC',
                               'SKIN1004',
                               'PREMIERE BEAUTE',
                               'ALLURA',
                               'LIPLAPIN',
                               'ROUNDLAB',
                               'FACE FLUX',
                               'DOLLGORAE',
                               'SKINUA',
                               'PUREFORET',
                               'SKINTIFIC',
                               'OHMYSKIN',
                               'FEAT FOR SKIN',
                               'SECONDATE',
                               'KYND',
                               'PURNAMA',
                               'BASE',
                               'LAVIE LASH',
                               'REI SKIN',
                               'USTRAA',
                               'BRUNBRUN PARISGLOWINC',
                               'SOONHAN',
                               'THE YEON',
                               'MIXSOON',
                               'KOSE COSMEPORT') THEN 'High GM'
           WHEN brand_name IN ('W DRESSROOM',
                               'BEAUSTA',
                               'Dewycel',
                               'GLUTANEX',
                               'HISTOIRE NATURELLE',
                               'FORENCOS',
                               'BELLFLOWER',
                               'MAXCLINIC') THEN 'EL/PL'
       END brand_type ,
       Customer_type,
       BB.gm_target,
       CASE
           WHEN B.discount_type = 'Flash'
                AND promo_amount IS NOT NULL THEN 'Flash'
           WHEN B.discount_type = 'Negotiated'
                AND promo_amount IS NOT NULL THEN 'Negotiated'
           WHEN promo_amount IS NOT NULL THEN 'Brand'
       END discount_promo_type ,
       promo_amount,
       actual_wholesale_price,
       effective_wholesale_price
FROM public.gm_dimensions_stage1 A
INNER JOIN public.base_netsuite_final B ON A.external_id =B.external_id
AND A.sku=B.product_sku
LEFT JOIN
  (SELECT sku ,
          max(cogs_non_promo) cogs_non_promo,
          max(cogs_promo) cogs_promo,
          Cast(created_at AS date) created_at
   FROM public.sku_cogs_audit
   GROUP BY sku ,
            Cast(created_at AS date)) C ON A.sku = C.sku
	    AND cast(transaction_date  AS date) = Cast(created_at AS date)
LEFT JOIN
  (SELECT DISTINCT id order_id,
                   CASE
                       WHEN customer_id IS NOT NULL
                            AND order_placed_by = 'admin' THEN 'Offline Dropshipper'
                       WHEN customer_id IS NOT NULL
                            AND order_placed_by <> 'admin' THEN 'Online Dropshipper'
                       WHEN customer_id IS NULL
                            AND order_placed_by <> 'admin' THEN 'Online Reseller'
                       WHEN customer_id IS NULL
                            AND order_placed_by = 'admin' THEN 'Offline Reseller'
                   END Customer_type
   FROM raena_order_management.order) AA ON A.external_id = AA.order_id
LEFT JOIN
  (SELECT DISTINCT SKU,
                   gm_target,
                   t.name AS tierName
   FROM raena_gross_margin_management.gross_margin_config AA
   LEFT JOIN raena_user_management.tier t ON cast(AA.gm_target_tier AS TEXT) =cast(t.id AS TEXT)) BB ON A.sku = BB.sku
AND A.reseller_tier_name =BB.tierName
GROUP BY A.external_id ,
       B.transaction_date,
       B.order_discount_amount ,
       B.shipping_cost ,
       A.shipping_province,
       B.coupon_code ,
       A.discount_type ,
       B.coupon_applied_on ,
       A.brand_name,
       A.category_name ,
       A.product_type ,
       A.sku,
       A.sku_name ,
       A.reseller_name ,
       A.reseller_email ,
       A.reseller_mobile ,
       reseller_tier_name,
       reseller_id ,
       B.quantity ,
       B.retail_price ,
       B.seller_margin ,
       B.discounted_price ,
       B.additional_discount ,
       B.loyalty_discount ,
       B.effective_coupon_discount,
       B.item_type ,
       order_placed_by ,
       shipping_to ,
       CASE
           WHEN brand_name IN ('LUXCRIME',
                               'SKINTIFIC',
                               'TRUEVE',
                               'SANIYE',
                               'BEAUDELAB',
                               'BRASOV',
                               'FACE REPUBLIC',
                               'SKIN1004',
                               'PREMIERE BEAUTE',
                               'ALLURA',
                               'LIPLAPIN',
                               'ROUNDLAB',
                               'FACE FLUX',
                               'DOLLGORAE',
                               'SKINUA',
                               'PUREFORET',
                               'SKINTIFIC',
                               'OHMYSKIN',
                               'FEAT FOR SKIN',
                               'SECONDATE',
                               'KYND',
                               'PURNAMA',
                               'BASE',
                               'LAVIE LASH',
                               'REI SKIN',
                               'USTRAA',
                               'BRUNBRUN PARISGLOWINC',
                               'SOONHAN',
                               'THE YEON',
                               'MIXSOON',
                               'KOSE COSMEPORT') THEN 'High GM'
           WHEN brand_name IN ('W DRESSROOM',
                               'BEAUSTA',
                               'Dewycel',
                               'GLUTANEX',
                               'HISTOIRE NATURELLE',
                               'FORENCOS',
                               'BELLFLOWER',
                               'MAXCLINIC') THEN 'EL/PL'
       END ,
       Customer_type,
       BB.gm_target,
       CASE
           WHEN B.discount_type = 'Flash'
                AND promo_amount IS NOT NULL THEN 'Flash'
           WHEN B.discount_type = 'Negotiated'
                AND promo_amount IS NOT NULL THEN 'Negotiated'
           WHEN promo_amount IS NOT NULL THEN 'Brand'
       END  ,
       promo_amount,
       actual_wholesale_price,
       effective_wholesale_price;



 
DROP TABLE IF EXISTS public.business_dimensions_stage1;

CREATE TABLE public.business_dimensions_stage1 AS
SELECT A.id AS external_id ,
          reseller_id,
          customer_id,
          cart_id,
          A.flash_sale_id,
          shipping_to,
          payment_status,
          A.status,
          A.stock_type,
          order_placed_by,
          order_warehouse,
          cast(channel_id as text) channel_id ,
          medium,
          marketplace,
          provider,
          coalesce(cast(B.tier_id as varchar),cast(C.tier_id as varchar)) tier_id ,
          json_extract_path_text(A.reseller_info,'city',TRUE) reseller_city,
	  json_extract_path_text(A.reseller_info,'province',TRUE) reseller_province,
          json_extract_path_text(A.reseller_info,'name',TRUE) reseller_name,
          json_extract_path_text(A.reseller_info,'email',TRUE) reseller_email,
          json_extract_path_text(A.reseller_info,'mobile',TRUE) reseller_mobile,
          json_extract_path_text(A.reseller_info,'store',TRUE) reseller_store,
          json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
          shipping_address_line1 ,
          shipping_address_line2 ,
          shipping_pincode ,
          shipping_district,
          shipping_city ,
          shipping_province ,
          is_bank_transfer ,
          is_campaign ,
          discount_type,
          shipping_coupon_discount
   FROM raena_order_management.order A
   left join public.tier_name B on A.id = B.id
   left join raena_user_management.user C on cast(A.reseller_id as varchar)  = cast(C.id as varchar)
   left join raena_order_management.discount_coupon D on A.coupon_code = D.coupon_code
   where cast(A.created_at AS date) >='$reportDate' ;

DELETE
FROM public.business_report
WHERE external_id IN
    (SELECT DISTINCT external_id
     FROM public.business_dimensions_stage1);

  Insert into public.business_report
  SELECT A.external_id,
  cast(B.transaction_date as date) created_date ,
       B.order_discount_amount,
       B.shipping_cost ,
       coupon_code ,
       D.name brand_name,
       C.product_type,
       E.name category_name,
       C.name sku_name,
       B.product_sku sku,
       reseller_id,
       customer_id,
       cart_id,
       A.flash_sale_id,
       shipping_to,
       payment_status,
       status,
       A.stock_type,
       order_placed_by,
       order_warehouse,
       channel_id,
       medium,
       marketplace,
       provider,
       reseller_tier_name tier,
       reseller_city,
       reseller_name,
       reseller_email,
       reseller_mobile,
       reseller_store,
       shipping_address_line1 ,
       shipping_address_line2 ,
       shipping_pincode ,
       shipping_district,
       shipping_city ,
       shipping_province ,
       is_bank_transfer ,
       is_campaign ,
       C.Country,
       B.quantity ,
       B.retail_price,
       seller_margin,
       discounted_price,
       loyalty_discount,
       effective_coupon_discount ,
       additional_discount,
       item_type,
       B.discount_type discount_promo_type,
       promo_amount,
       actual_wholesale_price,
       effective_wholesale_price,
       reseller_province
FROM public.business_dimensions_stage1 A
LEFT JOIN public.base_netsuite_final B ON A.external_id = B.external_id
left join raena_catalog_management.product C on B.product_sku = C.sku
left join raena_catalog_management.brand D on C.brand_id = D.id
left join raena_catalog_management.category E on C.category_id = E.id;

DELETE
FROM public.OM_GM_DB_Product_category
WHERE external_Id IN
    (SELECT DISTINCT external_id
     FROM public.GM_dashboard
     WHERE cast(transaction_date as date) >='$reportDate');


INSERT INTO OM_GM_DB_Product_category
SELECT A.external_id ,
cast(transaction_date as date) transaction_date ,
       concat(left(TO_char(transaction_date,'month'),3),date_part('year',transaction_date)) AS Time,
       order_discount_amount ,
       shipping_cost ,
       coupon_code ,
       discount_type ,
       coupon_applied_on ,
       D.brand_name,
       D.category_name ,
       D.product_type ,
       A.sku,
       D.name sku_name ,
       reseller_name ,
       reseller_email ,
       reseller_mobile ,
       tier_name ,
       reseller_id ,
       quantity ,
       retail_price ,
       seller_margin ,
       discounted_price ,
       additional_discount,
       item_type ,
       order_placed_by ,
       A.cogs,
       shipping_province ,
       CASE
           WHEN A.external_id = C.external_id THEN 'Yes'
           ELSE 'No'
       END Flag ,
       CASE
           WHEN tier_name='GOLD' THEN gold_price
           WHEN tier_name ='SILVER' THEN silver_price
           WHEN tier_name ='BRONZE' THEN bronze_price
           ELSE retail_price-seller_margin
       END wholesale_price,
       order_recipient,
       brand_type,
       Customer_type,
       gm_target,
       discount_promo_type,
       promo_amount,
       actual_wholesale_price,
       effective_wholesale_price
FROM public.GM_dashboard A
LEFT JOIN
  (SELECT DISTINCT external_id
   FROM
     (SELECT external_id,
             discounted_price ,
             sku ,
             (((retail_price-seller_margin)*quantity) -(cogs*quantity))/((retail_price-seller_margin)*quantity) Pre_discount,
             CASE WHEN discounted_price>0 THEN ((discounted_price*quantity) -(cogs*quantity))/(discounted_price*quantity) ELSE 0 END Post_discount
      FROM public.GM_dashboard) A
   WHERE Post_discount>Pre_discount) C ON A.external_id = C.external_id
LEFT JOIN
  (SELECT DISTINCT Sku ,
                   A.name,
                   A.product_type,
                   C.name category_name ,
                   B.name brand_name
   FROM raena_catalog_management.product A
   LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id
   LEFT JOIN raena_catalog_management.category c ON A.category_id=C.id) D ON A.sku = D.sku
LEFT JOIN pricing_sheet P ON A.sku=p.skucode
WHERE cast(transaction_date as date) >='$reportDate';

--Business Dashboard
DELETE
FROM public.GM_GROWTH_TAB1
WHERE created_date >='$reportDate';

INSERT INTO public.GM_GROWTH_TAB1
SELECT created_date,
       brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       order_placed_by,
       shipping_province,
       shipping_to,
       status,
       channel_name,
       reseller_name,reseller_email,
       count(DISTINCT external_id) AS Number_of_Orders ,
       sum(retail_price*quantity) RSP ,
       sum((retail_price*quantity)-(seller_margin*quantity))wholesale_price ,
       sum(discounted_price*quantity) AS Payment_Price ,
       sum(effective_coupon_discount*quantity) AS Effective_Coupon_Discount,
       sum(loyalty_discount*quantity)Total_Loyalty_point,
       sum(additional_discount*quantity)additional_discount,
       sum(seller_margin*quantity)seller_margin,
       sum(discounted_price*quantity)/count(DISTINCT external_id) AS AOV,
       sum(quantity) AS quantity,
       discount_promo_type,
       sum(promo_amount) promo_amount,
       sum(actual_wholesale_price* quantity) actual_wholesale_price,
       sum(effective_wholesale_price*quantity) effective_wholesale_price,
       reseller_province
FROM
  (SELECT DISTINCT a.created_date,
                   'All' AS brand_name,
                   'All' AS product_type,
                   'All' AS category_name,
                   'All' AS sku,
                   'All' AS sku_name,
                   order_placed_by,
                   shipping_province,
                   shipping_to,
                   status,
                   b.channel_name,
                   reseller_name,reseller_email,
                   external_id,
                   retail_price,
                   quantity,
                   seller_margin,
                   discounted_price,
                   effective_coupon_discount,
                   loyalty_discount,
                   additional_discount,
                   discount_promo_type,
                   promo_amount,
                   actual_wholesale_price,
		   effective_wholesale_price,
		   reseller_province,
                   row_number() over (partition BY external_id
                                      ORDER BY external_id) AS R
   FROM public.business_report a
   LEFT JOIN
     (SELECT DISTINCT A.id ,
                      channel_id ,
                      D.name channel_name
      FROM raena_order_management.order A
      LEFT JOIN
        (SELECT id,
                name
         FROM raena_order_management.channel) D ON A.channel_id = D.id
      WHERE cast(created_at AS date) >= '$reportDate'
        AND channel_id IS NOT NULL) b ON a.external_id=b.id
   WHERE cast(is_campaign AS varchar)='false'
     AND payment_status='Paid'
     AND created_date >= '$reportDate'
   UNION SELECT DISTINCT a.created_date,
                         brand_name,
                         product_type,
                         category_name,
                         sku,
                         sku_name,
                         order_placed_by,
                         shipping_province,
                         shipping_to,
                         status,
                         b.channel_name,
                         reseller_name,reseller_email,
                         external_id,
                         retail_price,
                         quantity,
                         seller_margin,
                         discounted_price,
                         effective_coupon_discount,
                         loyalty_discount,
                         additional_discount,
                   discount_promo_type,
                   promo_amount,
                   actual_wholesale_price,
		   effective_wholesale_price,
		   reseller_province,
                         row_number() over (partition BY external_id
                                            ORDER BY external_id) AS R
   FROM public.business_report a
   LEFT JOIN
     (SELECT DISTINCT A.id ,
                      channel_id ,
                      D.name channel_name
      FROM raena_order_management.order A
      LEFT JOIN
        (SELECT id,
                name
         FROM raena_order_management.channel) D ON A.channel_id = D.id
      WHERE cast(created_at AS date) >= '$reportDate'
        AND channel_id IS NOT NULL) b ON a.external_id=b.id
   WHERE cast(is_campaign AS varchar)='false'
     AND payment_status='Paid'
     AND created_date >= '$reportDate')
GROUP BY created_date,
         brand_name,
         product_type,
         category_name,
         sku,
         sku_name,
         order_placed_by,
         shipping_province,
         shipping_to,
         status,
         channel_name,
         reseller_name,reseller_email,
	 discount_promo_type,
	 reseller_province;
         
--Metric Trend
DROP TABLE IF EXISTS public.GM_GROWTH_TAB2;
CREATE TABLE public.GM_GROWTH_TAB2 AS
SELECT 'year' AS frequency,
       cast(date_part('year',created_date) AS varchar) AS time,
       cast(date_part('year',created_date) AS int) AS sort ,
       *
FROM public.GM_GROWTH_TAB1
UNION
SELECT 'quarter' AS frequency,
       To_char(created_date,'quarter') AS month_name,
       date_part('quarter',created_date) as sort ,
       *
FROM public.GM_GROWTH_TAB1
UNION
SELECT 'month' AS frequency,
       To_char(created_date,'month') AS month_name,
       date_part('month',created_date) as sort  ,
       *
FROM public.GM_GROWTH_TAB1
UNION
SELECT 'week' AS frequency,
       To_char(created_date,'week') AS month_name,
       cast(left(To_char(created_date,'week'),1) as int) AS sort ,
       *
FROM public.GM_GROWTH_TAB1
UNION
SELECT 'day' AS frequency,
       cast(date_part('day',created_date) as varchar) AS month_name,
       cast(date_part('day',created_date) AS int) AS sort ,
       *
FROM public.GM_GROWTH_TAB1
ORDER BY 3;

DROP TABLE IF EXISTS public.GM_GROWTH_TAB3;
CREATE TABLE public.GM_GROWTH_TAB3 AS
(
SELECT 'year' AS frequency,
	   'All' AS brand_name,
       'All' AS product_type,
       'All' AS category_name,
       'All' AS sku,
       'All' AS sku_name,
       cast(date_part('year',transaction_date) AS varchar) AS time,
       cast(date_part('year',transaction_date) AS int) AS sort,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 7,8,9,10,11,12,13,14
       union
SELECT 'year' AS frequency,
	   brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       cast(date_part('year',transaction_date) AS varchar) AS time,
       cast(date_part('year',transaction_date) AS int) AS sort,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 2,3,4,5,6,7,8,9,10,11,12,13,14
)
union
(
SELECT 'quarter' AS frequency,
	   'All' AS brand_name,
       'All' AS product_type,
       'All' AS category_name,
       'All' AS sku,
       'All' AS sku_name,
       To_char(transaction_date,'quarter') AS month_name,
       date_part('quarter',transaction_date) as sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 7,8,9,10,11,12,13,14
       union
SELECT 'quarter' AS frequency,
	   brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       To_char(transaction_date,'quarter') AS month_name,
       date_part('quarter',transaction_date) as sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by  2,3,4,5,6,7,8,9,10,11,12,13,14
)

union
(
SELECT 'month' AS frequency,
	   'All' AS brand_name,
       'All' AS product_type,
       'All' AS category_name,
       'All' AS sku,
       'All' AS sku_name,
       To_char(transaction_date,'month') AS month_name,
       date_part('month',transaction_date) as sort  ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 7,8,9,10,11,12,13,14
       union
SELECT 'month' AS frequency,
	   brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       To_char(transaction_date,'month') AS month_name,
       date_part('month',transaction_date) as sort  ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by  2,3,4,5,6,7,8,9,10,11,12,13,14
)

union
(
SELECT 'week' AS frequency,
	   'All' AS brand_name,
       'All' AS product_type,
       'All' AS category_name,
       'All' AS sku,
       'All' AS sku_name,
       To_char(transaction_date,'week') AS month_name,
       cast(left(To_char(transaction_date,'week'),1) as int) AS sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 7,8,9,10,11,12,13,14
       union
SELECT 'week' AS frequency,
	   brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       To_char(transaction_date,'week') AS month_name,
       cast(left(To_char(transaction_date,'week'),1) as int) AS sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by  2,3,4,5,6,7,8,9,10,11,12,13,14
)

union
(
SELECT 'day' AS frequency,
	   'All' AS brand_name,
       'All' AS product_type,
       'All' AS category_name,
       'All' AS sku,
       'All' AS sku_name,
       cast(date_part('day',transaction_date) as varchar) AS month_name,
       cast(date_part('day',transaction_date) AS int) AS sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by 7,8,9,10,11,12,13,14
       union
SELECT 'day' AS frequency,
	   brand_name,
       product_type,
       category_name,
       sku,
       sku_name,
       cast(date_part('day',transaction_date) as varchar) AS month_name,
       cast(date_part('day',transaction_date) AS int) AS sort ,o.status,
       order_placed_by,reseller_email,shipping_province,order_recipient,transaction_date,
       sum((quantity*discounted_price)-(cogs*quantity)) as Numerator,sum(quantity*discounted_price) as Denominator
       from public.OM_GM_DB_Product_category a
       inner join (select distinct id,status from raena_order_management."order") o on a.external_id=o.id
       group by  2,3,4,5,6,7,8,9,10,11,12,13,14
)
ORDER BY 3;

drop table shipping_fee_base_table;

create table shipping_fee_base_table as
select A.order_id ,
	 sum(case when A.sales_sub_order_shipment_id = C.reference_id then ((C.shipping_amount)/(C.order_weight))*(C.final_weight) else B.shipping_amount end ) shipping_amount
from (select distinct  order_id  ,sales_sub_order_shipment_id from  raena_order_management.sales_sub_order) A
left join raena_order_management.sales_sub_order_shipment B on A.sales_sub_order_shipment_id = B.id 
left join public.OM_Logistic_final_shipping C on A.sales_sub_order_shipment_id = C.reference_id
group by A.order_id;

DROP TABLE public.shipping_fee_sku_base_stage1 ;


CREATE TABLE public.shipping_fee_sku_base_stage1 AS
SELECT A.order_id ,
       A.id ,
       coalesce(B.sku,A.parent_sku) Sku ,
       A.sales_sub_order_shipment_id ,
       coalesce((B.quantity*A.quantity),A.quantity) Quantity,
       CASE
           WHEN A.sales_sub_order_shipment_id=D.reference_id THEN ((D.shipping_amount)/(D.order_weight))*(D.final_weight)
           ELSE C.shipping_amount
       END shipping_amount ,
       A.product_class
FROM raena_order_management.sales_sub_order A
LEFT JOIN raena_order_management.sales_sub_order_parent_child B ON A.id = B.sales_sub_order_id
LEFT JOIN raena_order_management.sales_sub_order_shipment C ON A.sales_sub_order_shipment_id = C.id
LEFT JOIN OM_Logistic_final_shipping D ON A.sales_sub_order_shipment_id=D.reference_id ;

DROP TABLE public.weight_sku_table;


CREATE TABLE public.weight_sku_table AS
SELECT sku,
       CASE
           WHEN weight>=volume_weight THEN weight
           ELSE volume_weight
       END weight
FROM
  (SELECT a.sku,
          weight,
          (height*width*LENGTH)/6000 AS volume_weight
   FROM raena_catalog_management.product a
   WHERE CLASS<>'Bundle') A;

DROP TABLE public.shipping_fee_sku_base_stage2;


CREATE TABLE public.shipping_fee_sku_base_stage2 AS
SELECT A.*,
       B.weight*Quantity sku_weight,
       CASE
           WHEN (B.weight*Quantity)<1.3 THEN 1
           WHEN (B.weight*Quantity)>=1.3
                AND (ABS((B.weight*Quantity)) - FLOOR(ABS((B.weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((B.weight*Quantity))+1
           ELSE FLOOR((B.weight*Quantity))
       END AS final_weight
FROM public.shipping_fee_sku_base_stage1 A
LEFT JOIN public.weight_sku_table B ON A.sku = B.sku ;


DROP TABLE public.shipping_fee_sku_base_stage3;


CREATE TABLE public.shipping_fee_sku_base_stage3 AS
SELECT *,
       (sku_weight*shipping_amount)/sum(sku_weight) over(partition BY sales_sub_order_shipment_id) new_shipment_amount
FROM public.shipping_fee_sku_base_stage2;


DROP TABLE sku_level_shipping_fee_final;


CREATE TABLE public.sku_level_shipping_fee_final AS
SELECT order_id ,
       sku,
       product_class ,
       sum(new_shipment_amount) new_shipment_amount
FROM public.shipping_fee_sku_base_stage3
GROUP BY 1,
         2,
         3;


DROP TABLE public.shipping_fee_sku_base_old_stage1;


CREATE TABLE public.shipping_fee_sku_base_old_stage1 AS
SELECT external_id ,
       A.sku ,
       quantity,
       shipping_cost ,
       CASE
           WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
           ELSE 'Product'
       END product_class,
       weight*Quantity sku_weight,
       CASE
           WHEN (weight*Quantity)<1.3 THEN 1
           WHEN (weight*Quantity)>=1.3
                AND (ABS((weight*Quantity)) - FLOOR(ABS((weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((weight*Quantity))+1
           ELSE FLOOR((weight*Quantity))
       END AS final_weight
FROM gm_dashboard A
LEFT JOIN public.weight_sku_table B ON A.sku = B.sku;



DROP TABLE public.shipping_fee_sku_base_old_stage2;


CREATE TABLE public.shipping_fee_sku_base_old_stage2 AS
SELECT *,
       (sku_weight*shipping_cost)/sum(sku_weight) over(partition BY external_id) new_shipment_amount
FROM public.shipping_fee_sku_base_old_stage1 ;


DROP TABLE public.sku_level_shipping_fee_old_final;

CREATE TABLE public.sku_level_shipping_fee_old_final AS
SELECT external_id ,
       sku,
       product_class ,
       sum(new_shipment_amount) new_shipment_amount
FROM public.shipping_fee_sku_base_old_stage2
GROUP BY 1,
         2,
         3;
 

" > /home/ec2-user/cronjob/Gm_dashboard/Final_GM_v2.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/Gm_dashboard/Final_GM_v2.sql > etlTransaction_job_v2.log