WITH "applicant_counts" AS (
	SELECT
		count('applicants.id') AS applicants_count,
		calendar_events.id AS calendar_event_id
	FROM
		"applicants"
		INNER JOIN "group_events_applicants" ON "group_events_applicants"."applicant_id" = "applicants"."id"
		INNER JOIN "calendar_events" ON "calendar_events"."id" = "group_events_applicants"."calendar_event_id"
	WHERE
		"applicants"."company_id" = 90
		AND(calendar_events.id = group_events_applicants.calendar_event_id)
		AND "calendar_events"."company_id" = 90
		AND "calendar_events"."archive" = false
		AND "calendar_events"."from" >= '2022-05-24 21:00:00'
		AND "calendar_events"."from" < '2022-05-25 20:59:59'
		AND(calendar_events.calendarable_type = 'GroupEvent'
			AND(calendar_events.author_id IN('63408', '74995', '79065', '81034', '63715')
				))

	GROUP BY
		calendar_events.id
)
SELECT
	calendar_events.*
FROM ((
		SELECT
			calendar_events.*,
			array_agg(jobs_users.id) AS jobs_users_ids,
			array_agg(group_events_users.user_id) AS events_users_ids,
			(
				SELECT
					applicants_count AS participants_counter
				FROM
					applicant_counts
				WHERE
					calendar_events.id = applicant_counts.calendar_event_id)
			FROM
				"calendar_events"
				INNER JOIN group_events ON group_events.id = calendar_events.calendarable_id
					AND calendar_events.calendarable_type = 'GroupEvent'
			LEFT JOIN group_events_jobs ON group_events_jobs.group_event_id = group_events.id
			LEFT JOIN jobs ON group_events_jobs.job_id = jobs.id
			LEFT JOIN jobs_users ON jobs.id = jobs_users.job_id
				AND jobs_users.user_id = 2038
				AND jobs_users.deleted_at IS NULL
		LEFT JOIN group_events_users ON group_events_users.group_event_id = group_events.id
	WHERE
		"calendar_events"."company_id" = 90
		AND "calendar_events"."archive" = false
		AND "calendar_events"."from" >= '2022-05-24 21:00:00'
		AND "calendar_events"."from" < '2022-05-25 20:59:59'
		AND(calendar_events.calendarable_type = 'GroupEvent'
			AND(calendar_events.author_id IN('63408', '74995', '79065', '81034', '63715')
				OR group_events.author_id IN('63408', '74995', '79065', '81034', '63715')
				OR group_events_users.user_id IN('63408', '74995', '79065', '81034', '63715')))
	GROUP BY
		calendar_events.id,
		calendar_events.text,
		calendar_events.from,
		calendar_events.to,
		calendar_events.kind,
		calendar_events.author_id,
		calendar_events.company_id,
		calendar_events.archive,
		calendar_events.calendarable_type,
		calendar_events.calendarable_id,
		calendar_events.created_at,
		calendar_events.updated_at,
		calendar_events.locales,
		calendar_events.remove_from_dump_at)
UNION ALL (
	SELECT
		calendar_events.*,
		array_agg(jobs_users.id) AS jobs_users_ids,
		array_agg(events_users.user_id) AS events_users_ids,
		NULL AS participants_counter
	FROM
		"calendar_events"
		INNER JOIN events ON events.id = calendar_events.calendarable_id
			AND calendar_events.calendarable_type = 'Event'
	LEFT JOIN jobs_users ON events.job_id = jobs_users.job_id
		AND jobs_users.user_id = 2038
		AND jobs_users.deleted_at IS NULL
	LEFT JOIN events_users ON events.id = events_users.event_id
	INNER JOIN (
		SELECT
			"applicants"."id"
		FROM
			"applicants"
		WHERE
			"applicants"."company_id" = 90) AS policy_applicants ON events.applicant_id = policy_applicants.id
	WHERE
		"calendar_events"."company_id" = 90
		AND "calendar_events"."archive" = false
		AND "calendar_events"."from" >= '2022-05-24 21:00:00'
		AND "calendar_events"."from" < '2022-05-25 20:59:59'
		AND(calendar_events.calendarable_type = 'Event'
			AND(calendar_events.author_id IN('63408', '74995', '79065', '81034', '63715')
				OR events_users.user_id IN('63408', '74995', '79065', '81034', '63715')))
	GROUP BY
		calendar_events.id, calendar_events.text, calendar_events.from, calendar_events.to, calendar_events.kind, calendar_events.author_id, calendar_events.company_id, calendar_events.archive, calendar_events.calendarable_type, calendar_events.calendarable_id, calendar_events.created_at, calendar_events.updated_at, calendar_events.locales, calendar_events.remove_from_dump_at)) AS "calendar_events"