From SQLite Proof-of-Concept to
Universal Database Mastery

public class SqliteDatabaseGptProvider(SqliteDatabaseGptProviderConfiguration settings) 
    : IDatabaseGptProvider
{
    private readonly SqliteConnection connection = new(settings.ConnectionString);

    public string Name => "SQLite";
    public string Language => "SQLite";

    // The elegance of simplicity: SQLite uses PRAGMA for schema discovery
    public async Task<IEnumerable<string>> GetTablesAsync(...)
    {
        var tables = await connection.QueryAsync<string>(@"
            SELECT TBL_NAME AS Tables
            FROM sqlite_schema
            WHERE type IN ('table','view') 
            AND name NOT LIKE 'sqlite_%'");
        
        return tables;
    }

    // PRAGMA_TABLE_INFO: SQLite's gift for schema introspection
    public async Task<string> GetCreateTablesScriptAsync(...)
    {
        var query = @"
            SELECT '[' || NAME || '] ' ||
                UPPER(TYPE) || ' ' ||
                CASE WHEN [NOTNULL] = 0 THEN 'NULL' ELSE 'NOT NULL' END
            FROM PRAGMA_TABLE_INFO(@table)";
        
        // Build CREATE TABLE script for AI context...
    }
}

namespace DatabaseGpt.Abstractions;

/// <summary>
/// The contract that enables universal database support.
/// Every database provider must implement these 6 methods.
/// </summary>
public interface IDatabaseGptProvider : IDisposable
{
    // Identity - What database is this?
    string Name { get; }      // e.g., "SQL Server", "PostgreSQL"
    string Language { get; }  // e.g., "T-SQL", "PL/pgSQL"

    // Schema Discovery - What tables exist?
    Task<IEnumerable<string>> GetTablesAsync(
        IEnumerable<string> includedTables,
        IEnumerable<string> excludedTables,
        CancellationToken cancellationToken);

    // Schema Context - Build CREATE TABLE scripts for AI
    Task<string> GetCreateTablesScriptAsync(
        IEnumerable<string> tables,
        IEnumerable<string> excludedColumns,
        CancellationToken cancellationToken);

    // Query Hints - Database-specific optimization tips
    Task<string?> GetQueryHintsAsync(CancellationToken cancellationToken);

    // Normalization - Pre-process queries before execution
    Task<string> NormalizeQueryAsync(string query, CancellationToken cancellationToken);

    // Execution - Run the generated SQL
    Task<DbDataReader> ExecuteQueryAsync(string query, CancellationToken cancellationToken);
}

[McpServerToolType]
public sealed class QueryExecutionTool
{
    private readonly SessionManager _sessionManager;
    private readonly QueryExecutionService _queryExecutionService;
    private readonly AuditLogger _auditLogger;

    [McpServerTool(Name = "ExecuteQueryAsync")]
    [Description("Execute a natural language query against a database")]
    public async Task<string> ExecuteQueryAsync(
        [Description("Session token from authenticate()")] string sessionToken,
        [Description("Database connection ID")] long connectionId,
        [Description("Natural language query")] string query,
        [Description("Include hints in context")] bool includeHints = true)
    {
        // Validate session
        var (isValid, userId, _) = _sessionManager.ValidateToken(sessionToken);
        if (!isValid)
            return JsonSerializer.Serialize(new { success = false, error = "Invalid token" });

        // Execute query through DatabaseGPT infrastructure
        var (sql, results) = await _queryExecutionService
            .ExecuteNaturalLanguageQueryAsync(connection, query, includeHints);

        // Log audit trail
        await _auditLogger.LogQueryAsync(userId, connectionId, query, sql, ...);

        return JsonSerializer.Serialize(new {
            success = true,
            generated_sql = sql,
            results = results,
            row_count = results.Count
        });
    }
}