DROP TABLE raena_analytics.campaign_inappEvent_base_data_base;


CREATE TABLE raena_analytics.campaign_inappEvent_base_data_base AS
SELECT channel,
       media_source ,
       campaign campaign_name,
       adset fb_adset_name,
       site_id af_siteid,
       ad,
       customer_user_id ,
       split_part(device_model,'::',1) device_brand,
       split_part(device_model,'::',2) device_model,
       platform,
       --install_time::date install_date,
       count(CASE WHEN lower(attributed_touch_type)='click' THEN 1 END) total_clicks,
       os_version ,
       app_version,
       A.City,
       original_url,
       --coalesce(reseller_mobile,profile_phone) AS reseller_mobile,
       advertising_id,
       event_time::date event_time,
       attributed_touch_time::date attributed_touch_time,
       count( case when lower(attributed_touch_type)='impression' then 1 end  )total_impression
FROM raena_appsflyer.dw_marketing_inappevent_stats A
GROUP BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9,
         10,
         12,
         13,14,
         15,
         16,17,18;


DROP TABLE raena_analytics.campaign_inappEvent_base_data;



CREATE TABLE raena_analytics.campaign_inappEvent_base_data AS
SELECT A.* ,
       D.min_created_date install_date,
       coalesce(reseller_mobile,C.profile_phone) AS reseller_mobile
FROM raena_analytics.campaign_inappEvent_base_data_base A
LEFT JOIN
  (SELECT B.id ,
          replace(B.mobile,'+','') AS reseller_mobile
   FROM raena_user_management.user B) B ON customer_user_id = cast(B.id AS varchar)
LEFT JOIN
  (SELECT DISTINCT profile_objectid,
                   profile_phone
   FROM clevertap.app_installed) C ON A.customer_user_id = C.profile_objectid
 left join  (SELECT profile_phone,
          min(created_at) min_created_date
   FROM
     (SELECT profile_phone,
             MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) created_at
      FROM clevertap.app_installed
      GROUP BY profile_phone
      UNION 
      SELECT replace(mobile,'+','') profile_phone,
                   MIN(cast(created_at AS date)) AS created_at
      FROM raena_user_management.user u
      GROUP BY 1) A
   GROUP BY 1) D ON B.reseller_mobile=D.profile_phone;  


   DROP TABLE IF EXISTS raena_analytics.gm_dashboard_appflyer_base_v1;


CREATE TABLE raena_analytics.gm_dashboard_appflyer_base_v1 AS
SELECT count(DISTINCT external_id) ttl_orders,
       sum(discounted_price*quantity) ttl_amount,
       sum(retail_price*quantity)total_retail_price,
       tier_name,
       cast(transaction_date AS date)transaction_date,
       reseller_id
FROM raena_analytics.gm_dashboard
WHERE is_campaign= 'false'
GROUP BY 
         4,
         5,6;

DROP TABLE IF EXISTS raena_analytics.user_base_appflyer_v1;


CREATE TABLE raena_analytics.user_base_appflyer_v1 AS
SELECT id,
       city,
       province,
       created_at,
       email,
       mobile
FROM raena_user_management.user;   

DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v1;

CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v1 AS
SELECT DISTINCT A.*,
                B.province,
                ttl_order,
                ttl_amount ,
                total_retail_price,
                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,
                conversion_date,
                last_transaction
FROM raena_analytics.campaign_inappEvent_base_data A
LEFT JOIN
  (SELECT A.id reseller_id ,
          sum(ttl_orders) ttl_order,
          sum(ttl_amount) ttl_amount,
          sum(total_retail_price) total_retail_price,
          A.city,
          A.province,
          A.created_at AS reg_date,
          sum(CASE WHEN B.tier_name='GOLD' THEN 1 END) reseller_tier_name_gold,
          sum(CASE WHEN B.tier_name='SILVER' THEN 1 END) reseller_tier_name_silver,
          sum(CASE WHEN B.tier_name='BRONZE' THEN 1 END) reseller_tier_name_bronze,
          min(B.transaction_date) conversion_date,
          max(B.transaction_date) last_transaction,
          A.email reseller_email,
          replace(mobile,'+','') AS reseller_mobile
   FROM raena_analytics.user_base_appflyer_v1  A
   LEFT JOIN raena_analytics.gm_dashboard_appflyer_base_v1 B ON cast(A.id AS varchar) = B.reseller_id
   GROUP BY 1, 5,
            6,
            7,
            13,14) B ON A.reseller_mobile = B.reseller_mobile;

DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v2;

CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v2 AS
SELECT reseller_mobile,
       sum(After_discount_GM) AS Post_Disc_GM,
       sum(a.Total_Payment_Price) AS Post_Disc_Revenue,
       sum(total_retail_amount)total_retail_amount
FROM
  (SELECT reseller_mobile,
          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,
          sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price,
          sum(quantity*retail_price) total_retail_amount
   FROM raena_analytics.OM_GM_DB_Product_category
   GROUP BY reseller_mobile) a
INNER JOIN
  (SELECT DISTINCT reseller_mobile phone
     FROM raena_analytics.campaign_inappEvent_base_data
     WHERE reseller_mobile <> '') B ON replace(reseller_mobile,'+','')=phone
GROUP BY 1;

DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v3;


CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v3 AS
SELECT DISTINCT replace(reseller_mobile,'+','') reseller_mobile ,
                brand_name ,
                sku_name ,
                sku
FROM
  (SELECT *
   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 raena_analytics.OM_GM_DB_Product_category
      GROUP BY sku_name,
               OM_GM_DB_Product_category.sku,
               brand_name,
               reseller_mobile) AA
   WHERE rnk = 1) B
INNER JOIN
  (SELECT DISTINCT reseller_mobile phone
   FROM raena_analytics.campaign_inappEvent_base_data
   WHERE reseller_mobile <> '') C ON replace(reseller_mobile,'+','') = phone;

DROP TABLE IF EXISTS raena_analytics.launched_total_in_app_campaign;


CREATE TABLE raena_analytics.launched_total_in_app_campaign AS
SELECT replace(profile_phone,'+','') profile_phone ,
       count(1) app_launched
FROM clevertap.app_launched al
INNER JOIN
  (SELECT DISTINCT reseller_mobile phone
   FROM raena_analytics.campaign_inappEvent_base_data
   WHERE reseller_mobile <> '') C ON profile_phone = phone
GROUP BY profile_phone;

DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v4 ;

CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v4 AS
SELECT A.*,
       B.Post_Disc_GM*100 Post_discount_gm,
       B.Post_Disc_Revenue,
       B.total_retail_amount,
       C.brand_name,
       C.sku_name,
       C.sku,
       D.app_launched
FROM raena_analytics.campaign_inappEvent_base_data_v1 A
LEFT JOIN raena_analytics.campaign_inappEvent_base_data_v2 B ON replace(A.reseller_mobile,'+','') = replace(B.reseller_mobile,'+','')
LEFT JOIN raena_analytics.campaign_inappEvent_base_data_v3 C ON replace(A.reseller_mobile,'+','')= replace(C.reseller_mobile,'+','')
LEFT JOIN raena_analytics.launched_total_in_app_campaign D ON replace(A.reseller_mobile,'+','')= replace(D.profile_phone,'+','');

DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_final;

CREATE TABLE raena_analytics.campaign_inappEvent_final AS
SELECT channel,
       media_source,
       campaign_name,
       fb_adset_name,
       af_siteid,
       ad,
       advertising_id,
       customer_user_id ,
       device_brand,
       device_model,
       platform ,
       install_date,
       total_clicks,
       total_impression,
       os_version ,
       app_version ,
       city,
       A.reseller_mobile ,
       province ,
       ttl_order ,
       ttl_amount total_amount,
       total_retail_amount,
       highest_tier,
       reg_date,
       reseller_email,
       conversion_date,
       Post_discount_gm,
       post_disc_revenue,
       BRAND_NAME ,
       SKU_NAME ,
       SKU ,
       app_launched TOTAL_app_launched,
       B.Current_tier,
       original_url,
       attributed_touch_time,
       event_time ,
       bucket,
       CASE WHEN date_trunc('Month',A.event_time)::date = date_trunc('Month',install_date)::date
           AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'New'
        WHEN date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>= 30
            AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'Activation'
        WHEN date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>=30
            AND date_trunc('Month',A.event_time)::date - date_trunc('Month',conversion_date)::date>=30 THEN 'Existing'
        WHEN (date_part('Month',install_date) =2 or date_part('Month',conversion_date)=2 ) and 
            date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>= 28
            AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'Activation'
        WHEN (date_part('Month',install_date) =2 or date_part('Month',conversion_date)=2 ) and
        date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>=28
            AND date_trunc('Month',A.event_time)::date - date_trunc('Month',conversion_date)::date>=28 THEN 'Existing'   
        ELSE 'Na'
        END  AS New_existing_flag
FROM raena_analytics.campaign_inappEvent_base_data_v4 A
LEFT JOIN
  (SELECT reseller_mobile mobile ,
          tier_name Current_tier ,
          transaction_date created_at
   FROM
     (SELECT transaction_date,
             A.reseller_mobile,
             tier_name,
             rank() over(partition BY reseller_mobile
                         ORDER BY transaction_date DESC) rnk
      FROM raena_analytics.gm_dashboard A
      ) D
   WHERE rnk = 1 and mobile <> '') B ON A.reseller_mobile= B.mobile
   left join (select A.*, replace(B.mobile,'+','') mobile from  raena_analytics.cohort_level_reseller A 
             left join raena_user_management.user B on A.reseller_id = B.id) C on A.reseller_mobile = C.mobile
  --LEFT JOIN raena_analytics.user_type_table flag ON replace(A.reseller_mobile,'+','')=flag.reseller_mobile
 ;