using Sonex.Data.Database;
using System.Text.Json;

namespace Sonex.Data.Records;

public sealed class WorkerInfoRecord
{
    private const string SelectColumns = """
        SELECT
            name,
            title,
            description,
            app_version,
            exe_path,
            work_type,
            status,
            activity,
            progress,
            progress_info,
            parameters::text AS parameters_json
        FROM sonex.worker_info
        """;

    private static readonly JsonSerializerOptions ParameterJsonOptions = new()
    {
        PropertyNameCaseInsensitive = true
    };

    public string Name { get; set; } = string.Empty;
    public string Title { get; set; } = string.Empty;
    public string? Description { get; set; }
    public string? AppVersion { get; set; }
    public string ExePath { get; set; } = string.Empty;
    public string WorkType { get; set; } = string.Empty;
    public string Status { get; set; } = string.Empty;
    public string Activity { get; set; } = string.Empty;
    public string Progress { get; set; } = string.Empty;
    public string ProgressInfo { get; set; } = string.Empty;
    public JsonElement Parameters { get; set; } = JsonSerializer.SerializeToElement(new List<object>());

    public static async Task<DB.SingleResult<WorkerInfoRecord>> Get(
        string name,
        CancellationToken ct = default)
    {
        var rowResult = await DB.QuerySingleAsync<WorkerInfoQueryRow>(
            $"""
            {SelectColumns}
            WHERE name = @name
            LIMIT 1;
            """,
            new
            {
                name = Normalize(name)
            },
            ct: ct).ConfigureAwait(false);

        if (!rowResult.Success)
        {
            return new DB.SingleResult<WorkerInfoRecord>
            {
                Success = false,
                ErrorMessage = rowResult.ErrorMessage,
                ErrorType = rowResult.ErrorType,
                ErrorStackTrace = rowResult.ErrorStackTrace,
                ErrorData = rowResult.ErrorData
            };
        }

        return new DB.SingleResult<WorkerInfoRecord>
        {
            Success = true,
            Item = rowResult.Item == null
                ? null
                : MapFromRow(rowResult.Item)
        };
    }

    public static async Task<DB.Result<WorkerInfoRecord>> GetAll(
        int limit = 1000,
        CancellationToken ct = default)
    {
        var normalizedLimit = limit <= 0 ? 1000 : limit;

        var rowResult = await DB.QueryListAsync<WorkerInfoQueryRow>(
            $"""
            {SelectColumns}
            ORDER BY title ASC NULLS LAST, name ASC NULLS LAST
            LIMIT @limit;
            """,
            new
            {
                limit = normalizedLimit
            },
            ct: ct).ConfigureAwait(false);

        if (!rowResult.Success)
        {
            return new DB.Result<WorkerInfoRecord>
            {
                Success = false,
                ErrorMessage = rowResult.ErrorMessage,
                ErrorType = rowResult.ErrorType,
                ErrorStackTrace = rowResult.ErrorStackTrace,
                ErrorData = rowResult.ErrorData
            };
        }

        return new DB.Result<WorkerInfoRecord>
        {
            Success = true,
            Items = rowResult.Items.Select(MapFromRow).ToList()
        };
    }

    public static void Register(WorkerInfoRecord workerInfo)
    {
        _ = RegisterAsync(workerInfo);
    }

    public static Task<DB.SingleResult<bool>> RegisterAsync(
        WorkerInfoRecord workerInfo,
        CancellationToken ct = default)
    {
        return RegisterInternalAsync(workerInfo, ct);
    }

    private static async Task<DB.SingleResult<bool>> RegisterInternalAsync(
        WorkerInfoRecord workerInfo,
        CancellationToken ct)
    {
        ArgumentNullException.ThrowIfNull(workerInfo);

        if (string.IsNullOrWhiteSpace(workerInfo.Name))
        {
            return Failed("Worker name cannot be empty.");
        }

        var mergedParametersResult = await MergeParametersAsync(
            workerInfo.Name,
            workerInfo.Parameters,
            ct).ConfigureAwait(false);

        if (!mergedParametersResult.Success)
        {
            return new DB.SingleResult<bool>
            {
                Success = false,
                Item = false,
                ErrorMessage = mergedParametersResult.ErrorMessage,
                ErrorType = mergedParametersResult.ErrorType,
                ErrorStackTrace = mergedParametersResult.ErrorStackTrace,
                ErrorData = mergedParametersResult.ErrorData
            };
        }

        var mergedParameters = mergedParametersResult.Item;
        workerInfo.Parameters = mergedParameters.ValueKind == JsonValueKind.Undefined
            ? JsonSerializer.SerializeToElement(new List<WorkerParameterModel>())
            : mergedParameters;

        return await DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.worker_info (
                name,
                title,
                description,
                app_version,
                exe_path,
                work_type,
                status,
                activity,
                progress,
                progress_info,
                parameters
            )
            VALUES (
                @Name,
                @Title,
                @Description,
                @AppVersion,
                @ExePath,
                @WorkType,
                @Status,
                @Activity,
                @Progress,
                @ProgressInfo,
                CAST(@Parameters AS jsonb)
            )
            ON CONFLICT (name) DO UPDATE
            SET
                title = EXCLUDED.title,
                description = EXCLUDED.description,
                app_version = EXCLUDED.app_version,
                exe_path = EXCLUDED.exe_path,
                work_type = EXCLUDED.work_type,
                status = EXCLUDED.status,
                activity = EXCLUDED.activity,
                progress = EXCLUDED.progress,
                progress_info = EXCLUDED.progress_info,
                parameters = EXCLUDED.parameters
            RETURNING TRUE;
            """,
            new
            {
                Name = Normalize(workerInfo.Name),
                Title = Normalize(workerInfo.Title),
                Description = NormalizeNullable(workerInfo.Description),
                AppVersion = NormalizeNullable(workerInfo.AppVersion),
                ExePath = Normalize(workerInfo.ExePath),
                WorkType = Normalize(workerInfo.WorkType),
                Status = Normalize(workerInfo.Status),
                Activity = Normalize(workerInfo.Activity),
                Progress = Normalize(workerInfo.Progress),
                ProgressInfo = Normalize(workerInfo.ProgressInfo),
                Parameters = ToJson(workerInfo.Parameters)
            },
            ct: ct).ConfigureAwait(false);
    }

    public static void UpdateStatus(
        string workerName,
        string status,
        string activity,
        string progress,
        string progressInfo)
    {
        _ = UpdateStatusAsync(workerName, status, activity, progress, progressInfo);
    }

    public static Task<DB.SingleResult<bool>> UpdateStatusAsync(
        string workerName,
        string status,
        string activity,
        string progress,
        string progressInfo,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(workerName))
        {
            return Task.FromResult(Failed("Worker name cannot be empty."));
        }

        return DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.worker_info (
                name,
                status,
                activity,
                progress,
                progress_info
            )
            VALUES (
                @Name,
                @Status,
                @Activity,
                @Progress,
                @ProgressInfo
            )
            ON CONFLICT (name) DO UPDATE
            SET
                status = EXCLUDED.status,
                activity = EXCLUDED.activity,
                progress = EXCLUDED.progress,
                progress_info = EXCLUDED.progress_info
            RETURNING TRUE;
            """,
            new
            {
                Name = Normalize(workerName),
                Status = Normalize(status),
                Activity = Normalize(activity),
                Progress = Normalize(progress),
                ProgressInfo = Normalize(progressInfo)
            },
            ct: ct);
    }

    public static void UpdateActivity(
        string workerName,
        string activity,
        string progress,
        string progressInfo)
    {
        _ = UpdateActivityAsync(workerName, activity, progress, progressInfo);
    }

    public static Task<DB.SingleResult<bool>> UpdateActivityAsync(
        string workerName,
        string activity,
        string progress,
        string progressInfo,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(workerName))
        {
            return Task.FromResult(Failed("Worker name cannot be empty."));
        }

        return DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.worker_info (
                name,
                activity,
                progress,
                progress_info
            )
            VALUES (
                @Name,
                @Activity,
                @Progress,
                @ProgressInfo
            )
            ON CONFLICT (name) DO UPDATE
            SET
                activity = EXCLUDED.activity,
                progress = EXCLUDED.progress,
                progress_info = EXCLUDED.progress_info
            RETURNING TRUE;
            """,
            new
            {
                Name = Normalize(workerName),
                Activity = Normalize(activity),
                Progress = Normalize(progress),
                ProgressInfo = Normalize(progressInfo)
            },
            ct: ct);
    }

    public static void UpdateProgress(
        string workerName,
        string progress,
        string progressInfo)
    {
        _ = UpdateProgressAsync(workerName, progress, progressInfo);
    }

    public static Task<DB.SingleResult<bool>> UpdateProgressAsync(
        string workerName,
        string progress,
        string progressInfo,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(workerName))
        {
            return Task.FromResult(Failed("Worker name cannot be empty."));
        }

        return DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.worker_info (
                name,
                progress,
                progress_info
            )
            VALUES (
                @Name,
                @Progress,
                @ProgressInfo
            )
            ON CONFLICT (name) DO UPDATE
            SET
                progress = EXCLUDED.progress,
                progress_info = EXCLUDED.progress_info
            RETURNING TRUE;
            """,
            new
            {
                Name = Normalize(workerName),
                Progress = Normalize(progress),
                ProgressInfo = Normalize(progressInfo)
            },
            ct: ct);
    }

    public static void UpdateParameters(
        string workerName,
        JsonElement parameters)
    {
        _ = UpdateParametersAsync(workerName, parameters);
    }

    public static Task<DB.SingleResult<bool>> UpdateParametersAsync(
        string workerName,
        JsonElement parameters,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(workerName))
        {
            return Task.FromResult(Failed("Worker name cannot be empty."));
        }

        var normalizedParameters = parameters.ValueKind == JsonValueKind.Undefined
            ? JsonSerializer.SerializeToElement(new List<object>())
            : parameters;

        return DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.worker_info (
                name,
                parameters
            )
            VALUES (
                @Name,
                CAST(@Parameters AS jsonb)
            )
            ON CONFLICT (name) DO UPDATE
            SET
                parameters = EXCLUDED.parameters
            RETURNING TRUE;
            """,
            new
            {
                Name = Normalize(workerName),
                Parameters = ToJson(normalizedParameters)
            },
            ct: ct);
    }

    public static void WorkerStarted(string workerName)
    {
        _ = UpdateStatusAsync(workerName, "Running", string.Empty, string.Empty, string.Empty);
    }

    public static void WorkerPaused(
        string workerName,
        string activity,
        string progress,
        string progressInfo)
    {
        _ = UpdateStatusAsync(workerName, "Paused", activity, progress, progressInfo);
    }

    public static void WorkerResumed(
        string workerName,
        string activity,
        string progress,
        string progressInfo)
    {
        _ = UpdateStatusAsync(workerName, "Running", activity, progress, progressInfo);
    }

    public static void WorkerFinished(
        string workerName,
        string progress,
        string progressInfo)
    {
        _ = UpdateStatusAsync(workerName, "Stopped", string.Empty, progress, progressInfo);
    }

    public static void WorkerError(
        string workerName,
        string activity,
        string progress,
        string progressInfo)
    {
        _ = UpdateStatusAsync(workerName, "Error", activity, progress, progressInfo);
    }

    private static DB.SingleResult<bool> Failed(string errorMessage)
    {
        return new DB.SingleResult<bool>
        {
            Success = false,
            Item = false,
            ErrorMessage = errorMessage
        };
    }

    private static string Normalize(string? value)
    {
        return string.IsNullOrWhiteSpace(value)
            ? string.Empty
            : value.Trim();
    }

    private static string? NormalizeNullable(string? value)
    {
        return string.IsNullOrWhiteSpace(value)
            ? null
            : value.Trim();
    }

    private static string ToJson(JsonElement value)
    {
        return value.ValueKind == JsonValueKind.Undefined
            ? "[]"
            : value.GetRawText();
    }

    private static async Task<DB.SingleResult<JsonElement>> MergeParametersAsync(
        string workerName,
        JsonElement inputParameters,
        CancellationToken ct)
    {
        var input = ParseParameters(ToJson(inputParameters));
        var normalizedWorkerName = Normalize(workerName);

        var existingJsonResult = await DB.QuerySingleAsync<string>(
            """
            SELECT parameters::text
            FROM sonex.worker_info
            WHERE name = @Name
            LIMIT 1;
            """,
            new
            {
                Name = normalizedWorkerName
            },
            ct: ct).ConfigureAwait(false);

        if (!existingJsonResult.Success)
        {
            return new DB.SingleResult<JsonElement>
            {
                Success = false,
                ErrorMessage = existingJsonResult.ErrorMessage,
                ErrorType = existingJsonResult.ErrorType,
                ErrorStackTrace = existingJsonResult.ErrorStackTrace,
                ErrorData = existingJsonResult.ErrorData
            };
        }

        var existing = ParseParameters(existingJsonResult.Item);
        var existingByKey = new Dictionary<string, WorkerParameterModel>(StringComparer.OrdinalIgnoreCase);

        foreach (var item in existing)
        {
            var key = NormalizeNullable(item.Parametr);
            if (string.IsNullOrWhiteSpace(key))
                continue;

            existingByKey[key] = item;
        }

        var merged = new List<WorkerParameterModel>();

        foreach (var item in input)
        {
            var key = NormalizeNullable(item.Parametr);
            if (string.IsNullOrWhiteSpace(key))
                continue;

            if (existingByKey.TryGetValue(key, out var existingItem))
            {
                item.Value = existingItem.Value;
            }

            merged.Add(item);
        }

        return new DB.SingleResult<JsonElement>
        {
            Success = true,
            Item = JsonSerializer.SerializeToElement(merged, ParameterJsonOptions)
        };
    }

    private static List<WorkerParameterModel> ParseParameters(string? json)
    {
        if (string.IsNullOrWhiteSpace(json))
            return [];

        try
        {
            var items = JsonSerializer.Deserialize<List<WorkerParameterModel>>(json, ParameterJsonOptions);
            return items ?? [];
        }
        catch (JsonException)
        {
            return [];
        }
    }

    private static WorkerInfoRecord MapFromRow(WorkerInfoQueryRow row)
    {
        return new WorkerInfoRecord
        {
            Name = Normalize(row.Name),
            Title = Normalize(row.Title),
            Description = NormalizeNullable(row.Description),
            AppVersion = NormalizeNullable(row.AppVersion),
            ExePath = Normalize(row.ExePath),
            WorkType = Normalize(row.WorkType),
            Status = Normalize(row.Status),
            Activity = Normalize(row.Activity),
            Progress = Normalize(row.Progress),
            ProgressInfo = Normalize(row.ProgressInfo),
            Parameters = ParseParametersJson(row.ParametersJson)
        };
    }

    private static JsonElement ParseParametersJson(string? json)
    {
        if (string.IsNullOrWhiteSpace(json))
            return JsonSerializer.SerializeToElement(new List<object>());

        try
        {
            using var jsonDocument = JsonDocument.Parse(json);
            return jsonDocument.RootElement.Clone();
        }
        catch (JsonException)
        {
            return JsonSerializer.SerializeToElement(new List<object>());
        }
    }

    private sealed class WorkerParameterModel
    {
        public string Parametr { get; set; } = string.Empty;
        public string? Value { get; set; }
        public string Title { get; set; } = string.Empty;
        public string? Description { get; set; }
        public string ValueType { get; set; } = string.Empty;
    }

    private sealed class WorkerInfoQueryRow
    {
        public string? Name { get; set; }
        public string? Title { get; set; }
        public string? Description { get; set; }
        public string? AppVersion { get; set; }
        public string? ExePath { get; set; }
        public string? WorkType { get; set; }
        public string? Status { get; set; }
        public string? Activity { get; set; }
        public string? Progress { get; set; }
        public string? ProgressInfo { get; set; }
        public string? ParametersJson { get; set; }
    }
}