# `Lotus.AI.Prompts.SQLGeneration`
[🔗](https://github.com/typhoonworks/lotus/blob/v0.16.6/lib/lotus/ai/prompts/sql_generation.ex#L1)

System prompts for SQL query generation.

Prompts focus on:
- Clear instructions for SQL generation
- Handling non-SQL questions gracefully
- Database-specific considerations
- Security guidelines (read-only, limits, etc.)

# `extract_response`

```elixir
@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: [...]}}

# `extract_sql`

```elixir
@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"}}

# `extract_variables`

```elixir
@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")
    []

# `system_prompt`

```elixir
@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..."

---

*Consult [api-reference.md](api-reference.md) for complete listing*