One Interface.
Five Databases.
Infinite Possibilities.

using System.Data.Common;

namespace DatabaseGpt.Abstractions;

public interface IDatabaseGptProvider : IDisposable
{
    /// <summary>The display name of the database (e.g., "SQL Server", "MySQL")</summary>
    string Name { get; }

    /// <summary>The SQL dialect used (e.g., "T-SQL", "PL/pgSQL")</summary>
    string Language { get; }

    /// <summary>Returns provider-specific query optimization hints</summary>
    Task<string?> GetQueryHintsAsync(CancellationToken cancellationToken = default);

    /// <summary>Retrieves available tables from the database schema</summary>
    Task<IEnumerable<string>> GetTablesAsync(
        IEnumerable<string> includedTables, 
        IEnumerable<string> excludedTables, 
        CancellationToken cancellationToken = default);

    /// <summary>Generates CREATE TABLE scripts for AI context</summary>
    Task<string> GetCreateTablesScriptAsync(
        IEnumerable<string> tables, 
        IEnumerable<string> excludedColumns, 
        CancellationToken cancellationToken = default);

    /// <summary>Normalizes and validates the generated query</summary>
    Task<string> NormalizeQueryAsync(
        string query, 
        CancellationToken cancellationToken = default);

    /// <summary>Executes the query and returns a data reader</summary>
    Task<DbDataReader> ExecuteQueryAsync(
        string query, 
        CancellationToken cancellationToken = default);
}

// Configure DatabaseGPT with SQL Server provider
builder.Services.AddDatabaseGpt(
    databaseGpt =>
    {
        // Use fluent extension method for SQL Server
        databaseGpt.UseSqlServer(connectionString);
        
        // Optional: Restrict tables visible to AI
        databaseGpt.IncludedTables = ["dbo.Customers", "dbo.Orders", "dbo.Products"];
        
        // Optional: Hide sensitive columns
        databaseGpt.ExcludedColumns = ["SSN", "CreditCardNumber", "Password"];
        
        // Optional: Add business context for AI
        databaseGpt.SystemMessage = @"
            This is an e-commerce database. 
            Orders are linked to Customers via CustomerId.
            Products have a CategoryId foreign key.";
        
        // Configure retry attempts for failed queries
        databaseGpt.MaxRetries = 3;
    },
    chatGpt =>
    {
        chatGpt.UseOpenAI(apiKey);
        chatGpt.DefaultModel = "gpt-4";
    }
);

// MySQL Configuration
databaseGpt.UseMySql("Server=localhost;Database=shop;User=root;Password=***;");

// PostgreSQL Configuration
databaseGpt.UseNpgsql("Host=localhost;Database=shop;Username=postgres;Password=***;");

// Oracle Configuration
databaseGpt.UseOracle("Data Source=//localhost:1521/XE;User Id=system;Password=***;");

// SQLite Configuration
databaseGpt.UseSqlite("Data Source=shop.db");

// Dynamic provider selection at runtime
switch (databaseType)
{
    case "SqlServer": databaseGpt.UseSqlServer(conn); break;
    case "MySql":     databaseGpt.UseMySql(conn);     break;
    case "Postgres":  databaseGpt.UseNpgsql(conn);    break;
    case "Oracle":    databaseGpt.UseOracle(conn);    break;
    case "Sqlite":    databaseGpt.UseSqlite(conn);    break;
}

// 1. Register the service
builder.Services.AddDatabaseGpt(
    db => db.UseSqlServer(connectionString),
    gpt => gpt.UseOpenAI(apiKey)
);

// 2. Inject the client
public class QueryController(IDatabaseGptClient client)
{
    // 3. Ask questions in natural language!
    public async Task<IActionResult> Query(string question)
    {
        var result = await client.ExecuteNaturalLanguageQueryAsync(
            Guid.NewGuid(),  // Session ID for conversation context
            question
        );
        
        // result.Query = Generated SQL
        // result.Reader = Data results
        
        return Ok(result);
    }
}