@spec extract_response(String.t()) ::
  {:ok, %{sql: String.t(), variables: [map()]}}
  | {:error, {:unable_to_generate, String.t()}}

Extract both SQL and variables from LLM response content.

Combines extract_sql/1 and extract_variables/1 into a single call. This is the primary extraction entry point for response parsing.

Parameters

• content - Raw response content from the LLM

Returns

• {:ok, %{sql: String.t(), variables: [map()]}} - Successfully extracted
• {:error, {:unable_to_generate, reason}} - LLM refused to generate

Examples

iex> extract_response("```sql\nSELECT * FROM users WHERE status = {{status}}\n```\n```variables\n[{\"name\": \"status\"}]\n```")
{:ok, %{sql: "SELECT * FROM users WHERE status = {{status}}", variables: [...]}}

@spec extract_sql(String.t()) ::
  {:ok, String.t()} | {:error, {:unable_to_generate, String.t()}}

Extract SQL from LLM response content.

Handles both markdown-wrapped SQL and plain SQL responses, and detects when the LLM has refused to generate SQL.

Parameters

• content - Raw response content from the LLM

Returns

• {:ok, sql} - Successfully extracted SQL query
• {:error, {:unable_to_generate, reason}} - LLM refused to generate

Examples

iex> extract_sql("```sql\nSELECT * FROM users\n```")
{:ok, "SELECT * FROM users"}

iex> extract_sql("UNABLE_TO_GENERATE: This is a weather question")
{:error, {:unable_to_generate, "This is a weather question"}}

@spec extract_variables(String.t()) :: [map()]

Extract variable configurations from LLM response content.

Parses a ```variables JSON block from the response. Returns an empty list if no block is found or if the JSON is malformed.

Parameters

• content - Raw response content from the LLM

Returns

List of variable configuration maps (empty list if none found).

Examples

iex> extract_variables("```variables\n[{\"name\": \"status\", \"type\": \"text\"}]\n```")
[%{"name" => "status", "type" => "text", "widget" => "input", "list" => false}]

iex> extract_variables("Just some SQL without variables")
[]

@spec system_prompt(atom(), [String.t()], keyword()) :: String.t()

Generate system prompt for SQL query generation.

Creates a comprehensive prompt instructing the LLM to generate SQL queries based on available database tables, with clear guidelines for handling non-SQL questions and security best practices.

Parameters

• database_type - Database type (e.g., :postgres, :mysql, :sqlite)
• table_names - List of available table names in the database
• opts - Optional keyword list:
  • :read_only - When true (default), instructs the LLM to only generate read-only queries. When false, allows write queries (INSERT, UPDATE, DELETE, DDL).

Returns

String containing the complete system prompt.

Example

iex> SQLGeneration.system_prompt(:postgres, ["users", "posts"])
"You are a specialized SQL query generator for postgres databases..."