#!/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")
date
echo 'reportDate'=$reportDate


echo "
drop table  raena_analytics.increase_cogs_alert;

create table raena_analytics.increase_cogs_alert
as
SELECT AA.sku,
       brand_name,
       new_cogs,
       new_cogs_updated_date,
       old_cogs,
       old_cogs_updated_date,
       po_number,
       cogs cogs_in_po,
       calculated_pkp_cogs po_cogs_with_pkp,
       quantity quantity_in_po,
       wholesale_gold_price,
       wholesale_silver_price,
       wholesale_bronze_price,
  cast(CEILING(new_cogs/cast((100-gold_gm_target) as bigint)) as bigint)*100 gold_msp,
  cast(CEILING(new_cogs/cast((100-silver_gm_target)as bigint)) as bigint)*100 silver_msp,
  cast(CEILING(new_cogs/cast((100-bronze_gm_target)as bigint)) as bigint)*100 bronze_msp
FROM
  (SELECT sku,
          cogs new_cogs,
          created_at  new_cogs_updated_date,
          old_cogs ,
          brand_name,
          cast(wholesale_gold_price as varchar)wholesale_gold_price ,
       cast(wholesale_silver_price as varchar) wholesale_silver_price,
       cast(wholesale_bronze_price as varchar)wholesale_bronze_price,
          old_created_date  old_cogs_updated_date,
          cogs-old_cogs diff
from (SELECT A.sku,
                        A.cogs,
                        A.old_cogs,
                        A.created_at,
                        A.old_created_date,
                        raena_catalog_management.brand.name brand_name,
                         json_extract_path_text(raena_catalog_management.product.tier_price,'bf645e97-8a48-4977-8367-e987489760f9',true) wholesale_gold_price,
                        json_extract_path_text(raena_catalog_management.product.tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',true) wholesale_silver_price,
                        json_extract_path_text(raena_catalog_management.product.tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',true) wholesale_bronze_price
                FROM (select sku , cogs , lead(cogs, 1)over ( partition by sku  order by created_at desc ) old_cogs ,
                     created_at ,
                  lead(created_at , 1 ) over (partition by sku  order by created_at desc ) old_created_date   
               from raena_catalog_management.cogs_audit 
               order by sku , created_at desc) A
                left join  raena_catalog_management.product on A.sku=raena_catalog_management.product.sku
                LEFT JOIN raena_catalog_management.brand  ON raena_catalog_management.product.brand_id=raena_catalog_management.brand.id 
        )A ) AA
LEFT JOIN
  (SELECT B.po_number,
          A.sku,
          A.received_quantity quantity ,
          C.cogs,
          C.calculated_pkp_cogs,
          A.created_at
   FROM raena_erp_management.inbound_grn_sku A
   LEFT JOIN raena_erp_management.inbound_order B ON A.po_id=B.id
   LEFT  JOIN raena_erp_management.inbound_order_sku C ON A.sku_id = C.id
  )BB ON AA.sku = BB.sku
AND AA.new_cogs_updated_date::date = BB.created_at::Date
LEFT JOIN
(SELECT sku ,
        sum(CASE WHEN tierName='GOLD' THEN gm_target END) gold_gm_target,
        sum(CASE WHEN tierName='SILVER' THEN gm_target END) silver_gm_target,
        sum(CASE WHEN tierName='BRONZE' THEN gm_target END)bronze_gm_target
 FROM
   (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)
    where gm_target <100) A
 GROUP BY 1) DD ON AA.sku = DD.sku
 where diff<>0
ORDER by gold_msp desc;

" > /home/ec2-user/cronjob/redshift/sql_code/increase_cogs_alert.sql

psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/increase_cogs_alert.sql