supabase/apps/studio/components/interfaces/Settings/Logs/Logs.utils.ts

import { useMonaco } from '@monaco-editor/react'
import { IS_PLATFORM } from 'common'
import dayjs, { Dayjs } from 'dayjs'
import { get } from 'lodash'
import uniqBy from 'lodash/uniqBy'
import { useEffect } from 'react'
import logConstants from 'shared-data/log-constants'

import {
  AUTH_LOG_ERROR_CONDITION,
  AUTH_LOG_WARNING_CONDITION,
  LogsTableName,
  SQL_FILTER_TEMPLATES,
  type SqlFilterEntry,
} from './Logs.constants'
import type { Filters, LogData, LogsEndpointParams, QueryType } from './Logs.types'
import { convertResultsToCSV } from '@/components/interfaces/SQLEditor/UtilityPanel/Results.utils'
import BackwardIterator from '@/components/ui/CodeEditor/Providers/BackwardIterator'
import {
  analyticsLiteral,
  joinSqlFragments,
  quotedIdent,
  safeSql,
  type SafeLogSqlFragment,
} from '@/data/logs/safe-analytics-sql'

/**
 * Convert a micro timestamp from number/string to iso timestamp
 */
export const unixMicroToIsoTimestamp = (unix: string | number): string => {
  return dayjs.utc(Number(unix) / 1000).toISOString()
}

export const isUnixMicro = (unix: string | number): boolean => {
  const digitLength = String(unix).length === 16
  const isNum = !Number.isNaN(Number(unix))
  return isNum && digitLength
}

/**
 * Boolean check to verify that there are 3 columns:
 * - id
 * - timestamp
 * - event_message
 */
export const isDefaultLogPreviewFormat = (log: LogData) =>
  log && log.timestamp && log.event_message && log.id

/**
 * Recursively retrieve all nested object key paths.
 *
 * TODO: move to utils
 *
 * @param obj any object
 * @param parent a string representing the parent key
 * @returns string[] all dot paths for keys.
 */
const getDotKeys = (obj: { [k: string]: unknown }, parent?: string): string[] => {
  const keys = Object.keys(obj).filter((k) => obj[k])
  return keys.flatMap((k) => {
    const currKey = parent ? `${parent}.${k}` : k
    if (typeof obj[k] === 'object') {
      return getDotKeys(obj[k] as any, currKey)
    } else {
      return [currKey]
    }
  })
}

// Fallback for filter keys with no template: a plain `key = value`.
// Drops the clause on non-scalar or un-encodable input.
const defaultResolveUnknownClause = (dotKey: string, value: unknown): SafeLogSqlFragment | null => {
  if (typeof value !== 'string' && typeof value !== 'number' && typeof value !== 'boolean') {
    return null
  }
  try {
    return safeSql`${quotedIdent(dotKey)} = ${analyticsLiteral(value)}`
  } catch {
    return null
  }
}

/**
 * Builds the AND-joined WHERE clauses for a table's filters. Top-level keys are
 * ANDed, nested keys under one key are ORed.
 *
 * `filterTemplates` and `resolveUnknownClause` can be passed in so the OTEL
 * builders reuse this with their own ClickHouse conditions. Defaults keep the
 * BigQuery behavior.
 */
export const buildWhereClauses = (
  table: LogsTableName,
  filters: Filters,
  filterTemplates: Record<string, SqlFilterEntry> = SQL_FILTER_TEMPLATES[table],
  resolveUnknownClause: (
    dotKey: string,
    value: unknown
  ) => SafeLogSqlFragment | null = defaultResolveUnknownClause
): SafeLogSqlFragment[] => {
  const keys = Object.keys(filters)
  const _resolveTemplateToStatement = (dotKey: string): SafeLogSqlFragment | null => {
    const template = filterTemplates[dotKey]
    const value = get(filters, dotKey)

    if (template === undefined) {
      return resolveUnknownClause(dotKey, value)
    }

    if (typeof template === 'function') {
      return value !== undefined ? template(value) : null
    }

    // template is SafeLogSqlFragment — emit unless the filter is explicitly disabled
    if (!template || value === false) return null
    return template
  }

  return keys
    .map((rootKey): SafeLogSqlFragment | null => {
      if (
        filters[rootKey] === undefined ||
        (typeof filters[rootKey] === 'string' && (filters[rootKey] as string).length === 0)
      ) {
        return null
      } else if (typeof filters[rootKey] === 'object') {
        // join all statements with an OR
        const nestedStatements = getDotKeys(filters[rootKey] as Filters, rootKey)
          .map(_resolveTemplateToStatement)
          .filter((s) => s !== null)

        if (nestedStatements.length > 0) {
          return safeSql`(${joinSqlFragments(nestedStatements, ' or ')})`
        } else {
          return null
        }
      } else {
        const nestedStatement = _resolveTemplateToStatement(rootKey)
        if (nestedStatement === null) return null
        return safeSql`(${nestedStatement})`
      }
    })
    .filter((s) => s !== null)
}

export const genWhereStatement = (
  table: LogsTableName,
  filters: Filters,
  filterTemplates?: Record<string, SqlFilterEntry>,
  resolveUnknownClause?: (dotKey: string, value: unknown) => SafeLogSqlFragment | null
): SafeLogSqlFragment => {
  const clauses = buildWhereClauses(table, filters, filterTemplates, resolveUnknownClause)
  return clauses.length > 0 ? safeSql`where ${joinSqlFragments(clauses, ' and ')}` : safeSql``
}

export const genDefaultQuery = (
  table: LogsTableName,
  filters: Filters,
  limit: number = 100
): SafeLogSqlFragment => {
  const where = genWhereStatement(table, filters)
  const joins = genCrossJoinUnnests(table)
  const orderBy = safeSql`order by timestamp desc`
  const limitLit = analyticsLiteral(limit)

  switch (table) {
    case 'edge_logs':
      if (!IS_PLATFORM) {
        return safeSql`
-- local dev edge_logs query
select id, edge_logs.timestamp, event_message, request.method, request.path, request.search, response.status_code
from edge_logs
${joins}
${where}
${orderBy}
limit ${limitLit};
`
      }
      return safeSql`select id, identifier, timestamp, event_message, request.method, request.path, request.search, response.status_code
  from ${LOG_TABLE_SQL[table]}
  ${joins}
  ${where}
  ${orderBy}
  limit ${limitLit}
  `

    case 'postgres_logs':
      if (!IS_PLATFORM) {
        return safeSql`
select postgres_logs.timestamp, id, event_message, parsed.error_severity, parsed.detail, parsed.hint
from postgres_logs
${joins}
${where}
${orderBy}
limit ${limitLit}
  `
      }
      return safeSql`select identifier, postgres_logs.timestamp, id, event_message, parsed.error_severity, parsed.detail, parsed.hint from ${LOG_TABLE_SQL[table]}
  ${joins}
  ${where}
  ${orderBy}
  limit ${limitLit}
  `

    case 'function_logs':
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message, metadata.event_type, metadata.function_id, metadata.execution_id, metadata.level from ${LOG_TABLE_SQL[table]}
  ${joins}
  ${where}
  ${orderBy}
  limit ${limitLit}
    `

    case 'auth_logs':
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message, metadata.level, metadata.status, metadata.path, metadata.msg as msg, metadata.error from ${LOG_TABLE_SQL[table]}
  ${joins}
  ${where}
  ${orderBy}
  limit ${limitLit}
    `

    case 'function_edge_logs':
      if (!IS_PLATFORM) {
        return safeSql`
select id, function_edge_logs.timestamp, event_message
from function_edge_logs
${orderBy}
limit ${limitLit}
`
      }
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message, response.status_code, request.method, request.pathname, m.function_id, m.execution_id, m.execution_time_ms, m.deployment_id, m.version from ${LOG_TABLE_SQL[table]}
  ${joins}
  ${where}
  ${orderBy}
  limit ${limitLit}
  `

    case 'supavisor_logs':
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message from ${LOG_TABLE_SQL[table]} ${joins} ${where} ${orderBy} limit ${limitLit}`

    case 'pg_upgrade_logs':
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message from ${LOG_TABLE_SQL[table]} ${joins} ${where} ${orderBy} limit ${analyticsLiteral(100)}`

    case 'pg_cron_logs': {
      const userClauses = buildWhereClauses(table, filters)
      const pgCronWhere =
        userClauses.length > 0
          ? safeSql`where ${basePgCronConditions} AND ${joinSqlFragments(userClauses, ' and ')}`
          : basePgCronWhere
      return safeSql`select id, postgres_logs.timestamp, event_message, parsed.error_severity, parsed.query
from postgres_logs
${joins}
${pgCronWhere}
${orderBy}
limit ${limitLit}
`
    }

    default:
      return safeSql`select id, ${LOG_TABLE_SQL[table]}.timestamp, event_message from ${LOG_TABLE_SQL[table]}
  ${where}
  ${orderBy}
  limit ${limitLit}
  `
  }
}

/**
 * Hardcoded cross join unnests and aliases for each table.
 * Should be used together with the getWhereStatements to allow for filtering on aliases
 */
const genCrossJoinUnnests = (table: LogsTableName): SafeLogSqlFragment => {
  switch (table) {
    case 'edge_logs':
      return safeSql`cross join unnest(metadata) as m
  cross join unnest(m.request) as request
  cross join unnest(m.response) as response`

    case 'pg_cron_logs':
    case 'postgres_logs':
      return safeSql`cross join unnest(metadata) as m
  cross join unnest(m.parsed) as parsed`

    case 'function_logs':
      return safeSql`cross join unnest(metadata) as metadata`

    case 'auth_logs':
      return safeSql`cross join unnest(metadata) as metadata`

    case 'function_edge_logs':
      return safeSql`cross join unnest(metadata) as m
  cross join unnest(m.response) as response
  cross join unnest(m.request) as request`

    case 'supavisor_logs':
      return safeSql`cross join unnest(metadata) as m`

    default:
      return safeSql``
  }
}

/** Brand-safe table name literals for analytics SQL. */
export const LOG_TABLE_SQL: Record<LogsTableName, SafeLogSqlFragment> = {
  [LogsTableName.EDGE]: safeSql`edge_logs`,
  [LogsTableName.POSTGRES]: safeSql`postgres_logs`,
  [LogsTableName.FUNCTIONS]: safeSql`function_logs`,
  [LogsTableName.FN_EDGE]: safeSql`function_edge_logs`,
  [LogsTableName.AUTH]: safeSql`auth_logs`,
  [LogsTableName.AUTH_AUDIT]: safeSql`auth_audit_logs`,
  [LogsTableName.REALTIME]: safeSql`realtime_logs`,
  [LogsTableName.STORAGE]: safeSql`storage_logs`,
  [LogsTableName.POSTGREST]: safeSql`postgrest_logs`,
  [LogsTableName.SUPAVISOR]: safeSql`supavisor_logs`,
  [LogsTableName.PGBOUNCER]: safeSql`pgbouncer_logs`,
  [LogsTableName.PG_UPGRADE]: safeSql`pg_upgrade_logs`,
  [LogsTableName.PG_CRON]: safeSql`pg_cron_logs`,
  [LogsTableName.ETL]: safeSql`etl_replication_logs`,
  [LogsTableName.MULTIGRES]: safeSql`multigres_logs`,
}

/**
 * SQL query to retrieve only one log
 */
export const genSingleLogQuery = (table: LogsTableName, id: string): SafeLogSqlFragment => {
  // multigres logs have no metadata column
  const metadataColumn = table === LogsTableName.MULTIGRES ? safeSql`` : safeSql`, metadata`
  return safeSql`select id, timestamp, event_message${metadataColumn} from ${LOG_TABLE_SQL[table]} where id = ${analyticsLiteral(id)} limit 1`
}

/**
 * Multigres logs store their structured payload as a JSON string in
 * `event_message`. Parse it into a plain object, returning `null` when the
 * value is missing, not valid JSON, or not a plain object (e.g. an array).
 */
export const parseMultigresEventMessage = (
  eventMessage: unknown
): Record<string, unknown> | null => {
  if (typeof eventMessage !== 'string') return null
  try {
    const parsed = JSON.parse(eventMessage)
    return parsed && typeof parsed === 'object' && !Array.isArray(parsed) ? parsed : null
  } catch {
    return null
  }
}

/**
 * Determine if we should show the user an upgrade prompt while browsing logs
 */
export const maybeShowUpgradePromptIfNotEntitled = (
  from: string | null | undefined,
  entitledToDays: number | undefined
) => {
  if (!entitledToDays) return false
  const day = Math.abs(dayjs().diff(dayjs(from), 'day'))
  return day > entitledToDays
}

export const genCountQuery = (table: LogsTableName, filters: Filters): SafeLogSqlFragment => {
  let where = genWhereStatement(table, filters)
  if (table === LogsTableName.PG_CRON) {
    table = LogsTableName.POSTGRES
    where = basePgCronWhere
  }
  const joins = genCrossJoinUnnests(table)
  return safeSql`SELECT count(*) as count FROM ${LOG_TABLE_SQL[table]} ${joins} ${where}`
}

// Falls back to now for missing or unparseable params so an Invalid Date can't
// reach .toISOString() downstream and throw RangeError (Sentry 7580074952).
export const resolveLogTimestamp = (value?: string): Dayjs => {
  const parsed = dayjs(value)
  return value && parsed.isValid() ? parsed : dayjs()
}

/** calculates how much the chart start datetime should be offset given the current datetime filter params */
const calcChartStart = (
  params: Partial<LogsEndpointParams>
): [Dayjs, 'minute' | 'hour' | 'day'] => {
  const ite = resolveLogTimestamp(params.iso_timestamp_end)
  // todo @TzeYiing needs typing
  const its: any = resolveLogTimestamp(params.iso_timestamp_start)

  let trunc: 'minute' | 'hour' | 'day' = 'minute'
  let extendValue = 60 * 6
  const minuteDiff = ite.diff(its, 'minute')
  const hourDiff = ite.diff(its, 'hour')
  if (minuteDiff > 60 * 12) {
    trunc = 'hour'
    extendValue = 24 * 5
  } else if (hourDiff > 24 * 3) {
    trunc = 'day'
    extendValue = 7
  }
  return [its.add(-extendValue, trunc), trunc]
}

// TODO(qiao): workaround for self-hosted cron logs error until logflare is fixed
const basePgCronConditions: SafeLogSqlFragment = IS_PLATFORM
  ? safeSql`( parsed.application_name = 'pg_cron' or regexp_contains(event_message, 'cron job') )`
  : safeSql`( parsed.application_name = 'pg_cron' or event_message::text LIKE '%cron job%' )`
const basePgCronWhere: SafeLogSqlFragment = safeSql`where ${basePgCronConditions}`

const TRUNC_SQL: Record<'minute' | 'hour' | 'day', SafeLogSqlFragment> = {
  minute: safeSql`minute`,
  hour: safeSql`hour`,
  day: safeSql`day`,
}
/**
 *
 * generates log event chart query
 */
export const genChartQuery = (
  table: LogsTableName,
  params: LogsEndpointParams,
  filters: Filters
): SafeLogSqlFragment => {
  const [startOffset, trunc] = calcChartStart(params)
  let where = genWhereStatement(table, filters)
  const errorCondition = getErrorCondition(table)
  const warningCondition = getWarningCondition(table)

  // pg_cron logs are a subset of postgres logs
  // to calculate the chart, we need to query postgres logs
  if (table === LogsTableName.PG_CRON) {
    table = LogsTableName.POSTGRES
    where = basePgCronWhere
  }

  const joins = genCrossJoinUnnests(table)
  const tsLit = analyticsLiteral(startOffset.toISOString())
  const whereFragment: SafeLogSqlFragment = where
    ? safeSql`${where} and t.timestamp > ${tsLit}`
    : safeSql`where t.timestamp > ${tsLit}`

  return safeSql`
SELECT
-- log-event-chart
  timestamp_trunc(t.timestamp, ${TRUNC_SQL[trunc]}) as timestamp,
  count(CASE WHEN NOT (${errorCondition} OR ${warningCondition}) THEN 1 END) as ok_count,
  count(CASE WHEN ${errorCondition} THEN 1 END) as error_count,
  count(CASE WHEN ${warningCondition} THEN 1 END) as warning_count,
FROM
  ${LOG_TABLE_SQL[table]} t
  ${joins}
  ${whereFragment}
GROUP BY
timestamp
ORDER BY
  timestamp ASC
  `
}

type TsPair = [string | '', string | '']
export const ensureNoTimestampConflict = (
  [initialStart, initialEnd]: TsPair,
  [nextStart, nextEnd]: TsPair
): TsPair => {
  if (initialStart && initialEnd && nextEnd && !nextStart) {
    const end = resolveLogTimestamp(nextEnd)
    let start = resolveLogTimestamp(initialStart)

    if (end.diff(start) <= 0) {
      const currDiff = Math.abs(resolveLogTimestamp(initialEnd).diff(start, 'minute'))
      start = end.subtract(currDiff, 'minute')
    }
    return [start.toISOString(), nextEnd]
  } else if (!nextEnd && nextStart) {
    return [nextStart, initialEnd]
  } else {
    return [nextStart, nextEnd]
  }
}

/**
 * Adds SQL code hints to logs explorer code editor
 */
export const useEditorHints = () => {
  const monaco = useMonaco()

  useEffect(() => {
    if (monaco) {
      const competionProvider = {
        triggerCharacters: ['`', ' ', '.'],
        provideCompletionItems: function (model: any, position: any, context: any) {
          let iterator = new BackwardIterator(model, position.column - 2, position.lineNumber - 1)
          if (iterator.isNextDQuote()) return { suggestions: [] }
          let suggestions: { label: string; kind: any; insertText: string }[] = []

          let schemasInUse = logConstants.schemas.filter((schema) =>
            iterator._text.includes(schema.reference)
          )
          if (schemasInUse.length === 0) {
            schemasInUse = logConstants.schemas
          }

          if (iterator.isNextPeriod()) {
            // should be nested key reference, suggest all tail endings of available fields
            const fields = schemasInUse.flatMap((schema) => schema.fields)
            const trailingKeys = fields.flatMap((field) => {
              const [_head, ...rest] = field.path.split('.')
              return rest
            })

            const trailingToAdd = trailingKeys.map((key) => ({
              label: key,
              kind: monaco.languages.CompletionItemKind.Property,
              insertText: key,
            }))
            suggestions = suggestions.concat(trailingToAdd)
          }

          if (context.triggerCharacter === '`' || context.triggerCharacter === ' ') {
            // should be reference or start of key
            const referencesToAdd = logConstants.schemas.map((schema) => ({
              label: schema.reference,
              kind: monaco.languages.CompletionItemKind.Class,
              insertText: schema.reference,
            }))

            const fields = schemasInUse.flatMap((schema) => schema.fields)
            const leadingKeys = fields.flatMap((field) => {
              const splitPath = field.path.split('.')

              return splitPath.slice(0, -1)
            })

            const leadingToAdd = leadingKeys.map((key) => ({
              label: key,
              kind: monaco.languages.CompletionItemKind.Property,
              insertText: key,
            }))
            suggestions = suggestions.concat(leadingToAdd)
            suggestions = suggestions.concat(referencesToAdd)
          }
          return {
            suggestions: uniqBy(suggestions, 'label'),
          }
        },
      } as any

      // register completion item provider for pgsql
      const completeProvider = monaco.languages.registerCompletionItemProvider(
        'pgsql',
        competionProvider
      )

      return () => {
        completeProvider.dispose()
      }
    }
  }, [monaco])
}

/**
 * Assumes that all timestamps are in ISO-8601 UTC timezone.
 *
 * min/max are the datetime strings that extend beyond the given timeseries data.
 */
export const fillTimeseries = (
  timeseriesData: any[],
  timestampKey: string,
  valueKey: string | string[],
  defaultValue: number,
  min?: string,
  max?: string,
  minPointsToFill: number = 20,
  interval?: string
) => {
  if (timeseriesData.length === 0 && !(min && max)) {
    return []
  }
  // If we have more points than minPointsToFill, just normalize timestamps and return
  if (timeseriesData.length > minPointsToFill) {
    return timeseriesData.map((datum) => {
      const timestamp = datum[timestampKey]
      const iso = isUnixMicro(timestamp)
        ? unixMicroToIsoTimestamp(timestamp)
        : dayjs.utc(timestamp).toISOString()
      datum[timestampKey] = iso
      return datum
    })
  }

  if (timeseriesData.length <= 1 && !(min && max)) return timeseriesData
  const dates: unknown[] = timeseriesData.map((datum) => dayjs.utc(datum[timestampKey]))

  const maxDate = max ? dayjs.utc(max) : dayjs.utc(Math.max.apply(null, dates as number[]))
  const minDate = min ? dayjs.utc(min) : dayjs.utc(Math.min.apply(null, dates as number[]))

  // When no data exists but min/max are provided, we need to determine truncation from the time range
  const truncationSamples = timeseriesData.length > 0 ? dates : [minDate, maxDate]
  let truncation: 'second' | 'minute' | 'hour' | 'day'
  let step = 1

  if (interval) {
    const match = interval.match(/^(\d+)(m|h|d|s)$/)
    if (match) {
      step = parseInt(match[1], 10)
      const unitChar = match[2] as 'm' | 'h' | 'd' | 's'
      const unitMap = { s: 'second', m: 'minute', h: 'hour', d: 'day' } as const
      truncation = unitMap[unitChar]
    } else {
      // Fallback for invalid format
      truncation = getTimestampTruncation(truncationSamples as Dayjs[])
    }
  } else {
    truncation = getTimestampTruncation(truncationSamples as Dayjs[])
  }

  // If no data exists and no interval specified, default to minute precision
  if (timeseriesData.length === 0 && !interval) {
    truncation = 'minute'
  }

  const newData = timeseriesData.map((datum) => {
    const timestamp = datum[timestampKey]
    const iso = isUnixMicro(timestamp)
      ? unixMicroToIsoTimestamp(timestamp)
      : dayjs.utc(timestamp).toISOString()

    if (Array.isArray(valueKey) && valueKey.length === 0) {
      return { [timestampKey]: iso }
    }

    datum[timestampKey] = iso
    return datum
  })

  let currentDate = minDate
  while (currentDate.isBefore(maxDate) || currentDate.isSame(maxDate)) {
    const found = dates.find((d) => {
      const d_date = d as Dayjs
      return (
        d_date.year() === currentDate.year() &&
        d_date.month() === currentDate.month() &&
        d_date.date() === currentDate.date() &&
        d_date.hour() === currentDate.hour() &&
        d_date.minute() === currentDate.minute() &&
        d_date.second() === currentDate.second()
      )
    })
    if (!found) {
      const keys = typeof valueKey === 'string' ? [valueKey] : valueKey

      const toMerge = keys.reduce(
        (acc, key) => ({
          ...acc,
          [key]: defaultValue,
        }),
        {}
      )
      newData.push({
        [timestampKey]: currentDate.toISOString(),
        ...toMerge,
      })
    }
    currentDate = currentDate.add(step, truncation)
  }

  return newData
}

const getTimestampTruncation = (samples: Dayjs[]): 'second' | 'minute' | 'hour' | 'day' => {
  const truncationCounts = samples.reduce(
    (acc, sample) => {
      const truncation = _getTruncation(sample)
      acc[truncation] += 1

      return acc
    },
    {
      second: 0,
      minute: 0,
      hour: 0,
      day: 0,
    }
  )

  const mostLikelyTruncation = (
    Object.keys(truncationCounts) as (keyof typeof truncationCounts)[]
  ).reduce((a, b) => (truncationCounts[a] > truncationCounts[b] ? a : b))
  return mostLikelyTruncation
}

const _getTruncation = (date: Dayjs) => {
  const values = ['second', 'minute', 'hour'].map((key) => date.get(key as dayjs.UnitType))
  const zeroCount = values.reduce((acc, value) => {
    if (value === 0) {
      acc += 1
    }
    return acc
  }, 0)
  const truncation = {
    0: 'second' as const,
    1: 'minute' as const,
    2: 'hour' as const,
    3: 'day' as const,
  }[zeroCount]!
  return truncation
}

export function checkForWithClause(query: string) {
  const queryWithoutComments = query.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//gm, '')

  const withClauseRegex = /\b(WITH)\b(?=(?:[^']*'[^']*')*[^']*$)/i
  return withClauseRegex.test(queryWithoutComments)
}

export function checkForILIKEClause(query: string) {
  const queryWithoutComments = query.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//gm, '')

  const ilikeClauseRegex = /\b(ILIKE)\b(?=(?:[^']*'[^']*')*[^']*$)/i
  return ilikeClauseRegex.test(queryWithoutComments)
}

export function checkForLimitClause(query: string) {
  const queryWithoutComments = query.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//gm, '')

  const limitClauseRegex = /\b(LIMIT)\b\s+\d+(?=(?:[^']*'[^']*')*[^']*$)/i
  return limitClauseRegex.test(queryWithoutComments)
}

export function checkForWildcard(query: string) {
  const queryWithoutComments = query.replace(/--.*$/gm, '').replace(/\/\*[\s\S]*?\*\//gm, '')

  const queryWithoutCount = queryWithoutComments.replace(/count\(\*\)/gi, '')

  const wildcardRegex = /\*/
  return wildcardRegex.test(queryWithoutCount)
}

function getErrorCondition(table: LogsTableName): SafeLogSqlFragment {
  switch (table) {
    case 'edge_logs':
      return safeSql`response.status_code >= 500`
    case 'postgres_logs':
      return safeSql`parsed.error_severity IN ('ERROR', 'FATAL', 'PANIC')`
    case 'auth_logs':
      return AUTH_LOG_ERROR_CONDITION
    case 'function_edge_logs':
      return safeSql`response.status_code >= 500`
    case 'function_logs':
      return safeSql`metadata.level IN ('error', 'fatal')`
    case 'pg_cron_logs':
      return safeSql`parsed.error_severity IN ('ERROR', 'FATAL', 'PANIC')`
    case 'multigres_logs':
      return safeSql`JSON_VALUE(event_message, '$.level') IN ('ERROR', 'FATAL', 'PANIC')`
    default:
      return safeSql`false`
  }
}

function getWarningCondition(table: LogsTableName): SafeLogSqlFragment {
  switch (table) {
    case 'edge_logs':
      return safeSql`response.status_code >= 400 AND response.status_code < 500`
    case 'postgres_logs':
      return safeSql`parsed.error_severity IN ('WARNING')`
    case 'auth_logs':
      return AUTH_LOG_WARNING_CONDITION
    case 'function_edge_logs':
      return safeSql`response.status_code >= 400 AND response.status_code < 500`
    case 'function_logs':
      return safeSql`metadata.level IN ('warning')`
    case 'multigres_logs':
      return safeSql`JSON_VALUE(event_message, '$.level') IN ('WARN', 'WARNING')`
    default:
      return safeSql`false`
  }
}

export const HTTP_SERVER_ERROR_STATUS = 500
export const HTTP_CLIENT_ERROR_STATUS = 400

/**
 * Derives the severity badge shown in the auth log table from the log level and
 * HTTP status, matching the chart: 5xx → error, 4xx → warning, otherwise the
 * log level. See the AUTH_LOG_*_CONDITION constants in Logs.constants.ts for the
 * matching SQL and the reason auth logs need this.
 */
export function getAuthLogSeverity(level?: unknown, status?: unknown): string {
  const normalizedLevel = typeof level === 'string' ? level : ''

  // Preserve explicit error-class levels so we never downgrade them and so the
  // original label (e.g. "fatal") is kept.
  if (normalizedLevel === 'error' || normalizedLevel === 'fatal') return normalizedLevel

  const statusCode =
    typeof status === 'number' ? status : typeof status === 'string' ? Number(status) : NaN
  const hasStatus = Number.isFinite(statusCode)

  if (hasStatus && statusCode >= HTTP_SERVER_ERROR_STATUS) return 'error'
  if (normalizedLevel === 'warning') return 'warning'
  if (hasStatus && statusCode >= HTTP_CLIENT_ERROR_STATUS) return 'warning'

  return normalizedLevel
}

export function jwtAPIKey(metadata: any) {
  const apikeyHeader = metadata?.[0]?.request?.[0]?.sb?.[0]?.jwt?.[0]?.apikey?.[0]
  if (!apikeyHeader) {
    return undefined
  }

  if (apikeyHeader.invalid) {
    return '<invalid>'
  }

  const payload = apikeyHeader?.payload?.[0]
  if (!payload) {
    return '<unrecognized>'
  }

  if (
    payload.algorithm === 'HS256' &&
    payload.issuer === 'supabase' &&
    ['anon', 'service_role'].includes(payload.role) &&
    !payload.subject
  ) {
    return payload.role
  }

  return '<unrecognized>'
}

export function apiKey(metadata: any) {
  const apikeyHeader = metadata?.[0]?.request?.[0]?.sb?.[0]?.apikey?.[0]?.apikey?.[0]
  if (!apikeyHeader) {
    return undefined
  }

  if (apikeyHeader.error) {
    return `${apikeyHeader.prefix}... <invalid: ${apikeyHeader.error}>`
  }

  return `${apikeyHeader.prefix}...`
}

export function role(metadata: any) {
  const authorizationHeader = metadata?.[0]?.request?.[0]?.sb?.[0]?.jwt?.[0]?.authorization?.[0]
  if (!authorizationHeader) {
    return undefined
  }

  if (authorizationHeader.invalid) {
    return undefined
  }

  const payload = authorizationHeader?.payload?.[0]
  if (!payload || !payload.role) {
    return undefined
  }

  return payload.role
}

export function formatLogsAsJson(rows: LogData[]): string {
  return JSON.stringify(rows, null, 2)
}

export function formatLogsAsCsv(rows: LogData[]): string {
  return convertResultsToCSV(rows as unknown as Record<string, unknown>[]) ?? ''
}

export function formatLogsAsMarkdown(rows: LogData[]): string {
  return rows
    .map((row, i) => {
      const lines: string[] = [`## Log ${i + 1}`]
      if (row.timestamp) {
        const numTs = Number(row.timestamp)
        let tsString: string
        if (isFinite(numTs)) {
          tsString = new Date(numTs / 1000).toISOString()
        } else if (typeof row.timestamp === 'string') {
          const d = new Date(row.timestamp)
          tsString = isNaN(d.getTime()) ? row.timestamp : d.toISOString()
        } else {
          tsString = String(row.timestamp)
        }
        lines.push(`**Timestamp:** ${tsString}`)
      }
      if (row.event_message) {
        lines.push(`**Message:** ${row.event_message}`)
      }
      const { id: _id, timestamp: _ts, event_message: _msg, ...rest } = row as any
      if (Object.keys(rest).length > 0) {
        lines.push('', '**Details:**', '```json', JSON.stringify(rest, null, 2), '```')
      }
      return lines.join('\n')
    })
    .join('\n\n---\n\n')
}

const QUERY_TYPE_LABELS: Record<QueryType, string> = {
  api: 'API Gateway (Edge Network)',
  database: 'Postgres Database',
  functions: 'Edge Functions',
  fn_edge: 'Edge Functions (edge runtime)',
  auth: 'Auth',
  realtime: 'Realtime',
  storage: 'Storage',
  supavisor: 'Supavisor (connection pooling)',
  postgrest: 'PostgREST',
  pg_upgrade: 'Postgres upgrade',
  pg_cron: 'pg_cron',
  pgbouncer: 'PgBouncer',
  etl: 'ETL',
  multigres: 'Multigres',
}

const LOG_TABLE_TO_SERVICE_LABEL: Record<LogsTableName, string> = {
  edge_logs: 'API Gateway (Edge Network)',
  postgres_logs: 'Postgres Database',
  function_logs: 'Edge Functions',
  function_edge_logs: 'Edge Functions (edge runtime)',
  auth_logs: 'Auth',
  auth_audit_logs: 'Auth (audit)',
  realtime_logs: 'Realtime',
  storage_logs: 'Storage',
  postgrest_logs: 'PostgREST',
  supavisor_logs: 'Supavisor (connection pooling)',
  pgbouncer_logs: 'PgBouncer',
  pg_upgrade_logs: 'Postgres upgrade',
  pg_cron_logs: 'pg_cron',
  etl_replication_logs: 'ETL',
  multigres_logs: 'Multigres',
}

const isLogsTableName = (value: string): value is LogsTableName =>
  value in LOG_TABLE_TO_SERVICE_LABEL
const isQueryType = (value: string): value is QueryType => value in QUERY_TYPE_LABELS

export function extractEdgeFunctionName(pathname: unknown): string {
  if (typeof pathname !== 'string' || !pathname) return ''
  const parts = pathname.split('/').filter(Boolean)
  return parts[parts.length - 1] ?? ''
}

function extractServiceLabelFromSql(sql: string): string | null {
  const match = sql.match(/\bfrom\s+(\w+)/i)
  const tableName = match?.[1]
  return tableName && isLogsTableName(tableName) ? LOG_TABLE_TO_SERVICE_LABEL[tableName] : null
}

export function buildLogsPrompt(rows: LogData[], queryType?: string, sqlQuery?: string): string {
  const serviceLabel =
    (queryType && isQueryType(queryType) ? QUERY_TYPE_LABELS[queryType] : null) ??
    (sqlQuery ? extractServiceLabelFromSql(sqlQuery) : null)
  const serviceContext = serviceLabel ? ` from the **${serviceLabel}** service` : ''
  const sqlContext = sqlQuery ? `\n\n**Query used:**\n\`\`\`sql\n${sqlQuery.trim()}\n\`\`\`` : ''
  const header = `I have ${rows.length} Supabase log entr${rows.length === 1 ? 'y' : 'ies'}${serviceContext} I'd like help debugging:\n\n`
  const body = formatLogsAsMarkdown(rows)
  return (
    header +
    body +
    sqlContext +
    '\n\nWhat do these logs indicate? What steps can I take to resolve it? Keep your answer very concise and actionable. Max 2 or 3 bullet points.'
  )
}

/**
 * Computes the next multi-select set after a shift-click on `targetKey`, extending
 * the selection from `anchorKey` (the last row the user clicked). Every key between
 * anchor and target (inclusive, in `orderedKeys` order) is added. If the whole range is
 * already selected, the range is removed instead. Falls back to a plain toggle of
 * `targetKey` when there is no usable anchor (null, or no longer in `orderedKeys`).
 */
export function getShiftClickSelection({
  orderedKeys,
  selectedKeys,
  anchorKey,
  targetKey,
}: {
  orderedKeys: string[]
  selectedKeys: Set<string>
  anchorKey: string | null
  targetKey: string
}): Set<string> {
  const next = new Set(selectedKeys)

  const anchorIndex = anchorKey === null ? -1 : orderedKeys.indexOf(anchorKey)
  const targetIndex = orderedKeys.indexOf(targetKey)
  const hasUsableAnchor = anchorIndex !== -1 && targetIndex !== -1

  if (!hasUsableAnchor) {
    if (next.has(targetKey)) {
      next.delete(targetKey)
    } else {
      next.add(targetKey)
    }
    return next
  }

  const startIndex = Math.min(anchorIndex, targetIndex)
  const endIndex = Math.max(anchorIndex, targetIndex)
  const rangeKeys = orderedKeys.slice(startIndex, endIndex + 1)
  const isRangeFullySelected = rangeKeys.every((key) => selectedKeys.has(key))

  rangeKeys.forEach((key) => {
    if (isRangeFullySelected) {
      next.delete(key)
    } else {
      next.add(key)
    }
  })

  return next
}