select "product".*, "allotment_info", "allotment_usage_info", "unavailability_info" from "product" left join (select "allotment"."product_id", json_agg(
    json_build_object(
      'allotment_period',allotment.allotment_period * $1,
      'initial_stock',allotment.initial_stock,
      'reassort',allotment.reassort
    )
  ) as allotment_info from "allotment" where allotment_period && $2 = true group by "allotment"."product_id") as "product_allotment_info" on "product_allotment_info"."product_id" = "product"."id" left join (select "allotment_usage_per_day"."product_id", json_agg(
  json_build_object(
    'consumption_date',consumption_date,
    'quantity',quantity
  )
) as allotment_usage_info from (select
product_id,consumption_date,sum(coalesce(quantity, 0)) as quantity
from product_allotment_usage
where $3::daterange @> consumption_date
group by product_id , consumption_date
) as allotment_usage_per_day group by "allotment_usage_per_day"."product_id") as "product_allotment_usage_info" on "product_allotment_usage_info"."product_id" = "product_allotment_info"."product_id" left join (select "product_unavailability"."product_id", json_agg(
      json_build_object(
        'unavailability_period',product_unavailability.unavailability_period * $4
      )
    ) as unavailability_info from "product_unavailability" where unavailability_period && $5 = true group by "product_unavailability"."product_id") as "product_unavailability_info" on "product_unavailability_info"."product_id" = "product_allotment_info"."product_id" inner join (select "product_id", json_agg(
        sub_periods)
        FILTER (WHERE price_per_pax IS NOT NULL
          OR price_per_occupation IS NOT NULL
          OR price_per_age IS NOT NULL
          OR fixed_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 * $6) as sub_periods, "fixed_price"."fit_price" as "fixed_price", "price_per_pax"."fit_price" as "price_per_pax", "per_occupation_price"."price" as "price_per_occupation", "per_age_price"."price" as "price_per_age" from "product_price_period" inner join "product_price_sub_period" on "product_price_sub_period"."product_price_period_id" = "product_price_period"."id" left join fixed_price on product_price_period.id = fixed_price.product_price_period_id and fixed_price.fit_price is not null left join price_per_pax on product_price_period.id = price_per_pax.product_price_period_id and price_per_pax.fit_price is not null left join lateral(
    select max(price) as price from price_per_occupation
    where product_price_period_id = product_price_period.id and price is not null) as per_occupation_price on true left join lateral(
    select max(fit_price) as price from price_per_age
    where product_price_period_id = product_price_period.id and fit_price is not null) as per_age_price on true where product_price_period.price_period && $7 = true and product_price_sub_period.price_sub_period && $8 = true group by "product_price_period"."id", "product_price_sub_period"."price_sub_period", "fixed_price"."fit_price", "price_per_pax"."fit_price", "per_occupation_price"."price", "per_age_price"."price") as "price_info" group by "product_id") as "product_price_info" on "product_price_info"."product_id" = "product"."id" inner join "establishment" on "establishment"."id" = "product"."establishment_id" inner join "provider" on "provider"."id" = "establishment"."provider_id" where "establishment_id" = $9 and "product_type_id" = $10 and "product"."status" = $11 and "establishment"."status" = $12 and "provider"."status" = $13 and "product_allotment_info" is not null and "product_price_info" is not null);EXECUTE loggedQuery('[2020-02-19,)','[2020-02-19,)','[2020-02-19,)','[2020-02-19,)','[2020-02-19,)','[2020-02-19,)','[2020-02-19,)','[2020-02-19,)',2661,1,true,true,true);