Get Next Collection

CREATE OR REPLACE FUNCTION public.get_next_collection(offset_param integer, user_id_param uuid)
 RETURNS TABLE(collection_id bigint, score numeric, type text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
with
	view_virtual_items as (
		  select
			  i_1.product_id,
				i_1.ext_product_id,
				COALESCE(ir.name, i_1.name) AS name,
				COALESCE((ir.price)::bigint, (i_1.price)::bigint) AS price,
				ir.original_price,
					CASE
						WHEN (ir.v2_main_image_id IS NULL) THEN NULL::json
						ELSE ir.v2_main_image
					END AS v2_main_image,
				COALESCE(ir.product_url, i_1.product_url) AS product_url,
				COALESCE(ir.brand, i_1.brand) AS brand,
				COALESCE(ir.is_available, true) AS is_available,
				COALESCE(ir.category, i_1.category) AS product_category,
				COALESCE(ir.currency_code, ((i_1.currency_code)::text)::character varying) AS currency_code,
				i_1.ltk_url,
				ir.created_at,
				ir.updated_at,
				ir.deleted_at,
				ir.description,
				ir.sizes,
				ir.img_ids
			   FROM (products i_1
				 LEFT JOIN aggregated_products ir ON ((i_1.product_id = ir.product_id)))
	  ),
	agg_sizes as (
		select
		  products.product_id,
		  count(*) filter (
			where
			  products_retrieval_sizes_availability.available
		  ) as c_available_sizes,
		  count(*) filter (
			where
			  not products_retrieval_sizes_availability.available
		  ) as c_not_available_sizes,
		  count(*) as c_sizes
		from
		  products
		  left join products_retrieval_sizes_availability on products.product_id = products_retrieval_sizes_availability.product_id
		group by
		  products.product_id
	  ),
	collection_views as (
   	 select 
	   sciv.collection_id
	  from 
	   stats.collection_product_views as sciv
	   where date(sciv.created_at) < date(now()) and sciv.user_id = user_id_param
	   group by sciv.collection_id
   ),	
   collection_style_views as (
   	 select
	   sciv.collection_id
	  from 
	   stats.collection_style_views as sciv
	   where date(sciv.created_at) < date(now()) and sciv.user_id = user_id_param
	   group by sciv.collection_id
   ),
   col_with_sizes as (
		select
		  collections.id as collection_id,
	      collections.type,
		  count(*) filter (
			where
			  coalesce(
				(
				  100 * agg_sizes.c_available_sizes / agg_sizes.c_sizes
				)::numeric > 45::numeric,
				true
			  ) and coalesce(agg_p.is_available, true)
		  ) as c_available_collection_items,
	   	  count (*) filter (
		 	  where agg_p.V2_main_image::json->>'photograph_type_id'::text = '7'
		  ) as c_available_style_items
		from
		  collections
		  left join collection_products on collections.id = collection_products.collection_id
		  left join aggregated_products as agg_p on agg_p.product_id = collection_products.product_id 
		  left join agg_sizes on agg_sizes.product_id = agg_p.product_id
	   	  where collections.type = 'plain'
		group by
		  collections.id, collections.type
    ),
	col_style_with_sizes as (
		select
		  collections.id as collection_id,
		  collections.type,
		  count(*) filter (
			where
			  coalesce(
				(
				  100 * agg_sizes.c_available_sizes / agg_sizes.c_sizes
				)::numeric > 45::numeric,
				true
			  ) and coalesce(agg_p.is_available, true)
		  ) as c_available_collection_items,
		 count (*) filter (
		 	where agg_p.V2_main_image::json->>'photograph_type_id'::text = '7' or agg_p.V2_main_image::json->>'photograph_type_id' is null
		 ) as c_available_style_items
		from
		  collections
		  left join style_collection_products as iis on collections.id = iis.collection_id
		  left join aggregated_products as agg_p on agg_p.product_id = iis.product_id 
		  left join agg_sizes on agg_sizes.product_id = iis.product_id
		  where collections.type = 'style'
		group by
		  collections.id, collections.type
    ),
	available_col_style_main_item as (
		select 
			DISTINCT(iis.collection_id)
		from 
			style_collection_products iis
			left join view_virtual_items items on items.product_id = iis.main_product_id
			where items.is_available = true and (items.v2_main_image::json->>'photograph_type_id'::text = '7' or items.v2_main_image::json->>'photograph_type_id' is null)
	)
select 
	cs.collection_id,
	cs.score,
	c.type
from 
	model.collection_score as cs
	LEFT JOIN collections as c on c.id = cs.collection_id
	LEFT JOIN col_with_sizes on col_with_sizes.collection_id = cs.collection_id
	LEFT JOIN col_style_with_sizes as csws on csws.collection_id = cs.collection_id
	left join collection_views as cv on cv.collection_id = cs.collection_id
	left join collection_style_views as c_sv on c_sv.collection_id = cs.collection_id
	left join available_col_style_main_item as av_m_i on av_m_i.collection_id = cs.collection_id
	where (col_with_sizes.c_available_collection_items >= 2 and col_with_sizes.c_available_style_items > 0 and cv.collection_id is null) or (csws.c_available_collection_items >= 0 and csws.c_available_style_items > 0 and c_sv.collection_id is null and av_m_i.collection_id is not null)
	order by cs.score desc
	offset offset_param
	limit 1;
END;
$function$
;