with orders as (
	select
		extract(month from order_date) as month_of_the_year_num,
		extract(isodow from order_date) as day_of_the_week_num,
		count(order_id) as total_orders
	from {{ref ('stg_orders')}}
	group by 1,2
),dim_dates as(
	select * from {{ref ('dim_dates')}}
), total_orders as (
    select 
        cast(now() as date) as ingestion_date,
        o.month_of_the_year_num,
        o.day_of_the_week_num,
        count(total_orders) as total_order
    from orders o
    left join dim_dates as d on d.month_of_the_year_num = (o.month_of_the_year_num)
    where (d.work_day = False) and (o.day_of_the_week_num between 1 and 5)
    group by 1,2,3
)
select 
ingestion_date,
sum(case when month_of_the_year_num = 1 then total_order end ) as tt_order_hol_jan,
sum(case when month_of_the_year_num = 2 then total_order end ) as tt_order_hol_feb,
sum(case when month_of_the_year_num = 3 then total_order end ) as tt_order_hol_mar,
sum(case when month_of_the_year_num = 4 then total_order end ) as tt_order_hol_apr,
sum(case when month_of_the_year_num = 5 then total_order end ) as tt_order_hol_may,
sum(case when month_of_the_year_num = 6 then total_order end ) as tt_order_hol_jun,
sum(case when month_of_the_year_num = 7 then total_order end ) as tt_order_hol_jul,
sum(case when month_of_the_year_num = 8 then total_order end ) as tt_order_hol_aug,
sum(case when month_of_the_year_num = 9 then total_order end )as tt_order_hol_sep,
sum(case when month_of_the_year_num = 10 then total_order end)  as tt_order_hol_oct,
sum(case when month_of_the_year_num = 11 then total_order end)  as tt_order_hol_nov,
sum(case when month_of_the_year_num = 12 then total_order end ) as tt_order_hol_dec
from total_orders
group by 1

with orders as (
   select * from {{ref ('stg_orders')}}
), shipments as (
   select * from {{ref ('stg_shipments_deliveries')}}
),
date_difference as (	
   select 
   	sd.*,
   	o.order_date,
       product_id,
   	(sd.shipment_date - o.order_date) as late_delivery_date_difference,
   	cast('2022-09-06' as date) -  o.order_date as undelivered_date_difference
   from shipments sd
   left join orders o on o.order_id = sd.order_id
)
select 
   order_id,
   product_id,
   case 
       when (late_delivery_date_difference >= 6) and (delivery_date is null) then 'late' 
       when (late_delivery_date_difference < 6) and (delivery_date is not null) then 'early'
       when (delivery_date is null and shipment_date is null) and (undelivered_date_difference > 15) then 'undelivered'
   end as late_early_undelivered
from date_difference

with shipment_performance as (
	select * from {{ref ('stg_shipment_performance')}}
)
select 
	cast(now() as date) as ingestion_date,
	count(case when late_early_undelivered = 'late' then True end) as tt_late_shipments,
	count(case when late_early_undelivered = 'undelivered' then True end) as tt_undelivered_shipmnets
from shipment_performance

with shipment_performance as (
	select * from {{ref ('stg_shipment_performance')}}
)
select 
	cast(now() as date) as ingestion_date,
	count(case when late_early_undelivered = 'late' then True end) as tt_late_shipments,
	count(case when late_early_undelivered = 'undelivered' then True end) as tt_undelivered_shipmnets
from shipment_performance

with 
orders as (
	select * from {{ref ('stg_orders')}}
),
reviews as (
	select * from {{ref ('stg_reviews')}}
),
dim_dates as (
	select * from {{ref ('dim_dates')}} 
),
shipments_performance as (
	select 
		*
	from {{ref ('stg_shipment_performance')}}
)
,total_reviews as(
	select 
		product_id, 
		sum(review) as total_reviews, 
		rank() over(order by sum(review) desc ) as ranking
	from reviews
	group by 1	 
 ),get_orders as (
	select 
		o.product_id,
		o.order_date,
		tr.total_reviews,
		count(o.order_id) as order_count,
		rank() over(order by count(o.order_id) desc) as ranking
	from orders o
	left join total_reviews tr on tr.product_id = o.product_id
	where tr.ranking = 1
	group by 1,2,3
 ), get_late_and_early as (
	select 
		g.*,
		count(case when late_early_undelivered = 'late' then true end) as count_late,
		count(case when late_early_undelivered = 'early' then true end) as count_early
	from get_orders g
	left join shipments_performance sp on sp.product_id = g.product_id
	where ranking = 1
	group by 1,2,3,4,5
 ), total_product_reviews as (
	select	
		gle.product_id,
		sum(review) as total_product_reviews
	from reviews as r
	left join orders as o on r.product_id = o.product_id
	left join get_late_and_early as gle on gle.order_date = o.order_date
	where o.product_id = gle.product_id and o.order_date = gle.order_date
	group by 1
 ), base_table as (
	select 
	gle.*,
	total_product_reviews
	from get_late_and_early gle
	left join total_product_reviews as tpr on tpr.product_id = gle.product_id
 ), is_public_holiday as (
	select
		product_id,
		order_date,
		case when (day_of_the_week_num between 1 and 5) and work_day = false then True else False end as is_public_holiday,
		total_reviews,
		(total_reviews * 100) / (total_reviews + total_product_reviews) as pct_dist_ttl_review_points,
		(count_early * 100) / (count_early + count_late) as pct_dist_early_to_late_shipments
	from base_table bs 
	left join dim_dates as d on d.calender_dt = bs.order_date
 )
 select* from is_public_holiday