sql-classify.ts

// altimate_change - SQL query classifier for write detection
//
// Uses altimate-core's AST-based getStatementTypes() for accurate classification.
// Handles CTEs, string literals, procedural blocks, all dialects correctly.
// Falls back to regex-based heuristics if the napi binary fails to load.

// Safe import: napi binary may not be available on all platforms
let getStatementTypes: ((sql: string, dialect?: string | null) => any) | null = null
try {
  // eslint-disable-next-line @typescript-eslint/no-require-imports
  const core = require("@altimateai/altimate-core")
  if (typeof core?.getStatementTypes === "function") {
    getStatementTypes = core.getStatementTypes
  }
} catch {
  // napi binary failed to load — will use regex fallback
}

// Only SELECT queries are known safe. "other" (SHOW, SET, USE, etc.) is ambiguous — prompt for permission.
const READ_CATEGORIES = new Set(["query"])

// Hard-deny patterns — blocked regardless of permissions
const HARD_DENY_TYPES = new Set(["DROP DATABASE", "DROP SCHEMA", "TRUNCATE", "TRUNCATE TABLE"])

// Regex fallback: patterns that indicate write operations (case-insensitive, anchored to statement start)
const WRITE_PATTERN =
  /^\s*(INSERT|UPDATE|DELETE|MERGE|CREATE|ALTER|DROP|TRUNCATE|GRANT|REVOKE|CALL|EXEC)\b/i
const HARD_DENY_PATTERN =
  /^\s*(DROP\s+(DATABASE|SCHEMA)\b|TRUNCATE(\s+TABLE)?\s)/i

/**
 * Regex-based fallback classifier for when altimate-core is unavailable.
 * Conservative: treats anything not clearly a SELECT/WITH/SHOW/EXPLAIN as "write".
 */
function classifyFallback(sql: string): { queryType: "read" | "write"; blocked: boolean } {
  const trimmed = sql.replace(/\/\*[\s\S]*?\*\//g, "").trim()
  const blocked = HARD_DENY_PATTERN.test(trimmed)
  const queryType = WRITE_PATTERN.test(trimmed) ? "write" : "read"
  return { queryType, blocked }
}

/**
 * Classify a SQL string as "read" or "write" using AST parsing.
 * If ANY statement is a write, returns "write".
 */
export function classify(sql: string): "read" | "write" {
  if (!sql || typeof sql !== "string") return "read"
  if (!getStatementTypes) return classifyFallback(sql).queryType
  try {
    const result = getStatementTypes(sql)
    if (!result?.categories?.length) return "read"
    return result.categories.some((c: string) => !READ_CATEGORIES.has(c)) ? "write" : "read"
  } catch {
    return classifyFallback(sql).queryType
  }
}

/**
 * Classify a multi-statement SQL string.
 * getStatementTypes handles multi-statement natively — no semicolon splitting needed.
 */
export function classifyMulti(sql: string): "read" | "write" {
  return classify(sql)
}

/**
 * Single-pass: classify and check for hard-denied statement types.
 * Returns both the overall query type and whether a hard-deny pattern was found.
 */
export function classifyAndCheck(sql: string): { queryType: "read" | "write"; blocked: boolean } {
  if (!sql || typeof sql !== "string") return { queryType: "read", blocked: false }
  if (!getStatementTypes) return classifyFallback(sql)
  try {
    const result = getStatementTypes(sql)
    if (!result?.statements?.length) return { queryType: "read", blocked: false }

    const blocked = result.statements.some(
      (s: { statement_type: string }) =>
        s.statement_type && HARD_DENY_TYPES.has(s.statement_type.toUpperCase()),
    )

    const categories = result.categories ?? []
    const queryType = categories.some((c: string) => !READ_CATEGORIES.has(c)) ? "write" : "read"
    return { queryType: queryType as "read" | "write", blocked }
  } catch {
    return classifyFallback(sql)
  }
}