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;

namespace TakeoutSaaS.Application.App.StoreAudits.Handlers;

/// <summary>
/// 审核统计查询处理器。
/// </summary>
public sealed class GetStoreAuditStatisticsQueryHandler(
    IDapperExecutor dapperExecutor)
    : IRequestHandler<GetStoreAuditStatisticsQuery, StoreAuditStatisticsDto>
{
    /// <inheritdoc />
    public async Task<StoreAuditStatisticsDto> Handle(GetStoreAuditStatisticsQuery request, CancellationToken cancellationToken)
    {
        // 1. 规范化日期范围
        var today = DateTime.UtcNow.Date;
        var dateFrom = request.DateFrom?.Date ?? today.AddDays(-30);
        var dateTo = request.DateTo?.Date ?? today;
        if (dateFrom > dateTo)
        {
            (dateFrom, dateTo) = (dateTo, dateFrom);
        }

        // 1.1 (空行后) 计算统计边界
        var dateToExclusive = dateTo.AddDays(1);
        var overdueDeadline = DateTime.UtcNow.AddDays(-7);

        // 2. (空行后) 查询统计
        return await dapperExecutor.QueryAsync(
            DatabaseConstants.AppDataSource,
            DatabaseConnectionRole.Read,
            async (connection, token) =>
            {
                // 2.1 读取待审核与超时数量
                var pendingCount = await ExecuteScalarIntAsync(
                    connection,
                    BuildPendingCountSql(),
                    [
                        ("pendingStatus", (int)StoreAuditStatus.Pending)
                    ],
                    token);
                var overdueCount = await ExecuteScalarIntAsync(
                    connection,
                    BuildOverdueCountSql(),
                    [
                        ("pendingStatus", (int)StoreAuditStatus.Pending),
                        ("overdueDeadline", overdueDeadline)
                    ],
                    token);

                // 2.2 (空行后) 读取通过/驳回数量
                var (approvedCount, rejectedCount) = await QueryApproveRejectCountsAsync(
                    connection,
                    dateFrom,
                    dateToExclusive,
                    token);

                // 2.3 (空行后) 读取平均处理时长
                var avgProcessingHours = await ExecuteScalarDoubleAsync(
                    connection,
                    BuildAvgProcessingSql(),
                    [
                        ("dateFrom", dateFrom),
                        ("dateTo", dateToExclusive),
                        ("approveAction", (int)StoreAuditAction.Approve),
                        ("rejectAction", (int)StoreAuditAction.Reject)
                    ],
                    token);

                // 2.4 (空行后) 读取每日趋势
                var dailyTrend = await QueryDailyTrendAsync(
                    connection,
                    dateFrom,
                    dateToExclusive,
                    token);

                // 2.5 (空行后) 组装结果
                return new StoreAuditStatisticsDto
                {
                    PendingCount = pendingCount,
                    OverdueCount = overdueCount,
                    ApprovedCount = approvedCount,
                    RejectedCount = rejectedCount,
                    AvgProcessingHours = avgProcessingHours,
                    DailyTrend = dailyTrend
                };
            },
            cancellationToken);
    }

    private static async Task<(int ApprovedCount, int RejectedCount)> QueryApproveRejectCountsAsync(
        IDbConnection connection,
        DateTime dateFrom,
        DateTime dateTo,
        CancellationToken cancellationToken)
    {
        // 1. 查询通过/驳回统计
        await using var command = CreateCommand(
            connection,
            BuildApproveRejectCountSql(),
            [
                ("dateFrom", dateFrom),
                ("dateTo", dateTo),
                ("approveAction", (int)StoreAuditAction.Approve),
                ("rejectAction", (int)StoreAuditAction.Reject)
            ]);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        if (!await reader.ReadAsync(cancellationToken))
        {
            return (0, 0);
        }

        // 2. (空行后) 返回统计
        var approved = reader.IsDBNull(reader.GetOrdinal("ApprovedCount")) ? 0 : reader.GetInt32(reader.GetOrdinal("ApprovedCount"));
        var rejected = reader.IsDBNull(reader.GetOrdinal("RejectedCount")) ? 0 : reader.GetInt32(reader.GetOrdinal("RejectedCount"));
        return (approved, rejected);
    }

    private static async Task<IReadOnlyList<StoreAuditDailyTrendDto>> QueryDailyTrendAsync(
        IDbConnection connection,
        DateTime dateFrom,
        DateTime dateTo,
        CancellationToken cancellationToken)
    {
        // 1. 查询每日趋势
        await using var command = CreateCommand(
            connection,
            BuildDailyTrendSql(),
            [
                ("dateFrom", dateFrom),
                ("dateTo", dateTo),
                ("submitAction", (int)StoreAuditAction.Submit),
                ("resubmitAction", (int)StoreAuditAction.Resubmit),
                ("approveAction", (int)StoreAuditAction.Approve),
                ("rejectAction", (int)StoreAuditAction.Reject)
            ]);

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);
        var items = new List<StoreAuditDailyTrendDto>();
        if (!reader.HasRows)
        {
            return items;
        }

        // 2. (空行后) 映射趋势项
        var dateOrdinal = reader.GetOrdinal("Date");
        var submittedOrdinal = reader.GetOrdinal("SubmittedCount");
        var approvedOrdinal = reader.GetOrdinal("ApprovedCount");
        var rejectedOrdinal = reader.GetOrdinal("RejectedCount");
        while (await reader.ReadAsync(cancellationToken))
        {
            var date = reader.GetDateTime(dateOrdinal);
            items.Add(new StoreAuditDailyTrendDto
            {
                Date = DateOnly.FromDateTime(date),
                Submitted = reader.GetInt32(submittedOrdinal),
                Approved = reader.GetInt32(approvedOrdinal),
                Rejected = reader.GetInt32(rejectedOrdinal)
            });
        }

        // 3. (空行后) 返回趋势列表
        return items;
    }

    private static string BuildPendingCountSql()
    {
        return """
            select count(*)
            from public.stores s
            where s."DeletedAt" is null
              and s."AuditStatus" = @pendingStatus;
            """;
    }

    private static string BuildOverdueCountSql()
    {
        return """
            select count(*)
            from public.stores s
            where s."DeletedAt" is null
              and s."AuditStatus" = @pendingStatus
              and s."SubmittedAt" <= @overdueDeadline;
            """;
    }

    private static string BuildApproveRejectCountSql()
    {
        return """
            select
              sum(case when r."Action" = @approveAction then 1 else 0 end) as "ApprovedCount",
              sum(case when r."Action" = @rejectAction then 1 else 0 end) as "RejectedCount"
            from public.store_audit_records r
            where r."DeletedAt" is null
              and r."CreatedAt" >= @dateFrom
              and r."CreatedAt" < @dateTo
              and r."Action" in (@approveAction, @rejectAction);
            """;
    }

    private static string BuildAvgProcessingSql()
    {
        return """
            select
              avg(extract(epoch from (r."CreatedAt" - s."SubmittedAt")) / 3600.0) as "AvgHours"
            from public.store_audit_records r
            join public.stores s on s."Id" = r."StoreId" and s."DeletedAt" is null
            where r."DeletedAt" is null
              and r."Action" in (@approveAction, @rejectAction)
              and r."CreatedAt" >= @dateFrom
              and r."CreatedAt" < @dateTo
              and s."SubmittedAt" is not null;
            """;
    }

    private static string BuildDailyTrendSql()
    {
        return """
            select
              date_trunc('day', r."CreatedAt") as "Date",
              sum(case when r."Action" in (@submitAction, @resubmitAction) then 1 else 0 end) as "SubmittedCount",
              sum(case when r."Action" = @approveAction then 1 else 0 end) as "ApprovedCount",
              sum(case when r."Action" = @rejectAction then 1 else 0 end) as "RejectedCount"
            from public.store_audit_records r
            where r."DeletedAt" is null
              and r."CreatedAt" >= @dateFrom
              and r."CreatedAt" < @dateTo
            group by date_trunc('day', r."CreatedAt")
            order by date_trunc('day', r."CreatedAt");
            """;
    }

    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 async Task<double> ExecuteScalarDoubleAsync(
        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 ? 0d : Convert.ToDouble(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;
    }
}
