fix(cohorts): match wildcard event property keys - #513

  const values: string[] = [];

  if (property.startsWith('properties.')) {
    const query = clix(ch)
      .select<{
        property_value: string;
        created_at: string;
      }>(['distinct property_value', 'max(created_at) as created_at'])
      .from(TABLE_NAMES.event_property_values_mv)
      .where('project_id', '=', projectId)
      .where(
        'property_key',
        property.includes('*') ? 'LIKE' : '=',
        property.includes('*')
          ? transformPropertyKey(property)
          : property.replace(/^properties\./, ''),
      )
      .groupBy(['property_value'])
      // Recency + key tie-break for a stable list under the cap — same
      // rationale as the key picker above.
      .orderBy('created_at', 'DESC')
      .orderBy('property_value', 'ASC')
      .limit(EVENT_PROPERTY_VALUE_AUTOCOMPLETE_LIMIT);

    if (event && event !== '*') {
      query.where('name', '=', event);
    }

    const res = await query.execute();

    values.push(...res.map((e) => e.property_value));
  } else if (property.startsWith('profile.')) {
    const selectExpr = getProfilePropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.profiles, true)
      .where('project_id', '=', projectId)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property.startsWith('group.')) {
    const selectExpr = getGroupPropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.groups, true)
      .where('project_id', '=', projectId)
      .where('deleted', '=', 0)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property === 'cohort' || property.startsWith('cohort:')) {
    // Cohort filters use a dedicated cohort multi-select on the client
    // (ComboboxAdvanced over all cohorts) — values aren't sourced from
    // an event-column distinct query. Without this guard, the events
    // SELECT would emit a literal `cohort:<uuid>` identifier and crash
    // with a ClickHouse syntax error.
    return { values: [] };
  } else {
    // Normalize bare utm_* names to `properties.__query.utm_*` and rewrite
    // camelCase aliases (`referrerName`) to their snake_case columns.
    // Unknown identifiers (saved-report typos like `temple_name`, or
    // misnamed columns from older clients) get an empty value list rather
    // than crashing the autocomplete query with UNKNOWN_IDENTIFIER.
    const resolvedProperty = normalizeEventField(property);
    if (!isKnownEventField(resolvedProperty)) {
      return { values: [] };
    }
    // Session-level columns (geo/device/referrer) come from the small
    // `sessions` table, not a billions-row `events` scan. Only for
    // all-events (`*`): `sessions` has no `name` column to filter on.
    const useSessions =
      event === '*' && SESSION_LEVEL_VALUE_COLUMNS.has(resolvedProperty);
    const query = clix(ch)
      .select<{ values: string[] }>([
        `distinct ${getSelectPropertyKey(resolvedProperty)} as values`,
      ])
      .from(useSessions ? TABLE_NAMES.sessions : TABLE_NAMES.events)
      .where('project_id', '=', projectId)
      .where(
        'created_at',
        '>',
        clix.exp(`now() - INTERVAL ${VALUES_LOOKBACK_DAYS} DAY`),
      )
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    if (!useSessions && event !== '*') {
      query.where('name', '=', event);
    }

    const events = await query.execute();

    values.push(
      ...pipe(
        (data: typeof events) => map(prop('values'), data),
        flatten,
        uniq,
        sort((a, b) => a.length - b.length)
      )(events)
    );
  }

  return {
    values,
  };
})

  const values: string[] = [];

  if (property.startsWith('properties.')) {
    const query = clix(ch)
      .select<{
        property_value: string;
        created_at: string;
      }>(['distinct property_value', 'max(created_at) as created_at'])
      .from(TABLE_NAMES.event_property_values_mv)
      .where('project_id', '=', projectId)
      .where(
        'property_key',
        property.includes('*') ? 'LIKE' : '=',
        property.includes('*')
          ? transformPropertyKey(property)
          : property.replace(/^properties\./, ''),
      )
      .groupBy(['property_value'])
      // Recency + key tie-break for a stable list under the cap — same
      // rationale as the key picker above.
      .orderBy('created_at', 'DESC')
      .orderBy('property_value', 'ASC')
      .limit(EVENT_PROPERTY_VALUE_AUTOCOMPLETE_LIMIT);

    if (event && event !== '*') {
      query.where('name', '=', event);
    }

    const res = await query.execute();

    values.push(...res.map((e) => e.property_value));
  } else if (property.startsWith('profile.')) {
    const selectExpr = getProfilePropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.profiles, true)
      .where('project_id', '=', projectId)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property.startsWith('group.')) {
    const selectExpr = getGroupPropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.groups, true)
      .where('project_id', '=', projectId)
      .where('deleted', '=', 0)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property === 'cohort' || property.startsWith('cohort:')) {
    // Cohort filters use a dedicated cohort multi-select on the client
    // (ComboboxAdvanced over all cohorts) — values aren't sourced from
    // an event-column distinct query. Without this guard, the events
    // SELECT would emit a literal `cohort:<uuid>` identifier and crash
    // with a ClickHouse syntax error.
    return { values: [] };
  } else {
    // Normalize bare utm_* names to `properties.__query.utm_*` and rewrite
    // camelCase aliases (`referrerName`) to their snake_case columns.
    // Unknown identifiers (saved-report typos like `temple_name`, or
    // misnamed columns from older clients) get an empty value list rather
    // than crashing the autocomplete query with UNKNOWN_IDENTIFIER.
    const resolvedProperty = normalizeEventField(property);
    if (!isKnownEventField(resolvedProperty)) {
      return { values: [] };
    }
    // Session-level columns (geo/device/referrer) come from the small
    // `sessions` table, not a billions-row `events` scan. Only for
    // all-events (`*`): `sessions` has no `name` column to filter on.
    const useSessions =
      event === '*' && SESSION_LEVEL_VALUE_COLUMNS.has(resolvedProperty);
    const query = clix(ch)
      .select<{ values: string[] }>([
        `distinct ${getSelectPropertyKey(resolvedProperty)} as values`,
      ])
      .from(useSessions ? TABLE_NAMES.sessions : TABLE_NAMES.events)
      .where('project_id', '=', projectId)
      .where(
        'created_at',
        '>',
        clix.exp(`now() - INTERVAL ${VALUES_LOOKBACK_DAYS} DAY`),
      )
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    if (!useSessions && event !== '*') {
      query.where('name', '=', event);
    }

    const events = await query.execute();

    values.push(
      ...pipe(
        (data: typeof events) => map(prop('values'), data),
        flatten,
        uniq,
        sort((a, b) => a.length - b.length)
      )(events)
    );
  }

  return {
    values,
  };
})

  const values: string[] = [];

  if (property.startsWith('properties.')) {
    const query = clix(ch)
      .select<{
        property_value: string;
        created_at: string;
      }>(['distinct property_value', 'max(created_at) as created_at'])
      .from(TABLE_NAMES.event_property_values_mv)
      .where('project_id', '=', projectId)
      .where(
        'property_key',
        property.includes('*') ? 'LIKE' : '=',
        property.includes('*')
          ? transformPropertyKey(property)
          : property.replace(/^properties\./, ''),
      )
      .groupBy(['property_value'])
      // Recency + key tie-break for a stable list under the cap — same
      // rationale as the key picker above.
      .orderBy('created_at', 'DESC')
      .orderBy('property_value', 'ASC')
      .limit(EVENT_PROPERTY_VALUE_AUTOCOMPLETE_LIMIT);

    if (event && event !== '*') {
      query.where('name', '=', event);
    }

    const res = await query.execute();

    values.push(...res.map((e) => e.property_value));
  } else if (property.startsWith('profile.')) {
    const selectExpr = getProfilePropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.profiles, true)
      .where('project_id', '=', projectId)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property.startsWith('group.')) {
    const selectExpr = getGroupPropertySelect(property);
    const query = clix(ch)
      .select<{ values: string }>([`distinct ${selectExpr} as values`])
      .from(TABLE_NAMES.groups, true)
      .where('project_id', '=', projectId)
      .where('deleted', '=', 0)
      .where(selectExpr, '!=', '')
      .where(selectExpr, 'IS NOT NULL', null)
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    const res = await query.execute();
    values.push(...res.map((r) => String(r.values)).filter(Boolean));
  } else if (property === 'cohort' || property.startsWith('cohort:')) {
    // Cohort filters use a dedicated cohort multi-select on the client
    // (ComboboxAdvanced over all cohorts) — values aren't sourced from
    // an event-column distinct query. Without this guard, the events
    // SELECT would emit a literal `cohort:<uuid>` identifier and crash
    // with a ClickHouse syntax error.
    return { values: [] };
  } else {
    // Normalize bare utm_* names to `properties.__query.utm_*` and rewrite
    // camelCase aliases (`referrerName`) to their snake_case columns.
    // Unknown identifiers (saved-report typos like `temple_name`, or
    // misnamed columns from older clients) get an empty value list rather
    // than crashing the autocomplete query with UNKNOWN_IDENTIFIER.
    const resolvedProperty = normalizeEventField(property);
    if (!isKnownEventField(resolvedProperty)) {
      return { values: [] };
    }
    // Session-level columns (geo/device/referrer) come from the small
    // `sessions` table, not a billions-row `events` scan. Only for
    // all-events (`*`): `sessions` has no `name` column to filter on.
    const useSessions =
      event === '*' && SESSION_LEVEL_VALUE_COLUMNS.has(resolvedProperty);
    const query = clix(ch)
      .select<{ values: string[] }>([
        `distinct ${getSelectPropertyKey(resolvedProperty)} as values`,
      ])
      .from(useSessions ? TABLE_NAMES.sessions : TABLE_NAMES.events)
      .where('project_id', '=', projectId)
      .where(
        'created_at',
        '>',
        clix.exp(`now() - INTERVAL ${VALUES_LOOKBACK_DAYS} DAY`),
      )
      .orderBy('created_at', 'DESC')
      .limit(100_000);

    if (!useSessions && event !== '*') {
      query.where('name', '=', event);
    }

    const events = await query.execute();

    values.push(
      ...pipe(
        (data: typeof events) => map(prop('values'), data),
        flatten,
        uniq,
        sort((a, b) => a.length - b.length)
      )(events)
    );
  }

  return {
    values,
  };
})