with tag_table as (select template_id,
                        string_agg(distinct tag_id, '|') as tag_id
                        from dynamic_descriptions.dynamic_descriptions_template_tags
                        group by 1
                        ),
synonym_table as (with synonymtext_table as (select slot_id,
                        string_agg(distinct dynamic_descriptions.dynamic_descriptions_synonymtext.text, '|') as synonymtext
                        from dynamic_descriptions.dynamic_descriptions_synonymtext
                        group by 1)
select distinct template_id,
max(case when index = 1 then synonymtext end) as synonym_1,
max(case when index = 2 then synonymtext end) as synonym_2,
max(case when index = 3 then synonymtext end) as synonym_3,
max(case when index = 4 then synonymtext end) as synonym_4,
max(case when index = 5 then synonymtext end) as synonym_5,
max(case when index = 6 then synonymtext end) as synonym_6,
max(case when index = 7 then synonymtext end) as synonym_7,
max(case when index = 8 then synonymtext end) as synonym_8,
max(case when index = 9 then synonymtext end) as synonym_9,
max(case when index = 10 then synonymtext end) as synonym_10,
max(case when index = 11 then synonymtext end) as synonym_11,
max(case when index = 12 then synonymtext end) as synonym_12
from dynamic_descriptions.dynamic_descriptions_synonymslot
inner join synonymtext_table
on dynamic_descriptions.dynamic_descriptions_synonymslot.id = synonymtext_table.slot_id
group by 1)
select dynamic_descriptions.dynamic_descriptions_template.id as "template_id",
dynamic_descriptions.dynamic_descriptions_template.category_id as "category_id",
dynamic_descriptions.dynamic_descriptions_template.language_id as "locale",
dynamic_descriptions.dynamic_descriptions_template.static,
template_type_id as "template_type",
dynamic_descriptions.dynamic_descriptions_variancename.name as "variance_name",
dynamic_descriptions.dynamic_descriptions_template.text,
title_table.title_1,
title_table.title_2,
title_table.title_3,
tag_table.tag_id as "tags",
synonym_table.synonym_1,
synonym_table.synonym_2,
synonym_table.synonym_3,
synonym_table.synonym_4,
synonym_table.synonym_5,
synonym_table.synonym_6,
synonym_table.synonym_7,
synonym_table.synonym_8,
synonym_table.synonym_9,
synonym_table.synonym_10,
synonym_table.synonym_11,
synonym_table.synonym_12
from dynamic_descriptions.dynamic_descriptions_template
--TITLES TABLE
left join (
select distinct template_id,
                max(case when rn=1 then text end) as title_1,
                max(case when rn=2 then text end) as title_2,
                max(case when rn=3 then text end) as title_3
            from            (
                            select template_id,text,
                            row_number() over(partition by template_id order by id) as rn
                            from dynamic_descriptions.dynamic_descriptions_title
                            ) as d_d_t
                            group by 1
            ) as title_table
        on title_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
--
left join tag_table
        on tag_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
left join synonym_table
        on synonym_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
left join dynamic_descriptions.dynamic_descriptions_variancename
        on dynamic_descriptions.dynamic_descriptions_template.variance_name_id = dynamic_descriptions.dynamic_descriptions_variancename.id
where template_type_id in ('descriptive','topical') --set template types
--and ((tag_table.tag_id ilike '%plural%' and not tag_table.tag_id ilike '%signup2%'))--tag_table.tag_id is null)) --tag filters
--and (dynamic_descriptions.dynamic_descriptions_template.text ilike '% my %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% me % ' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I''ve %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I''m %')
--and (dynamic_descriptions.dynamic_descriptions_template.text ilike '% our %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% us % ' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we''ve %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we''ll %')
--and dynamic_descriptions.dynamic_descriptions_template.language_id in ('en-gb') --set language
--and dynamic_descriptions.dynamic_descriptions_template.category_id in ('f02500_12','f03200_14') --set category
order by 2