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

namespace Sonex.Data.Records;

// Stores minimal image metadata for website-imported products.
public sealed class ProductWebImageRecord
{
    public string ArticleNumber { get; set; } = string.Empty;
    public int ImageNo { get; set; }
    public string ImageUrlTemplate { get; set; } = string.Empty;
    public DateTime UpdatedAt { get; set; }

    public static Task<DB.Result<ProductWebImageRecord>> GetByArticleNumber(
        string articleNumber,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(articleNumber))
        {
            return Task.FromResult(new DB.Result<ProductWebImageRecord>
            {
                Success = false,
                ErrorMessage = "ArticleNumber cannot be empty."
            });
        }

        return DB.QueryListAsync<ProductWebImageRecord>(
            """
            SELECT
                article_number,
                image_no,
                image_url_template,
                updated_at
            FROM sonex.product_web_images
            WHERE article_number = @articleNumber
            ORDER BY image_no ASC;
            """,
            new
            {
                articleNumber = articleNumber.Trim()
            },
            ct: ct);
    }

    public static Task<DB.Result<ProductWebImageRecord>> GetOlderThan(
        DateTime updatedOlderThan,
        CancellationToken ct = default)
    {
        return DB.QueryListAsync<ProductWebImageRecord>(
            """
            SELECT
                article_number,
                image_no,
                image_url_template,
                updated_at
            FROM sonex.product_web_images
            WHERE updated_at < @updatedOlderThan
            ORDER BY article_number ASC, image_no ASC;
            """,
            new
            {
                updatedOlderThan
            },
            ct: ct);
    }

    public static async Task<DB.SingleResult<bool>> ReplaceArticle(
        string articleNumber,
        IReadOnlyCollection<ProductWebImageRecord> items,
        CancellationToken ct = default)
    {
        ArgumentNullException.ThrowIfNull(items);

        if (string.IsNullOrWhiteSpace(articleNumber))
        {
            return new DB.SingleResult<bool>
            {
                Success = false,
                Item = false,
                ErrorMessage = "ArticleNumber cannot be empty."
            };
        }

        string normalizedArticleNumber = articleNumber.Trim();

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

            await connection.ExecuteAsync(
                new CommandDefinition(
                    """
                    DELETE FROM sonex.product_web_images
                    WHERE article_number = @articleNumber;
                    """,
                    new
                    {
                        articleNumber = normalizedArticleNumber
                    },
                    transaction: transaction,
                    cancellationToken: ct)).ConfigureAwait(false);

            if (items.Count > 0)
            {
                var rows = items
                    .Where(item => !string.IsNullOrWhiteSpace(item.ImageUrlTemplate))
                    .Select(item => new
                    {
                        ArticleNumber = normalizedArticleNumber,
                        ImageNo = Math.Max(1, item.ImageNo),
                        ImageUrlTemplate = Normalize(item.ImageUrlTemplate) ?? string.Empty
                    })
                    .ToArray();

                if (rows.Length > 0)
                {
                    await connection.ExecuteAsync(
                        new CommandDefinition(
                            """
                            INSERT INTO sonex.product_web_images
                            (
                                article_number,
                                image_no,
                                image_url_template,
                                updated_at
                            )
                            VALUES
                            (
                                @ArticleNumber,
                                @ImageNo,
                                @ImageUrlTemplate,
                                NOW()
                            );
                            """,
                            rows,
                            transaction: transaction,
                            cancellationToken: ct)).ConfigureAwait(false);
                }
            }

            transaction.Commit();
            return new DB.SingleResult<bool>
            {
                Success = true,
                Item = true
            };
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (Exception ex)
        {
            return new DB.SingleResult<bool>
            {
                Success = false,
                Item = false,
                ErrorMessage = ex.Message,
                ErrorStackTrace = ex.StackTrace,
                ErrorData = BuildErrorData(ex)
            };
        }
    }

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

    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();
    }
}