DEALLOCATE all;PREPARE loggedQuery AS (with "unavailability" as (select distinct "product_id" from "product_unavailability" where "unavailability_period" && $1), "product_price_period_list" as (select "product_price_period"."product_id", "product_price_period"."id" as "period_id", "product_price_period"."is_premium", "product_price_period"."price_mode_id", "product"."max_child_age" from "product_price_period" inner join "product" on "product_id" = "product"."id" left join "unavailability" on "product_price_period"."product_id" = "unavailability"."product_id" where "unavailability"."product_id" is null and "product"."status" = $2 and "price_period" && $3), "product_price_per_period" as (select "product_id", "period_id", "price_mode_id", "is_premium", "product_price_period_list"."max_child_age", sum((upper((price_sub_period * $4)::daterange))::date -lower ((price_sub_period * $5)::daterange)) as "numberOfDaysInPeriod", JSON_AGG((price_sub_period * $6)::daterange) as listOfDaysInPeriod from "product_price_sub_period" inner join "product_price_period_list" on "period_id" = "product_price_period_id" where "price_sub_period" && $7 group by "product_id", "period_id", "price_mode_id", "is_premium", "product_price_period_list"."max_child_age"), "duplicated_product_price_per_period_and_room" as (select "product_id", "period_id", "price_mode_id", "is_premium", "numberOfDaysInPeriod", listOfDaysInPeriod, $8::integer as room,$9::integer as adults, CAST(array[] AS int[]) as ages,
      CASE
          WHEN COALESCE (max_child_age,
                        0) = 0 THEN $10::integer
          WHEN COALESCE (max_child_age,
                        0) < ANY(CAST(array[] AS int[])) THEN
                (SELECT $11::integer + COUNT(*) FILTER (
                                              WHERE child_age > COALESCE (max_child_age, 0))
                  FROM unnest(CAST(array[] AS int[])) child_age)
          ELSE $12::integer
      END AS total_adults from "product_price_per_period"), "valid_products_per_room" as (select "product_id", "room", "is_premium" from "duplicated_product_price_per_period_and_room" group by "product_id", "room", "is_premium" having sum("numberOfDaysInPeriod") <= $13::date - $14::date), "product_price_per_period_and_room" as (select "product_id", "period_id", "price_mode_id", "is_premium", "numberOfDaysInPeriod", listOfDaysInPeriod, "room", "adults", "ages", "total_adults" from "duplicated_product_price_per_period_and_room" where (product_id, room) in (select product_id,room from valid_products_per_room)), "product_price_classic" as (select "product_id", "room" from "product_price_per_period_and_room" ,LATERAL (
          SELECT
           CASE
          WHEN  price_mode_id = 4 THEN
           (SELECT json_build_object('unitPrice', sum(fit_price))
 FROM unnest(array_cat(CAST(ages AS int[]), array_fill(18, ARRAY[adults]))) age
 ,lateral (
  SELECT fit_price
  FROM price_per_age
  where price_per_age.product_price_period_id = period_id
  and age <@ age_range
  order by price_per_age.age_range desc
  limit 1
  ) max_price
  having count(*) = adults+coalesce(array_length(ages, 1),0))
          WHEN  price_mode_id = 3 THEN
          (SELECT json_build_object('unitPrice', fit_price * (adults + coalesce(array_length(ages, 1),0)))
  FROM price_per_pax
   where price_per_pax.product_price_period_id = period_id
   and (adults + coalesce(array_length(ages, 1), 0)) <@ pax_range
   order by price_per_pax.pax_range desc
   limit 1)
           WHEN  price_mode_id = 2 THEN
          (SELECT json_build_object('unitPrice', fit_price)
  FROM fixed_price
   where fixed_price.product_price_period_id = period_id)
          WHEN  price_mode_id = 1 THEN
          (SELECT json_build_object('unitPrice', price ,'occupation_type_id', occupation_type_id)
   FROM price_per_occupation
   where price_per_occupation.product_price_period_id = period_id
   and occupation_type_id = any(
   case when total_adults = 2 then ARRAY[2,3]
   when total_adults > 2 then ARRAY[total_adults + 1]
   else ARRAY[1] END)
   order by occupation_type_id
   limit 1 )
          END AS "fit_price"
          )  max_price where "is_premium" = $15 and ("fit_price" is not null and not "price_mode_id" = $16) or "price_mode_id" = $17 group by "product_id", "room"), "product_availabilities" as (select "allotment"."product_id", case when sum((upper((allotment_period * $18)::daterange))::date -
        lower ((allotment_period * $19)::daterange)) <> upper($20::daterange)::date -lower ($21::daterange)
          then -1
          else SIGN(min(
            COALESCE(initial_stock,0) +  COALESCE(reassort,0) - COALESCE(maxQuantity,0)))
          end as balance from "allotment" inner join "product" on "product"."id" = "allotment"."product_id" and "product"."qty_in_inventory" = $22 left join (SELECT distinct product_id
        FROM product_retrocession
        where retrocession_period && $23 = true
        and now()::date > lower(retrocession_period * $24) - retrocession
        ) productretrocession on
        productretrocession.product_id = allotment.product_id LEFT JOIN LATERAL (
        select SUM(coalesce(quantity,0)) OVER (PARTITION BY consumption_date ORDER BY consumption_date) AS maxQuantity
        from product_allotment_usage
        where product_allotment_usage.product_id = allotment.product_id
        and allotment.allotment_period::daterange @> consumption_date
        order by maxQuantity desc
        limit 1
        ) allotment_usage on TRUE where allotment_period && $25 = true and "productretrocession"."product_id" is null group by "allotment"."product_id") select "establishment_id", "establishment_name", "latitude", "longitude", "category_id", "balance" from (select "product"."establishment_id", "establishment"."name" as "establishment_name", "establishment"."category_id", ST_X(ST_Transform (establishment.coordinate, 4326)) as longitude, ST_Y(ST_Transform (establishment.coordinate, 4326)) as latitude, (ST_Distance("establishment"."coordinate"::geography, "establishment:city"."coordinate"::geography) / 1000)::numeric(10,2)  as distance, max(coalesce(balance, -1)) as balance from "product" inner join "establishment" on "establishment"."id" = "product"."establishment_id" inner join "geoname" as "establishment:city" on "establishment:city"."geonameid" = "establishment"."city_id" inner join "provider" on "provider"."id" = "establishment"."provider_id" inner join "product_price_classic" on "product"."id" = "product_price_classic"."product_id" left join "product_availabilities" on "product_availabilities"."product_id" = "product_price_classic"."product_id" where ST_DWithin("establishment"."coordinate"::geography, "establishment:city"."coordinate"::geography , $26) and "establishment"."city_id" = $27 and "establishment"."status" = $28 and "provider"."status" = $29 group by "product"."establishment_id", "establishment"."id", "establishment"."coordinate", "establishment:city"."coordinate" having count(product.id) > 0) as "establishments_list" order by balance desc , category_id asc);EXECUTE loggedQuery('[2019-12-20,2020-01-09)',true,'[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)',1,2,2,2,2,'2020-01-09','2019-12-20',false,7,7,'[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)','[2019-12-20,2020-01-09)',50000,6077243,true,true);