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

namespace NewLife.Studio.Data.Tests;

public class SQLiteMetadataReaderTests : IDisposable
{
    private readonly SqliteConnection _connection;

    public SQLiteMetadataReaderTests()
    {
        _connection = new SqliteConnection("Data Source=:memory:");
        _connection.Open();
    }

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

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

    private SqliteDataReader ExecutePragmaTableInfo(string tableName)
    {
        // Do NOT dispose cmd here — SqliteDataReader is tied to the command,
        // and disposing the command closes the reader.
        var cmd = _connection.CreateCommand();
        cmd.CommandText = $"PRAGMA table_info('{tableName}')";
        return cmd.ExecuteReader();
    }

    [Fact]
    public void ParseTableInfo_FromRealPragmaResults_ReturnsCorrectColumns()
    {
        ExecuteSql("CREATE TABLE test_meta (" +
                   "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                   "name TEXT NOT NULL DEFAULT 'untitled', " +
                   "score REAL);");

        using var reader = ExecutePragmaTableInfo("test_meta");
        var columns = SQLiteMetadataReader.ParseTableInfo(reader);

        Assert.Equal(3, columns.Length);

        // id: cid=0, name=id, type=INTEGER, notnull=1 (true but ! => false), pk=1
        var idCol = columns[0];
        Assert.Equal(0, idCol.Ordinal);
        Assert.Equal("id", idCol.Name);
        Assert.Equal("INTEGER", idCol.DataType);
        Assert.False(idCol.IsNullable);
        Assert.Null(idCol.DefaultValue);
        Assert.True(idCol.IsPrimaryKey);

        // name: cid=1, name=name, type=TEXT, notnull=1, dflt='untitled', pk=0
        var nameCol = columns[1];
        Assert.Equal(1, nameCol.Ordinal);
        Assert.Equal("name", nameCol.Name);
        Assert.Equal("TEXT", nameCol.DataType);
        Assert.False(nameCol.IsNullable);
        Assert.Equal("'untitled'", nameCol.DefaultValue);
        Assert.False(nameCol.IsPrimaryKey);

        // score: cid=2, name=score, type=REAL, notnull=0, dflt=null, pk=0
        var scoreCol = columns[2];
        Assert.Equal(2, scoreCol.Ordinal);
        Assert.Equal("score", scoreCol.Name);
        Assert.Equal("REAL", scoreCol.DataType);
        Assert.True(scoreCol.IsNullable);
        Assert.Null(scoreCol.DefaultValue);
        Assert.False(scoreCol.IsPrimaryKey);
    }

    [Fact]
    public void ParseTableInfo_WithNoRows_ReturnsEmptyArray()
    {
        // PRAGMA table_info on a non-existent table yields no rows
        using var reader = ExecutePragmaTableInfo("nonexistent_table_xyz");
        var columns = SQLiteMetadataReader.ParseTableInfo(reader);

        Assert.Empty(columns);
    }

    [Fact]
    public void ParseIndexList_ReturnsCorrectIndexNames()
    {
        ExecuteSql("CREATE TABLE test_idx (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT);");
        ExecuteSql("CREATE INDEX idx_test_idx_name ON test_idx(name);");

        var indexes = SQLiteMetadataReader.ParseIndexList(_connection, "test_idx");

        // At least: sqlite_autoindex_test_idx_1 (for UNIQUE on email)
        // And: idx_test_idx_name
        Assert.Contains(indexes, i => i == "idx_test_idx_name");
    }

    [Fact]
    public void ParseIndexList_WithNoIndexes_ReturnsEmptyList()
    {
        ExecuteSql("CREATE TABLE test_no_idx (id INTEGER PRIMARY KEY, data TEXT);");

        var indexes = SQLiteMetadataReader.ParseIndexList(_connection, "test_no_idx");

        // No user-defined indexes, sqlite autoindex may exist for PK — should just run without error
        Assert.NotNull(indexes);
    }

    [Fact]
    public void ParseForeignKeyList_ReturnsCorrectForeignKeyText()
    {
        ExecuteSql("CREATE TABLE test_departments (id INTEGER PRIMARY KEY, name TEXT);");
        ExecuteSql("CREATE TABLE test_employees (" +
                   "id INTEGER PRIMARY KEY, " +
                   "name TEXT, " +
                   "dept_id INTEGER REFERENCES test_departments(id));");

        var fks = SQLiteMetadataReader.ParseForeignKeyList(_connection, "test_employees");

        Assert.NotEmpty(fks);
        Assert.Contains("dept_id -> test_departments(id)", fks);
    }

    [Fact]
    public void ParseForeignKeyList_WithNoForeignKeys_ReturnsEmptyList()
    {
        ExecuteSql("CREATE TABLE test_no_fk (id INTEGER PRIMARY KEY, data TEXT);");

        var fks = SQLiteMetadataReader.ParseForeignKeyList(_connection, "test_no_fk");

        Assert.NotNull(fks);
        Assert.Empty(fks);
    }
}