#!/bin/bash

echo "
                   
--------------------------OOS ------------------------- 
drop table if exists public.om_oos_soldout_1;


create table public.om_oos_soldout_1
as
select snp_date_time,sku_code,inventory 
from (
		select snp_date,snp_time,snp_date_time,sku_code,inventory,
		row_number() over (partition by sku_code,cast(snp_date_time  as date) order by snp_date_time desc,inventory desc) as R
		from (
				select distinct snp_date,snp_time,dateadd(hour,7,cast(cast(snp_date as varchar) || ' ' || cast(snp_time as varchar) as datetime)) as snp_date_time,sku_code,inventory
				from public.inventory_snapshot
		) 
		) where R=1;

--age
drop table if exists  public.om_oos_soldout_2;


create table public.om_oos_soldout_2
as
select *, datediff(day,cast(snp_date_time as date),current_date) as age
from public.om_oos_soldout_1;

drop table if exists  public.om_oos_soldout_2_med;


create table public.om_oos_soldout_2_med
as
select *, case when age between 1 and 3 then '<=3' when age between 4 and 7 then '4-7' when age between 8 and 15 then '8-15'
when age > 15 then 'GT 15' end as age_group
from public.om_oos_soldout_2 
where age>0;

drop table if exists  public.om_oos_soldout_3;


create table public.om_oos_soldout_3
as
select a.*,b.inventory as Maximum_occurred_inventory
from public.om_oos_soldout_2_med a 
inner join (
			select inventory,age_group,sku_code
			from (
					select inventory,age_group,sku_code,row_number() over (partition by age_group,sku_code order by Max_inventory desc) as R
					from (
							select inventory,age_group,sku_code,count(*) as Max_inventory
							from public.om_oos_soldout_2_med 
							group by inventory,age_group,sku_code order by 4 desc,1
					) 
				) where R=1 order by 2,3
			) b on a.sku_code=b.sku_code and a.age_group=b.age_group ;

drop table if exists  public.om_oos_soldout_3_med;


create table public.om_oos_soldout_3_med
as
select a.*,b.\"<=3 Maximum_occurred_inventory\",\"4-7 Maximum_occurred_inventory\",\"8-15 Maximum_occurred_inventory\",\"GT 15 Maximum_occurred_inventory\"
from public.om_oos_soldout_3 a
inner join (
		 select distinct sku_code,
		 sum(case when Age_group in ('<=3') then Maximum_occurred_inventory end)as \"<=3 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('4-7') then Maximum_occurred_inventory end) as \"4-7 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('8-15') then Maximum_occurred_inventory end) as \"8-15 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('GT 15') then Maximum_occurred_inventory end )as \"GT 15 Maximum_occurred_inventory\"
		 from public.om_oos_soldout_3 
		 group by 1 order by sku_code
		 ) b on a.sku_code=b.sku_code ;


drop table if exists  public.om_oos_soldout_3_med_2;


create table public.om_oos_soldout_3_med_2
as
select a.*,b.sold_out_fl
from public.om_oos_soldout_3 a 
inner join (
			select sku_code,sold_out_fl 
			from (
					select *,row_number() over (partition by sku_code order by sold_out_fl) as R
					from (
							select distinct sku_code,
							case when  ((age=16 or age=17 or age=18 or age=19) and inventory=0) and (\"8-15 Maximum_occurred_inventory\"=0) and \"4-7 Maximum_occurred_inventory\"=0 and \"<=3 Maximum_occurred_inventory\"=0 then '1.GT 15 and sold out'
							when  (age=8 and inventory=0) and \"4-7 Maximum_occurred_inventory\"=0 and \"<=3 Maximum_occurred_inventory\"=0 then '2.8-15 and sold out'
							when  ((age=4 or age=5 or age=6) and inventory=0) and \"<=3 Maximum_occurred_inventory\"=0 then '3.4-7 and sold out'
							when  (age=1 and inventory=0) then '4.<=3 and sold out' else '5.stocked' end as sold_out_fl--,count(distinct sku_code)
							from public.om_oos_soldout_3_med 
							order by sku_code,age
							)
				) where R=1
			) b on a.sku_code=b.sku_code ;

drop table if exists  public.om_oos_soldout_4;


create table public.om_oos_soldout_4
as
select a.*,b.brand_name,b.\"EL/PL Flag\",b.give_away,b.active_status
from public.om_oos_soldout_3_med_2 a
inner join 
(
		select distinct b.name as brand_name,a.sku,case when moqremark = 'NOT ORDER GIVE AWAY ITEM' then 'Yes' else 'No' end give_away,
				   CASE
		           WHEN a.is_archived = 'true' THEN 'Permanatly_delisted'
		           WHEN a.is_delisted = 'true' THEN 'delisted'
		           WHEN a.is_archived = 'false'
		           AND a.is_delisted = 'false' THEN 'Active'
		       	   END active_status,
		       	   case when brand_name in ('HISTOIRE NATURELLE','INGRID','W DRESSROOM','FORENCOS','DEWYCEL','GLUTANEX','BELLFLOWER','ONE THING','BEAUSTA') then 'EL/PL' else 'NA' end as \"EL/PL Flag\"
		from raena_catalog_management.product a
		left join raena_catalog_management.brand b on a.brand_id=b.id
		left join public.demand_forecasting_configs c on a.sku=c.sku
) b on a.sku_code=b.sku ;

drop table if exists  public.om_oos_soldout_5;


create table public.om_oos_soldout_5
as
select a.*,coalesce(b.sku_type,'Not Moving') as sku_type,coalesce(b.new_sku_flag,'No') as new_sku_flag,
cikarang_runrate,surabaya_runrate,semarang_runrate,medan_runrate,makassar_runrate,samarinda_runrate,total_runrate,c.gold_price,d.runrate as \"90_days_runrate\"
from public.om_oos_soldout_4 a
left join public.demand_forecast_dashboard_final b on a.sku_code=b.sku
LEFT JOIN
			(SELECT distinct sku ,
			        case when is_slash_price='true' 
		then json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) 
		else  json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) end as gold_price
			 FROM raena_catalog_management.product p) c on a.sku_code=c.sku
left join 
		( select sku,sum(oi.quantity)/count(distinct cast(o.created_at as date)) as runrate
from raena_order_management.order o
inner join raena_order_management.order_item oi on o.id=oi.order_id
where cast(o.created_at as date)>=cast((current_date-90) as date) and o.payment_status='Paid' and cast(o.is_archived as varchar)='false'
group by sku
		) d on a.sku_code=d.sku
			 ; 

/*drop table if exists  public.om_oos_soldout_5;


create table public.om_oos_soldout_5
as
select a.*,coalesce(b.sku_type,'Not Moving') as sku_type,coalesce(b.new_sku_flag,'No') as new_sku_flag,
cikarang_runrate,surabaya_runrate,semarang_runrate,medan_runrate,makassar_runrate,samarinda_runrate,total_runrate,c.gold_price,d.runrate as \"90_days_runrate\"
from public.om_oos_soldout_4 a
left join public.demand_forecast_dashboard_final b on a.sku_code=b.sku
LEFT JOIN
			(SELECT distinct sku ,
			        case when is_slash_price='true' 
		then json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) 
		else  json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) end as gold_price
			 FROM raena_catalog_management.product p) c on a.sku_code=c.sku
left join 
		( select distinct sku,total_runrate as runrate from public.demand_forecast_dashboard_final
		) d on a.sku_code=d.sku; */


			
drop table if exists  public.om_oos_soldout_6;


create table public.om_oos_soldout_6
as			
select distinct cast(a.snp_date_time as date) as snp_date_time,a.sku_code,\"90_days_runrate\",a.gold_price,OOS_days,a.sold_out_fl,
a.active_status,a.brand_name,\"EL/PL Flag\",a.give_away,a.sku_type,a.new_sku_flag,c.total_runrate,d.avg_remaining_LM_RV,d.avg_All_LM_RV,e.PO_Ordered_quantity
from public.om_oos_soldout_5 a
inner join (
			select sku_code,sold_out_fl,max(case when inventory=0 and sold_out_fl='1.GT 15 and sold out' and age>15 then age 
			when inventory=0 and sold_out_fl='2.8-15 and sold out' and age between 8 and 15 then age 
			when inventory=0 and sold_out_fl='3.4-7 and sold out' and age between 4 and 7 then age 
			when inventory=0 and sold_out_fl='4.<=3 and sold out' and age <=3 then age 
			end) as OOS_days
			from public.om_oos_soldout_5 where sold_out_fl not like '%stocked'
			group by 1,2
) b on a.sku_code=b.sku_code
left join public.demand_forecast_dashboard_final c on a.sku_code=c.sku
left join (
			select sku,avg(remaining_shipping_fee)/avg(remaining_payment_amount) as avg_remaining_LM_RV,avg(shipping_fee)/avg(payment_amount) as avg_All_LM_RV
			from (
							select distinct sku,case when (actual_warehouse!=expected_warehouse) or expected_warehouse isnull and payment_amount notnull then payment_amount end as remaining_payment_amount,
				case when (actual_warehouse!=expected_warehouse) or expected_warehouse isnull and payment_amount notnull then \"Actual Shipping Fee\" end as remaining_shipping_fee,
				payment_amount,\"Actual Shipping Fee\" as shipping_fee
							from (
									select distinct sku,actual_warehouse,expected_warehouse,A.payment_amount,
									case 		when A.sub_order_shipping_id=sb.reference_id and A.order_id=sb.order_id then ((sb.shipping_amount)/(sb.order_weight))*(sb.final_weight)
									            when A.order_id = D.id and (A.sub_order_shipping_id= D.id or A.sub_order_shipping_id is null) then D.shipping_amount 
									            when A.sub_order_shipping_id=C.id then C.shipping_amount end \"Actual Shipping Fee\"
									from public.rate_sheet_wise_expected_warehouse A
									left join raena_order_management.sales_sub_order_shipment C on A.sub_order_shipping_id=C.id
									left join raena_order_management.order D on A.order_id = D.id and (A.sub_order_shipping_id= D.id or A.sub_order_shipping_id is null)
									left join public.OM_Logistic_final_shipping_base sb on  A.sub_order_shipping_id=sb.reference_id and A.order_id=sb.order_id 
									where A.created_date>=(current_date-90) and A.is_campaign='false'
									)
				) where payment_amount!=0
				group by 1
) d on a.sku_code=d.sku
left join ( 
			select raena_code sku,
			coalesce(sum(case when warehouse_id = 'CGK WHS' then COALESCE(quantity,0) end),0)  +
			coalesce(sum(case when warehouse_id = 'Makassar WHS' then COALESCE(quantity,0) end),0) +
			coalesce(sum(case when warehouse_id = 'Medan WHS' then COALESCE(quantity,0) end),0) +
			coalesce(sum(case when warehouse_id = 'Samarinda WHS' then COALESCE(quantity,0) end),0) +
			coalesce(sum(case when warehouse_id = 'Semarang WHS' then COALESCE(quantity,0) end),0) +
			coalesce(sum(case when warehouse_id = 'Surabaya WHS' then COALESCE(quantity,0) end),0) PO_Ordered_quantity
			from raena_erp_management.inbound_order A
			left join raena_erp_management.inbound_order_sku B on A.id = B.orderIdId
			 where cast(expected_arrival_date  as date) > current_date -1 and received_time is null  
			group by raena_code
) e on a.sku_code=e.sku;

-------------------restock

drop table if exists public.om_oos_restock_1;


create table public.om_oos_restock_1
as
select report_date,a.sku,overall_qty_required_per_day as restock,sku_type,brand_name,give_away,new_sku_flag,active_status,
coalesce(cikarang_project_quantity_to_order,0) as cikarang_project_quantity_to_order,coalesce(surabaya_project_quantity_to_order,0) as surabaya_project_quantity_to_order,
coalesce(semarang_project_quantity_to_order,0) as semarang_project_quantity_to_order,coalesce(medan_project_quantity_to_order,0) as medan_project_quantity_to_order,
coalesce(makassar_project_quantity_to_order,0) as makassar_project_quantity_to_order,coalesce(semarandi_project_quantity_to_order,0) as semarandi_project_quantity_to_order
,case when brand_name in ('HISTOIRE NATURELLE','INGRID','W DRESSROOM','FORENCOS','DEWYCEL','GLUTANEX','BELLFLOWER','ONE THING','BEAUSTA') then 'EL/PL' else 'NA' end as \"EL/PL Flag\",
datediff(day,cast(report_date as date),current_date) as age,c.gold_price
from public.OM_OOS_Base a
LEFT JOIN
			(SELECT distinct sku ,
			        case when is_slash_price='true' 
		then json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) 
		else  json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) end as gold_price
			 FROM raena_catalog_management.product p) c on a.sku=c.sku;

drop table if exists public.om_oos_restock_1_med;


create table public.om_oos_restock_1_med
as
select *
from public.om_oos_restock_1 unpivot
(cnt for warehouse in (cikarang_project_quantity_to_order,surabaya_project_quantity_to_order,semarang_project_quantity_to_order,medan_project_quantity_to_order,
makassar_project_quantity_to_order,semarandi_project_quantity_to_order));


drop table if exists public.om_oos_restock_2;


create table public.om_oos_restock_2
as
select *,case when age between 1 and 3 then '<=3' when age between 4 and 7 then '4-7' when age between 8 and 15 then '8-15'
when age > 15 then 'GT 15' end as age_group
from public.om_oos_restock_1_med
where age>0;

drop table if exists public.om_oos_restock_3;


create table public.om_oos_restock_3
as
select a.*,b.restock as Maximum_occurred_inventory
from public.om_oos_restock_2 a
inner join (
			select restock,age_group,sku,warehouse
			from (
					select restock,age_group,warehouse,sku,row_number() over (partition by age_group,warehouse,sku order by Max_inventory desc) as R
					from (
							select cnt as restock,age_group,warehouse,sku,count(*) as Max_inventory
							from public.om_oos_restock_2
							group by cnt,age_group,warehouse,sku order by 5 desc,1
					)
				) where R=1 order by 2,3
			) b on a.sku=b.sku and a.age_group=b.age_group and a.warehouse=b.warehouse;



drop table if exists public.om_oos_restock_4;


create table public.om_oos_restock_4
as
select a.*,b.\"<=3 Maximum_occurred_inventory\",\"4-7 Maximum_occurred_inventory\",\"8-15 Maximum_occurred_inventory\",\"GT 15 Maximum_occurred_inventory\"
from public.om_oos_restock_3 a
inner join (
		 select distinct sku,warehouse,
		 sum(case when Age_group in ('<=3') then Maximum_occurred_inventory end)as \"<=3 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('4-7') then Maximum_occurred_inventory end) as \"4-7 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('8-15') then Maximum_occurred_inventory end) as \"8-15 Maximum_occurred_inventory\",
		 sum(case when Age_group in ('GT 15') then Maximum_occurred_inventory end )as \"GT 15 Maximum_occurred_inventory\"
		 from public.om_oos_restock_3
		 group by 1,2 order by sku
		 ) b on a.sku=b.sku and a.warehouse=b.warehouse;

drop table if exists public.om_oos_restock_5;


create table public.om_oos_restock_5
as
select a.*,b.sold_out_fl,c.runrate as \"90_days_runrate\"
from public.om_oos_restock_4 a
inner join (
			select sku,sold_out_fl,warehouse
			from (
					select *,row_number() over (partition by sku order by sold_out_fl) as R
					from (
							select distinct sku,warehouse,
							case when  ((age=16) and restock=0) and (\"8-15 Maximum_occurred_inventory\"=0) and \"4-7 Maximum_occurred_inventory\"=0 and \"<=3 Maximum_occurred_inventory\"=0 then '1.GT 15 and sold out'
							when  (age=8 and restock=0) and \"4-7 Maximum_occurred_inventory\"=0 and \"<=3 Maximum_occurred_inventory\"=0 then '2.8-15 and sold out'
							when  ((age=4) and restock=0) and \"<=3 Maximum_occurred_inventory\"=0 then '3.4-7 and sold out'
							when  (age=1 and restock=0) then '4.<=3 and sold out' else '5.stocked' end as sold_out_fl--,count(distinct sku_code)
							from public.om_oos_restock_4
							order by sku,age
							)
				) where R=1
			) b on a.sku=b.sku and a.warehouse=b.warehouse
left join (
			select sku,sum(oi.quantity)/count(distinct cast(o.created_at as date)) as runrate
			from raena_order_management.order o
			inner join raena_order_management.order_item oi on o.id=oi.order_id
			where cast(o.created_at as date)>=cast((current_date-90) as date) and o.payment_status='Paid' and cast(o.is_archived as varchar)='false'
			group by sku
) c on a.sku=c.sku;

drop table if exists public.weight_sku_table;


CREATE TABLE public.weight_sku_table AS
SELECT sku,
       CASE
           WHEN weight>=volume_weight THEN weight
           ELSE volume_weight
       END weight
FROM
  (SELECT a.sku,
          weight,
          (height*width*LENGTH)/6000 AS volume_weight
   FROM raena_catalog_management.product a
   WHERE CLASS<>'Bundle') A;

drop table if exists public.om_shipping_fee_sku_base_stage2;


CREATE TABLE public.om_shipping_fee_sku_base_stage2 AS
SELECT A.*,
       B.weight*Quantity sku_weight,
       CASE
           WHEN (B.weight*Quantity)<1.3 THEN 1
           WHEN (B.weight*Quantity)>=1.3
                AND (ABS((B.weight*Quantity)) - FLOOR(ABS((B.weight*Quantity)))) BETWEEN 0.3 AND 0.999999 THEN FLOOR((B.weight*Quantity))+1
           ELSE FLOOR((B.weight*Quantity))
       END AS final_weight
FROM (
		select sku,actual_warehouse,expected_warehouse,payment_amount,quantity,sub_order_shipping_id,missed_inventory,
		coalesce(expected_JTR_shipping_fee,Expected_shipping_fee,\"Actual Shipping Fee\") as Expected_shipping_fee,\"Actual Shipping Fee\"
		from (
				select distinct A.sku,actual_warehouse,expected_warehouse,A.payment_amount,quantity,A.sub_order_shipping_id,missed_inventory,
				case when A.sub_order_shipping_id=sb.reference_id and A.order_id=sb.order_id then ((sb.shipping_amount)/(sb.order_weight))*(sb.final_weight)
				when A.order_id = D.id and (A.sub_order_shipping_id= D.id or A.sub_order_shipping_id is null) then D.shipping_amount
				when A.sub_order_shipping_id=C.id then C.shipping_amount end \"Actual Shipping Fee\",
				public.rate_sheet_wise_expected_warehouse_expected_shipping_final.Expected_shipping_fee,jsf.expected_JTR_shipping_fee
				from public.rate_sheet_wise_expected_warehouse A
				left join raena_order_management.sales_sub_order_shipment C on A.sub_order_shipping_id=C.id
				left join raena_order_management.order D on A.order_id = D.id and (A.sub_order_shipping_id= D.id or A.sub_order_shipping_id is null)
				left join public.OM_Logistic_final_shipping_base sb on  A.sub_order_shipping_id=sb.reference_id and A.order_id=sb.order_id
				left join public.rate_sheet_wise_expected_warehouse_expected_shipping_final  on A.sub_order_shipping_id=public.rate_sheet_wise_expected_warehouse_expected_shipping_final.sub_order_shipping_id
				left join public.om_shipment_jtr_shipping_fee_base_11 jsf on A.sub_order_shipping_id=jsf.sub_order_shipping_id
				where A.created_date>=(current_date-90) and A.is_campaign='false'
		)
) A
LEFT JOIN public.weight_sku_table B ON A.sku = B.sku ;

drop table if exists public.om_shipping_fee_sku_base_stage3;


CREATE TABLE public.om_shipping_fee_sku_base_stage3 AS
SELECT *,
       (sku_weight*\"Actual Shipping Fee\")/sum(sku_weight) over(partition BY sub_order_shipping_id) new_shipment_amount,
       (sku_weight*Expected_shipping_fee)/sum(sku_weight) over(partition BY sub_order_shipping_id) new_expected_shipment_amount
FROM public.om_shipping_fee_sku_base_stage2;

drop table if exists public.om_oos_restock_6;


create table public.om_oos_restock_6
as
select distinct a.report_date,a.sku,a.sku_type,a.brand_name,a.give_away,a.new_sku_flag,a.active_status,a.\"el/pl flag\",a.age,a.gold_price,a.cnt,a.age_group,a.sold_out_fl,\"90_days_runrate\",
b.restock_days,c.total_runrate,d.avg_remaining_LM_RV,d.avg_All_LM_RV
from public.om_oos_restock_5 a
inner join (
		select sku,sold_out_fl,max(case when restock=0 and sold_out_fl='1.GT 15 and sold out' and age>15 then age
			when restock=0 and sold_out_fl='2.8-15 and sold out' and age between 8 and 15 then age
			when restock=0 and sold_out_fl='3.4-7 and sold out' and age between 4 and 7 then age
			when restock=0 and sold_out_fl='4.<=3 and sold out' and age <=3 then age
			end) as restock_days
			from public.om_oos_restock_5 where sold_out_fl not like '%stocked'
			group by 1,2
) b on a.sku=b.sku
left join public.demand_forecast_dashboard_final c on a.sku=c.sku
left join (
			select sku,avg(shipping_fee)/avg(payment_amount) as avg_remaining_LM_RV,avg(new_expected_shipment_amount)/avg(payment_amount) as avg_All_LM_RV
			from (
							select  sku,
							new_expected_shipment_amount,
							payment_amount,\"Actual Shipping Fee\" as shipping_fee
							from (
									select sku,actual_warehouse,expected_warehouse,
									(payment_amount/quantity) as payment_amount,missed_inventory,(new_shipment_amount/quantity) as \"Actual Shipping Fee\",
									(new_expected_shipment_amount/quantity) as new_expected_shipment_amount
									from public.om_shipping_fee_sku_base_stage3 --where sku='HAN020'
									)
				) where payment_amount!=0
				group by 1

) d on a.sku=d.sku;

                 
             
                  
                 
         " > /home/ec2-user/cronjob/OOS/OOS_code.sql

psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/OOS/OOS_code.sql