using System.Diagnostics;
using Microsoft.Data.Sqlite;
using NewLife.Studio.Core.DTOs;
using NewLife.Log;

namespace NewLife.Studio.Data.Providers.SQLite;

/// <summary>SQLite 数据库会话</summary>
public class SQLiteSession : IDbSession
{
    private SqliteConnection? _connection;
    private bool _disposed;

    public string SessionId { get; } = Guid.NewGuid().ToString("N");
    public ConnectionInfo Connection { get; }
    public bool IsOpen => _connection?.State == System.Data.ConnectionState.Open;

    public SQLiteSession(ConnectionInfo connection)
    {
        Connection = connection;
    }

    private SqliteConnection GetConnection()
    {
        if (_connection == null)
        {
            var connStr = Connection.ConnectionString;
            if (!connStr.StartsWith("Data Source=", StringComparison.OrdinalIgnoreCase))
                connStr = $"Data Source={connStr}";

            _connection = new SqliteConnection(connStr);
            _connection.Open();
            XTrace.WriteLine($"SQLiteSession: Opened connection {SessionId}");
        }

        if (_connection.State != System.Data.ConnectionState.Open)
            _connection.Open();

        return _connection;
    }

    public Task<TableInfo[]> GetTablesAsync(CancellationToken ct = default)
    {
        var conn = GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";

        var tables = new List<TableInfo>();
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            tables.Add(new TableInfo
            {
                Name = reader.GetString(0),
                Schema = "main"
            });
        }

        return Task.FromResult(tables.ToArray());
    }

    public Task<ColumnInfo[]> GetColumnsAsync(string tableName, CancellationToken ct = default)
    {
        var conn = GetConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"PRAGMA table_info('{tableName}')";

        using var reader = cmd.ExecuteReader();
        var columns = SQLiteMetadataReader.ParseTableInfo(reader);
        return Task.FromResult(columns);
    }

    public async Task<QueryResult> ExecuteQueryAsync(QueryRequest request, CancellationToken ct = default)
    {
        var sw = Stopwatch.StartNew();
        var result = new QueryResult();

        try
        {
            var conn = GetConnection();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = request.Sql;
            cmd.CommandTimeout = request.TimeoutSeconds;

            using var reader = await cmd.ExecuteReaderAsync(ct);

            // 提取列信息
            var columns = new List<ColumnInfo>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                columns.Add(new ColumnInfo
                {
                    Ordinal = i,
                    Name = reader.GetName(i),
                    DataType = reader.GetDataTypeName(i)
                });
            }
            result.Columns = columns.ToArray();

            // 读取数据行
            var rows = new List<object?[]>();
            int rowCount = 0;
            while (await reader.ReadAsync(ct))
            {
                if (rowCount >= request.MaxRows)
                {
                    result.Truncated = true;
                    break;
                }

                var row = new object?[reader.FieldCount];
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[i] = reader.IsDBNull(i) ? null : reader.GetValue(i);
                }
                rows.Add(row);
                rowCount++;
            }

            result.Rows = rows;
            result.RowCount = rowCount;
        }
        catch (Exception ex)
        {
            result.Error = ex.Message;
            XTrace.WriteLine($"SQLiteSession ExecuteQuery error: {ex.Message}");
        }
        finally
        {
            sw.Stop();
            result.ElapsedMs = sw.ElapsedMilliseconds;
        }

        return result;
    }

    public async Task CloseAsync()
    {
        if (_connection != null)
        {
            await _connection.CloseAsync();
            _connection.Dispose();
            _connection = null;
            XTrace.WriteLine($"SQLiteSession: Closed connection {SessionId}");
        }
    }

    public void Dispose()
    {
        if (!_disposed)
        {
            _connection?.Dispose();
            _connection = null;
            _disposed = true;
        }
    }
}