--------------------------Revenue Leakage ------------------------- 
                  
drop table if exists om_clevertap_install_jan_may;


create table 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 '2022-04-01' as App_install_date,email,phone,email as user_id FROM public.clevertap_april_csv
union
SELECT '2022-03-30' as App_install_date,email,phone,email as user_id FROM public.clevertap_march_csv where phone not in (select distinct profile_phone from clevertap.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  
)
) where R=1 ;--and App_install_date>='2022-01-01'

drop table if exists om_clevertap_install_jan_may_2;


create table 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 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 
) 
where R=1 and email notnull) 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
);

--select count(*) from om_clevertap_install_jan_may_2 --494190

drop table if exists om_clevertap_install_jan_may_3;



create table om_clevertap_install_jan_may_3
as
select a.*,cast(b.created_at as date) as transaction_date,transacted_email
--case when cast(b.created_at as date) isnull then 'Never transacted'
--when cast(b.created_at as date)>=app_install_date and cast(b.created_at as date)<=cast(dateadd(day,30,app_install_date) as date) then 'Yes' else 'No' end as transacted_flag
from om_clevertap_install_jan_may_2 a
left join (select json_extract_path_text(reseller_info,'email',TRUE) as transacted_email ,min(created_at) as created_at
		 from raena_order_management.order where payment_status='Paid' and cast(is_archived as varchar)='false' group by 1 ) b 
on a.email=transacted_email;

----select count(*) from om_clevertap_install_jan_may_3 --495191 

drop table if exists om_clevertap_install_jan_may_final;


create table 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<=cast(dateadd(day,30,app_install_date) as date) 
then 'Yes' else 'No' end as transacted_flag
from om_clevertap_install_jan_may_3 ;

--order data
drop table if exists om_clevertap_install_jan_may_order;


create table om_clevertap_install_jan_may_order
as
select * from om_clevertap_install_jan_may_final where transacted_email notnull and rtrim(ltrim(transacted_email)) != '';                  ;

Insert into Test_1_week_om_clevertap_install_jan_may_final
select *,current_date-1 as Report_date
from om_clevertap_install_jan_may_final;

drop table if exists public.om_reseller_info;

create table public.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 public.OM_Order_id_payment_id;

CREATE TABLE public.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;

drop table if exists OM_Events_user_type;

create table OM_Events_user_type
as
select clevertap.clevertap_master_data.*,
CASE WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',first_install_date)::date
                AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
           WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date-date_trunc('Month',first_install_date)::date>= 30
                AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
           WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date-date_trunc('Month',first_install_date)::date>=30
                AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
                else 'Na' end  AS New_existing_flag
from clevertap.clevertap_master_data
left join user_type_table flag ON replace(clevertap.clevertap_master_data.phone,'+','')=flag.reseller_mobile
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');

drop table if exists public.om_order_brand_type;

create table public.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 raena_order_management.sales_sub_order  sso
left join 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;