--------------------------Reseller Business metrics--------------------------

Drop table if exists public.om_app_installed_reseller_business_metrics_db
create table public.om_app_installed_reseller_business_metrics_db
as 
select App_install_date_1 as App_install_date,profile_objectid,profile_phone,profile_email,User_id,tier,flag,r_f,R
from (
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when App_install_date_1 >= app_install_date and App_install_date_1 < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag,
				row_number() over (partition by User_id,date_trunc('month', App_install_date_1) - interval '0 month' 
				order by date_trunc('month', App_install_date_1) - interval '0 month') as R_f,
				row_number() over (partition by User_id order by App_install_date_1) as R
from (
		select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_install_date_1,
		profile_objectid,profile_phone,profile_email,case when profile_phone=' ' then profile_objectid else profile_phone end as user_id
		--coalesce(profile_phone,profile_objectid) as User_id
		from clevertap.app_installed 
) a 
left join om_New_Existing_flag b on a.profile_phone=b.profile_phone and b.r_flag=1
) where R_f=1 

--order table creation
Drop table if exists public.om_order_reseller_business_metrics_db
create table public.om_order_reseller_business_metrics_db
as 
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when created_at >= app_install_date and created_at < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag
from (
select a.*,b.shipping_to
from
(
select replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,cast(created_at as date) as created_at
,count(distinct id) as order_id,sum(payment_amount) as payment_amount
from raena_order_management.order 
where payment_status='Paid' and cast(is_archived as varchar)='false'
group by 1,2
) a
left join 
(
select distinct replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,
row_number() over (partition by replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') order by shipping_to) as R_asc
,shipping_to
from raena_order_management.order
where payment_status='Paid' and cast(is_archived as varchar)='false'
) b on a.mobile=b.mobile and b.R_asc=1
) a 
left join om_New_Existing_flag b on a.mobile=b.profile_phone and b.r_flag=1



--app_uninstalled table creation
Drop table if exists public.om_app_uninstalled_reseller_business_metrics_db
create table public.om_app_uninstalled_reseller_business_metrics_db
as 
select * from (
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when app_uninstall_date >= app_install_date and app_uninstall_date < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag,
				row_number() over (partition by User_id,date_trunc('month', App_uninstall_date) - interval '0 month' 
				order by date_trunc('month', App_uninstall_date) - interval '0 month') as R_f
from (
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_uninstall_date,
profile_phone,profile_objectid  as Uninstall_profile_objectid ,case when profile_phone=' ' then profile_objectid else profile_phone end as user_id 
				from clevertap.app_uninstalled 
) a 
left join om_New_Existing_flag b on a.profile_phone=b.profile_phone and b.r_flag=1
) where R_f=1
				 
--app launched
Drop table if exists public.om_app_launched_reseller_business_metrics_db
create table public.om_app_launched_reseller_business_metrics_db
as 
select * from (
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when App_launch_date >= app_install_date and App_launch_date < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag,
				row_number() over (partition by User_id,date_trunc('month', App_launch_date) - interval '0 month' 
				order by date_trunc('month', App_launch_date) - interval '0 month') as R_f,
				row_number() over (partition by User_id  order by App_launch_date) as R
from (
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_launch_date,
profile_phone,profile_objectid  as launch_profile_objectid ,case when profile_phone=' ' then profile_objectid else profile_phone end as user_id
				from clevertap.app_launched 
) a 
left join om_New_Existing_flag b on a.profile_phone=b.profile_phone and b.r_flag=1
) where R_f=1

--view 
Drop table if exists public.om_product_view_reseller_business_metrics_db
create table public.om_product_view_reseller_business_metrics_db
as 
select * from (
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when App_view_date >= app_install_date and App_view_date < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag,
				row_number() over (partition by User_id,date_trunc('month', App_view_date) - interval '0 month' 
				order by date_trunc('month', App_view_date) - interval '0 month') as R_f
from(
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_view_date,
				profile_objectid as view_object_id,profile_phone,profile_email,case when profile_phone=' ' then profile_objectid else profile_phone end as user_id
from clevertap.view_item vi 
union 
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2)  || '-' || right(left(ts,8),2) as date) as App_view_date,
				profile_objectid as view_object_id,profile_phone,profile_email,case when profile_phone=' ' then profile_objectid else profile_phone end as user_id
from clevertap.view_cart vi 
) a 
left join om_New_Existing_flag b on a.profile_phone=b.profile_phone and b.r_flag=1
) where R_f=1

--view conversion
Drop table if exists public.om_order_reseller_business_metrics_db_view_conversion
create table public.om_order_reseller_business_metrics_db_view_conversion
as 
select distinct a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when created_at >= app_install_date and created_at < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag
from (
select a.*,b.shipping_to
from
(
select distinct replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,cast(created_at as date) as created_at
,ID as order_id, payment_amount
from raena_order_management.order 
where payment_status='Paid' and cast(is_archived as varchar)='false'
) a
left join 
(
select distinct replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,
row_number() over (partition by replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') order by shipping_to) as R_asc
,shipping_to
from raena_order_management.order
where payment_status='Paid' and cast(is_archived as varchar)='false'
) b on a.mobile=b.mobile and b.R_asc=1
) a 
left join om_New_Existing_flag b on a.mobile=b.profile_phone and b.r_flag=1

--Sku order/--Unique sku 
Drop table if exists public.om_sku_reseller_business_metrics_db
create table public.om_sku_reseller_business_metrics_db
as
select a.*,coalesce(b.tier,'Not Present') as tier,case when b.profile_phone isnull then 'New' 
				when created_at >= app_install_date and created_at < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag
from (
select distinct replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as mobile,cast(oi.created_at as date) as created_at,
sku
from raena_order_management.order_item oi
inner join raena_order_management.order o on oi.order_id=o.id 
where o.payment_status='Paid' and cast(o.is_archived as varchar)='false'  
--and replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') !=' '
) a 
left join om_New_Existing_flag b on a.mobile=b.profile_phone and b.r_flag=1

Drop table if exists public.OM_Post_disc_reseller_business_db
create table OM_Post_disc_reseller_business_db
as
select transaction_date,coalesce(b.tier,'Not Present') as tier,
case when b.profile_phone isnull then 'New' 
when transaction_date >= app_install_date and transaction_date < cast(dateadd(day,30,app_install_date) as date) then 'New' else 'Existing' end as flag,
sum(quantity) as quantity,sum(discounted_price) as discounted_price,sum(cogs) as cogs
from OM_GM_DB_Product_category a
left join om_New_Existing_flag b
on replace(a.reseller_mobile,'+','')=b.profile_phone 
and b.R_flag=1
group by 1,2,3