using Microsoft.Data.Sqlite;
using NewLife.Studio.Core.DTOs;
using NewLife.Studio.Data.Providers.SQLite;
using Xunit;

namespace NewLife.Studio.Data.Tests;

public class SQLiteSessionTests : IDisposable
{
    private readonly SqliteConnection _setupConnection;

    public SQLiteSessionTests()
    {
        // shared in-memory DB: tables created via _setupConnection
        // are visible to SQLiteSession connections opened with the same shared URI
        _setupConnection = new SqliteConnection("Data Source=file::memory:?cache=shared");
        _setupConnection.Open();
    }

    public void Dispose()
    {
        _setupConnection?.Dispose();
    }

    private static ConnectionInfo CreateInMemoryConnection() => new()
    {
        Name = "test-session",
        ConnectionString = "Data Source=file::memory:?cache=shared",
        ProviderType = "sqlite"
    };

    private void ExecuteSql(string sql)
    {
        using var cmd = _setupConnection.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
    }

    [Fact]
    public async Task GetTablesAsync_WithCreatedTable_ReturnsTable()
    {
        ExecuteSql("CREATE TABLE test_users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var tables = await session.GetTablesAsync();

        Assert.NotEmpty(tables);
        Assert.Contains(tables, t => t.Name == "test_users" && t.Schema == "main");
    }

    [Fact]
    public async Task GetTablesAsync_WhenEmpty_Database_ReturnsNoTables()
    {
        // Use a private in-memory connection (not shared) so no tables exist
        var conn = new ConnectionInfo
        {
            Name = "test-empty",
            ConnectionString = "Data Source=:memory:",
            ProviderType = "sqlite"
        };
        var session = new SQLiteSession(conn);

        var tables = await session.GetTablesAsync();

        Assert.Empty(tables);
    }

    [Fact]
    public async Task GetColumnsAsync_ReturnsCorrectColumnInfo()
    {
        ExecuteSql("CREATE TABLE test_products (" +
                   "id INTEGER PRIMARY KEY NOT NULL, " +
                   "name TEXT NOT NULL, " +
                   "price REAL, " +
                   "description TEXT DEFAULT 'N/A');");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var columns = await session.GetColumnsAsync("test_products");

        Assert.Equal(4, columns.Length);

        var idCol = columns.First(c => c.Name == "id");
        Assert.Equal("INTEGER", idCol.DataType);
        Assert.False(idCol.IsNullable);
        Assert.True(idCol.IsPrimaryKey);
        Assert.Equal(0, idCol.Ordinal);

        var nameCol = columns.First(c => c.Name == "name");
        Assert.Equal("TEXT", nameCol.DataType);
        Assert.False(nameCol.IsNullable);

        var priceCol = columns.First(c => c.Name == "price");
        Assert.Equal("REAL", priceCol.DataType);
        Assert.True(priceCol.IsNullable);

        var descCol = columns.First(c => c.Name == "description");
        Assert.Equal("TEXT", descCol.DataType);
        Assert.Equal("'N/A'", descCol.DefaultValue);
    }

    [Fact]
    public async Task ExecuteQueryAsync_WithSelect_ReturnsExpectedRows()
    {
        ExecuteSql("CREATE TABLE test_items (id INTEGER PRIMARY KEY, value TEXT);");
        ExecuteSql("INSERT INTO test_items (value) VALUES ('alpha');");
        ExecuteSql("INSERT INTO test_items (value) VALUES ('beta');");
        ExecuteSql("INSERT INTO test_items (value) VALUES ('gamma');");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var request = new QueryRequest
        {
            Sql = "SELECT id, value FROM test_items ORDER BY id",
            MaxRows = 100
        };
        var result = await session.ExecuteQueryAsync(request);

        Assert.Null(result.Error);
        Assert.Equal(3, result.RowCount);
        Assert.False(result.Truncated);
        Assert.Equal(2, result.Columns.Length);
        Assert.Equal("id", result.Columns[0].Name);
        Assert.Equal("value", result.Columns[1].Name);

        Assert.Equal(3, result.Rows.Count);
        Assert.Equal("alpha", result.Rows[0][1]);
        Assert.Equal("beta", result.Rows[1][1]);
        Assert.Equal("gamma", result.Rows[2][1]);
    }

    [Fact]
    public async Task ExecuteQueryAsync_MaxRowsTruncation_TruncatesResult()
    {
        ExecuteSql("CREATE TABLE test_rows (id INTEGER PRIMARY KEY, num INTEGER);");
        for (int i = 0; i < 10; i++)
            ExecuteSql($"INSERT INTO test_rows (num) VALUES ({i});");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var request = new QueryRequest
        {
            Sql = "SELECT id, num FROM test_rows ORDER BY id",
            MaxRows = 4
        };
        var result = await session.ExecuteQueryAsync(request);

        Assert.True(result.Truncated);
        Assert.Equal(4, result.RowCount);
        Assert.Equal(4, result.Rows.Count);
    }

    [Fact]
    public async Task ExecuteQueryAsync_WithInvalidSql_ReturnsError()
    {
        var session = new SQLiteSession(CreateInMemoryConnection());

        var request = new QueryRequest
        {
            Sql = "SELECTZ * FROM nonexistent_table;"
        };
        var result = await session.ExecuteQueryAsync(request);

        Assert.NotNull(result.Error);
        Assert.NotEmpty(result.Error);
    }

    [Fact]
    public async Task ExecuteQueryAsync_RecordsElapsedTime()
    {
        ExecuteSql("CREATE TABLE test_time (id INTEGER PRIMARY KEY, data TEXT);");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var request = new QueryRequest
        {
            Sql = "SELECT * FROM test_time",
            MaxRows = 100
        };
        var result = await session.ExecuteQueryAsync(request);

        Assert.True(result.ElapsedMs >= 0);
    }

    [Fact]
    public async Task ExecuteQueryAsync_WithDBNull_ReturnsNullInRow()
    {
        ExecuteSql("CREATE TABLE test_nulls (id INTEGER PRIMARY KEY, value TEXT);");
        ExecuteSql("INSERT INTO test_nulls (value) VALUES (NULL);");
        var session = new SQLiteSession(CreateInMemoryConnection());

        var request = new QueryRequest
        {
            Sql = "SELECT id, value FROM test_nulls",
            MaxRows = 100
        };
        var result = await session.ExecuteQueryAsync(request);

        Assert.Equal(1, result.RowCount);
        Assert.Null(result.Rows[0][1]);
    }

    [Fact]
    public async Task CloseAsync_ClosesSession()
    {
        ExecuteSql("CREATE TABLE test_close (id INTEGER PRIMARY KEY);");
        var session = new SQLiteSession(CreateInMemoryConnection());
        // force open
        await session.GetTablesAsync();
        Assert.True(session.IsOpen);

        await session.CloseAsync();

        Assert.False(session.IsOpen);
    }

    [Fact]
    public async Task Dispose_ClosesConnection()
    {
        ExecuteSql("CREATE TABLE test_dispose (id INTEGER PRIMARY KEY);");
        var session = new SQLiteSession(CreateInMemoryConnection());
        await session.GetTablesAsync();
        Assert.True(session.IsOpen);

        session.Dispose();

        Assert.False(session.IsOpen);
    }

    [Fact]
    public async Task CloseAsync_CanBeCalledMultipleTimes()
    {
        var session = new SQLiteSession(CreateInMemoryConnection());
        await session.CloseAsync();
        await session.CloseAsync();

        Assert.False(session.IsOpen);
    }

    [Fact]
    public async Task Dispose_CanBeCalledMultipleTimes()
    {
        var session = new SQLiteSession(CreateInMemoryConnection());

        session.Dispose();
        session.Dispose();

        Assert.False(session.IsOpen);
    }
}