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

namespace Sonex.Data.Records;

public sealed class DictionaryValueRecord
{
    public string TableName { get; set; } = string.Empty;
    public string ColumnName { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;

    public static async Task<DB.SingleResult<Dictionary<string, string[]>>> GetValuesMap(
        string tableName,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(tableName))
        {
            return new DB.SingleResult<Dictionary<string, string[]>>
            {
                Success = false,
                Item = null,
                ErrorMessage = "TableName cannot be empty."
            };
        }

        string normalizedTableName = tableName.Trim();

        var queryResult = await DB.QueryListAsync<DictionaryValueRecord>(
            """
            SELECT
                table_name,
                column_name,
                value
            FROM sonex.dictionary_values
            WHERE lower(table_name) = lower(@tableName)
            ORDER BY column_name ASC,
                     value ASC;
            """,
            new
            {
                tableName = normalizedTableName
            },
            ct: ct).ConfigureAwait(false);

        if (!queryResult.Success)
        {
            return new DB.SingleResult<Dictionary<string, string[]>>
            {
                Success = false,
                Item = null,
                ErrorMessage = queryResult.ErrorMessage,
                ErrorType = queryResult.ErrorType,
                ErrorStackTrace = queryResult.ErrorStackTrace,
                ErrorData = queryResult.ErrorData
            };
        }

        var valueMap = queryResult.Items
            .Where(static row => !string.IsNullOrWhiteSpace(row.ColumnName))
            .GroupBy(
                static row => row.ColumnName.Trim().ToLowerInvariant(),
                StringComparer.OrdinalIgnoreCase)
            .ToDictionary(
                static group => group.Key,
                static group => group
                    .Select(static row => row.Value?.Trim())
                    .Where(static value => !string.IsNullOrWhiteSpace(value))
                    .Select(static value => value!)
                    .Distinct(StringComparer.OrdinalIgnoreCase)
                    .OrderBy(static value => value, StringComparer.OrdinalIgnoreCase)
                    .ToArray(),
                StringComparer.OrdinalIgnoreCase);

        return new DB.SingleResult<Dictionary<string, string[]>>
        {
            Success = true,
            Item = valueMap
        };
    }

    public static async Task<DB.SingleResult<int>> ReplaceColumnValues(
        string tableName,
        string columnName,
        IReadOnlyCollection<string> values,
        CancellationToken ct = default)
    {
        ArgumentNullException.ThrowIfNull(values);

        if (string.IsNullOrWhiteSpace(tableName))
        {
            return new DB.SingleResult<int>
            {
                Success = false,
                Item = 0,
                ErrorMessage = "TableName cannot be empty."
            };
        }

        if (string.IsNullOrWhiteSpace(columnName))
        {
            return new DB.SingleResult<int>
            {
                Success = false,
                Item = 0,
                ErrorMessage = "ColumnName cannot be empty."
            };
        }

        string normalizedTableName = tableName.Trim();
        string normalizedColumnName = columnName.Trim();
        string[] normalizedValues = values
            .Where(static value => !string.IsNullOrWhiteSpace(value))
            .Select(static value => value.Trim())
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToArray();

        try
        {
            using IDbConnection connection = DB.Open();
            using IDbTransaction transaction = connection.BeginTransaction();

            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    DELETE FROM sonex.dictionary_values
                    WHERE lower(table_name) = lower(@tableName)
                      AND lower(column_name) = lower(@columnName);
                    """,
                    new
                    {
                        tableName = normalizedTableName,
                        columnName = normalizedColumnName
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            if (normalizedValues.Length > 0)
            {
                var rows = normalizedValues.Select(value => new
                {
                    TableName = normalizedTableName,
                    ColumnName = normalizedColumnName,
                    Value = value
                }).ToArray();

                await connection.ExecuteAsync(
                    new CommandDefinition(
                        """
                        INSERT INTO sonex.dictionary_values
                        (
                            table_name,
                            column_name,
                            value
                        )
                        VALUES
                        (
                            @TableName,
                            @ColumnName,
                            @Value
                        );
                        """,
                        rows,
                        transaction: transaction,
                        cancellationToken: ct)).ConfigureAwait(false);
            }

            transaction.Commit();
            return new DB.SingleResult<int>
            {
                Success = true,
                Item = normalizedValues.Length
            };
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (Exception ex)
        {
            return new DB.SingleResult<int>
            {
                Success = false,
                Item = 0,
                ErrorMessage = ex.Message,
                ErrorType = ex.GetType().FullName,
                ErrorStackTrace = ex.StackTrace,
                ErrorData = BuildErrorData(ex)
            };
        }
    }

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

        var builder = new StringBuilder();
        foreach (System.Collections.DictionaryEntry entry in exception.Data)
        {
            builder.AppendLine($"{entry.Key}: {entry.Value}");
        }

        return builder.ToString();
    }
}