/*
Complete list of menus/positions per template and TE engine (old and new)
Possible to detect all differences when commented parts are uncommented
Where menu_to_be_rendered is blank - it means that it is AUTO (this applies only to new TE)
*/

SET @OLD_TEMPLATE_NAME = '';
SELECT
	/*count(distinct mt.menu_identificator) as menu_identificator_count,	*/
	mt.template_name,
	mt.engine_name,
	mt.menu_position,
	mt.menu_to_be_rendered,
	mt.menu_identificator
FROM
(
	SELECT 'old_te' AS engine_name, new_mt.menu_identificator, new_mt.template_name, new_mt.menu_to_be_rendered, new_mt.menu_position FROM
	(
		SELECT
			pl.template_name AS template_name,
			IF(pl.template_name = @OLD_TEMPLATE_NAME, @NR:=@NR+1, @NR:=1) AS menu_to_be_rendered,
			IF(pl.template_name <> @OLD_TEMPLATE_NAME, @OLD_TEMPLATE_NAME:=pl.template_name, @OLD_TEMPLATE_NAME),
			p.name AS menu_position,
			CONCAT_WS('-',
				pl.template_name,
				p.name,
				@NR
			) AS menu_identificator
		FROM flexiweb_template_engine.predefined_layouts AS pl
		LEFT JOIN flexiweb_template_engine.predefined_page_layouts AS ppl ON (ppl.predefined_layout_id = pl.id)
		LEFT JOIN flexiweb_template_engine.positions AS p ON (p.id = ppl.position_id)
		WHERE
			pl.name = 'default' AND
			ppl.page_id = 2 AND
			ppl.content_id = 0 AND
			ppl.module_name LIKE 'menu'
		ORDER BY pl.template_name, ppl.position_id
	) AS new_mt

	UNION

	SELECT 'new_te' AS engine_name, old_mt.menu_identificator, old_mt.template_name, old_mt.menu_to_be_rendered, old_mt.menu_position FROM
	(
		SELECT
			t.folder_name AS template_name,
			te.instance AS selected_instance,
			t.id AS selected_template_id,
			@MENU_TO_BE_RENDERED:=(
				SELECT if(`value` is not null, `value`, 'AUTO')
				FROM flexiweb.te_t_elements AS te2
				WHERE
					te2.contenttype_model = 'TeContenttype' AND
					te2.contenttype_fk = 'TeCMaster' AND
					te2.classname = 'TeEMenu' AND
					te2.`field` = 'menu_sortnum' AND
					te2.instance = selected_instance AND
					te2.template_id = selected_template_id
			) AS menu_to_be_rendered,
			te.`value` AS menu_position,
			IF(te.`value` = 'top_left', 1,
				IF(te.`value` = 'top', 2,
					IF(te.`value` = 'top_right', 3,
						IF(te.`value` = 'left', 4,
							IF(te.`value` = 'main', 5,
								IF(te.`value` = 'right', 6,
									IF(te.`value` = 'bottom_left', 7,
										IF(te.`value` = 'bottom', 8, 9)
									)
								)
							)
						)
					)
				)
			) AS menu_position_sortnum,
			(
				SELECT `value`
				FROM flexiweb.te_t_elements AS te2
				WHERE
					te2.contenttype_model = 'TeContenttype' AND
					te2.contenttype_fk = 'TeCMaster' AND
					te2.classname = 'TeEMenu' AND
					te2.`field` = 'start_level' AND
					te2.instance = selected_instance AND
					te2.template_id = selected_template_id
			) AS menu_start_level,
			CONCAT_WS('-',
				t.folder_name,
				te.`value`,
				@MENU_TO_BE_RENDERED
			) AS menu_identificator
		FROM flexiweb.te_t_elements AS te
		LEFT JOIN flexiweb.te_templates AS t ON t.id = te.template_id
		WHERE
		te.contenttype_model = 'TeContenttype' AND
		te.contenttype_fk = 'TeCMaster' AND
		te.classname = 'TeEMenu' AND
		te.`field` = 'position' AND
		te.instance <> ''
		HAVING menu_start_level IS NULL OR menu_start_level = 0
		ORDER BY t.folder_name, menu_position_sortnum
	) AS old_mt
) AS mt
/*GROUP BY mt.menu_identificator
HAVING menu_identificator_count < 2*/
ORDER BY mt.template_name, mt.engine_name, mt.menu_to_be_rendered;