!/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.campaign_base_data_V3;


CREATE TABLE public.campaign_base_data_V3 AS
SELECT DISTINCT channel,
                media_source ,
                 campaign_type,
                     campaign campaign_name,
                     fb_adset_name,
                     af_siteid,
                     ad_id,
                     A.user_id customer_user_id ,
                     device_brand,
                     device_model,
                     platform,
                     cast(install_time AS date) install_date,
                     sum(impressions) impressions,
                     sum(clicks) total_clicks,
                     sum(install) total_install,
                     os_version ,
                     app_version,
                     A.City_id,
                     B.profile_phone phone,
                     install_time
 FROM (select distinct * from raena_appsflyer.dw_marketing_install_stats) A
LEFT JOIN
  (SELECT DISTINCT profile_objectid,
                   profile_phone
   FROM clevertap.app_installed) B ON A.user_id = B.profile_objectid
GROUP BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9,
         10,
         11,
         12,
         16,
         17,
         18,
         19,
         20;


DROP TABLE IF EXISTS public.campaign_base_data_V4;


CREATE TABLE public.campaign_base_data_V4 AS
SELECT DISTINCT A.*,
                B.province,
                ttl_order,
                ttl_amount ,
                CASE
                    WHEN reseller_tier_name_gold>0 THEN 'GOLD'
                    WHEN reseller_tier_name_silver>0 THEN 'SILVER'
                    WHEN reseller_tier_name_bronze>0 THEN 'BRONZE'
                END highest_Tier ,
                reg_date,
                reseller_email,
                reseller_mobile,
                conversion_date
FROM public.campaign_base_data_V3 A
LEFT JOIN
  (SELECT A.id reseller_id ,
          count(DISTINCT B.id) ttl_order,
          sum(B.total_amount) AS ttl_amount ,
          A.city,
          A.province,
          A.created_at AS reg_date,
          sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='GOLD' THEN 1 END) reseller_tier_name_gold,
          sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='SILVER' THEN 1 END) reseller_tier_name_silver,
          sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='BRONZE' THEN 1 END) reseller_tier_name_bronze,
          min(B.created_at) conversion_date,
          A.email reseller_email,
          A.mobile AS reseller_mobile
   FROM raena_user_management.user A
   LEFT JOIN raena_order_management.order B ON A.id = B.reseller_id and B.payment_status = 'Paid' and is_campaign= 'false'
   GROUP BY 1,
            4,
            5,
            6,
            11,
            12) B ON phone = replace(B.reseller_mobile,'+','');


DROP TABLE IF EXISTS public.reseller_post_gm;


CREATE TABLE public.reseller_post_gm AS
SELECT reseller_name ,
       reseller_email ,
       reseller_mobile,
       sum(Before_Discount_GM) AS Pre_Disc_GM,
       sum(After_discount_GM) AS Post_Disc_GM,
       sum(GM_GAP) AS GM_GAP,
       sum(Total_Wholesale_price) AS Pre_Disc_Revenue,
       sum(a.Total_Payment_Price) AS Post_Disc_Revenue,
       sum(Blended_gm_target)/count(1) AS Blended_gm_target,
       cast(sum(cast((a.Total_Payment_Price) AS float))/sum(cast((b.Total_Payment_Price)AS float)) AS decimal(10,4)) AS Revenue,
       sum(Number_of_resellers) AS No_of_resellers,
       sum(Number_of_orders) AS No_of_orders
FROM
  (SELECT reseller_name ,
          reseller_email ,
          reseller_mobile,
          1 AS id,
         cast((sum(((retail_price*quantity)-(seller_margin*quantity))-(cogs*quantity))
/sum(case when retail_price<>0 then (retail_price*quantity)-(seller_margin*quantity) end)) as decimal(10,4)) as Before_Discount_GM,
cast((sum((quantity*discounted_price)-(cogs*quantity))
/sum(case when discounted_price<>0 then quantity*discounted_price end)) as decimal(10,4)) as After_discount_GM,
cast((sum(((retail_price*quantity)-(seller_margin*quantity))-(cogs*quantity))
/sum(case when retail_price<>0 then (retail_price*quantity)-(seller_margin*quantity) end)) as decimal(10,4))-
cast((sum((quantity*discounted_price)-(cogs*quantity))
/sum(case when discounted_price<>0 then quantity*discounted_price end)) as decimal(10,4))
 as GM_GAP,          Sum(gm_target)/count(OM_GM_DB_Product_category.sku) Blended_gm_target,
          sum((cast(retail_price AS int)*quantity)-(cast(seller_margin AS int)*quantity)) AS Total_Wholesale_price,
          sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price,
          count(DISTINCT reseller_id) AS Number_of_resellers,
          count(DISTINCT external_id) AS Number_of_orders
   FROM OM_GM_DB_Product_category
   GROUP BY reseller_name ,
            reseller_email ,
            reseller_mobile) a
LEFT JOIN
  (SELECT 1 AS id,
          sum(quantity*discounted_price) AS Total_Payment_Price
   FROM OM_GM_DB_Product_category) b ON a.id=b.id
WHERE replace(reseller_mobile,'+','') IN
    (SELECT DISTINCT phone
     FROM public.campaign_base_data_V4
     WHERE phone <> '')
GROUP BY 1,
         2,
         3
ORDER BY 10 DESC, 2 ASC;


DROP TABLE IF EXISTS public.top_brand_reseller;


CREATE TABLE public.top_brand_reseller AS
SELECT DISTINCT reseller_mobile ,
                brand_name ,
                sku_name ,
                sku
FROM
  (SELECT reseller_mobile ,
          sku_name,
          brand_name,
          OM_GM_DB_Product_category.sku,
          sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price ,
          row_number() over(partition BY reseller_mobile
                      ORDER BY sum(quantity*cast(discounted_price AS int)) DESC) rnk
   FROM OM_GM_DB_Product_category
   GROUP BY sku_name,
            OM_GM_DB_Product_category.sku,
            brand_name,
            reseller_mobile) AA
WHERE rnk = 1
  AND replace(reseller_mobile,'+','') IN
    (SELECT DISTINCT phone
     FROM public.campaign_base_data_V4
     WHERE phone <> '');


DROP TABLE IF EXISTS public.launched_total;


CREATE TABLE public.launched_total AS
SELECT profile_phone ,
       count(1) app_launched
FROM clevertap.app_launched al
WHERE profile_phone IN
    (SELECT DISTINCT phone
     FROM public.campaign_base_data_V4
     WHERE phone <> '')
GROUP BY profile_phone;


DROP TABLE IF EXISTS public.campaign_base_data_v5;


CREATE TABLE public.campaign_base_data_v5 AS
SELECT A.*,
       B.Post_Disc_GM*100 Post_discount_gm,
       B.Post_Disc_Revenue,
       C.brand_name,
       C.sku_name,
       C.sku,
       D.app_launched
FROM public.campaign_base_data_V4 A
LEFT JOIN public.reseller_post_gm B ON A.reseller_mobile = B.reseller_mobile
LEFT JOIN public.top_brand_reseller C ON A.reseller_mobile= C.reseller_mobile
LEFT JOIN public.launched_total D ON A.phone= D.profile_phone;



DROP TABLE IF EXISTS public.campaign_base_data_final_v2;


CREATE TABLE public.campaign_base_data_final_v2 AS
SELECT channel,
       media_source,
       campaign_type,
       campaign_name,
       fb_adset_name,
       af_siteid,
       ad_id ,
       customer_user_id ,
       device_brand,
       device_model,
       platform ,
       install_date,
       install_time ,
       impressions,
       total_clicks,
       total_install,
       os_version ,
       app_version ,
       city_id ,
       phone ,
       province ,
       ttl_order ,
       ttl_amount total_amount,
       highest_tier,
       reg_date,
       reseller_email,
       conversion_date,
       Post_discount_gm,
       BRAND_NAME ,
       SKU_NAME ,
       SKU ,
       app_launched TOTAL_app_launched, B.Current_tier
FROM public.campaign_base_data_v5 A
LEFT JOIN
  (SELECT mobile ,
          reseller_tier_name Current_tier ,
          created_at
   FROM
     (SELECT created_at,
             json_extract_path_text(A.reseller_info,'mobile',TRUE) mobile,
             json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
             rank() over(partition BY json_extract_path_text(A.reseller_info,'mobile',TRUE)
                         ORDER BY created_at DESC) rnk
      FROM raena_order_management.
      ORDER A
      ORDER BY created_at DESC) D
   WHERE rnk = 1) B ON A.reseller_mobile= B.mobile;



DROP TABLE IF EXISTS public.campaign_base_data_final_unistall;


CREATE TABLE public.campaign_base_data_final_unistall AS
SELECT distinct city_id ,
       cast(install_time AS date) install_date ,
       install_time ,
       cast(uninstall_time AS date) uninstall_date ,
       uninstall_time ,
       media_source,
       channel,
       campaign,
       fb_adset_name,
       af_siteid,
       device_brand,
       device_model,
       platform,
       ad_id,
       install,
       uninstall
FROM raena_appsflyer.dw_marketing_uninstall_stats;

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