/*
drop table if exists raena_analytics.clevertap_event_data_vishnu;

create table raena_analytics.clevertap_event_data_vishnu
as
select ts::date ,events, name , email ,
 json_extract_path_text(phone,event_props,'CT Session Id',true) sessionid ,
json_extract_path_text(event_props,'item_id',true) item_id ,
json_extract_path_text(event_props,'isGuestUser',true) isGuestUser ,
json_extract_path_text(event_props,'CT Source',true) Source ,
json_extract_path_text(event_props,'id',true) id ,
json_extract_path_text(event_props,'brandName',true) brandName ,
json_extract_path_text(event_props,'item_name',true) item_name ,
json_extract_path_text(event_props,'offenderItems',true) offenderItems ,
json_extract_path_text(event_props,'items',true) items ,
json_extract_path_text(event_props,'search_term',true) search_term ,
json_extract_path_text(event_props,'screen',true) screen 
from clevertap.clevertap_master_data where events in (
'brands_tab_press' ,
'brand_logo_carousel_press',
'App Launched',
'view_item',
'begin_checkout',
'search',
'Charged',
'App Installed',
'view_cart',
'add_to_cart',
'customer_search',
'Notification Clicked',
'Notification Viewed',
'Push Impressions',
'home_category',
'pdp_brandprice',
'order_items_view',
'sidebar_browsebrands',
'confirm_payment',
'finish_checkout',
'loyalty_page_visit',
'home_brand_Caraousel',
'home_brand_grid_view',
'WISHLIST',
'brand_carousel_press',
'continue_shopping',
'home_banner',
'brandpage_image_banner_press',
'skip_login',
'App Uninstalled',
'coupon_applied_successfully',
'coupon_could_not_be_applied',
'view_rewards',
'view_order_checkout',
'coupon_remove',
'homeintent_buyws',
'homepage_image_banner_press',
'home_continue_payment',
'homepage_carousel_category_press',
'flashsale_carousel_item_press',
'flashsale_carousel_view_all_press',
'homeintent_managemp'
)
and ts::date >='2023-04-01';
*/

drop table raena_analytics.hero_sku_l6m;
      
CREATE TABLE raena_analytics.hero_sku_l6m AS
SELECT DISTINCT brand_name ,
                brand_id,
                SKU
FROM
  (SELECT * ,
          sum(contribution) over (partition BY brand_name  ORDER BY rnk rows unbounded preceding) final_rnk
   FROM
     (SELECT * ,
             sum(payment_amount) over (partition BY brand_name) running_sum,
                                                                (payment_amount*100)/nullif(sum(payment_amount) over (partition BY brand_name),0) contribution
      FROM
        (SELECT GD.brand_name ,
                gd.sku ,
                B.brand_id,
                sum(quantity* discounted_price) payment_amount ,
                rank() over (partition BY brand_name
                             ORDER BY sum(quantity* discounted_price) DESC) rnk
         FROM raena_analytics.gm_dashboard gd
         LEFT JOIN raena_catalog_management.product B ON gd.sku = B.sku
         WHERE transaction_date BETWEEN (date_trunc('Month',CURRENT_DATE)::date + interval'-6 Month')::date AND date_trunc('Month',CURRENT_DATE)::date
         GROUP BY 1,
                  2,
                  3) A) AA) BB
WHERE (final_rnk <=75)
  OR contribution >= 75 ;




drop table if exists raena_analytics.whatsapp_campaign_base_table ;

CREATE TABLE raena_analytics.whatsapp_campaign_base_table AS
SELECT DISTINCT brand_name ,
                segment_name,
                reseller_email,
                reseller_mobile ,
                reseller_name
from 		
(select *,row_number()over(partition by brand_name order by segment_number ) brand_limit_user
FROM
  (SELECT * ,
          rank()over(partition BY brand_name,reseller_email
                     ORDER BY segment_number) remove_duplicate_user
   FROM
     (SELECT DISTINCT anchor_brand_name brand_name,
                      'SEGMENT_PERSONAL_ANCHOR_BRAND' segment_name,
                                                      1 segment_number,
                                                        reseller_email,
                                                        reseller_mobile,
                                                        NULL reseller_name
      FROM
        (SELECT Transaction_date2 year_date,
                reseller_email,
                reseller_mobile,
                bucket revenue_cohort,
                Brand_name,
                avg_brand_contribution ,
                avg_payment_amount avg_brand_revenue,
                anchor_brand_name,
                avg_anchor_brand_contribution,
                avg_anchor_payment_amount avg_anchor_brand_revenue
         FROM raena_analytics.reseller_level_anchor_brand_data
         WHERE 1=1
           AND bucket IS NOT NULL
         ORDER BY year_date DESC , avg_anchor_brand_contribution DESC) A
      UNION ALL SELECT DISTINCT brand_name brand_name,
                                'SEGMENT_PARENT_BRAND_FOR_ANCHOR' segment_name,
                                                                  2 segment_number,
                                                                    reseller_email,
                                                                    reseller_mobile,
                                                                    NULL reseller_namme
      FROM
        (SELECT Transaction_date2 year_date,
                reseller_email,
                reseller_mobile,
                bucket revenue_cohort,
                Brand_name,
                avg_brand_contribution ,
                avg_payment_amount avg_brand_revenue,
                anchor_brand_name,
                avg_anchor_brand_contribution,
                avg_anchor_payment_amount avg_anchor_brand_revenue
         FROM raena_analytics.reseller_level_anchor_brand_data
         WHERE 1=1
           AND bucket IS NOT NULL
         ORDER BY year_date DESC , avg_anchor_brand_contribution DESC) A
      UNION ALL SELECT DISTINCT brand_name brand_name,
                                'SEGMENT_PERSONALIZED_RECOMMENDATIONS' segment_name,
                                                                       3 segment_number,
                                                                         reseller_email,
                                                                         reseller_mobile,
                                                                         name reseller_name
      FROM
        (SELECT reseller_email,
                reseller_mobile,
                u.name,
                dr.sku,
                b.name brand_name,
                score match_score,
                after_discount post_discount_gm,
                stock_type,
                pi.current_inventory_ready,
                pi.current_inventory_pre_order,
                p.name product_name
         FROM
           (SELECT *
            FROM raena_recommendation_engine.daily_recommendation
            ORDER BY reseller_email,
                     score DESC) dr
         INNER JOIN
           ( SELECT *
            FROM raena_user_management.user
            WHERE status = 'active') u ON u.mobile = dr.reseller_mobile
         INNER JOIN
           ( SELECT id,
                    sku,
                    stock_type,
                    name,
                    brand_id
            FROM raena_catalog_management.product
            WHERE is_archived = 'false'
              AND is_delisted = 'false') p ON p.sku = dr.sku
         INNER JOIN
           ( SELECT product_id,
                    sum(CASE WHEN stock_type = 'READY_SKU' THEN stock_limit - reserve_stock END) AS current_inventory_ready ,
                    sum(CASE WHEN stock_type = 'PRE_ORDER' THEN stock_limit - reserve_stock END) AS current_inventory_pre_order
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pi ON pi.product_id = p.id
         INNER JOIN
           ( SELECT *
            FROM raena_catalog_management.brand) b ON b.id = p.brand_id
         ORDER BY match_score DESC) A
      UNION ALL SELECT DISTINCT name brand_name,
                                'SEGMENT_PROVINCE_BASED_RECOMMENDATION' segment_name,
                                                                        4 segment_number,
                                                                          reseller_email,
                                                                          reseller_mobile,
                                                                          name reseller_name
      FROM
        ( SELECT shipping_province ,
                 dr.sku ,
                 b.name ,
                 score ,
                 after_discount ,
                 stock_type,
                 pi.current_inventory_ready,
                 pi.current_inventory_pre_order,
                 p.name product_name
         FROM
           (SELECT shipping_province,
                   sku,
                   score,
                   after_discount
            FROM raena_recommendation_engine.daily_recommendation_new_user
            ORDER BY score DESC, rank) dr
         INNER JOIN
           (SELECT id,
                   sku,
                   stock_type,
                   name,
                   brand_id
            FROM raena_catalog_management.product
            WHERE is_archived = 'false'
              AND is_delisted = 'false') p ON p.sku = dr.sku
         INNER JOIN
           (SELECT product_id,
                   sum(CASE WHEN stock_type = 'READY_SKU' THEN stock_limit - reserve_stock END) AS current_inventory_ready ,
                   sum(CASE WHEN stock_type = 'PRE_ORDER' THEN stock_limit - reserve_stock END) AS current_inventory_pre_order
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pi ON pi.product_id = p.id
         INNER JOIN
           (SELECT id,
                   name
            FROM raena_catalog_management.brand) b ON b.id = p.brand_id
         ORDER BY Score DESC) A
      INNER JOIN
        (SELECT reseller_email,
                reseller_mobile,
                reseller_name ,
                shipping_province,
                sku,
                brand_name
         FROM raena_analytics.gm_dashboard gd
         WHERE transaction_date::date >='2023-02-01'
           AND reseller_email IS NOT NULL)B ON lower(A.shipping_province) = lower(B.shipping_province)
      AND A.name = B.brand_name
      AND A.sku = B.sku
      UNION ALL SELECT DISTINCT AA.Brand_name brand_name,
                                'SEGMENT_REVENUE_COHORT_ANCHOR_BRAND' segment_name,
                                                                      4 segment_number,
                                                                        reseller_email,
                                                                        reseller_mobile,
                                                                        reseller_name
      FROM
        (SELECT Transaction_date2 year_date,
                bucket revenue_cohort,
                Brand_name,
                avg_brand_contribution ,
                avg_payment_amount avg_brand_revenue,
                anchor_brand_name,
                avg_anchor_brand_contribution,
                avg_anchor_payment_amount avg_anchor_brand_revenue
         FROM raena_analytics.anchor_brand_data
         ORDER BY year_date DESC, avg_anchor_brand_contribution DESC) AA
      INNER JOIN
        (SELECT brand_name ,
                bucket,
                reseller_email,
                reseller_mobile ,
                reseller_name
         FROM raena_analytics.gm_dashboard gd
         INNER JOIN
           (SELECT DISTINCT reseller_id ,
                            CASE WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '0 to 2M' WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2M to 10M' WHEN max_payment_amount >10000000 THEN '>10M' END bucket
            FROM
              (SELECT reseller_id,
                      max(payment_amount) max_payment_amount
               FROM
                 (SELECT reseller_id ,
                         date_trunc('Month',transaction_date)::date ,
                         sum(discounted_price*quantity) payment_amount
                  FROM raena_analytics.gm_dashboard gd2
                  GROUP BY 1,
                           2) A
               GROUP BY 1) B)C ON gd.reseller_id = C.reseller_id
         WHERE transaction_date::date BETWEEN '2023-05-01' AND '2023-07-31') BB ON AA.brand_name = BB.brand_name
      AND AA.revenue_cohort = BB.bucket
      UNION ALL SELECT DISTINCT brand_name,
                                'SEGMENT_PERSONALIZED_RECOMMENDATIONS_SIMILAR_USER' segment_name,
                                                                                    5 segment_number,
                                                                                      reseller_email,
                                                                                      reseller_mobile,
                                                                                      name reseller_name
      FROM
        (SELECT reseller_email,
                mobile Reseller_mobile,
                bb.name,
                dr.sku,
                b.name brand_name,
                score match_score,
                after_discount post_discount_gm,
                stock_type,
                pi.current_inventory_ready,
                pipreorder.current_inventory_pre_order,
                p.name product_name
         FROM
           (SELECT *
            FROM raena_recommendation_engine.daily_similar_user_v2
            ORDER BY reseller_email,
                     score DESC) dr
         LEFT JOIN
           ( SELECT id,
                    sku,
                    stock_type,
                    name,
                    brand_id
            FROM raena_catalog_management.product
            WHERE is_archived = 'false'
              AND is_delisted = 'false') p ON p.sku = dr.sku
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_ready
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'READY_SKU'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pi ON pi.product_id = p.id
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
                   max(estimated_arrival_date) AS estimated_arrival_date
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'PRE_ORDER'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
         INNER JOIN
           ( SELECT id,
                    name
            FROM raena_catalog_management.brand) b ON b.id = p.brand_id
         INNER JOIN
           ( SELECT *
            FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
         ORDER BY reseller_email,
                  match_score DESC) AA
      UNION ALL SELECT DISTINCT brand_name,
                                'SEGMENT_PERSONALIZED_RECOMMENDATIONS_SHIPPING_PROVINCE' segment_name,
                                                                                         6 segment_number,
                                                                                           reseller_email,
                                                                                           reseller_mobile,
                                                                                           name reseller_name
      FROM
        (SELECT dr.reseller_email,
                mobile Reseller_mobile,
                bb.name,
                Shipping_Province,
                dr.sku,
                b.name brand_name,
                score match_score,
                after_discount post_discount_gm,
                stock_type,
                pi.current_inventory_ready,
                pipreorder.current_inventory_pre_order,
                p.name product_name
         FROM
           (SELECT *
            FROM raena_recommendation_engine.daily_recommendation_shipping_province
            ORDER BY reseller_email,
                     score DESC) dr
         LEFT JOIN
           (SELECT id,
                   sku,
                   stock_type,
                   name,
                   brand_id
            FROM raena_catalog_management.product
            WHERE is_archived = 'false'
              AND is_delisted = 'false') p ON p.sku = dr.sku
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_ready
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'READY_SKU'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pi ON pi.product_id = p.id
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
                   max(estimated_arrival_date) AS estimated_arrival_date
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'PRE_ORDER'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
         INNER JOIN
           (SELECT id,
                   name
            FROM raena_catalog_management.brand) b ON b.id = p.brand_id
         INNER JOIN
           (SELECT *
            FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
         ORDER BY match_score DESC) AA
      UNION ALL SELECT DISTINCT brand_name,
                                'SEGMENT_PERSONALIZED_RECOMMENDATIONS_REVENUE' segment_name,
                                                                               7 segment_number,
                                                                                 reseller_email,
                                                                                 reseller_mobile,
                                                                                 name reseller_name
      FROM
        (SELECT dr.reseller_email,
                mobile Reseller_mobile,
                bb.name,
                dr.sku,
                b.name brand_name,
                score match_score,
                after_discount post_discount_gm,
                stock_type,
                pi.current_inventory_ready,
                pipreorder.current_inventory_pre_order,
                p.name product_name
         FROM
           ( SELECT *
            FROM raena_recommendation_engine.daily_recommendation_revenue
            ORDER BY reseller_email,
                     score DESC) dr
         LEFT JOIN
           (SELECT id,
                   sku,
                   stock_type,
                   name,
                   brand_id
            FROM raena_catalog_management.product
            WHERE is_archived = 'false'
              AND is_delisted = 'false') p ON p.sku = dr.sku
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_ready
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'READY_SKU'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pi ON pi.product_id = p.id
         LEFT JOIN
           (SELECT product_id,
                   sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
                   max(estimated_arrival_date) AS estimated_arrival_date
            FROM raena_catalog_management.product_inventory
            WHERE is_archived = 'false'
              AND stock_type = 'PRE_ORDER'
              AND (stock_limit - reserve_stock) > 0
            GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
         INNER JOIN
           ( SELECT id,
                    name
            FROM raena_catalog_management.brand) b ON b.id = p.brand_id
         INNER JOIN
           ( SELECT *
            FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
         ORDER BY match_score DESC) A) AA) BB
	 WHERE remove_duplicate_user =1) CC
  where  brand_limit_user <=2000;


update raena_analytics.whatsapp_campaign_base_table 
set reseller_name = name 
from raena_user_management.user 
where reseller_email = email ;


update raena_analytics.whatsapp_campaign_base_table 
set reseller_name = name 
from raena_user_management.user 
where reseller_mobile = mobile 
and reseller_name is null ;


update raena_analytics.whatsapp_campaign_base_table 
set reseller_email = email 
from raena_user_management.user 
where reseller_mobile = mobile 
and reseller_email is null ;