using Sonex.Data.Database;
using System.Net.Sockets;

namespace Sonex.Library.WorkersCore;

public static partial class Worker
{
    private const int DatabaseRetryAttempts = 10;
    private static readonly TimeSpan DatabaseRetryDelay = TimeSpan.FromMinutes(2);
    private static readonly string[] TransientDatabaseMarkers =
    [
        "connection",
        "timeout",
        "timed out",
        "network",
        "socket",
        "broken pipe",
        "connection refused",
        "host is unreachable",
        "08000",
        "08001",
        "08003",
        "08004",
        "08006",
        "57p01",
        "57p02",
        "57p03",
        "53300"
    ];

    private static readonly object DatabaseInitLock = new();

    public static void EnsureDatabaseInitialized()
    {
        if (DB.IsInitialized)
            return;

        lock (DatabaseInitLock)
        {
            if (DB.IsInitialized)
                return;

            string host = GetDatabaseHost();
            int port = Config.GetIntValue("DB_PORT", 5432);
            string database = Config.GetStringValue("DB_DATA_BASE", "sonex");
            string userName = GetDatabaseUserName();
            string password = GetDatabasePassword();
            int timeout = Config.GetIntValue("DB_TIMEOUT", 15);
            int commandTimeout = Config.GetIntValue("DB_COMMAND_TIMEOUT", 30);
            bool pooling = Config.GetBoolValue("DB_POOLING", true);
            int minPoolSize = Config.GetIntValue("DB_MIN_POOL_SIZE", 0);
            int maxPoolSize = Config.GetIntValue("DB_MAXPOOLSIZE", 100);
            bool includeErrorDetail = Config.GetBoolValue("DB_INCLUDE_ERROR_DETAIL", false);

            var connectionStringBuilder =
                $"Host={host};" +
                $"Port={port};" +
                $"Database={database};" +
                $"Username={userName};" +
                $"Timeout={timeout};" +
                $"Command Timeout={commandTimeout};" +
                $"Pooling={pooling};" +
                $"Minimum Pool Size={minPoolSize};" +
                $"Maximum Pool Size={maxPoolSize};" +
                $"Include Error Detail={includeErrorDetail};" +
                $"Application Name={Name};";

            if (!string.IsNullOrWhiteSpace(password))
            {
                connectionStringBuilder += $"Password={password};";
            }

            string connectionString = connectionStringBuilder;
            DB.InitPostgreSQL(connectionString);

            using var connection = DB.Open();
        }
    }

    public static DB.SingleResult<T> ExecuteDatabaseSingleWithRetry<T>(
        Func<CancellationToken, Task<DB.SingleResult<T>>> action,
        string operation)
    {
        return ExecuteDatabaseWithRetryAsync(action, operation, CancellationToken.None)
            .GetAwaiter()
            .GetResult();
    }

    public static Task<DB.SingleResult<T>> ExecuteDatabaseSingleWithRetryAsync<T>(
        Func<CancellationToken, Task<DB.SingleResult<T>>> action,
        string operation,
        CancellationToken cancellationToken = default)
    {
        return ExecuteDatabaseWithRetryAsync(action, operation, cancellationToken);
    }

    public static Task<DB.Result<T>> ExecuteDatabaseListWithRetryAsync<T>(
        Func<CancellationToken, Task<DB.Result<T>>> action,
        string operation,
        CancellationToken cancellationToken = default)
    {
        return ExecuteDatabaseWithRetryAsync(action, operation, cancellationToken);
    }

    private static async Task<TResult> ExecuteDatabaseWithRetryAsync<TResult>(
        Func<CancellationToken, Task<TResult>> action,
        string operation,
        CancellationToken cancellationToken)
        where TResult : DB.Result
    {
        ArgumentNullException.ThrowIfNull(action);

        EnsureDatabaseInitialized();
        string normalizedOperation = string.IsNullOrWhiteSpace(operation)
            ? "DatabaseOperation"
            : operation.Trim();

        for (int attempt = 1; attempt <= DatabaseRetryAttempts; attempt++)
        {
            cancellationToken.ThrowIfCancellationRequested();

            try
            {
                TResult result = await action(cancellationToken).ConfigureAwait(false);
                if (result.Success || !IsTransientDatabaseFailure(result))
                    return result;

                if (attempt >= DatabaseRetryAttempts)
                    return result;

                PrintDatabaseRetryWarning(
                    normalizedOperation,
                    attempt,
                    result.ErrorMessage ?? "Unknown database connection error.");
            }
            catch (OperationCanceledException)
            {
                throw;
            }
            catch (Exception ex)
            {
                if (!IsTransientDatabaseException(ex) || attempt >= DatabaseRetryAttempts)
                    throw;

                PrintDatabaseRetryWarning(
                    normalizedOperation,
                    attempt,
                    ex.Message);
            }

            await Task.Delay(DatabaseRetryDelay, cancellationToken).ConfigureAwait(false);
        }

        throw new InvalidOperationException("Database retry loop ended unexpectedly.");
    }

    private static bool IsTransientDatabaseFailure(DB.Result result)
    {
        return ContainsTransientMarker(result.ErrorType) ||
               ContainsTransientMarker(result.ErrorMessage) ||
               ContainsTransientMarker(result.ErrorData);
    }

    private static bool IsTransientDatabaseException(Exception exception)
    {
        if (exception is TimeoutException or System.IO.IOException or SocketException)
            return true;

        if (ContainsTransientMarker(exception.GetType().FullName) ||
            ContainsTransientMarker(exception.Message) ||
            ContainsTransientMarker(exception.ToString()))
        {
            return true;
        }

        return exception.InnerException is not null &&
               IsTransientDatabaseException(exception.InnerException);
    }

    private static bool ContainsTransientMarker(string? value)
    {
        if (string.IsNullOrWhiteSpace(value))
            return false;

        foreach (string marker in TransientDatabaseMarkers)
        {
            if (value.Contains(marker, StringComparison.OrdinalIgnoreCase))
                return true;
        }

        return false;
    }

    private static void PrintDatabaseRetryWarning(
        string operation,
        int attempt,
        string errorMessage)
    {
        Console.Error.WriteLine(
            $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss}] [WorkerCore] Database connection failure. Operation={operation}. Attempt={attempt}/{DatabaseRetryAttempts}. Retrying in {(int)DatabaseRetryDelay.TotalSeconds}s. Error={errorMessage}");
    }

    private static string GetDatabaseHost()
    {
        if (Config.TryGetStringValue("DB_HOST", out string host) &&
            !string.IsNullOrWhiteSpace(host))
        {
            return host.Trim();
        }

        if (Config.TryGetStringValue("Host", out string legacyHost) &&
            !string.IsNullOrWhiteSpace(legacyHost))
        {
            return legacyHost.Trim();
        }

        return "localhost";
    }

    private static string GetDatabaseUserName()
    {
        if (Config.TryGetStringValue("DB_USER_NAME", out string userName) &&
            !string.IsNullOrWhiteSpace(userName))
        {
            return userName.Trim();
        }

        if (Config.TryGetStringValue("USER_NAME", out string legacyUserName) &&
            !string.IsNullOrWhiteSpace(legacyUserName))
        {
            return legacyUserName.Trim();
        }

        return "worker";
    }

    private static string GetDatabasePassword()
    {
        if (Config.TryGetStringValue("DB_PASSWORD", out string password) &&
            !string.IsNullOrWhiteSpace(password))
        {
            return password.Trim();
        }

        if (Config.TryGetStringValue("USER_PASSWORD", out string legacyPassword) &&
            !string.IsNullOrWhiteSpace(legacyPassword))
        {
            return legacyPassword.Trim();
        }

        return string.Empty;
    }
}