WITH total_attribute AS (
SELECT 'attribute|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.rdbms_columns
 GROUP BY 1, 2, 3),

total_table AS (
SELECT 'table|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.rdbms_tables
 GROUP BY 1, 2, 3),

total_schema AS (
SELECT 'schema|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.rdbms_schemas
 GROUP BY 1, 2, 3),

total_data AS (
SELECT 'data|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.rdbms_datasources
 GROUP BY 1, 2, 3),

total_article AS (
SELECT 'article|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.article
 GROUP BY 1, 2, 3),

total_query AS (
SELECT 'query|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.query
WHERE discarded = FALSE GROUP BY 1, 2, 3),

total_bi_folder AS (
SELECT 'bi_folder|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.bi_folder
 GROUP BY 1, 2, 3),

total_bi_report AS (
SELECT 'bi_report|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.bi_report
 GROUP BY 1, 2, 3),

total_bi_datasource AS (
SELECT 'bi_datasource|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.bi_datasources
 GROUP BY 1, 2, 3),

total_bi_server AS (
SELECT 'bi_server|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.bi_server
 GROUP BY 1, 2, 3),

total_directory AS (
SELECT 'directory|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.directories
 GROUP BY 1, 2, 3),

total_filesystem AS (
SELECT 'filesystem|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.filesystems
 GROUP BY 1, 2, 3),

total_file AS (
SELECT 'file|total'::text AS measure,
NULL::char AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.files
 GROUP BY 1, 2, 3),

query_title AS (
SELECT 'query|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt, COUNT(title) AS N
FROM public.query
WHERE LENGTH(title) > 0 AND discarded = FALSE GROUP BY 1, 2, 3),

query_description AS (
SELECT 'query|description'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, NULL::char AS deletion_dt, COUNT(description) AS N
FROM public.query
WHERE LENGTH(description) > 0 AND discarded = FALSE GROUP BY 1, 2, 3),


attribute_title AS (
SELECT 'attribute|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(title) AS N
FROM public.rdbms_columns
WHERE LENGTH(title) > 0  GROUP BY 1, 2, 3),

attribute_description AS (
SELECT 'attribute|description'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(description) AS N
FROM public.rdbms_columns
WHERE LENGTH(description) > 0  GROUP BY 1, 2, 3),

table_title AS (
SELECT 'table|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(title) AS N
FROM public.rdbms_tables
WHERE LENGTH(title) > 0  GROUP BY 1, 2, 3),

table_description AS (
SELECT 'table|description'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(description) AS N
FROM public.rdbms_tables
WHERE LENGTH(description) > 0  GROUP BY 1, 2, 3),


schema_title AS (
SELECT 'schema|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(title) AS N
FROM public.rdbms_schemas
WHERE LENGTH(title) > 0  GROUP BY 1, 2, 3),

schema_description AS (
SELECT 'schema|description'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(description) AS N
FROM public.rdbms_schemas
WHERE LENGTH(description) > 0  GROUP BY 1, 2, 3),

data_title AS (
SELECT 'data|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(title) AS N
FROM public.rdbms_datasources
WHERE LENGTH(title) > 0  GROUP BY 1, 2, 3),

data_description AS (
SELECT 'data|description'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(description) AS N
FROM public.rdbms_datasources
WHERE LENGTH(description) > 0  GROUP BY 1, 2, 3),

article_title AS (
SELECT 'article|title'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(title) AS N
FROM public.article
WHERE LENGTH(title) > 0  GROUP BY 1, 2, 3),

user_ts AS(SELECT 'user timeseries'::text, TO_CHAR(DATE(date_joined), 'yyyy-mm-dd') AS creation_dt,
CASE WHEN is_active = True THEN NULL::char ELSE TO_CHAR(DATE(last_login), 'yyyy-mm-dd') END AS deletion_dt,
COUNT(*) FROM public.users GROUP BY 1,2,3),


conv_ts AS (SELECT 'conversation timeseries'::text, TO_CHAR(DATE(thread_ts_created), 'yyyy-mm-dd') AS creation_dt,
TO_CHAR(DATE(thread_ts_deleted), 'yyyy-mm-dd') AS deletion_dt, COUNT(*) AS N
FROM public.conversation GROUP BY 1,2,3),

exec_ts AS (SELECT 'execution timeseries'::text, TO_CHAR(DATE(executed_at), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.execution_event GROUP BY 1,2,3),

flag_ts AS(SELECT 'flag ts'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, NULL::char AS deletion_dt, COUNT(*) AS N
FROM public.flags WHERE is_propagated = False GROUP BY 1, 2, 3),

search_ts AS (SELECT 'search timeseries'::text, TO_CHAR(DATE(SQ.ts_created), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt, COUNT(DISTINCT SQ.id) AS N
FROM public.search_queries AS SQ
JOIN public.search_clicks AS SC USING(request_id)
WHERE SQ.source NOT IN ('search_custom_action_menu','imention',
'table_select_view','article_child_view','mention_search')
AND SQ.source IS NOT NULL GROUP BY 1,2,3),

published_q AS (SELECT 'published queries'::text as measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt,
COUNT(*) AS N FROM public.query
WHERE published = TRUE AND discarded = False GROUP BY 1,2,3),

pub_exec_ts AS (SELECT 'published_execution timeseries'::text, TO_CHAR(DATE(EE.executed_at), 'yyyy-mm-dd') AS creation_dt,
NULL::char AS deletion_dt, COUNT(EE.*) AS N
FROM public.execution_event AS EE
JOIN public.query AS QU USING (query_id)
WHERE QU.published = True GROUP BY 1,2,3),

-- NEW: combining visits data
visit_ts AS(SELECT 'visits'::text, TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'), NULL::char, COUNT(*) AS n FROM public.visits GROUP BY 1,2,3),

-- NEW: combining mentions data
mentions_data AS(WITH article_mention_st AS (WITH article_article_text_mention_st AS(SELECT 'article'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt,
UNNEST(REGEXP_MATCHES(article_text, 'data-otype="(.+?)" href=','g')) AS target FROM public.article),

full_data AS((SELECT * FROM article_article_text_mention_st))
SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1,2,3,4),

query_mention_st AS (WITH query_description_mention_st AS(SELECT 'query'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, NULL::char AS deletion_dt,
UNNEST(REGEXP_MATCHES(description, 'data-otype="(.+?)" href=','g')) AS target FROM public.query),

full_data AS((SELECT * FROM query_description_mention_st))
SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1, 2,3,4),
schema_mention_st AS (WITH schema_description_mention_st AS(SELECT 'schema'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt,
UNNEST(REGEXP_MATCHES(description, 'data-otype="(.+?)" href=','g')) AS target FROM public.rdbms_schemas),

full_data AS((SELECT * FROM schema_description_mention_st))

SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1, 2,3,4),

data_mention_st AS (WITH data_description_mention_st AS(SELECT 'data'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt,
UNNEST(REGEXP_MATCHES(description, 'data-otype="(.+?)" href=','g')) AS target FROM public.rdbms_datasources),

full_data AS((SELECT * FROM data_description_mention_st))
SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1, 2,3,4),

table_mention_st AS (WITH table_description_mention_st AS(SELECT 'table'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt,
UNNEST(REGEXP_MATCHES(description, 'data-otype="(.+?)" href=','g')) AS target FROM public.rdbms_tables),

full_data AS((SELECT * FROM table_description_mention_st))
SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1, 2,3,4),

attribute_mention_st AS (WITH attribute_description_mention_st AS(SELECT 'attribute'::text AS source,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS creation_dt, TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS deletion_dt,
UNNEST(REGEXP_MATCHES(description, 'data-otype="(.+?)" href=','g')) AS target FROM public.rdbms_columns),

full_data AS((SELECT * FROM attribute_description_mention_st))
SELECT source, creation_dt, deletion_dt, target, COUNT(*) AS N FROM full_data GROUP BY 1, 2,3,4),

all_the_data AS((SELECT * FROM article_mention_st)
UNION ALL
(SELECT * FROM query_mention_st)
UNION ALL
(SELECT * FROM schema_mention_st)
UNION ALL
(SELECT * FROM data_mention_st)
UNION ALL
(SELECT * FROM table_mention_st)
UNION ALL
(SELECT * FROM attribute_mention_st))
SELECT CONCAT('mentions|'::text, source,'|', target), creation_dt, deletion_dt, SUM(n) AS N FROM all_the_data GROUP BY 1,2,3),

total_terms AS (SELECT 'terms|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS cre_date,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS del_date, COUNT(*) AS N
FROM public.terms
GROUP BY 1, 2, 3),

total_dochubs AS (SELECT 'dochubs|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS cre_date,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS del_date, COUNT(*) AS N
FROM public.documenthubs
GROUP BY 1, 2, 3),

total_glossaries AS(SELECT 'glossaries|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS cre_date,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS del_date, COUNT(*) AS N
FROM public.glossaries
 GROUP BY 1, 2, 3),

total_business_policies AS(SELECT 'business_policies|total'::text AS measure,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS cre_date,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS del_date, COUNT(*) AS N
FROM public.business_policy
GROUP BY 1, 2, 3),

total_data_policies AS(SELECT 'data_policies|total'::text AS measure,
TO_CHAR(DATE(policy_ts_created), 'yyyy-mm-dd') AS cre_date,
TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd') AS del_date, COUNT(*) AS N
FROM public.data_policy
 GROUP BY 1, 2, 3)

(SELECT * FROM total_attribute)
UNION ALL
(SELECT * FROM total_table)
UNION ALL
(SELECT * FROM total_schema)
UNION ALL
(SELECT * FROM total_data)
UNION ALL
(SELECT * FROM total_article)
UNION ALL
(SELECT * FROM total_terms)
UNION ALL
(SELECT * FROM total_dochubs)
UNION ALL
(SELECT * FROM total_glossaries)
UNION ALL
(SELECT * FROM total_business_policies)
UNION ALL
(SELECT * FROM total_data_policies)
UNION ALL
(SELECT * FROM total_query)
UNION ALL
(SELECT * FROM total_bi_folder)
UNION ALL
(SELECT * FROM total_bi_report)
UNION ALL
(SELECT * FROM total_bi_datasource)
UNION ALL
(SELECT * FROM total_bi_server)
UNION ALL
(SELECT * FROM total_directory)
UNION ALL
(SELECT * FROM total_filesystem)
UNION ALL
(SELECT * FROM total_file)
UNION ALL
(SELECT * FROM query_title)
UNION ALL
(SELECT * FROM query_description)
UNION ALL
(SELECT * FROM attribute_title)
UNION ALL
(SELECT * FROM attribute_description)
UNION ALL
(SELECT * FROM table_title)
UNION ALL
(SELECT * FROM table_description)
UNION ALL
(SELECT * FROM schema_title)
UNION ALL
(SELECT * FROM schema_description)
UNION ALL
(SELECT * FROM data_title)
UNION ALL
(SELECT * FROM data_description)
UNION ALL
(SELECT * FROM article_title)
UNION ALL
(SELECT * FROM user_ts)
UNION ALL
(SELECT * FROM conv_ts)
UNION ALL
(SELECT * FROM exec_ts)
UNION ALL
(SELECT * FROM flag_ts)
UNION ALL
(SELECT * FROM search_ts)
UNION ALL
(SELECT * FROM published_q)
UNION ALL
(SELECT * FROM pub_exec_ts)
UNION ALL
(SELECT * FROM visit_ts)
UNION ALL
(SELECT * FROM mentions_data);