namespace Sonex.Services.ImageSync;

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

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

    public static Task<DB.Result<ImageSyncRecord>> GetByArticleNumberAsync(
        string articleNumber,
        CancellationToken ct = default)
    {
        return DB.QueryListAsync<ImageSyncRecord>(
            """
            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>> ReplaceArticleAsync(
        string articleNumber,
        IReadOnlyCollection<ImageSyncRecordUpsert> items,
        CancellationToken ct = default)
    {
        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)
            {
                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
                        );
                        """,
                        items,
                        transaction: transaction,
                        cancellationToken: ct)).ConfigureAwait(false);
            }

            transaction.Commit();
            return new DB.SingleResult<bool>(true, 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();
    }
}
