WITH _discounts AS (
	SELECT
		d. ID,
		d. NAME,
		d.amount,
		d.percentage,
		d.minimum_order_price,
		ARRAY_AGG (DISTINCT tag.producttag_id) tags,
		ARRAY_AGG (DISTINCT cat.category_id) cats,
		ARRAY_AGG (
			DISTINCT customer.customergroup_id
		) customers
	FROM
		discount d
	LEFT JOIN discount_category cat ON (d. ID = cat.category_id)
	LEFT JOIN discount_product_tag tag ON (d. ID = tag.discount_id)
	LEFT JOIN discount_customer_group customer ON (customer.discount_id = d. ID)
	WHERE
		d.is_active = TRUE
	AND CURRENT_DATE BETWEEN start_date
	AND end_date
	GROUP BY
		d. ID
),
 dataset AS (
	SELECT
		product. ID,
		manufacturer_id,
		category_id,
		product.grup_id,
		round(
			COALESCE (
				(
					SELECT
						(
							CASE
							WHEN d.percentage IS NOT NULL THEN
								(100 - d.percentage) / 100.0 * COALESCE (
									product.discount_price,
									product.price
								)
							ELSE
								COALESCE (
									product.discount_price,
									product.price
								) - d.amount
							END
						)
					FROM
						_discounts d
					WHERE
						(
							d.cats = '{NULL}' :: INT []
							OR product.category_id = ANY (d.cats)
						)
					AND (
						d.customers = '{NULL}' :: INT []
						OR 1 = ANY (d.customers)
					)
					AND (
						d.tags = '{NULL}' :: INT []
						OR d.tags && ARRAY (
							SELECT
								producttag_id
							FROM
								product_tags
							WHERE
								product_id = product. ID
						)
					)
					ORDER BY
						COALESCE (
							d.percentage * product.price,
							d.amount
						) DESC
					LIMIT 1
				),
				product.discount_price,
				product.price
			),
			2
		) price
	FROM
		product
	WHERE
		product.active = TRUE
) SELECT
	COUNT (product. ID),
	NULL AS ID,
	NULL AS NAME,
	NULL AS slug,
	0 AS FILTER
FROM
	dataset product
UNION ALL
	(
		SELECT
			COUNT (dataset. ID),
			M . ID AS ID,
			M . NAME,
			NULL AS slug,
			1 AS FILTER
		FROM
			dataset
		JOIN manufacturer M ON (M . ID = dataset.manufacturer_id)
		GROUP BY
			M . ID
		ORDER BY
			M . NAME
	)
UNION ALL
	(
		SELECT
			COUNT (dataset. ID),
			T . ID AS ID,
			T . NAME AS NAME,
			T .slug AS slug,
			4 AS FILTER
		FROM
			dataset
		JOIN product_tags rel ON (rel.product_id = dataset. ID)
		JOIN product_tag T ON (T . ID = rel.producttag_id)
		GROUP BY
			T . ID
		ORDER BY
			T . NAME
	)
UNION ALL
	(
		SELECT
			COUNT (dataset. ID),
			C . ID AS ID,
			C . NAME,
			C .slug,
			2 AS FILTER
		FROM
			category C
		JOIN category AS node ON (
			C .tree_id = node.tree_id
			AND node.lft > C .lft - 1
			AND node.rght < C .rght + 1
		)
		JOIN dataset ON (dataset.category_id = node. ID)
		WHERE
			C .parent_id IS NULL
		GROUP BY
			C . ID
	)
UNION ALL
	(
		SELECT
			COUNT (DISTINCT dataset. ID),
			brand. ID,
			brand. NAME,
			brand.slug,
			3 AS FILTER
		FROM
			dataset
		JOIN vehicle_grup_id rel ON (rel.grup_id = dataset.grup_id)
		JOIN vehicle v ON (rel.vehicle_id = v. ID)
		JOIN vehicle_tree model_type ON (
			model_type. ID = v.vehicle_model_type_id
		)
		JOIN vehicle_tree model ON (model. ID = model_type.parent_id)
		JOIN vehicle_tree brand ON (brand. ID = model.parent_id)
		GROUP BY
			brand. ID
		ORDER BY
			brand. NAME
	)