Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

301 KB
Raw Blame
import crypto from 'crypto'
import { gzipSync, gunzipSync } from 'zlib'
import _isEmpty from 'lodash/isEmpty'
import _split from 'lodash/split'
import _reverse from 'lodash/reverse'
import _size from 'lodash/size'
import _includes from 'lodash/includes'
import _map from 'lodash/map'
import _toUpper from 'lodash/toUpper'
import _head from 'lodash/head'
import _join from 'lodash/join'
import _isArray from 'lodash/isArray'
import _startsWith from 'lodash/startsWith'
import _sortBy from 'lodash/sortBy'
import _reduce from 'lodash/reduce'
import _keys from 'lodash/keys'
import _last from 'lodash/last'
import _replace from 'lodash/replace'
import _some from 'lodash/some'
import _find from 'lodash/find'
import _isString from 'lodash/isString'
import _values from 'lodash/values'
import _round from 'lodash/round'
import _filter from 'lodash/filter'
import dayjs from 'dayjs'
import utc from 'dayjs/plugin/utc'
import dayjsTimezone from 'dayjs/plugin/timezone'
import isSameOrBefore from 'dayjs/plugin/isSameOrBefore'
import {
  Injectable,
  BadRequestException,
  InternalServerErrorException,
  HttpException,
  HttpStatus,
  UnprocessableEntityException,
  PreconditionFailedException,
  PayloadTooLargeException,
} from '@nestjs/common'
import { isbot } from 'isbot'

import {
  DEFAULT_TIMEZONE,
  PlanCode,
  getSessionReplayQuota,
  getSessionReplayRetentionEntitlement,
} from '../user/entities/user.entity'
import {
  redis,
  UNIQUE_SESSION_LIFE_TIME,
  ONLINE_VISITORS_WINDOW_MINUTES,
  TRAFFIC_COLUMNS,
  ALL_COLUMNS,
  CAPTCHA_COLUMNS,
  ERROR_COLUMNS,
  PERFORMANCE_COLUMNS,
  MIN_PAGES_IN_FUNNEL,
  MAX_PAGES_IN_FUNNEL,
  REDIS_USERS_COUNT_KEY,
  REDIS_PROJECTS_COUNT_KEY,
  REDIS_EVENTS_COUNT_KEY,
  TRAFFIC_METAKEY_COLUMNS,
  REDIS_TRIALS_COUNT_KEY,
} from '../common/constants'
import { SaltService } from './salt.service'
import {
  BotDetectionService,
  BotEndpoint,
  BotDetectionResult,
} from './bot-detection.service'
import { AppLoggerService } from '../logger/logger.service'
import { clickhouse } from '../common/integrations/clickhouse'
import { getDomainsForRefName } from './utils/referrers.map'
import {
  calculateRelativePercentage,
  getIPDetails,
  hash,
  millisecondsToSeconds,
  sumArrays,
  formatDuration,
} from '../common/utils'
import { isIpInRange } from '../common/ip-range'
import { PageviewsDto } from './dto/pageviews.dto'
import { EventsDto } from './dto/events.dto'
import { ProjectService } from '../project/project.service'
import { UserService } from '../user/user.service'
import { BotsProtectionLevel, Project } from '../project/entity/project.entity'
import { ChartRenderMode, TimeBucketType } from './dto/getData.dto'
import { GetCustomEventMetadata } from './dto/get-custom-event-meta.dto'
import {
  PerformanceCHResponse,
  CustomsCHResponse,
  CustomsCHAggregatedResponse,
  TrafficCEFilterCHResponse,
  BirdseyeCHResponse,
  TrafficCHResponse,
  IGetGroupFromTo,
  GetFiltersQuery,
  IJourney,
  IJourneys,
  IJourneyNodeDetails,
  IJourneyLinkDetails,
  IExtractChartData,
  IGenerateXAxis,
  IAggregatedMetadata,
  IFunnelCHResponse,
  IFunnel,
  IFunnelBreakdowns,
  IConversionTimeMetric,
  IOverall,
  IOverallPerformance,
  IPageflow,
  PerfMeasure,
  PropertiesCHResponse,
  IPageProperty,
  ICustomEvent,
} from './interfaces'
import { ErrorDto } from './dto/error.dto'
import { GetPagePropertyMetaDto } from './dto/get-page-property-meta.dto'
import { DATA_DELETION_EVENT_TYPES } from './dto/data-deletion.dto'
import { MAX_USER_PROFILE_ID_LENGTH } from './dto/identify.dto'
import { ProjectViewCustomEventMetaValueType } from '../project/entity/project-view-custom-event.entity'
import { ProjectViewCustomEventDto } from '../project/dto/create-project-view.dto'
import { UAParser } from '@ua-parser-js/pro-business'
import { extensions } from './utils/ua-parser'
import { In, Not } from 'typeorm'
import { SessionReplayS3Service } from './session-replay-s3.service'
import { SessionReplayPrivacyMode } from './dto/session-replay.dto'

dayjs.extend(utc)
dayjs.extend(dayjsTimezone)
dayjs.extend(isSameOrBefore)

const isValidLocale = (lc: string): boolean => {
  try {
    Intl.getCanonicalLocales(lc.replace(/_/g, '-'))
    return true
  } catch {
    return false
  }
}

// 2 minutes
const hasTimeComponent = (date: string): boolean => /\d{2}:\d{2}/.test(date)

const LEGACY_SESSION_FLAG = '1'

redis.defineCommand('swetrixResolveSession', {
  numberOfKeys: 1,
  lua: `
    local existing = redis.call('GET', KEYS[1])
    if existing and existing ~= ARGV[3] then
      redis.call('EXPIRE', KEYS[1], ARGV[2])
      return {existing, '0'}
    end
    redis.call('SET', KEYS[1], ARGV[1], 'EX', ARGV[2])
    if existing == ARGV[3] then
      return {ARGV[1], '0'}
    end
    return {ARGV[1], '1'}
  `,
})

redis.defineCommand('swetrixPeekSession', {
  numberOfKeys: 1,
  lua: `
    local existing = redis.call('GET', KEYS[1])
    if not existing then
      return ''
    end
    if existing == ARGV[3] then
      local ttl = redis.call('TTL', KEYS[1])
      if ttl < 0 then
        ttl = tonumber(ARGV[2])
      end
      redis.call('SET', KEYS[1], ARGV[1], 'EX', ttl)
      return ARGV[1]
    end
    return existing
  `,
})

interface RedisWithSessionScripts {
  swetrixResolveSession(
    key: string,
    candidateSid: string,
    ttlSeconds: number,
    legacyFlag: string,
  ): Promise<[string, string]>
  swetrixPeekSession(
    key: string,
    candidateSid: string,
    ttlSeconds: number,
    legacyFlag: string,
  ): Promise<string>
}

const sessionScripts = redis as unknown as RedisWithSessionScripts

const LIVE_SESSION_THRESHOLD_SECONDS = 120
// The concurrency sweep fans every active minute out into a row per minute of
// the liveness window, so cap how far back it can be reconstructed to keep the
// scan bounded — period=all on an old project would otherwise read the entire
// event history
const MAX_CONCURRENCY_WINDOW_MINUTES = 366 * 24 * 60
const MAX_FILTERS = 100
const MAX_FILTER_VALUES = 100
// Journeys keep only the first {steps} distinct pages of a session, but the
// dedupe happens after aggregation, so the cap has to sit far above {steps}:
// a session that reloads one page N times still needs its later, different
// pages to survive. 1000 raw pageviews is orders of magnitude beyond any real
// session while keeping a bot's session array bounded.
const MAX_JOURNEY_PAGEVIEWS_PER_SESSION = 1000

// Synthetic link target marking sessions whose journey ended at the source
// node; must match the frontend's Sankey exit node identifier.
const JOURNEY_EXIT_TARGET = '__exit__'

// Event types that can be targeted by the data-deletion tool.
const DELETABLE_EVENT_TYPES = [...DATA_DELETION_EVENT_TYPES]
// The subset that actually lives in the `events` table. Session replays are
// stored separately (session_replay_chunks + object storage), so they are
// counted and deleted on their own path.
const EVENT_TABLE_DELETION_TYPES = DELETABLE_EVENT_TYPES.filter(
  (type) => type !== 'session_replay',
)
// Sentinel bounds used when no explicit date range is given, so that
// session-scoped filters (entry/exit page, referrer) still resolve.
const DELETION_MIN_DATE = '2000-01-01 00:00:00'
const DELETION_MAX_DATE = '2100-01-01 00:00:00'
const DELETION_TIMELINE_BUCKETS = 40
const DELETION_TIMELINE_MAX_POINTS = 60

export interface DataDeletionPreview {
  // count of matching rows, keyed by event type
  counts: Record<string, number>
  total: number
  // gap-filled, uniform buckets for the preview sparkline
  timeline: { x: string[]; counts: number[] }
}
const SESSION_REPLAY_RETENTION_VALUES = [30, 90, 365, 1825] as const
const MAX_SESSION_REPLAY_EVENTS_PER_CHUNK = 1000
const MAX_SESSION_REPLAY_EVENT_BYTES = 5 * 1024 * 1024
const MAX_SESSION_REPLAY_CHUNK_BYTES = 15 * 1024 * 1024
const MAX_SESSION_REPLAY_CHUNKS_PER_REPLAY = 1200
const MAX_SESSION_REPLAY_EVENTS_PER_REPLAY = 100000
const MAX_SESSION_REPLAY_BYTES_PER_REPLAY = 150 * 1024 * 1024
const MAX_SESSION_REPLAY_DURATION_MS = 30 * 60 * 1000
const SESSION_REPLAY_CLEANUP_BATCH_SIZE = 500
const SESSION_REPLAY_CHUNK_FETCH_CONCURRENCY = 6
const SESSION_REPLAY_CHUNK_INDEX_RANGE = MAX_SESSION_REPLAY_CHUNKS_PER_REPLAY
const RESERVE_REPLAY_START_LUA = `
local activeReplayKey = KEYS[1]
local proposedReplayId = ARGV[1]
local chunkIndexRange = tonumber(ARGV[2])
local ttl = tonumber(ARGV[3])
local maxDurationMs = tonumber(ARGV[4])
local time = redis.call("TIME")
local currentTimeMs = tonumber(time[1]) * 1000 + math.floor(tonumber(time[2]) / 1000)

local replayId = redis.call("HGET", activeReplayKey, "replayId")
local replayStartTs = tonumber(redis.call("HGET", activeReplayKey, "replayStartTs") or "")
local nextChunkIndex = tonumber(redis.call("HGET", activeReplayKey, "nextChunkIndex") or "0")

if
  replayId == false or replayId == nil or replayId == "" or
  replayStartTs == nil or replayStartTs + maxDurationMs <= currentTimeMs
then
  replayId = proposedReplayId
  replayStartTs = currentTimeMs
  nextChunkIndex = 0
end

local allocatedChunkIndex = nextChunkIndex
redis.call(
  "HSET",
  activeReplayKey,
  "replayId",
  replayId,
  "replayStartTs",
  tostring(replayStartTs),
  "nextChunkIndex",
  allocatedChunkIndex + chunkIndexRange
)
redis.call("EXPIRE", activeReplayKey, ttl)

return { replayId, tostring(allocatedChunkIndex) }
`
const RESERVE_REPLAY_USAGE_LUA = `
local reservationKey = KEYS[1]
local counterKey = KEYS[2]
local quota = tonumber(ARGV[1])
local monthlyUsage = tonumber(ARGV[2]) or 0
local ttl = tonumber(ARGV[3])

if redis.call("EXISTS", reservationKey) == 1 then
  return 0
end

local reservedUsage = tonumber(redis.call("GET", counterKey) or "0")
local currentUsage = reservedUsage
if monthlyUsage > currentUsage then
  currentUsage = monthlyUsage
end

if currentUsage >= quota then
  return -1
end

redis.call("SET", reservationKey, "1", "EX", ttl)
redis.call("SET", counterKey, currentUsage + 1, "EX", ttl)
return 1
`
const RELEASE_REPLAY_USAGE_LUA = `
if redis.call("DEL", KEYS[1]) == 1 then
  local current = tonumber(redis.call("GET", KEYS[2]) or "0")
  if current > 0 then
    redis.call("DECR", KEYS[2])
  end
  return 1
end
return 0
`
const RESERVE_REPLAY_CHUNK_LUA = `
local chunkSetKey = KEYS[1]
local countersKey = KEYS[2]
local firstTsKey = KEYS[3]
local lastTsKey = KEYS[4]
local maxChunks = tonumber(ARGV[1])
local maxEvents = tonumber(ARGV[2])
local maxBytes = tonumber(ARGV[3])
local maxDurationMs = tonumber(ARGV[4])
local chunkIndex = ARGV[5]
local eventCount = tonumber(ARGV[6])
local uncompressedBytes = tonumber(ARGV[7])
local firstTimestamp = tonumber(ARGV[8])
local lastTimestamp = tonumber(ARGV[9])
local ttl = tonumber(ARGV[10])

if redis.call("SADD", chunkSetKey, chunkIndex) == 0 then
  return -4
end

local chunks = redis.call("HINCRBY", countersKey, "chunks", 1)
local events = redis.call("HINCRBY", countersKey, "events", eventCount)
local bytes = redis.call("HINCRBY", countersKey, "bytes", uncompressedBytes)

if firstTimestamp ~= nil then
  redis.call("ZADD", firstTsKey, firstTimestamp, chunkIndex)
end

if lastTimestamp ~= nil then
  redis.call("ZADD", lastTsKey, lastTimestamp, chunkIndex)
end

local first = redis.call("ZRANGE", firstTsKey, 0, 0, "WITHSCORES")
local last = redis.call("ZREVRANGE", lastTsKey, 0, 0, "WITHSCORES")
local duration = 0

if first[2] ~= nil and last[2] ~= nil then
  duration = tonumber(last[2]) - tonumber(first[2])
end

local result = 1
if chunks > maxChunks then
  result = -1
elseif events > maxEvents then
  result = -2
elseif bytes > maxBytes then
  result = -3
elseif duration > maxDurationMs then
  result = -5
end

if result ~= 1 then
  redis.call("SREM", chunkSetKey, chunkIndex)
  redis.call("HINCRBY", countersKey, "chunks", -1)
  redis.call("HINCRBY", countersKey, "events", -eventCount)
  redis.call("HINCRBY", countersKey, "bytes", -uncompressedBytes)
  redis.call("ZREM", firstTsKey, chunkIndex)
  redis.call("ZREM", lastTsKey, chunkIndex)
  return result
end

redis.call("EXPIRE", chunkSetKey, ttl)
redis.call("EXPIRE", countersKey, ttl)
redis.call("EXPIRE", firstTsKey, ttl)
redis.call("EXPIRE", lastTsKey, ttl)
return 1
`
const RELEASE_REPLAY_CHUNK_LUA = `
local chunkSetKey = KEYS[1]
local countersKey = KEYS[2]
local firstTsKey = KEYS[3]
local lastTsKey = KEYS[4]
local chunkIndex = ARGV[1]
local eventCount = tonumber(ARGV[2])
local uncompressedBytes = tonumber(ARGV[3])

if redis.call("SREM", chunkSetKey, chunkIndex) ~= 1 then
  return 0
end

local chunks = redis.call("HINCRBY", countersKey, "chunks", -1)
redis.call("HINCRBY", countersKey, "events", -eventCount)
redis.call("HINCRBY", countersKey, "bytes", -uncompressedBytes)
redis.call("ZREM", firstTsKey, chunkIndex)
redis.call("ZREM", lastTsKey, chunkIndex)

if chunks <= 0 then
  redis.call("DEL", chunkSetKey, countersKey, firstTsKey, lastTsKey)
end

return 1
`

const SOFTWARE_WITH_PATCH_VERSION = ['GameVault']

type FunnelStepDetails = Record<
  keyof Required<IFunnelBreakdowns>,
  Record<number, Record<string, number>>
>

const mapLimit = async <T, R>(
  items: T[],
  limit: number,
  fn: (item: T, index: number) => Promise<R>,
): Promise<R[]> => {
  const results: R[] = new Array(items.length)
  const concurrency = Math.max(1, Math.min(limit, items.length))
  let nextIndex = 0

  const workers = Array.from({ length: concurrency }).map(async () => {
    while (true) {
      const current = nextIndex++
      if (current >= items.length) {
        break
      }
      results[current] = await fn(items[current], current)
    }
  })

  await Promise.all(workers)
  return results
}

const GMT_0_TIMEZONES = [
  'Atlantic/Azores',
  'Etc/GMT',
  // 'Africa/Casablanca',
]

const MEASURES_MAP = {
  average: 'avg',
  median: 'median',
  p95: 'quantileExact(0.95)',
  p75: 'quantileExact(0.75)',
}

// mapping of allowed timebuckets per difference between days
// (e.g. if difference is lower than (lt) (including) -> then the specified timebuckets are allowed to be applied)
const timeBucketToDays = [
  {
    lt: 0,
    tb: [TimeBucketType.MINUTE, TimeBucketType.HOUR],
  }, // < 1 day
  {
    lt: 7,
    tb: [TimeBucketType.HOUR, TimeBucketType.DAY, TimeBucketType.MONTH],
  }, // 7 days
  {
    lt: 28,
    tb: [TimeBucketType.DAY, TimeBucketType.MONTH],
  }, // 4 weeks
  { lt: 366, tb: [TimeBucketType.DAY, TimeBucketType.MONTH] }, // 12 months
  { lt: 732, tb: [TimeBucketType.MONTH] }, // 24 months
  { lt: 1464, tb: [TimeBucketType.MONTH, TimeBucketType.YEAR] }, // 48 months
  { lt: 99999, tb: [TimeBucketType.YEAR] },
]

const timeBucketConversion = {
  minute: 'toStartOfMinute',
  hour: 'toStartOfHour',
  day: 'toStartOfDay',
  month: 'toStartOfMonth',
  year: 'toStartOfYear',
}

const isValidTimezone = (timezone: string): boolean => {
  if (_isEmpty(timezone)) {
    return false
  }

  try {
    dayjs.tz('2013-11-18 11:55', timezone)
    return true
  } catch {
    return false
  }
}

export const isValidDate = (date: string, format = 'YYYY-MM-DD'): boolean => {
  if (_isEmpty(date)) {
    return false
  }

  if (/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}/.test(date)) {
    return dayjs(date).isValid()
  }

  return dayjs(date, format).format(format) === date
}

const checkIfTBAllowed = (
  timeBucket: TimeBucketType,
  from: string,
  to: string,
): void => {
  const diff = dayjs(to).diff(dayjs(from), 'days')

  const tbMap = _find(timeBucketToDays, ({ lt }) => diff <= lt)

  if (_isEmpty(tbMap)) {
    throw new PreconditionFailedException(
      "The difference between 'from' and 'to' is greater than allowed",
    )
  }

  const { tb } = tbMap

  if (!_includes(tb, timeBucket)) {
    throw new PreconditionFailedException(
      "The specified 'timeBucket' parameter cannot be applied to the date range",
    )
  }
}

export const getLowestPossibleTimeBucket = (
  period?: string,
  from?: string,
  to?: string,
): TimeBucketType => {
  if (!from && !to) {
    if (period === '1h') {
      return TimeBucketType.MINUTE
    }

    if (
      period === 'today' ||
      period === 'yesterday' ||
      period === '1d' ||
      period === '7d'
    ) {
      return TimeBucketType.HOUR
    }

    // The finest buckets must match the frontend's per-period options
    // (tbPeriodPairs), otherwise summary cards and the chart cover
    // different boundaries
    if (period === '4w' || period === '3M' || period === '12M') {
      return TimeBucketType.DAY
    }

    if (period === '24M') {
      return TimeBucketType.MONTH
    }

    return TimeBucketType.YEAR
  }

  const diff = dayjs(to).diff(dayjs(from), 'days')

  const tbMap = _find(timeBucketToDays, ({ lt }) => diff <= lt)

  if (_isEmpty(tbMap)) {
    throw new PreconditionFailedException(
      "The difference between 'from' and 'to' is greater than allowed",
    )
  }

  return _head(tbMap.tb)
}

// getAnalyticsSummary may be called without an explicit timeBucket (the v2
// traffic summary endpoint never sends one). getGroupFromTo rounds the
// window's lower boundary down to the bucket, so the period-only fallback
// must be the lowest bucket the period allows — a fixed DAY fallback would
// turn `period=1h` into "since midnight"
export const getSummaryTimeBucket = (
  timeBucket?: string,
  period?: string,
  from?: string,
  to?: string,
): TimeBucketType => {
  if (timeBucket) {
    return timeBucket as TimeBucketType
  }

  // Preserve the legacy fallback for custom or partial ranges: passing them
  // to getLowestPossibleTimeBucket could throw a misleading range-length
  // error before getGroupFromTo runs its own validation
  if (from || to || !period) {
    return ['today', 'yesterday', 'custom'].includes(period ?? '')
      ? TimeBucketType.HOUR
      : TimeBucketType.DAY
  }

  return getLowestPossibleTimeBucket(period)
}

const EXCLUDE_NULL_FOR = [
  'so',
  'me',
  'ca',
  'te',
  'co',
  'rg',
  'ct',
  'isp',
  'og',
  'ut',
  'ctp',
]

const ERROR_COLUMN_MAP: Record<string, string> = {
  name: 'error_name',
  message: 'error_message',
  filename: 'error_filename',
}

const mapErrorColumn = (type: string): string => ERROR_COLUMN_MAP[type] || type

const captchaMetaValue = (key: string, fallback: string) =>
  `if(indexOf(\`meta.key\`, '${key}') = 0, '${fallback}', arrayElement(\`meta.value\`, indexOf(\`meta.key\`, '${key}')))`

export const CAPTCHA_EVENT_SQL = captchaMetaValue('captcha_event', 'pass')
export const CAPTCHA_DIFFICULTY_SQL = captchaMetaValue(
  'captcha_difficulty',
  '0',
)
export const CAPTCHA_REASON_SQL = captchaMetaValue('captcha_reason', '')
export const CAPTCHA_SOLVE_MS_SQL = `toUInt32OrZero(${captchaMetaValue('solve_ms', '0')})`
const CAPTCHA_SOLVE_TIME_SQL = `multiIf(${CAPTCHA_SOLVE_MS_SQL} = 0, 'unknown', ${CAPTCHA_SOLVE_MS_SQL} < 1000, '<1s', ${CAPTCHA_SOLVE_MS_SQL} < 3000, '1-3s', ${CAPTCHA_SOLVE_MS_SQL} < 10000, '3-10s', ${CAPTCHA_SOLVE_MS_SQL} < 30000, '10-30s', '30s+')`

const CAPTCHA_META_COLUMN_MAP: Record<string, string> = {
  captcha_event: CAPTCHA_EVENT_SQL,
  captcha_difficulty: CAPTCHA_DIFFICULTY_SQL,
  captcha_reason: CAPTCHA_REASON_SQL,
  solve_time: CAPTCHA_SOLVE_TIME_SQL,
}

export const getCaptchaColumnExpression = (col: string): string =>
  CAPTCHA_META_COLUMN_MAP[col] || col

const generateParamsQuery = (
  col: string,
  subQuery: string,
  customEVFilterApplied: boolean,
  type: 'traffic' | 'performance' | 'captcha' | 'errors',
  measure?: PerfMeasure,
): string => {
  const sqlCol = type === 'errors' ? mapErrorColumn(col) : col
  let columns = [`${sqlCol} as name`]

  // For regions and cities we'll return an array of objects, that will also include the country code and region code
  // We need the conutry code to display the flag next to the region/city name
  if (col === 'rg') {
    columns = [...columns, 'cc', 'rgc']
  } else if (col === 'ct') {
    columns = [...columns, 'cc']
  }

  // For browser version and OS version, include the browser or OS name
  if (col === 'brv') {
    columns = [...columns, 'br']
  } else if (col === 'osv') {
    columns = [...columns, 'os']
  }

  const columnsQuery = columns.join(', ')

  if (type === 'performance') {
    const processedMeasure = measure === 'quantiles' ? 'median' : measure

    const fn = MEASURES_MAP[processedMeasure]

    if (sqlCol === 'pg' || sqlCol === 'host') {
      return `SELECT ${columnsQuery}, round(divide(${fn}(pageLoad), 1000), 2) as count ${subQuery} GROUP BY ${columnsQuery}`
    }

    return `SELECT ${columnsQuery}, round(divide(${fn}(pageLoad), 1000), 2) as count ${subQuery} ${EXCLUDE_NULL_FOR.includes(sqlCol) ? `AND ${sqlCol} IS NOT NULL` : ''} GROUP BY ${columnsQuery}`
  }

  if (type === 'errors') {
    return `SELECT ${columnsQuery}, count(*) as count ${subQuery} ${EXCLUDE_NULL_FOR.includes(sqlCol) ? `AND ${sqlCol} IS NOT NULL` : ''} GROUP BY ${columnsQuery}`
  }

  if (type === 'captcha') {
    const captchaColumn = getCaptchaColumnExpression(col)
    let captchaFilter = ''

    if (['cc', 'br', 'os', 'dv'].includes(col)) {
      captchaFilter = `AND ${CAPTCHA_EVENT_SQL} = 'pass'`
    } else if (col === 'captcha_difficulty') {
      captchaFilter = `AND ${CAPTCHA_EVENT_SQL} = 'generate' AND toUInt8OrZero(${CAPTCHA_DIFFICULTY_SQL}) > 0`
    } else if (col === 'captcha_reason') {
      captchaFilter = `AND ${CAPTCHA_EVENT_SQL} = 'generate' AND ${CAPTCHA_REASON_SQL} NOT IN ('', 'baseline', 'manual')`
    } else if (col === 'solve_time') {
      captchaFilter = `AND ${CAPTCHA_EVENT_SQL} = 'pass' AND ${CAPTCHA_SOLVE_MS_SQL} > 0`
    }

    return `SELECT ${captchaColumn} as name, count(*) as count ${subQuery} ${captchaFilter} GROUP BY name`
  }

  if (customEVFilterApplied) {
    return `SELECT ${columnsQuery}, count(*) as count ${subQuery} ${EXCLUDE_NULL_FOR.includes(col) ? `AND ${col} IS NOT NULL` : ''} GROUP BY ${columnsQuery}`
  }

  return `SELECT ${columnsQuery}, count(DISTINCT psid) as count ${subQuery} ${EXCLUDE_NULL_FOR.includes(col) ? `AND ${col} IS NOT NULL` : ''} GROUP BY ${columnsQuery}`
}

export enum DataType {
  ANALYTICS = 'analytics',
  PERFORMANCE = 'performance',
  CAPTCHA = 'captcha',
  ERRORS = 'errors',
}

type AnalyticsEventType = 'pageview' | 'custom_event'
type EventsAllTimeType =
  | 'outbound'
  | AnalyticsEventType
  | 'performance'
  | 'error'
  | 'captcha'
type SessionsListEventType =
  | 'traffic'
  | 'pageview'
  | 'custom_event'
  | 'error'
  | 'performance'

const isValidOrigin = (origins: string[], origin: string) => {
  const escapeRegex = (str: string) =>
    str.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')

  for (let i = 0; i < _size(origins); ++i) {
    const allowedOrigin = origins[i]

    // Check if the allowedOrigin is an exact match
    if (allowedOrigin === origin) {
      return true
    }

    // Check if the allowedOrigin contains a wildcard
    if (_includes(allowedOrigin, '*')) {
      // Convert wildcard host pattern into a safe, anchored regex.
      // Example: "*.example.com" -> /^.*\.example\.com$/i
      const parts = allowedOrigin.split('*').map(escapeRegex)
      const wildcardRegex = new RegExp(`^${parts.join('.*')}$`, 'i')

      // Check if the origin matches the wildcard pattern
      if (wildcardRegex.test(origin)) {
        return true
      }
    }
  }

  return false
}

@Injectable()
export class AnalyticsService {
  constructor(
    private readonly projectService: ProjectService,
    private readonly userService: UserService,
    private readonly saltService: SaltService,
    private readonly botDetectionService: BotDetectionService,
    private readonly logger: AppLoggerService,
    private readonly sessionReplayStorage: SessionReplayS3Service,
  ) {}

  async checkBot(
    pid: string,
    userAgent: string | undefined,
    headers: Record<string, string | string[] | undefined>,
    ip: string,
    referrer: string | null | undefined,
    path: string | null | undefined,
    endpoint: BotEndpoint,
  ): Promise<BotDetectionResult> {
    // Fail open: if bot detection itself errors (e.g. Redis is down inside
    // getRedisProject), treat the request as legitimate so ingestion is not
    // blocked by an unrelated infrastructure issue.
    let result: BotDetectionResult
    try {
      result = await this.botDetectionService.detect({
        pid,
        userAgent,
        headers,
        ip,
        referrer: referrer ?? null,
        path: path ?? null,
        endpoint,
      })
    } catch (reason) {
      this.logger.warn(
        `Bot detection failed for pid ${pid} (${endpoint}): ${reason}`,
        'checkBot',
      )
      return { isBot: false, reason: null }
    }

    if (result.isBot && result.reason) {
      const { country } = getIPDetails(ip)

      clickhouse
        .insert({
          table: 'bot_blocks',
          format: 'JSONEachRow',
          values: [
            {
              pid,
              reason: result.reason,
              cc: country,
              created: dayjs.utc().format('YYYY-MM-DD HH:mm:ss'),
            },
          ],
          clickhouse_settings: { async_insert: 1 },
        })
        .catch((reason) => {
          this.logger.warn(
            `Failed to log bot_block for pid ${pid}: ${reason}`,
            'checkBot',
          )
        })
    }

    return result
  }

  async checkProjectAccess(
    pid: string,
    uid: string | null,
    password?: string,
  ): Promise<void> {
    const project = await this.projectService.getRedisProject(pid)
    this.projectService.allowedToView(project, uid, password)
  }

  async checkManageAccess(pid: string, uid: string | null): Promise<void> {
    const project = await this.projectService.getRedisProject(pid)
    this.projectService.allowedToManage(project, uid)
  }

  async checkBillingAccess(pid: string) {
    const project = await this.projectService.getRedisProject(pid)

    if (project.admin?.dashboardBlockReason) {
      throw new HttpException(
        'The account that owns this site is currently suspended, this is because of a billing issue. Please resolve the issue to continue.',
        HttpStatus.PAYMENT_REQUIRED,
      )
    }
  }

  async isBot(pid: string, userAgent: string) {
    const project = await this.projectService.getRedisProject(pid)

    if (project.botsProtectionLevel === BotsProtectionLevel.OFF) {
      return false
    }

    return isbot(userAgent)
  }

  /**
   * Aggregate bot-block events for a project over the requested window.
   * Used by the Shields settings panel to render the Fathom-style report.
   */
  async getBotStats(
    pid: string,
    period: '7d' | '30d' | '90d',
  ): Promise<{
    total: number
    period: '7d' | '30d' | '90d'
    byReason: { reason: string; count: number }[]
    byCountry: { cc: string; count: number }[]
  }> {
    const days = period === '7d' ? 7 : period === '30d' ? 30 : 90

    const since = dayjs
      .utc()
      .subtract(days, 'day')
      .format('YYYY-MM-DD HH:mm:ss')

    const query = `
      SELECT 'reason' AS kind, reason AS bucket, count() AS cnt
      FROM bot_blocks
      WHERE pid = {pid:FixedString(12)} AND created >= {since:DateTime}
      GROUP BY reason
      UNION ALL
      SELECT 'country' AS kind, cc AS bucket, count() AS cnt
      FROM bot_blocks
      WHERE
        pid = {pid:FixedString(12)}
        AND created >= {since:DateTime}
        AND cc IS NOT NULL
      GROUP BY cc
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, since },
      })
      .then((resultSet) =>
        resultSet.json<{ kind: string; bucket: string; cnt: number }>(),
      )

    const byReason: { reason: string; count: number }[] = []
    const byCountry: { cc: string; count: number }[] = []

    for (const row of data) {
      const count = Number(row.cnt) || 0
      if (row.kind === 'reason') {
        byReason.push({ reason: row.bucket, count })
      } else if (row.kind === 'country' && row.bucket) {
        byCountry.push({ cc: row.bucket, count })
      }
    }

    byReason.sort((a, b) => b.count - a.count)
    byCountry.sort((a, b) => b.count - a.count)

    const total = byReason.reduce((sum, item) => sum + item.count, 0)

    return { total, period, byReason, byCountry: byCountry.slice(0, 10) }
  }

  checkOrigin(project: Project, origin: string): void {
    // For some reasons the project.origins sometimes may look like [''], let's filter it out
    // TODO: Properly validate the origins on project update
    const origins = _filter(project.origins, Boolean) as string[]

    if (!_isEmpty(origins) && !_isEmpty(origin)) {
      if (origin === 'null') {
        if (!_includes(origins, 'null')) {
          throw new BadRequestException(
            "'null' origin is not added to your project's whitelist. To send requests from this origin either add it to your origins policy or leave it empty.",
          )
        }
      } else {
        const urlString = /^https?:\/\//i.test(origin)
          ? origin
          : `https://${origin}`

        let hostname: string

        try {
          hostname = new URL(urlString).hostname
        } catch {
          throw new BadRequestException(
            "This origin is prohibited by the project's origins policy",
          )
        }

        if (!isValidOrigin(origins, hostname)) {
          throw new BadRequestException(
            "This origin is prohibited by the project's origins policy",
          )
        }
      }
    }
  }

  getHostFromOrigin(origin: string): string | null {
    try {
      const { hostname } = new URL(origin)
      return hostname || null
    } catch {
      return null
    }
  }

  checkIpBlacklist(project: Project, ip: string): void {
    // For some reasons the project.ipBlacklist sometimes may look like [''], let's filter it out
    // TODO: Properly validate the ipBlacklist on project update
    const ipBlacklist = _filter(project.ipBlacklist, Boolean) as string[]

    if (!_isEmpty(ipBlacklist) && isIpInRange(ip, ipBlacklist)) {
      throw new BadRequestException(
        'Incoming analytics is disabled for this IP address',
      )
    }
  }

  checkCountryBlacklist(project: Project, country: string | null): void {
    if (!country) {
      return
    }

    const countryBlacklist = _filter(
      project.countryBlacklist,
      Boolean,
    ) as string[]

    if (
      !_isEmpty(countryBlacklist) &&
      _includes(countryBlacklist, _toUpper(country))
    ) {
      throw new BadRequestException(
        'Incoming analytics is disabled for this country',
      )
    }
  }

  checkIfAccountSuspended(project: Project) {
    if (project.admin?.isAccountBillingSuspended) {
      throw new HttpException(
        'The account that owns this site is currently suspended, this is because of a billing issue. This, and all other events, are NOT being tracked and saved on our side. Please log in to your account on Swetrix or contact our support to resolve the issue.',
        HttpStatus.PAYMENT_REQUIRED,
      )
    }
  }

  async validate(
    logDTO:
      | PageviewsDto
      | EventsDto
      | ErrorDto
      | { pid: string; lc?: string | null },
    origin: string,
    ip?: string,
  ): Promise<Project> {
    const { pid } = logDTO

    // 'tz' does not need validation as it's based on getCountryForTimezone detection
    const { lc } = logDTO

    // validate locale ('lc' param)
    if (!_isEmpty(lc)) {
      if (isValidLocale(lc)) {
        // uppercase the locale after '-' char, so for example both 'en-gb' and 'en-GB' in result will be 'en-GB'
        const lcParted = _split(lc, '-')

        if (_size(lcParted) > 1) {
          lcParted[1] = _toUpper(lcParted[1])
        }

        logDTO.lc = _join(lcParted, '-')
      } else {
        logDTO.lc = null
      }
    }

    const project = await this.projectService.getRedisProject(pid)

    this.checkIpBlacklist(project, ip)

    if (!project.active) {
      throw new BadRequestException(
        'Incoming analytics is disabled for this project',
      )
    }

    // this.checkIfAccountSuspended(project)

    this.checkOrigin(project, origin)

    return project
  }

  async validateHeartbeat(
    logDTO: PageviewsDto,
    origin: string,
    ip?: string,
  ): Promise<Project> {
    const { pid } = logDTO

    const project = await this.projectService.getRedisProject(pid)

    this.checkIpBlacklist(project, ip)

    if (!project.active) {
      throw new BadRequestException(
        'Incoming analytics is disabled for this project',
      )
    }

    // this.checkIfAccountSuspended(project)

    this.checkOrigin(project, origin)

    return project
  }

  getDataTypeColumns(dataType: DataType): string[] {
    if (dataType === DataType.ANALYTICS) {
      return TRAFFIC_COLUMNS
    }

    if (dataType === DataType.PERFORMANCE) {
      return PERFORMANCE_COLUMNS
    }

    if (dataType === DataType.ERRORS) {
      return ERROR_COLUMNS
    }

    return CAPTCHA_COLUMNS
  }

  getAnalyticsEventType(customEVFilterApplied: boolean): AnalyticsEventType {
    return customEVFilterApplied ? 'custom_event' : 'pageview'
  }

  buildAnalyticsEventsSubQuery(
    filtersQuery: string,
    customEVFilterApplied: boolean,
    isCaptcha = false,
  ): string {
    const eventType = isCaptcha
      ? 'captcha'
      : this.getAnalyticsEventType(customEVFilterApplied)

    return `FROM events WHERE pid = {pid:FixedString(12)} AND type = '${eventType}' ${filtersQuery} AND created BETWEEN {groupFrom:String} AND {groupTo:String}`
  }

  getGroupFromTo(
    from: string,
    to: string,
    timeBucket: TimeBucketType | null,
    period: string,
    safeTimezone: string,
    diff?: number,
    checkTimebucket = true,
    now?: dayjs.Dayjs,
  ): IGetGroupFromTo {
    let groupFrom: dayjs.Dayjs
    let groupTo: dayjs.Dayjs
    let groupFromUTC: string
    let groupToUTC: string
    const formatFrom = 'YYYY-MM-DD HH:mm:ss'
    const formatTo = 'YYYY-MM-DD HH:mm:ss'
    const djsNow = now
      ? _includes(GMT_0_TIMEZONES, safeTimezone)
        ? dayjs.utc(now.toDate())
        : dayjs(now.toDate()).tz(safeTimezone)
      : _includes(GMT_0_TIMEZONES, safeTimezone)
        ? dayjs.utc()
        : dayjs().tz(safeTimezone)

    if (!_isEmpty(from) && !_isEmpty(to)) {
      if (!isValidDate(from)) {
        throw new PreconditionFailedException(
          "The timeframe 'from' parameter is invalid",
        )
      }

      if (!isValidDate(to)) {
        throw new PreconditionFailedException(
          "The timeframe 'to' parameter is invalid",
        )
      }

      if (dayjs.utc(from).isAfter(dayjs.utc(to), 'second')) {
        throw new PreconditionFailedException(
          "The timeframe 'from' parameter cannot be greater than 'to'",
        )
      }

      if (!_isEmpty(timeBucket) && checkTimebucket) {
        checkIfTBAllowed(timeBucket, from, to)
      }

      if (from === to) {
        groupFrom = dayjs.tz(from, safeTimezone).startOf('d')
        groupTo = dayjs.tz(from, safeTimezone).endOf('d')
        groupFromUTC = groupFrom.utc().format(formatFrom)
        groupToUTC = groupTo.utc().format(formatFrom)
      } else {
        groupFrom = dayjs.utc(from)
        groupTo = dayjs.utc(to)
        // Only date-only inputs are expanded to whole buckets; exact
        // timestamps keep their bounds — rounding them to UTC buckets would
        // widen a single local day picked in a non-UTC timezone into up to
        // 48 hours of data
        groupFromUTC = hasTimeComponent(from)
          ? groupFrom.format(formatFrom)
          : groupFrom.startOf(timeBucket).format(formatFrom)
        groupToUTC = hasTimeComponent(to)
          ? groupTo.format(formatTo)
          : groupTo.endOf(timeBucket).format(formatTo)
      }
    } else if (!_isEmpty(period)) {
      if (period === 'today') {
        groupFrom = djsNow.startOf('d')
        groupTo = djsNow.endOf('d')
      } else if (period === 'yesterday') {
        groupFrom = djsNow.subtract(1, 'day').startOf('d')
        groupTo = djsNow.subtract(1, 'day').endOf('d')
      } else if (period === 'all' && (diff === 0 || diff === 1)) {
        groupFrom = djsNow.subtract(1, 'day').startOf('d')
        groupTo = djsNow
      } else if (period === 'all') {
        groupFrom = djsNow.subtract(diff - 1, 'day').startOf(timeBucket)
        groupTo = djsNow
      } else {
        groupFrom = djsNow.subtract(
          parseInt(period, 10),
          _last(period) as dayjs.ManipulateType,
        )

        groupFrom = groupFrom.startOf(timeBucket)
        groupTo = djsNow
      }

      if (!_isEmpty(timeBucket) && checkTimebucket) {
        checkIfTBAllowed(
          timeBucket,
          groupFrom.format(formatFrom),
          groupTo.format(formatTo),
        )
      }

      // groupFrom is already rounded to the bucket in the project's timezone;
      // rounding again after the UTC conversion would shift the boundary for
      // timezones with a non-whole-hour offset (e.g. Asia/Kathmandu)
      groupFromUTC = groupFrom.utc().format(formatFrom)
      groupToUTC = groupTo.utc().format(formatFrom)
    } else {
      throw new BadRequestException(
        'The timeframe (either from/to pair or period) has to be provided',
      )
    }

    const groupFromFormatted = groupFrom.format(formatFrom)
    const groupToFormatted = groupTo.format(formatTo)

    return {
      // Timezone shifted time
      groupFrom: groupFromFormatted,
      groupTo: groupToFormatted,
      // UTC time
      groupFromUTC,
      groupToUTC,
    }
  }

  async getJourneys(
    params: Record<string, unknown>,
    filtersQuery: string,
    steps: number,
    journeys: number,
  ): Promise<IJourneys> {
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    // Single-page sessions (bounces) produce no transitions to draw, so they
    // are excluded. On top of the overall top-{journeys} ranking, the top 3
    // paths at every depth are kept so that long journeys remain visible even
    // when short paths dominate the ranking.
    //
    // fullLength (the compacted path length before arraySlice) is kept so
    // that sessions which ENDED at the last drawn step can be distinguished
    // from sessions which CONTINUED past it (truncated by the steps limit).
    const sessionPathsCTE = `
      session_paths AS (
        SELECT
          psid,
          arrayCompact(
            arrayMap(
              x -> x.2,
              groupArraySorted({maxPageviews:UInt32})((created, pg))
            )
          ) AS fullPath,
          arraySlice(fullPath, 1, {steps:UInt32}) AS path,
          length(fullPath) AS fullLength
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND psid != 0
          AND pg IS NOT NULL
          ${sessionFiltersQuery}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
        HAVING fullLength >= 2
      )
    `

    const query = `
      WITH ${sessionPathsCTE}
      SELECT
        path,
        value,
        continuedPast,
        totalSessions,
        totalPaths
      FROM (
        SELECT
          path,
          value,
          continuedPast,
          sum(value) OVER () AS totalSessions,
          count() OVER () AS totalPaths,
          row_number() OVER (ORDER BY value DESC, path ASC) AS overallRank,
          row_number() OVER (PARTITION BY length(path) ORDER BY value DESC, path ASC) AS depthRank
        FROM (
          SELECT
            path,
            count() AS value,
            countIf(fullLength > {steps:UInt32}) AS continuedPast
          FROM session_paths
          GROUP BY path
        )
      )
      WHERE overallRank <= {journeys:UInt32} OR depthRank <= 3
      ORDER BY value DESC
    `

    // How many sessions have a (sliced) path of each length. Lets the client
    // compute, per step, how many sessions reached that step at all -- the
    // basis for the "Other paths" and "Exited" nodes and the coverage line.
    const histogramQuery = `
      WITH ${sessionPathsCTE}
      SELECT
        length(path) AS len,
        count() AS sessions,
        countIf(fullLength > {steps:UInt32}) AS truncated
      FROM session_paths
      GROUP BY len
      ORDER BY len
    `

    const queryParams = {
      ...params,
      steps,
      journeys,
      maxPageviews: MAX_JOURNEY_PAGEVIEWS_PER_SESSION,
    }

    const [{ data }, { data: histogramData }] = await Promise.all([
      clickhouse.query({ query, query_params: queryParams }).then((res) =>
        res.json<{
          path: string[]
          value: string
          continuedPast: string
          totalSessions: string
          totalPaths: string
        }>(),
      ),
      clickhouse
        .query({ query: histogramQuery, query_params: queryParams })
        .then((res) =>
          res.json<{ len: string; sessions: string; truncated: string }>(),
        ),
    ])

    if (_isEmpty(data)) {
      return {
        journeys: [],
        totalSessions: 0,
        totalPaths: 0,
        lengthHistogram: [],
      }
    }

    const journeyList: IJourney[] = _map(data, (row) => ({
      path: row.path,
      value: Number(row.value),
      continuedPast: Number(row.continuedPast),
    }))

    return {
      journeys: journeyList,
      totalSessions: Number(data[0].totalSessions),
      totalPaths: Number(data[0].totalPaths),
      lengthHistogram: _map(histogramData, (row) => ({
        len: Number(row.len),
        sessions: Number(row.sessions),
        truncated: Number(row.truncated),
      })),
    }
  }

  // Top sources & countries for every node AND link drawn in the journeys
  // Sankey. Computed over the TRUE population (every session whose path
  // passes through the node / makes the transition), matching what the
  // journey sessions drawer shows -- not just the sessions on top-N drawn
  // paths. Links with target '__exit__' describe sessions whose journey
  // ended at the source node.
  async getJourneyNodeDetails(
    params: Record<string, unknown>,
    filtersQuery: string,
    steps: number,
    journeys: number,
  ): Promise<{
    nodes: IJourneyNodeDetails[]
    links: IJourneyLinkDetails[]
  }> {
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const query = `
      WITH session_paths AS (
        SELECT
          psid,
          arrayCompact(
            arrayMap(
              x -> x.2,
              groupArraySorted({maxPageviews:UInt32})((created, pg))
            )
          ) AS fullPath,
          arraySlice(fullPath, 1, {steps:UInt32}) AS path,
          length(fullPath) AS fullLength
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND psid != 0
          AND pg IS NOT NULL
          ${sessionFiltersQuery}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
        HAVING fullLength >= 2
      ),
      ranked_paths AS (
        SELECT path
        FROM (
          SELECT
            path,
            count() AS value,
            row_number() OVER (ORDER BY value DESC, path ASC) AS overallRank,
            row_number() OVER (PARTITION BY length(path) ORDER BY value DESC, path ASC) AS depthRank
          FROM session_paths
          GROUP BY path
        )
        WHERE overallRank <= {journeys:UInt32} OR depthRank <= 3
      ),
      drawn_nodes AS (
        SELECT DISTINCT idx - 1 AS step, path[idx] AS page
        FROM ranked_paths
        ARRAY JOIN arrayEnumerate(path) AS idx
      ),
      node_sessions AS (
        SELECT sp.psid AS psid, idx - 1 AS step, sp.path[idx] AS page
        FROM session_paths sp
        ARRAY JOIN arrayEnumerate(sp.path) AS idx
        WHERE (idx - 1, sp.path[idx]) IN (SELECT step, page FROM drawn_nodes)
      ),
      drawn_links AS (
        SELECT DISTINCT
          idx - 1 AS step,
          path[idx] AS source,
          if(idx < length(path), path[idx + 1], '${JOURNEY_EXIT_TARGET}') AS target
        FROM ranked_paths
        ARRAY JOIN arrayEnumerate(path) AS idx
      ),
      link_sessions AS (
        SELECT
          sp.psid AS psid,
          idx - 1 AS step,
          sp.path[idx] AS source,
          if(idx < length(sp.path), sp.path[idx + 1], '${JOURNEY_EXIT_TARGET}') AS target
        FROM session_paths sp
        ARRAY JOIN arrayEnumerate(sp.path) AS idx
        WHERE (idx < length(sp.path) OR sp.fullLength = length(sp.path))
          AND (
            idx - 1,
            sp.path[idx],
            if(idx < length(sp.path), sp.path[idx + 1], '${JOURNEY_EXIT_TARGET}')
          ) IN (SELECT step, source, target FROM drawn_links)
      ),
      session_info AS (
        SELECT
          psid,
          argMin(cc, created) AS cc,
          argMin(if(so IS NOT NULL AND so != '', so, if(domain(ref) != '', domain(ref), 'Direct / None')), created) AS source
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
      )
      SELECT step, page, target, type, val, cnt FROM (
        SELECT ns.step AS step, ns.page AS page, '' AS target, 'countries' AS type, si.cc AS val, count() AS cnt
        FROM node_sessions ns
        INNER JOIN session_info si ON ns.psid = si.psid
        WHERE si.cc != ''
        GROUP BY ns.step, ns.page, si.cc

        UNION ALL

        SELECT ns.step AS step, ns.page AS page, '' AS target, 'sources' AS type, si.source AS val, count() AS cnt
        FROM node_sessions ns
        INNER JOIN session_info si ON ns.psid = si.psid
        GROUP BY ns.step, ns.page, si.source

        UNION ALL

        SELECT ns.step AS step, ns.page AS page, '' AS target, 'total' AS type, '' AS val, count() AS cnt
        FROM node_sessions ns
        GROUP BY ns.step, ns.page

        UNION ALL

        SELECT ls.step AS step, ls.source AS page, ls.target AS target, 'countries' AS type, si.cc AS val, count() AS cnt
        FROM link_sessions ls
        INNER JOIN session_info si ON ls.psid = si.psid
        WHERE si.cc != ''
        GROUP BY ls.step, ls.source, ls.target, si.cc

        UNION ALL

        SELECT ls.step AS step, ls.source AS page, ls.target AS target, 'sources' AS type, si.source AS val, count() AS cnt
        FROM link_sessions ls
        INNER JOIN session_info si ON ls.psid = si.psid
        GROUP BY ls.step, ls.source, ls.target, si.source

        UNION ALL

        SELECT ls.step AS step, ls.source AS page, ls.target AS target, 'total' AS type, '' AS val, count() AS cnt
        FROM link_sessions ls
        GROUP BY ls.step, ls.source, ls.target
      )
      ORDER BY step, page, target, type, cnt DESC, val ASC
      LIMIT 5 BY step, page, target, type
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...params,
          steps,
          journeys,
          maxPageviews: MAX_JOURNEY_PAGEVIEWS_PER_SESSION,
        },
      })
      .then((res) =>
        res.json<{
          step: string
          page: string
          target: string
          type: 'countries' | 'sources' | 'total'
          val: string
          cnt: string
        }>(),
      )

    const nodeMap = new Map<string, IJourneyNodeDetails>()
    const linkMap = new Map<string, IJourneyLinkDetails>()

    for (const row of data) {
      const step = Number(row.step)

      let entry: IJourneyNodeDetails | IJourneyLinkDetails

      if (row.target === '') {
        const key = `${step}:${row.page}`
        let node = nodeMap.get(key)

        if (!node) {
          node = { step, page: row.page, total: 0, sources: {}, countries: {} }
          nodeMap.set(key, node)
        }

        entry = node
      } else {
        const key = `${step}:${row.page}→${row.target}`
        let link = linkMap.get(key)

        if (!link) {
          link = {
            step,
            source: row.page,
            target: row.target,
            total: 0,
            sources: {},
            countries: {},
          }
          linkMap.set(key, link)
        }

        entry = link
      }

      if (row.type === 'total') {
        entry.total = Number(row.cnt)
      } else {
        entry[row.type][row.val] = Number(row.cnt)
      }
    }

    return {
      nodes: Array.from(nodeMap.values()),
      links: Array.from(linkMap.values()),
    }
  }

  async getPagesArray(
    rawPages?: string,
    funnelId?: string,
    projectId?: string,
  ): Promise<string[]> {
    if (funnelId && projectId) {
      const funnel = await this.projectService.getFunnel(funnelId, projectId)

      if (!funnel || _isEmpty(funnel)) {
        throw new UnprocessableEntityException(
          'The provided funnelId is incorrect',
        )
      }

      return funnel.steps
    }

    try {
      const pages = JSON.parse(rawPages)

      if (!_isArray(pages)) {
        throw new UnprocessableEntityException(
          'An array of pages has to be provided as a pages param',
        )
      }

      const size = _size(pages)

      if (size < MIN_PAGES_IN_FUNNEL) {
        throw new UnprocessableEntityException(
          `A minimum of ${MIN_PAGES_IN_FUNNEL} pages or events has to be provided`,
        )
      }

      if (size > MAX_PAGES_IN_FUNNEL) {
        throw new UnprocessableEntityException(
          `A maximum of ${MAX_PAGES_IN_FUNNEL} pages or events can be provided`,
        )
      }

      if (_some(pages, (page) => !_isString(page))) {
        throw new UnprocessableEntityException(
          'Pages array must contain string values only',
        )
      }

      return pages
    } catch {
      throw new UnprocessableEntityException(
        'Cannot process the provided array of pages',
      )
    }
  }

  async calculateTimeBucketForAllTime(
    pid: string,
    eventTypes: EventsAllTimeType | readonly EventsAllTimeType[],
  ): Promise<{
    timeBucket: TimeBucketType[]
    diff: number
  }> {
    const types = Array.isArray(eventTypes) ? [...eventTypes] : [eventTypes]

    const { data: fromData } = await clickhouse
      .query({
        query: `SELECT min(created) AS firstCreated FROM events WHERE pid = {pid:FixedString(12)} AND type IN ({types:Array(String)})`,
        query_params: { pid, types },
      })
      .then((res) => res.json<{ firstCreated?: string }>())

    const firstCreated = fromData?.[0]?.firstCreated

    if (!firstCreated) {
      return {
        timeBucket: [TimeBucketType.DAY],
        diff: 0,
      }
    }

    const nowUtc = dayjs.utc()
    const firstCreatedUtc = dayjs.utc(firstCreated)
    const diff = nowUtc.diff(firstCreatedUtc, 'days')

    const tbMap = _find(timeBucketToDays, ({ lt }) => diff <= lt)

    if (_isEmpty(tbMap)) {
      console.error(
        `[ERROR] calculateTimeBucketForAllTime: Difference ${diff} exceeds allowed limits for pid ${pid}`,
      )
      return {
        timeBucket: [TimeBucketType.YEAR],
        diff,
      }
    }

    return {
      timeBucket: tbMap.tb,
      diff,
    }
  }

  postProcessParsedFilters(parsedFilters: any[]): any[] {
    if (!_isArray(parsedFilters)) {
      throw new UnprocessableEntityException(
        'The provided filters are not in a valid format',
      )
    }

    if (parsedFilters.length > MAX_FILTERS) {
      throw new UnprocessableEntityException('Too many filters provided')
    }

    return _reduce(
      parsedFilters,
      (prev, curr) => {
        const { column, filter, isExclusive, isContains = false } = curr

        if (_isArray(filter)) {
          if (filter.length > MAX_FILTER_VALUES) {
            throw new UnprocessableEntityException(
              'Too many filter values provided',
            )
          }

          const filterArray = _map(filter, (f) => ({
            column,
            filter: f,
            isExclusive,
            isContains,
          }))
          return [...prev, ...filterArray]
        }

        return [...prev, curr]
      },
      [],
    )
  }

  parseMetrics(metrics: string): ProjectViewCustomEventDto[] {
    if (metrics === '""' || _isEmpty(metrics)) {
      return []
    }

    try {
      return JSON.parse(metrics)
    } catch {
      console.error(
        `[ERROR] parseMetrics: Cannot parse the metrics array: ${metrics}`,
      )
      return []
    }
  }

  // returns SQL filters query in a format like 'AND col=value AND ...'
  getFiltersQuery(
    filters: string,
    dataType: DataType,
    ignoreEV?: boolean,
  ): GetFiltersQuery {
    const params = {}
    let parsed = []
    let query = ''
    let customEVFilterApplied = false
    const allowTagFilters =
      dataType === DataType.ANALYTICS || dataType === DataType.ERRORS
    const supportsReferrerFilters = dataType === DataType.ANALYTICS
    const supportsSessionScopedPageFilters =
      dataType === DataType.ANALYTICS || dataType === DataType.ERRORS

    if (filters === '""' || _isEmpty(filters)) {
      return [query, params, parsed, customEVFilterApplied]
    }

    try {
      parsed = JSON.parse(filters)
    } catch {
      console.error(`Cannot parse the filters array: ${filters}`)
      return [query, params, parsed, customEVFilterApplied]
    }

    if (_isEmpty(parsed)) {
      return [query, params, parsed, customEVFilterApplied]
    }

    if (!_isArray(parsed)) {
      throw new UnprocessableEntityException(
        'The provided filters are not in a valid format',
      )
    }

    if (parsed.length > MAX_FILTERS) {
      throw new UnprocessableEntityException('Too many filters provided')
    }

    const SUPPORTED_COLUMNS = this.getDataTypeColumns(dataType)

    // Converting something like [{"column":"cc","filter":"BG", "isExclusive":false},{"column":"cc","filter":"PL", "isExclusive":false},{"column":"pg","filter":"/hello", "isExclusive":false}]
    // to
    // {cc: [{filter: 'BG', isExclusive: false}, {filter: 'PL', isExclusive: false}], pg: [{filter: '/hello', isExclusive: false}]}
    const converted = _reduce(
      parsed,
      (prev, curr) => {
        const { column, filter, isExclusive = false, isContains = false } = curr

        if (column === 'ev' && ignoreEV) {
          return prev
        }

        // ev:key -> custom event metadata
        // ev:key: -> custom event metadata, but we want to check if some meta.key's meta.value equals to a specific value
        if (
          column === 'ev' ||
          column === 'ev:key' ||
          _startsWith(column, 'ev:key:')
        ) {
          customEVFilterApplied = true
        } else if (
          !_includes(SUPPORTED_COLUMNS, column) &&
          !(supportsReferrerFilters && column === 'refn') &&
          !(allowTagFilters && _includes(TRAFFIC_METAKEY_COLUMNS, column)) &&
          !(allowTagFilters && _startsWith(column, 'tag:key:')) &&
          !(
            supportsSessionScopedPageFilters &&
            (column === 'entryPage' || column === 'exitPage')
          )
        ) {
          return prev
        }

        const res = []

        // commented encodeURIComponent lines of code to support filtering for pages like /product/[id]
        // until I find a more suitable solution for that later
        if (_isArray(filter)) {
          if (filter.length > MAX_FILTER_VALUES) {
            throw new UnprocessableEntityException(
              'Too many filter values provided',
            )
          }

          for (const f of filter) {
            // let encoded = f

            // if (column === 'pg' && f !== null) {
            //   encoded = _replace(encodeURIComponent(f), /%2F/g, '/')
            // }

            // res.push({ filter: encoded, isExclusive })
            res.push({ filter: f, isExclusive, isContains })
          }
        } else {
          // let encoded = filter

          // if (column === 'pg' && filter !== null) {
          //   encoded = _replace(encodeURIComponent(filter), /%2F/g, '/')
          // }

          // res.push({ filter: encoded, isExclusive })
          res.push({ filter, isExclusive, isContains })
        }

        if (prev[column]) {
          prev[column].push(...res)
        } else {
          prev[column] = res
        }

        return prev
      },
      {},
    )

    const columns = _keys(converted)

    for (let col = 0; col < _size(columns); ++col) {
      const column: string = columns[col]
      query += ' AND ('

      for (let f = 0; f < _size(converted[column]); ++f) {
        if (f > 0) {
          query += ' OR '
        }

        const { filter, isExclusive, isContains } = converted[column][f]
        let sqlColumn = column
        let isArrayDataset = false

        const param = `qf_${col}_${f}`
        params[param] = filter

        // Special: refn (canonical referrer name/root domain)
        if (column === 'refn') {
          const patterns = getDomainsForRefName(String(filter)) || [
            String(filter),
          ]
          const parts: string[] = []
          for (let i = 0; i < patterns.length; i++) {
            const pattern = String(patterns[i])
            if (pattern.includes('://')) {
              // Scheme-specific referrers (e.g., android-app://...) -> prefix match on full ref value
              const pp = `qf_${col}_${f}_p_${i}`
              params[pp] = pattern.toLowerCase()
              parts.push(`startsWith(lower(ref), {${pp}:String})`)
              continue
            }

            // Plain domain pattern -> host equals domain OR endsWith('.' + domain)
            const dp = `qf_${col}_${f}_d_${i}`
            params[dp] = pattern.toLowerCase()
            parts.push(
              `(domain(ref) != '' AND (lower(domain(ref)) = {${dp}:String} OR endsWith(lower(domain(ref)), concat('.', {${dp}:String})))) OR (domain(ref) = '' AND (lower(ref) = {${dp}:String} OR endsWith(lower(ref), concat('.', {${dp}:String}))))`,
            )
          }
          const combined = parts.map((p) => `(${p})`).join(' OR ')
          query += isExclusive ? `NOT (${combined})` : `(${combined})`
          continue
        }

        // Entry/Exit page filters (virtual columns via session scope)
        if (column === 'entryPage' || column === 'exitPage') {
          const pageSelector =
            column === 'entryPage'
              ? 'argMin(pg, created)'
              : 'argMax(pg, created)'
          const subQueryForPages = isContains
            ? `SELECT psid FROM (SELECT psid, ${pageSelector} as page FROM events WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND created BETWEEN {groupFrom:String} AND {groupTo:String} GROUP BY psid) WHERE page ILIKE concat('%', {${param}:String}, '%')`
            : `SELECT psid FROM (SELECT psid, ${pageSelector} as page FROM events WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND created BETWEEN {groupFrom:String} AND {groupTo:String} GROUP BY psid) WHERE page = {${param}:String}`

          // For exclusive filter (isNot) we exclude sessions with matching entry/exit page
          query += `psid ${isExclusive ? 'NOT IN' : 'IN'} (${subQueryForPages})`
          continue
        }

        // when we want to filter meta.value for a specific meta.key
        if (
          _startsWith(column, 'ev:key:') ||
          (allowTagFilters && _startsWith(column, 'tag:key:'))
        ) {
          const key = column.replace(/^ev:key:/, '').replace(/^tag:key:/, '')
          const keyParam = `qfk_${col}_${f}`
          params[keyParam] = key

          if (isContains) {
            query += `indexOf(meta.key, {${keyParam}:String}) > 0 AND ${
              isExclusive ? 'NOT ' : ''
            }(meta.value[indexOf(meta.key, {${keyParam}:String})] ILIKE concat('%', {${param}:String}, '%'))`
          } else {
            query += `indexOf(meta.key, {${keyParam}:String}) > 0 AND meta.value[indexOf(meta.key, {${keyParam}:String})] ${
              isExclusive ? '!= ' : '='
            } {${param}:String}`
          }
          continue
        }

        // meta.key filters for page properties and custom event metadata
        // e.g. article "author" (property)
        if (column === 'ev:key' || (allowTagFilters && column === 'tag:key')) {
          sqlColumn = 'meta.key'
          isArrayDataset = true
          // meta.value filters for page properties and custom event metadata
          // e.g. "Andrii" ("author" value)
        } else if (
          column === 'ev:value' ||
          (allowTagFilters && column === 'tag:value')
        ) {
          sqlColumn = 'meta.value'
          isArrayDataset = true
        } else if (column === 'ev') {
          // Public filter contract `ev` maps to the renamed `event_name` column
          sqlColumn = 'event_name'
        } else if (dataType === DataType.ERRORS) {
          sqlColumn = mapErrorColumn(column)
        } else if (dataType === DataType.CAPTCHA) {
          sqlColumn = getCaptchaColumnExpression(column)
        }

        const isNullFilter =
          filter === null ||
          (typeof filter === 'string' && filter.toLowerCase() === 'null')

        if (isNullFilter) {
          query += isArrayDataset
            ? ''
            : `${sqlColumn} IS ${isExclusive ? 'NOT' : ''} NULL`
          continue
        }

        if (isContains) {
          if (isArrayDataset) {
            // any array element contains substring (case-insensitive)
            query += `${isExclusive ? 'NOT ' : ''}arrayExists(v -> positionCaseInsensitive(v, {${param}:String}) > 0, ${sqlColumn})`
          } else {
            query += `${isExclusive ? 'NOT ' : ''}${sqlColumn} ILIKE concat('%', {${param}:String}, '%')`
          }
        } else {
          query += isArrayDataset
            ? `indexOf(${sqlColumn}, {${param}:String}) ${isExclusive ? '=' : '>'} 0`
            : `${isExclusive ? 'NOT ' : ''}${sqlColumn} = {${param}:String}`
        }
      }

      query += ')'
    }

    return [
      query,
      params,
      this.postProcessParsedFilters(parsed),
      customEVFilterApplied,
    ]
  }

  // Counts the rows a data-deletion request would remove and returns a uniform
  // timeline for the preview sparkline. Mirrors the WHERE clause of deleteData
  // exactly, so the preview is an honest dry run of the destructive query.
  async getDataDeletionPreview(
    pid: string,
    filters: string,
    from: string | null,
    to: string | null,
  ): Promise<DataDeletionPreview> {
    // The preview always counts every deletable type so the UI can show a
    // per-type breakdown; which types actually get deleted is chosen at delete
    // time and applied in deleteData.
    const [filtersQuery, filtersParams] = this.getFiltersQuery(
      filters,
      DataType.ANALYTICS,
    )

    const dateRange = this.getDataDeletionDateRange(from, to)
    from = dateRange.from
    to = dateRange.to

    const params: Record<string, unknown> = {
      pid,
      types: EVENT_TABLE_DELETION_TYPES,
      groupFrom: from || DELETION_MIN_DATE,
      groupTo: to || DELETION_MAX_DATE,
      ...filtersParams,
      ...(from ? { from } : {}),
      ...(to ? { to } : {}),
    }

    const whereClause = `pid = {pid:FixedString(12)} AND type IN ({types:Array(String)}) ${dateRange.condition} ${filtersQuery}`

    const { data: countRows } = await clickhouse
      .query({
        query: `
          SELECT type, count() AS c, min(created) AS mn, max(created) AS mx
          FROM events
          WHERE ${whereClause}
          GROUP BY type
        `,
        query_params: params,
      })
      .then((resultSet) =>
        resultSet.json<{ type: string; c: string; mn: string; mx: string }>(),
      )

    const counts: Record<string, number> = {}
    let total = 0
    let minCreated: string | null = null
    let maxCreated: string | null = null

    for (const row of countRows) {
      const count = Number(row.c) || 0
      if (count === 0) {
        continue
      }
      counts[row.type] = count
      total += count
      if (!minCreated || row.mn < minCreated) {
        minCreated = row.mn
      }
      if (!maxCreated || row.mx > maxCreated) {
        maxCreated = row.mx
      }
    }

    // Session replays aren't rows in the `events` table, so count them
    // separately using the same date range + (filter-resolved) sessions.
    const replayCount = await this.countMatchingSessionReplays(
      pid,
      filtersQuery,
      filtersParams,
      from,
      to,
      dateRange.condition,
    )
    if (replayCount > 0) {
      counts.session_replay = replayCount
      total += replayCount
    }

    if (total === 0) {
      return { counts: {}, total: 0, timeline: { x: [], counts: [] } }
    }

    const timelineFrom = from || minCreated
    const timelineTo = to || maxCreated

    if (!timelineFrom || !timelineTo) {
      return { counts, total, timeline: { x: [], counts: [] } }
    }

    const timeline = await this.buildDeletionTimeline(
      whereClause,
      params,
      timelineFrom,
      timelineTo,
    )

    return { counts, total, timeline }
  }

  // session_replay_chunks carries no traffic columns, so when field filters are
  // present the matching sessions are resolved from the `events` table by psid.
  // The clause is shared by the preview count and the actual deletion so that
  // what the user previews is exactly what gets removed.
  private buildSessionReplayDeletionWhere(
    filtersQuery: string,
    dateCondition: string,
  ): string {
    const psidFilter = _isEmpty(filtersQuery)
      ? ''
      : `AND psid IN (SELECT DISTINCT psid FROM events WHERE pid = {pid:FixedString(12)} ${dateCondition} ${filtersQuery})`

    return `pid = {pid:FixedString(12)} ${dateCondition} ${psidFilter}`
  }

  private buildSessionReplayDeletionParams(
    pid: string,
    filtersParams: Record<string, unknown>,
    from: string | null,
    to: string | null,
  ): Record<string, unknown> {
    return {
      pid,
      groupFrom: from || DELETION_MIN_DATE,
      groupTo: to || DELETION_MAX_DATE,
      ...filtersParams,
      ...(from ? { from } : {}),
      ...(to ? { to } : {}),
    }
  }

  private async countMatchingSessionReplays(
    pid: string,
    filtersQuery: string,
    filtersParams: Record<string, unknown>,
    from: string | null,
    to: string | null,
    dateCondition: string,
  ): Promise<number> {
    if (!this.sessionReplayStorage.isConfigured()) {
      return 0
    }

    const where = this.buildSessionReplayDeletionWhere(
      filtersQuery,
      dateCondition,
    )

    const { data } = await clickhouse
      .query({
        query: `SELECT count(DISTINCT replayId) AS c FROM session_replay_chunks WHERE ${where}`,
        query_params: this.buildSessionReplayDeletionParams(
          pid,
          filtersParams,
          from,
          to,
        ),
      })
      .then((resultSet) => resultSet.json<{ c: string }>())

    return Number(data[0]?.c) || 0
  }

  // Removes every replay matching the deletion query: first the object-storage
  // payloads, then the chunk rows. Mirrors deleteSessionReplay's two-step delete.
  private async deleteMatchingSessionReplays(
    pid: string,
    filtersQuery: string,
    filtersParams: Record<string, unknown>,
    from: string | null,
    to: string | null,
    dateCondition: string,
  ): Promise<void> {
    if (!this.sessionReplayStorage.isConfigured()) {
      return
    }

    const where = this.buildSessionReplayDeletionWhere(
      filtersQuery,
      dateCondition,
    )
    const params = this.buildSessionReplayDeletionParams(
      pid,
      filtersParams,
      from,
      to,
    )

    const { data: chunks } = await clickhouse
      .query({
        query: `SELECT DISTINCT objectKey FROM session_replay_chunks WHERE ${where}`,
        query_params: params,
      })
      .then((resultSet) => resultSet.json<{ objectKey: string }>())

    const objectKeys = chunks.map((chunk) => chunk.objectKey).filter(Boolean)

    await mapLimit(objectKeys, SESSION_REPLAY_CHUNK_FETCH_CONCURRENCY, (key) =>
      this.sessionReplayStorage.deleteObject(key),
    )

    await clickhouse.command({
      query: `ALTER TABLE session_replay_chunks DELETE WHERE ${where}`,
      query_params: params,
      clickhouse_settings: { mutations_sync: '2' },
    })
  }

  private getDataDeletionDateRange(from: string | null, to: string | null) {
    const normalizedFrom = from
      ? dayjs.utc(from).format('YYYY-MM-DD HH:mm:ss')
      : null
    const normalizedTo = to ? dayjs.utc(to).format('YYYY-MM-DD HH:mm:ss') : null
    const conditions: string[] = []

    if (normalizedFrom) {
      conditions.push('created >= {from:String}')
    }
    if (normalizedTo) {
      conditions.push('created <= {to:String}')
    }

    return {
      from: normalizedFrom,
      to: normalizedTo,
      condition: conditions.length ? `AND ${conditions.join(' AND ')}` : '',
    }
  }

  private async buildDeletionTimeline(
    whereClause: string,
    baseParams: Record<string, unknown>,
    windowFrom: string,
    windowTo: string,
  ): Promise<{ x: string[]; counts: number[] }> {
    const fromMs = dayjs.utc(windowFrom).valueOf()
    const toMs = dayjs.utc(windowTo).valueOf()
    const spanSeconds = Math.max(1, Math.round((toMs - fromMs) / 1000))
    const bucketSeconds = Math.max(
      1,
      Math.ceil(spanSeconds / DELETION_TIMELINE_BUCKETS),
    )
    const bucketMs = bucketSeconds * 1000

    // Anchor buckets to the epoch-aligned start of the window, then bucket on
    // absolute unix time. Matching by integer index (rather than a formatted
    // timestamp string) keeps this correct no matter how `created`'s timezone
    // is rendered in the query result.
    const originMs = Math.floor(fromMs / bucketMs) * bucketMs
    const originSeconds = Math.floor(originMs / 1000)

    const x: string[] = []
    for (
      let ms = originMs;
      ms <= toMs && x.length < DELETION_TIMELINE_MAX_POINTS;
      ms += bucketMs
    ) {
      x.push(dayjs.utc(ms).format('YYYY-MM-DD HH:mm:ss'))
    }

    const counts = new Array<number>(x.length).fill(0)

    const { data: rows } = await clickhouse
      .query({
        query: `
          SELECT
            intDiv(toInt64(toUnixTimestamp(created)) - {originSeconds:Int64}, {bucketSeconds:UInt32}) AS bucketIndex,
            count() AS c
          FROM events
          WHERE ${whereClause}
          GROUP BY bucketIndex
          ORDER BY bucketIndex
        `,
        query_params: { ...baseParams, originSeconds, bucketSeconds },
      })
      .then((resultSet) => resultSet.json<{ bucketIndex: string; c: string }>())

    for (const row of rows) {
      const index = Number(row.bucketIndex)
      if (index >= 0 && index < counts.length) {
        counts[index] = Number(row.c) || 0
      }
    }

    return { x, counts }
  }

  // Permanently removes matching analytics rows. The WHERE clause is identical
  // to getDataDeletionPreview's, so what the user previews is what gets deleted.
  async deleteData(
    pid: string,
    filters: string,
    from: string | null,
    to: string | null,
    types: string[],
  ): Promise<void> {
    const safeTypes = _filter(types, (type) =>
      _includes(DELETABLE_EVENT_TYPES, type),
    )

    if (_isEmpty(safeTypes)) {
      throw new BadRequestException('No event types selected for deletion')
    }

    const eventTableTypes = _filter(safeTypes, (type) =>
      _includes(EVENT_TABLE_DELETION_TYPES, type),
    )

    const [filtersQuery, filtersParams] = this.getFiltersQuery(
      filters,
      DataType.ANALYTICS,
    )

    const dateRange = this.getDataDeletionDateRange(from, to)
    from = dateRange.from
    to = dateRange.to

    const params: Record<string, unknown> = {
      pid,
      types: eventTableTypes,
      groupFrom: from || DELETION_MIN_DATE,
      groupTo: to || DELETION_MAX_DATE,
      ...filtersParams,
      ...(from ? { from } : {}),
      ...(to ? { to } : {}),
    }

    const commands: Promise<unknown>[] = []

    if (!_isEmpty(eventTableTypes)) {
      commands.push(
        clickhouse.command({
          query: `ALTER TABLE events DELETE WHERE pid = {pid:FixedString(12)} AND type IN ({types:Array(String)}) ${dateRange.condition} ${filtersQuery}`,
          query_params: params,
          clickhouse_settings: { mutations_sync: '2' },
        }),
      )

      // error_statuses carries no traffic columns, so it can only be cleared
      // when no field filters are applied (a full or plain date-range reset).
      if (_includes(safeTypes, 'error') && _isEmpty(filtersQuery)) {
        commands.push(
          clickhouse.command({
            query: `ALTER TABLE error_statuses DELETE WHERE pid = {pid:FixedString(12)} ${dateRange.condition}`,
            query_params: {
              pid,
              ...(from ? { from } : {}),
              ...(to ? { to } : {}),
            },
            clickhouse_settings: { mutations_sync: '2' },
          }),
        )
      }
    }

    await Promise.all(commands)

    if (!_isEmpty(eventTableTypes)) {
      await this.projectService.refreshUsageAfterDeletion(pid)
    }

    // Session replays live in their own table + object storage, so they are
    // removed on a dedicated path after the events-table mutations settle.
    if (_includes(safeTypes, 'session_replay')) {
      await this.deleteMatchingSessionReplays(
        pid,
        filtersQuery,
        filtersParams,
        from,
        to,
        dateRange.condition,
      )
    }
  }

  derivePsidFromInputs(
    pid: string,
    userAgent: string,
    ip: string,
    salt: string,
  ): string {
    const combined = `${userAgent}${ip}${pid}${salt}`
    return crypto
      .createHash('sha256')
      .update(combined)
      .digest()
      .readBigUInt64BE(0)
      .toString()
  }

  getSessionKey(psid: string): string {
    return `ses:${psid}`
  }

  private generateSid(): string {
    return crypto.randomBytes(8).readBigUInt64BE(0).toString()
  }

  async getSessionId(
    pid: string,
    userAgent: string,
    ip: string,
  ): Promise<{ exists: boolean; psid: string; sid: string | null }> {
    const salt = await this.saltService.getSaltForSession()
    const psid = this.derivePsidFromInputs(pid, userAgent, ip, salt)
    const sessionKey = this.getSessionKey(psid)

    const sid = (await sessionScripts.swetrixPeekSession(
      sessionKey,
      this.generateSid(),
      UNIQUE_SESSION_LIFE_TIME,
      LEGACY_SESSION_FLAG,
    )) as string

    return { exists: Boolean(sid), psid, sid: sid || null }
  }

  async generateAndStoreSessionId(
    pid: string,
    userAgent: string,
    ip: string,
  ): Promise<[boolean, string, string]> {
    const salt = await this.saltService.getSaltForSession()
    const psid = this.derivePsidFromInputs(pid, userAgent, ip, salt)
    const sessionKey = this.getSessionKey(psid)

    const [sid, isNewFlag] = (await sessionScripts.swetrixResolveSession(
      sessionKey,
      this.generateSid(),
      UNIQUE_SESSION_LIFE_TIME,
      LEGACY_SESSION_FLAG,
    )) as [string, string]

    return [isNewFlag === '1', psid, sid]
  }

  // EXPIRE, not SET: the value is the sid and must survive a heartbeat.
  async extendSessionTTL(psid: string): Promise<void> {
    const sessionKey = this.getSessionKey(psid)
    await redis.expire(sessionKey, UNIQUE_SESSION_LIFE_TIME)
  }

  static readonly PROFILE_PREFIX_ANON = 'anon_'
  static readonly PROFILE_PREFIX_USER = 'usr_'

  private hashToNumericString(value: string): string {
    return crypto
      .createHash('sha256')
      .update(value)
      .digest()
      .readBigUInt64BE(0)
      .toString()
  }

  async generateProfileId(
    pid: string,
    userAgent: string,
    ip: string,
    userSupplied?: string,
  ): Promise<string> {
    if (userSupplied) {
      const normalised = this.normaliseUserSuppliedProfileId(userSupplied)

      if (normalised) {
        return `${AnalyticsService.PROFILE_PREFIX_USER}${normalised}`
      }

      // Unusable identifier - fall back to the anonymous fingerprint rather
      // than fusing every visitor sending it into one profile.
    }

    const salt = await this.saltService.getSaltForProfile()
    const combined = `${userAgent}${ip}${pid}${salt}`
    const hash = this.hashToNumericString(combined)

    return `${AnalyticsService.PROFILE_PREFIX_ANON}${hash}`
  }

  isUserSuppliedProfile(profileId: string): boolean {
    return profileId.startsWith(AnalyticsService.PROFILE_PREFIX_USER)
  }

  // Values that are almost certainly instrumentation bugs rather than real user
  // IDs (e.g. stringified nulls). Identifying with them would fuse unrelated
  // visitors into a single profile.
  private static readonly ILLEGAL_USER_PROFILE_IDS = new Set([
    '',
    'null',
    'undefined',
    'nan',
    'none',
    'nil',
    'true',
    'false',
    '0',
    'anonymous',
    'anon',
    'guest',
    'user',
    'id',
    'distinct_id',
    'distinctid',
    'profileid',
    'profile_id',
    'not_authenticated',
    'email',
    '[object object]',
  ])

  // Control (\p{Cc}) and format (\p{Cf}) characters never appear in a genuine
  // identifier, but would corrupt log lines and let a profile masquerade as
  // another one in the dashboard (e.g. via a right-to-left override).
  private static readonly UNPRINTABLE_REGEX = /[\p{Cc}\p{Cf}]/u

  // Traits accumulate across identify() calls, so the number of distinct keys
  // a profile ends up with is bounded on read rather than on write.
  static readonly MAX_PROFILE_TRAITS = 100

  /**
   * Normalises a profile ID supplied by the site (via identify() or the
   * `profileId` field of an event). The value is stored as-is behind the
   * `usr_` prefix - it belongs to the site, and hashing it would only make
   * the dashboard unreadable.
   *
   * @returns the identifier to store, or null when it can't be used as one.
   */
  normaliseUserSuppliedProfileId(userSupplied: string): string | null {
    if (typeof userSupplied !== 'string') {
      return null
    }

    const trimmed = userSupplied.trim()

    if (
      !trimmed ||
      trimmed.length > MAX_USER_PROFILE_ID_LENGTH ||
      AnalyticsService.UNPRINTABLE_REGEX.test(trimmed) ||
      AnalyticsService.ILLEGAL_USER_PROFILE_IDS.has(trimmed.toLowerCase())
    ) {
      return null
    }

    return trimmed
  }

  validateUserSuppliedProfileId(userSupplied: string): string {
    const normalised = this.normaliseUserSuppliedProfileId(userSupplied)

    if (!normalised) {
      const echoed = String(userSupplied ?? '').slice(0, 64)

      throw new BadRequestException(
        `"${echoed}" is not a valid profileId. Pass a unique, stable identifier of the user (e.g. an internal user ID) of up to ${MAX_USER_PROFILE_ID_LENGTH} characters, see https://swetrix.com/docs/visitor-identification`,
      )
    }

    return normalised
  }

  private getProfileAliasKey(pid: string, anonProfileId: string): string {
    return `pfa:${pid}:${anonProfileId}`
  }

  // Sentinel cached for anonymous profiles with no alias, so unlinked (the
  // overwhelmingly common case) lookups don't hit ClickHouse every time.
  // Cannot collide with a real value - those always start with usr_.
  private static readonly PROFILE_ALIAS_NONE = '-'

  /**
   * Looks up the identified (usr_) profile an anonymous (anon_) profile has
   * been linked to. First identification wins, so the mapping is resolved
   * with argMin(created).
   */
  async getUserProfileForAnon(
    pid: string,
    anonProfileId: string,
  ): Promise<string | null> {
    const cacheKey = this.getProfileAliasKey(pid, anonProfileId)

    const cached = await redis.get(cacheKey)

    if (cached) {
      return cached === AnalyticsService.PROFILE_ALIAS_NONE ? null : cached
    }

    const query = `
      SELECT argMin(userProfileId, created) AS userProfileId
      FROM profile_aliases
      WHERE pid = {pid:FixedString(12)}
        AND anonProfileId = {anonProfileId:String}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, anonProfileId },
      })
      .then((resultSet) => resultSet.json<{ userProfileId: string | null }>())

    const userProfileId = data[0]?.userProfileId || null

    if (userProfileId) {
      await redis.set(cacheKey, userProfileId, 'EX', 3600)
    } else {
      // Short TTL: a link created concurrently on another node (linkProfiles
      // uses async_insert, so the row may not be SELECTable yet) must become
      // visible shortly after this sentinel expires.
      await redis.set(cacheKey, AnalyticsService.PROFILE_ALIAS_NONE, 'EX', 60)
    }

    return userProfileId
  }

  /**
   * Links an anonymous (anon_) profile to an identified (usr_) profile.
   * The link is created only once per anonymous profile - if it is already
   * linked to a different user, the existing link is kept (first
   * identification wins; e.g. a second person logging in on a shared device
   * must not steal the anonymous history of the first).
   *
   * @returns whether the anonymous profile is linked to the supplied user
   * profile after the call
   */
  async linkProfiles(
    pid: string,
    anonProfileId: string,
    userProfileId: string,
  ): Promise<boolean> {
    const existing = await this.getUserProfileForAnon(pid, anonProfileId)

    if (existing) {
      return existing === userProfileId
    }

    try {
      await clickhouse.insert({
        table: 'profile_aliases',
        format: 'JSONEachRow',
        values: [
          {
            pid,
            anonProfileId,
            userProfileId,
            created: dayjs.utc().format('YYYY-MM-DD HH:mm:ss'),
          },
        ],
        clickhouse_settings: { async_insert: 1 },
      })

      await redis.set(
        this.getProfileAliasKey(pid, anonProfileId),
        userProfileId,
        'EX',
        3600,
      )
    } catch (error) {
      this.logger.error(`[linkProfiles] Failed to link profiles: ${error}`)
      return false
    }

    return true
  }

  private getProfileTraitsKey(pid: string, profileId: string): string {
    return `pft:${pid}:${profileId}`
  }

  /**
   * Stores the traits (arbitrary key/value metadata a site attaches to a user -
   * email, plan, signup date, ...) of an identified profile.
   *
   * Traits are merged per key: a later call only overwrites the keys it
   * carries, and an empty value removes a trait. Storing one row per key lets
   * ClickHouse handle that without a read-modify-write cycle.
   */
  async saveProfileTraits(
    pid: string,
    profileId: string,
    traits: Record<string, string>,
  ): Promise<void> {
    const keys = _sortBy(_keys(traits))

    if (_isEmpty(keys)) {
      return
    }

    // identify() is called on every page load, so the same traits arrive over
    // and over again - skip the insert when nothing changed since the last one.
    const cacheKey = this.getProfileTraitsKey(pid, profileId)
    const fingerprint = this.hashToNumericString(
      JSON.stringify(_map(keys, (key) => [key, traits[key]])),
    )

    try {
      if ((await redis.get(cacheKey)) === fingerprint) {
        return
      }

      // Millisecond precision: `created` is the ReplacingMergeTree version, so
      // two updates of the same trait within a second must still be ordered.
      const created = dayjs.utc().format('YYYY-MM-DD HH:mm:ss.SSS')

      await clickhouse.insert({
        table: 'profile_traits',
        format: 'JSONEachRow',
        values: _map(keys, (key) => ({
          pid,
          profileId,
          key,
          value: traits[key],
          created,
        })),
        clickhouse_settings: { async_insert: 1 },
      })

      await redis.set(cacheKey, fingerprint, 'EX', 3600)
    } catch (error) {
      this.logger.error(`[saveProfileTraits] Failed to save traits: ${error}`)
    }
  }

  /**
   * Latest value of every trait set for the given profiles. Traits whose value
   * was cleared are dropped.
   */
  async getProfileTraits(
    pid: string,
    profileIds: string[],
  ): Promise<Record<string, string>> {
    if (_isEmpty(profileIds)) {
      return {}
    }

    const query = `
      SELECT key, argMax(value, created) AS value
      FROM profile_traits
      WHERE pid = {pid:FixedString(12)}
        AND profileId IN {profileIds:Array(String)}
      GROUP BY key
      HAVING value != ''
      ORDER BY key ASC
      LIMIT {limit:UInt32}
    `

    try {
      const { data } = await clickhouse
        .query({
          query,
          query_params: {
            pid,
            profileIds,
            limit: AnalyticsService.MAX_PROFILE_TRAITS,
          },
        })
        .then((resultSet) => resultSet.json<{ key: string; value: string }>())

      return _reduce(
        data,
        (acc, { key, value }) => ({ ...acc, [key]: value }),
        {} as Record<string, string>,
      )
    } catch (error) {
      this.logger.error(`[getProfileTraits] Failed to load traits: ${error}`)
      return {}
    }
  }

  /**
   * Resolves the full identity of a profile: the canonical profile ID to
   * display and every profile ID whose events belong to it.
   *
   * For an identified (usr_) profile this is the profile itself plus all
   * anonymous profiles linked to it. For a linked anonymous profile the
   * identity is canonicalised to the identified profile. For an unlinked
   * anonymous profile it is just the profile itself.
   */
  async resolveProfileIdentity(
    pid: string,
    profileId: string,
  ): Promise<{ canonicalId: string; profileIds: string[] }> {
    if (!this.isUserSuppliedProfile(profileId)) {
      const userProfileId = await this.getUserProfileForAnon(pid, profileId)

      if (!userProfileId) {
        return { canonicalId: profileId, profileIds: [profileId] }
      }

      profileId = userProfileId
    }

    const query = `
      SELECT anonProfileId
      FROM (
        SELECT
          anonProfileId,
          argMin(userProfileId, created) AS userProfileId
        FROM profile_aliases
        WHERE pid = {pid:FixedString(12)}
        GROUP BY anonProfileId
      )
      WHERE userProfileId = {userProfileId:String}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, userProfileId: profileId },
      })
      .then((resultSet) => resultSet.json<{ anonProfileId: string }>())

    return {
      canonicalId: profileId,
      profileIds: [profileId, ..._map(data, 'anonProfileId')],
    }
  }

  /**
   * CTE mapping anonymous profile IDs to the identified profiles they are
   * linked to. LEFT JOIN it on profileId and coalesce(nullIf(pam.userProfileId, ''),
   * profileId) to attribute pre-identification events to the identified
   * profile. Requires a {pid:FixedString(12)} query param.
   */
  private buildProfileAliasMapCTE(): string {
    return `
      profile_alias_map AS (
        SELECT
          anonProfileId,
          argMin(userProfileId, created) AS userProfileId
        FROM profile_aliases
        WHERE pid = {pid:FixedString(12)}
        GROUP BY anonProfileId
      )`
  }

  private getSessionFirstSeenKey(pid: string, sid: string): string {
    return `ses:fs:${pid}:${sid}`
  }

  // Recovers the start of an already-running session after its Redis key was
  // lost. sid leads the sessions ORDER BY, so this is a primary key lookup.
  private async getSessionFirstSeenFromClickHouse(
    sid: string,
    pid: string,
  ): Promise<string | null> {
    const query = `
      SELECT minOrNull(firstSeen) AS firstSeen
      FROM sessions
      WHERE sid = {sid:UInt64}
        AND pid = {pid:FixedString(12)}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { sid, pid },
      })
      .then((resultSet) => resultSet.json<{ firstSeen: string | null }>())

    const firstSeen = data[0]?.firstSeen

    return firstSeen ? dayjs.utc(firstSeen).format('YYYY-MM-DD HH:mm:ss') : null
  }

  async recordSessionActivity(
    sid: string,
    psid: string,
    pid: string,
    profileId: string,
    isNewSession = false,
  ): Promise<void> {
    const now = dayjs.utc().format('YYYY-MM-DD HH:mm:ss')
    const cacheKey = this.getSessionFirstSeenKey(pid, sid)

    try {
      const stored = await redis.set(
        cacheKey,
        now,
        'EX',
        UNIQUE_SESSION_LIFE_TIME,
        'NX',
      )

      let firstSeen = now

      if (stored !== 'OK') {
        firstSeen = (await redis.get(cacheKey)) || now
        await redis.expire(cacheKey, UNIQUE_SESSION_LIFE_TIME)
      } else if (!isNewSession) {
        // We created the key, yet the session predates this request — Redis
        // dropped it (restart, eviction). Leaving firstSeen at `now` would be
        // silently destructive: sessions is a ReplacingMergeTree(lastSeen), so
        // this row wins the merge and the real start is lost for good.
        // Unreachable while Redis holds the key, so it costs nothing in the
        // normal path.
        const recovered = await this.getSessionFirstSeenFromClickHouse(sid, pid)

        if (recovered) {
          firstSeen = recovered
          await redis.set(cacheKey, firstSeen, 'EX', UNIQUE_SESSION_LIFE_TIME)
        }
      }

      await clickhouse.insert({
        table: 'sessions',
        format: 'JSONEachRow',
        values: [
          {
            sid,
            psid,
            pid,
            profileId,
            firstSeen,
            lastSeen: now,
          },
        ],
        clickhouse_settings: { async_insert: 1 },
      })
    } catch (error) {
      console.error('Failed to record session:', error)
    }
  }

  async getSessionDurationFromClickHouse(
    psid: string,
    pid: string,
  ): Promise<number | null> {
    try {
      const query = `
        SELECT max(duration) as duration
        FROM (
          SELECT dateDiff('second', min(firstSeen), max(lastSeen)) as duration
          FROM sessions
          WHERE psid = {psid:UInt64}
            AND pid = {pid:FixedString(12)}
          GROUP BY sid
        )
      `

      const { data } = await clickhouse
        .query({
          query,
          query_params: { psid, pid },
        })
        .then((resultSet) => resultSet.json<{ duration: number }>())

      return data[0]?.duration ?? null
    } catch (error) {
      console.error('Failed to get session duration:', error)
      return null
    }
  }

  isValidSessionReplayRetentionDays(
    value: number,
  ): value is 30 | 90 | 365 | 1825 {
    return SESSION_REPLAY_RETENTION_VALUES.includes(value as any)
  }

  private getSessionReplayRetention(project: Project): {
    retentionDays: number
    expiresAt: string
  } {
    const configuredRetentionDays = this.isValidSessionReplayRetentionDays(
      project.sessionReplayRetentionDays,
    )
      ? project.sessionReplayRetentionDays
      : 30
    const retentionDays = Math.min(
      configuredRetentionDays,
      getSessionReplayRetentionEntitlement(project.admin),
    )

    return {
      retentionDays,
      expiresAt: dayjs
        .utc()
        .add(retentionDays, 'day')
        .format('YYYY-MM-DD HH:mm:ss'),
    }
  }

  private async resolveReplaySession(
    pid: string,
    userAgent: string,
    ip: string,
  ): Promise<{ psid: string; sid: string; isNewSession: boolean }> {
    const [isNewSession, psid, sid] = await this.generateAndStoreSessionId(
      pid,
      userAgent,
      ip,
    )
    return { psid, sid, isNewSession }
  }

  async startSessionReplay(
    project: Project,
    pid: string,
    replayId: string,
    privacy: SessionReplayPrivacyMode,
    userAgent: string,
    ip: string,
    profileId?: string,
  ) {
    if (!this.sessionReplayStorage.isConfigured()) {
      throw new InternalServerErrorException(
        'Session replay storage is not configured',
      )
    }

    const { psid, sid, isNewSession } = await this.resolveReplaySession(
      pid,
      userAgent,
      ip,
    )
    const resolvedProfileId = await this.generateProfileId(
      pid,
      userAgent,
      ip,
      profileId,
    )

    await this.recordSessionActivity(
      sid,
      psid,
      pid,
      resolvedProfileId,
      isNewSession,
    )

    const retention = this.getSessionReplayRetention(project)
    const replayStart = await this.reserveActiveReplayStart(pid, psid, replayId)

    return {
      replayId: replayStart.replayId,
      nextChunkIndex: replayStart.nextChunkIndex,
      psid,
      privacy,
      ...retention,
    }
  }

  private getSessionReplayObjectKey(
    pid: string,
    psid: string,
    replayId: string,
    chunkIndex: number,
  ): string {
    const date = dayjs.utc()
    return [
      'session-replays',
      pid,
      date.format('YYYY'),
      date.format('MM'),
      psid,
      replayId,
      `${chunkIndex}.json.gz`,
    ].join('/')
  }

  private getReplayUsageKey(
    accountId: string,
    pid: string,
    psid: string,
    replayId: string,
  ) {
    return `session-replay:usage:${dayjs.utc().format('YYYY-MM')}:${accountId}:${pid}:${psid}:${replayId}`
  }

  private getReplayUsageCounterKey(accountId: string) {
    return `session-replay:usage-count:${dayjs.utc().format('YYYY-MM')}:${accountId}`
  }

  private getActiveReplayKey(pid: string, psid: string) {
    return `session-replay:active:${pid}:${psid}`
  }

  private getReplayUsageTtlSeconds() {
    return Math.max(60, dayjs.utc().endOf('month').diff(dayjs.utc(), 'second'))
  }

  private getReplayLimitKeys(pid: string, psid: string, replayId: string) {
    const prefix = `session-replay:limits:${pid}:${psid}:${replayId}`
    return {
      chunkSetKey: `${prefix}:chunks`,
      countersKey: `${prefix}:counters`,
      firstTsKey: `${prefix}:first`,
      lastTsKey: `${prefix}:last`,
    }
  }

  private getReplayLimitTtlSeconds(retention: { expiresAt: string }) {
    return Math.max(
      60,
      dayjs.utc(retention.expiresAt).diff(dayjs.utc(), 'second'),
    )
  }

  private getActiveReplayTtlSeconds() {
    return Math.max(60, Math.ceil(MAX_SESSION_REPLAY_DURATION_MS / 1000))
  }

  private async reserveActiveReplayStart(
    pid: string,
    psid: string,
    replayId: string,
  ) {
    const result = await redis.eval(
      RESERVE_REPLAY_START_LUA,
      1,
      this.getActiveReplayKey(pid, psid),
      replayId,
      SESSION_REPLAY_CHUNK_INDEX_RANGE,
      this.getActiveReplayTtlSeconds(),
      MAX_SESSION_REPLAY_DURATION_MS,
    )
    const [resolvedReplayId, nextChunkIndex] = Array.isArray(result)
      ? result
      : [replayId, 0]

    return {
      replayId:
        typeof resolvedReplayId === 'string' && resolvedReplayId
          ? resolvedReplayId
          : replayId,
      nextChunkIndex: Number(nextChunkIndex) || 0,
    }
  }

  private getReplayChunkTimestamps(events: Record<string, unknown>[]) {
    const timestamps = events
      .map((event) =>
        typeof event.timestamp === 'number' ? event.timestamp : null,
      )
      .filter((timestamp): timestamp is number => timestamp !== null)

    return {
      firstEventTimestamp: timestamps.length ? Math.min(...timestamps) : null,
      lastEventTimestamp: timestamps.length ? Math.max(...timestamps) : null,
    }
  }

  private async getReplayAccountProjectIds(
    project: Project,
    pid: string,
  ): Promise<string[]> {
    const adminId = project.admin?.id
    if (!adminId) {
      return [pid]
    }

    const projectIds = await this.projectService.getProjectIdsByAdminId(adminId)
    return Array.from(new Set([pid, ...projectIds])).filter(Boolean)
  }

  private async getMonthlyReplayUsage(pids: string[]): Promise<number> {
    if (_isEmpty(pids)) {
      return 0
    }

    const monthStart = dayjs
      .utc()
      .startOf('month')
      .format('YYYY-MM-DD HH:mm:ss')

    const { data } = await clickhouse
      .query({
        query: `
          SELECT uniqExact(concat(toString(psid), ':', replayId)) AS usage
          FROM session_replay_chunks
          WHERE pid IN {pids:Array(FixedString(12))}
            AND created >= {monthStart:DateTime}
        `,
        query_params: { pids, monthStart },
      })
      .then((resultSet) => resultSet.json<{ usage: number }>())

    return Number(data[0]?.usage) || 0
  }

  private async reserveReplayUsage(
    project: Project,
    pid: string,
    psid: string,
    replayId: string,
  ): Promise<{ reserved: boolean; key?: string; counterKey?: string }> {
    const accountId = project.admin?.id || pid
    const key = this.getReplayUsageKey(accountId, pid, psid, replayId)
    const quota = getSessionReplayQuota(project.admin)

    if (quota === 'custom') {
      const result = await redis.set(
        key,
        '1',
        'EX',
        this.getReplayUsageTtlSeconds(),
        'NX',
      )

      return result === 'OK' ? { reserved: true, key } : { reserved: false }
    }

    const counterKey = this.getReplayUsageCounterKey(accountId)
    const pids = await this.getReplayAccountProjectIds(project, pid)
    const usage = await this.getMonthlyReplayUsage(pids)
    const result = await redis.eval(
      RESERVE_REPLAY_USAGE_LUA,
      2,
      key,
      counterKey,
      quota,
      usage,
      this.getReplayUsageTtlSeconds(),
    )

    if (Number(result) === -1) {
      throw new HttpException(
        'Monthly session replay quota exceeded',
        HttpStatus.PAYMENT_REQUIRED,
      )
    }

    if (Number(result) !== 1) {
      return { reserved: false }
    }

    return { reserved: true, key, counterKey }
  }

  private async releaseReplayUsageReservation(reservation: {
    key?: string
    counterKey?: string
  }) {
    if (!reservation.key) {
      return
    }

    if (!reservation.counterKey) {
      await redis.del(reservation.key)
      return
    }

    await redis.eval(
      RELEASE_REPLAY_USAGE_LUA,
      2,
      reservation.key,
      reservation.counterKey,
    )
  }

  private async reserveReplayChunkStorage(
    pid: string,
    psid: string,
    replayId: string,
    chunkIndex: number,
    eventCount: number,
    uncompressedBytes: number,
    retention: { expiresAt: string },
    timestamps: {
      firstEventTimestamp: number | null
      lastEventTimestamp: number | null
    },
  ) {
    const keys = this.getReplayLimitKeys(pid, psid, replayId)
    const result = await redis.eval(
      RESERVE_REPLAY_CHUNK_LUA,
      4,
      keys.chunkSetKey,
      keys.countersKey,
      keys.firstTsKey,
      keys.lastTsKey,
      MAX_SESSION_REPLAY_CHUNKS_PER_REPLAY,
      MAX_SESSION_REPLAY_EVENTS_PER_REPLAY,
      MAX_SESSION_REPLAY_BYTES_PER_REPLAY,
      MAX_SESSION_REPLAY_DURATION_MS,
      chunkIndex,
      eventCount,
      uncompressedBytes,
      timestamps.firstEventTimestamp ?? '',
      timestamps.lastEventTimestamp ?? '',
      this.getReplayLimitTtlSeconds(retention),
    )

    switch (Number(result)) {
      case 1:
        return {
          ...keys,
          chunkIndex,
          eventCount,
          uncompressedBytes,
          duplicate: false,
        }
      case -1:
        throw new PayloadTooLargeException('Session replay has too many chunks')
      case -2:
        throw new PayloadTooLargeException('Session replay has too many events')
      case -3:
        throw new PayloadTooLargeException('Session replay is too large')
      case -4:
        return {
          ...keys,
          chunkIndex,
          eventCount,
          uncompressedBytes,
          duplicate: true,
        }
      case -5:
        throw new PayloadTooLargeException('Session replay is too long')
      default:
        throw new InternalServerErrorException(
          'Unable to reserve session replay chunk',
        )
    }
  }

  private async releaseReplayChunkStorageReservation(reservation: {
    chunkSetKey: string
    countersKey: string
    firstTsKey: string
    lastTsKey: string
    chunkIndex: number
    eventCount: number
    uncompressedBytes: number
  }) {
    await redis.eval(
      RELEASE_REPLAY_CHUNK_LUA,
      4,
      reservation.chunkSetKey,
      reservation.countersKey,
      reservation.firstTsKey,
      reservation.lastTsKey,
      reservation.chunkIndex,
      reservation.eventCount,
      reservation.uncompressedBytes,
    )
  }

  private validateSessionReplayReadSize(replay: {
    chunkCount: number
    eventCount: number
    uncompressedBytes: number
    replayDuration: number
  }) {
    if (
      replay.chunkCount > MAX_SESSION_REPLAY_CHUNKS_PER_REPLAY ||
      replay.eventCount > MAX_SESSION_REPLAY_EVENTS_PER_REPLAY ||
      replay.uncompressedBytes > MAX_SESSION_REPLAY_BYTES_PER_REPLAY ||
      replay.replayDuration * 1000 > MAX_SESSION_REPLAY_DURATION_MS
    ) {
      throw new PayloadTooLargeException('Session replay is too large to load')
    }
  }

  private validateSessionReplayEventSizes(events: Record<string, unknown>[]) {
    const hasOversizedEvent = events.some((event) => {
      try {
        return (
          Buffer.byteLength(JSON.stringify(event)) >
          MAX_SESSION_REPLAY_EVENT_BYTES
        )
      } catch {
        return true
      }
    })

    if (hasOversizedEvent) {
      throw new PayloadTooLargeException('Session replay event is too large')
    }
  }

  async storeSessionReplayChunk(
    project: Project,
    pid: string,
    replayId: string,
    privacy: SessionReplayPrivacyMode,
    chunkIndex: number,
    events: Record<string, unknown>[],
    userAgent: string,
    ip: string,
  ) {
    if (!this.sessionReplayStorage.isConfigured()) {
      throw new InternalServerErrorException(
        'Session replay storage is not configured',
      )
    }

    if (events.length > MAX_SESSION_REPLAY_EVENTS_PER_CHUNK) {
      throw new BadRequestException('Session replay chunk has too many events')
    }

    this.validateSessionReplayEventSizes(events)

    const { psid } = await this.resolveReplaySession(pid, userAgent, ip)
    const retention = this.getSessionReplayRetention(project)
    const payload = JSON.stringify({ events })
    const uncompressedBytes = Buffer.byteLength(payload)
    const timestamps = this.getReplayChunkTimestamps(events)

    if (uncompressedBytes > MAX_SESSION_REPLAY_CHUNK_BYTES) {
      throw new PayloadTooLargeException('Session replay chunk is too large')
    }

    const chunkReservation = await this.reserveReplayChunkStorage(
      pid,
      psid,
      replayId,
      chunkIndex,
      events.length,
      uncompressedBytes,
      retention,
      timestamps,
    )

    if (chunkReservation.duplicate) {
      return {
        replayId,
        psid,
        chunkIndex,
        eventCount: events.length,
        ...retention,
        countedUsage: false,
      }
    }

    const objectKey = this.getSessionReplayObjectKey(
      pid,
      psid,
      replayId,
      chunkIndex,
    )
    const compressed = gzipSync(Buffer.from(payload))
    let usageReservation: Awaited<
      ReturnType<AnalyticsService['reserveReplayUsage']>
    > | null = null

    try {
      usageReservation = await this.reserveReplayUsage(
        project,
        pid,
        psid,
        replayId,
      )

      await this.sessionReplayStorage.putObject(objectKey, compressed)

      await clickhouse.insert({
        table: 'session_replay_chunks',
        format: 'JSONEachRow',
        values: [
          {
            pid,
            psid,
            replayId,
            chunkIndex,
            objectKey,
            privacyMode: privacy,
            eventCount: events.length,
            uncompressedBytes,
            compressedBytes: compressed.byteLength,
            firstEventTimestamp: timestamps.firstEventTimestamp,
            lastEventTimestamp: timestamps.lastEventTimestamp,
            created: dayjs.utc().format('YYYY-MM-DD HH:mm:ss'),
            expiresAt: retention.expiresAt,
          },
        ],
        clickhouse_settings: { async_insert: 1, wait_for_async_insert: 1 },
      })
    } catch (reason) {
      await Promise.allSettled([
        this.releaseReplayChunkStorageReservation(chunkReservation),
        usageReservation
          ? this.releaseReplayUsageReservation(usageReservation)
          : Promise.resolve(),
        this.sessionReplayStorage.deleteObject(objectKey),
      ])
      throw reason
    }

    return {
      replayId,
      psid,
      chunkIndex,
      eventCount: events.length,
      ...retention,
      countedUsage: usageReservation?.reserved || false,
    }
  }

  async getSessionReplaySummary(pid: string, psid: string, replayId?: string) {
    const { data } = await clickhouse
      .query({
        query: `
          SELECT
            replayId,
            argMax(privacyMode, latestCreated) AS privacyMode,
            count() AS chunkCount,
            sum(eventCount) AS eventCount,
            sum(uncompressedBytes) AS uncompressedBytes,
            min(firstEventTimestamp) AS firstEventTimestamp,
            max(lastEventTimestamp) AS lastEventTimestamp,
            max(chunkExpiresAt) AS replayExpiresAt,
            max(latestCreated) AS lastCreated
          FROM (
            SELECT
              replayId,
              chunkIndex,
              argMax(privacyMode, created) AS privacyMode,
              argMax(eventCount, created) AS eventCount,
              argMax(uncompressedBytes, created) AS uncompressedBytes,
              argMax(firstEventTimestamp, created) AS firstEventTimestamp,
              argMax(lastEventTimestamp, created) AS lastEventTimestamp,
              argMax(expiresAt, created) AS chunkExpiresAt,
              max(created) AS latestCreated
            FROM session_replay_chunks
            WHERE pid = {pid:FixedString(12)}
              AND psid = toUInt64OrNull({psid:String})
              ${replayId ? 'AND replayId = {replayId:String}' : ''}
              AND expiresAt > now()
            GROUP BY replayId, chunkIndex
          )
          GROUP BY replayId
          ORDER BY lastCreated DESC
          LIMIT 1
        `,
        query_params: { pid, psid, replayId: replayId || '' },
      })
      .then((resultSet) =>
        resultSet.json<{
          replayId: string
          privacyMode: SessionReplayPrivacyMode
          chunkCount: number
          eventCount: number
          uncompressedBytes: number
          firstEventTimestamp: number | string | null
          lastEventTimestamp: number | string | null
          replayExpiresAt: string
        }>(),
      )

    const replay = data[0]
    if (!replay) {
      return null
    }

    const first = Number(replay.firstEventTimestamp)
    const last = Number(replay.lastEventTimestamp)
    const duration =
      Number.isFinite(first) && Number.isFinite(last) && last > first
        ? Math.round((last - first) / 1000)
        : await this.getSessionDurationFromClickHouse(psid, pid)

    return {
      hasReplay: true,
      replayId: replay.replayId,
      privacyMode: replay.privacyMode,
      chunkCount: Number(replay.chunkCount) || 0,
      eventCount: Number(replay.eventCount) || 0,
      uncompressedBytes: Number(replay.uncompressedBytes) || 0,
      replayDuration: duration || 0,
      replayExpiresAt: replay.replayExpiresAt,
    }
  }

  async getSessionReplay(
    pid: string,
    psid: string,
    replayId?: string,
  ): Promise<{
    replay: Awaited<ReturnType<AnalyticsService['getSessionReplaySummary']>>
    events: Record<string, unknown>[]
  }> {
    const replay = await this.getSessionReplaySummary(pid, psid, replayId)
    const selectedReplayId = replay?.replayId

    if (!selectedReplayId) {
      return { replay: null, events: [] }
    }

    this.validateSessionReplayReadSize(replay)

    const { data: chunks } = await clickhouse
      .query({
        query: `
          SELECT
            chunkIndex,
            argMax(objectKey, created) AS objectKey
          FROM session_replay_chunks
          WHERE pid = {pid:FixedString(12)}
            AND psid = toUInt64OrNull({psid:String})
            AND replayId = {replayId:String}
            AND expiresAt > now()
          GROUP BY chunkIndex
          ORDER BY chunkIndex ASC
        `,
        query_params: { pid, psid, replayId: selectedReplayId },
      })
      .then((resultSet) =>
        resultSet.json<{ chunkIndex: number; objectKey: string }>(),
      )

    const chunkEvents = await mapLimit(
      chunks,
      SESSION_REPLAY_CHUNK_FETCH_CONCURRENCY,
      async (chunk) => {
        const compressed = await this.sessionReplayStorage.getObject(
          chunk.objectKey,
        )
        if (!compressed) return []

        const parsed = JSON.parse(gunzipSync(compressed).toString('utf8')) as {
          events?: Record<string, unknown>[]
        }

        return Array.isArray(parsed.events) ? parsed.events : []
      },
    )

    // Chunk index order is upload order, which can diverge from event time
    // order (e.g. two tabs recording into the same replay). The replayer
    // expects a monotonic timeline, so stable-sort by timestamp — the same
    // normalisation the MP4 export applies.
    const events = chunkEvents
      .flat()
      .sort((a, b) => (Number(a?.timestamp) || 0) - (Number(b?.timestamp) || 0))

    return {
      replay,
      events,
    }
  }

  async deleteSessionReplay(pid: string, psid: string, replayId?: string) {
    if (!this.sessionReplayStorage.isConfigured()) {
      throw new InternalServerErrorException(
        'Session replay storage is not configured',
      )
    }

    const replay = await this.getSessionReplaySummary(pid, psid, replayId)
    const selectedReplayId = replay?.replayId

    if (!selectedReplayId) {
      return { deleted: false, deletedChunks: 0 }
    }

    const { data: chunks } = await clickhouse
      .query({
        query: `
          SELECT DISTINCT objectKey
          FROM session_replay_chunks
          WHERE pid = {pid:FixedString(12)}
            AND psid = toUInt64OrNull({psid:String})
            AND replayId = {replayId:String}
        `,
        query_params: { pid, psid, replayId: selectedReplayId },
      })
      .then((resultSet) => resultSet.json<{ objectKey: string }>())

    const objectKeys = chunks.map((chunk) => chunk.objectKey).filter(Boolean)

    await clickhouse.command({
      query: `
        ALTER TABLE session_replay_chunks
        DELETE WHERE pid = {pid:FixedString(12)}
          AND psid = toUInt64OrNull({psid:String})
          AND replayId = {replayId:String}
      `,
      query_params: { pid, psid, replayId: selectedReplayId },
      clickhouse_settings: { mutations_sync: '2' },
    })

    await mapLimit(objectKeys, SESSION_REPLAY_CHUNK_FETCH_CONCURRENCY, (key) =>
      this.sessionReplayStorage.deleteObject(key),
    )

    return { deleted: true, deletedChunks: objectKeys.length }
  }

  async cleanupExpiredSessionReplays(
    limit = SESSION_REPLAY_CLEANUP_BATCH_SIZE,
  ): Promise<number> {
    if (!this.sessionReplayStorage.isConfigured()) {
      return 0
    }

    const { data } = await clickhouse
      .query({
        query: `
          SELECT objectKey
          FROM session_replay_chunks
          WHERE expiresAt <= now()
          LIMIT {limit:UInt32}
        `,
        query_params: { limit },
      })
      .then((resultSet) => resultSet.json<{ objectKey: string }>())

    if (_isEmpty(data)) {
      return 0
    }

    const objectKeys = data.map((row) => row.objectKey)

    const deleteResults = await Promise.allSettled(
      objectKeys.map((objectKey) =>
        this.sessionReplayStorage.deleteObject(objectKey),
      ),
    )
    const deletedObjectKeys = objectKeys.filter(
      (_, index) => deleteResults[index].status === 'fulfilled',
    )

    if (_isEmpty(deletedObjectKeys)) {
      return 0
    }

    await clickhouse.command({
      query: `
        ALTER TABLE session_replay_chunks
        DELETE WHERE objectKey IN ({objectKeys:Array(String)})
      `,
      query_params: { objectKeys: deletedObjectKeys },
    })

    return deletedObjectKeys.length
  }

  formatFunnel(data: IFunnelCHResponse[], pages: string[]): IFunnel[] {
    const funnel = _map(data, (row, index) => {
      const value = pages[index]

      let events = row.c
      let dropoff = 0
      let eventsPerc = 100
      let eventsPercStep = 100
      let dropoffPercStep = 0

      if (index > 0) {
        const prev = data[index - 1]
        events = row.c
        dropoff = prev.c - row.c
        eventsPerc =
          data[0].c > 0 ? Number(_round((row.c / data[0].c) * 100, 2)) : 0
        eventsPercStep =
          prev.c > 0 ? Number(_round((row.c / prev.c) * 100, 2)) : 0
        dropoffPercStep =
          prev.c > 0 ? Number(_round((dropoff / prev.c) * 100, 2)) : 0
      }

      return {
        value,
        events,
        eventsPerc,
        eventsPercStep,
        dropoff,
        dropoffPercStep,
        topCountries: {} as Record<string, number>,
        topSources: {} as Record<string, number>,
      }
    })

    return funnel
  }

  /*
    ClickHouse's windowFunnel() function returns steps that have non-zero counts.
    I.e. if we have data like [{level: 1, c: 100}, {level: 3, c: 50}],
    it means that level 2 should be 50 as well (because 50 users got up to level 3).

    This function backfills missing levels with previous count.
  */
  backfillFunnel(
    data: IFunnelCHResponse[],
    pages: string[],
  ): IFunnelCHResponse[] {
    // Get max level
    const maxLevel = _size(pages)

    // Build map of level -> count
    const levelCounts = new Map()
    for (const d of data) {
      levelCounts.set(d.level, d.c)
    }

    let prevCount = 0

    // Backfill missing levels with previous count
    const filled = []
    for (let level = maxLevel; level >= 1; level--) {
      const count = levelCounts.get(level)
      if (count !== undefined) {
        prevCount += count
      }

      filled.push({
        level,
        c: prevCount,
      })
    }

    return filled
  }

  generateEmptyFunnel(pages: string[]): IFunnel[] {
    return _map(pages, (value) => ({
      value,
      events: 0,
      eventsPerc: 0,
      eventsPercStep: 0,
      dropoff: 0,
      dropoffPercStep: 0,
      topCountries: {},
      topSources: {},
      breakdowns: {},
    }))
  }

  async getFunnel(
    pages: string[],
    params: any,
    filtersQuery = '',
  ): Promise<IFunnel[]> {
    const pageParams: Record<string, string> = {}
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const pagesStr = _join(
      _map(pages, (value, index) => {
        pageParams[`v${index}`] = value

        return `ifNull(value={v${index}:String}, false)`
      }),
      ',',
    )

    const query = `
      SELECT
        level,
        count() as c
      FROM (
        SELECT
          psid,
          windowFunnel(86400)(created, ${pagesStr}) AS level
        FROM (
          SELECT
            psid,
            if(type = 'pageview', pg, event_name) AS value,
            created
          FROM events
          WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event')
          AND psid != 0
          ${sessionFiltersQuery}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )
        GROUP BY psid
      )
      WHERE level != 0
      GROUP BY level
      ORDER BY level DESC;
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...params, ...pageParams },
      })
      .then((resultSet) => resultSet.json<IFunnelCHResponse>())

    if (_isEmpty(data)) {
      return this.generateEmptyFunnel(pages)
    }

    return this.formatFunnel(_reverse(this.backfillFunnel(data, pages)), pages)
  }

  async getFunnelStepDetails(
    pages: string[],
    params: any,
    filtersQuery = '',
  ): Promise<FunnelStepDetails> {
    const pageParams: Record<string, string> = {}
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const pagesStr = _join(
      _map(pages, (value, index) => {
        pageParams[`v${index}`] = value
        return `ifNull(value={v${index}:String}, false)`
      }),
      ',',
    )

    const query = `
      WITH funnel_sessions AS (
        SELECT
          psid,
          CAST(windowFunnel(86400)(created, ${pagesStr}) AS UInt64) AS level
        FROM (
          SELECT
            psid,
            if(type = 'pageview', pg, event_name) AS value,
            created
          FROM events
          WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event')
          AND psid != 0
          ${sessionFiltersQuery}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )
        GROUP BY psid
        HAVING level > 0
      ),
      expanded AS (
        SELECT psid, arrayJoin(range(1, level + 1)) AS step
        FROM funnel_sessions
      ),
      session_info AS (
        SELECT
          psid,
          argMin(cc, created) AS cc,
          argMin(dv, created) AS dv,
          argMin(br, created) AS br,
          argMin(if(so IS NOT NULL AND so != '', so, if(domain(ref) != '', domain(ref), 'Direct / None')), created) AS source,
          argMin(ca, created) AS campaign,
          argMin(pg, created) AS page,
          argMax(profileId, created) AS profileId
        FROM events
        WHERE pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event')
        AND psid != 0
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
      )
      SELECT step, type, val, cnt FROM (
        SELECT e.step AS step, 'countries' AS type, si.cc AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.cc != ''
        GROUP BY e.step, si.cc

        UNION ALL

        SELECT e.step AS step, 'devices' AS type, si.dv AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.dv != ''
        GROUP BY e.step, si.dv

        UNION ALL

        SELECT e.step AS step, 'browsers' AS type, si.br AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.br != ''
        GROUP BY e.step, si.br

        UNION ALL

        SELECT e.step AS step, 'sources' AS type, si.source AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        GROUP BY e.step, si.source

        UNION ALL

        SELECT e.step AS step, 'campaigns' AS type, si.campaign AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.campaign != ''
        GROUP BY e.step, si.campaign

        UNION ALL

        SELECT e.step AS step, 'pages' AS type, si.page AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.page != ''
        GROUP BY e.step, si.page

        UNION ALL

        SELECT e.step AS step, 'profileTypes' AS type, if(startsWith(si.profileId, '${AnalyticsService.PROFILE_PREFIX_USER}'), 'identified', 'anonymous') AS val, count() AS cnt
        FROM expanded e
        INNER JOIN session_info si ON e.psid = si.psid
        WHERE si.profileId != ''
        GROUP BY e.step, val
      )
      ORDER BY step, type, cnt DESC, val ASC
      LIMIT 5 BY step, type
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...params, ...pageParams },
      })
      .then((resultSet) =>
        resultSet.json<{
          step: number
          type: string
          val: string
          cnt: number
        }>(),
      )

    const createDetailsBucket = () =>
      Object.create(null) as Record<number, Record<string, number>>
    const createValueBucket = () =>
      Object.create(null) as Record<string, number>

    const details: FunnelStepDetails = {
      countries: createDetailsBucket(),
      devices: createDetailsBucket(),
      browsers: createDetailsBucket(),
      sources: createDetailsBucket(),
      campaigns: createDetailsBucket(),
      pages: createDetailsBucket(),
      profileTypes: createDetailsBucket(),
    }

    for (const row of data) {
      const { step } = row
      const type = row.type as keyof FunnelStepDetails

      if (details[type]) {
        if (!details[type][step]) details[type][step] = createValueBucket()
        details[type][step][row.val] = row.cnt
      }
    }

    return details
  }

  async getFunnelSessionsList(
    pages: string[],
    params: any,
    safeTimezone: string,
    step: number,
    take = 30,
    skip = 0,
    filtersQuery = '',
    dropoff = false,
  ): Promise<object | void> {
    const pageParams: Record<string, string> = {}
    const levelCondition = dropoff
      ? 'level = {step:UInt32}'
      : 'level >= {step:UInt32}'
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const pagesStr = _join(
      _map(pages, (value, index) => {
        pageParams[`v${index}`] = value
        return `ifNull(value={v${index}:String}, false)`
      }),
      ',',
    )

    const query = `
      WITH ${this.buildProfileAliasMapCTE()},
      funnel_qualified AS (
        SELECT psid
        FROM (
          SELECT
            psid,
            windowFunnel(86400)(created, ${pagesStr}) AS level
          FROM (
            SELECT
              psid,
              if(type = 'pageview', pg, event_name) AS value,
              created
            FROM events
            WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${sessionFiltersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          )
          GROUP BY psid
        )
        WHERE ${levelCondition}
      ),
      distinct_sessions AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          any(cc) AS cc,
          any(os) AS os,
          any(br) AS br,
          min(toTimeZone(created, {timezone:String})) AS sessionStart,
          max(toTimeZone(created, {timezone:String})) AS lastActivity
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event')
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM funnel_qualified)
        GROUP BY psidCasted, pid
      ),
      pageview_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM funnel_qualified)
        GROUP BY psidCasted, pid
      ),
      event_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'custom_event' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM funnel_qualified)
        GROUP BY psidCasted, pid
      ),
      error_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'error' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM funnel_qualified)
        GROUP BY psidCasted, pid
      ),
      session_duration_agg AS (
        SELECT
          psidCasted,
          pid,
          sum(session_duration) as total_duration,
          argMax(sessionProfileId, lastActivity) as profileId
        FROM (
          SELECT
            CAST(psid, 'String') AS psidCasted,
            pid,
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) as session_duration,
            max(lastSeen) as lastActivity,
            argMax(coalesce(nullIf(pam.userProfileId, ''), s.profileId), lastSeen) as sessionProfileId
          FROM sessions AS s
          LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
          WHERE pid = {pid:FixedString(12)}
            AND psid IN (SELECT psid FROM funnel_qualified)
          GROUP BY psidCasted, pid, sid
        )
        GROUP BY psidCasted, pid
      ),
      first_session_per_profile AS (
        SELECT
          coalesce(nullIf(pam.userProfileId, ''), s.profileId) AS profileId,
          argMin(CAST(psid, 'String'), firstSeen) AS firstPsid
        FROM sessions AS s FINAL
        LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
        WHERE pid = {pid:FixedString(12)}
          AND s.profileId IS NOT NULL
          AND s.profileId != ''
        GROUP BY profileId
      )
      SELECT
        dsf.psidCasted AS psid,
        dsf.cc,
        dsf.os,
        dsf.br,
        COALESCE(pc.count, 0) AS pageviews,
        COALESCE(ec.count, 0) AS customEvents,
        COALESCE(errc.count, 0) AS errors,
        dsf.sessionStart,
        dsf.lastActivity,
        if(dateDiff('second', dsf.lastActivity, now()) < ${LIVE_SESSION_THRESHOLD_SECONDS}, 1, 0) AS isLive,
        sda.total_duration AS sdur,
        sda.profileId AS profileId,
        if(startsWith(sda.profileId, '${AnalyticsService.PROFILE_PREFIX_USER}'), 1, 0) AS isIdentified,
        if(fsp.firstPsid = dsf.psidCasted, 1, 0) AS isFirstSession
      FROM distinct_sessions dsf
      LEFT JOIN pageview_counts pc ON dsf.psidCasted = pc.psidCasted AND dsf.pid = pc.pid
      LEFT JOIN event_counts ec ON dsf.psidCasted = ec.psidCasted AND dsf.pid = ec.pid
      LEFT JOIN error_counts errc ON dsf.psidCasted = errc.psidCasted AND dsf.pid = errc.pid
      LEFT JOIN session_duration_agg sda ON dsf.psidCasted = sda.psidCasted AND dsf.pid = sda.pid
      LEFT JOIN first_session_per_profile fsp ON sda.profileId = fsp.profileId
      WHERE dsf.psidCasted IS NOT NULL
      ORDER BY dsf.sessionStart DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...params,
          ...pageParams,
          timezone: safeTimezone,
          step,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json())

    return data
  }

  async getJourneySessionsList(
    params: any,
    safeTimezone: string,
    step: number,
    page: string,
    take = 30,
    skip = 0,
    filtersQuery = '',
  ): Promise<object | void> {
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const query = `
      WITH ${this.buildProfileAliasMapCTE()},
      journey_qualified AS (
        SELECT psid
        FROM (
          SELECT
            psid,
            arrayCompact(arrayMap(x -> x.2, arraySort(groupArray((created, pg))))) AS path
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type = 'pageview'
            AND psid != 0
            AND pg IS NOT NULL
            ${sessionFiltersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          GROUP BY psid
        )
        WHERE arrayElement(path, {step:UInt32}) = {page:String}
          AND length(path) >= 2
      ),
      distinct_sessions AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          any(cc) AS cc,
          any(os) AS os,
          any(br) AS br,
          min(toTimeZone(created, {timezone:String})) AS sessionStart,
          max(toTimeZone(created, {timezone:String})) AS lastActivity
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event')
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM journey_qualified)
        GROUP BY psidCasted, pid
      ),
      pageview_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM journey_qualified)
        GROUP BY psidCasted, pid
      ),
      event_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'custom_event' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM journey_qualified)
        GROUP BY psidCasted, pid
      ),
      error_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'error' AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND psid IN (SELECT psid FROM journey_qualified)
        GROUP BY psidCasted, pid
      ),
      session_duration_agg AS (
        SELECT
          psidCasted,
          pid,
          sum(session_duration) as total_duration,
          argMax(sessionProfileId, lastActivity) as profileId
        FROM (
          SELECT
            CAST(psid, 'String') AS psidCasted,
            pid,
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) as session_duration,
            max(lastSeen) as lastActivity,
            argMax(coalesce(nullIf(pam.userProfileId, ''), s.profileId), lastSeen) as sessionProfileId
          FROM sessions AS s
          LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
          WHERE pid = {pid:FixedString(12)}
            AND psid IN (SELECT psid FROM journey_qualified)
          GROUP BY psidCasted, pid, sid
        )
        GROUP BY psidCasted, pid
      ),
      first_session_per_profile AS (
        SELECT
          coalesce(nullIf(pam.userProfileId, ''), s.profileId) AS profileId,
          argMin(CAST(psid, 'String'), firstSeen) AS firstPsid
        FROM sessions AS s FINAL
        LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
        WHERE pid = {pid:FixedString(12)}
          AND s.profileId IS NOT NULL
          AND s.profileId != ''
        GROUP BY profileId
      )
      SELECT
        dsf.psidCasted AS psid,
        dsf.cc,
        dsf.os,
        dsf.br,
        COALESCE(pc.count, 0) AS pageviews,
        COALESCE(ec.count, 0) AS customEvents,
        COALESCE(errc.count, 0) AS errors,
        dsf.sessionStart,
        dsf.lastActivity,
        if(dateDiff('second', dsf.lastActivity, now()) < ${LIVE_SESSION_THRESHOLD_SECONDS}, 1, 0) AS isLive,
        sda.total_duration AS sdur,
        sda.profileId AS profileId,
        if(startsWith(sda.profileId, '${AnalyticsService.PROFILE_PREFIX_USER}'), 1, 0) AS isIdentified,
        if(fsp.firstPsid = dsf.psidCasted, 1, 0) AS isFirstSession
      FROM distinct_sessions dsf
      LEFT JOIN pageview_counts pc ON dsf.psidCasted = pc.psidCasted AND dsf.pid = pc.pid
      LEFT JOIN event_counts ec ON dsf.psidCasted = ec.psidCasted AND dsf.pid = ec.pid
      LEFT JOIN error_counts errc ON dsf.psidCasted = errc.psidCasted AND dsf.pid = errc.pid
      LEFT JOIN session_duration_agg sda ON dsf.psidCasted = sda.psidCasted AND dsf.pid = sda.pid
      LEFT JOIN first_session_per_profile fsp ON sda.profileId = fsp.profileId
      WHERE dsf.psidCasted IS NOT NULL
      ORDER BY dsf.sessionStart DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...params,
          timezone: safeTimezone,
          step,
          page,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json())

    return data
  }

  async getFunnelTimeToConvert(
    pages: string[],
    params: any,
    filtersQuery = '',
  ): Promise<{
    fromSessionStart: IConversionTimeMetric
    fromFirstPage: IConversionTimeMetric
    fromFirstFunnelStep: IConversionTimeMetric
  }> {
    const emptyMetric: IConversionTimeMetric = {
      average: null,
      median: null,
      p75: null,
    }
    const pageParams: Record<string, string | number> = {
      steps: pages.length,
    }
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''

    const pagesStr = _join(
      _map(pages, (value, index) => {
        pageParams[`v${index}`] = value
        return `ifNull(value={v${index}:String}, false)`
      }),
      ',',
    )

    const query = `
      WITH funnel_sessions AS (
        SELECT
          psid,
          minIf(created, value = {v0:String}) AS firstStepAt,
          maxIf(created, value = {v${pages.length - 1}:String}) AS conversionAt,
          CAST(windowFunnel(86400)(created, ${pagesStr}) AS UInt64) AS level
        FROM (
          SELECT
            psid,
            if(type = 'pageview', pg, event_name) AS value,
            created
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${sessionFiltersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )
        GROUP BY psid
        HAVING level = {steps:UInt32}
      ),
      session_starts AS (
        SELECT psid, min(firstSeen) AS sessionStart
        FROM sessions FINAL
        WHERE pid = {pid:FixedString(12)}
        GROUP BY psid
      ),
      first_pages AS (
        SELECT psid, min(created) AS firstPageAt
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
      )
      SELECT
        round(avgOrNull(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)), 2) AS averageSession,
        quantileExactOrNull(0.5)(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)) AS medianSession,
        quantileExactOrNull(0.75)(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)) AS p75Session,
        round(avgOrNull(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)), 2) AS averageFirstPage,
        quantileExactOrNull(0.5)(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)) AS medianFirstPage,
        quantileExactOrNull(0.75)(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)) AS p75FirstPage,
        round(avgOrNull(if(firstStepAt > toDateTime(0) AND firstStepAt <= conversionAt, dateDiff('second', firstStepAt, conversionAt), NULL)), 2) AS averageFirstStep,
        quantileExactOrNull(0.5)(if(firstStepAt > toDateTime(0) AND firstStepAt <= conversionAt, dateDiff('second', firstStepAt, conversionAt), NULL)) AS medianFirstStep,
        quantileExactOrNull(0.75)(if(firstStepAt > toDateTime(0) AND firstStepAt <= conversionAt, dateDiff('second', firstStepAt, conversionAt), NULL)) AS p75FirstStep
      FROM funnel_sessions fs
      LEFT JOIN session_starts ss ON fs.psid = ss.psid
      LEFT JOIN first_pages fp ON fs.psid = fp.psid
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...params, ...pageParams },
      })
      .then((resultSet) =>
        resultSet.json<{
          averageSession: number | null
          medianSession: number | null
          p75Session: number | null
          averageFirstPage: number | null
          medianFirstPage: number | null
          p75FirstPage: number | null
          averageFirstStep: number | null
          medianFirstStep: number | null
          p75FirstStep: number | null
        }>(),
      )

    const row = data[0]

    if (!row) {
      return {
        fromSessionStart: emptyMetric,
        fromFirstPage: emptyMetric,
        fromFirstFunnelStep: emptyMetric,
      }
    }

    return {
      fromSessionStart: {
        average: row.averageSession,
        median: row.medianSession,
        p75: row.p75Session,
      },
      fromFirstPage: {
        average: row.averageFirstPage,
        median: row.medianFirstPage,
        p75: row.p75FirstPage,
      },
      fromFirstFunnelStep: {
        average: row.averageFirstStep,
        median: row.medianFirstStep,
        p75: row.p75FirstStep,
      },
    }
  }

  async getTotalPageviews(
    pid: string,
    groupFrom: string,
    groupTo: string,
    filtersQuery = '',
    filtersParams: Record<string, unknown> = {},
  ): Promise<number> {
    const sessionFiltersQuery = filtersQuery
      ? `AND psid IN (
          SELECT DISTINCT psid
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event')
            AND psid != 0
            ${filtersQuery}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )`
      : ''
    const query = `
      SELECT
        count() as c
      FROM events
      WHERE pid = {pid:FixedString(12)}
      AND type = 'pageview'
      ${sessionFiltersQuery}
      AND created BETWEEN {groupFrom:String} AND {groupTo:String}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, groupFrom, groupTo, ...filtersParams },
      })
      .then((resultSet) => resultSet.json<{ c: number }>())

    return data[0]?.c || 0
  }

  convertSummaryToObsoleteFormat(summary: IOverall): any {
    const result = {}

    for (const pid of _keys(summary)) {
      const { current, previous } = summary[pid]

      result[pid] = {
        thisWeek: current.all,
        lastWeek: previous.all,
        thisWeekUnique: current.unique,
        lastWeekUnique: previous.unique,
        percChange: calculateRelativePercentage(previous.all, current.all),
        percChangeUnique: calculateRelativePercentage(
          previous.unique,
          current.unique,
        ),
      }
    }

    return result
  }

  convertSummaryToReportFormat(summary: IOverall): any {
    const result = {}

    for (const pid of _keys(summary)) {
      const { current, previous } = summary[pid]

      result[pid] = {
        // Existing metrics
        pageviews: current.all,
        previousPageviews: previous.all,
        percChangePageviews: calculateRelativePercentage(
          previous.all,
          current.all,
        ),

        uniqueVisitors: current.unique,
        previousUniqueVisitors: previous.unique,
        percChangeUnique: calculateRelativePercentage(
          previous.unique,
          current.unique,
        ),

        // Active Users (MAU/WAU)
        activeUsers: current.users || 0,
        previousActiveUsers: previous.users || 0,
        percChangeUsers: calculateRelativePercentage(
          previous.users || 0,
          current.users || 0,
        ),

        // Average Session Duration
        avgDuration: formatDuration(current.sdur),
        avgDurationSeconds: _round(current.sdur || 0),
        previousAvgDuration: formatDuration(previous.sdur),
        previousAvgDurationSeconds: _round(previous.sdur || 0),
        percChangeDuration: calculateRelativePercentage(
          previous.sdur || 0,
          current.sdur || 0,
        ),

        // Bounce Rate
        bounceRate: _round(current.bounceRate || 0, 1),
        previousBounceRate: _round(previous.bounceRate || 0, 1),
        // For bounce rate, lower is better, so we subtract current from previous
        bounceRateChange: _round(
          (previous.bounceRate || 0) - (current.bounceRate || 0),
          1,
        ),
      }
    }

    return result
  }

  async getTopCountryForReport(
    pid: string,
    groupFrom: string,
    groupTo: string,
  ): Promise<{ cc: string; count: number } | null> {
    const query = `
      SELECT
        cc,
        count() as count
      FROM events
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'pageview'
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        AND cc IS NOT NULL
        AND cc != ''
      GROUP BY cc
      ORDER BY count DESC
      LIMIT 1
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, groupFrom, groupTo },
      })
      .then((resultSet) => resultSet.json<{ cc: string; count: number }>())

    return data[0] || null
  }

  async getTopCountriesForReport(
    pids: string[],
    groupFrom: string,
    groupTo: string,
  ): Promise<Record<string, { cc: string; count: number }>> {
    if (_isEmpty(pids)) {
      return {}
    }

    const query = `
      WITH counts AS (
        SELECT
          pid,
          cc,
          count() as cnt
        FROM events
        WHERE
          pid IN {pids:Array(FixedString(12))}
          AND type = 'pageview'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND cc IS NOT NULL
          AND cc != ''
        GROUP BY pid, cc
      )
      SELECT
        pid,
        argMax(cc, cnt) as cc,
        max(cnt) as count
      FROM counts
      GROUP BY pid
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pids, groupFrom, groupTo },
      })
      .then((resultSet) =>
        resultSet.json<{ pid: string; cc: string; count: any }>(),
      )

    const result: Record<string, { cc: string; count: number }> = {}
    for (const row of data || []) {
      if (!row?.pid) {
        continue
      }
      result[row.pid] = {
        cc: row.cc,
        count: Number(row.count) || 0,
      }
    }

    return result
  }

  async getErrorCountForReport(
    pid: string,
    groupFrom: string,
    groupTo: string,
  ): Promise<{ count: number; uniqueErrors: number }> {
    const query = `
      SELECT
        count() as count,
        count(DISTINCT eid) as uniqueErrors
      FROM events
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'error'
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, groupFrom, groupTo },
      })
      .then((resultSet) =>
        resultSet.json<{ count: number; uniqueErrors: number }>(),
      )

    return data[0] || { count: 0, uniqueErrors: 0 }
  }

  async getErrorCountsForReport(
    pids: string[],
    groupFrom: string,
    groupTo: string,
  ): Promise<Record<string, { count: number; uniqueErrors: number }>> {
    if (_isEmpty(pids)) {
      return {}
    }

    const query = `
      SELECT
        pid,
        count() as count,
        count(DISTINCT eid) as uniqueErrors
      FROM events
      WHERE
        pid IN {pids:Array(FixedString(12))}
        AND type = 'error'
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
      GROUP BY pid
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pids, groupFrom, groupTo },
      })
      .then((resultSet) =>
        resultSet.json<{
          pid: string
          count: any
          uniqueErrors: any
        }>(),
      )

    const result: Record<string, { count: number; uniqueErrors: number }> = {}
    for (const row of data || []) {
      if (!row?.pid) {
        continue
      }
      result[row.pid] = {
        count: Number(row.count) || 0,
        uniqueErrors: Number(row.uniqueErrors) || 0,
      }
    }

    return result
  }

  async getTotalSessionsForReport(
    pids: string[],
    groupFrom: string,
    groupTo: string,
  ): Promise<Record<string, number>> {
    if (_isEmpty(pids)) {
      return {}
    }

    const query = `
      SELECT
        pid,
        uniqExact(coalesce(sid, psid)) as totalSessions
      FROM events
      WHERE
        pid IN {pids:Array(FixedString(12))}
        AND type IN ('pageview', 'custom_event', 'error')
        AND psid IS NOT NULL
        AND psid != 0
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
      GROUP BY pid
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pids, groupFrom, groupTo },
      })
      .then((resultSet) =>
        resultSet.json<{ pid: string; totalSessions: any }>(),
      )

    const result: Record<string, number> = {}
    for (const row of data || []) {
      if (!row?.pid) {
        continue
      }
      result[row.pid] = Number(row.totalSessions) || 0
    }

    return result
  }

  private calculateBounceRate(bounces: number, sessions: number): number {
    if (!sessions) {
      return 0
    }

    return _round((bounces * 100) / sessions, 1)
  }

  async getAnalyticsSummary(
    pids: string[],
    timeBucket?: string,
    period?: string,
    from?: string,
    to?: string,
    timezone?: string,
    filters?: string,
    includeChart?: boolean,
    now?: dayjs.Dayjs,
  ): Promise<IOverall> {
    const safeTimezone = this.getSafeTimezone(timezone)

    const effectiveTimeBucket = getSummaryTimeBucket(
      timeBucket,
      period,
      from,
      to,
    )

    const { groupFrom, groupTo, groupFromUTC, groupToUTC } =
      this.getGroupFromTo(
        from,
        to,
        effectiveTimeBucket,
        period,
        safeTimezone,
        undefined,
        false,
        now,
      )

    const result = {}

    const [filtersQuery, filtersParams, , customEVFilterApplied] =
      this.getFiltersQuery(filters, DataType.ANALYTICS)

    const promises = pids.map(async (pid) => {
      try {
        if (period === 'all') {
          const allTimeType = this.getAnalyticsEventType(customEVFilterApplied)

          const queryAll = `
            WITH analytics_counts AS (
              SELECT
                count(*) AS all,
                count(DISTINCT coalesce(sid, psid)) AS unique,
                count(DISTINCT profileId) AS users
              FROM events
              WHERE
                pid = {pid:FixedString(12)}
                AND type = '${allTimeType}'
                ${filtersQuery}
            ),
            duration_avg AS (
              SELECT avgOrNull(duration) as sdur
              FROM (
                SELECT
                  sid,
                  dateDiff('second', min(firstSeen), max(lastSeen)) as duration
                FROM sessions
                WHERE pid = {pid:FixedString(12)}
                  AND psid IN (
                    SELECT DISTINCT psid
                    FROM events
                    WHERE pid = {pid:FixedString(12)}
                      AND type = '${allTimeType}'
                      AND psid IS NOT NULL
                      ${filtersQuery}
                  )
                GROUP BY sid
              )
            ),
            bounce_counts AS (
              SELECT count() AS bounces
              FROM (
                SELECT coalesce(sid, psid) AS sessionId
                FROM events
                WHERE pid = {pid:FixedString(12)}
                  AND type = 'pageview'
                  AND psid IS NOT NULL
                  AND psid != 0
                  AND psid IN (
                    SELECT DISTINCT psid
                    FROM events
                    WHERE pid = {pid:FixedString(12)}
                      AND type = 'pageview'
                      AND psid IS NOT NULL
                      AND psid != 0
                      ${filtersQuery}
                  )
                GROUP BY sessionId
                HAVING count() = 1
              )
            )
            SELECT
              analytics_counts.*,
              duration_avg.sdur,
              bounce_counts.bounces
            FROM analytics_counts, duration_avg, bounce_counts
          `

          const { data } = await clickhouse
            .query({
              query: queryAll,
              query_params: {
                pid,
                groupFrom: '1970-01-01 00:00:00',
                groupTo: now
                  ? dayjs.utc(now.toDate()).format('YYYY-MM-DD HH:mm:ss')
                  : dayjs.utc().format('YYYY-MM-DD HH:mm:ss'),
                ...filtersParams,
              },
            })
            .then((resultSet) => resultSet.json<BirdseyeCHResponse>())

          let bounceRate = 0

          if (!customEVFilterApplied) {
            bounceRate = this.calculateBounceRate(
              data[0].bounces,
              data[0].unique,
            )
          }

          result[pid] = {
            current: {
              all: data[0].all,
              unique: data[0].unique,
              users: data[0].users,
              bounceRate,
              sdur: data[0].sdur,
            },
            previous: {
              all: 0,
              unique: 0,
              users: 0,
              bounceRate: 0,
              sdur: 0,
            },
            change: data[0].all,
            uniqueChange: data[0].unique,
            usersChange: data[0].users,
            bounceRateChange: bounceRate,
            sdurChange: data[0].sdur,
            customEVFilterApplied,
          }

          if (includeChart) {
            const { diff: allDiff, timeBucket: allowedBuckets } =
              await this.calculateTimeBucketForAllTime(
                pid,
                this.getAnalyticsEventType(customEVFilterApplied),
              )
            const allTimeChartBucket = _includes(
              allowedBuckets,
              TimeBucketType.MONTH,
            )
              ? TimeBucketType.MONTH
              : _last(allowedBuckets) || TimeBucketType.MONTH
            const { groupFrom: allFrom, groupTo: allTo } = this.getGroupFromTo(
              undefined,
              undefined,
              allTimeChartBucket,
              'all',
              safeTimezone,
              allDiff,
            )
            const chartData = await this.getSimplifiedChartData(
              pid,
              allTimeChartBucket,
              allFrom,
              allTo,
              filtersQuery,
              filtersParams,
              safeTimezone,
              customEVFilterApplied,
            )
            result[pid].chart = chartData
          }
          return
        }

        const periodSubtracted = dayjs
          .utc(groupFromUTC)
          .subtract(
            Math.abs(
              dayjs.utc(groupFromUTC).diff(dayjs.utc(groupToUTC), 'minutes'),
            ),
            'minutes',
          )
          .format('YYYY-MM-DD HH:mm:ss')

        const periodType = this.getAnalyticsEventType(customEVFilterApplied)
        const currentFiltersQuery = filtersQuery
          .replace(/{groupFrom:String}/g, '{groupFromUTC:String}')
          .replace(/{groupTo:String}/g, '{groupToUTC:String}')
        const previousFiltersQuery = filtersQuery
          .replace(/{groupFrom:String}/g, '{periodSubtracted:String}')
          .replace(/{groupTo:String}/g, '{groupFromUTC:String}')

        const queryCurrent = `
          WITH analytics_counts AS (
            SELECT
              1 AS sortOrder,
              count(*) AS all,
              count(DISTINCT coalesce(sid, psid)) AS unique,
              count(DISTINCT profileId) AS users
            FROM events
            WHERE
              pid = {pid:FixedString(12)}
              AND type = '${periodType}'
              AND created BETWEEN {groupFromUTC:String} AND {groupToUTC:String}
              ${currentFiltersQuery}
          ),
          duration_avg AS (
            SELECT avgOrNull(duration) as sdur
            FROM (
              SELECT
                sid,
                dateDiff('second', min(firstSeen), max(lastSeen)) as duration
              FROM sessions
              WHERE pid = {pid:FixedString(12)}
                AND psid IN (
                  SELECT DISTINCT psid
                  FROM events
                  WHERE pid = {pid:FixedString(12)}
                    AND type = '${periodType}'
                    AND psid IS NOT NULL
                    AND created BETWEEN {groupFromUTC:String} AND {groupToUTC:String}
                    ${currentFiltersQuery}
                )
              GROUP BY sid
            )
          ),
          bounce_counts AS (
            SELECT count() AS bounces
            FROM (
              SELECT coalesce(sid, psid) AS sessionId
              FROM events
              WHERE pid = {pid:FixedString(12)}
                AND type = 'pageview'
                AND psid IS NOT NULL
                AND psid != 0
                AND created BETWEEN {groupFromUTC:String} AND {groupToUTC:String}
                AND psid IN (
                  SELECT DISTINCT psid
                  FROM events
                  WHERE pid = {pid:FixedString(12)}
                    AND type = 'pageview'
                    AND psid IS NOT NULL
                    AND psid != 0
                    AND created BETWEEN {groupFromUTC:String} AND {groupToUTC:String}
                    ${currentFiltersQuery}
                )
              GROUP BY sessionId
              HAVING count() = 1
            )
          )
          SELECT
            analytics_counts.*,
            duration_avg.sdur,
            bounce_counts.bounces
          FROM analytics_counts, duration_avg, bounce_counts
        `

        const queryPrevious = `
          WITH analytics_counts AS (
            SELECT
              2 AS sortOrder,
              count(*) AS all,
              count(DISTINCT coalesce(sid, psid)) AS unique,
              count(DISTINCT profileId) AS users
            FROM events
            WHERE
              pid = {pid:FixedString(12)}
              AND type = '${periodType}'
              AND created BETWEEN {periodSubtracted:String} AND {groupFromUTC:String}
              ${previousFiltersQuery}
          ),
          duration_avg AS (
            SELECT avgOrNull(duration) as sdur
            FROM (
              SELECT
                sid,
                dateDiff('second', min(firstSeen), max(lastSeen)) as duration
              FROM sessions
              WHERE pid = {pid:FixedString(12)}
                AND psid IN (
                  SELECT DISTINCT psid
                  FROM events
                  WHERE pid = {pid:FixedString(12)}
                    AND type = '${periodType}'
                    AND psid IS NOT NULL
                    AND created BETWEEN {periodSubtracted:String} AND {groupFromUTC:String}
                    ${previousFiltersQuery}
                )
              GROUP BY sid
            )
          ),
          bounce_counts AS (
            SELECT count() AS bounces
            FROM (
              SELECT coalesce(sid, psid) AS sessionId
              FROM events
              WHERE pid = {pid:FixedString(12)}
                AND type = 'pageview'
                AND psid IS NOT NULL
                AND psid != 0
                AND created BETWEEN {periodSubtracted:String} AND {groupFromUTC:String}
                AND psid IN (
                  SELECT DISTINCT psid
                  FROM events
                  WHERE pid = {pid:FixedString(12)}
                    AND type = 'pageview'
                    AND psid IS NOT NULL
                    AND psid != 0
                    AND created BETWEEN {periodSubtracted:String} AND {groupFromUTC:String}
                    ${previousFiltersQuery}
                )
              GROUP BY sessionId
              HAVING count() = 1
            )
          )
          SELECT
            analytics_counts.*,
            duration_avg.sdur,
            bounce_counts.bounces
          FROM analytics_counts, duration_avg, bounce_counts
        `

        const query = `${queryCurrent} UNION ALL ${queryPrevious}`

        let { data } = await clickhouse
          .query({
            query,
            query_params: {
              pid,
              groupFromUTC,
              groupToUTC,
              periodSubtracted,
              ...filtersParams,
            },
          })
          .then((resultSet) => resultSet.json<BirdseyeCHResponse>())

        data = _sortBy(data, 'sortOrder')

        const currentPeriod = data[0]
        const previousPeriod = data[1]

        let bounceRate = 0
        let prevBounceRate = 0

        if (!customEVFilterApplied) {
          bounceRate = this.calculateBounceRate(
            currentPeriod.bounces,
            currentPeriod.unique,
          )

          prevBounceRate = this.calculateBounceRate(
            previousPeriod.bounces,
            previousPeriod.unique,
          )
        }

        result[pid] = {
          current: {
            all: currentPeriod.all,
            unique: currentPeriod.unique,
            users: currentPeriod.users,
            sdur: currentPeriod.sdur || 0,
            bounceRate,
          },
          previous: {
            all: previousPeriod.all,
            unique: previousPeriod.unique,
            users: previousPeriod.users,
            sdur: previousPeriod.sdur || 0,
            bounceRate: prevBounceRate,
          },
          change: currentPeriod.all - previousPeriod.all,
          uniqueChange: currentPeriod.unique - previousPeriod.unique,
          usersChange: currentPeriod.users - previousPeriod.users,
          bounceRateChange: (bounceRate - prevBounceRate) * -1,
          sdurChange: currentPeriod.sdur - previousPeriod.sdur,
          customEVFilterApplied,
        }

        // Add chart data if requested
        if (includeChart) {
          const chartData = await this.getSimplifiedChartData(
            pid,
            effectiveTimeBucket,
            groupFrom,
            groupTo,
            filtersQuery,
            filtersParams,
            safeTimezone,
            customEVFilterApplied,
          )
          result[pid].chart = chartData
        }
      } catch (reason) {
        if (reason instanceof HttpException) {
          throw reason
        }

        console.error(
          `[ERROR] (getAnalyticsSummary) Error occurred for PID ${pid}`,
        )
        console.error(reason)
        throw new InternalServerErrorException(
          "Can't process the provided PID. Please, try again later.",
        )
      }
    })

    await Promise.all(promises)

    return result
  }

  private generateAnalyticsAggregationQueryForScope(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    mode: ChartRenderMode,
    customEVFilterApplied: boolean,
  ): string {
    return customEVFilterApplied
      ? this.generateCustomEventsAggregationQuery(
          timeBucket,
          filtersQuery,
          mode,
        )
      : this.generateAnalyticsAggregationQuery(timeBucket, filtersQuery, mode)
  }

  private extractAnalyticsChartDataForScope(
    result: Array<TrafficCHResponse | TrafficCEFilterCHResponse>,
    xShifted: string[],
    customEVFilterApplied: boolean,
    mode: ChartRenderMode,
  ): IExtractChartData {
    const chartData = this.extractChartData(result, xShifted)

    if (customEVFilterApplied) {
      const visits =
        this.extractCustomEventsChartData(result, xShifted)?._unknown_event ||
        []

      chartData.visits = Array.from(
        { length: _size(xShifted) },
        (_, index) => visits[index] || 0,
      )
    }

    // Propagate the previous cumulative value forward for empty buckets
    if (mode === ChartRenderMode.CUMULATIVE) {
      for (let i = 1; i < chartData.visits.length; ++i) {
        if (chartData.visits[i] === 0) {
          chartData.visits[i] = chartData.visits[i - 1]
        }
        if (chartData.uniques[i] === 0) {
          chartData.uniques[i] = chartData.uniques[i - 1]
        }
        if (chartData.bounces && chartData.bounces[i] === 0) {
          chartData.bounces[i] = chartData.bounces[i - 1]
        }
      }
    }

    return chartData
  }

  /**
   * Get simplified chart data for dashboard cards
   * Returns only x (dates) and visits (pageviews) for a lightweight chart
   */
  async getSimplifiedChartData(
    pid: string,
    timeBucket: TimeBucketType,
    groupFrom: string,
    groupTo: string,
    filtersQuery: string,
    filtersParams: Record<string, string | boolean>,
    safeTimezone: string,
    customEVFilterApplied: boolean,
  ): Promise<{ x: string[]; visits: number[] }> {
    const { xShifted } = this.generateXAxis(
      timeBucket,
      groupFrom,
      groupTo,
      safeTimezone,
    )

    const paramsData = {
      params: {
        pid,
        groupFrom,
        groupTo,
        ...filtersParams,
      },
    }

    const query = this.generateAnalyticsAggregationQueryForScope(
      timeBucket,
      filtersQuery,
      ChartRenderMode.PERIODICAL,
      customEVFilterApplied,
    )

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...paramsData.params, timezone: safeTimezone },
      })
      .then((resultSet) =>
        resultSet.json<TrafficCHResponse | TrafficCEFilterCHResponse>(),
      )

    const { visits } = this.extractAnalyticsChartDataForScope(
      data,
      xShifted,
      customEVFilterApplied,
      ChartRenderMode.PERIODICAL,
    )

    return { x: xShifted, visits }
  }

  async getPerformanceSummary(
    pids: string[],
    period?: string,
    from?: string,
    to?: string,
    timezone?: string,
    filters?: string,
    measure: PerfMeasure = 'median',
  ): Promise<IOverallPerformance> {
    let _from: string

    let _to: string

    if (_isEmpty(period) || ['today', 'yesterday', 'custom'].includes(period)) {
      const safeTimezone = this.getSafeTimezone(timezone)

      const { groupFromUTC, groupToUTC } = this.getGroupFromTo(
        from,
        to,
        ['today', 'yesterday'].includes(period) ? TimeBucketType.HOUR : null,
        period,
        safeTimezone,
      )

      _from = groupFromUTC
      _to = groupToUTC
    }

    const result = {}

    const [filtersQuery, filtersParams] = this.getFiltersQuery(
      filters,
      DataType.PERFORMANCE,
      true,
    )

    const cols = ['dns', 'tls', 'conn', 'response', 'render', 'domLoad', 'ttfb']

    const columnSelectors = cols
      .map((col) => `${MEASURES_MAP[measure]}(${col}) as ${col}`)
      .join(', ')

    const promises = pids.map(async (pid) => {
      try {
        if (period === 'all') {
          const queryAll = `SELECT ${columnSelectors} FROM events WHERE pid = {pid:FixedString(12)} AND type = 'performance' ${filtersQuery}`

          const { data } = await clickhouse
            .query({
              query: queryAll,
              query_params: {
                pid,
                ...filtersParams,
              },
            })
            .then((resultSet) =>
              resultSet.json<Partial<PerformanceCHResponse>>(),
            )

          result[pid] = {
            current: {
              frontend: millisecondsToSeconds(data[0].render + data[0].domLoad),
              network: millisecondsToSeconds(
                data[0].dns + data[0].tls + data[0].conn + data[0].response,
              ),
              backend: millisecondsToSeconds(data[0].ttfb),
            },
            previous: {
              frontend: 0,
              network: 0,
              backend: 0,
            },
            frontendChange: millisecondsToSeconds(
              data[0].render + data[0].domLoad,
            ),
            networkChange: millisecondsToSeconds(
              data[0].dns + data[0].tls + data[0].conn + data[0].response,
            ),
            backendChange: millisecondsToSeconds(data[0].ttfb),
          }
          return
        }

        let now: string
        let periodFormatted: string
        let periodSubtracted: string

        if (_from && _to) {
          // diff may be 0 (when selecting data for 1 day), so let's make it 1 to grab some data for the prev day as well
          const diff = dayjs.utc(_to).diff(dayjs.utc(_from), 'days') || 1

          now = _to
          periodFormatted = _from
          periodSubtracted = dayjs
            .utc(_from)
            .subtract(diff, 'days')
            .format('YYYY-MM-DD HH:mm:ss')
        } else {
          const amountToSubtract = parseInt(period, 10)
          const unit = _replace(period, /[0-9]/g, '') as dayjs.ManipulateType

          now = dayjs.utc().format('YYYY-MM-DD HH:mm:ss')
          const periodRaw = dayjs.utc().subtract(amountToSubtract, unit)
          periodFormatted = periodRaw.format('YYYY-MM-DD HH:mm:ss')
          periodSubtracted = periodRaw
            .subtract(amountToSubtract, unit)
            .format('YYYY-MM-DD HH:mm:ss')
        }

        const queryCurrent = `SELECT 1 AS sortOrder, ${columnSelectors} FROM events WHERE pid = {pid:FixedString(12)} AND type = 'performance' AND created BETWEEN {periodFormatted:String} AND {now:String} ${filtersQuery}`
        const queryPrevious = `SELECT 2 AS sortOrder, ${columnSelectors} FROM events WHERE pid = {pid:FixedString(12)} AND type = 'performance' AND created BETWEEN {periodSubtracted:String} AND {periodFormatted:String} ${filtersQuery}`

        const query = `${queryCurrent} UNION ALL ${queryPrevious}`

        let { data } = await clickhouse
          .query({
            query,
            query_params: {
              pid,
              periodFormatted,
              periodSubtracted,
              now,
              ...filtersParams,
            },
          })
          .then((resultSet) => resultSet.json<Partial<PerformanceCHResponse>>())

        data = _sortBy(data, 'sortOrder')

        const currentPeriod = data[0]
        const previousPeriod = data[1]

        result[pid] = {
          current: {
            frontend: millisecondsToSeconds(
              currentPeriod.render + currentPeriod.domLoad,
            ),
            network: millisecondsToSeconds(
              currentPeriod.dns +
                currentPeriod.tls +
                currentPeriod.conn +
                currentPeriod.response,
            ),
            backend: millisecondsToSeconds(currentPeriod.ttfb),
          },
          previous: {
            frontend: millisecondsToSeconds(
              previousPeriod.render + previousPeriod.domLoad,
            ),
            network: millisecondsToSeconds(
              previousPeriod.dns +
                previousPeriod.tls +
                previousPeriod.conn +
                previousPeriod.response,
            ),
            backend: millisecondsToSeconds(previousPeriod.ttfb),
          },
          frontendChange: millisecondsToSeconds(
            currentPeriod.render +
              currentPeriod.domLoad -
              previousPeriod.render -
              previousPeriod.domLoad,
          ),
          networkChange: millisecondsToSeconds(
            currentPeriod.dns +
              currentPeriod.tls +
              currentPeriod.conn +
              currentPeriod.response -
              previousPeriod.dns -
              previousPeriod.tls -
              previousPeriod.conn -
              previousPeriod.response,
          ),
          backendChange: millisecondsToSeconds(
            currentPeriod.ttfb - previousPeriod.ttfb,
          ),
        }
      } catch (reason) {
        console.error(
          `[ERROR] (getPerformanceSummary) Error occurred for PID ${pid}`,
        )
        console.error(reason)
        throw new InternalServerErrorException(
          "Can't process the provided PID. Please, try again later.",
        )
      }
    })

    await Promise.all(promises)

    return result
  }

  async getFilters(pid: string, type: string): Promise<Array<string>> {
    if (!_includes(ALL_COLUMNS, type)) {
      throw new UnprocessableEntityException(
        `The provided type (${type}) is incorrect`,
      )
    }

    // Special handling for virtual columns
    if (type === 'entryPage' || type === 'exitPage') {
      const selector =
        type === 'entryPage' ? 'argMin(pg, created)' : 'argMax(pg, created)'
      const query = `
        WITH session_pages AS (
          SELECT psid, ${selector} as page
          FROM events
          WHERE pid = {pid:FixedString(12)} AND type = 'pageview'
          GROUP BY psid
        )
        SELECT page FROM session_pages WHERE page IS NOT NULL GROUP BY page
      `

      const { data } = await clickhouse
        .query({
          query,
          query_params: { pid },
        })
        .then((resultSet) => resultSet.json<any>())

      return _map(data, 'page')
    }

    // Public filter contract `ev` reads from the renamed event_name column.
    let query =
      type === 'ev'
        ? `SELECT event_name AS ev FROM events WHERE pid={pid:FixedString(12)} AND type = 'custom_event' AND event_name IS NOT NULL GROUP BY event_name`
        : `SELECT ${type} FROM events WHERE pid={pid:FixedString(12)} AND type IN ('pageview', 'custom_event') AND ${type} IS NOT NULL GROUP BY ${type}`

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          pid,
        },
      })
      .then((resultSet) => resultSet.json())

    return _map(data, type)
  }

  async getErrorsFilters(pid: string, type: string): Promise<Array<string>> {
    if (!_includes(ERROR_COLUMNS, type)) {
      throw new UnprocessableEntityException(
        `The provided type (${type}) is incorrect`,
      )
    }

    const sqlCol = mapErrorColumn(type)

    const query = `SELECT ${sqlCol} AS ${type} FROM events WHERE pid={pid:FixedString(12)} AND type = 'error' AND ${sqlCol} IS NOT NULL GROUP BY ${sqlCol}`

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          pid,
        },
      })
      .then((resultSet) => resultSet.json())

    return _map(data, type)
  }

  async getVersionFilters(
    pid: string,
    type: 'traffic' | 'errors',
    column: 'br' | 'os',
  ): Promise<Array<{ name: string; version: string }>> {
    const safeType =
      type === 'errors'
        ? "type = 'error'"
        : "type IN ('pageview', 'custom_event')"
    const safeVersionCol = column === 'br' ? 'brv' : 'osv'

    const query = `
      SELECT ${column}, ${safeVersionCol}
      FROM events
      WHERE pid={pid:FixedString(12)} AND ${safeType} AND ${column} IS NOT NULL AND ${safeVersionCol} IS NOT NULL
      GROUP BY ${column}, ${safeVersionCol}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid },
      })
      .then((resultSet) => resultSet.json<Record<string, string>[]>())

    return data.map((row) => ({
      name: row[column],
      version: row[safeVersionCol],
    }))
  }

  async generateParams(
    parsedFilters: Array<{ [key: string]: string }>,
    subQuery: string,
    customEVFilterApplied: boolean,
    paramsData: any,
    type: 'traffic' | 'performance' | 'captcha' | 'errors',
    measure?: PerfMeasure,
  ) {
    let columns = TRAFFIC_COLUMNS

    if (type === 'captcha') {
      columns = CAPTCHA_COLUMNS
    }

    if (type === 'errors') {
      columns = ERROR_COLUMNS
    }

    if (type === 'performance') {
      columns = PERFORMANCE_COLUMNS
    }

    // Build a single query combining all metrics
    const withClauses = columns.map((col) => {
      const baseQuery = generateParamsQuery(
        col,
        subQuery,
        customEVFilterApplied,
        type,
        measure,
      )
      return `metrics_${col} AS (${baseQuery})`
    })

    const EXTRA_FIELDS = {
      rg: ['cc', 'rgc'],
      ct: ['cc'],
      brv: ['br'],
      osv: ['os'],
    } as const

    const query = `
      WITH ${withClauses.join(',\n')}
      SELECT
        column_name,
        name,
        count,
        extra_fields
      FROM (
        ${columns
          .map((col) => {
            let extraField = `CAST([] AS Array(Nullable(String))) as extra_fields`

            if (col === 'rg') {
              extraField = `CAST([CAST(cc AS Nullable(String)), CAST(rgc AS Nullable(String))] AS Array(Nullable(String))) as extra_fields`
            } else if (col === 'ct') {
              extraField = `CAST([CAST(cc AS Nullable(String))] AS Array(Nullable(String))) as extra_fields`
            } else if (col === 'brv') {
              extraField = `CAST([CAST(br AS Nullable(String))] AS Array(Nullable(String))) as extra_fields`
            } else if (col === 'osv') {
              extraField = `CAST([CAST(os AS Nullable(String))] AS Array(Nullable(String))) as extra_fields`
            }

            return `
              SELECT
                '${col}' as column_name,
                name,
                count,
                ${extraField}
              FROM metrics_${col}
            `
          })
          .join('\nUNION ALL\n')}
      )
      ORDER BY column_name, count DESC
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: paramsData.params,
      })
      .then((resultSet) => resultSet.json<any>())

    const params = Object.fromEntries(columns.map((col) => [col, []]))

    const { length } = data

    for (let i = 0; i < length; ++i) {
      const row = data[i]

      const extras: Record<string, string | null> = {}
      const extraKeys =
        EXTRA_FIELDS[row.column_name as keyof typeof EXTRA_FIELDS]
      const extraValues = row.extra_fields as (string | null)[] | undefined

      if (extraKeys && extraValues && extraValues.length > 0) {
        for (let j = 0; j < extraKeys.length; j++) {
          const key = extraKeys[j]
          const value = extraValues[j]
          if (key && typeof value !== 'undefined') {
            extras[key] = value
          }
        }
      }

      params[row.column_name].push({
        name: row.name,
        count: row.count,
        ...extras,
      })
    }

    return params
  }

  generateXAxis(
    timeBucket: TimeBucketType,
    from: string, // timezone is already applied to the from and to parameters
    to: string,
    safeTimezone: string,
  ): IGenerateXAxis {
    const iterateTo = _includes(GMT_0_TIMEZONES, safeTimezone)
      ? dayjs.utc(to)
      : dayjs.tz(to, safeTimezone)
    let djsFrom = _includes(GMT_0_TIMEZONES, safeTimezone)
      ? dayjs.utc(from)
      : dayjs.tz(from, safeTimezone)

    let format

    switch (timeBucket) {
      case TimeBucketType.MINUTE:
        djsFrom = djsFrom.startOf('minute')
        format = 'YYYY-MM-DD HH:mm:ss'
        break

      case TimeBucketType.HOUR:
        djsFrom = djsFrom.startOf('hour')
        format = 'YYYY-MM-DD HH:mm:ss'
        break

      case TimeBucketType.DAY:
        djsFrom = djsFrom.startOf('day')
        format = 'YYYY-MM-DD'
        break

      case TimeBucketType.MONTH:
        djsFrom = djsFrom.startOf('month')
        format = 'YYYY-MM'
        break

      case TimeBucketType.YEAR:
        djsFrom = djsFrom.startOf('year')
        format = 'YYYY'
        break

      default:
        throw new BadRequestException(
          `The provided time bucket (${timeBucket}) is incorrect`,
        )
    }

    // UTC dates
    const x = []

    // Timezone shifted dates (a hack to map Clickhouse timezone processed data to UTC dates)
    const xShifted = []

    while (djsFrom.isSameOrBefore(iterateTo, timeBucket)) {
      x.push(djsFrom.utc().format(format))
      xShifted.push(djsFrom.format(format))
      djsFrom = djsFrom.add(1, timeBucket)
    }

    if (_includes(GMT_0_TIMEZONES, safeTimezone)) {
      return {
        x,
        xShifted: x,
      }
    }

    return {
      x,
      xShifted,
    }
  }

  generateDateString(row: { [key: string]: number }): string {
    const { year, month, day, hour, minute } = row

    let dateString = `${year}`

    if (typeof month === 'number') {
      if (month < 10) {
        dateString += `-0${month}`
      } else {
        dateString += `-${month}`
      }
    }

    if (typeof day === 'number') {
      if (day < 10) {
        dateString += `-0${day}`
      } else {
        dateString += `-${day}`
      }
    }

    if (typeof hour === 'number') {
      const strMinute =
        typeof minute === 'number'
          ? minute < 10
            ? `0${minute}`
            : minute
          : '00'

      if (hour < 10) {
        dateString += ` 0${hour}:${strMinute}:00`
      } else {
        dateString += ` ${hour}:${strMinute}:00`
      }
    }

    return dateString
  }

  extractChartData(result, x: string[]): IExtractChartData {
    const visits = Array(x.length).fill(0)
    const uniques = Array(x.length).fill(0)
    const sdur = Array(x.length).fill(0)
    const bounces = Array(x.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])
      const index = x.indexOf(dateString)

      if (index !== -1) {
        visits[index] = result[row].pageviews
        uniques[index] = result[row].uniques
        sdur[index] = _round(result[row].sdur)
        bounces[index] = result[row].bounces || 0
      }
    }

    return {
      visits,
      uniques,
      sdur,
      bounces,
    }
  }

  extractCountsForSessionChart(
    result: any[],
    xShifted: string[],
    countFieldName: string,
  ): number[] {
    const counts = Array(xShifted.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])
      const index = xShifted.indexOf(dateString)

      if (index !== -1) {
        counts[index] = result[row][countFieldName] || 0
      }
    }
    return counts
  }

  extractCustomEventsChartData(queryResult, x: string[]): any {
    const result = {}

    for (let row = 0; row < _size(queryResult); ++row) {
      const { ev = '_unknown_event' } = queryResult[row]

      const dateString = this.generateDateString(queryResult[row])

      const index = x.indexOf(dateString)

      if (index !== -1) {
        if (!result[ev]) {
          result[ev] = Array(x.length).fill(0)
        }

        result[ev][index] = queryResult[row].count
      }
    }

    return result
  }

  getSafeTimezone(timezone: string): string {
    if (timezone === DEFAULT_TIMEZONE || !isValidTimezone(timezone)) {
      return DEFAULT_TIMEZONE
    }

    return timezone
  }

  getGroupSubquery(timeBucket: TimeBucketType): [string, string] {
    if (timeBucket === TimeBucketType.MINUTE) {
      return [
        `toYear(tz_created) as year,
        toMonth(tz_created) as month,
        toDayOfMonth(tz_created) as day,
        toHour(tz_created) as hour,
        toMinute(tz_created) as minute`,
        'year, month, day, hour, minute',
      ]
    }

    if (timeBucket === TimeBucketType.HOUR) {
      return [
        `toYear(tz_created) as year,
        toMonth(tz_created) as month,
        toDayOfMonth(tz_created) as day,
        toHour(tz_created) as hour`,
        'year, month, day, hour',
      ]
    }

    if (timeBucket === TimeBucketType.MONTH) {
      return [
        `toYear(tz_created) as year,
        toMonth(tz_created) as month`,
        'year, month',
      ]
    }

    if (timeBucket === TimeBucketType.YEAR) {
      return [`toYear(tz_created) as year`, 'year']
    }

    return [
      `toYear(tz_created) as year,
      toMonth(tz_created) as month,
      toDayOfMonth(tz_created) as day`,
      'year, month, day',
    ]
  }

  generateAnalyticsAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    mode: ChartRenderMode,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const baseQuery = `
      SELECT
        ${selector},
        avgOrNull(sessions_data.duration) as sdur,
        count() as pageviews,
        count(DISTINCT subquery.sessionId) as uniques,
        countIf(session_counts.pageviews = 1) as bounces
      FROM (
        SELECT
          pid,
          psid,
          coalesce(sid, psid) as sessionId,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        PREWHERE pid = {pid:FixedString(12)} AND type = 'pageview'
        WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
        ${filtersQuery}
      ) as subquery
      LEFT JOIN (
        SELECT
          pid,
          sid,
          dateDiff('second', min(firstSeen), max(lastSeen)) as duration
        FROM sessions
        WHERE pid = {pid:FixedString(12)}
        GROUP BY pid, sid
      ) as sessions_data
      ON subquery.pid = sessions_data.pid
      AND subquery.sessionId = sessions_data.sid
      LEFT JOIN (
        SELECT
          pid,
          sessionId,
          count() as pageviews
        FROM (
          SELECT pid, coalesce(sid, psid) as sessionId, psid
          FROM events
          PREWHERE pid = {pid:FixedString(12)} AND type = 'pageview'
          WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
            AND psid IS NOT NULL
            AND psid != 0
            AND psid IN (
              SELECT DISTINCT psid
              FROM events
              PREWHERE pid = {pid:FixedString(12)} AND type = 'pageview'
              WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
                AND psid IS NOT NULL
                AND psid != 0
                ${filtersQuery}
            )
        )
        GROUP BY pid, sessionId
      ) as session_counts
      ON subquery.pid = session_counts.pid
      AND subquery.sessionId = session_counts.sessionId
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    if (mode === ChartRenderMode.CUMULATIVE) {
      return `
        SELECT
          *,
          sum(pageviews) OVER (ORDER BY ${groupBy} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as pageviews,
          sum(uniques) OVER (ORDER BY ${groupBy} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as uniques,
          sum(bounces) OVER (ORDER BY ${groupBy} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as bounces
        FROM (${baseQuery})
      `
    }

    return baseQuery
  }

  generateSessionAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const baseQuery = `
      SELECT
        ${selector},
        count() as pageviews
      FROM (
        SELECT
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        PREWHERE pid = {pid:FixedString(12)} AND type = 'pageview'
        WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
        ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    return baseQuery
  }

  generateSessionCustomEventsAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    return `
      SELECT
        ${selector},
        count() as customEventsCount
      FROM (
        SELECT
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        PREWHERE pid = {pid:FixedString(12)} AND type = 'custom_event'
        WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
        ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `
  }

  generateSessionErrorsAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    return `
      SELECT
        ${selector},
        count() as errorsCount
      FROM (
        SELECT
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        PREWHERE pid = {pid:FixedString(12)} AND type = 'error'
        WHERE created BETWEEN ${tzFromDate} AND ${tzToDate}
        ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `
  }

  generateCustomEventsAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    mode: ChartRenderMode,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const baseQuery = `
      SELECT
        ${selector},
        avgOrNull(sessions_data.duration) as sdur,
        count(DISTINCT subquery.sessionId) as uniques,
        count() as count
      FROM (
        SELECT
          pid,
          psid,
          coalesce(sid, psid) as sessionId,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'custom_event'
          AND created BETWEEN ${tzFromDate} AND ${tzToDate}
          ${filtersQuery}
      ) as subquery
      LEFT JOIN (
        SELECT
          pid,
          sid,
          dateDiff('second', min(firstSeen), max(lastSeen)) as duration
        FROM sessions
        WHERE pid = {pid:FixedString(12)}
        GROUP BY pid, sid
      ) as sessions_data
      ON subquery.pid = sessions_data.pid
      AND subquery.sessionId = sessions_data.sid
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
      `

    if (mode === ChartRenderMode.CUMULATIVE) {
      return `
          SELECT
            *,
            sum(count) OVER (ORDER BY ${groupBy}) as count,
            sum(uniques) OVER (ORDER BY ${groupBy}) as uniques
          FROM (${baseQuery})
        `
    }

    return baseQuery
  }

  generatePerformanceAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    measure: PerfMeasure,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const cols = ['dns', 'tls', 'conn', 'response', 'render', 'domLoad', 'ttfb']

    const columnSelectors = cols
      .map((col) => `${MEASURES_MAP[measure]}(${col}) as ${col}`)
      .join(', ')

    return `
      SELECT
        ${selector},
        ${columnSelectors}
      FROM (
        SELECT *,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'performance'
          AND created BETWEEN ${tzFromDate} AND ${tzToDate}
          ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `
  }

  generatePerformanceQuantilesQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const cols = ['dns', 'tls', 'conn', 'response', 'render', 'domLoad', 'ttfb']

    const columnSelectors = cols
      .map(
        (col) => `quantilesExactInclusive(0.5, 0.75, 0.95)(${col}) as ${col}`,
      )
      .join(', ')

    return `
      SELECT
        ${selector},
        ${columnSelectors}
      FROM (
        SELECT *,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'performance'
          AND created BETWEEN ${tzFromDate} AND ${tzToDate}
          ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `
  }

  generateCaptchaAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    mode: ChartRenderMode,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`

    const baseQuery = `
      SELECT
        ${selector},
        greatest(countIf(captcha_event = 'generate'), countIf(captcha_event = 'pass')) as generated,
        countIf(captcha_event = 'pass') as passed,
        countIf(captcha_event = 'verify_fail') as failed,
        countIf(captcha_event = 'validation_fail') as validationFailed,
        countIf(captcha_event = 'replay') as replayed
      FROM (
        SELECT
          ${CAPTCHA_EVENT_SQL} as captcha_event,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'captcha'
          AND created BETWEEN ${tzFromDate} AND ${tzToDate}
          ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    if (mode === ChartRenderMode.CUMULATIVE) {
      return `
        SELECT
          ${groupBy},
          sum(generated) OVER (ORDER BY ${groupBy}) as generated,
          sum(passed) OVER (ORDER BY ${groupBy}) as passed,
          sum(failed) OVER (ORDER BY ${groupBy}) as failed,
          sum(validationFailed) OVER (ORDER BY ${groupBy}) as validationFailed,
          sum(replayed) OVER (ORDER BY ${groupBy}) as replayed
        FROM (${baseQuery})
      `
    }

    return baseQuery
  }

  generateErrorsAggregationQuery(
    timeBucket: TimeBucketType,
    filtersQuery: string,
    mode: ChartRenderMode,
  ): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)

    const baseQuery = `
      SELECT
        ${selector},
        count() as count,
        uniqExact(profileId) as affectedUsers
      FROM (
        SELECT pg, dv, br, os, lc, cc, rg, ct, profileId,
          ${timeBucketFunc}(created) as tz_created
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'error'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    if (mode === ChartRenderMode.CUMULATIVE) {
      return `
        SELECT
          ${groupBy},
          sum(count) OVER (ORDER BY ${groupBy}) as count,
          sum(affectedUsers) OVER (ORDER BY ${groupBy}) as affectedUsers
        FROM (${baseQuery})
        ORDER BY ${groupBy}
      `
    }

    return baseQuery
  }

  async groupByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    subQuery: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    customEVFilterApplied: boolean,
    parsedFilters: Array<{ [key: string]: string }>,
    mode: ChartRenderMode,
    includeConcurrency = false,
  ): Promise<object | void> {
    let params: unknown = {}
    let chart: unknown = {}

    const promises = [
      // Getting params
      (async () => {
        params = await this.groupParamsByTimeBucket(
          subQuery,
          paramsData,
          customEVFilterApplied,
          parsedFilters,
        )
      })(),

      // Getting chart & average session duration data
      (async () => {
        const groupedData = await this.groupChartByTimeBucket(
          timeBucket,
          from,
          to,
          filtersQuery,
          paramsData,
          safeTimezone,
          customEVFilterApplied,
          mode,
          includeConcurrency,
        )

        // @ts-ignore
        chart = groupedData.chart
      })(),
    ]

    await Promise.all(promises)

    return {
      params,
      chart,
    }
  }

  async getEntryPages(
    subQuery: string,
    paramsData: any,
  ): Promise<{ name: string; count: number }[]> {
    const query = `
      WITH session_first_pages AS (
        SELECT
          psid,
          argMin(pg, created) as entry_page
        ${subQuery}
        GROUP BY psid
      )
      SELECT
        entry_page as name,
        count() as count
      FROM session_first_pages
      GROUP BY entry_page
      ORDER BY count DESC
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: paramsData.params,
      })
      .then((resultSet) => resultSet.json<any>())

    return data || []
  }

  async getExitPages(
    subQuery: string,
    paramsData: any,
  ): Promise<{ name: string; count: number }[]> {
    const query = `
      WITH session_last_pages AS (
        SELECT
          psid,
          argMax(pg, created) as exit_page
        ${subQuery}
        GROUP BY psid
      )
      SELECT
        exit_page as name,
        count() as count
      FROM session_last_pages
      GROUP BY exit_page
      ORDER BY count DESC
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: paramsData.params,
      })
      .then((resultSet) => resultSet.json<any>())

    return data || []
  }

  async groupParamsByTimeBucket(
    subQuery: string,
    paramsData: any,
    customEVFilterApplied: boolean,
    parsedFilters: Array<{ [key: string]: string }>,
  ): Promise<object | void> {
    const [params, entryPage, exitPage] = await Promise.all([
      this.generateParams(
        parsedFilters,
        subQuery,
        customEVFilterApplied,
        paramsData,
        'traffic',
      ),
      customEVFilterApplied
        ? Promise.resolve([])
        : this.getEntryPages(subQuery, paramsData),
      customEVFilterApplied
        ? Promise.resolve([])
        : this.getExitPages(subQuery, paramsData),
    ])

    return {
      ...params,
      entryPage,
      exitPage,
    }
  }

  generateConcurrencyAggregationQuery(timeBucket: TimeBucketType): string {
    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)

    return `
      WITH
        parseDateTimeBestEffort({groupFrom:String}) AS fromTs,
        parseDateTimeBestEffort({groupTo:String}) AS toTs,
        toStartOfMinute(fromTs) AS fromMinute,
        toStartOfMinute(toTs) AS toMinute
      SELECT
        ${selector},
        toInt32(max(live)) AS concurrency
      FROM (
        SELECT
          ${timeBucketFunc}(toTimeZone(m, {timezone:String})) AS tz_created,
          uniqExact(psid) AS live
        FROM (
          SELECT
            psid,
            activeMinute + toIntervalMinute(arrayJoin(range(0, ${ONLINE_VISITORS_WINDOW_MINUTES}))) AS m
          FROM (
            SELECT DISTINCT
              psid,
              toStartOfMinute(created) AS activeMinute
            FROM events
            WHERE
              pid = {pid:FixedString(12)}
              AND type IN ('pageview', 'custom_event')
              AND psid IS NOT NULL
              AND psid != 0
              AND created > fromTs - toIntervalMinute(${ONLINE_VISITORS_WINDOW_MINUTES})
              AND created <= toTs
          )
        )
        WHERE m BETWEEN fromMinute AND toMinute
        GROUP BY tz_created, m
      )
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `
  }

  async getConcurrencyChartData(
    timeBucket: TimeBucketType,
    paramsData: any,
    safeTimezone: string,
    xShifted: string[],
  ): Promise<number[]> {
    const query = this.generateConcurrencyAggregationQuery(timeBucket)

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...paramsData.params, timezone: safeTimezone },
      })
      .then((resultSet) => resultSet.json<any>())

    return this.extractCountsForSessionChart(data, xShifted, 'concurrency')
  }

  async groupChartByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    customEVFilterApplied: boolean,
    mode: ChartRenderMode,
    includeConcurrency = false,
  ): Promise<object | void> {
    const { xShifted } = this.generateXAxis(timeBucket, from, to, safeTimezone)

    const query = this.generateAnalyticsAggregationQueryForScope(
      timeBucket,
      filtersQuery,
      mode,
      customEVFilterApplied,
    )

    // Concurrency is only computed when explicitly requested (the live visitors
    // metric is off by default) and within a bounded window. It is derived from
    // an unfiltered per-minute sweep over the events table, so it cannot respect
    // dashboard filters — return zeros instead of misleading unfiltered data
    const shouldComputeConcurrency =
      includeConcurrency &&
      !customEVFilterApplied &&
      !filtersQuery &&
      dayjs.utc(to).diff(dayjs.utc(from), 'minute') <=
        MAX_CONCURRENCY_WINDOW_MINUTES

    const [{ data }, concurrency] = await Promise.all([
      clickhouse
        .query({
          query,
          query_params: { ...paramsData.params, timezone: safeTimezone },
        })
        .then((resultSet) =>
          resultSet.json<TrafficCHResponse | TrafficCEFilterCHResponse>(),
        ),
      shouldComputeConcurrency
        ? this.getConcurrencyChartData(
            timeBucket,
            paramsData,
            safeTimezone,
            xShifted,
          )
        : Promise.resolve(Array(xShifted.length).fill(0)),
    ])

    const { visits, uniques, sdur, bounces } =
      this.extractAnalyticsChartDataForScope(
        data,
        xShifted,
        customEVFilterApplied,
        mode,
      )

    return Promise.resolve({
      chart: {
        x: xShifted,
        visits,
        uniques,
        sdur,
        bounces,
        concurrency,
      },
    })
  }

  generateUTCXAxis(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
  ): {
    x: string[]
    format: string
  } {
    const iterateTo = dayjs.utc(to)
    let djsFrom = dayjs.utc(from)

    let format

    switch (timeBucket) {
      case TimeBucketType.MINUTE:
      case TimeBucketType.HOUR:
        format = 'YYYY-MM-DD HH:mm:ss'
        break

      case TimeBucketType.DAY:
        format = 'YYYY-MM-DD'
        break

      case TimeBucketType.MONTH:
        format = 'YYYY-MM'
        break

      case TimeBucketType.YEAR:
        format = 'YYYY'
        break

      default:
        throw new BadRequestException(
          `The provided time bucket (${timeBucket}) is incorrect`,
        )
    }

    const x = []

    while (djsFrom.isSameOrBefore(iterateTo, timeBucket)) {
      x.push(djsFrom.format(format))
      djsFrom = djsFrom.add(1, timeBucket)
    }

    return {
      x,
      format,
    }
  }

  shiftToTimezone(x: string[], timezone: string, format: string): string[] {
    return _map(x, (date: string) =>
      dayjs.utc(date).tz(timezone).format(format),
    )
  }

  async groupSessionChartByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
  ): Promise<object | void> {
    const { xShifted } = this.generateXAxis(timeBucket, from, to, safeTimezone)

    const pageviewsQuery = this.generateSessionAggregationQuery(
      timeBucket,
      filtersQuery,
    )
    const customEventsQuery = this.generateSessionCustomEventsAggregationQuery(
      timeBucket,
      filtersQuery,
    )
    const errorsQuery = this.generateSessionErrorsAggregationQuery(
      timeBucket,
      filtersQuery,
    )

    const queryParams = { ...paramsData.params, timezone: safeTimezone }

    const [pageviewsData, customEventsData, errorsData] = await Promise.all([
      clickhouse
        .query({
          query: pageviewsQuery,
          query_params: queryParams,
        })
        .then((resultSet) => resultSet.json<any>()),
      clickhouse
        .query({
          query: customEventsQuery,
          query_params: queryParams,
        })
        .then((resultSet) => resultSet.json<any>()),
      clickhouse
        .query({
          query: errorsQuery,
          query_params: queryParams,
        })
        .then((resultSet) => resultSet.json<any>()),
    ])

    const pageviews = this.extractCountsForSessionChart(
      pageviewsData.data,
      xShifted,
      'pageviews',
    )
    const customEvents = this.extractCountsForSessionChart(
      customEventsData.data,
      xShifted,
      'customEventsCount',
    )
    const errors = this.extractCountsForSessionChart(
      errorsData.data,
      xShifted,
      'errorsCount',
    )

    const chartOutput: {
      x: string[]
      pageviews?: number[]
      customEvents?: number[]
      errors?: number[]
    } = {
      x: xShifted,
    }

    if (_some(pageviews, (count) => count > 0)) {
      chartOutput.pageviews = pageviews
    }

    if (_some(customEvents, (count) => count > 0)) {
      chartOutput.customEvents = customEvents
    }

    if (_some(errors, (count) => count > 0)) {
      chartOutput.errors = errors
    }

    return Promise.resolve({
      chart: chartOutput,
    })
  }

  extractSoftwareVersion(
    version: string | null,
    software?: string | null,
  ): string | null {
    if (!version) return null

    const [major, minor = '0', patch = '0'] = version.split('.')

    if (!major) return null

    if (software && SOFTWARE_WITH_PATCH_VERSION.includes(software)) {
      return `${major}.${minor}.${patch}`
    }

    return `${major}.${minor}`
  }

  async getRequestInformation(headers: any) {
    const ua = await UAParser(
      headers?.['user-agent'],
      extensions,
      headers,
    ).withClientHints()

    return {
      deviceType: ua.device.type || 'desktop',
      browserName: ua.browser.name,
      browserVersion: this.extractSoftwareVersion(
        ua.browser.version,
        ua.browser.name,
      ),
      osName: ua.os.name,
      osVersion: this.extractSoftwareVersion(ua.os.version),
    }
  }

  extractCaptchaChartData(result, x: string[]): any {
    const generated = Array(x.length).fill(0)
    const passed = Array(x.length).fill(0)
    const failed = Array(x.length).fill(0)
    const validationFailed = Array(x.length).fill(0)
    const replayed = Array(x.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])

      const index = x.indexOf(dateString)

      if (index !== -1) {
        const passedCount = Number(result[row].passed || 0)

        generated[index] = Math.max(
          Number(result[row].generated || 0),
          passedCount,
        )
        passed[index] = passedCount
        failed[index] = Number(result[row].failed || 0)
        validationFailed[index] = Number(result[row].validationFailed || 0)
        replayed[index] = Number(result[row].replayed || 0)
      }
    }

    return {
      generated,
      passed,
      failed,
      validationFailed,
      replayed,
      results: passed,
    }
  }

  async getCaptchaSummary(
    filtersQuery: string,
    paramsData: any,
  ): Promise<object> {
    const baseWhere = `
      FROM events
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'captcha'
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        ${filtersQuery}
    `

    const summaryQuery = `
      WITH
        ${CAPTCHA_EVENT_SQL} as captcha_event,
        ${CAPTCHA_SOLVE_MS_SQL} as solve_ms
      SELECT
        greatest(countIf(captcha_event = 'generate'), countIf(captcha_event = 'pass')) as generated,
        countIf(captcha_event = 'pass') as passed,
        round(quantileExactIf(0.5)(solve_ms, captcha_event = 'pass' AND solve_ms > 0) / 1000, 2) as solveP50,
        round(quantileExactIf(0.75)(solve_ms, captcha_event = 'pass' AND solve_ms > 0) / 1000, 2) as solveP75,
        round(quantileExactIf(0.95)(solve_ms, captcha_event = 'pass' AND solve_ms > 0) / 1000, 2) as solveP95
      ${baseWhere}
    `

    const difficultyQuery = `
      WITH
        ${CAPTCHA_EVENT_SQL} as captcha_event,
        toUInt8OrZero(${CAPTCHA_DIFFICULTY_SQL}) as difficulty
      SELECT toString(difficulty) as name, count() as count
      ${baseWhere}
        AND captcha_event = 'generate'
        AND difficulty > 0
      GROUP BY difficulty
      ORDER BY difficulty ASC
    `

    const solveTimeQuery = `
      WITH
        ${CAPTCHA_EVENT_SQL} as captcha_event,
        ${CAPTCHA_SOLVE_MS_SQL} as solve_ms,
        ${CAPTCHA_SOLVE_TIME_SQL} as bucket
      SELECT bucket as name, count() as count
      ${baseWhere}
        AND captcha_event = 'pass'
        AND solve_ms > 0
      GROUP BY bucket
      ORDER BY min(solve_ms) ASC
    `

    const queryParams = paramsData.params
    const [summary, difficulty, solveTime] = await Promise.all([
      clickhouse
        .query({ query: summaryQuery, query_params: queryParams })
        .then((resultSet) => resultSet.json<any>()),
      clickhouse
        .query({ query: difficultyQuery, query_params: queryParams })
        .then((resultSet) => resultSet.json<any>()),
      clickhouse
        .query({ query: solveTimeQuery, query_params: queryParams })
        .then((resultSet) => resultSet.json<any>()),
    ])

    const row = summary.data?.[0] || {}
    const passed = Number(row.passed || 0)
    const generated = Math.max(Number(row.generated || 0), passed)

    return {
      generated,
      passed,
      passRate: generated === 0 ? null : _round((passed / generated) * 100, 2),
      solveP50: Number(row.solveP50 || 0),
      solveP75: Number(row.solveP75 || 0),
      solveP95: Number(row.solveP95 || 0),
      difficulty: difficulty.data || [],
      solveTime: solveTime.data || [],
    }
  }

  async groupCaptchaByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    subQuery: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    mode: ChartRenderMode,
    period: string,
  ): Promise<object | void> {
    let params: unknown = {}
    let chart: unknown = {}
    let summary: unknown = {}
    let previousSummary: unknown = null

    const promises = [
      // Getting params
      (async () => {
        params = await this.generateParams(
          null,
          subQuery,
          false,
          paramsData,
          'captcha',
        )

        if (!_some(_values(params), (val) => !_isEmpty(val))) {
          throw new BadRequestException(
            'There are no parameters for the specified time frames',
          )
        }
      })(),

      // Getting CAPTCHA chart data
      (async () => {
        const { xShifted } = this.generateXAxis(
          timeBucket,
          from,
          to,
          safeTimezone,
        )

        const query = this.generateCaptchaAggregationQuery(
          timeBucket,
          filtersQuery,
          mode,
        )

        const { data } = await clickhouse
          .query({
            query,
            query_params: { ...paramsData.params, timezone: safeTimezone },
          })
          .then((resultSet) => resultSet.json<TrafficCEFilterCHResponse>())
        const captchaChart = this.extractCaptchaChartData(data, xShifted)

        chart = {
          x: xShifted,
          ...captchaChart,
        }
      })(),

      (async () => {
        summary = await this.getCaptchaSummary(filtersQuery, paramsData)
      })(),
    ]

    if (period !== 'all') {
      promises.push(
        (async () => {
          const { groupFrom, groupTo } = paramsData.params
          const periodSubtracted = dayjs
            .utc(groupFrom)
            .subtract(
              Math.abs(
                dayjs.utc(groupFrom).diff(dayjs.utc(groupTo), 'minutes'),
              ),
              'minutes',
            )
            .format('YYYY-MM-DD HH:mm:ss')

          previousSummary = await this.getCaptchaSummary(filtersQuery, {
            params: {
              ...paramsData.params,
              groupFrom: periodSubtracted,
              groupTo: groupFrom,
            },
          })
        })(),
      )
    }

    await Promise.all(promises)

    return Promise.resolve({
      params,
      chart,
      summary,
      previousSummary,
    })
  }

  async groupErrorsByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    subQuery: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    mode: ChartRenderMode,
  ): Promise<object | void> {
    let params: unknown = {}
    let chart: unknown = {}

    const promises = [
      // Params
      (async () => {
        params = await this.generateParams(
          null,
          subQuery,
          false,
          paramsData,
          'errors',
        )

        if (!_some(_values(params), (val) => !_isEmpty(val))) {
          throw new BadRequestException(
            'There are no error details for specified time frame',
          )
        }
      })(),

      // Chart data
      (async () => {
        const { x, format } = this.generateUTCXAxis(timeBucket, from, to)

        const query = this.generateErrorsAggregationQuery(
          timeBucket,
          filtersQuery,
          mode,
        )

        const { data } = await clickhouse
          .query({
            query,
            query_params: paramsData.params,
          })
          .then((resultSet) => resultSet.json<TrafficCEFilterCHResponse>())
        const { count, affectedUsers } = this.extractErrorsChartData(data, x)

        chart = {
          x: this.shiftToTimezone(x, safeTimezone, format),
          occurrences: count,
          affectedUsers,
        }
      })(),
    ]

    await Promise.all(promises)

    return Promise.resolve({
      params,
      chart,
    })
  }

  extractErrorsChartData(
    result: any[],
    x: string[],
  ): { count: number[]; affectedUsers: number[] } {
    const count = Array(x.length).fill(0)
    const affectedUsers = Array(x.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])
      const index = x.indexOf(dateString)

      if (index !== -1) {
        count[index] = Number(result[row].count) || 0
        affectedUsers[index] = Number(result[row].affectedUsers) || 0
      }
    }

    return { count, affectedUsers }
  }

  extractPerformanceChartData(result, x: string[]): any {
    const dns = Array(x.length).fill(0)
    const tls = Array(x.length).fill(0)
    const conn = Array(x.length).fill(0)
    const response = Array(x.length).fill(0)
    const render = Array(x.length).fill(0)
    const domLoad = Array(x.length).fill(0)
    const ttfb = Array(x.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])

      const index = x.indexOf(dateString)

      if (index !== -1) {
        dns[index] = _round(millisecondsToSeconds(result[row].dns), 2)
        tls[index] = _round(millisecondsToSeconds(result[row].tls), 2)
        conn[index] = _round(millisecondsToSeconds(result[row].conn), 2)
        response[index] = _round(millisecondsToSeconds(result[row].response), 2)
        render[index] = _round(millisecondsToSeconds(result[row].render), 2)
        domLoad[index] = _round(millisecondsToSeconds(result[row].domLoad), 2)
        ttfb[index] = _round(millisecondsToSeconds(result[row].ttfb), 2)
      }
    }

    return {
      dns,
      tls,
      conn,
      response,
      render,
      domLoad,
      ttfb,
    }
  }

  extractPerformanceQuantilesData(result, x: string[]): any {
    const cols = ['dns', 'tls', 'conn', 'response', 'render', 'domLoad', 'ttfb']

    const p50 = Array(x.length).fill(0)
    const p75 = Array(x.length).fill(0)
    const p95 = Array(x.length).fill(0)

    for (let row = 0; row < _size(result); ++row) {
      const dateString = this.generateDateString(result[row])

      const index = x.indexOf(dateString)

      if (index !== -1) {
        let quantiles = [0, 0, 0]

        for (let i = 0; i < cols.length; ++i) {
          const col = cols[i]
          quantiles = sumArrays(quantiles, result[row][col] || [])
        }

        p50[index] = _round(millisecondsToSeconds(quantiles[0]), 2)
        p75[index] = _round(millisecondsToSeconds(quantiles[1]), 2)
        p95[index] = _round(millisecondsToSeconds(quantiles[2]), 2)
      }
    }

    return {
      p50,
      p75,
      p95,
    }
  }

  async getPerfChartData(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    measure: PerfMeasure,
  ) {
    const { xShifted } = this.generateXAxis(timeBucket, from, to, safeTimezone)

    if (measure === 'quantiles') {
      const query = this.generatePerformanceQuantilesQuery(
        timeBucket,
        filtersQuery,
      )

      const { data } = await clickhouse
        .query({
          query,
          query_params: { ...paramsData.params, timezone: safeTimezone },
        })
        .then((resultSet) => resultSet.json())

      return {
        x: xShifted,
        ...this.extractPerformanceQuantilesData(data, xShifted),
      }
    }

    const query = this.generatePerformanceAggregationQuery(
      timeBucket,
      filtersQuery,
      measure,
    )

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...paramsData.params, timezone: safeTimezone },
      })
      .then((resultSet) => resultSet.json<PerformanceCHResponse>())

    return {
      x: xShifted,
      ...this.extractPerformanceChartData(data, xShifted),
    }
  }

  async groupPerfByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    subQuery: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    measure: PerfMeasure,
  ): Promise<object | void> {
    let params: unknown = {}
    let chart: unknown = {}

    const promises = [
      // Getting params
      (async () => {
        params = await this.generateParams(
          null,
          subQuery,
          false,
          paramsData,
          'performance',
          measure,
        )

        if (!_some(_values(params), (val) => !_isEmpty(val))) {
          throw new BadRequestException(
            'There are no parameters for the specified time frames',
          )
        }
      })(),

      // Getting chart data
      (async () => {
        chart = await this.getPerfChartData(
          timeBucket,
          from,
          to,
          filtersQuery,
          paramsData,
          safeTimezone,
          measure,
        )
      })(),
    ]

    await Promise.all(promises)

    return Promise.resolve({
      params,
      chart,
    })
  }

  async getCustomEvents(
    filtersQuery: string,
    params: any,
  ): Promise<ICustomEvent> {
    const query = `SELECT event_name AS ev, count() FROM events WHERE pid = {pid:FixedString(12)} AND type = 'custom_event' ${filtersQuery} AND created BETWEEN {groupFrom:String} AND {groupTo:String} GROUP BY event_name`
    const result = {}

    const { data } = await clickhouse
      .query({
        query,
        query_params: params.params,
      })
      .then((resultSet) => resultSet.json<CustomsCHResponse>())
    const size = _size(data)

    for (let i = 0; i < size; ++i) {
      const { ev, 'count()': c } = data[i]
      result[ev] = c
    }

    return result
  }

  async getPageProperties(
    filtersQuery: string,
    params: any,
  ): Promise<IPageProperty> {
    const query = `
      SELECT
        key AS property,
        count()
      FROM (
        SELECT
          meta.key AS key
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'pageview'
          ${filtersQuery}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
      )
      ARRAY JOIN key
      GROUP BY property`
    const result = {}

    const { data } = await clickhouse
      .query({
        query,
        query_params: params.params,
      })
      .then((resultSet) => resultSet.json<PropertiesCHResponse>())
    const size = _size(data)

    for (let i = 0; i < size; ++i) {
      const { property, 'count()': c } = data[i]

      if (!property) {
        continue
      }

      result[property] = c
    }

    return result
  }

  async getCustomEventMetadata(data: GetCustomEventMetadata): Promise<{
    result: IAggregatedMetadata[]
    appliedFilters: GetFiltersQuery[2]
  }> {
    const {
      pid,
      period,
      timeBucket,
      from,
      to,
      filters,
      timezone = DEFAULT_TIMEZONE,
      event,
    } = data

    let newTimebucket = timeBucket

    let diff

    const [filtersQuery, filtersParams, appliedFilters] = this.getFiltersQuery(
      filters,
      DataType.ANALYTICS,
    )

    if (period === 'all') {
      const res = await this.calculateTimeBucketForAllTime(pid, 'custom_event')

      diff = res.diff

      newTimebucket = _includes(res.timeBucket, timeBucket)
        ? timeBucket
        : res.timeBucket[0]
    }

    const safeTimezone = this.getSafeTimezone(timezone)
    const { groupFromUTC, groupToUTC } = this.getGroupFromTo(
      from,
      to,
      newTimebucket,
      period,
      safeTimezone,
      diff,
    )

    const query = `
      SELECT
        meta.key AS key,
        meta.value AS value,
        count() AS count
      FROM (
        SELECT
          meta.key,
          meta.value
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'custom_event'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND event_name = {event:String}
          ${filtersQuery}
      )
      ARRAY JOIN meta.key, meta.value
      GROUP BY key, value
    `

    const paramsData = {
      params: {
        pid,
        groupFrom: groupFromUTC,
        groupTo: groupToUTC,
        event,
        ...filtersParams,
      },
    }

    try {
      const { data: result } = await clickhouse
        .query({
          query,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json<IAggregatedMetadata>())

      return {
        result,
        appliedFilters,
      }
    } catch (reason) {
      console.error(`[ERROR](getCustomEventMetadata): ${reason}`)
      throw new InternalServerErrorException(
        'Something went wrong. Please, try again later.',
      )
    }
  }

  async getPagePropertyMeta(data: GetPagePropertyMetaDto): Promise<{
    result: IAggregatedMetadata[]
    appliedFilters: GetFiltersQuery[2]
  }> {
    const {
      pid,
      period,
      timeBucket,
      from,
      to,
      timezone = DEFAULT_TIMEZONE,
      property,
      filters,
    } = data

    let newTimebucket = timeBucket

    let diff

    const [filtersQuery, filtersParams, appliedFilters, customEVFilterApplied] =
      this.getFiltersQuery(filters, DataType.ANALYTICS)

    // Page properties are only present on pageview-scoped analytics rows.
    if (customEVFilterApplied) {
      return {
        result: [],
        appliedFilters,
      }
    }

    if (period === 'all') {
      const res = await this.calculateTimeBucketForAllTime(pid, 'pageview')

      diff = res.diff

      newTimebucket = _includes(res.timeBucket, timeBucket)
        ? timeBucket
        : res.timeBucket[0]
    }

    const safeTimezone = this.getSafeTimezone(timezone)
    const { groupFromUTC, groupToUTC } = this.getGroupFromTo(
      from,
      to,
      newTimebucket,
      period,
      safeTimezone,
      diff,
    )

    const query = `
      SELECT
        meta.key AS key,
        meta.value AS value,
        count() AS count
      FROM (
        SELECT
          meta.key,
          meta.value
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND indexOf(meta.key, {property:String}) > 0
          ${filtersQuery}
      )
      ARRAY JOIN meta.key, meta.value
      WHERE meta.key = {property:String}
      GROUP BY key, value
    `

    const paramsData = {
      params: {
        pid,
        groupFrom: groupFromUTC,
        groupTo: groupToUTC,
        property,
        ...filtersParams,
      },
    }

    try {
      const { data: result } = await clickhouse
        .query({
          query,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json<IAggregatedMetadata>())

      return {
        result,
        appliedFilters,
      }
    } catch (reason) {
      console.error(`[ERROR](getPagePropertyMeta): ${reason}`)
      throw new InternalServerErrorException(
        'Something went wrong. Please, try again later.',
      )
    }
  }

  async getOnlineUserCount(pid: string): Promise<number> {
    const since = dayjs
      .utc()
      .subtract(ONLINE_VISITORS_WINDOW_MINUTES, 'minute')
      .format('YYYY-MM-DD HH:mm:ss')

    const query = `
      SELECT uniqExact(psid) as count
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event')
        AND created >= {since:DateTime}
        AND psid IS NOT NULL
    `

    try {
      const { data } = await clickhouse
        .query({
          query,
          query_params: {
            pid,
            since,
          },
        })
        .then((resultSet) => resultSet.json<{ count: string }>())

      return Number(data[0]?.count || 0)
    } catch (error) {
      console.error(`[ERROR](getOnlineUserCount): ${error}`)
      return 0
    }
  }

  async groupCustomEVByTimeBucket(
    timeBucket: TimeBucketType,
    from: string,
    to: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    customEvents?: string[],
  ): Promise<object | void> {
    const { xShifted } = this.generateXAxis(timeBucket, from, to, safeTimezone)

    const timeBucketFunc = timeBucketConversion[timeBucket]
    const [selector, groupBy] = this.getGroupSubquery(timeBucket)
    const tzFromDate = `toTimeZone(parseDateTimeBestEffort({groupFrom:String}), {timezone:String})`
    const tzToDate = `toTimeZone(parseDateTimeBestEffort({groupTo:String}), {timezone:String})`
    const customEventsFilter =
      customEvents && customEvents.length > 0
        ? 'AND event_name IN {customEvents:Array(String)}'
        : ''

    const query = `
      SELECT
        ${selector},
        event_name AS ev,
        count() as count
      FROM (
        SELECT *,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'custom_event'
          AND created BETWEEN ${tzFromDate} AND ${tzToDate}
          ${filtersQuery}
          ${customEventsFilter}
      ) as subquery
      GROUP BY ${groupBy}, event_name
      ORDER BY ${groupBy}
      `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...paramsData.params,
          timezone: safeTimezone,
          customEvents: customEvents || [],
        },
      })
      .then((resultSet) => resultSet.json<CustomsCHAggregatedResponse>())

    const events = this.extractCustomEventsChartData(data, xShifted)

    return Promise.resolve({
      chart: {
        x: xShifted,
        events,
      },
    })
  }

  getSafeNumber(value: any, defaultValue: number): number {
    if (typeof value === 'undefined' || value === null || value === '') {
      return defaultValue
    }

    const n = typeof value === 'number' ? value : Number(value)

    if (!Number.isFinite(n)) {
      return defaultValue
    }

    // ClickHouse LIMIT/OFFSET must be non-negative integers
    return Math.max(0, Math.trunc(n))
  }

  processPageflow(pages: IPageflow[]) {
    if (_isEmpty(pages)) {
      return []
    }

    return _map(pages, (page: IPageflow) => {
      const processedMetadata = page.metadata
        ? _map(page.metadata, ([key, value]: [string, string]) => ({
            key,
            value,
          }))
        : undefined

      // For monetary events, extract amount and currency from metadata
      if (
        (page.type === 'sale' ||
          page.type === 'subscription' ||
          page.type === 'refund') &&
        processedMetadata
      ) {
        const amountEntry = processedMetadata.find((m) => m.key === 'amount')
        const currencyEntry = processedMetadata.find(
          (m) => m.key === 'currency',
        )

        let amount: number | undefined

        if (amountEntry) {
          const parsedAmount = parseFloat(amountEntry.value)

          if (Number.isFinite(parsedAmount) && parsedAmount >= 0) {
            amount = parsedAmount
          }
        }

        return {
          ...page,
          metadata: processedMetadata.filter(
            (m) => m.key !== 'amount' && m.key !== 'currency',
          ),
          amount,
          currency: currencyEntry ? currencyEntry.value : undefined,
        }
      }

      return {
        ...page,
        metadata: processedMetadata,
      }
    })
  }

  async getSessionDetails(
    pid: string,
    psid: string,
    safeTimezone: string,
  ): Promise<any> {
    const queryPages = `
      WITH events_with_meta AS (
        SELECT
          multiIf(event_type = 'pageview', 'pageview', event_type = 'custom_event', 'event', 'error') AS type,
          multiIf(
            event_type = 'pageview', toString(pg),
            event_type = 'custom_event', toString(event_name),
            toString(error_name)
          ) AS value,
          toTimeZone(created, {timezone:String}) AS created,
          pid,
          toString(psid) AS psid,
          if(
            event_type = 'error',
            arrayFilter(x -> x.2 != '' AND x.2 != '0', [
              tuple('message', COALESCE(error_message, '')),
              tuple('lineno', ifNull(toString(lineno), '')),
              tuple('colno', ifNull(toString(colno), '')),
              tuple('filename', COALESCE(error_filename, ''))
            ]),
            arrayFilter(x -> x.1 != '' AND x.2 != '', arrayZip(meta_key, meta_value))
          ) AS metadata
        FROM (
          SELECT
            type AS event_type,
            pg,
            event_name,
            error_name,
            error_message,
            lineno,
            colno,
            error_filename,
            created,
            pid,
            psid,
            meta.key AS meta_key,
            meta.value AS meta_value
          FROM events
          WHERE
            pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event', 'error')
            AND psid IS NOT NULL
            AND toString(psid) = {psid:String}
        )

        UNION ALL

        SELECT
          type,
          COALESCE(
            toString(product_name),
            toString(product_id),
            multiIf(
              type = 'refund',
              'Refund',
              type = 'subscription',
              'Subscription',
              'Sale'
            )
          ) AS value,
          toTimeZone(created, {timezone:String}) AS created,
          pid,
          toString(session_id) AS psid,
          [
            tuple('amount', toString(amount)),
            tuple('currency', toString(currency)),
            tuple('transaction_id', toString(transaction_id)),
            tuple('status', toString(status)),
            tuple('provider', toString(provider))
          ] AS metadata
        FROM (
          SELECT
            argMax(type, synced_at) AS type,
            argMax(product_name, synced_at) AS product_name,
            argMax(product_id, synced_at) AS product_id,
            argMax(created, synced_at) AS created,
            pid,
            session_id,
            argMax(amount, synced_at) AS amount,
            argMax(currency, synced_at) AS currency,
            transaction_id,
            argMax(status, synced_at) AS status,
            argMax(provider, synced_at) AS provider
          FROM revenue
          WHERE
            pid = {pid:FixedString(12)}
            AND session_id IS NOT NULL
            AND toString(session_id) = {psid:String}
            AND revenue.type IN ('sale', 'subscription', 'refund')
          GROUP BY pid, session_id, transaction_id
        )
      )

      SELECT
        type,
        value,
        created,
        metadata
      FROM events_with_meta
      ORDER BY created ASC
    `

    const querySessionDetails = `
      SELECT
        dv, br, brv, os, osv, lc, ref, so, me, ca, te, co, cc, rg, ct, isp, og, ut, ctp, profileId
      FROM events
      WHERE
        pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event', 'error')
        AND psid IS NOT NULL
        AND toString(psid) = {psid:String}
      ORDER BY
        CASE
          WHEN type = 'pageview' THEN 0
          WHEN type = 'custom_event' THEN 1
          ELSE 2
        END,
        created ASC
      LIMIT 1;
    `

    // A psid covers a whole day, so it can span several sessions — sum their
    // individual durations instead of measuring first-to-last, which would
    // count the idle gaps between them. Matches the sessions list's sdur.
    const querySessionDuration = `
      SELECT
        sumOrNull(duration) as duration,
        maxOrNull(sessionEnd) as lastSeen
      FROM (
        SELECT
          dateDiff('second', min(firstSeen), max(lastSeen)) as duration,
          max(lastSeen) AS sessionEnd
        FROM sessions
        WHERE
          pid = {pid:FixedString(12)}
          AND psid = toUInt64OrNull({psid:String})
        GROUP BY sid
      )
    `

    const querySessionRevenue = `
      SELECT
        toFloat64(
          sum(
            CASE WHEN type IN ('sale', 'subscription') THEN amount ELSE 0 END
          ) - sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END)
        ) AS revenue,
        toFloat64(sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END)) AS refunds
      FROM (
        SELECT
          argMax(type, synced_at) AS type,
          argMax(amount, synced_at) AS amount
        FROM revenue
        WHERE
          pid = {pid:FixedString(12)}
          AND session_id IS NOT NULL
          AND toString(session_id) = {psid:String}
          AND revenue.type IN ('sale', 'subscription', 'refund')
        GROUP BY pid, session_id, transaction_id
      )
    `

    const paramsData = {
      params: {
        pid,
        psid,
        timezone: safeTimezone,
      },
    }

    const { data: pages } = await clickhouse
      .query({
        query: queryPages,
        query_params: paramsData.params,
      })
      .then((resultSet) => resultSet.json<IPageflow>())

    let details = (
      await clickhouse
        .query({
          query: querySessionDetails,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json())
        .then(({ data }) => data)
    )[0] as any

    let duration: number = 0

    const { data: durationRows } = await clickhouse
      .query({
        query: querySessionDuration,
        query_params: paramsData.params,
      })
      .then((resultSet) =>
        resultSet.json<{
          duration: number | null
          lastSeen: string | null
        }>(),
      )

    const fromSessionsTable = durationRows[0]?.duration ?? null
    const lastSeen = durationRows[0]?.lastSeen ?? null
    if (typeof fromSessionsTable === 'number' && fromSessionsTable > 0) {
      duration = fromSessionsTable
    }

    const { data: revenueRows } = await clickhouse
      .query({
        query: querySessionRevenue,
        query_params: paramsData.params,
      })
      .then((resultSet) =>
        resultSet.json<{
          revenue: number
          refunds: number
        }>(),
      )

    const revenueTotals = revenueRows[0] || { revenue: 0, refunds: 0 }

    // If sessions table exists but `firstSeen` was historically broken (e.g. firstSeen == lastSeen),
    // we can still approximate using the first recorded event time + the session's lastSeen (which
    // may include heartbeat updates and thus be later than the last pageflow event).
    if (!duration && lastSeen && Array.isArray(pages) && pages.length >= 1) {
      const first = dayjs(pages[0].created)
      const diffSeconds = dayjs(lastSeen).diff(first, 'second')
      if (diffSeconds > 0) {
        duration = diffSeconds
      }
    }

    // Last resort: if we have at least 2 events in the pageflow, compute duration
    // from the time between the first and last events. This prevents "N/A" durations
    // when the sessions table doesn't have usable data.
    if (!duration && Array.isArray(pages) && pages.length >= 2) {
      const first = dayjs(pages[0].created)
      const last = dayjs(pages[pages.length - 1].created)
      const diffSeconds = last.diff(first, 'second')
      if (diffSeconds > 0) {
        duration = diffSeconds
      }
    }

    if (!details) {
      const querySessionDetailsFromCustomEV = `
        SELECT
          dv, br, brv, os, osv, lc, ref, so, me, ca, te, co, cc, rg, ct, isp, og, ut, ctp, profileId
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type IN ('custom_event', 'error')
          AND psid IS NOT NULL
          AND toString(psid) = {psid:String}
        ORDER BY created ASC
        LIMIT 1;
      `

      details = (
        await clickhouse
          .query({
            query: querySessionDetailsFromCustomEV,
            query_params: paramsData.params,
          })
          .then((resultSet) => resultSet.json())
          .then(({ data }) => data)
      )[0]
    }

    let chartData = {}
    let timeBucket = null

    if (!_isEmpty(pages)) {
      const from = dayjs
        .utc(pages[0].created)
        .tz(safeTimezone)
        .startOf('minute')
        .format('YYYY-MM-DD HH:mm:ss')
      const to = dayjs
        .utc(pages[_size(pages) - 1].created)
        .tz(safeTimezone)
        .endOf('minute')
        .format('YYYY-MM-DD HH:mm:ss')

      timeBucket =
        dayjs(to).diff(dayjs(from), 'hour') > 1
          ? TimeBucketType.HOUR
          : TimeBucketType.MINUTE

      const groupedChart = await this.groupSessionChartByTimeBucket(
        timeBucket,
        from,
        to,
        'AND psid IS NOT NULL AND toString(psid) = {psid:String}',
        {
          params: {
            ...paramsData.params,
            groupFrom: pages[0].created,
            groupTo: pages[_size(pages) - 1].created,
          },
        },
        safeTimezone,
      )

      // @ts-ignore
      chartData = groupedChart.chart
    }

    let isLive = false

    if (!_isEmpty(pages)) {
      const lastActivityTime = dayjs(pages[pages.length - 1].created)

      const liveThresholdTime = dayjs().subtract(
        LIVE_SESSION_THRESHOLD_SECONDS,
        'seconds',
      )
      isLive = lastActivityTime.isAfter(liveThresholdTime)
    }

    const replay = await this.getSessionReplaySummary(pid, psid)

    // If the session's profile was linked to an identified profile
    // (via the identify API), display the identified profile
    if (details?.profileId && !this.isUserSuppliedProfile(details.profileId)) {
      const userProfileId = await this.getUserProfileForAnon(
        pid,
        details.profileId,
      )

      if (userProfileId) {
        details.profileId = userProfileId
      }
    }

    return {
      pages: this.processPageflow(pages),
      details: {
        ...(details || {}),
        sdur: duration,
        isLive,
        revenue: revenueTotals.revenue || 0,
        refunds: revenueTotals.refunds || 0,
      },
      psid,
      chart: chartData,
      timeBucket,
      replay,
    }
  }

  async getSessionsList(
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    take = 30,
    skip = 0,
    customEVFilterApplied = false,
    sessionEvent: SessionsListEventType = 'traffic',
    primaryEventFilterQuery = '',
  ): Promise<object | void> {
    const primaryEventsSubquery = this.buildSessionsListPrimaryEventsSubquery(
      filtersQuery,
      customEVFilterApplied,
      sessionEvent,
      primaryEventFilterQuery,
    )

    const query = `
      WITH ${this.buildProfileAliasMapCTE()},
      distinct_sessions_filtered AS (
        SELECT
          psidCasted,
          pid,
          argMaxIf(profileId, created_for_grouping, profileId IS NOT NULL AND profileId != '') AS profileId,
          any(cc) AS cc,
          any(os) AS os,
          any(br) AS br,
          min(created_for_grouping) AS sessionStart,
          max(created_for_grouping) AS lastActivity
        FROM (${primaryEventsSubquery}) AS primary_events
        GROUP BY psidCasted, pid
      ),
      pageview_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      event_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'custom_event' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      error_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'error' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      revenue_totals AS (
        SELECT
          psidCasted,
          pid,
          sum(
            CASE WHEN type IN ('sale', 'subscription') THEN amount ELSE 0 END
          ) - sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END) as revenue,
          sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END) as refunds
        FROM (
          SELECT
            ifNull(session_id, '') AS psidCasted,
            pid,
            argMax(type, synced_at) AS type,
            argMax(amount, synced_at) AS amount
          FROM revenue
          WHERE pid = {pid:FixedString(12)} AND session_id IS NOT NULL
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
            AND revenue.type IN ('sale', 'subscription', 'refund')
          GROUP BY psidCasted, pid, transaction_id
        )
        GROUP BY psidCasted, pid
      ),
      session_duration_agg AS (
        SELECT
          psidCasted,
          pid,
          sum(session_duration) as total_duration,
          argMax(sessionProfileId, lastActivity) as profileId
        FROM (
          SELECT
            toString(psid) AS psidCasted,
            pid,
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) as session_duration,
            max(lastSeen) as lastActivity,
            argMax(coalesce(nullIf(pam.userProfileId, ''), s.profileId), lastSeen) as sessionProfileId
          FROM sessions AS s
          LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
          WHERE pid = {pid:FixedString(12)}
          GROUP BY psidCasted, pid, sid
        )
        GROUP BY psidCasted, pid
      ),
      replay_summary AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          countDistinct(replayId) AS replayCount,
          min(firstEventTimestamp) AS firstReplayTimestamp,
          max(lastEventTimestamp) AS lastReplayTimestamp,
          max(expiresAt) AS replayExpiresAt
        FROM session_replay_chunks
        WHERE pid = {pid:FixedString(12)}
          AND psid != 0
          AND expiresAt > now()
        GROUP BY psidCasted, pid
      ),
      first_session_per_profile AS (
        SELECT
          coalesce(nullIf(pam.userProfileId, ''), s.profileId) AS profileId,
          argMin(toString(psid), firstSeen) AS firstPsid
        FROM sessions AS s FINAL
        LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
        WHERE pid = {pid:FixedString(12)}
          AND s.profileId IS NOT NULL
          AND s.profileId != ''
        GROUP BY profileId
      ),
      sessions_enriched AS (
        SELECT
          dsf.psidCasted AS psidCasted,
          dsf.pid AS pid,
          dsf.cc AS cc,
          dsf.os AS os,
          dsf.br AS br,
          COALESCE(pc.count, 0) AS pageviews,
          COALESCE(ec.count, 0) AS customEvents,
          COALESCE(errc.count, 0) AS errors,
          toFloat64(COALESCE(rt.revenue, toDecimal64(0, 4))) AS revenue,
          toFloat64(COALESCE(rt.refunds, toDecimal64(0, 4))) AS refunds,
          dsf.sessionStart AS sessionStart,
          dsf.lastActivity AS lastActivity,
          sda.total_duration AS sdur,
          coalesce(nullIf(sda.profileId, ''), dsf.profileId) AS profileId,
          COALESCE(rs.replayCount, 0) AS replayCount,
          if(
            COALESCE(rs.replayCount, 0) = 0,
            NULL,
            if(
              isNull(rs.firstReplayTimestamp)
                OR isNull(rs.lastReplayTimestamp)
                OR rs.lastReplayTimestamp <= rs.firstReplayTimestamp,
              ifNull(sda.total_duration, 0),
              intDiv(rs.lastReplayTimestamp - rs.firstReplayTimestamp, 1000)
            )
          ) AS replayDuration,
          rs.replayExpiresAt AS replayExpiresAt
        FROM distinct_sessions_filtered dsf
        LEFT JOIN pageview_counts pc ON dsf.psidCasted = pc.psidCasted AND dsf.pid = pc.pid
        LEFT JOIN event_counts ec ON dsf.psidCasted = ec.psidCasted AND dsf.pid = ec.pid
        LEFT JOIN error_counts errc ON dsf.psidCasted = errc.psidCasted AND dsf.pid = errc.pid
        LEFT JOIN revenue_totals rt ON dsf.psidCasted = rt.psidCasted AND dsf.pid = rt.pid
        LEFT JOIN session_duration_agg sda ON dsf.psidCasted = sda.psidCasted AND dsf.pid = sda.pid
        LEFT JOIN replay_summary rs ON dsf.psidCasted = rs.psidCasted AND dsf.pid = rs.pid
      )
      SELECT
        se.psidCasted AS psid,
        se.cc,
        se.os,
        se.br,
        se.pageviews,
        se.customEvents,
        se.errors,
        se.revenue,
        se.refunds,
        se.sessionStart,
        se.lastActivity,
        if(dateDiff('second', se.lastActivity, now()) < ${LIVE_SESSION_THRESHOLD_SECONDS}, 1, 0) AS isLive,
        se.sdur,
        se.profileId AS profileId,
        if(startsWith(ifNull(se.profileId, ''), '${AnalyticsService.PROFILE_PREFIX_USER}'), 1, 0) AS isIdentified,
        if(
          ifNull(se.profileId, '') = '',
          0,
          if(isNull(fsp.firstPsid) OR fsp.firstPsid = '' OR fsp.firstPsid = se.psidCasted, 1, 0)
        ) AS isFirstSession,
        if(se.replayCount > 0, 1, 0) AS hasReplay,
        se.replayDuration,
        se.replayExpiresAt
      FROM sessions_enriched se
      LEFT JOIN first_session_per_profile fsp ON se.profileId = fsp.profileId
      WHERE se.psidCasted IS NOT NULL
      ORDER BY se.sessionStart DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...paramsData.params,
          timezone: safeTimezone,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json())

    return data
  }

  async getSessionReplaysList(
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    take = 30,
    skip = 0,
    customEVFilterApplied = false,
  ): Promise<object[]> {
    const primaryEventsSubquery = this.buildSessionsListPrimaryEventsSubquery(
      filtersQuery,
      customEVFilterApplied,
      'traffic',
      '',
      true,
    )

    const filteredSessionsCTE =
      _isEmpty(filtersQuery) && !customEVFilterApplied
        ? `
      filtered_sessions AS (
        SELECT
          psidCasted,
          pid,
          argMaxIf(profileId, created_for_grouping, profileId IS NOT NULL AND profileId != '') AS profileId,
          any(cc) AS cc,
          any(os) AS os,
          any(br) AS br,
          min(created_for_grouping) AS sessionStart,
          max(created_for_grouping) AS lastActivity
        FROM (${primaryEventsSubquery}) AS primary_events
        GROUP BY psidCasted, pid
      )`
        : `
      matched_sessions AS (
        SELECT DISTINCT psidCasted, pid
        FROM (${primaryEventsSubquery}) AS primary_events
        WHERE psidCasted != '0'
      ),
      filtered_sessions AS (
        SELECT
          ms.psidCasted AS psidCasted,
          ms.pid AS pid,
          argMaxIf(e.profileId, toTimeZone(e.created, {timezone:String}), e.profileId IS NOT NULL AND e.profileId != '') AS profileId,
          anyIf(e.cc, e.psid IS NOT NULL AND e.psid != 0) AS cc,
          anyIf(e.os, e.psid IS NOT NULL AND e.psid != 0) AS os,
          anyIf(e.br, e.psid IS NOT NULL AND e.psid != 0) AS br,
          minOrNull(if(e.psid IS NOT NULL AND e.psid != 0, toTimeZone(e.created, {timezone:String}), NULL)) AS sessionStart,
          maxOrNull(if(e.psid IS NOT NULL AND e.psid != 0, toTimeZone(e.created, {timezone:String}), NULL)) AS lastActivity
        FROM matched_sessions ms
        LEFT JOIN events e ON toString(ifNull(e.psid, 0)) = ms.psidCasted
          AND e.pid = ms.pid
          AND e.type IN ('pageview', 'custom_event', 'error')
          AND e.psid IS NOT NULL
          AND e.psid != 0
          AND e.created BETWEEN {groupFrom:String} AND {groupTo:String}
        WHERE ms.pid = {pid:FixedString(12)}
        GROUP BY ms.psidCasted, ms.pid
      )`

    const query = `
      WITH ${this.buildProfileAliasMapCTE()},
      ${filteredSessionsCTE},
      replay_summary AS (
        SELECT
          psidCasted,
          pid,
          replayId,
          argMax(privacyMode, latestCreated) AS privacyMode,
          count() AS chunkCount,
          sum(eventCount) AS eventCount,
          min(firstEventTimestamp) AS firstEventTimestamp,
          max(lastEventTimestamp) AS lastEventTimestamp,
          min(latestCreated) AS replayCreatedAt,
          max(latestCreated) AS lastReplayCreatedAt,
          max(chunkExpiresAt) AS replayExpiresAt
        FROM (
          SELECT
            toString(ifNull(psid, 0)) AS psidCasted,
            pid,
            replayId,
            chunkIndex,
            argMax(privacyMode, created) AS privacyMode,
            argMax(eventCount, created) AS eventCount,
            argMax(firstEventTimestamp, created) AS firstEventTimestamp,
            argMax(lastEventTimestamp, created) AS lastEventTimestamp,
            argMax(expiresAt, created) AS chunkExpiresAt,
            max(created) AS latestCreated
          FROM session_replay_chunks
          WHERE pid = {pid:FixedString(12)}
            AND psid != 0
            AND expiresAt > now()
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          GROUP BY psidCasted, pid, replayId, chunkIndex
        )
        GROUP BY psidCasted, pid, replayId
      ),
      pageview_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      event_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'custom_event' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      error_counts AS (
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)} AND type = 'error' AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      revenue_totals AS (
        SELECT
          psidCasted,
          pid,
          sum(
            CASE WHEN type IN ('sale', 'subscription') THEN amount ELSE 0 END
          ) - sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END) as revenue,
          sum(CASE WHEN type = 'refund' THEN abs(amount) ELSE 0 END) as refunds
        FROM (
          SELECT
            ifNull(session_id, '') AS psidCasted,
            pid,
            argMax(type, synced_at) AS type,
            argMax(amount, synced_at) AS amount
          FROM revenue
          WHERE pid = {pid:FixedString(12)} AND session_id IS NOT NULL
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
            AND revenue.type IN ('sale', 'subscription', 'refund')
          GROUP BY psidCasted, pid, transaction_id
        )
        GROUP BY psidCasted, pid
      ),
      session_duration_agg AS (
        SELECT
          psidCasted,
          pid,
          sum(session_duration) as total_duration,
          argMax(sessionProfileId, lastActivity) as profileId
        FROM (
          SELECT
            toString(psid) AS psidCasted,
            pid,
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) as session_duration,
            max(lastSeen) as lastActivity,
            argMax(coalesce(nullIf(pam.userProfileId, ''), s.profileId), lastSeen) as sessionProfileId
          FROM sessions AS s
          LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
          WHERE pid = {pid:FixedString(12)}
          GROUP BY psidCasted, pid, sid
        )
        GROUP BY psidCasted, pid
      ),
      replays_enriched AS (
        SELECT
          rs.psidCasted AS psidCasted,
          rs.pid AS pid,
          rs.replayId AS replayId,
          rs.privacyMode AS privacyMode,
          rs.chunkCount AS chunkCount,
          rs.eventCount AS eventCount,
          rs.firstEventTimestamp AS firstEventTimestamp,
          rs.lastEventTimestamp AS lastEventTimestamp,
          rs.replayCreatedAt AS replayCreatedAt,
          rs.lastReplayCreatedAt AS lastReplayCreatedAt,
          rs.replayExpiresAt AS replayExpiresAt,
          fs.cc AS cc,
          fs.os AS os,
          fs.br AS br,
          COALESCE(pc.count, 0) AS pageviews,
          COALESCE(ec.count, 0) AS customEvents,
          COALESCE(errc.count, 0) AS errors,
          toFloat64(COALESCE(rt.revenue, toDecimal64(0, 4))) AS revenue,
          toFloat64(COALESCE(rt.refunds, toDecimal64(0, 4))) AS refunds,
          fs.sessionStart AS sessionStart,
          fs.lastActivity AS lastActivity,
          sda.total_duration AS sdur,
          coalesce(nullIf(sda.profileId, ''), fs.profileId) AS profileId
        FROM replay_summary rs
        INNER JOIN filtered_sessions fs ON rs.psidCasted = fs.psidCasted AND rs.pid = fs.pid
        LEFT JOIN pageview_counts pc ON rs.psidCasted = pc.psidCasted AND rs.pid = pc.pid
        LEFT JOIN event_counts ec ON rs.psidCasted = ec.psidCasted AND rs.pid = ec.pid
        LEFT JOIN error_counts errc ON rs.psidCasted = errc.psidCasted AND rs.pid = errc.pid
        LEFT JOIN revenue_totals rt ON rs.psidCasted = rt.psidCasted AND rs.pid = rt.pid
        LEFT JOIN session_duration_agg sda ON rs.psidCasted = sda.psidCasted AND rs.pid = sda.pid
      ),
      first_session_per_profile AS (
        SELECT
          coalesce(nullIf(pam.userProfileId, ''), s.profileId) AS profileId,
          argMin(toString(psid), firstSeen) AS firstPsid
        FROM sessions AS s FINAL
        LEFT JOIN profile_alias_map pam ON s.profileId = pam.anonProfileId
        WHERE pid = {pid:FixedString(12)}
          AND s.profileId IS NOT NULL
          AND s.profileId != ''
        GROUP BY profileId
      )
      SELECT
        re.psidCasted AS psid,
        re.replayId,
        re.privacyMode,
        re.chunkCount,
        re.eventCount,
        re.firstEventTimestamp,
        re.lastEventTimestamp,
        re.replayCreatedAt,
        re.lastReplayCreatedAt,
        re.replayExpiresAt,
        re.cc,
        re.os,
        re.br,
        re.pageviews,
        re.customEvents,
        re.errors,
        re.revenue,
        re.refunds,
        re.sessionStart,
        re.lastActivity,
        if(dateDiff('second', re.lastActivity, now()) < ${LIVE_SESSION_THRESHOLD_SECONDS}, 1, 0) AS isLive,
        re.sdur,
        re.profileId AS profileId,
        if(startsWith(ifNull(re.profileId, ''), '${AnalyticsService.PROFILE_PREFIX_USER}'), 1, 0) AS isIdentified,
        if(
          ifNull(re.profileId, '') = '',
          0,
          if(isNull(fsp.firstPsid) OR fsp.firstPsid = '' OR fsp.firstPsid = re.psidCasted, 1, 0)
        ) AS isFirstSession
      FROM replays_enriched re
      LEFT JOIN first_session_per_profile fsp ON re.profileId = fsp.profileId
      ORDER BY re.lastReplayCreatedAt DESC, re.replayId DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...paramsData.params,
          timezone: safeTimezone,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json<any>())

    return data.map((row: any) => {
      const firstTimestamp =
        row.firstEventTimestamp === null ||
        typeof row.firstEventTimestamp === 'undefined'
          ? Number.NaN
          : Number(row.firstEventTimestamp)
      const lastTimestamp =
        row.lastEventTimestamp === null ||
        typeof row.lastEventTimestamp === 'undefined'
          ? Number.NaN
          : Number(row.lastEventTimestamp)
      const sessionDuration = Number(row.sdur) || 0
      const replayDuration =
        Number.isFinite(firstTimestamp) &&
        Number.isFinite(lastTimestamp) &&
        lastTimestamp > firstTimestamp
          ? Math.round((lastTimestamp - firstTimestamp) / 1000)
          : sessionDuration

      return {
        ...row,
        hasReplay: 1,
        chunkCount: Number(row.chunkCount) || 0,
        eventCount: Number(row.eventCount) || 0,
        replayDuration,
        replayStart:
          Number.isFinite(firstTimestamp) && firstTimestamp > 0
            ? dayjs.utc(firstTimestamp).format('YYYY-MM-DD HH:mm:ss')
            : row.replayCreatedAt,
      }
    })
  }

  private buildSessionsListPrimaryEventsSubquery(
    filtersQuery: string,
    customEVFilterApplied: boolean,
    sessionEvent: SessionsListEventType = 'traffic',
    primaryEventFilterQuery = '',
    includeProfileMatchedEventsWithPsid = false,
  ): string {
    if (customEVFilterApplied || sessionEvent === 'custom_event') {
      return `
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          profileId,
          cc,
          os,
          br,
          toTimeZone(created, {timezone:String}) AS created_for_grouping
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'custom_event'
          AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
          ${primaryEventFilterQuery}
        UNION ALL
        SELECT
          toString(s.psid) AS psidCasted,
          matching_custom_events.pid,
          matching_custom_events.profileId,
          matching_custom_events.cc,
          matching_custom_events.os,
          matching_custom_events.br,
          toTimeZone(matching_custom_events.created, {timezone:String}) AS created_for_grouping
        FROM sessions AS s FINAL
        INNER JOIN (
          SELECT pid, psid, profileId, cc, os, br, created
          FROM events
          WHERE
            pid = {pid:FixedString(12)}
            AND type = 'custom_event'
            ${
              includeProfileMatchedEventsWithPsid
                ? ''
                : 'AND (psid IS NULL OR psid = 0)'
            }
            AND profileId IS NOT NULL
            AND profileId != ''
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
            ${filtersQuery}
            ${primaryEventFilterQuery}
        ) AS matching_custom_events
          ON s.pid = matching_custom_events.pid
          AND s.profileId = matching_custom_events.profileId
          AND (matching_custom_events.psid IS NULL OR matching_custom_events.psid = 0 OR matching_custom_events.psid = s.psid)
        WHERE matching_custom_events.created BETWEEN s.firstSeen AND addSeconds(s.lastSeen, 1)
      `
    }

    if (sessionEvent === 'performance') {
      return `
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          profileId,
          cc,
          os,
          br,
          toTimeZone(created, {timezone:String}) AS created_for_grouping
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'performance'
          AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
          ${primaryEventFilterQuery}
        UNION ALL
        SELECT
          toString(ifNull(pageviews.psid, 0)) AS psidCasted,
          performance_events.pid,
          pageviews.profileId,
          pageviews.cc,
          pageviews.os,
          pageviews.br,
          toTimeZone(performance_events.created, {timezone:String}) AS created_for_grouping
        FROM (
          SELECT pid, pg, created
          FROM events
          WHERE
            pid = {pid:FixedString(12)}
            AND type = 'performance'
            AND (psid IS NULL OR psid = 0)
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
            ${filtersQuery}
            ${primaryEventFilterQuery}
        ) AS performance_events
        INNER JOIN events AS pageviews
          ON pageviews.pid = performance_events.pid
          AND pageviews.type = 'pageview'
          AND pageviews.psid IS NOT NULL
          AND pageviews.psid != 0
          AND pageviews.created BETWEEN subtractSeconds(performance_events.created, 2) AND addSeconds(performance_events.created, 2)
          AND ifNull(pageviews.pg, '') = ifNull(performance_events.pg, '')
      `
    }

    if (sessionEvent !== 'traffic') {
      return `
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          profileId,
          cc,
          os,
          br,
          toTimeZone(created, {timezone:String}) AS created_for_grouping
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = '${sessionEvent}'
          AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
          ${primaryEventFilterQuery}
      `
    }

    return `
        SELECT
          toString(ifNull(psid, 0)) AS psidCasted,
          pid,
          profileId,
          cc,
          os,
          br,
          toTimeZone(created, {timezone:String}) AS created_for_grouping
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event', 'error')
          AND psid IS NOT NULL
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
      `
  }

  private buildProfilesListDataCTE(
    filtersQuery: string,
    customEVFilterApplied: boolean,
  ): string {
    const scopedProfileFilter = customEVFilterApplied
      ? `
            AND events.profileId IN (
              SELECT DISTINCT profileId
              FROM events
              WHERE pid = {pid:FixedString(12)}
                AND type = 'custom_event'
                AND created BETWEEN {groupFrom:String} AND {groupTo:String}
                AND profileId IS NOT NULL
                AND profileId != ''
                ${filtersQuery}
            )
        `
      : filtersQuery

    // Events recorded for an anonymous profile before the visitor was
    // identified get attributed to the identified profile via profile_alias_map
    return `
        all_profile_data AS (
          SELECT
            coalesce(nullIf(pam.userProfileId, ''), events.profileId) AS profileId,
            psid,
            cc,
            os,
            br,
            dv,
            created,
            if(type = 'pageview', 1, 0) AS isPageview,
            if(type = 'custom_event', 1, 0) AS isEvent,
            if(type = 'error', 1, 0) AS isError
          FROM events
          LEFT JOIN profile_alias_map pam ON events.profileId = pam.anonProfileId
          WHERE pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event', 'error')
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
            AND events.profileId IS NOT NULL
            AND events.profileId != ''
            ${scopedProfileFilter}
        )`
  }

  async getProfilesList(
    pid: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    take = 30,
    skip = 0,
    profileType: 'all' | 'anonymous' | 'identified' = 'all',
    customEVFilterApplied = false,
  ): Promise<object[]> {
    // The profile type filter is applied to the alias-resolved profileId, so
    // anonymous history linked to an identified profile counts as identified
    let profileTypeFilter = ''
    if (profileType === 'anonymous') {
      profileTypeFilter = `WHERE profileId LIKE '${AnalyticsService.PROFILE_PREFIX_ANON}%'`
    } else if (profileType === 'identified') {
      profileTypeFilter = `WHERE profileId LIKE '${AnalyticsService.PROFILE_PREFIX_USER}%'`
    }

    const allProfileDataCTE = this.buildProfilesListDataCTE(
      filtersQuery,
      customEVFilterApplied,
    )

    const query = `
      WITH ${this.buildProfileAliasMapCTE()},
      ${allProfileDataCTE},
      profile_aggregated AS (
        SELECT
          profileId,
          sum(isPageview) AS pageviewsCount,
          countDistinct(psid) AS sessionsCount,
          sum(isEvent) AS eventsCount,
          sum(isError) AS errorsCount,
          min(created) AS firstSeen,
          max(created) AS lastSeen,
          any(cc) AS cc_agg,
          any(os) AS os_agg,
          any(br) AS br_agg,
          any(dv) AS dv_agg
        FROM all_profile_data
        ${profileTypeFilter}
        GROUP BY profileId
      )
      SELECT
        pa.profileId AS profileId,
        pa.sessionsCount AS sessionsCount,
        pa.pageviewsCount AS pageviewsCount,
        pa.eventsCount AS eventsCount,
        pa.errorsCount AS errorsCount,
        pa.firstSeen AS firstSeen,
        pa.lastSeen AS lastSeen,
        pa.cc_agg AS cc,
        pa.os_agg AS os,
        pa.br_agg AS br,
        pa.dv_agg AS dv
      FROM profile_aggregated AS pa
      ORDER BY pa.lastSeen DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, ...paramsData.params, take, skip },
      })
      .then((resultSet) => resultSet.json())

    return data
      .filter((profile: any) => profile.profileId)
      .map((profile: any) => ({
        ...profile,
        isIdentified: this.isUserSuppliedProfile(profile.profileId),
      }))
  }

  async getProfileDetails(
    pid: string,
    profileId: string,
    _safeTimezone: string,
  ): Promise<any> {
    const { canonicalId, profileIds } = await this.resolveProfileIdentity(
      pid,
      profileId,
    )

    // Query session count from sessions table
    const querySessionCount = `
      SELECT
        count() AS sessionsCount,
        min(sessions.firstSeen) AS firstSeen,
        max(sessions.lastSeen) AS lastSeen
      FROM sessions FINAL
      WHERE pid = {pid:FixedString(12)}
        AND profileId IN {profileIds:Array(String)}
    `

    // Prefer pageview timings, but fall back to sessions for event/error-only profiles.
    const queryAvgDuration = `
      WITH pageview_avg AS (
        SELECT
          avgOrNull(session_duration) AS avgDuration
        FROM (
          SELECT
            coalesce(sid, psid) AS sessionId,
            dateDiff('second', min(created), max(created)) AS session_duration
          FROM events
          WHERE pid = {pid:FixedString(12)}
            AND type = 'pageview'
            AND profileId IN {profileIds:Array(String)}
            AND psid IS NOT NULL
          GROUP BY sessionId
          HAVING session_duration > 0
        )
      ),
      sessions_avg AS (
        SELECT
          avgOrNull(session_duration) AS avgDuration
        FROM (
          SELECT
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) AS session_duration
          FROM sessions FINAL
          WHERE pid = {pid:FixedString(12)}
            AND profileId IN {profileIds:Array(String)}
          GROUP BY sid
          HAVING session_duration > 0
        )
      )
      SELECT
        coalesce(nullIf(pageview_avg.avgDuration, 0), sessions_avg.avgDuration, 0) AS avgDuration
      FROM pageview_avg, sessions_avg
    `

    const queryPageviews = `
      SELECT count() AS pageviewsCount
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type = 'pageview'
        AND profileId IN {profileIds:Array(String)}
    `

    const queryEvents = `
      SELECT count() AS eventsCount
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type = 'custom_event'
        AND profileId IN {profileIds:Array(String)}
    `

    const queryErrors = `
      SELECT count() AS errorsCount
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type = 'error'
        AND profileId IN {profileIds:Array(String)}
    `

    const queryDetails = `
      SELECT
        any(cc) AS cc,
        any(rg) AS rg,
        any(ct) AS ct,
        any(os) AS os,
        any(osv) AS osv,
        any(br) AS br,
        any(brv) AS brv,
        any(dv) AS dv,
        any(lc) AS lc,
        any(isp) AS isp,
        any(og) AS og,
        any(ut) AS ut,
        any(ctp) AS ctp
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND profileId IN {profileIds:Array(String)}
        AND type IN ('pageview', 'custom_event', 'error')
    `

    const queryRevenue = `
      SELECT
        sum(amount) AS totalRevenue,
        any(currency) AS revenueCurrency
      FROM (
        SELECT
          argMax(amount, synced_at) AS amount,
          argMax(currency, synced_at) AS currency
        FROM revenue
        WHERE pid = {pid:FixedString(12)}
          AND profile_id IN {profileIds:Array(String)}
          AND type IN ('sale', 'subscription')
          AND status = 'completed'
        GROUP BY transaction_id
      )
    `

    const params = { pid, profileIds }

    const [
      sessionCountResult,
      avgDurationResult,
      pageviewsResult,
      eventsResult,
      errorsResult,
      detailsResult,
      revenueResult,
      traits,
    ] = await Promise.all([
      clickhouse
        .query({ query: querySessionCount, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryAvgDuration, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryPageviews, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryEvents, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryErrors, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryDetails, query_params: params })
        .then((resultSet) => resultSet.json()),
      clickhouse
        .query({ query: queryRevenue, query_params: params })
        .then((resultSet) => resultSet.json()),
      this.getProfileTraits(pid, profileIds),
    ])

    const sessionCount = (sessionCountResult.data[0] || {}) as Record<
      string,
      any
    >
    const avgDuration = (avgDurationResult.data[0] || {}) as Record<string, any>
    const pageviews = (pageviewsResult.data[0] || {}) as Record<string, any>
    const events = (eventsResult.data[0] || {}) as Record<string, any>
    const errors = (errorsResult.data[0] || {}) as Record<string, any>
    const details = (detailsResult.data[0] || {}) as Record<string, any>
    const revenue = (revenueResult.data[0] || {}) as Record<string, any>

    return {
      profileId: canonicalId,
      isIdentified: this.isUserSuppliedProfile(canonicalId),
      traits,
      sessionsCount: sessionCount.sessionsCount || 0,
      pageviewsCount: pageviews.pageviewsCount || 0,
      eventsCount: events.eventsCount || 0,
      errorsCount: errors.errorsCount || 0,
      firstSeen: sessionCount.firstSeen,
      lastSeen: sessionCount.lastSeen,
      avgDuration: avgDuration.avgDuration || 0,
      totalRevenue: revenue.totalRevenue || 0,
      revenueCurrency: revenue.revenueCurrency || null,
      ...details,
    }
  }

  async getProfileTopPages(
    pid: string,
    profileId: string,
    limit = 10,
  ): Promise<{ page: string; count: number }[]> {
    const { profileIds } = await this.resolveProfileIdentity(pid, profileId)

    const query = `
      SELECT
        pg AS page,
        count() AS count
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type = 'pageview'
        AND profileId IN {profileIds:Array(String)}
      GROUP BY pg
      ORDER BY count DESC
      LIMIT {limit:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, profileIds, limit: Number(limit) },
      })
      .then((resultSet) => resultSet.json())

    return data as { page: string; count: number }[]
  }

  async getProfileActivityCalendar(
    pid: string,
    profileId: string,
    months = 4,
  ): Promise<{ date: string; pageviews: number; events: number }[]> {
    const { profileIds } = await this.resolveProfileIdentity(pid, profileId)

    const startDate = dayjs
      .utc()
      .subtract(months, 'month')
      .startOf('day')
      .format('YYYY-MM-DD')

    const query = `
      SELECT
        toDate(created) AS date,
        countIf(type = 'pageview') AS pageviews,
        countIf(type = 'custom_event') AS events
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event')
        AND profileId IN {profileIds:Array(String)}
        AND created >= {startDate:Date}
      GROUP BY date
      ORDER BY date ASC
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { pid, profileIds, startDate },
      })
      .then((resultSet) => resultSet.json())

    return data as { date: string; pageviews: number; events: number }[]
  }

  private buildProfileSessionsEventsCTE(
    filtersQuery: string,
    customEVFilterApplied: boolean,
  ): string {
    const scopedSessionFilter = customEVFilterApplied
      ? `
          AND CAST(psid, 'String') IN (
            SELECT DISTINCT CAST(psid, 'String')
            FROM events
            WHERE pid = {pid:FixedString(12)}
              AND type = 'custom_event'
              AND profileId IN {profileIds:Array(String)}
              AND psid IS NOT NULL
              AND created BETWEEN {groupFrom:String} AND {groupTo:String}
              ${filtersQuery}
          )
        `
      : filtersQuery

    return `
      all_profile_events AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          cc,
          os,
          br,
          toTimeZone(created, {timezone:String}) AS created_tz
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'custom_event', 'error')
          AND profileId IN {profileIds:Array(String)}
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${scopedSessionFilter}
      )`
  }

  async getProfileSessionPageflows(
    pid: string,
    psids: string[],
    safeTimezone: string,
    profileIds: string[],
    groupFrom: string,
    groupTo: string,
  ): Promise<Map<string, any[]>> {
    if (_isEmpty(psids)) {
      return new Map()
    }

    const query = `
      WITH events_with_meta AS (
        SELECT
          multiIf(event_type = 'pageview', 'pageview', event_type = 'custom_event', 'event', 'error') AS type,
          multiIf(
            event_type = 'pageview', toString(pg),
            event_type = 'custom_event', toString(event_name),
            toString(error_name)
          ) AS value,
          toTimeZone(created, {timezone:String}) AS created,
          toString(psid) AS psid,
          if(
            event_type = 'error',
            arrayFilter(x -> x.2 != '' AND x.2 != '0', [
              tuple('message', COALESCE(error_message, '')),
              tuple('lineno', ifNull(toString(lineno), '')),
              tuple('colno', ifNull(toString(colno), '')),
              tuple('filename', COALESCE(error_filename, ''))
            ]),
            arrayFilter(x -> x.1 != '' AND x.2 != '', arrayZip(meta_key, meta_value))
          ) AS metadata
        FROM (
          SELECT
            type AS event_type,
            pg,
            event_name,
            error_name,
            error_message,
            lineno,
            colno,
            error_filename,
            created,
            psid,
            meta.key AS meta_key,
            meta.value AS meta_value
          FROM events
          WHERE
            pid = {pid:FixedString(12)}
            AND type IN ('pageview', 'custom_event', 'error')
            AND profileId IN {profileIds:Array(String)}
            AND psid IS NOT NULL
            AND toString(psid) IN {psids:Array(String)}
            AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        )

        UNION ALL

        SELECT
          type,
          COALESCE(
            toString(product_name),
            toString(product_id),
            multiIf(
              type = 'refund',
              'Refund',
              type = 'subscription',
              'Subscription',
              'Sale'
            )
          ) AS value,
          toTimeZone(created, {timezone:String}) AS created,
          toString(session_id) AS psid,
          [
            tuple('amount', toString(amount)),
            tuple('currency', toString(currency)),
            tuple('transaction_id', toString(transaction_id)),
            tuple('status', toString(status)),
            tuple('provider', toString(provider))
          ] AS metadata
        FROM (
          SELECT
            argMax(type, synced_at) AS type,
            argMax(product_name, synced_at) AS product_name,
            argMax(product_id, synced_at) AS product_id,
            argMax(created, synced_at) AS created,
            session_id,
            argMax(amount, synced_at) AS amount,
            argMax(currency, synced_at) AS currency,
            transaction_id,
            argMax(status, synced_at) AS status,
            argMax(provider, synced_at) AS provider
          FROM revenue
          WHERE
            pid = {pid:FixedString(12)}
            AND profile_id IN {profileIds:Array(String)}
            AND session_id IS NOT NULL
            AND toString(session_id) IN {psids:Array(String)}
            AND revenue.created BETWEEN {groupFrom:String} AND {groupTo:String}
            AND revenue.type IN ('sale', 'subscription', 'refund')
          GROUP BY session_id, transaction_id
        )
      )

      SELECT
        type,
        value,
        created,
        psid,
        metadata
      FROM events_with_meta
      ORDER BY psid ASC, created ASC
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          pid,
          psids,
          timezone: safeTimezone,
          profileIds,
          groupFrom,
          groupTo,
        },
      })
      .then((resultSet) => resultSet.json<IPageflow>())

    const pageflows = new Map<string, any[]>()
    const processedPages = this.processPageflow(data as IPageflow[]) as any[]

    for (const page of processedPages) {
      const sessionId = page.psid ? String(page.psid) : null
      if (!sessionId) {
        continue
      }

      const { psid: _psid, ...pageData } = page
      const existing = pageflows.get(sessionId) || []
      existing.push(pageData)
      pageflows.set(sessionId, existing)
    }

    return pageflows
  }

  async getProfileSessionsList(
    pid: string,
    profileId: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    take = 30,
    skip = 0,
    customEVFilterApplied = false,
  ): Promise<object[]> {
    const { profileIds } = await this.resolveProfileIdentity(pid, profileId)

    const allProfileEventsCTE = this.buildProfileSessionsEventsCTE(
      filtersQuery,
      customEVFilterApplied,
    )

    const query = `
      WITH ${allProfileEventsCTE},
      profile_sessions AS (
        SELECT
          psidCasted,
          pid,
          any(cc) AS cc_agg,
          any(os) AS os_agg,
          any(br) AS br_agg,
          min(created_tz) AS sessionStart,
          max(created_tz) AS lastActivity
        FROM all_profile_events
        GROUP BY psidCasted, pid
      ),
      pageview_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND profileId IN {profileIds:Array(String)}
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      event_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'custom_event'
          AND profileId IN {profileIds:Array(String)}
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      error_counts AS (
        SELECT
          CAST(psid, 'String') AS psidCasted,
          pid,
          count() as count
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'error'
          AND profileId IN {profileIds:Array(String)}
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psidCasted, pid
      ),
      session_duration_agg AS (
        SELECT
          psidCasted,
          pid,
          sum(session_duration) as total_duration
        FROM (
          SELECT
            CAST(psid, 'String') AS psidCasted,
            pid,
            sid,
            dateDiff('second', min(firstSeen), max(lastSeen)) as session_duration
          FROM sessions
          WHERE pid = {pid:FixedString(12)}
            AND profileId IN {profileIds:Array(String)}
          GROUP BY psidCasted, pid, sid
        )
        GROUP BY psidCasted, pid
      )
      SELECT
        ps.psidCasted AS psid,
        ps.cc_agg AS cc,
        ps.os_agg AS os,
        ps.br_agg AS br,
        COALESCE(pc.count, 0) AS pageviews,
        COALESCE(ec.count, 0) AS customEvents,
        COALESCE(errc.count, 0) AS errors,
        ps.sessionStart,
        ps.lastActivity,
        if(dateDiff('second', ps.lastActivity, now()) < ${LIVE_SESSION_THRESHOLD_SECONDS}, 1, 0) AS isLive,
        sda.total_duration AS sdur
      FROM profile_sessions ps
      LEFT JOIN pageview_counts pc ON ps.psidCasted = pc.psidCasted AND ps.pid = pc.pid
      LEFT JOIN event_counts ec ON ps.psidCasted = ec.psidCasted AND ps.pid = ec.pid
      LEFT JOIN error_counts errc ON ps.psidCasted = errc.psidCasted AND ps.pid = errc.pid
      LEFT JOIN session_duration_agg sda ON ps.psidCasted = sda.psidCasted AND ps.pid = sda.pid
      WHERE ps.psidCasted IS NOT NULL
      ORDER BY ps.sessionStart DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          pid,
          profileIds,
          ...paramsData.params,
          timezone: safeTimezone,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json())

    const sessions = data as any[]
    const pageflows = await this.getProfileSessionPageflows(
      pid,
      sessions.map((session) => String(session.psid)).filter(Boolean),
      safeTimezone,
      profileIds,
      paramsData.params.groupFrom,
      paramsData.params.groupTo,
    )

    return sessions.map((session) => ({
      ...session,
      pages: pageflows.get(String(session.psid)) || [],
    }))
  }

  async getErrorsList(
    options: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    take = 30,
    skip = 0,
  ): Promise<object | void> {
    let parsedOptions: {
      showResolved?: boolean
    } = {}

    try {
      parsedOptions = JSON.parse(options)
    } catch {
      console.error('[getErrorsList] Failed to parse options:', options)
    }

    const query = `
      SELECT
        eid,
        any(name) as name,
        any(message) as message,
        any(filename) as filename,
        count(*) as count,
        max(created) as last_seen,
        count(DISTINCT profileId) as users,
        count(DISTINCT coalesce(sid, psid)) as sessions,
        status.status
      FROM (
        SELECT
          eid,
          error_name AS name,
          error_message AS message,
          error_filename AS filename,
          sid,
          psid,
          profileId,
          toTimeZone(created, {timezone:String}) AS created
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'error'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
      ) AS errors
      LEFT JOIN (
        SELECT
          eid,
          argMax(status, updated) AS status
        FROM error_statuses
        WHERE pid = {pid:FixedString(12)}
        GROUP BY eid
      ) AS status ON errors.eid = status.eid
      ${
        parsedOptions?.showResolved
          ? ''
          : "WHERE status.status = 'active' OR status.status = 'regressed' OR status.status IS NULL"
      }
      GROUP BY errors.eid, status.status
      ORDER BY last_seen DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32};
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...paramsData.params,
          timezone: safeTimezone,
          take,
          skip,
        },
      })
      .then((resultSet) => resultSet.json())

    return data
  }

  async getErrorDetails(
    pid: string,
    eid: string,
    safeTimezone: string,
    groupFrom: string,
    groupTo: string,
    timeBucket: any,
  ): Promise<any> {
    const queryErrorDetails = `
      SELECT
        subquery.eid,
        any(subquery.name) AS name,
        any(subquery.message) AS message,
        any(subquery.filename) AS filename,
        any(subquery.colno) AS colno,
        any(subquery.lineno) AS lineno,
        any(subquery.stackTrace) AS stackTrace,
        count(*) AS count,
        status.status
      FROM (
        SELECT
          eid,
          error_name AS name,
          error_message AS message,
          error_filename AS filename,
          colno,
          lineno,
          stackTrace
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'error'
          AND eid = {eid:FixedString(32)}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        ORDER BY created DESC
        LIMIT 1 BY eid
      ) AS subquery
      LEFT JOIN (
        SELECT
          eid,
          argMax(status, updated) AS status
        FROM error_statuses
        WHERE pid = {pid:FixedString(12)}
          AND eid = {eid:FixedString(32)}
        GROUP BY eid
      ) AS status ON subquery.eid = status.eid
      GROUP BY subquery.eid, status.status;
    `

    const queryFirstLastSeen = `
      SELECT
        max(created) AS last_seen,
        min(created) AS first_seen,
        count() AS count
      FROM events
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'error'
        AND eid = {eid:FixedString(32)}
        AND created BETWEEN {groupFrom:String} AND {groupTo:String};
    `

    const queryMetadata = `
      SELECT
        meta.key AS key,
        meta.value AS value,
        count() AS count
      FROM (
        SELECT
          meta.key,
          meta.value
        FROM events
        WHERE
          pid = {pid:FixedString(12)}
          AND type = 'error'
          AND eid = {eid:FixedString(32)}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
      )
      ARRAY JOIN meta.key, meta.value
      WHERE meta.key != '' AND meta.value != ''
      GROUP BY key, value
      ORDER BY count DESC
    `

    const paramsData = {
      params: {
        pid,
        eid,
        groupFrom,
        groupTo,
      },
    }

    const [details, occurenceDetails, metadata] = await Promise.all([
      clickhouse
        .query({
          query: queryErrorDetails,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0]),
      clickhouse
        .query({
          query: queryFirstLastSeen,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0]),
      clickhouse
        .query({
          query: queryMetadata,
          query_params: paramsData.params,
        })
        .then((resultSet) => resultSet.json<IAggregatedMetadata>())
        .then(({ data }) => data),
    ])

    const groupedChart = await this.groupErrorsByTimeBucket(
      timeBucket,
      groupFrom,
      groupTo,
      `FROM events WHERE type = 'error' AND pid = {pid:FixedString(12)} AND eid = {eid:FixedString(32)} AND created BETWEEN {groupFrom:String} AND {groupTo:String}`,
      'AND pid = {pid:FixedString(12)} AND eid = {eid:FixedString(32)}',
      paramsData,
      safeTimezone,
      ChartRenderMode.PERIODICAL,
    )

    return {
      details: {
        ...details,
        ...occurenceDetails,
      },
      metadata,
      ...groupedChart,
      timeBucket,
    }
  }

  async getErrorOverview(
    pid: string,
    filtersQuery: string,
    paramsData: any,
    safeTimezone: string,
    groupFrom: string,
    groupTo: string,
    timeBucket: string,
    showResolved: boolean,
    sessionFiltersQuery = '',
    sessionFiltersParams: Record<string, unknown> = {},
  ): Promise<any> {
    const resolvedFilter = showResolved
      ? ''
      : "AND (status.status = 'active' OR status.status = 'regressed' OR status.status IS NULL)"

    // Get total sessions from all matching events for the time range
    const queryTotalSessions = `
      SELECT count(DISTINCT coalesce(sid, psid)) as totalSessions
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event', 'error')
        AND psid IS NOT NULL
        AND psid != 0
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        ${sessionFiltersQuery}
    `

    // Get error stats: total errors, unique errors, affected sessions, affected users
    const queryErrorStats = `
      SELECT
        count(*) as totalErrors,
        count(DISTINCT eid) as uniqueErrors,
        count(DISTINCT psid) as affectedSessions,
        count(DISTINCT profileId) as affectedUsers
      FROM events AS errors
      LEFT JOIN (
        SELECT eid, argMax(status, updated) AS status
        FROM error_statuses
        WHERE pid = {pid:FixedString(12)}
        GROUP BY eid
      ) AS status ON errors.eid = status.eid
      WHERE pid = {pid:FixedString(12)}
        AND type = 'error'
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        ${filtersQuery}
        ${resolvedFilter}
    `

    // Get most frequent error
    const queryMostFrequentError = `
      SELECT
        errors.eid as eid,
        any(errors.error_name) as name,
        any(errors.error_message) as message,
        count(*) as count,
        count(DISTINCT errors.profileId) as usersAffected,
        max(errors.created) as lastSeen
      FROM events AS errors
      LEFT JOIN (
        SELECT eid, argMax(status, updated) AS status
        FROM error_statuses
        WHERE pid = {pid:FixedString(12)}
        GROUP BY eid
      ) AS status ON errors.eid = status.eid
      WHERE errors.pid = {pid:FixedString(12)}
        AND errors.type = 'error'
        AND errors.created BETWEEN {groupFrom:String} AND {groupTo:String}
        ${filtersQuery}
        ${resolvedFilter}
      GROUP BY errors.eid
      ORDER BY count DESC
      LIMIT 1
    `

    // Get chart data with affected users - use same pattern as other chart methods
    const timeBucketFunc =
      timeBucketConversion[timeBucket as keyof typeof timeBucketConversion] ||
      timeBucketConversion.day
    const [selector, groupBy] = this.getGroupSubquery(
      timeBucket as TimeBucketType,
    )
    const queryChart = `
      SELECT
        ${selector},
        count(*) as count,
        uniqExact(profileId) as affectedUsers
      FROM (
        SELECT
          profileId,
          ${timeBucketFunc}(toTimeZone(created, {timezone:String})) as tz_created
        FROM events AS errors
        LEFT JOIN (
          SELECT eid, argMax(status, updated) AS status
          FROM error_statuses
          WHERE pid = {pid:FixedString(12)}
          GROUP BY eid
        ) AS status ON errors.eid = status.eid
        WHERE pid = {pid:FixedString(12)}
          AND type = 'error'
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
          ${resolvedFilter}
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    const paramsWithTimezone = {
      ...paramsData.params,
      timezone: safeTimezone,
    }

    const [
      totalSessionsResult,
      errorStatsResult,
      mostFrequentResult,
      chartResult,
    ] = await Promise.all([
      clickhouse
        .query({
          query: queryTotalSessions,
          query_params: {
            ...paramsData.params,
            ...sessionFiltersParams,
          },
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0]),
      clickhouse
        .query({
          query: queryErrorStats,
          query_params: paramsWithTimezone,
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0]),
      clickhouse
        .query({
          query: queryMostFrequentError,
          query_params: paramsWithTimezone,
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0] || null),
      clickhouse
        .query({
          query: queryChart,
          query_params: paramsWithTimezone,
        })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data),
    ])

    // Process chart data using same pattern as extractErrorsChartData
    const { x, format } = this.generateUTCXAxis(
      timeBucket as TimeBucketType,
      groupFrom,
      groupTo,
    )
    const occurrences = Array(x.length).fill(0)
    const affectedUsersChart = Array(x.length).fill(0)

    for (const row of chartResult) {
      const dateString = this.generateDateString(row)
      const index = x.indexOf(dateString)
      if (index !== -1) {
        occurrences[index] = Number(row.count) || 0
        affectedUsersChart[index] = Number(row.affectedUsers) || 0
      }
    }

    const errorRate =
      totalSessionsResult?.totalSessions > 0
        ? (Number(errorStatsResult?.affectedSessions || 0) /
            Number(totalSessionsResult.totalSessions)) *
          100
        : 0

    return {
      stats: {
        totalErrors: Number(errorStatsResult?.totalErrors || 0),
        uniqueErrors: Number(errorStatsResult?.uniqueErrors || 0),
        affectedSessions: Number(errorStatsResult?.affectedSessions || 0),
        affectedUsers: Number(errorStatsResult?.affectedUsers || 0),
        errorRate: Math.round(errorRate * 100) / 100,
      },
      mostFrequentError: mostFrequentResult
        ? {
            eid: mostFrequentResult.eid,
            name: mostFrequentResult.name,
            message: mostFrequentResult.message,
            count: Number(mostFrequentResult.count),
            usersAffected: Number(mostFrequentResult.usersAffected),
            lastSeen: mostFrequentResult.lastSeen,
          }
        : null,
      chart: {
        x: this.shiftToTimezone(x, safeTimezone, format),
        occurrences,
        affectedUsers: affectedUsersChart,
      },
      timeBucket,
    }
  }

  async getErrorAffectedSessions(
    pid: string,
    eid: string,
    groupFrom: string,
    groupTo: string,
    take: number = 10,
    skip: number = 0,
    filtersQuery = '',
    filtersParams: Record<string, unknown> = {},
  ): Promise<{ sessions: any[]; total: number }> {
    const queryCount = `
      SELECT count(DISTINCT psid) as total
      FROM events
      WHERE pid = {pid:FixedString(12)}
        AND type = 'error'
        AND eid = {eid:FixedString(32)}
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        ${filtersQuery}
    `

    const querySessions = `
      SELECT
        CAST(errors.psid, 'String') as psid,
        errors.profileId as profileId,
        any(analytics.cc) as cc,
        any(analytics.br) as br,
        any(analytics.os) as os,
        errors.firstErrorAt as firstErrorAt,
        errors.lastErrorAt as lastErrorAt,
        errors.errorCount as errorCount
      FROM (
        SELECT
          pid,
          psid,
          any(profileId) AS profileId,
          min(created) AS firstErrorAt,
          max(created) AS lastErrorAt,
          count(*) AS errorCount
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'error'
          AND eid = {eid:FixedString(32)}
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
          ${filtersQuery}
        GROUP BY pid, psid
      ) AS errors
      LEFT JOIN (
        SELECT
          pid,
          psid,
          any(cc) AS cc,
          any(br) AS br,
          any(os) AS os,
          countIf(type = 'pageview') AS pageviews
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type IN ('pageview', 'error', 'custom_event')
          AND psid IS NOT NULL
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY pid, psid
      ) AS analytics
        ON errors.psid = analytics.psid
        AND errors.pid = analytics.pid
      GROUP BY
        errors.psid,
        errors.profileId,
        errors.firstErrorAt,
        errors.lastErrorAt,
        errors.errorCount
      ORDER BY errors.lastErrorAt DESC
      LIMIT {take:UInt32}
      OFFSET {skip:UInt32}
    `

    const params = {
      ...filtersParams,
      pid,
      eid,
      groupFrom,
      groupTo,
      take,
      skip,
    }

    const [countResult, sessionsResult] = await Promise.all([
      clickhouse
        .query({ query: queryCount, query_params: params })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data[0]),
      clickhouse
        .query({ query: querySessions, query_params: params })
        .then((resultSet) => resultSet.json<any>())
        .then(({ data }) => data),
    ])

    return {
      sessions: sessionsResult,
      total: Number(countResult?.total || 0),
    }
  }

  getErrorID(errorDTO: ErrorDto): string {
    const { name, message, colno, lineno, filename } = errorDTO

    return hash(`${name}${message}${colno}${lineno}${filename}`).substring(
      0,
      32,
    )
  }

  async validateEIDs(eids: string[], pid: string) {
    if (_isEmpty(eids)) {
      throw new BadRequestException('At least one error ID is required')
    }

    const params = _reduce(
      eids,
      (acc, curr, index) => ({
        ...acc,
        [`e_${index}`]: curr,
      }),
      {},
    )

    const query = `SELECT count(DISTINCT eid) as count FROM events WHERE pid = {pid:FixedString(12)} AND type = 'error' AND eid IN (${_map(
      params,
      (val, key) => `{${key}:FixedString(32)}`,
    ).join(', ')})`

    let result

    try {
      result = (
        await clickhouse
          .query({
            query,
            query_params: { ...params, pid },
          })
          .then((resultSet) => resultSet.json())
          .then(({ data }) => data)
      )[0]
    } catch (reason) {
      console.error('validateEIDs - clickhouse request error')
      console.error(reason)
      throw new InternalServerErrorException(
        'Error occured while validating error IDs',
      )
    }

    if (result.count !== _size(eids)) {
      throw new UnprocessableEntityException(
        'Some of the error IDs you provided are not valid (do not exist in our database)',
      )
    }
  }

  async updateEIDStatus(
    eids: string[],
    status: 'resolved' | 'active',
    pid: string,
  ) {
    const values = _reduce(
      eids,
      (acc, curr) => [
        ...acc,
        {
          eid: curr,
          status,
          pid,
        },
      ],
      [],
    )

    try {
      await clickhouse.insert({
        table: 'error_statuses',
        format: 'JSONEachRow',
        values: [values],
      })
    } catch (reason) {
      console.error('Error at PATCH error-status:')
      console.error(reason)
      throw new InternalServerErrorException(
        'Error occured while updating error status',
      )
    }
  }

  checkIfPerfMeasureIsValid(measure: PerfMeasure) {
    const validMeasures = ['average', 'median', 'p95', 'p75', 'quantiles']

    if (!_includes(validMeasures, measure)) {
      throw new UnprocessableEntityException(
        `Please provide a valid "measure" parameter, it must be one of ${validMeasures}`,
      )
    }
  }

  async getGeneralStats(): Promise<{
    users: number
    trials: number
    projects: number
    events: number
  }> {
    const query = `
      SELECT count(*) AS count FROM events
    `

    const users = await this.userService.count({
      where: {
        planCode: Not(In([PlanCode.free, PlanCode.trial, PlanCode.none])),
      },
    })
    const trials = await this.userService.count()
    const projects = await this.projectService.count()
    const { data } = await clickhouse
      .query({
        query,
      })
      .then((resultSet) => resultSet.json())

    // @ts-expect-error
    const events = data.reduce((total, row) => total + row.count, 0) as number

    await redis.set(REDIS_USERS_COUNT_KEY, users, 'EX', 630)
    await redis.set(REDIS_TRIALS_COUNT_KEY, trials, 'EX', 630)
    await redis.set(REDIS_PROJECTS_COUNT_KEY, projects, 'EX', 630)
    await redis.set(REDIS_EVENTS_COUNT_KEY, events, 'EX', 630)

    return {
      users,
      trials,
      projects,
      events,
    }
  }

  async getMetaResults(
    pid: string,
    customEvents: ProjectViewCustomEventDto[],
    filtersQuery: string,
    paramsData: any,
    timezone: string,
    period?: string,
    from?: string,
    to?: string,
  ) {
    let _from = from

    let _to = to

    if (_isEmpty(period) || ['today', 'yesterday', 'custom'].includes(period)) {
      const safeTimezone = this.getSafeTimezone(timezone)

      const { groupFrom, groupTo } = this.getGroupFromTo(
        from,
        to,
        ['today', 'yesterday'].includes(period) ? TimeBucketType.HOUR : null,
        period,
        safeTimezone,
      )

      _from = groupFrom
      _to = groupTo
    }

    const results = []

    await Promise.all(
      customEvents.map(async (event) => {
        const result = await this.getMetaResult(
          pid,
          event,
          filtersQuery,
          paramsData,
          period,
          _from,
          _to,
        )
        results.push(result)
      }),
    )

    return results.filter((r) => !!r)
  }

  async getMetaResult(
    pid: string,
    metric: ProjectViewCustomEventDto,
    filtersQuery: string,
    paramsData: any,
    period?: string,
    from?: string,
    to?: string,
  ) {
    if (metric.metaValueType === ProjectViewCustomEventMetaValueType.STRING) {
      return undefined
    }

    const paramsKeys = ['customEventName', 'metaKey', 'metaValue', 'metricKey']

    const aggFunction =
      metric.metaValueType === ProjectViewCustomEventMetaValueType.INTEGER
        ? 'toInt32OrZero(value)'
        : 'toFloat32OrZero(value)'

    let kvQuery = ''

    if (metric.metaKey && metric.metaValue) {
      kvQuery = `AND (
        indexOf(meta.key, {metaKey:String}) > 0
        AND meta.value[indexOf(meta.key, {metaKey:String})] = {metaValue:String}
      )`
    } else if (metric.metaKey) {
      kvQuery = `AND indexOf(meta.key, {metaKey:String}) > 0`
    } else if (metric.metaValue) {
      kvQuery = `AND indexOf(meta.value, {metaValue:String}) > 0`
    }

    const queryCurrent = `
      SELECT
        1 AS sortOrder,
        key,
        round(sum(${aggFunction}), 2) AS sum,
        round(avg(${aggFunction}), 2) AS avg
      FROM events
      ARRAY JOIN meta.key AS key, meta.value AS value
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'custom_event'
        AND key = {metricKey:String}
        AND event_name = {customEventName:String}
        AND created BETWEEN {periodFormatted:String} AND {now:String}
        ${kvQuery}
        ${filtersQuery}
      GROUP BY key
    `

    const queryPrevious = `
      SELECT
        2 AS sortOrder,
        key,
        round(sum(${aggFunction}), 2) AS sum,
        round(avg(${aggFunction}), 2) AS avg
      FROM events
      ARRAY JOIN meta.key AS key, meta.value AS value
      WHERE
        pid = {pid:FixedString(12)}
        AND type = 'custom_event'
        AND key = {metricKey:String}
        AND event_name = {customEventName:String}
        AND created BETWEEN {periodSubtracted:String} AND {periodFormatted:String}
        ${kvQuery}
        ${filtersQuery}
      GROUP BY key
    `

    let now
    let periodFormatted
    let periodSubtracted

    if (period === 'all') {
      now = dayjs.utc().format('YYYY-MM-DD HH:mm:ss')
      periodFormatted = '1970-01-01 00:00:00'
      periodSubtracted = '1970-01-01 00:00:00'
    } else if (from && to) {
      const diff = dayjs(to).diff(dayjs(from), 'days') || 1

      now = to
      periodFormatted = from
      periodSubtracted = dayjs(from)
        .subtract(diff, 'days')
        .format('YYYY-MM-DD HH:mm:ss')
    } else {
      const amountToSubtract = parseInt(period, 10)
      const unit = _replace(period, /[0-9]/g, '') as dayjs.ManipulateType

      now = dayjs.utc().format('YYYY-MM-DD HH:mm:ss')
      const periodRaw = dayjs.utc().subtract(amountToSubtract, unit)
      periodFormatted = periodRaw.format('YYYY-MM-DD HH:mm:ss')
      periodSubtracted = periodRaw
        .subtract(amountToSubtract, unit)
        .format('YYYY-MM-DD HH:mm:ss')
    }

    const params = {
      pid,
      ...paramsKeys.reduce(
        (acc, curr) => ({
          ...acc,
          [curr]: metric[curr],
        }),
        {},
      ),
      now,
      periodFormatted,
      periodSubtracted,
      ...paramsData.params,
    }

    try {
      let { data } = await clickhouse
        .query({
          query: `${queryCurrent} UNION ALL ${queryPrevious}`,
          query_params: params,
        })
        .then((resultSet) =>
          resultSet.json<{
            sortOrder: 1 | 2
            key: string
            sum: number
            avg: number
          }>(),
        )

      data = _sortBy(data, 'sortOrder')

      const currentPeriod = data[0]
      const previousPeriod = data[1]

      if (!currentPeriod) {
        return undefined
      }

      return {
        key: currentPeriod.key,
        current: {
          sum: currentPeriod.sum || 0,
          avg: currentPeriod.avg || 0,
        },
        previous: {
          sum: previousPeriod?.sum || 0,
          avg: previousPeriod?.avg || 0,
        },
      }
    } catch (reason) {
      console.error('[ERROR] (getMetaResult) - Clickhouse query error:')
      console.error(reason)
      throw new InternalServerErrorException(
        'Error occurred while fetching meta results',
      )
    }
  }
}