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.Application.App.Stores.Dto;
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 GetStoreAuditDetailQueryHandler(
    IDapperExecutor dapperExecutor)
    : IRequestHandler<GetStoreAuditDetailQuery, StoreAuditDetailDto?>
{
    /// <inheritdoc />
    public async Task<StoreAuditDetailDto?> Handle(GetStoreAuditDetailQuery request, CancellationToken cancellationToken)
    {
        // 1. 查询门店与主体信息
        return await dapperExecutor.QueryAsync(
            DatabaseConstants.AppDataSource,
            DatabaseConnectionRole.Read,
            async (connection, token) =>
            {
                // 1.1 查询门店基础信息
                await using var storeCommand = CreateCommand(
                    connection,
                    BuildStoreSql(),
                    [
                        ("storeId", request.StoreId)
                    ]);

                await using var reader = await storeCommand.ExecuteReaderAsync(token);
                if (!await reader.ReadAsync(token))
                {
                    return null;
                }

                // 1.2 (空行后) 映射门店信息
                var store = new StoreAuditStoreDto
                {
                    Id = reader.GetInt64(reader.GetOrdinal("StoreId")),
                    Name = reader.GetString(reader.GetOrdinal("StoreName")),
                    Code = reader.GetString(reader.GetOrdinal("StoreCode")),
                    Phone = reader.IsDBNull(reader.GetOrdinal("Phone")) ? null : reader.GetString(reader.GetOrdinal("Phone")),
                    SignboardImageUrl = reader.IsDBNull(reader.GetOrdinal("SignboardImageUrl"))
                        ? null
                        : reader.GetString(reader.GetOrdinal("SignboardImageUrl")),
                    Province = reader.IsDBNull(reader.GetOrdinal("Province")) ? null : reader.GetString(reader.GetOrdinal("Province")),
                    City = reader.IsDBNull(reader.GetOrdinal("City")) ? null : reader.GetString(reader.GetOrdinal("City")),
                    District = reader.IsDBNull(reader.GetOrdinal("District")) ? null : reader.GetString(reader.GetOrdinal("District")),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString(reader.GetOrdinal("Address")),
                    Longitude = reader.IsDBNull(reader.GetOrdinal("Longitude")) ? null : reader.GetDouble(reader.GetOrdinal("Longitude")),
                    Latitude = reader.IsDBNull(reader.GetOrdinal("Latitude")) ? null : reader.GetDouble(reader.GetOrdinal("Latitude")),
                    OwnershipType = (StoreOwnershipType)reader.GetInt32(reader.GetOrdinal("OwnershipType")),
                    AuditStatus = (StoreAuditStatus)reader.GetInt32(reader.GetOrdinal("AuditStatus")),
                    SubmittedAt = reader.IsDBNull(reader.GetOrdinal("SubmittedAt"))
                        ? null
                        : reader.GetDateTime(reader.GetOrdinal("SubmittedAt"))
                };

                // 1.3 (空行后) 映射租户信息
                var tenant = new StoreAuditTenantDto
                {
                    Id = reader.GetInt64(reader.GetOrdinal("TenantId")),
                    Name = reader.GetString(reader.GetOrdinal("TenantName")),
                    ContactName = reader.IsDBNull(reader.GetOrdinal("TenantContactName"))
                        ? null
                        : reader.GetString(reader.GetOrdinal("TenantContactName")),
                    ContactPhone = reader.IsDBNull(reader.GetOrdinal("TenantContactPhone"))
                        ? null
                        : reader.GetString(reader.GetOrdinal("TenantContactPhone"))
                };

                // 1.4 (空行后) 映射商户信息
                var merchant = new StoreAuditMerchantDto
                {
                    Id = reader.GetInt64(reader.GetOrdinal("MerchantId")),
                    Name = reader.GetString(reader.GetOrdinal("MerchantName")),
                    LegalName = reader.IsDBNull(reader.GetOrdinal("MerchantLegalName"))
                        ? null
                        : reader.GetString(reader.GetOrdinal("MerchantLegalName")),
                    CreditCode = reader.IsDBNull(reader.GetOrdinal("MerchantCreditCode"))
                        ? null
                        : reader.GetString(reader.GetOrdinal("MerchantCreditCode"))
                };

                // 2. (空行后) 查询资质列表
                var qualifications = await QueryQualificationsAsync(connection, request.StoreId, token);

                // 3. (空行后) 查询审核记录
                var auditHistory = await QueryAuditRecordsAsync(connection, request.StoreId, token);

                // 4. (空行后) 组装结果
                return new StoreAuditDetailDto
                {
                    Store = store,
                    Tenant = tenant,
                    Merchant = merchant,
                    Qualifications = qualifications,
                    AuditHistory = auditHistory
                };
            },
            cancellationToken);
    }

    private static async Task<IReadOnlyList<StoreQualificationDto>> QueryQualificationsAsync(
        IDbConnection connection,
        long storeId,
        CancellationToken cancellationToken)
    {
        // 1. 查询门店资质
        await using var command = CreateCommand(
            connection,
            BuildQualificationSql(),
            [
                ("storeId", storeId)
            ]);

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

        // 2. (空行后) 映射资质 DTO
        var today = DateOnly.FromDateTime(DateTime.UtcNow);
        while (await reader.ReadAsync(cancellationToken))
        {
            DateOnly? expiresAt = reader.IsDBNull(reader.GetOrdinal("ExpiresAt"))
                ? null
                : DateOnly.FromDateTime(reader.GetDateTime(reader.GetOrdinal("ExpiresAt")));
            int? daysUntilExpiry = expiresAt.HasValue
                ? expiresAt.Value.DayNumber - today.DayNumber
                : null;
            var isExpired = expiresAt.HasValue && expiresAt.Value < today;
            var isExpiringSoon = expiresAt.HasValue
                && expiresAt.Value >= today
                && expiresAt.Value <= today.AddDays(30);

            // 2.1 (空行后) 写入列表
            items.Add(new StoreQualificationDto
            {
                Id = reader.GetInt64(reader.GetOrdinal("Id")),
                StoreId = reader.GetInt64(reader.GetOrdinal("StoreId")),
                QualificationType = (StoreQualificationType)reader.GetInt32(reader.GetOrdinal("QualificationType")),
                FileUrl = reader.GetString(reader.GetOrdinal("FileUrl")),
                DocumentNumber = reader.IsDBNull(reader.GetOrdinal("DocumentNumber"))
                    ? null
                    : reader.GetString(reader.GetOrdinal("DocumentNumber")),
                IssuedAt = reader.IsDBNull(reader.GetOrdinal("IssuedAt"))
                    ? null
                    : DateOnly.FromDateTime(reader.GetDateTime(reader.GetOrdinal("IssuedAt"))),
                ExpiresAt = expiresAt,
                IsExpired = isExpired,
                IsExpiringSoon = isExpiringSoon,
                DaysUntilExpiry = daysUntilExpiry,
                SortOrder = reader.GetInt32(reader.GetOrdinal("SortOrder")),
                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                UpdatedAt = reader.IsDBNull(reader.GetOrdinal("UpdatedAt"))
                    ? null
                    : reader.GetDateTime(reader.GetOrdinal("UpdatedAt"))
            });
        }

        // 3. (空行后) 返回结果
        return items;
    }

    private static async Task<IReadOnlyList<StoreAuditRecordDto>> QueryAuditRecordsAsync(
        IDbConnection connection,
        long storeId,
        CancellationToken cancellationToken)
    {
        // 1. 查询审核记录
        await using var command = CreateCommand(
            connection,
            BuildAuditRecordSql(),
            [
                ("storeId", storeId)
            ]);

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

        // 2. (空行后) 映射审核记录 DTO
        while (await reader.ReadAsync(cancellationToken))
        {
            var action = (StoreAuditAction)reader.GetInt32(reader.GetOrdinal("Action"));
            items.Add(new StoreAuditRecordDto
            {
                Id = reader.GetInt64(reader.GetOrdinal("Id")),
                Action = action,
                ActionName = StoreAuditActionNameResolver.Resolve(action),
                OperatorId = reader.IsDBNull(reader.GetOrdinal("OperatorId"))
                    ? null
                    : reader.GetInt64(reader.GetOrdinal("OperatorId")),
                OperatorName = reader.GetString(reader.GetOrdinal("OperatorName")),
                PreviousStatus = reader.IsDBNull(reader.GetOrdinal("PreviousStatus"))
                    ? null
                    : (StoreAuditStatus)reader.GetInt32(reader.GetOrdinal("PreviousStatus")),
                NewStatus = (StoreAuditStatus)reader.GetInt32(reader.GetOrdinal("NewStatus")),
                RejectionReasonId = reader.IsDBNull(reader.GetOrdinal("RejectionReasonId"))
                    ? null
                    : reader.GetInt64(reader.GetOrdinal("RejectionReasonId")),
                RejectionReasonText = reader.IsDBNull(reader.GetOrdinal("RejectionReason"))
                    ? null
                    : reader.GetString(reader.GetOrdinal("RejectionReason")),
                Remark = reader.IsDBNull(reader.GetOrdinal("Remarks"))
                    ? null
                    : reader.GetString(reader.GetOrdinal("Remarks")),
                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
            });
        }

        // 3. (空行后) 返回结果
        return items;
    }

    private static string BuildStoreSql()
    {
        return """
            select
              s."Id" as "StoreId",
              s."Name" as "StoreName",
              s."Code" as "StoreCode",
              s."Phone",
              s."SignboardImageUrl",
              s."Province",
              s."City",
              s."District",
              s."Address",
              s."Longitude",
              s."Latitude",
              s."OwnershipType",
              s."AuditStatus",
              s."SubmittedAt",
              s."TenantId",
              t."Name" as "TenantName",
              t."ContactName" as "TenantContactName",
              t."ContactPhone" as "TenantContactPhone",
              s."MerchantId",
              m."BrandName" as "MerchantName",
              m."LegalPerson" as "MerchantLegalName",
              m."TaxNumber" as "MerchantCreditCode"
            from public.stores s
            join public.tenants t on t."Id" = s."TenantId" and t."DeletedAt" is null
            join public.merchants m on m."Id" = s."MerchantId" and m."DeletedAt" is null
            where s."DeletedAt" is null
              and s."Id" = @storeId;
            """;
    }

    private static string BuildQualificationSql()
    {
        return """
            select
              q."Id",
              q."StoreId",
              q."QualificationType",
              q."FileUrl",
              q."DocumentNumber",
              q."IssuedAt",
              q."ExpiresAt",
              q."SortOrder",
              q."CreatedAt",
              q."UpdatedAt"
            from public.store_qualifications q
            where q."DeletedAt" is null
              and q."StoreId" = @storeId
            order by q."SortOrder", q."QualificationType";
            """;
    }

    private static string BuildAuditRecordSql()
    {
        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;
            """;
    }

    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;
    }
}
