raena_analytics_scripts/postgresql/consignement/consignment_sales_report.sql

drop table raena_analytics.pay_on_delivery_payment_dashboard;

CREATE TABLE raena_analytics.pay_on_delivery_payment_dashboard AS
SELECT A.* ,
       payment_id,
       total_amount,
       payment_status,
       gateway_expiry,
       number_of_day_pending,
       payment_link,
       case when no_of_retries >=2 then 'Yes' else 'No' end offender_reseller
FROM
  (SELECT A.id order_id ,
          A.created_at::date order_created_date ,
          A.status order_status,
          payment_amount,
          B.name Am_name,
          CASE
              WHEN B.email = C."emailid " THEN 'Denny'
              ELSE 'Manthan'
          END Team,
          reseller_info->>'mobile' reseller_mobile,
          reseller_info->>'name' reseller_name
   FROM raena_order_management.order A
   LEFT JOIN raena_user_management.admin_user B ON A.created_by = cast(B.id AS varchar)
   LEFT JOIN raena_analytics.denny_acq_team_member C ON B.email = C."emailid "
   WHERE A.id LIKE 'PL%') A
LEFT JOIN
  (SELECT order_id,
          id AS payment_id ,
          total_amount,
          payment_status,
          gateway_expiry,
	  gateway_expiry::date-CURRENT_DATE::date number_of_day_pending,
                             CASE
                                 WHEN payment_provider = 'DurianPay' THEN payment_details::jsonb->>'invoice_url'
                                 WHEN payment_provider ='Xendit' THEN concat('https://checkout.xendit.co/web/',payment_provider_code)
                             END payment_link,
                             no_of_retries
   FROM
     (SELECT replace(cast(jsonb_array_elements(order_ids) AS varchar),'"','') AS order_id ,
             *
      FROM raena_order_management.payment p) A
   WHERE order_id LIKE '%PL%') B ON A.order_id = B.order_id;

DROP TABLE IF EXISTS raena_analytics.fullfillment_base_data ;


CREATE TABLE raena_analytics.fullfillment_base_data AS
SELECT DISTINCT B.order_id ,
                A.sku ,
                batch,
                shipment_id,
                quantity,
                sum(A.applied_cogs*(CASE WHEN quantity>0 THEN quantity ELSE 1 END))/sum(CASE WHEN quantity>0 THEN quantity ELSE 1 END) applied_cogs,
                A.cogs_type
FROM raena_order_management.fulfillment_detail A
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.shipment_id = B.id
WHERE applied_cogs IS NOT NULL
  AND applied_cogs <>0
GROUP BY 1,
         2,
         3,
         4,
         5,
         7;
  
  
DROP TABLE IF EXISTS raena_analytics.cogs_base_data;


CREATE TABLE raena_analytics.cogs_base_data AS
SELECT sku,
       cogs,
       promo,
       (created_at+interval'7 hours')::date created_at,
       coalesce(lead((created_at+interval'7 hours')::date-interval'1 day' , 1) over (partition BY sku
        ORDER BY sku,created_at),((CURRENT_DATE+interval'7 hours')+interval'1 day'))::date last_date
FROM raena_catalog_management.cogs_audit ca 
ORDER BY 1,
         4;
       
  DROP TABLE raena_analytics.consignment_base_data ;


CREATE TABLE raena_analytics.consignment_base_data AS
SELECT transaction_date,
       paid_status,       
Brand_name ,
       raena_code,
       B.Quantity consignment_quantity,
       B.pkp_cogs,
       batch_no,
       C.order_id ,
       applied_cogs ,
       cogs_type ,
       C.quantity fulfilled_quantity,
       C.shipment_id,
       D.sku,
       D.retail_price,
       D.quantity,
       D.cogs,
       D.effective_cogs,
       D.promo,
       D.payment_amount
FROM raena_erp_management.inbound_order A
INNER JOIN raena_erp_management.inbound_order_sku B ON A.po_number = B.reference_no
INNER JOIN raena_analytics.fullfillment_base_data C ON B.batch_no = C.batch
AND B.raena_code = C.sku
INNER JOIN
  (SELECT A.order_id ,
          date_trunc('Month',A.created_at)::date transaction_date,
          sales_sub_order_shipment_id,
          coalesce(B.sku,A.parent_sku) sku,
          coalesce(B.retail_price,A.retail_price)retail_price ,
          coalesce(B.quantity,A.quantity) quantity,
          coalesce(C.cogs,A.cogs) cogs,
          A.effective_cogs ,
          C.promo,
          coalesce(B.payment_amount,A.payment_amount) payment_amount
   FROM raena_order_management.sales_sub_order A
   LEFT JOIN raena_order_management.sales_sub_order_parent_child B ON A.id = B.sales_sub_order_id
   LEFT JOIN raena_analytics.cogs_base_data C ON B.sku = C.sku
   AND B.product_class = 'Bundle'
   AND B.created_at::date BETWEEN C.created_at AND C.last_date 
   WHERE A.created_at::date >='2023-01-01'
   ORDER BY 1) D ON C.order_id = D.order_id
AND shipment_id = sales_sub_order_shipment_id
AND C.sku = D.sku
WHERE transaction_date>='2023-01-01';


drop table if exists raena_analytics.clevertap_event_data_vishnu_new;

create table raena_analytics.clevertap_event_data_vishnu_new
as
select ts::date ,events, name , email ,phone,event_props::jsonb->>'CT Session Id' sessionid ,
event_props::jsonb->>'item_id' item_id ,
event_props::jsonb->>'isGuestUser' isGuestUser ,
event_props::jsonb->>'CT Source' Source ,
event_props::jsonb->>'id' id ,
event_props::jsonb->>'brandName' brandName ,
event_props::jsonb->>'item_name' item_name ,
event_props::jsonb->>'offenderItems' offenderItems ,
event_props::jsonb->>'items' items ,
event_props::jsonb->>'search_term' search_term ,
event_props::jsonb->>'screen' 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 event_props not like '%OOREDOO%'
and ts::date between current_date+interval'-100 days'  and current_date ;

drop table if exists raena_analytics.clevertap_event_data_vishnu;

create table raena_analytics.clevertap_event_data_vishnu
as
select * from raena_analytics.clevertap_event_data_vishnu_new ;