select allotment.product_id,allotment.allotment_period,balance_period, coalesce(allotment.reassort,0)+coalesce(allotment.initial_stock,0)-coalesce(quantity.quantity,0) as balance ,allotment.reassort, allotment.initial_stock,quantity.quantity,quantity.consumption_date
          from allotment
          	          left join lateral (
	select
	(product_unavailability.unavailability_period)::daterange as balance_period
	from
		product_unavailability
	where allotment.product_id = product_unavailability.product_id
	and allotment_period::daterange && product_unavailability.unavailability_period=true
	) as unavailability_period on true
          left join lateral (
	select
		SUM(coalesce(quantity,0)) as quantity,consumption_date
	from
		product_allotment_usage
	where allotment.product_id = product_allotment_usage.product_id
	and allotment_period::daterange @> product_allotment_usage.consumption_date
	group by consumption_date,product_id,quantity) as quantity on true
inner join (
	select
		"product_id",
		"price_period",
		json_agg(sub_periods) filter (
		where price is not null)as price_info
	from
		(
		select
			"product_price_period"."id",
			"product_price_period"."price_period",
			"product_price_period"."product_id",
			json_build_object( 'sub_period', product_price_sub_period.price_sub_period) as sub_periods,
			(
			select
				case
					when product_price_period.price_mode_id = 1 then (
					select
						price
					from
						price_per_occupation
					where
						product_price_period_id = product_price_period.id
						and price notnull
					limit 1)
					when product_price_period.price_mode_id = 2 then (
					select
						fit_price
					from
						fixed_price
					where
						product_price_period_id = product_price_period.id
						and fit_price notnull
					limit 1)
					when product_price_period.price_mode_id = 3 then (
					select
						fit_price
					from
						price_per_pax
					where
						product_price_period_id = product_price_period.id
						and fit_price notnull
					limit 1)
					when product_price_period.price_mode_id = 4 then (
					select
						fit_price
					from
						price_per_age
					where
						product_price_period_id = product_price_period.id
						and fit_price notnull
					limit 1)
			end) as price
		from
			"product_price_period"
		inner join "product_price_sub_period" on
			"product_price_sub_period"."product_price_period_id" = "product_price_period"."id"
		group by
			"product_price_period"."id",
			product_price_period.price_period,
			"product_price_sub_period"."price_sub_period") as "price_info"
	group by
		"product_id",price_info.price_period)  as "product_price_info" on
	"product_price_info"."product_id" = allotment.product_id
	and
	product_price_info.price_period @> allotment_period = true
        where allotment.product_id = 24155
--where allotment.product_id=21430
          	and allotment.allotment_period && $1 =true

          and coalesce(allotment.reassort,0)+coalesce(allotment.initial_stock,0)-coalesce(quantity.quantity,0) <=0
          and price_info notnull
          group by allotment.product_id,allotment.reassort, allotment.allotment_period,allotment.initial_stock,quantity.quantity,quantity.consumption_date,balance_period