#!/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 IF EXISTS raena_analytics.om_clevertap_install_jan_may;


CREATE TABLE raena_analytics.om_clevertap_install_jan_may AS
SELECT App_install_date,
       email,
       phone,
       user_id
FROM
  (SELECT *,
          row_number() over (partition BY user_id
                             ORDER BY app_install_date) AS R
   FROM
     (SELECT cast('2022-04-01' as date) AS App_install_date,
             email,
             phone,
             email AS user_id
      FROM raena_analytics.clevertap_april_csv
      UNION SELECT '2022-03-30' AS App_install_date,
                   email,
                   phone,
                   email AS user_id
      FROM raena_analytics.clevertap_march_csv
      WHERE phone NOT IN
          (SELECT DISTINCT profile_phone
           FROM raena_analytics.app_installed)
      UNION 
      SELECT cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) AS date) AS App_install_date,
                   profile_email,
                   profile_phone,
                   CASE WHEN profile_email=' ' THEN profile_objectid ELSE profile_email END AS user_id
      FROM clevertap.app_installed)A)B
WHERE R=1 ;

DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_2;

CREATE TABLE raena_analytics.om_clevertap_install_jan_may_2 AS
SELECT user_id,
       email,
       phone,
       to_char(app_install_date,'month') AS MONTH,
       date_part('DAY',app_install_date) AS DAY,
       address_line1,
       address_line2,
       city,
       province,
       app_install_date
FROM
  (SELECT a.user_id,
          a.email,
          phone,
          CASE
              WHEN app_install_date='2022-03-30'
                   OR app_install_date='2022-04-01' THEN cast(b.created_at AS date)
              ELSE app_install_date
          END AS app_install_date,
          coalesce(address_line1,customer_address_line1) AS address_line1,
          coalesce(address_line2,customer_address_line2) AS address_line2,
          coalesce(city,customer_city) AS city,
          coalesce(province,customer_province) AS province
   FROM raena_analytics.om_clevertap_install_jan_may a
   LEFT JOIN
     (SELECT mobile,
             email,
             created_at,
             address_line1,
             address_line2,
             city,
             province
      FROM
        (SELECT email,
                replace(mobile,'+','') AS mobile,
                cast(created_at AS date) AS created_at,
                address_line1,
                address_line2,
                city,
                province,
                row_number() over (partition BY email
                                   ORDER BY created_at) AS R
         FROM raena_user_management.user)A
      WHERE R=1
        AND email is NOT NULL) b ON a.email=b.email
   AND a.app_install_date<=b.created_at
   LEFT JOIN
     (SELECT DISTINCT user_id,
                      email,
                      address_line1 AS customer_address_line1,
                      address_line2 AS customer_address_line2,
                      city AS customer_city,
                      province AS customer_province,
                      row_number() over (partition BY email
                                         ORDER BY created_at) AS R,
                      cast(created_at AS date) AS created_at
      FROM raena_user_management.customer) c ON a.email=c.email
   AND c.R=1
   AND a.app_install_date<=b.created_at)A;


DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_3;


CREATE TABLE raena_analytics.om_clevertap_install_jan_may_3 AS
SELECT a.*,
       cast(b.created_at AS date) AS transaction_date,
       transacted_email
FROM raena_analytics.om_clevertap_install_jan_may_2 a
LEFT JOIN
  (SELECT reseller_email AS transacted_email ,
          min(transaction_date) AS created_at
   FROM raena_analytics.gm_dashboard
   GROUP BY 1) b ON a.email=transacted_email;

DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_final;


CREATE TABLE raena_analytics.om_clevertap_install_jan_may_final AS
SELECT *,
       CASE
           WHEN email NOTNULL
                AND rtrim(ltrim(email)) != '' THEN 'Yes'
           ELSE 'No'
       END AS email_flag,
       CASE
           WHEN phone NOTNULL
                AND rtrim(ltrim(phone)) != '' THEN 'Yes'
           ELSE 'No'
       END AS phone_flag,
       CASE
           WHEN address_line1 NOTNULL
                AND rtrim(ltrim(address_line1)) != '' THEN 'Yes'
           ELSE 'No'
       END AS address_flag,
       CASE
           WHEN transacted_email NOTNULL
                AND rtrim(ltrim(transacted_email)) != ''
                AND transaction_date>=app_install_date
                AND transaction_date<=app_install_date+interval'30 day' THEN 'Yes'
           ELSE 'No'
       END AS transacted_flag
FROM raena_analytics.om_clevertap_install_jan_may_3 ;

DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_order;


CREATE TABLE raena_analytics.om_clevertap_install_jan_may_order AS
SELECT *
FROM raena_analytics.om_clevertap_install_jan_may_final
WHERE transacted_email NOTNULL
  AND rtrim(ltrim(transacted_email)) != '';             ;

INSERT INTO raena_analytics.Test_1_week_om_clevertap_install_jan_may_final
SELECT *,
       CURRENT_DATE-1 AS Report_date
FROM raena_analytics.om_clevertap_install_jan_may_final;


DROP TABLE IF EXISTS raena_analytics.om_reseller_info;


CREATE TABLE raena_analytics.om_reseller_info AS
SELECT DISTINCT json_extract_path_text(reseller_info,'mobile',true) AS reseller_mobile ,
                json_extract_path_text(reseller_info,'email',true) AS reseller_email,
                reseller_id
FROM raena_order_management.order
WHERE id LIKE '%DSF';


DROP TABLE IF EXISTS raena_analytics.OM_Order_id_payment_id;


CREATE TABLE raena_analytics.OM_Order_id_payment_id AS WITH NS AS
  (SELECT 1 AS n
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 7
   UNION ALL SELECT 8
   UNION ALL SELECT 9
   UNION ALL SELECT 10
   UNION ALL SELECT 11
   UNION ALL SELECT 12
   UNION ALL SELECT 13
   UNION ALL SELECT 14
   UNION ALL SELECT 15
   UNION ALL SELECT 16
   UNION ALL SELECT 17
   UNION ALL SELECT 18
   UNION ALL SELECT 19
   UNION ALL SELECT 20
   UNION ALL SELECT 21
   UNION ALL SELECT 22
   UNION ALL SELECT 23
   UNION ALL SELECT 24
   UNION ALL SELECT 25)
SELECT id,
       max(created_at) created_at,
       replace(cast(substring(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar),2,length(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar))-2) AS varchar),'\"','') AS order_id
FROM NS
INNER JOIN raena_order_management.payment B ON NS.n <= REGEXP_COUNT(B.order_ids , ',') + 1
GROUP BY 1,
         3;

delete from raena_analytics.OM_Events_user_type
where cast(ts as date)>='$reportDate';

insert into raena_analytics.OM_Events_user_type
SELECT A.*,
       CASE
           WHEN date_trunc('Month',A.ts)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',A.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',A.ts)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',A.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',A.ts)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',A.ts)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
           ELSE 'Na'
       END AS New_existing_flag
FROM (select * from clevertap.clevertap_master_data where events in ('add_to_cart','view_item','dsfpage_pdp_view' ,'dsfpage_submit','pdp_setmargin','dsfpage_chatwithseller','dsfpage_buynow',
'share_whatsapp','share_generic','share_facebook','share_tiktok','share_instagram','video_page_play','reviews_show_all','video_page_productclick',
'video_page_instagram','video_page_whatsapp','video_page_generic','video_page_tiktok','video_page_facebook','video_page_play') and ts::date >= '$reportDate') A
LEFT JOIN raena_analytics.user_type_table flag ON replace(A.phone,'+','')=flag.reseller_mobile;


DROP TABLE IF EXISTS raena_analytics.om_order_brand_type;


DROP TABLE IF EXISTS raena_analytics.om_order_brand_type;


CREATE TABLE raena_analytics.om_order_brand_type AS
SELECT DISTINCT sso.order_id,
                coalesce(ssopc.sku,parent_sku) AS sku,
                CASE
                    WHEN b.name IN ('HISTOIRE NATURELLE',
                                    'INGRID',
                                    'W DRESSROOM',
                                    'FORENCOS',
                                    'DEWYCEL',
                                    'GLUTANEX',
                                    'BELLFLOWER',
                                    'ONE THING',
                                    'BEAUSTA') THEN 'EL/PL'
                    WHEN b.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'
                END Brand_type,
                b.name AS brand_name
FROM (select distinct id,order_id,parent_sku from  raena_order_management.sales_sub_order) sso
LEFT JOIN (select distinct sales_sub_order_id,sku from raena_order_management.sales_sub_order_parent_child) ssopc ON sso.id=ssopc.sales_sub_order_id
INNER JOIN raena_catalog_management.product p ON coalesce(ssopc.sku,parent_sku)=p.sku
INNER JOIN raena_catalog_management.brand b ON p.brand_id=b.id;


         " > /home/ec2-user/cronjob/redshift/sql_code/new_user_funnel_etl.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/new_user_funnel_etl.sql