with cte_1 as (
	select col_1,
		col_2,
		col_3
	from ${database_a}.table_1
	where col_2 <> 'DOMAIN_X' --		and col_3 = 'XXXXX' -- test case
),
cte_2 as (
	select col_4,
		col_5
	from ${database_b}.table_2
	where col_6 IN (
			select max(col_6) as maxdate
			from ${database_b}.table_2
		) -- avoids duplicates by picking only the lastest version
		--		and col_5 = 'XXXXX' -- test case
),
cte_3 as (
	select distinct alias_1.col_5,
		case
			when alias_2.col_1 IS NULL then concat('REGION', '/', alias_1.col_4)
			else concat(alias_2.col_2, '\\', alias_2.col_1)
		end as col_19
	from cte_2 alias_1
		inner join cte_1 alias_2 on alias_2.col_3 = alias_1.col_5
),
cte_4 as (
	select distinct alias_3.col_3,
		alias_4.col_7,
		regexp_replace(cast(alias_3.col_8 as string), '\\[|\\]|"', '') as col_20
	from ${database_c}.table_3 alias_3
		inner join ${database_c}.table_4 alias_4 on alias_3.col_3 = alias_4.col_3
	where cast(alias_3.col_8 as string) <> '["ALL"]'
		and (
			alias_3.col_9 <> 'AdminProfile1'
			and alias_3.col_9 <> 'AdminProfile2'
		) -- profiles on which we don't habilitate
		and alias_4.col_10 = TRUE -- users with access to the dashboard are only the users with access to the application
		--		and col_16 = 44407
),
cte_5 as(
	select distinct alias_3.col_3,
		alias_4.col_7,
		regexp_replace(cast(alias_3.col_8 as string), '\\[|\\]|"', '') as col_20
	from ${database_c}.table_3 alias_3
		inner join ${database_c}.table_4 alias_4 on alias_3.col_3 = alias_4.col_3
	where cast(alias_3.col_11 as string) = '["ALL"]'
		and (
			alias_3.col_9 <> 'AdminProfile1'
			and alias_3.col_9 <> 'AdminProfile2'
		) -- profiles on which we don't habilitate
		and alias_4.col_10 = TRUE -- users with access to the dashboard are only the users with access to the application
		--		and col_16 = aezez
),
cte_6 as (
	select cte_4.col_3,
		cte_4.col_7,
		alias_5.col_12 as col_21,
		alias_5.col_13
	from cte_4 lateral view posexplode(split(col_20, ',')) alias_5 as col_13,
		col_12
),
cte_7 as (
	select DISTINCT cte_6.col_3,
		cte_6.col_7,
		cte_6.col_21,
		alias_6.`level` as col_22
	from cte_6
		inner join ${database_d}.table_5 alias_6 on cte_6.col_21 = alias_6.functional_code
),
cte_8 as (
	select distinct cte_7.col_21,
		alias_7.col_15 as col_23
	from cte_7
		inner join ${database_e}.table_6 alias_7 on substring(
			alias_7.col_15,
			1,
			length(cte_7.col_21)
		) = cte_7.col_21 --	where cte_7.col_21 = 'A01'
),
cte_9 as (
	select cte_7.col_3,
		cte_7.col_7,
		cte_3.col_19,
		cte_7.col_21 as col_25,
		cte_7.col_22,
		cte_8.col_23,
		alias_6.col_16 as col_24
	from cte_7
		inner join cte_8 on cte_7.col_21 = cte_8.col_21
		inner join cte_3 on cte_3.col_5 = cte_7.col_3
		inner join ${database_d}.table_5 alias_6 on cte_8.col_23 = alias_6.functional_code --	where cte_7.col_3 in ('XXXXX1', 'XXXXX2', 'XXXXX3', 'XXXXX4') -- homol test case
),
cte_10 as (
	SELECT cte_5.col_3,
		cte_5.col_7,
		cte_3.col_19,
		cte_5.col_20 as col_27,
		alias_6.col_16 as col_24
	from cte_5
		inner join cte_3 on cte_3.col_5 = cte_5.col_3
		LEFT join ${database_d}.table_5 alias_6 on cte_5.col_20 = alias_6.functional_code
)
select DISTINCT cte_9.col_3,
	cte_9.col_7,
	cte_9.col_19,
	cte_9.col_23,
	cte_9.col_24
from cte_9
UNION
select DISTINCT cte_10.col_3,
	cte_10.col_7,
	cte_10.col_19,
	cte_10.col_27,
	cte_10.col_24
from cte_10