SELECT
	# parent item
	parent_settings.setting_id AS settings_id,
	parent_settings.setting_node_id AS node_parent_node_id,
	parent_settings.setting_datatype AS parent_datatype,
	parent_settings.setting_label AS label,
	parent_settings.setting_name AS `name`,
	# parent defaults
	default_parent_junctions.junction_varchar AS parent_default_varchar,
	default_parent_junctions.junction_int AS parent_default_int,
	default_parent_junctions.junction_decimal AS parent_default_decimal,
	parent_settings.setting_type AS `type`,
	# items
	cms_settings.setting_node_id AS item_node_id,
	cms_settings.setting_datatype AS item_datatype,
	cms_settings.setting_type AS item_type,
	cms_settings.setting_label AS item_label,
	cms_settings.setting_name AS `key`,
	# defaults
	default_junctions.junction_varchar AS default_varchar,
	default_junctions.junction_int AS default_int,
	default_junctions.junction_decimal AS default_decimal,
	# value
	cms_junctions.*,
	:page_node_id AS page_node_id

FROM cms_settings AS parent_settings

JOIN cms_nodes AS setting_parent_nodes 
ON setting_parent_nodes.node_id = parent_settings.setting_node_id
OR (!ISNULL(:ids) AND setting_parent_nodes.node_id = 1 AND parent_settings.setting_node_id = 1) # default record

LEFT JOIN cms_junctions AS default_parent_junctions 
ON parent_settings.setting_node_id = default_parent_junctions.junction_setting_node_id

JOIN cms_nodes AS setting_nodes
ON setting_nodes.node_parent_node_id = setting_parent_nodes.node_id
OR (!ISNULL(:ids) AND ISNULL(setting_nodes.node_parent_node_id) AND setting_parent_nodes.node_id = 1) # default record

JOIN cms_settings
ON cms_settings.setting_node_id = setting_nodes.node_id
OR (!ISNULL(:ids) AND cms_settings.setting_node_id = 1 AND setting_nodes.node_id = 1) # default record

LEFT JOIN cms_junctions AS default_junctions 
ON setting_nodes.node_id = default_junctions.junction_setting_node_id AND default_junctions.junction_node_id = 1

LEFT JOIN cms_junctions
ON cms_junctions.junction_node_id = :page_node_id AND cms_junctions.junction_setting_node_id = setting_nodes.node_id

WHERE CASE WHEN ISNULL(:ids) THEN (
	parent_settings.setting_name = :setting_name OR (
		ISNULL(:setting_name) AND setting_parent_nodes.node_parent_node_id = 0 # root nodes => node_parent_node_id = 0
	) 
	AND setting_parent_nodes.node_type = 'setting'
) ELSE ( # default record
	parent_settings.setting_id = 1
	AND cms_settings.setting_id = 1 # self join
) END
ORDER BY setting_parent_nodes.node_ordering, setting_nodes.node_ordering
