using System.Data;
using System.Data.Common;
using MediatR;
using TakeoutSaaS.Application.App.StoreAudits;
using TakeoutSaaS.Application.App.StoreAudits.Dto;
using TakeoutSaaS.Application.App.StoreAudits.Queries;
using TakeoutSaaS.Domain.Stores.Enums;
using TakeoutSaaS.Shared.Abstractions.Constants;
using TakeoutSaaS.Shared.Abstractions.Data;
using TakeoutSaaS.Shared.Abstractions.Results;

namespace TakeoutSaaS.Application.App.StoreAudits.Handlers;

/// <summary>
/// 门店审核记录查询处理器。
/// </summary>
public sealed class ListStoreAuditRecordsQueryHandler(
    IDapperExecutor dapperExecutor)
    : IRequestHandler<ListStoreAuditRecordsQuery, PagedResult<StoreAuditRecordDto>>
{
    /// <inheritdoc />
    public async Task<PagedResult<StoreAuditRecordDto>> Handle(ListStoreAuditRecordsQuery request, CancellationToken cancellationToken)
    {
        // 1. 参数规范化
        var page = request.Page <= 0 ? 1 : request.Page;
        var pageSize = request.PageSize is <= 0 or > 200 ? 20 : request.PageSize;
        var offset = (page - 1) * pageSize;

        // 2. (空行后) 查询审核记录
        return await dapperExecutor.QueryAsync(
            DatabaseConstants.AppDataSource,
            DatabaseConnectionRole.Read,
            async (connection, token) =>
            {
                // 2.1 统计总数
                var total = await ExecuteScalarIntAsync(
                    connection,
                    BuildCountSql(),
                    [
                        ("storeId", request.StoreId)
                    ],
                    token);

                // 2.2 (空行后) 查询列表
                await using var listCommand = CreateCommand(
                    connection,
                    BuildListSql(),
                    [
                        ("storeId", request.StoreId),
                        ("offset", offset),
                        ("limit", pageSize)
                    ]);

                await using var reader = await listCommand.ExecuteReaderAsync(token);

                // 2.3 (空行后) 映射列表
                var items = new List<StoreAuditRecordDto>();
                if (!reader.HasRows)
                {
                    return new PagedResult<StoreAuditRecordDto>(items, page, pageSize, total);
                }

                // 2.3.1 (空行后) 初始化字段序号
                var idOrdinal = reader.GetOrdinal("Id");
                var actionOrdinal = reader.GetOrdinal("Action");
                var operatorIdOrdinal = reader.GetOrdinal("OperatorId");
                var operatorNameOrdinal = reader.GetOrdinal("OperatorName");
                var previousStatusOrdinal = reader.GetOrdinal("PreviousStatus");
                var newStatusOrdinal = reader.GetOrdinal("NewStatus");
                var rejectionReasonIdOrdinal = reader.GetOrdinal("RejectionReasonId");
                var rejectionReasonOrdinal = reader.GetOrdinal("RejectionReason");
                var remarksOrdinal = reader.GetOrdinal("Remarks");
                var createdAtOrdinal = reader.GetOrdinal("CreatedAt");

                while (await reader.ReadAsync(token))
                {
                    var action = (StoreAuditAction)reader.GetInt32(actionOrdinal);
                    items.Add(new StoreAuditRecordDto
                    {
                        Id = reader.GetInt64(idOrdinal),
                        Action = action,
                        ActionName = StoreAuditActionNameResolver.Resolve(action),
                        OperatorId = reader.IsDBNull(operatorIdOrdinal) ? null : reader.GetInt64(operatorIdOrdinal),
                        OperatorName = reader.GetString(operatorNameOrdinal),
                        PreviousStatus = reader.IsDBNull(previousStatusOrdinal)
                            ? null
                            : (StoreAuditStatus)reader.GetInt32(previousStatusOrdinal),
                        NewStatus = (StoreAuditStatus)reader.GetInt32(newStatusOrdinal),
                        RejectionReasonId = reader.IsDBNull(rejectionReasonIdOrdinal)
                            ? null
                            : reader.GetInt64(rejectionReasonIdOrdinal),
                        RejectionReasonText = reader.IsDBNull(rejectionReasonOrdinal)
                            ? null
                            : reader.GetString(rejectionReasonOrdinal),
                        Remark = reader.IsDBNull(remarksOrdinal) ? null : reader.GetString(remarksOrdinal),
                        CreatedAt = reader.GetDateTime(createdAtOrdinal)
                    });
                }

                // 2.4 (空行后) 返回分页结果
                return new PagedResult<StoreAuditRecordDto>(items, page, pageSize, total);
            },
            cancellationToken);
    }

    private static string BuildCountSql()
    {
        return """
            select count(*)
            from public.store_audit_records r
            where r."DeletedAt" is null
              and r."StoreId" = @storeId;
            """;
    }

    private static string BuildListSql()
    {
        return """
            select
              r."Id",
              r."Action",
              r."OperatorId",
              r."OperatorName",
              r."PreviousStatus",
              r."NewStatus",
              r."RejectionReasonId",
              r."RejectionReason",
              r."Remarks",
              r."CreatedAt"
            from public.store_audit_records r
            where r."DeletedAt" is null
              and r."StoreId" = @storeId
            order by r."CreatedAt" desc
            offset @offset
            limit @limit;
            """;
    }

    private static async Task<int> ExecuteScalarIntAsync(
        IDbConnection connection,
        string sql,
        (string Name, object? Value)[] parameters,
        CancellationToken cancellationToken)
    {
        await using var command = CreateCommand(connection, sql, parameters);
        var result = await command.ExecuteScalarAsync(cancellationToken);
        return result is null or DBNull ? 0 : Convert.ToInt32(result);
    }

    private static DbCommand CreateCommand(IDbConnection connection, string sql, (string Name, object? Value)[] parameters)
    {
        var command = connection.CreateCommand();
        command.CommandText = sql;

        // 1. (空行后) 绑定参数
        foreach (var (name, value) in parameters)
        {
            var p = command.CreateParameter();
            p.ParameterName = name;
            p.Value = value ?? DBNull.Value;
            command.Parameters.Add(p);
        }

        return (DbCommand)command;
    }
}
