DROP TABLE IF EXISTS public.loyalty_reseller_stage1;

CREATE TABLE public.loyalty_reseller_stage1 AS
SELECT distinct cast(createdat+interval'7 Hours' as date) created_date,
       transactionid,
       eventtype,
       resellerid ,
       email,
       mobile,
       C.name tier_name,
       A.status ,
       points,
       CASE
           WHEN lms_orderid LIKE 'OD%' THEN lms_orderid
       END order_id,
       totalamount
FROM public.lms_transactions A
LEFT JOIN raena_user_management.user B ON A.resellerid = B.id
LEFT JOIN raena_user_management.tier C ON A.tierid = C.id ;



DROP TABLE IF EXISTS public.loyalty_total_orders;



CREATE TABLE public.loyalty_total_orders AS
SELECT cast(A.created_at+interval'7 Hours' as date) AS created_date ,
       A.reseller_id ,
       json_extract_path_text(A.reseller_info,'name',TRUE) reseller_name,
       json_extract_path_text(A.reseller_info,'email',TRUE) reseller_email,
       json_extract_path_text(A.reseller_info,'mobile',TRUE) reseller_mobile,
       json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
       A.payment_status,
       A.id order_id,
       A.status order_status,
       A.payment_amount payment_price
FROM raena_order_management.order A 
where A.created_at ::date >='2022-01-01';


drop table if exists public.loyalty_base_1;


create table  public.loyalty_base_1
as
select A.created_date ,
       A.reseller_id ,
       A.reseller_name,
       A.reseller_email,
       A.reseller_mobile,
       A.reseller_tier_name,
       A.payment_status,
       A.order_id,
       A.payment_price,
       A.order_status,
       B.transactionid,
       B.created_date,
       B.eventtype,
       B.resellerid lm_reseller,
       B.email lm_email,
       B.mobile lm_mobile,
       B.tier_name,
       B.status ,
       B.order_id lm_orders,
       totalamount totalamount,
       points loyalty_point
from  public.loyalty_total_orders  A left join  public.loyalty_reseller_stage1 b 
       on  A.order_id = B.order_id 
       and B.order_id is not null 
union 
select B.created_date,
       B.resellerid,
       'Na',
       B.email,
       B.mobile,
       B.tier_name,
       'Na',
       B.order_id,
       0,
       'Na',
       B.transactionid,
       B.created_date,
       B.eventtype,
       B.resellerid lm_reseller,
       B.email lm_email,
       B.mobile lm_mobile,
       B.tier_name,
       B.status ,
       B.order_id lm_orders,
       totalamount totalamount,
       points loyalty_point
from   public.loyalty_reseller_stage1 B
where  B.order_id is null;