drop table if exists public.sku_type_warehouse;

create table public.sku_type_warehouse
as
select distinct sku , sku_type from public.final_basic_calucation_table;

DROP TABLE IF EXISTS public.warehouse_analysis_stage1 ;


CREATE TABLE public.warehouse_analysis_stage1 AS WITH basetable AS
  (SELECT A.id ,
          CASE
              WHEN origin_area_id= '12416' THEN 'Cikarang'
              WHEN origin_area_id= '21599' THEN 'Semarang'
              WHEN origin_area_id= '30110' THEN 'Surabaya'
              WHEN origin_area_id= '64985' THEN 'Makassar'
              WHEN origin_area_id= '81092' THEN 'Medan'
              WHEN origin_area_id= '36700' THEN 'Samarinda'
          END origin_warehouse ,
              destination_area_id ,
              min(price) price ,
                         B.name partner_name,
                                C.name Service_name
   FROM raena_transport_management.logistic_rate A
   LEFT JOIN raena_transport_management.logistic_partner B ON A.partner_id = B.id
   LEFT JOIN raena_transport_management.logistic_service C ON A.service_id = C.id
   WHERE A.active_status ='true'
   GROUP BY A.id ,
            CASE
                WHEN origin_area_id= '12416' THEN 'Cikarang'
                WHEN origin_area_id= '21599' THEN 'Semarang'
                WHEN origin_area_id= '30110' THEN 'Surabaya'
                WHEN origin_area_id= '64985' THEN 'Makassar'
                WHEN origin_area_id= '81092' THEN 'Medan'
                WHEN origin_area_id= '36700' THEN 'Samarinda'
            END ,
            destination_area_id ,
            B.name ,
            C.name)
SELECT AA.*,
       BB.origin_warehouse expected_warehouse
FROM
  (SELECT A.*,
          B.min_price
   FROM basetable A
   LEFT JOIN
     (SELECT destination_area_id ,
             min(price) min_price,
             partner_name,
             Service_name
      FROM basetable
      GROUP BY destination_area_id ,
               partner_name,
               Service_name) B ON A.destination_area_id=B.destination_area_id
   AND A.partner_name =B.partner_name
   AND A.Service_name= B.Service_name) AA
LEFT JOIN
  (SELECT DISTINCT min_price,
                   service_name,
                   destination_area_id ,
                   origin_warehouse,
                   row_number () over (partition BY min_price,service_name,destination_area_id
                                       ORDER BY min_price ,service_name ,destination_area_id) rnk
   FROM
     (SELECT DISTINCT A.destination_area_id,
                      A.origin_warehouse ,
                      A.service_name,
                      min_price --A.*,B.min_price
FROM basetable A
      INNER JOIN
        (SELECT destination_area_id ,
                min(price) min_price,
                partner_name,
                Service_name
         FROM basetable
         GROUP BY destination_area_id ,
                  partner_name,
                  Service_name) B ON A.destination_area_id=B.destination_area_id
      AND A.partner_name =B.partner_name
      AND A.Service_name= B.Service_name
      AND min_price = Price)AA) BB ON AA.service_name = BB.service_name
AND AA.min_price = BB.min_price
AND AA.destination_area_id= BB.destination_area_id
AND rnk = 1 ;
DROP TABLE IF EXISTS public.warehouse_analysis_stage3;


CREATE TABLE public.warehouse_analysis_stage3 AS
SELECT DISTINCT coalesce(B.order_id) order_id ,
                A.reference_id,
                E.name service_name ,
                E.type service_type , 
                EE.name partner_name , 
                CASE
                    WHEN warehouse_code='WH-CGK45' THEN 'Cikarang'
                    WHEN warehouse_code='WH-SRG18' THEN 'Semarang'
                    WHEN warehouse_code='WH-SUB51' THEN 'Surabaya'
                    WHEN warehouse_code='WH-UPG04' THEN 'Makassar'
                    WHEN warehouse_code='WH-MES07' THEN 'Medan'
                    WHEN warehouse_code='WH-AAP02' THEN 'Samarinda'
                END actual_warehouse,
                B.rate_id,
                sku,
                C.quantity quantity,
                B.shipping_amount shipping_amount,
                C.payment_amount*C.Quantity payment_amount
FROM raena_transport_management.logistic_order A
LEFT JOIN raena_order_management.sales_sub_order_shipment B ON A.reference_id = B.id
LEFT JOIN raena_transport_management.logistic_service E ON A.service_id = E.id
LEFT JOIN raena_transport_management.logistic_partner EE ON A.partner_id = EE.id
LEFT JOIN
  (SELECT AA.order_id ,
          AA.sales_sub_order_shipment_id,
          AA.sku,
          CC.Quantity ,
          CC.discounted_price payment_amount
   FROM
     (SELECT order_id ,
             sales_sub_order_shipment_id,
             coalesce(BB.sku ,parent_sku) sku,
             sum(coalesce(BB.quantity , AA.quantity)) Quantity
      FROM raena_order_management.sales_sub_order AA
      LEFT JOIN raena_order_management.sales_sub_order_parent_child BB ON AA.id = BB.sales_sub_order_id
      GROUP BY 1,
               2,
               3) AA
   LEFT JOIN
     (SELECT DISTINCT external_id ,
                      product_sku ,
                      quantity,
                      discounted_price
      FROM base_netsuite_final) CC ON AA.order_id = CC.external_id
   AND AA.sku = CC.product_sku  and AA.Quantity= CC.quantity
   ) C ON A.reference_id = C.sales_sub_order_shipment_id
WHERE cast(A.created_at AS date)>='2022-04-01'
  AND A.status IN ('RETURNED',
                   'DELIVERED',
                   'PICKED_UP',
                   'CREATED');

                  
DROP TABLE IF EXISTS public.warehouse_analysis_stage3_final_v1;


CREATE TABLE public.warehouse_analysis_stage3_final_v1 AS
SELECT A.reference_id AS Order_id ,
       reference_id ,
       service_name ,
       service_type , 
       partner_name , 
       'Cikarang' actual_warehouse ,
                  -1 rate_id ,
                     product_sku sku ,
                     B.quantity ,
                     B.shipping_cost shipping_amount ,
                     B.discounted_price*B.quantity payment_amount
FROM public.warehouse_analysis_stage3 A
LEFT JOIN base_netsuite_final B ON A.reference_id = B.external_id
WHERE A.order_id IS NULL
UNION
SELECT *
FROM public.warehouse_analysis_stage3
WHERE order_id IS NOT NULL;


DROP TABLE IF EXISTS public.warehouse_analysis_stage3_final;



CREATE TABLE public.warehouse_analysis_stage3_final AS
SELECT *
FROM public.warehouse_analysis_stage3_final_v1
WHERE order_id NOT IN
    (SELECT DISTINCT id
     FROM raena_order_management.order
     WHERE is_campaign='true')
  AND payment_amount IS NOT NULL
UNION
SELECT A.Order_id,
       reference_id,
        service_name ,
       service_type , 
       partner_name , 
       actual_warehouse,
       rate_id,
       A.sku ,
       A.quantity,
       shipping_amount,
       B.payment_price
FROM public.warehouse_analysis_stage3_final_v1 A
INNER JOIN
  (SELECT order_id ,
          sales_sub_order_shipment_id,
          coalesce(BB.sku ,parent_sku) sku,
          sum(coalesce(BB.quantity , AA.quantity)) Quantity,
          sum(coalesce(BB.payment_amount , AA.payment_amount)) payment_price
   FROM raena_order_management.sales_sub_order AA
   LEFT JOIN raena_order_management.sales_sub_order_parent_child BB ON AA.id = BB.sales_sub_order_id
   WHERE order_id IN
       (SELECT DISTINCT id
        FROM raena_order_management.order
        WHERE is_campaign='true')
   GROUP BY 1,
            2,
            3) B ON A.order_id = B.order_id
AND A.reference_id= B.sales_sub_order_shipment_id
AND A.sku = B.sku;

DROP TABLE IF EXISTS public.warehouse_analysis_stage1_1;

CREATE TABLE public.warehouse_analysis_stage1_1 AS
SELECT DISTINCT Z.id order_id ,
                A.reference_id,
                 service_name ,
                service_type , 
                partner_name , 
                actual_warehouse,
                A.rate_id,
                A.sku,
                A.quantity,
                A.shipping_amount,
                A.payment_amount,
                payment_status ,
                Z.status order_status,
                case when Z.provider='dsf' then 'DSF' 
                     when Z.provider='rdash360' then 'RDash'
                     when Z.order_placed_by='merchant' then 'Merchant'
                     when Z.order_placed_by='admin' then 'Admin Panel' 
                     when Z.order_placed_by='reseller' then 'App' end order_placed_by,
                case 
                     when Z.is_campaign='true' then 'Campaign Order'
                     when Z.is_consignment='true' then 'Consignment Order'
                     when Z.provider='dsf' then 'DSF Order'
                     when Z.order_placed_by='merchant' then 'Merchant Order'
                     when D.name is not null then 'Channel Order'
                         else 'Reseller Order' end order_type,     
                Z.created_at,
                z.is_campaign,
                Z.created_by
FROM raena_order_management.order Z
INNER JOIN public.warehouse_analysis_stage3_final A ON Z.id=A.order_id
left join raena_order_management.channel D on Z.channel_id = D.id
WHERE Z.payment_status ='Paid'
  AND cast(Z.created_at AS date)>='2022-04-01';


DROP TABLE IF EXISTS public.warehouse_analysis_stage1_2;


CREATE TABLE public.warehouse_analysis_stage1_2 AS
SELECT DISTINCT Z.id order_id ,
                BB.sku sku,
                BB.quantity quantity,
                --BB.shipping_cost shipping_amount,
		case when  BB.external_id = B.order_id
and BB.sku = B.sku
and (case when BB.item_type like '%Bundle%' then 'Bundle' else 'Product' end )=B.product_class
then B.new_shipment_amount else C.new_shipment_amount end shipping_amount,
                BB.Brand_name AS Brand_name ,
                shipping_to shipping_to,
                BB.discounted_price*BB.quantity payment_amount,
                payment_status ,
                Z.status order_status,
                case when Z.provider='dsf' then 'DSF' 
                     when Z.provider='rdash360' then 'RDash'
                     when Z.order_placed_by='merchant' then 'Merchant'
                     when Z.order_placed_by='admin' then 'Admin Panel' 
                     when Z.order_placed_by='reseller' then 'App' end order_placed_by,
                case 
                     when Z.is_campaign='true' then 'Campaign Order'
                     when Z.is_consignment='true' then 'Consignment Order'
                     when Z.provider='dsf' then 'DSF Order'
                     when Z.order_placed_by='merchant' then 'Merchant Order'
                     when D.name is not null then 'Channel Order'
                         else 'Reseller Order' end order_type,     
                Z.created_at,
                z.is_campaign,
                Z.created_by
FROM raena_order_management.order Z
INNER JOIN public.gm_dashboard BB ON Z.id = BB.external_id
left join public.sku_level_shipping_fee_final B
on BB.external_id = B.order_id
and BB.sku = B.sku
and (case when BB.item_type like '%Bundle%' then 'Bundle' else 'Product' end )=B.product_class
left join public.sku_level_shipping_fee_old_final C
on BB.external_id = C.external_id
and BB.sku = C.sku
and (case when BB.item_type like '%Bundle%' then 'Bundle' else 'Product' end )=C.product_class
left join raena_order_management.channel D on Z.channel_id = D.id
WHERE Z.payment_status ='Paid'
  AND cast(Z.created_at AS date)>='2022-04-01'
  AND BB.external_id NOT IN
    (SELECT order_id
     FROM public.warehouse_analysis_stage3_final) ;




DROP TABLE IF EXISTS public.warehouse_analysis_stage1_3_v1;


CREATE TABLE public.warehouse_analysis_stage1_3_v1 AS
SELECT Z.id AS order_id ,
       A.reference_id,
      A.service_name ,
       A.service_type , 
       A.partner_name , 
       A.actual_warehouse,
       A.rate_id,
       coalesce(A.sku,B.sku) sku,
       coalesce(A.quantity,B.quantity) quantity,
       coalesce(A.shipping_amount,B.shipping_amount) shipping_amount,
       Z.shipping_to shipping_to,
       coalesce(A.payment_amount,B.payment_amount) payment_amount,
       Z.payment_status ,
       Z.status order_status,
       case when Z.provider='dsf' then 'DSF' 
                     when Z.provider='rdash360' then 'RDash'
                     when Z.order_placed_by='merchant' then 'Merchant'
                     when Z.order_placed_by='admin' then 'Admin Panel' 
                     when Z.order_placed_by='reseller' then 'App' end order_placed_by,
       case 
                     when Z.is_campaign='true' then 'Campaign Order'
                     when Z.is_consignment='true' then 'Consignment Order'
                     when Z.provider='dsf' then 'DSF Order'
                     when Z.order_placed_by='merchant' then 'Merchant Order'
                     when D.name is not null then 'Channel Order'
                         else 'Reseller Order' end order_type,
       Z.created_at,
       z.is_campaign,
       Z.created_by
FROM raena_order_management.order Z
LEFT JOIN public.warehouse_analysis_stage1_1 A ON Z.id = A.order_id
LEFT JOIN public.warehouse_analysis_stage1_2 B ON Z.id = B.order_id
left join raena_order_management.channel D on Z.channel_id = D.id
WHERE Z.payment_status ='Paid'
  AND cast(Z.created_at AS date)>='2022-04-01';



DROP TABLE IF EXISTS public.warehouse_analysis_stage1_3;


CREATE TABLE public.warehouse_analysis_stage1_3 AS
SELECT order_id,
       A.reference_id,
       service_name ,
       service_type , 
       partner_name , 
       actual_warehouse,
       rate_id,
       sku,
       quantity,
       case when b.shipping_amount is not null then ((b.shipping_amount)/(b.order_weight))*(b.final_weight) else A.shipping_amount end  shipping_amount,
       shipping_to,
       payment_amount,
       A.payment_status,
       order_status,
       order_placed_by,
       order_type,
       created_at,
       is_campaign,
       created_by
FROM public.warehouse_analysis_stage1_3_v1 A
LEFT JOIN OM_Logistic_final_shipping B ON A.reference_id= B.reference_id;



DROP TABLE IF EXISTS public.warehouse_analysis_stage2_v1_final_weight;


CREATE TABLE public.warehouse_analysis_stage2_v1_final_weight AS
SELECT order_id , reference_id , service_name ,
       service_type , 
       partner_name ,
       actual_warehouse,
       CASE
           WHEN (sum(B.Final_weight*A.Quantity))<1.3 THEN 1
           WHEN (sum(B.Final_weight*A.Quantity))>=1.3
                AND (ABS(sum(B.Final_weight*A.Quantity)) - FLOOR(ABS(sum(B.Final_weight*A.Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(sum(B.Final_weight*A.Quantity))+1
           ELSE FLOOR(sum(B.Final_weight*A.Quantity))
       END AS sku_weight
FROM public.warehouse_analysis_stage1_3 A
LEFT JOIN
  (SELECT sku ,
          B.name brand_name,
          CASE
              WHEN height*width*LENGTH/6000 > weight THEN height*width*LENGTH/6000
              ELSE weight
          END Final_weight,
          height*width*LENGTH/6000 vw,
                                   weight
   FROM raena_catalog_management.product A
   LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id) B ON A.sku = B.sku
  group by 1,2,3,4,5,6;
 
 
 DROP TABLE IF EXISTS public.warehouse_analysis_stage2_v1;


CREATE TABLE public.warehouse_analysis_stage2_v1 AS
SELECT A.*,
       B.brand_name,
       B.Final_weight*sku_weight/sum(B.Final_weight) over(partition by A.order_id , A.reference_id ) Final_weight
FROM public.warehouse_analysis_stage1_3 A
left join public.warehouse_analysis_stage2_v1_final_weight C on A.order_id = C.order_id and A.reference_id = C.reference_id 
LEFT JOIN
  (SELECT sku ,
          B.name brand_name,
          CASE
              WHEN height*width*LENGTH/6000 > weight THEN height*width*LENGTH/6000
              ELSE weight
          END Final_weight,
          height*width*LENGTH/6000 vw,
                                   weight
   FROM raena_catalog_management.product A
   LEFT JOIN raena_catalog_management.brand B ON A.brand_id = B.id) B ON A.sku = B.sku;


drop table if exists public.calculated_ninja_jne_oct_shipping_fee;

CREATE TABLE public.calculated_ninja_jne_oct_shipping_fee AS
SELECT order_id ,
       reference_id ,
       service_name ,
       partner_id,
       sum(Calculated_shipping_fee+insurance_fee) Calculated_shipping_fee
FROM
  (SELECT order_id ,
          A.reference_id ,
          service_name ,
          C.partner_id,
          B.price*final_weight Calculated_shipping_fee ,
          B.price,
          final_weight,
          (B.price*final_weight)*(CASE WHEN C.partner_id = 5 THEN C.insurance_fee ELSE C.insurance_fee END) /sum(B.price*final_weight)over(partition BY A.reference_id) AS insurance_fee
   FROM
     (SELECT order_id ,
             reference_id ,
             rate_id,
             service_name ,
             created_at,
             sum(final_weight) final_weight
      FROM public.warehouse_analysis_stage2_v1
      GROUP BY 1,
               2,
               3,
               4,
               5) A
   LEFT JOIN raena_transport_management.logistic_rate B ON A.rate_id = B.id
   LEFT JOIN
     (SELECT DISTINCT reference_id ,
                      insurance_fee,
                      partner_id ,
                      service_id
      FROM raena_transport_management.logistic_order
      WHERE status IN ('RETURNED',
                       'DELIVERED',
                       'PICKED_UP',
                       'CREATED')) C ON A.reference_id=C.reference_id
   WHERE A.created_at::date BETWEEN '2022-10-01' AND '2022-10-14')
GROUP BY 1,
         2,
         3,
         4;

        
        
 DROP TABLE IF EXISTS public.warehouse_analysis_stage2;


CREATE TABLE public.warehouse_analysis_stage2 AS
SELECT A.*,
       (final_weight*coalesce(Calculated_shipping_fee,shipping_amount))/sum(final_weight) over(partition BY A.reference_id) new_shipment_amount
FROM public.warehouse_analysis_stage2_v1 A
left join public.calculated_ninja_jne_oct_shipping_fee B on A.order_id = B.order_id and A.reference_id = B.reference_id ;

select count(1) from public.warehouse_analysis_stage2 ;


DROP TABLE IF EXISTS public.shipping_fee_coupon_purpose;


CREATE TABLE public.shipping_fee_coupon_purpose AS
SELECT O.order_id ,
       C.sub_order_id,
       payment_status,
       order_status,
       sub_order_status,
       sub_order_shipment_status,
       tierName,
       payment_amount,
       shipping_amount AS shipping_fee,
       applied_shipping_amount AS applied_shipping_fee,
       shipping_to shipping_to,
       order_placed_by,
       order_type,
       email,
       mobile,
       CASE
           WHEN D.min_date = O.Created_at
                AND O.reseller_id = D.reseller_id THEN 'New'
           ELSE 'Old'
       END User_type ,
       province,
       created_at
FROM
  (SELECT A.id AS order_id ,
          reseller_id,
          json_extract_path_text(A.reseller_info,'tierName',TRUE) tierName,
          json_extract_path_text(A.reseller_info,'email',TRUE) email,
          json_extract_path_text(A.reseller_info,'mobile',TRUE) mobile,
          lower(shipping_province) province,
          payment_status,
          status order_status,
          shipping_to ,
          case when provider='dsf' then 'DSF' 
                     when provider='rdash360' then 'RDash'
                     when order_placed_by='merchant' then 'Merchant'
                     when order_placed_by='admin' then 'Admin Panel' 
                     when order_placed_by='reseller' then 'App' end order_placed_by,
       case 
                     when is_campaign='true' then 'Campaign Order'
                     when is_consignment='true' then 'Consignment Order'
                     when provider='dsf' then 'DSF Order'
                     when order_placed_by='merchant' then 'Merchant Order'
                     when D.name is not null then 'Channel Order'
                         else 'Reseller Order' end order_type,
          A.created_at
   FROM raena_order_management.order A
   left join raena_order_management.channel D on A.channel_id = D.id
   WHERE A.is_archived = 'false'
     AND cast(A.created_at AS date)>='2022-04-01'
   ORDER BY A.created_at DESC) O
LEFT JOIN
  (SELECT id sub_order_shipping_id,
          order_id,
          shipping_amount,
          applied_shipping_amount,
          status sub_order_shipment_status
   FROM raena_order_management.sales_sub_order_shipment
   ORDER BY 2) B ON O.order_id = B.order_id
LEFT JOIN
(SELECT id sub_order_id ,
          order_id,
          A.status sub_order_status,
          sales_sub_order_shipment_id ,
          B.discounted_price*B.quantity payment_amount
   FROM raena_order_management.sales_sub_order A
   INNER JOIN public.business_report B ON A.order_id = B.external_id
   AND A.parent_sku = B.sku
   ORDER BY 2,
            4 DESC) C ON B.sub_order_shipping_id= C.sales_sub_order_shipment_id
LEFT JOIN
  (SELECT reseller_id,
          min(created_at) min_date
   FROM raena_order_management.
   ORDER
   WHERE payment_status= 'Paid'
     AND is_archived = 'false'
   GROUP BY reseller_id) D ON O.reseller_id = D.reseller_id
ORDER BY o.created_at DESC;

select count(1) from public.shipping_fee_coupon_purpose;

DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse ;


CREATE TABLE public.rate_sheet_wise_expected_warehouse AS
SELECT order_id ,
       reference_id Sub_order_shipping_id,
       sku ,
        case when actual_warehouse is null then 'Cikarang' else actual_warehouse end actual_warehouse ,
       case when expected_warehouse is null then 'Cikarang' else expected_warehouse end expected_warehouse,
       CASE
           WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
           ELSE 'No'
       END AS missed_Inventory,
       service_name ,
       service_type , 
       partner_name ,     
       shipping_to,
       payment_amount,
       payment_status ,
       order_status,
       order_placed_by,
       order_type,
       am_name,
       am_email,
       sum(quantity)quantity,
       sum(new_shipment_amount) shipping_amount,
       count(order_id) number_of_order,
       brand_name,
       tierName,
       email,
       mobile ,
       province ,
       User_type,
       is_campaign,
       cast((created_at+interval'7 Hours') AS date) AS created_date,
        DSF_flag,
       brand_type
FROM
  (
 SELECT DISTINCT A.order_id ,
                   reference_id,
                     A.service_name ,
                   A.service_type , 
                   A.partner_name ,   
                   actual_warehouse,
                   rate_id,
                   A.sku,
                   quantity,
		   coalesce(new_shipment_amount,shipping_amount)new_shipment_amount,
                   expected_warehouse,
                   coalesce(Y.Name,A.Brand_name) AS Brand_name ,
                   shipping_to,
                   payment_amount,
                   payment_status ,
                   order_status,
                   order_placed_by,
                   order_type,
                   AA.name am_name,
                   AA.email am_email,
                   tierName,
                   DD.email,
                   mobile ,
                   province ,
                   User_type,
                   is_campaign,
                   A.created_at,
                   case when A.order_id like '%DSF%' then 'Yes' Else 'No' end DSF_flag,
       CASE
 WHEN coalesce(Y.Name,A.Brand_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'
           WHEN coalesce(Y.Name,A.Brand_name) IN ('W DRESSROOM',
                               'BEAUSTA',
                               'Dewycel',
                               'GLUTANEX',
                               'HISTOIRE NATURELLE',
                               'FORENCOS',
                               'BELLFLOWER',
                               'MAXCLINIC'
                               'INGRID',
                               'ONE THING',
                               'ITFER') THEN 'EL/PL'
       END brand_type
       FROM public.warehouse_analysis_stage2 A
   LEFT JOIN
     (SELECT DISTINCT order_id ,
                      tierName,
                      email,
                      mobile ,
                      province ,
                      User_type
      FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id
   LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar)
   LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku
   LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id
   LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id
   AND A.service_name = D.service_name)AA
WHERE sku IS NOT NULL
GROUP BY order_id ,
         reference_id,
         sku ,
          case when actual_warehouse is null then 'Cikarang' else actual_warehouse end  ,
       case when expected_warehouse is null then 'Cikarang' else expected_warehouse end ,
       CASE
           WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
           ELSE 'No'
       END ,
         service_name ,
       service_type , 
       partner_name ,     
         shipping_to,
         payment_amount,
         payment_status ,
         order_status,
         order_placed_by,
         order_type,
         am_name,
         am_email,
         brand_name,
         tierName,
         email,
         mobile ,
         province ,
         User_type,
         is_campaign,
         cast((created_at+interval'7 Hours') AS date),
          DSF_flag,
       brand_type;

update public.rate_sheet_wise_expected_warehouse
        set payment_amount =0
        from public.gm_dashboard
        where discounted_price= 0
        and public.rate_sheet_wise_expected_warehouse.order_id =  public.gm_dashboard.external_id
        and public.rate_sheet_wise_expected_warehouse.sku =  public.gm_dashboard.sku ;


   select count(1) from public.rate_sheet_wise_expected_warehouse ;

DROP TABLE IF EXISTS quantity_trend_new;


CREATE TABLE quantity_trend_new AS
SELECT is_campaign ,
       frequency ,
       transaction_date ,
       child_sku ,
       warehouse ,
       quantity
FROM quantity_trend_v1
WHERE transaction_date <'2022-04-01'
UNION
SELECT is_campaign ,
       'Month',
       date_trunc('Month',created_date) :: date ,
       sku ,
       actual_warehouse,
       sum(quantity)
FROM public.rate_sheet_wise_expected_warehouse
GROUP BY 1,
         2,
         3,
         4,
         5;


DROP TABLE IF EXISTS  quantity_trend;


CREATE TABLE quantity_trend AS
SELECT *
FROM quantity_trend_new;

 ;


DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse_expected_shipping;


CREATE TABLE public.rate_sheet_wise_expected_warehouse_expected_shipping AS
SELECT order_id ,
       reference_id Sub_order_shipping_id,
       sku ,
        case when actual_warehouse is null then 'Cikarang' else actual_warehouse end actual_warehouse ,
       case when expected_warehouse is null then 'Cikarang' else expected_warehouse end expected_warehouse,
       CASE
           WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
           ELSE 'No'
       END AS missed_Inventory,
       service_name ,
       shipping_to,
       payment_amount,
       payment_status ,
       order_status,
       order_placed_by,
       order_type,
       am_name,
       am_email,
       sum(quantity)quantity,
       sum(new_shipment_amount) shipping_amount,
       sum(price) Per_kg_cost,
       count(order_id) number_of_order,
       brand_name,
       tierName,
       email,
       mobile ,
       province ,
       User_type,
       cast(created_at AS date) AS created_date
FROM
(SELECT DISTINCT A.order_id ,
                   reference_id,
                   A.service_name ,
                   actual_warehouse,
                   rate_id,
                   A.sku,
                   quantity,
                   new_shipment_amount,
                   expected_warehouse,
                   coalesce(Y.Name,A.Brand_name) AS Brand_name ,
                   shipping_to,
                   payment_amount,
                   D.price,
                   payment_status ,
                   order_status,
                   order_placed_by,
                   order_type,
                   AA.name am_name,
                   AA.email am_email,
                   tierName,
                   DD.email,
                   mobile ,
                   province ,
                   User_type,
                   A.created_at
   FROM public.warehouse_analysis_stage2 A
   LEFT JOIN
     (SELECT DISTINCT order_id ,
                      tierName,
                      email,
                      mobile ,
                      province ,
                      User_type
      FROM public.shipping_fee_coupon_purpose) DD ON A.order_id = DD.order_id
   LEFT JOIN raena_user_management.admin_user AA ON cast(A.created_by AS varchar) = cast(AA.id AS varchar)
   LEFT JOIN raena_catalog_management.product X ON A.sku = X.sku
   LEFT JOIN raena_catalog_management.brand Y ON X.brand_id =Y.id
   LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id
   AND A.service_name = D.service_name)AA
WHERE sku IS NOT NULL
GROUP BY order_id ,
         reference_id,
         sku ,
          case when actual_warehouse is null then 'Cikarang' else actual_warehouse end  ,
       case when expected_warehouse is null then 'Cikarang' else expected_warehouse end ,
       CASE
           WHEN actual_warehouse <>expected_warehouse THEN 'Yes'
           ELSE 'No'
       END ,
          service_name ,
         shipping_to,
         payment_amount,
         payment_status ,
         order_status,
         order_placed_by,
         order_type,
         am_name,
         am_email,
         brand_name,
         tierName,
         email,
         mobile ,
         province ,
         User_type,
         cast(created_at AS date);

         select count(1) from public.rate_sheet_wise_expected_warehouse_expected_shipping ;

DROP TABLE IF EXISTS om_distr_actaul_origin;


CREATE TABLE om_distr_actaul_origin AS
SELECT DISTINCT sub_order_shipping_id,
                service_name,
                actual_warehouse,
                expected_warehouse,
                order_weight,
                B.partner_id,
                B.service_id,
                rate_id,
                destination_area_id,
                origin_area_id AS actual_origin,
                CASE
                    WHEN expected_warehouse='Cikarang' THEN 12416
                    WHEN expected_warehouse='Semarang' THEN 21599
                    WHEN expected_warehouse='Surabaya' THEN 30110
                    WHEN expected_warehouse='Makassar' THEN 64985
                    WHEN expected_warehouse='Medan' THEN 81092
                    WHEN expected_warehouse='Samarinda' THEN 36700
                END AS expected_origin
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
INNER JOIN raena_transport_management.logistic_order b ON a.sub_order_shipping_id=b.reference_id
LEFT JOIN raena_transport_management.logistic_rate c ON b.rate_id=c.id;


DROP TABLE IF EXISTS om_distr_actaul_origin_2;


CREATE TABLE om_distr_actaul_origin_2 AS
SELECT DISTINCT sub_order_shipping_id,
                actual_warehouse,
                expected_warehouse,
                c.price,
                c.eta_min
FROM om_distr_actaul_origin b
INNER JOIN raena_transport_management.logistic_rate c ON b.expected_origin=c.origin_area_id
AND b.destination_area_id=c.destination_area_id
and b.service_id = c.service_id
and b.partner_id = c.partner_id
INNER JOIN raena_transport_management.logistic_service s ON s.id=c.service_id
AND s.name=b.service_name
WHERE rate_id NOTNULL
  AND actual_warehouse!=expected_warehouse;


DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse;


CREATE TABLE public.OM_Logistic_base_exepected_warehouse AS
SELECT DISTINCT cast(lo.created_at AS date) AS created_at,
                reference_id,
                order_weight,
                lo.shipping_amount,
                rate_id,
                lr.price AS Shipping_fee_per_kg_weight,
                lr.eta_min AS expected_eta_min
FROM raena_transport_management.logistic_order lo
INNER JOIN om_distr_actaul_origin_2 lr ON lo.reference_id=lr.sub_order_shipping_id
WHERE lo.created_at >= '2022-04-01'
  AND rate_id IS NOT NULL;

 --order_id for suborders

DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_2;


CREATE TABLE public.OM_Logistic_base_exepected_warehouse_2 AS
SELECT created_at,
       reference_id,
       order_weight,
       shipping_amount,
       rate_id,
       shipping_fee_per_kg_weight,
       expected_eta_min,
       coalesce(order_id,reference_id) AS order_id,
       sku,
       payment_amount,
       sales_sub_order_id,
       quantity
FROM
  (SELECT a.*,
          b.order_id,
          b.parent_sku AS sku,
          b.payment_amount ,
          b.id AS sales_sub_order_id,
          b.quantity
   FROM public.OM_Logistic_base_exepected_warehouse a
   LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id) A;

 ---sku tagging

DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_3;


CREATE TABLE public.OM_Logistic_base_exepected_warehouse_3 AS
SELECT DISTINCT a.*,
                coalesce(b.sku,a.sku) AS new_sku,
                coalesce(b.quantity,a.quantity) AS new_quantity
FROM public.OM_Logistic_base_exepected_warehouse_2 a
LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id;


DROP TABLE IF EXISTS public.OM_Logistic_base_exepected_warehouse_4;



CREATE TABLE public.OM_Logistic_base_exepected_warehouse_4 AS
SELECT Created_at,
       order_id,
       reference_id,
       sales_sub_order_id,
       new_quantity AS quantity,
       order_weight,
       shipping_amount,
       shipping_fee_per_kg_weight,
       expected_eta_min,
       new_sku,
       payment_amount,
       b.weight AS actual_weight,
       b.volume_weight
FROM public.OM_Logistic_base_exepected_warehouse_3 a
LEFT JOIN
  (SELECT sku,
          id,
          weight,
          (height*width*LENGTH)/6000 AS volume_weight
   FROM raena_catalog_management.product) b ON a.new_sku=b.sku
WHERE reference_id LIKE 'SH%'
ORDER BY 2;

 --final dataset

DROP TABLE IF EXISTS public.OM_Logistic_final_shipping_exepected_warehouse_base;


CREATE TABLE public.OM_Logistic_final_shipping_exepected_warehouse_base AS
SELECT Created_at,
       order_id,
       reference_id,
       paymnet_amount,
       shipping_amount,
       Shipping_fee_per_kg_weight,
       expected_eta_min,
       CASE
           WHEN order_weight<1.3 THEN 1
           WHEN order_weight>=1.3
                AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1
           ELSE FLOOR(order_weight)
       END AS order_weight,
       CASE
           WHEN compared_weight<1.3 THEN 1
           WHEN compared_weight>=1.3
                AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1
           ELSE FLOOR(compared_weight)
       END AS final_weight
FROM
(SELECT *,
          CASE
              WHEN volume_weight>actual_weight THEN volume_weight
              WHEN volume_weight<actual_weight THEN actual_weight
              WHEN volume_weight=actual_weight THEN actual_weight
          END AS compared_weight
   FROM
     (SELECT Created_at,
             order_id,
             reference_id,
             shipping_amount,
             order_weight,
             Shipping_fee_per_kg_weight,
             expected_eta_min,
             (sum(quantity*actual_weight)) AS actual_weight,
             (sum(quantity*volume_weight)) AS volume_weight,
             sum(payment_amount) AS paymnet_amount
      FROM public.OM_Logistic_base_exepected_warehouse_4
      GROUP BY 1,
               2,
               3,
               4,
               5,
               6,
               7))
ORDER BY final_weight DESC;


select count(1) from public.OM_Logistic_final_shipping_exepected_warehouse_base  ;

DROP TABLE IF EXISTS public.OM_Logistic_final_shipping_exepected_warehouse;


CREATE TABLE public.OM_Logistic_final_shipping_exepected_warehouse AS
SELECT DISTINCT reference_id,
                final_weight AS expected_warehouse_weight,
                Shipping_fee_per_kg_weight*final_weight AS Expected_shipping_fee,
                expected_eta_min
FROM public.OM_Logistic_final_shipping_exepected_warehouse_base a;

 --select count(*),count(distinct reference_id) from public.OM_Logistic_final_shipping_exepected_warehouse

DROP TABLE IF EXISTS public.rate_sheet_wise_expected_warehouse_expected_shipping_final_v11;


CREATE TABLE public.rate_sheet_wise_expected_warehouse_expected_shipping_final_v11 AS
SELECT DISTINCT a.Sub_order_shipping_id,
                (b.Expected_shipping_fee+(case when o.partner_id = 5 then insurance_fee/100 else insurance_fee end )) AS Expected_shipping_fee,
                expected_eta_min,
                lr.eta_min AS actual_eta_min,
                o.order_weight
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
LEFT JOIN public.OM_Logistic_final_shipping_exepected_warehouse b ON a.Sub_order_shipping_id=b.reference_id
INNER JOIN raena_transport_management.logistic_order o ON a.Sub_order_shipping_id=o.reference_id
INNER JOIN raena_transport_management.logistic_rate lr ON o.rate_id=lr.id
WHERE Sub_order_shipping_id IS NOT NULL;


drop table if exists public.rate_sheet_wise_expected_warehouse_expected_shipping_final;

create table public.rate_sheet_wise_expected_warehouse_expected_shipping_final
as
select A.*,AA.new_sku sku,compared_weight sku_weight ,sum((compared_weight/order_weight*expected_shipping_fee)) sku_level_expected_shipping_fee
from public.rate_sheet_wise_expected_warehouse_expected_shipping_final_v11 A
left join
(SELECT order_id , sales_sub_order_id , reference_id ,new_sku,
          CASE
              WHEN new_volume_weight>new_actual_weight THEN new_volume_weight
              WHEN new_volume_weight<new_actual_weight THEN new_actual_weight
              WHEN new_volume_weight=new_actual_weight THEN new_actual_weight
          END AS compared_weight
   FROM
     (SELECT *,(quantity*actual_weight) AS new_actual_weight,
             (quantity*volume_weight) AS new_volume_weight
      FROM public.OM_Logistic_base_exepected_warehouse_4
      )) AA on A.sub_order_shipping_id = AA.reference_id
      group by 1,2,3,4,5,6,7;


DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_2;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_2 AS
SELECT sub_order_shipping_id,
       actual_warehouse,
       expected_warehouse,
       b.eta_min AS actual__JTR_min_eta,
       b.price AS actual_JTR_per_kg_price
FROM public.om_distr_actaul_origin a
INNER JOIN raena_transport_management.logistic_rate b ON a.actual_origin=b.origin_area_id
AND a.destination_area_id=b.destination_area_id
WHERE b.service_id=2;


DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_3;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_3 AS
SELECT sub_order_shipping_id,
       actual_warehouse,
       expected_warehouse,
       b.eta_min AS expected__JTR_min_eta,
       b.price AS expected_JTR_per_kg_price
FROM public.om_distr_actaul_origin a
INNER JOIN raena_transport_management.logistic_rate b ON a.expected_origin=b.origin_area_id
AND a.destination_area_id=b.destination_area_id
WHERE b.service_id=2;



DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_4;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_4 AS
SELECT a.*,
       b.actual__JTR_min_eta,
       b.actual_JTR_per_kg_price,
       c.expected__JTR_min_eta,
       c.expected_JTR_per_kg_price
FROM om_distr_actaul_origin a
INNER JOIN om_shipment_jtr_shipping_fee_base_2 b ON a.sub_order_shipping_id=b.sub_order_shipping_id
INNER JOIN om_shipment_jtr_shipping_fee_base_3 c ON a.sub_order_shipping_id=c.sub_order_shipping_id
where a.rate_id is not null;

select count(1)jtr_shipping_base from public.om_shipment_jtr_shipping_fee_base_4 ;

DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_5;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_5 AS
SELECT DISTINCT cast(lo.created_at AS date) AS created_at,
                reference_id,
                lo.order_weight,
                lo.shipping_amount,
                lr.actual__JTR_min_eta,
                lr.actual_JTR_per_kg_price,
                lr.expected__JTR_min_eta,
                lr.expected_JTR_per_kg_price
FROM raena_transport_management.logistic_order lo
INNER JOIN om_shipment_jtr_shipping_fee_base_4 lr ON lo.reference_id=lr.sub_order_shipping_id
WHERE lo.created_at >= '2022-04-01'
  AND lo.rate_id IS NOT NULL;


DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_6;



CREATE TABLE public.om_shipment_jtr_shipping_fee_base_6 AS
SELECT created_at,
       reference_id,
       order_weight,
       shipping_amount,
       actual__JTR_min_eta,
       actual_JTR_per_kg_price,
       expected__JTR_min_eta,
       expected_JTR_per_kg_price,
       coalesce(order_id,reference_id) AS order_id,
       sku,
       payment_amount,
       sales_sub_order_id,
       quantity
FROM
  (SELECT a.*,
          b.order_id,
          b.parent_sku AS sku,
          b.payment_amount ,
          b.id AS sales_sub_order_id,
          b.quantity
   FROM public.om_shipment_jtr_shipping_fee_base_5 a
   LEFT JOIN raena_order_management.sales_sub_order b ON a.reference_id=b.sales_sub_order_shipment_id);

 ---sku tagging

DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_7;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_7 AS
SELECT DISTINCT a.*,
                coalesce(b.sku,a.sku) AS new_sku,
                coalesce(b.quantity,a.quantity) AS new_quantity
FROM public.om_shipment_jtr_shipping_fee_base_6 a
LEFT JOIN raena_order_management.sales_sub_order_parent_child b ON a.sales_sub_order_id=b.sales_sub_order_id;


DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_8;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_8 AS
SELECT Created_at,
       order_id,
       reference_id,
       sales_sub_order_id,
       new_quantity AS quantity,
       order_weight,
       shipping_amount,
       actual__JTR_min_eta,
       actual_JTR_per_kg_price,
       expected__JTR_min_eta,
       expected_JTR_per_kg_price,
       new_sku,
       payment_amount,
       b.weight AS actual_weight,
       b.volume_weight
FROM public.om_shipment_jtr_shipping_fee_base_7 a
LEFT JOIN
  (SELECT sku,
          id,
          weight,
          (height*width*LENGTH)/6000 AS volume_weight
   FROM raena_catalog_management.product) b ON a.new_sku=b.sku
WHERE reference_id LIKE 'SH%'
ORDER BY 2;


 --final dataset
DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_9;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_9 AS
SELECT Created_at,
       order_id,
       reference_id,
       paymnet_amount,
       shipping_amount,
       actual__JTR_min_eta,
       actual_JTR_per_kg_price,
       expected__JTR_min_eta,
       expected_JTR_per_kg_price,
       CASE
           WHEN order_weight<1.3 THEN 1
           WHEN order_weight>=1.3
                AND (ABS(order_weight) - FLOOR(ABS(order_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(order_weight)+1
           ELSE FLOOR(order_weight)
       END AS order_weight,
       CASE
           WHEN compared_weight<1.3 THEN 1
           WHEN compared_weight>=1.3
                AND (ABS(compared_weight) - FLOOR(ABS(compared_weight))) BETWEEN 0.3 AND 0.999999 THEN FLOOR(compared_weight)+1
           ELSE FLOOR(compared_weight)
       END AS final_weight
FROM
  (SELECT *,
          CASE
              WHEN volume_weight>actual_weight THEN volume_weight
              WHEN volume_weight<actual_weight THEN actual_weight
              WHEN volume_weight=actual_weight THEN actual_weight
          END AS compared_weight
   FROM
     (SELECT Created_at,
             order_id,
             reference_id,
             shipping_amount,
             order_weight,
             actual__JTR_min_eta,
             actual_JTR_per_kg_price,
             expected__JTR_min_eta,
             expected_JTR_per_kg_price,
             (sum(quantity*actual_weight)) AS actual_weight,
             (sum(quantity*volume_weight)) AS volume_weight,
             sum(payment_amount) AS paymnet_amount
      FROM public.om_shipment_jtr_shipping_fee_base_8
      GROUP BY 1,
               2,
               3,
               4,
               5,
               6,
               7,
               8,
               9))
             ORDER BY final_weight DESC;

select count(1) from public.om_shipment_jtr_shipping_fee_base_9 ;

DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_10;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_10 AS
SELECT DISTINCT reference_id,
                final_weight ,
                actual__JTR_min_eta,
                actual_JTR_per_kg_price*final_weight AS actual_JTR_shipping_fee,
                expected__JTR_min_eta,
                expected_JTR_per_kg_price*final_weight AS expected_JTR_shipping_fee
FROM public.om_shipment_jtr_shipping_fee_base_9 a
WHERE final_weight>=10;



DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_11_v11 ;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_11_v11 AS
SELECT DISTINCT a.Sub_order_shipping_id,
                (b.actual_JTR_shipping_fee+(CASE WHEN o.partner_id = 5 then insurance_fee/100 else insurance_fee end )) AS actual_JTR_shipping_fee,
                actual__JTR_min_eta,
                (b.expected_JTR_shipping_fee+(CASE WHEN o.partner_id = 5 then insurance_fee/100 else insurance_fee end )) AS expected_JTR_shipping_fee,
                expected__JTR_min_eta
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping a
LEFT JOIN public.om_shipment_jtr_shipping_fee_base_10 b ON a.Sub_order_shipping_id=b.reference_id
INNER JOIN raena_transport_management.logistic_order o ON a.Sub_order_shipping_id=o.reference_id and o.status <>'CANCELLED'
WHERE Sub_order_shipping_id IS NOT NULL;

drop table if exists public.om_shipment_jtr_shipping_fee_base_11;

create table public.om_shipment_jtr_shipping_fee_base_11
as
select A.*,AA.new_sku sku,compared_weight sku_weight ,
sum((compared_weight/order_weight)*expected_jtr_shipping_fee) sku_level_jtr_expected_shipping_fee,
sum((compared_weight/order_weight)*actual_jtr_shipping_fee) sku_level_jtr_actual_shipping_fee
from public.om_shipment_jtr_shipping_fee_base_11_v11 A
left join
(SELECT order_id , sales_sub_order_id , reference_id ,new_sku,
        sum(CASE
              WHEN new_volume_weight>new_actual_weight THEN new_volume_weight
              WHEN new_volume_weight<new_actual_weight THEN new_actual_weight
              WHEN new_volume_weight=new_actual_weight THEN new_actual_weight
          END) over(partition by reference_id) order_weight,
          CASE
              WHEN new_volume_weight>new_actual_weight THEN new_volume_weight
              WHEN new_volume_weight<new_actual_weight THEN new_actual_weight
              WHEN new_volume_weight=new_actual_weight THEN new_actual_weight
          END AS compared_weight
   FROM
     (SELECT *,
             (quantity*actual_weight) AS new_actual_weight,
             (quantity*volume_weight) AS new_volume_weight
      FROM public.om_shipment_jtr_shipping_fee_base_8
      )
      ) AA on A.sub_order_shipping_id = AA.reference_id
      group by 1,2,3,4,5,6,7;

DROP TABLE IF EXISTS public.om_shipment_jtr_shipping_fee_base_12;


CREATE TABLE public.om_shipment_jtr_shipping_fee_base_12 AS
SELECT b.*,
       a.expected_eta_min
FROM public.rate_sheet_wise_expected_warehouse_expected_shipping_final a
INNER JOIN public.om_shipment_jtr_shipping_fee_base_11 b ON a.Sub_order_shipping_id=b.Sub_order_shipping_id
AND expected__JTR_min_eta-a.expected_eta_min<=3;


drop table if exists public.rate_sheet_wise_expected_warehouse_destination_area_id;

CREATE TABLE public.rate_sheet_wise_expected_warehouse_destination_area_id AS
select distinct  order_id ,
       reference_id Sub_order_shipping_id,
       destination_area_id
FROM
  (SELECT DISTINCT A.order_id ,
                   reference_id,
                   destination_area_id
   FROM public.warehouse_analysis_stage2 A
   LEFT JOIN public.warehouse_analysis_stage1 D ON A.rate_id = D.id
   AND A.service_name = D.service_name
   WHERE sku IS NOT null
   )AA;