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

namespace Sonex.Data.Records;

public sealed class ProductImageRecord
{
    public long Id { get; set; }
    public string ArticleNumber { get; set; } = string.Empty;
    public int ImageNo { get; set; }
    public string SourceRelativePath { get; set; } = string.Empty;
    public string SourceFileName { get; set; } = string.Empty;
    public string SourceStem { get; set; } = string.Empty;
    public string SourceExtension { get; set; } = string.Empty;
    public string TargetFileName { get; set; } = string.Empty;
    public string TargetRelativePath { get; set; } = string.Empty;
    public long SourceLength { get; set; }
    public DateTime SourceLastWriteAt { get; set; }
    public DateTime UpdatedAt { get; set; }

    public static Task<DB.Result<ProductImageRecord>> GetAll(CancellationToken ct = default)
    {
        return DB.QueryListAsync<ProductImageRecord>(
            """
            SELECT *
            FROM sonex.products_images
            ORDER BY article_number, image_no, source_file_name;
            """,
            ct: ct);
    }

    public static Task<DB.Result<ProductImageRecord>> GetByArticleNumber(
        string articleNumber,
        CancellationToken ct = default)
    {
        return DB.QueryListAsync<ProductImageRecord>(
            """
            SELECT *
            FROM sonex.products_images
            WHERE article_number = @articleNumber
            ORDER BY image_no, source_file_name;
            """,
            new
            {
                articleNumber
            },
            ct: ct);
    }

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

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

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

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

            if (items.Count > 0)
            {
                var rows = items
                    .Select(item => new
                    {
                        ArticleNumber = articleNumber,
                        item.ImageNo,
                        item.SourceRelativePath,
                        item.SourceFileName,
                        item.SourceStem,
                        item.SourceExtension,
                        item.SourceLength,
                        item.SourceLastWriteAt,
                        item.UpdatedAt
                    })
                    .ToArray();

                await connection.ExecuteAsync(
                    new CommandDefinition(
                        """
                        INSERT INTO sonex.products_images
                        (
                            article_number,
                            image_no,
                            source_relative_path,
                            source_file_name,
                            source_stem,
                            source_extension,
                            source_length,
                            source_last_write_at,
                            updated_at
                        )
                        VALUES
                        (
                            @ArticleNumber,
                            @ImageNo,
                            @SourceRelativePath,
                            @SourceFileName,
                            @SourceStem,
                            @SourceExtension,
                            @SourceLength,
                            @SourceLastWriteAt,
                            @UpdatedAt
                        );
                        """,
                        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? BuildErrorData(Exception exception)
    {
        if (exception.Data is null || exception.Data.Count == 0)
            return null;

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

        return builder.ToString();
    }
}