using System.Collections;
using System.Data;
using System.Text;
using Dapper;
using Npgsql;
using Sonex.Data.Messages;

namespace Sonex.Data.Database;

public static partial class DB
{
    private static Func<IDbConnection>? _connectionFactory;
    private static Func<NpgsqlConnection>? _npgsqlConnectionFactory;

    public static event Action<Exception>? OnError;
    public static MessagesService Notifications { get; } = new();

    public static DbClientSessionInfo? ClientSessionInfo { get; private set; }

    public static bool IsInitialized => _connectionFactory is not null;

    public static void Init(Func<IDbConnection> connectionFactory)
    {
        Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
        _connectionFactory = connectionFactory ?? throw new ArgumentNullException(nameof(connectionFactory));
        _npgsqlConnectionFactory = null;
    }

    public static void SetClientSessionInfo(DbClientSessionInfo clientSessionInfo)
    {
        ClientSessionInfo = clientSessionInfo ?? throw new ArgumentNullException(nameof(clientSessionInfo));
    }

    public static void InitPostgreSQL(string connectionString)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
            throw new ArgumentException("Connection string cannot be null or empty.", nameof(connectionString));

        Init(() => new NpgsqlConnection(connectionString));
        _npgsqlConnectionFactory = () => new NpgsqlConnection(connectionString);
    }

    public static void InitPostgreSQL(
        string host,
        int port,
        string database,
        string username,
        string password,
        string? applicationName = null,
        int timeout = 15,
        int commandTimeout = 30,
        bool pooling = true,
        int minPoolSize = 0,
        int maxPoolSize = 100,
        bool includeErrorDetail = false)
    {
        if (string.IsNullOrWhiteSpace(host))
            throw new ArgumentException("Host cannot be empty.", nameof(host));

        if (port <= 0)
            throw new ArgumentOutOfRangeException(nameof(port));

        if (string.IsNullOrWhiteSpace(database))
            throw new ArgumentException("Database cannot be empty.", nameof(database));

        if (string.IsNullOrWhiteSpace(username))
            throw new ArgumentException("Username cannot be empty.", nameof(username));

        if (string.IsNullOrWhiteSpace(password))
            throw new ArgumentException("Password cannot be empty.", nameof(password));

        var builder = new NpgsqlConnectionStringBuilder
        {
            Host = host,
            Port = port,
            Database = database,
            Username = username,
            Password = password,
            Timeout = timeout,
            CommandTimeout = commandTimeout,
            Pooling = pooling,
            MinPoolSize = minPoolSize,
            MaxPoolSize = maxPoolSize,
            IncludeErrorDetail = includeErrorDetail
        };

        var effectiveApplicationName = string.IsNullOrWhiteSpace(applicationName)
            ? ClientSessionInfo?.BuildDatabaseApplicationName()
            : applicationName;

        if (!string.IsNullOrWhiteSpace(effectiveApplicationName))
            builder.ApplicationName = effectiveApplicationName;

        Init(() => new NpgsqlConnection(builder.ConnectionString));
        _npgsqlConnectionFactory = () => new NpgsqlConnection(builder.ConnectionString);
    }

    public static IDbConnection Open()
    {
        if (_connectionFactory is null)
            throw new InvalidOperationException("DB.Init(...) must be called before using Sonex.Data.");

        var connection = _connectionFactory();
        connection.Open();
        return connection;
    }

    public static NpgsqlConnection OpenNpgsqlConnection()
    {
        if (_npgsqlConnectionFactory is null)
            throw new InvalidOperationException("DB.InitPostgreSQL(...) must be called before using PostgreSQL notifications.");

        var connection = _npgsqlConnectionFactory();
        connection.Open();
        return connection;
    }

    public static async Task<SingleResult<int>> ExecuteAsync(
        string sql,
        object? param = null,
        IDbTransaction? tx = null,
        int? commandTimeout = null,
        CommandType? commandType = null,
        CancellationToken ct = default)
    {
        try
        {
            var (connection, shouldDispose) = ResolveConnection(tx);
            try
            {
                var command = new CommandDefinition(
                    commandText: sql,
                    parameters: param,
                    transaction: tx,
                    commandTimeout: commandTimeout,
                    commandType: commandType,
                    cancellationToken: ct);

                int affectedRows = await connection.ExecuteAsync(command).ConfigureAwait(false);
                return new SingleResult<int>
                {
                    Success = true,
                    Item = affectedRows
                };
            }
            finally
            {
                if (shouldDispose)
                    connection.Dispose();
            }
        }
        catch (Exception exception)
        {
            return ToSingleError<int>(exception);
        }
    }

    public static async Task<Result<T>> QueryListAsync<T>(
        string sql,
        object? param = null,
        IDbTransaction? tx = null,
        int? commandTimeout = null,
        CommandType? commandType = null,
        CancellationToken ct = default)
    {
        try
        {
            var (connection, shouldDispose) = ResolveConnection(tx);
            try
            {
                var command = new CommandDefinition(
                    commandText: sql,
                    parameters: param,
                    transaction: tx,
                    commandTimeout: commandTimeout,
                    commandType: commandType,
                    cancellationToken: ct);

                var rows = await connection.QueryAsync<T>(command).ConfigureAwait(false);
                return new Result<T>
                {
                    Success = true,
                    Items = rows.AsList()
                };
            }
            finally
            {
                if (shouldDispose)
                    connection.Dispose();
            }
        }
        catch (Exception exception)
        {
            return ToError<T>(exception);
        }
    }

    public static async Task<SingleResult<T>> QuerySingleAsync<T>(
        string sql,
        object? param = null,
        IDbTransaction? tx = null,
        int? commandTimeout = null,
        CommandType? commandType = null,
        CancellationToken ct = default)
    {
        try
        {
            var (connection, shouldDispose) = ResolveConnection(tx);
            try
            {
                var command = new CommandDefinition(
                    commandText: sql,
                    parameters: param,
                    transaction: tx,
                    commandTimeout: commandTimeout,
                    commandType: commandType,
                    cancellationToken: ct);

                var item = await connection.QuerySingleOrDefaultAsync<T>(command).ConfigureAwait(false);
                return new SingleResult<T>
                {
                    Success = true,
                    Item = item
                };
            }
            finally
            {
                if (shouldDispose)
                    connection.Dispose();
            }
        }
        catch (Exception exception)
        {
            return ToSingleError<T>(exception);
        }
    }

    private static (IDbConnection Connection, bool ShouldDispose) ResolveConnection(IDbTransaction? tx)
    {
        if (tx?.Connection is not null)
            return (tx.Connection, false);

        return (Open(), true);
    }

    private static Result<T> ToError<T>(Exception exception)
    {
        OnError?.Invoke(exception);

        return new Result<T>
        {
            Success = false,
            ErrorMessage = exception.Message,
            ErrorType = exception.GetType().FullName,
            ErrorStackTrace = exception.StackTrace,
            ErrorData = FormatErrorData(exception)
        };
    }

    private static SingleResult<T> ToSingleError<T>(Exception exception)
    {
        OnError?.Invoke(exception);

        return new SingleResult<T>
        {
            Success = false,
            ErrorMessage = exception.Message,
            ErrorType = exception.GetType().FullName,
            ErrorStackTrace = exception.StackTrace,
            ErrorData = FormatErrorData(exception)
        };
    }

    private static string? FormatErrorData(Exception exception)
    {
        if (exception.Data is null || exception.Data.Count == 0)
            return null;

        var builder = new StringBuilder();

        foreach (DictionaryEntry item in exception.Data)
        {
            try
            {
                builder.AppendLine($"{item.Key}: {item.Value}");
            }
            catch
            {
                builder.AppendLine($"{item.Key}: <ToString failed>");
            }
        }

        return builder.ToString();
    }
}