-- User activity timeseries
WITH visits AS (
-- visits
SELECT user_id,
  TO_CHAR(DATE(ts_created), 'yyyy-mm-dd') AS date,
  object_type,
  'visit'::text AS activity,
  -- number of page views
  COUNT(*) AS N
FROM public.visits
GROUP BY 1,2,3,4),

search_q AS (
-- search qeuries
SELECT user_id,
  TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
  NULL::text AS object_type,
  CONCAT('search_query:',source) AS activity,
  -- number of searches run
  COUNT(*) AS N
FROM public.search_queries
GROUP BY 1,2,3,4),

search_c AS (
-- search clicks
SELECT user_id,
  TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
  object_type,
  CONCAT('search_click:',source) AS activity,
  -- number of clicks
  COUNT(*) AS N
FROM public.search_clicks
GROUP BY 1,2,3,4),

curation AS (
-- curation
SELECT user_id,
  TO_CHAR(DATE(ts_updated), 'yyyy-mm-dd'),
  object_type,
  CONCAT('curation:',action) AS activity,
  -- number of curations
  COUNT(*) AS N
FROM public.curation_history
WHERE action ILIKE '%custom_field%'
AND (field_id IN (3,4,8) OR field_id > 9999)
GROUP BY 1,2,3,4),

flags AS (
-- flags
SELECT user_id,
  TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
  object_type,
  CONCAT('flag:', deleted, ':',flag_type, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')) AS activity,
  -- number of flags
  COUNT(*) AS N
FROM public.flags
GROUP BY 1,2,3,4),

tags AS (
-- tags
SELECT tagger_id AS user_id,
  TO_CHAR(DATE(ts_tagged), 'yyyy-mm-dd'),
  object_type,
  CONCAT('tag:',tag_id) AS activity,
  -- number of flags
  COUNT(*) AS N
FROM public.tags
GROUP BY 1,2,3,4),

query_ts AS (
-- query executions
SELECT user_id,
  TO_CHAR(DATE(executed_at), 'yyyy-mm-dd'),
  'query'::TEXT AS object_type,
  CONCAT('query_execution:'::TEXT, CASE WHEN execution_error IS NULL THEN 0 ELSE 1 END, ':', session_type) AS activity,
  -- number of executions
  COUNT(*) AS N
FROM public.execution_event GROUP BY 1,2,3,4),

query_session AS (
-- query sessions
SELECT user_id,
  TO_CHAR(DATE(executed_at), 'yyyy-mm-dd'),
  'query'::TEXT AS object_type,
  'query_sessions'::TEXT AS activity,
  -- number of executions
  COUNT(DISTINCT session_id) AS N
FROM public.execution_event
GROUP BY 1,2,3,4),

conv_ans AS (SELECT user_id,
  TO_CHAR(DATE(post_ts_created), 'yyyy-mm-dd'),
  'conversation'::text AS object_type,
  'post_answer'::text AS activity,
  COUNT(*) AS N
FROM public.conversation WHERE post_type = 'answer'
GROUP BY 1,2,3,4),

conv_q AS (SELECT user_id,
  TO_CHAR(DATE(post_ts_created), 'yyyy-mm-dd'),
  'conversation'::text AS object_type,
  'post_question'::text AS activity,
  COUNT(*) AS N
FROM public.conversation WHERE post_type = 'question'
GROUP BY 1,2,3,4),

article_creation AS (SELECT user_id,
  TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
  'article'::text AS object_type,
  CONCAT('article_creation:'::text, deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')) AS activity,
  COUNT(*)
FROM public.article GROUP BY 1,2,3,4),

q_pub AS (SELECT user_id,
  TO_CHAR(DATE(ts_updated), 'yyyy-mm-dd'),
  object_type,
  'query_publish'::text AS activity,
  COUNT(*) AS N
FROM public.curation_history
WHERE action = 'query.publish' GROUP BY 1,2,3,4),

term_creation AS (SELECT created_by_id AS user_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'term'::text AS object_type,
CONCAT('term_creation:'::text, deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')) AS activity,
COUNT(*)
FROM public.terms GROUP BY 1,2,3,4),

bulk_action_count AS (SELECT actor_id,
TO_CHAR(DATE(ts_started), 'yyyy-mm-dd'),
CONCAT('bulk_action_count:'::text, action_status),
action_type,
COUNT(*)
FROM public.stewardship_bulk_action
WHERE actor_id != 0 GROUP BY 1,2,3,4),

bulk_action_update_count AS (SELECT actor_id,
TO_CHAR(DATE(ts_started), 'yyyy-mm-dd'),
CONCAT('bulk_action_update_count:'::text, action_status),
action_type,
SUM(updated_object_count)
FROM public.stewardship_bulk_action
WHERE actor_id != 0 GROUP BY 1,2,3,4),

business_policy_creation AS(SELECT owner_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'business_policy',
CONCAT('business_policy_creation:', deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')),
COUNT(*)
FROM business_policy GROUP BY 1,2,3,4),

domain_creation AS(SELECT user_id,
TO_CHAR(DATE(created_at), 'yyyy-mm-dd'),
'domain'::text,
CONCAT('domain_creation:'::text, CASE WHEN deleted_at IS NULL THEN false ELSE true END, ':', TO_CHAR(DATE(deleted_at), 'yyyy-mm-dd')),
COUNT(*)
FROM domains GROUP BY 1,2,3,4),

glossary_creation AS(SELECT created_by_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'glossary',
CONCAT('glossary_creation:', deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')),
COUNT(*)
FROM glossaries GROUP BY 1,2,3,4),

workflow_creation AS(SELECT creator_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'workflow',
CONCAT('workflow_creation:', deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd'), ':', workflow_status, ':', workflow_type),
COUNT(*)
FROM workflows GROUP BY 1,2,3,4),

workflow_request AS(SELECT requester_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'workflow',
CONCAT('workflow_request:', deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd'), ':',
request_status, ':', request_type, ':', TO_CHAR(ts_started_at, 'yyyy-mm-dd HH24-MM'), ':', TO_CHAR(ts_finished_at, 'yyyy-mm-dd HH24-MM')),
COUNT(*)
FROM workflow_requests GROUP BY 1,2,3,4),

workflow_req_action AS(SELECT actor_id,
TO_CHAR(DATE(ts_updated), 'yyyy-mm-dd'),
'workflow',
CONCAT('workflow_request_action:', action_type),
COUNT(*)
FROM workflow_request_actions GROUP BY 1,2,3,4),

policy_group AS(SELECT owner_id,
TO_CHAR(DATE(ts_created), 'yyyy-mm-dd'),
'policy_group',
CONCAT('policy_group_creation:', deleted, ':', TO_CHAR(DATE(ts_deleted), 'yyyy-mm-dd')),
COUNT(*)
FROM policy_group GROUP BY 1,2,3,4),

all_data AS (
(SELECT * FROM visits)
UNION ALL
(SELECT * FROM search_q)
UNION ALL
(SELECT * FROM search_c)
UNION ALL
(SELECT * FROM curation)
UNION ALL
(SELECT * FROM flags)
UNION ALL
(SELECT * FROM tags)
UNION ALL
(SELECT * FROM query_ts)
UNION ALL
(SELECT * FROM query_session)
UNION ALL
(SELECT * FROM conv_ans)
UNION ALL
(SELECT * FROM conv_q)
UNION ALL
(SELECT * FROM article_creation)
UNION ALL
(SELECT * FROM term_creation)
UNION ALL
(SELECT * FROM q_pub)
UNION ALL
(SELECT * FROM bulk_action_count)
UNION ALL
(SELECT * FROM bulk_action_update_count)
UNION ALL
(SELECT * FROM business_policy_creation)
UNION ALL
(SELECT * FROM domain_creation)
UNION ALL
(SELECT * FROM glossary_creation)
UNION ALL
(SELECT * FROM workflow_creation)
UNION ALL
(SELECT * FROM workflow_request)
UNION ALL
(SELECT * FROM workflow_req_action)
UNION ALL
(SELECT * FROM policy_group)
),
user_info AS(
SELECT US.user_id, US.user_name, US.user_email, US.user_type,
  STRING_AGG(AG.group_name, '|') AS groups,
  TO_CHAR(DATE(US.date_joined), 'yyyy-mm-dd') AS date_joined,
  TO_CHAR(DATE(US.ts_suspended), 'yyyy-mm-dd') AS date_suspended,
  CASE WHEN US.ts_suspended IS NULL THEN 'Active' ELSE 'Deactivated' END AS license_status
  FROM public.users AS US
  LEFT OUTER JOIN public.user_group_membership AS UGM ON US.user_id = UGM.user_id
  JOIN public.alation_group AS AG ON AG.group_id = UGM.group_id
  GROUP BY 1, 2, 3, 4, 6, 7, 8
  )

SELECT US.user_id,
  US.user_name,
  US.user_email,
  US.user_type,
  US.groups,
  AD.date AS activity_date,
  AD.object_type,
  AD.activity,
  AD.n AS N,
  US.date_joined,
  US.date_suspended,
  US.license_status
FROM all_data AS AD
JOIN user_info AS US ON AD.user_id = US.user_id;