using System.Data;
using System.Data.Common;
using MediatR;
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 ListPendingStoreAuditsQueryHandler(
    IDapperExecutor dapperExecutor)
    : IRequestHandler<ListPendingStoreAuditsQuery, PagedResult<PendingStoreAuditDto>>
{
    /// <inheritdoc />
    public async Task<PagedResult<PendingStoreAuditDto>> Handle(ListPendingStoreAuditsQuery request, CancellationToken cancellationToken)
    {
        // 1. 参数规范化
        var page = request.Page <= 0 ? 1 : request.Page;
        var pageSize = request.PageSize is <= 0 or > 200 ? 20 : request.PageSize;
        var keyword = string.IsNullOrWhiteSpace(request.Keyword) ? null : request.Keyword.Trim();
        var offset = (page - 1) * pageSize;
        var now = DateTime.UtcNow;
        var overdueDeadline = now.AddDays(-7);

        // 2. (空行后) 排序白名单
        var orderBy = request.SortBy?.Trim() switch
        {
            "StoreName" => "s.\"Name\"",
            "MerchantName" => "m.\"BrandName\"",
            "TenantName" => "t.\"Name\"",
            "SubmittedAt" => "s.\"SubmittedAt\"",
            _ => "s.\"SubmittedAt\""
        };

        // 3. (空行后) 执行查询
        return await dapperExecutor.QueryAsync(
            DatabaseConstants.AppDataSource,
            DatabaseConnectionRole.Read,
            async (connection, token) =>
            {
                // 3.1 统计总数
                var total = await ExecuteScalarIntAsync(
                    connection,
                    BuildCountSql(),
                    [
                        ("tenantId", request.TenantId),
                        ("keyword", keyword),
                        ("submittedFrom", request.SubmittedFrom),
                        ("submittedTo", request.SubmittedTo),
                        ("overdueOnly", request.OverdueOnly),
                        ("overdueDeadline", overdueDeadline),
                        ("pendingStatus", (int)StoreAuditStatus.Pending)
                    ],
                    token);

                // 3.2 (空行后) 查询列表
                var listSql = BuildListSql(orderBy, request.SortDesc);
                await using var listCommand = CreateCommand(
                    connection,
                    listSql,
                    [
                        ("tenantId", request.TenantId),
                        ("keyword", keyword),
                        ("submittedFrom", request.SubmittedFrom),
                        ("submittedTo", request.SubmittedTo),
                        ("overdueOnly", request.OverdueOnly),
                        ("overdueDeadline", overdueDeadline),
                        ("pendingStatus", (int)StoreAuditStatus.Pending),
                        ("offset", offset),
                        ("limit", pageSize)
                    ]);

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

                // 3.3 (空行后) 读取并映射
                var items = new List<PendingStoreAuditDto>();
                if (!reader.HasRows)
                {
                    return new PagedResult<PendingStoreAuditDto>(items, page, pageSize, total);
                }

                // 3.3.1 (空行后) 初始化字段序号
                var storeIdOrdinal = reader.GetOrdinal("StoreId");
                var storeNameOrdinal = reader.GetOrdinal("StoreName");
                var storeCodeOrdinal = reader.GetOrdinal("StoreCode");
                var tenantIdOrdinal = reader.GetOrdinal("TenantId");
                var tenantNameOrdinal = reader.GetOrdinal("TenantName");
                var merchantIdOrdinal = reader.GetOrdinal("MerchantId");
                var merchantNameOrdinal = reader.GetOrdinal("MerchantName");
                var signboardOrdinal = reader.GetOrdinal("SignboardImageUrl");
                var provinceOrdinal = reader.GetOrdinal("Province");
                var cityOrdinal = reader.GetOrdinal("City");
                var districtOrdinal = reader.GetOrdinal("District");
                var addressOrdinal = reader.GetOrdinal("Address");
                var ownershipOrdinal = reader.GetOrdinal("OwnershipType");
                var submittedAtOrdinal = reader.GetOrdinal("SubmittedAt");
                var qualificationCountOrdinal = reader.GetOrdinal("QualificationCount");

                while (await reader.ReadAsync(token))
                {
                    DateTime? submittedAt = reader.IsDBNull(submittedAtOrdinal)
                        ? null
                        : reader.GetDateTime(submittedAtOrdinal);
                    var waitingDays = submittedAt.HasValue
                        ? (int)Math.Floor((now - submittedAt.Value).TotalDays)
                        : 0;
                    if (waitingDays < 0)
                    {
                        waitingDays = 0;
                    }

                    // 3.3.2 (空行后) 组装地址信息
                    var province = reader.IsDBNull(provinceOrdinal) ? null : reader.GetString(provinceOrdinal);
                    var city = reader.IsDBNull(cityOrdinal) ? null : reader.GetString(cityOrdinal);
                    var district = reader.IsDBNull(districtOrdinal) ? null : reader.GetString(districtOrdinal);
                    var address = reader.IsDBNull(addressOrdinal) ? null : reader.GetString(addressOrdinal);
                    var fullAddress = string.Concat(
                        province ?? string.Empty,
                        city ?? string.Empty,
                        district ?? string.Empty,
                        address ?? string.Empty);

                    items.Add(new PendingStoreAuditDto
                    {
                        StoreId = reader.GetInt64(storeIdOrdinal),
                        StoreName = reader.GetString(storeNameOrdinal),
                        StoreCode = reader.GetString(storeCodeOrdinal),
                        TenantId = reader.GetInt64(tenantIdOrdinal),
                        TenantName = reader.GetString(tenantNameOrdinal),
                        MerchantId = reader.GetInt64(merchantIdOrdinal),
                        MerchantName = reader.GetString(merchantNameOrdinal),
                        SignboardImageUrl = reader.IsDBNull(signboardOrdinal) ? null : reader.GetString(signboardOrdinal),
                        FullAddress = fullAddress,
                        OwnershipType = (StoreOwnershipType)reader.GetInt32(ownershipOrdinal),
                        SubmittedAt = submittedAt,
                        WaitingDays = waitingDays,
                        IsOverdue = submittedAt.HasValue && submittedAt.Value <= overdueDeadline,
                        QualificationCount = reader.GetInt32(qualificationCountOrdinal)
                    });
                }

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

    private static string BuildCountSql()
    {
        return """
            select count(*)
            from public.stores s
            join public.merchants m on m."Id" = s."MerchantId" and m."DeletedAt" is null
            join public.tenants t on t."Id" = s."TenantId" and t."DeletedAt" is null
            where s."DeletedAt" is null
              and s."AuditStatus" = @pendingStatus
              and (@tenantId::bigint is null or s."TenantId" = @tenantId)
              and (
                @keyword::text is null
                or s."Name" ilike ('%' || @keyword::text || '%')
                or s."Code" ilike ('%' || @keyword::text || '%')
                or m."BrandName" ilike ('%' || @keyword::text || '%')
              )
              and (@submittedFrom::timestamp with time zone is null or s."SubmittedAt" >= @submittedFrom)
              and (@submittedTo::timestamp with time zone is null or s."SubmittedAt" <= @submittedTo)
              and (@overdueOnly::boolean = false or s."SubmittedAt" <= @overdueDeadline);
            """;
    }

    private static string BuildListSql(string orderBy, bool sortDesc)
    {
        var direction = sortDesc ? "desc" : "asc";

        // 1. (空行后) 构造列表 SQL
        return $"""
            select
              s."Id" as "StoreId",
              s."Name" as "StoreName",
              s."Code" as "StoreCode",
              s."TenantId",
              t."Name" as "TenantName",
              s."MerchantId",
              m."BrandName" as "MerchantName",
              s."SignboardImageUrl",
              s."Province",
              s."City",
              s."District",
              s."Address",
              s."OwnershipType",
              s."SubmittedAt",
              (
                select count(1)
                from public.store_qualifications q
                where q."StoreId" = s."Id" and q."DeletedAt" is null
              ) as "QualificationCount"
            from public.stores s
            join public.merchants m on m."Id" = s."MerchantId" and m."DeletedAt" is null
            join public.tenants t on t."Id" = s."TenantId" and t."DeletedAt" is null
            where s."DeletedAt" is null
              and s."AuditStatus" = @pendingStatus
              and (@tenantId::bigint is null or s."TenantId" = @tenantId)
              and (
                @keyword::text is null
                or s."Name" ilike ('%' || @keyword::text || '%')
                or s."Code" ilike ('%' || @keyword::text || '%')
                or m."BrandName" ilike ('%' || @keyword::text || '%')
              )
              and (@submittedFrom::timestamp with time zone is null or s."SubmittedAt" >= @submittedFrom)
              and (@submittedTo::timestamp with time zone is null or s."SubmittedAt" <= @submittedTo)
              and (@overdueOnly::boolean = false or s."SubmittedAt" <= @overdueDeadline)
            order by {orderBy} {direction}
            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;
    }
}
