using CashLineERP.Application.Internal;
using CashLineERP.SharedKernel.IslamicFinance;
using CashLineERP.SharedKernel.Workflow;
using Microsoft.EntityFrameworkCore;

namespace CashLineERP.Infrastructure.Persistence.Repositories;

public sealed class InternalWorkbenchRepository : IInternalWorkbenchRepository
{
    private const string FinanceModuleCode = "FINANCE";
    private const string IslamicFinanceExceptionSourceType = "ISLAMIC_FINANCE";
    private const string IslamicFinancePartnerLenderDocumentType = "IF_PARTNER_LENDER";
    private const string IslamicFinanceProductProfileDocumentType = "IF_PRODUCT_PROFILE";
    private const string IslamicFinanceContractTemplateDocumentType = "IF_CONTRACT_TEMPLATE";
    private const string IslamicFinanceFacilityDocumentType = "IF_FACILITY";
    private const string IslamicFinanceRequestDocumentType = "IF_FINANCING_REQUEST";
    private const string IslamicFinanceDecisionDocumentType = "IF_FINANCING_DECISION";
    private const string IslamicFinanceShariahReviewDocumentType = "IF_SHARIAH_REVIEW";
    private const string IslamicFinanceMurabahaCaseDocumentType = "IF_MURABAHA_CASE";
    private const string IslamicFinanceMurabahaDisbursementDocumentType = "IF_MURABAHA_DISBURSEMENT";
    private readonly CashLineErpDbContext _dbContext;

    public InternalWorkbenchRepository(CashLineErpDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public InternalDashboardSummaryView GetDashboardSummary()
    {
        var approvals = GetApprovalQueue();
        var exceptions = GetExceptionQueue();

        return new InternalDashboardSummaryView(
            approvals.Count,
            approvals.Count(item => string.Equals(item.ModuleCode, "PROCUREMENT", StringComparison.OrdinalIgnoreCase)),
            approvals.Count(item => string.Equals(item.ModuleCode, "FINANCE", StringComparison.OrdinalIgnoreCase)),
            approvals.Count(item => string.Equals(item.ModuleCode, "SALES", StringComparison.OrdinalIgnoreCase)),
            approvals.Count(item => string.Equals(item.ModuleCode, "INVENTORY", StringComparison.OrdinalIgnoreCase)),
            exceptions.Count(item => string.Equals(item.SourceType, "RECONCILIATION", StringComparison.OrdinalIgnoreCase)),
            exceptions.Count(item => string.Equals(item.SourceType, "ETA", StringComparison.OrdinalIgnoreCase)),
            exceptions.Count(item => string.Equals(item.SourceType, "CREDIT_CONTROL", StringComparison.OrdinalIgnoreCase)),
            exceptions.Count);
    }

    public InternalOperationalDashboardView GetOperationalDashboard()
    {
        var metrics = GetOperationalMetrics();
        var alerts = BuildOperationalAlerts(metrics);

        return new InternalOperationalDashboardView(
            metrics.PendingApprovalCount,
            metrics.TotalExceptionCount,
            metrics.OpenReconciliationExceptionCount,
            metrics.EtaAttentionCount,
            metrics.CreditHoldCount,
            metrics.OpenSettlementExceptionCount,
            metrics.PendingOutboxCount,
            metrics.RetryOutboxCount,
            metrics.DeadLetterOutboxCount,
            metrics.ReceivedInboxCount,
            metrics.RetryInboxCount,
            metrics.DeadLetterInboxCount,
            metrics.PendingNotificationCount,
            metrics.RetryNotificationCount,
            metrics.FailedNotificationCount,
            metrics.DeadLetterNotificationCount,
            metrics.OverdueCollectionsCount,
            alerts.Count(item => string.Equals(item.SeverityCode, "CRITICAL", StringComparison.OrdinalIgnoreCase)),
            alerts.Count(item => string.Equals(item.SeverityCode, "HIGH", StringComparison.OrdinalIgnoreCase)),
            alerts.Count(item => string.Equals(item.SeverityCode, "WARNING", StringComparison.OrdinalIgnoreCase)));
    }

    public IReadOnlyCollection<InternalApprovalQueueItemView> GetApprovalQueue(string? moduleCode = null)
    {
        var normalizedModuleCode = string.IsNullOrWhiteSpace(moduleCode) ? null : moduleCode.Trim().ToUpperInvariant();
        var partiesById = _dbContext.BusinessParties
            .AsNoTracking()
            .ToArray()
            .ToDictionary(party => party.Id, party => party.PartyCode);
        var usersById = _dbContext.Users
            .AsNoTracking()
            .ToArray()
            .ToDictionary(user => user.Id, user => user.LoginName);

        var workflowQueueRows = _dbContext.WorkflowInstances
            .AsNoTracking()
            .Where(instance => instance.CurrentStatusCode == WorkflowStatusCode.Submitted)
            .OrderByDescending(instance => instance.SubmittedAtUtc ?? instance.CreatedAtUtc)
            .Select(instance => new
            {
                instance.Id,
                instance.ModuleCode,
                instance.DocumentType,
                instance.DocumentId,
                instance.CurrentStatusCode,
                SubmittedBy = _dbContext.Users
                    .Where(user => user.Id == instance.MakerUserId)
                    .Select(user => user.LoginName)
                    .FirstOrDefault(),
                SubmittedAtUtc = instance.SubmittedAtUtc ?? instance.CreatedAtUtc
            })
            .ToArray()
            .Where(instance => normalizedModuleCode is null || string.Equals(instance.ModuleCode, normalizedModuleCode, StringComparison.OrdinalIgnoreCase))
            .Select(instance =>
            {
                var metadata = TryGetApprovalMetadata(instance.DocumentType, instance.DocumentId, partiesById)
                    ?? new ApprovalMetadata($"{instance.DocumentType}-{instance.DocumentId:D6}", null, null, null);
                return new InternalApprovalQueueItemView(
                    instance.Id,
                    instance.ModuleCode,
                    instance.DocumentType,
                    instance.DocumentId,
                    metadata.DocumentNumber,
                    instance.CurrentStatusCode,
                    instance.SubmittedBy ?? "UNKNOWN",
                    instance.SubmittedAtUtc,
                    metadata.BranchId,
                    metadata.PartyCode,
                    metadata.Amount);
            })
            .ToArray();

        var islamicFinanceQueueRows = BuildIslamicFinanceApprovalQueue(partiesById, usersById, normalizedModuleCode);

        return workflowQueueRows
            .Concat(islamicFinanceQueueRows)
            .OrderByDescending(item => item.SubmittedAtUtc)
            .ThenBy(item => item.DocumentType)
            .ThenBy(item => item.DocumentNumber)
            .ToArray();
    }

    public IReadOnlyCollection<InternalExceptionQueueItemView> GetExceptionQueue(string? sourceType = null)
    {
        var normalizedSourceType = string.IsNullOrWhiteSpace(sourceType) ? null : sourceType.Trim().ToUpperInvariant();
        var partiesById = _dbContext.BusinessParties
            .AsNoTracking()
            .ToArray()
            .ToDictionary(party => party.Id, party => party.PartyCode);

        var items = new List<InternalExceptionQueueItemView>();

        var reconciliationItems = _dbContext.ReceiptReconciliationItems
            .AsNoTracking()
            .ToArray()
            .Where(item => item.MatchStatusCode is "EXCEPTION" or "UNMATCHED")
            .ToArray();
        var reconciliationRunsById = _dbContext.ReceiptReconciliationRuns.AsNoTracking().ToArray().ToDictionary(run => run.Id);
        var statementLinesById = _dbContext.BankStatementLines.AsNoTracking().ToArray().ToDictionary(line => line.Id);
        var statementsById = _dbContext.BankStatements.AsNoTracking().ToArray().ToDictionary(statement => statement.Id);

        items.AddRange(reconciliationItems.Select(item =>
        {
            var line = statementLinesById[item.BankStatementLineId];
            var statement = statementsById[line.BankStatementId];
            var run = reconciliationRunsById[item.ReceiptReconciliationRunId];

            return new InternalExceptionQueueItemView(
                "RECONCILIATION",
                item.MatchStatusCode,
                "RECONCILIATION_ITEM",
                item.Id,
                $"{run.RunNumber}:{line.LineNumber}",
                null,
                null,
                item.MatchStatusCode,
                line.TransactionDate,
                item.DifferenceAmount != 0m ? Math.Abs(item.DifferenceAmount) : line.GetStatementAmount(),
                line.DescriptionText ?? "Reconciliation item requires review.",
                line.ExternalReference ?? statement.StatementNumber);
        }));

        var salesOrders = _dbContext.SalesOrders
            .AsNoTracking()
            .ToArray()
            .Where(order => string.Equals(order.CreditStatusCode, "ON_HOLD", StringComparison.OrdinalIgnoreCase))
            .ToArray();
        items.AddRange(salesOrders.Select(order => new InternalExceptionQueueItemView(
            "CREDIT_CONTROL",
            "CREDIT_HOLD",
            "SALES_ORDER",
            order.Id,
            order.SalesOrderNumber,
            order.BranchId,
            partiesById.GetValueOrDefault(order.CustomerId),
            order.CreditStatusCode,
            order.OrderDate,
            order.TotalAmount,
            order.CreditHoldReason ?? "Sales order requires finance credit review.",
            order.CreditReleaseDecisionCode)));

        var etaInvoices = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .ToArray()
            .Where(invoice =>
                invoice.StatusId == WorkflowStatusSeedIds.Posted &&
                CashLineERP.Application.Tax.EtaPayloadBuilder.SupportsInvoiceType(invoice.InvoiceType) &&
                !string.Equals(invoice.EtaStatus, "ACCEPTED", StringComparison.OrdinalIgnoreCase))
            .ToArray();
        var salesOrdersById = _dbContext.SalesOrders.AsNoTracking().ToArray().ToDictionary(order => order.Id);
        items.AddRange(etaInvoices.Select(invoice =>
        {
            long? branchId = null;
            if (invoice.SalesOrderId.HasValue && salesOrdersById.TryGetValue(invoice.SalesOrderId.Value, out var salesOrder))
            {
                branchId = salesOrder.BranchId;
            }

            var status = string.IsNullOrWhiteSpace(invoice.EtaStatus) ? "PENDING" : invoice.EtaStatus!;
            return new InternalExceptionQueueItemView(
                "ETA",
                status,
                "AR_INVOICE",
                invoice.Id,
                invoice.InvoiceNumber,
                branchId,
                partiesById.GetValueOrDefault(invoice.CustomerId),
                status,
                invoice.TaxDate ?? invoice.InvoiceDate,
                invoice.TotalAmount,
                $"ETA document requires attention with status {status}.",
                invoice.EtaUuid);
        }));

        var dispatches = _dbContext.DispatchOrders
            .AsNoTracking()
            .Include(entry => entry.Lines)
            .ToArray()
            .Where(entry =>
                !string.IsNullOrWhiteSpace(entry.DeliveryExceptionCode)
                && !string.Equals(entry.DeliveryRecoveryStatusCode, "RECOVERED", StringComparison.OrdinalIgnoreCase)
                && entry.Lines.Any(line => line.QuantityFailed > line.QuantityRecovered))
            .ToArray();
        items.AddRange(dispatches.Select(dispatch =>
        {
            if (!salesOrdersById.TryGetValue(dispatch.SalesOrderId, out var salesOrder))
            {
                return new InternalExceptionQueueItemView(
                    "DELIVERY",
                    dispatch.DeliveryRecoveryStatusCode ?? "PENDING",
                "DISPATCH",
                dispatch.Id,
                dispatch.DispatchNumber,
                dispatch.BranchId,
                null,
                dispatch.DeliveryExceptionCode ?? "DELIVERY_EXCEPTION",
                DateOnly.FromDateTime(dispatch.DeliveredAtUtc ?? dispatch.ShippedAtUtc ?? DateTime.UtcNow),
                dispatch.Lines.Sum(line => line.QuantityFailed - line.QuantityRecovered),
                dispatch.DeliveryExceptionNotes ?? "Dispatch requires failed-delivery recovery.",
                dispatch.ProofOfDeliveryReference);
            }

            return new InternalExceptionQueueItemView(
                "DELIVERY",
                dispatch.DeliveryRecoveryStatusCode ?? "PENDING",
                "DISPATCH",
                dispatch.Id,
                dispatch.DispatchNumber,
                dispatch.BranchId,
                partiesById.GetValueOrDefault(salesOrder.CustomerId),
                dispatch.DeliveryExceptionCode ?? "DELIVERY_EXCEPTION",
                DateOnly.FromDateTime(dispatch.DeliveredAtUtc ?? dispatch.ShippedAtUtc ?? DateTime.UtcNow),
                dispatch.Lines.Sum(line => line.QuantityFailed - line.QuantityRecovered),
                dispatch.DeliveryExceptionNotes ?? "Dispatch requires failed-delivery recovery.",
                dispatch.ProofOfDeliveryReference);
        }));

        var settlementExceptions = _dbContext.PaymentSettlementExceptions
            .AsNoTracking()
            .ToArray()
            .Where(entry => string.Equals(entry.StatusCode, "OPEN", StringComparison.OrdinalIgnoreCase))
            .ToArray();
        var settlementBatchesById = _dbContext.PaymentSettlementBatches.AsNoTracking().ToArray().ToDictionary(entry => entry.Id);
        var settlementChannelsById = _dbContext.PaymentChannelConfigurations.AsNoTracking().ToArray().ToDictionary(entry => entry.Id);
        items.AddRange(settlementExceptions.Select(entry =>
        {
            var batch = settlementBatchesById[entry.PaymentSettlementBatchId];
            var channel = settlementChannelsById[batch.PaymentChannelConfigurationId];
            return new InternalExceptionQueueItemView(
                "PAYMENT_SETTLEMENT",
                entry.ExceptionCode,
                "PAYMENT_SETTLEMENT_EXCEPTION",
                entry.Id,
                $"{batch.BatchNumber}:{entry.SourceDocumentNumber}",
                channel.BranchId,
                partiesById.GetValueOrDefault(entry.PartyId ?? 0L),
                entry.StatusCode,
                batch.SettlementDate,
                Math.Abs(entry.DifferenceAmount),
                entry.Notes ?? "Payment settlement exception requires finance review.",
                channel.ChannelCode);
        }));

        items.AddRange(BuildIslamicFinanceExceptionQueue(partiesById));

        return items
            .Where(item => normalizedSourceType is null || string.Equals(item.SourceType, normalizedSourceType, StringComparison.OrdinalIgnoreCase))
            .OrderByDescending(item => item.BusinessDate)
            .ThenBy(item => item.SourceType)
            .ThenBy(item => item.DocumentNumber)
            .ToArray();
    }

    private IReadOnlyCollection<InternalApprovalQueueItemView> BuildIslamicFinanceApprovalQueue(
        IReadOnlyDictionary<long, string> partiesById,
        IReadOnlyDictionary<long, string> usersById,
        string? normalizedModuleCode)
    {
        if (normalizedModuleCode is not null && !string.Equals(normalizedModuleCode, FinanceModuleCode, StringComparison.OrdinalIgnoreCase))
        {
            return Array.Empty<InternalApprovalQueueItemView>();
        }

        var queueRows = new List<InternalApprovalQueueItemView>();
        var requestNumbersById = _dbContext.IslamicFinanceRequests
            .AsNoTracking()
            .ToArray()
            .ToDictionary(entry => entry.Id, entry => entry.RequestNumber);

        queueRows.AddRange(_dbContext.IslamicFinancePartnerLenders
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(11, entry.Id),
                FinanceModuleCode,
                IslamicFinancePartnerLenderDocumentType,
                entry.Id,
                partiesById.GetValueOrDefault(entry.PartnerLenderPartyId) ?? $"IF-PL-{entry.Id:D6}",
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                partiesById.GetValueOrDefault(entry.PartnerLenderPartyId),
                null)));

        queueRows.AddRange(_dbContext.IslamicFinanceProductProfiles
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(12, entry.Id),
                FinanceModuleCode,
                IslamicFinanceProductProfileDocumentType,
                entry.Id,
                entry.ProductCode,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                null,
                null)));

        queueRows.AddRange(_dbContext.IslamicFinanceContractTemplates
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(13, entry.Id),
                FinanceModuleCode,
                IslamicFinanceContractTemplateDocumentType,
                entry.Id,
                entry.TemplateCode,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                null,
                null)));

        queueRows.AddRange(_dbContext.IslamicFinanceFacilities
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(14, entry.Id),
                FinanceModuleCode,
                IslamicFinanceFacilityDocumentType,
                entry.Id,
                entry.FacilityNumber,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                entry.ApprovedLimitAmount)));

        queueRows.AddRange(_dbContext.IslamicFinanceRequests
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(15, entry.Id),
                FinanceModuleCode,
                IslamicFinanceRequestDocumentType,
                entry.Id,
                entry.RequestNumber,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                entry.RequestedAmount)));

        queueRows.AddRange(_dbContext.IslamicFinanceDecisions
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                BuildSyntheticQueueId(16, entry.Id),
                FinanceModuleCode,
                IslamicFinanceDecisionDocumentType,
                entry.Id,
                BuildDecisionDocumentNumber(requestNumbersById, entry.FinancingRequestId, entry.Id),
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                entry.DecidingPartyId.HasValue ? partiesById.GetValueOrDefault(entry.DecidingPartyId.Value) : null,
                null)));

        queueRows.AddRange(_dbContext.IslamicFinanceShariahReviewCases
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(17, entry.Id),
                FinanceModuleCode,
                IslamicFinanceShariahReviewDocumentType,
                entry.Id,
                entry.ReviewCaseNumber,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                ResolveRequestPartyCode(entry.FinancingRequestId, partiesById),
                ResolveRequestAmount(entry.FinancingRequestId))));

        queueRows.AddRange(_dbContext.IslamicFinanceMurabahaCases
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(18, entry.Id),
                FinanceModuleCode,
                IslamicFinanceMurabahaCaseDocumentType,
                entry.Id,
                entry.CaseNumber,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                entry.SalePriceAmount)));

        queueRows.AddRange(_dbContext.IslamicFinanceMurabahaDisbursements
            .AsNoTracking()
            .Where(entry => entry.StatusCode == IslamicFinanceWorkflowCatalog.Submitted)
            .ToArray()
            .Select(entry => new InternalApprovalQueueItemView(
                entry.WorkflowInstanceId ?? BuildSyntheticQueueId(19, entry.Id),
                FinanceModuleCode,
                IslamicFinanceMurabahaDisbursementDocumentType,
                entry.Id,
                entry.DisbursementNumber,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveSubmittedBy(usersById, entry.SubmittedByUserId),
                entry.SubmittedAtUtc ?? entry.CreatedAtUtc,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                entry.InstructedAmount)));

        return queueRows;
    }

    private IReadOnlyCollection<InternalExceptionQueueItemView> BuildIslamicFinanceExceptionQueue(
        IReadOnlyDictionary<long, string> partiesById)
    {
        var items = new List<InternalExceptionQueueItemView>();
        var requestsById = _dbContext.IslamicFinanceRequests
            .AsNoTracking()
            .ToArray()
            .ToDictionary(entry => entry.Id);
        var requestNumbersById = requestsById.ToDictionary(item => item.Key, item => item.Value.RequestNumber);

        items.AddRange(requestsById.Values
            .Where(entry =>
                entry.StatusCode == IslamicFinanceWorkflowCatalog.Returned
                || entry.StatusCode == IslamicFinanceWorkflowCatalog.Rejected)
            .Select(entry => new InternalExceptionQueueItemView(
                IslamicFinanceExceptionSourceType,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                IslamicFinanceRequestDocumentType,
                entry.Id,
                entry.RequestNumber,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveRequestExceptionDate(entry),
                entry.RequestedAmount,
                string.Equals(entry.StatusCode, IslamicFinanceWorkflowCatalog.Returned, StringComparison.OrdinalIgnoreCase)
                    ? "Islamic Finance financing request was returned and requires rework."
                    : "Islamic Finance financing request was rejected and requires finance follow-up.",
                $"{entry.SourceDocumentType}:{entry.SourceDocumentId}")));

        items.AddRange(_dbContext.IslamicFinanceDecisions
            .AsNoTracking()
            .Where(entry =>
                entry.StatusCode == IslamicFinanceWorkflowCatalog.Returned
                || entry.StatusCode == IslamicFinanceWorkflowCatalog.Rejected)
            .ToArray()
            .Select(entry =>
            {
                requestsById.TryGetValue(entry.FinancingRequestId, out var request);
                return new InternalExceptionQueueItemView(
                    IslamicFinanceExceptionSourceType,
                    IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                    IslamicFinanceDecisionDocumentType,
                    entry.Id,
                    BuildDecisionDocumentNumber(requestNumbersById, entry.FinancingRequestId, entry.Id),
                    null,
                    request is null ? null : partiesById.GetValueOrDefault(request.CustomerPartyId),
                    IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                    ResolveDecisionExceptionDate(entry),
                    request?.RequestedAmount,
                    string.Equals(entry.StatusCode, IslamicFinanceWorkflowCatalog.Returned, StringComparison.OrdinalIgnoreCase)
                        ? "Islamic Finance financing decision was returned and requires resubmission."
                        : "Islamic Finance financing decision was rejected and requires review.",
                    IslamicFinanceDecisionCatalog.NormalizeDecisionCode(entry.DecisionCode));
            }));

        items.AddRange(_dbContext.IslamicFinanceShariahReviewCases
            .AsNoTracking()
            .Where(entry =>
                entry.StatusCode == IslamicFinanceWorkflowCatalog.Returned
                || entry.StatusCode == IslamicFinanceWorkflowCatalog.Rejected)
            .ToArray()
            .Select(entry =>
            {
                Domain.IslamicFinance.Requests.IslamicFinanceRequest? request = null;
                if (entry.FinancingRequestId.HasValue)
                {
                    requestsById.TryGetValue(entry.FinancingRequestId.Value, out request);
                }

                return new InternalExceptionQueueItemView(
                    IslamicFinanceExceptionSourceType,
                    IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                    IslamicFinanceShariahReviewDocumentType,
                    entry.Id,
                    entry.ReviewCaseNumber,
                    null,
                    request is null ? null : partiesById.GetValueOrDefault(request.CustomerPartyId),
                    IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                    entry.ApprovedAtUtc.HasValue
                        ? DateOnly.FromDateTime(entry.ApprovedAtUtc.Value)
                        : DateOnly.FromDateTime(entry.UpdatedAtUtc ?? entry.CreatedAtUtc),
                    request?.RequestedAmount,
                    string.Equals(entry.StatusCode, IslamicFinanceWorkflowCatalog.Returned, StringComparison.OrdinalIgnoreCase)
                        ? "Islamic Finance Shariah review was returned and needs corrective action."
                        : "Islamic Finance Shariah review was rejected and blocks case continuation.",
                    entry.ReviewOutcomeCode);
            }));

        items.AddRange(_dbContext.IslamicFinanceMurabahaDisbursements
            .AsNoTracking()
            .Where(entry =>
                entry.StatusCode == IslamicFinanceWorkflowCatalog.Returned
                || entry.StatusCode == IslamicFinanceWorkflowCatalog.Rejected
                || entry.StatusCode == IslamicFinanceWorkflowCatalog.Suspended)
            .ToArray()
            .Select(entry => new InternalExceptionQueueItemView(
                IslamicFinanceExceptionSourceType,
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                IslamicFinanceMurabahaDisbursementDocumentType,
                entry.Id,
                entry.DisbursementNumber,
                null,
                partiesById.GetValueOrDefault(entry.CustomerPartyId),
                IslamicFinanceWorkflowCatalog.NormalizeStatusCode(entry.StatusCode),
                ResolveDisbursementExceptionDate(entry),
                entry.InstructedAmount,
                BuildDisbursementExceptionSummary(entry.StatusCode),
                entry.ExecutionReferenceNumber ?? entry.SuspensionReasonCode)));

        return items;
    }

    public IReadOnlyCollection<InternalOperationalAlertView> GetOperationalAlerts(string? severityCode = null, string? sourceArea = null)
    {
        var normalizedSeverityCode = string.IsNullOrWhiteSpace(severityCode) ? null : severityCode.Trim().ToUpperInvariant();
        var normalizedSourceArea = string.IsNullOrWhiteSpace(sourceArea) ? null : sourceArea.Trim().ToUpperInvariant();
        var metrics = GetOperationalMetrics();

        return BuildOperationalAlerts(metrics)
            .Where(item => normalizedSeverityCode is null || string.Equals(item.SeverityCode, normalizedSeverityCode, StringComparison.OrdinalIgnoreCase))
            .Where(item => normalizedSourceArea is null || string.Equals(item.SourceArea, normalizedSourceArea, StringComparison.OrdinalIgnoreCase))
            .OrderBy(item => SeverityRank(item.SeverityCode))
            .ThenBy(item => item.SourceArea)
            .ThenBy(item => item.AlertCode)
            .ToArray();
    }

    private ApprovalMetadata? TryGetApprovalMetadata(
        string documentType,
        long documentId,
        IReadOnlyDictionary<long, string> partiesById)
    {
        return documentType switch
        {
            "PURCHASE_REQUISITION" => _dbContext.PurchaseRequisitions
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .Select(entry => new ApprovalMetadata(entry.RequisitionNumber, entry.BranchId, null, entry.EstimatedTotalAmount))
                .SingleOrDefault(),
            "RFQ" => _dbContext.RequestForQuotations
                .AsNoTracking()
                .Include(entry => entry.Lines)
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RfqNumber,
                    entry.BranchId,
                    null,
                    entry.Lines.Sum(line => line.RequestedQuantity * (line.TargetUnitPrice ?? 0m))))
                .SingleOrDefault(),
            "SALES_ORDER" => _dbContext.SalesOrders
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.SalesOrderNumber, entry.BranchId, partiesById.GetValueOrDefault(entry.CustomerId), entry.TotalAmount))
                .SingleOrDefault(),
            "PRICING_RULE" => _dbContext.PricingRules
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RuleCode,
                    entry.BranchId,
                    partiesById.GetValueOrDefault(entry.CustomerId ?? 0L),
                    entry.FixedUnitPrice))
                .SingleOrDefault(),
            "DISCOUNT_OVERRIDE" => _dbContext.SalesDiscountOverrides
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry =>
                {
                    var salesOrder = _dbContext.SalesOrders
                        .AsNoTracking()
                        .SingleOrDefault(order => order.Id == entry.SalesOrderId);
                    return salesOrder is null
                        ? null
                        : new ApprovalMetadata(
                            entry.OverrideNumber,
                            salesOrder.BranchId,
                            partiesById.GetValueOrDefault(salesOrder.CustomerId),
                            entry.RequestedDiscountAmount);
                })
                .SingleOrDefault(),
            "NOTIFICATION_TEMPLATE" => _dbContext.NotificationTemplates
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.TemplateCode, null, null, null))
                .SingleOrDefault(),
            "AR_INVOICE" => _dbContext.AccountsReceivableInvoices
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.InvoiceNumber,
                    ResolveInvoiceBranchId(entry.SalesOrderId),
                    partiesById.GetValueOrDefault(entry.CustomerId),
                    entry.TotalAmount))
                .SingleOrDefault(),
            "AR_RECEIPT" => _dbContext.AccountsReceivableReceipts
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.ReceiptNumber, null, partiesById.GetValueOrDefault(entry.CustomerId), entry.AmountAmount))
                .SingleOrDefault(),
            "CUSTOMER_REFUND" => _dbContext.CustomerRefunds
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.RefundNumber, null, partiesById.GetValueOrDefault(entry.CustomerId), entry.AmountAmount))
                .SingleOrDefault(),
            "PAYMENT_SETTLEMENT_BATCH" => _dbContext.PaymentSettlementBatches
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry =>
                {
                    var channel = _dbContext.PaymentChannelConfigurations
                        .AsNoTracking()
                        .SingleOrDefault(channelEntry => channelEntry.Id == entry.PaymentChannelConfigurationId);
                    return new ApprovalMetadata(
                        entry.BatchNumber,
                        channel?.BranchId,
                        null,
                        entry.TotalNetAmount);
                })
                .SingleOrDefault(),
            "AP_PAYMENT" => _dbContext.AccountsPayablePayments
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.PaymentNumber, null, partiesById.GetValueOrDefault(entry.SupplierId), entry.AmountAmount))
                .SingleOrDefault(),
            "PURCHASE_RETURN" => _dbContext.PurchaseReturns
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.PurchaseReturnNumber, entry.BranchId, partiesById.GetValueOrDefault(entry.SupplierId), null))
                .SingleOrDefault(),
            "SALES_RETURN" => _dbContext.SalesReturns
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.SalesReturnNumber, entry.BranchId, partiesById.GetValueOrDefault(entry.CustomerId), null))
                .SingleOrDefault(),
            "INV_ADJUSTMENT" => _dbContext.InventoryAdjustments
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.AdjustmentNumber, entry.BranchId, null, entry.TotalAdjustmentAmount))
                .SingleOrDefault(),
            "INV_STOCK_COUNT" => _dbContext.StockCounts
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.CountNumber, entry.BranchId, null, entry.TotalVarianceAmount))
                .SingleOrDefault(),
            "INV_TRANSFER" => _dbContext.WarehouseTransfers
                .AsNoTracking()
                .Include(entry => entry.Lines)
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.TransferNumber,
                    entry.BranchId,
                    null,
                    entry.Lines.Sum(line => line.QuantityTransferred * (line.UnitCost ?? 0m))))
                .SingleOrDefault(),
            "REPLENISHMENT_RULE" => _dbContext.ReplenishmentRules
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RuleCode,
                    entry.BranchId,
                    null,
                    entry.TargetStockQuantity))
                .SingleOrDefault(),
            "FIN_CLOSE_JOURNAL" => _dbContext.FinanceCloseJournals
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(entry.CloseJournalNumber, entry.BranchId, null, entry.TotalDebitAmount))
                .SingleOrDefault(),
            "INTEGRATION_ENDPOINT" => _dbContext.IntegrationEndpointRegistryEntries
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.EndpointCode,
                    null,
                    null,
                    null))
                .SingleOrDefault(),
            "FX_OVERRIDE" => _dbContext.ForeignExchangeOverrides
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.OverrideNumber,
                    null,
                    null,
                    entry.OverrideRateToBase))
                .SingleOrDefault(),
            "FX_REVALUATION" => _dbContext.ForeignExchangeRevaluations
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RevaluationNumber,
                    null,
                    null,
                    Math.Abs(entry.NetVarianceBaseAmount)))
                .SingleOrDefault(),
            "CARD_TOKEN_REFERENCE" => _dbContext.CardTokenReferences
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.CardTokenReferenceNumber,
                    null,
                    null,
                    null))
                .SingleOrDefault(),
            "PAYMENT_CHANNEL" => _dbContext.PaymentChannelConfigurations
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.ChannelCode,
                    entry.BranchId,
                    null,
                    entry.MaximumAmount ?? entry.MinimumAmount))
                .SingleOrDefault(),
            "WALLET_RULE" => _dbContext.WalletRules
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RuleCode,
                    entry.BranchId,
                    null,
                    entry.MaximumAmount ?? entry.MinimumAmount))
                .SingleOrDefault(),
            "FIN_ACCOUNTING_RULE" => _dbContext.FinanceAccountingRules
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RuleCode,
                    null,
                    null,
                    null))
                .SingleOrDefault(),
            "REFERENCE_DATA_ENTRY" => _dbContext.ReferenceDataEntries
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.ReferenceCode,
                    TryResolveBranchId(entry.AttributesJson),
                    null,
                    null))
                .SingleOrDefault(),
            IslamicFinancePartnerLenderDocumentType => _dbContext.IslamicFinancePartnerLenders
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    partiesById.GetValueOrDefault(entry.PartnerLenderPartyId) ?? $"IF-PL-{entry.Id:D6}",
                    null,
                    partiesById.GetValueOrDefault(entry.PartnerLenderPartyId),
                    null))
                .SingleOrDefault(),
            IslamicFinanceProductProfileDocumentType => _dbContext.IslamicFinanceProductProfiles
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .Select(entry => new ApprovalMetadata(entry.ProductCode, null, null, null))
                .SingleOrDefault(),
            IslamicFinanceContractTemplateDocumentType => _dbContext.IslamicFinanceContractTemplates
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .Select(entry => new ApprovalMetadata(entry.TemplateCode, null, null, null))
                .SingleOrDefault(),
            IslamicFinanceFacilityDocumentType => _dbContext.IslamicFinanceFacilities
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.FacilityNumber,
                    null,
                    partiesById.GetValueOrDefault(entry.CustomerPartyId),
                    entry.ApprovedLimitAmount))
                .SingleOrDefault(),
            IslamicFinanceRequestDocumentType => _dbContext.IslamicFinanceRequests
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.RequestNumber,
                    null,
                    partiesById.GetValueOrDefault(entry.CustomerPartyId),
                    entry.RequestedAmount))
                .SingleOrDefault(),
            IslamicFinanceShariahReviewDocumentType => _dbContext.IslamicFinanceShariahReviewCases
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.ReviewCaseNumber,
                    null,
                    ResolveRequestPartyCode(entry.FinancingRequestId, partiesById),
                    ResolveRequestAmount(entry.FinancingRequestId)))
                .SingleOrDefault(),
            IslamicFinanceMurabahaCaseDocumentType => _dbContext.IslamicFinanceMurabahaCases
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.CaseNumber,
                    null,
                    partiesById.GetValueOrDefault(entry.CustomerPartyId),
                    entry.SalePriceAmount))
                .SingleOrDefault(),
            IslamicFinanceMurabahaDisbursementDocumentType => _dbContext.IslamicFinanceMurabahaDisbursements
                .AsNoTracking()
                .Where(entry => entry.Id == documentId)
                .ToArray()
                .Select(entry => new ApprovalMetadata(
                    entry.DisbursementNumber,
                    null,
                    partiesById.GetValueOrDefault(entry.CustomerPartyId),
                    entry.InstructedAmount))
                .SingleOrDefault(),
            _ => null
        };
    }

    private string? ResolveRequestPartyCode(long? financingRequestId, IReadOnlyDictionary<long, string> partiesById)
    {
        if (!financingRequestId.HasValue)
        {
            return null;
        }

        var request = _dbContext.IslamicFinanceRequests
            .AsNoTracking()
            .SingleOrDefault(entry => entry.Id == financingRequestId.Value);

        return request is null ? null : partiesById.GetValueOrDefault(request.CustomerPartyId);
    }

    private decimal? ResolveRequestAmount(long? financingRequestId)
    {
        if (!financingRequestId.HasValue)
        {
            return null;
        }

        return _dbContext.IslamicFinanceRequests
            .AsNoTracking()
            .Where(entry => entry.Id == financingRequestId.Value)
            .Select(entry => (decimal?)entry.RequestedAmount)
            .SingleOrDefault();
    }

    private static long BuildSyntheticQueueId(long typeDiscriminator, long documentId) =>
        (typeDiscriminator * 1_000_000_000_000L) + documentId;

    private static string ResolveSubmittedBy(IReadOnlyDictionary<long, string> usersById, long? userId) =>
        userId.HasValue && usersById.TryGetValue(userId.Value, out var loginName)
            ? loginName
            : "UNKNOWN";

    private static string BuildDecisionDocumentNumber(
        IReadOnlyDictionary<long, string> requestNumbersById,
        long financingRequestId,
        long decisionId) =>
        requestNumbersById.TryGetValue(financingRequestId, out var requestNumber)
            ? $"{requestNumber}/DEC-{decisionId:D6}"
            : $"IF-DEC-{decisionId:D6}";

    private static DateOnly ResolveRequestExceptionDate(Domain.IslamicFinance.Requests.IslamicFinanceRequest request) =>
        request.ReturnedAtUtc.HasValue
            ? DateOnly.FromDateTime(request.ReturnedAtUtc.Value)
            : request.RejectedAtUtc.HasValue
                ? DateOnly.FromDateTime(request.RejectedAtUtc.Value)
                : DateOnly.FromDateTime(request.UpdatedAtUtc ?? request.CreatedAtUtc);

    private static DateOnly ResolveDecisionExceptionDate(Domain.IslamicFinance.Requests.IslamicFinanceDecision decision) =>
        decision.ReturnedAtUtc.HasValue
            ? DateOnly.FromDateTime(decision.ReturnedAtUtc.Value)
            : decision.RejectedAtUtc.HasValue
                ? DateOnly.FromDateTime(decision.RejectedAtUtc.Value)
                : DateOnly.FromDateTime(decision.UpdatedAtUtc ?? decision.CreatedAtUtc);

    private static DateOnly ResolveDisbursementExceptionDate(Domain.IslamicFinance.Disbursements.IslamicFinanceMurabahaDisbursement disbursement) =>
        disbursement.SuspendedAtUtc.HasValue
            ? DateOnly.FromDateTime(disbursement.SuspendedAtUtc.Value)
            : DateOnly.FromDateTime(disbursement.UpdatedAtUtc ?? disbursement.CreatedAtUtc);

    private static string BuildDisbursementExceptionSummary(string statusCode) =>
        IslamicFinanceWorkflowCatalog.NormalizeStatusCode(statusCode) switch
        {
            IslamicFinanceWorkflowCatalog.Suspended => "Islamic Finance Murabaha disbursement is suspended and requires operations review.",
            IslamicFinanceWorkflowCatalog.Returned => "Islamic Finance Murabaha disbursement was returned and requires rework.",
            IslamicFinanceWorkflowCatalog.Rejected => "Islamic Finance Murabaha disbursement was rejected and requires finance follow-up.",
            _ => "Islamic Finance Murabaha disbursement requires review."
        };

    private long? ResolveInvoiceBranchId(long? salesOrderId)
    {
        if (!salesOrderId.HasValue)
        {
            return null;
        }

        return _dbContext.SalesOrders
            .AsNoTracking()
            .Where(order => order.Id == salesOrderId.Value)
            .Select(order => (long?)order.BranchId)
            .SingleOrDefault();
    }

    private OperationalMetrics GetOperationalMetrics()
    {
        var dashboardSummary = GetDashboardSummary();

        var pendingOutboxCount = _dbContext.OutboxEvents.AsNoTracking().Count(entry => entry.StatusCode == "PENDING");
        var retryOutboxCount = _dbContext.OutboxEvents.AsNoTracking().Count(entry => entry.StatusCode == "RETRY");
        var deadLetterOutboxCount = _dbContext.OutboxEvents.AsNoTracking().Count(entry => entry.StatusCode == "DEAD_LETTER");

        var receivedInboxCount = _dbContext.InboxMessages.AsNoTracking().Count(entry => entry.StatusCode == "RECEIVED");
        var retryInboxCount = _dbContext.InboxMessages.AsNoTracking().Count(entry => entry.StatusCode == "RETRY");
        var deadLetterInboxCount = _dbContext.InboxMessages.AsNoTracking().Count(entry => entry.StatusCode == "DEAD_LETTER");

        var pendingNotificationCount = _dbContext.NotificationQueueItems.AsNoTracking().Count(entry => entry.StatusCode == "PENDING");
        var retryNotificationCount = _dbContext.NotificationQueueItems.AsNoTracking().Count(entry => entry.StatusCode == "RETRY");
        var failedNotificationCount = _dbContext.NotificationQueueItems.AsNoTracking().Count(entry => entry.StatusCode == "FAILED");
        var deadLetterNotificationCount = _dbContext.NotificationQueueItems.AsNoTracking().Count(entry => entry.StatusCode == "DEAD_LETTER");

        var openSettlementExceptionCount = _dbContext.PaymentSettlementExceptions.AsNoTracking().Count(entry => entry.StatusCode == "OPEN");
        var overdueCollectionsCount = GetOverdueCollectionsCount(DateOnly.FromDateTime(DateTime.UtcNow));

        return new OperationalMetrics(
            dashboardSummary.PendingApprovalCount,
            dashboardSummary.TotalExceptionCount,
            dashboardSummary.ReconciliationExceptionCount,
            dashboardSummary.EtaAttentionCount,
            dashboardSummary.CreditHoldCount,
            openSettlementExceptionCount,
            pendingOutboxCount,
            retryOutboxCount,
            deadLetterOutboxCount,
            receivedInboxCount,
            retryInboxCount,
            deadLetterInboxCount,
            pendingNotificationCount,
            retryNotificationCount,
            failedNotificationCount,
            deadLetterNotificationCount,
            overdueCollectionsCount);
    }

    private int GetOverdueCollectionsCount(DateOnly asOfDate)
    {
        var invoices = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Where(entry => entry.StatusId == WorkflowStatusSeedIds.Posted && entry.DueDate < asOfDate)
            .Select(entry => new
            {
                entry.Id,
                entry.TotalAmount
            })
            .ToArray();

        if (invoices.Length == 0)
        {
            return 0;
        }

        var postedReceiptAllocations = _dbContext.AccountsReceivableReceiptAllocations
            .AsNoTracking()
            .ToArray()
            .Join(
                _dbContext.AccountsReceivableReceipts
                    .AsNoTracking()
                    .Where(receipt => receipt.StatusId == WorkflowStatusSeedIds.Posted)
                    .ToArray(),
                allocation => allocation.AccountsReceivableReceiptId,
                receipt => receipt.Id,
                (allocation, _) => allocation)
            .GroupBy(allocation => allocation.AccountsReceivableInvoiceId)
            .Select(group => new
            {
                InvoiceId = group.Key,
                AllocatedAmount = group.Sum(entry => entry.AllocationAmount)
            })
            .ToArray()
            .ToDictionary(entry => entry.InvoiceId, entry => entry.AllocatedAmount);

        return invoices.Count(invoice => invoice.TotalAmount - postedReceiptAllocations.GetValueOrDefault(invoice.Id) > 0m);
    }

    private static IReadOnlyCollection<InternalOperationalAlertView> BuildOperationalAlerts(OperationalMetrics metrics)
    {
        var alerts = new List<InternalOperationalAlertView>();

        AddAlertIfNeeded(alerts, metrics.DeadLetterOutboxCount > 0,
            new InternalOperationalAlertView(
                "INTEGRATION",
                "CRITICAL",
                "OUTBOX_DEAD_LETTER",
                "Outbox dead-letter backlog",
                "One or more outbox events are dead-lettered and require replay or corrective action.",
                "OPEN",
                metrics.DeadLetterOutboxCount,
                0m,
                "Review dead-lettered outbox events.",
                "/api/v1/integrations/dead-letters?queueType=OUTBOX"));

        AddAlertIfNeeded(alerts, metrics.DeadLetterInboxCount > 0,
            new InternalOperationalAlertView(
                "INTEGRATION",
                "CRITICAL",
                "INBOX_DEAD_LETTER",
                "Inbox dead-letter backlog",
                "Inbound integration messages are dead-lettered and require replay or handler correction.",
                "OPEN",
                metrics.DeadLetterInboxCount,
                0m,
                "Review dead-lettered inbox messages.",
                "/api/v1/integrations/dead-letters?queueType=INBOX"));

        AddAlertIfNeeded(alerts, metrics.DeadLetterNotificationCount > 0,
            new InternalOperationalAlertView(
                "NOTIFICATIONS",
                "HIGH",
                "NOTIFICATION_DEAD_LETTER",
                "Notification dead-letter backlog",
                "Outbound notifications are dead-lettered and require operations review.",
                "OPEN",
                metrics.DeadLetterNotificationCount,
                0m,
                "Review dead-lettered notification queue items.",
                "/api/v1/notifications/queue?statusCode=DEAD_LETTER"));

        AddAlertIfNeeded(alerts, metrics.OpenSettlementExceptionCount > 0,
            new InternalOperationalAlertView(
                "FINANCE",
                "HIGH",
                "SETTLEMENT_EXCEPTION_OPEN",
                "Open settlement exceptions",
                "Payment settlement exceptions remain unresolved and can block settlement posting.",
                "OPEN",
                metrics.OpenSettlementExceptionCount,
                0m,
                "Review open settlement exception items.",
                "/api/v1/finance/payment-settlement-exceptions?statusCode=OPEN"));

        AddAlertIfNeeded(alerts, metrics.OpenReconciliationExceptionCount > 0,
            new InternalOperationalAlertView(
                "FINANCE",
                "HIGH",
                "RECONCILIATION_EXCEPTION_OPEN",
                "Open reconciliation exceptions",
                "Bank reconciliation exceptions remain unresolved and require finance review.",
                "OPEN",
                metrics.OpenReconciliationExceptionCount,
                0m,
                "Review reconciliation exception queue items.",
                "/api/v1/finance/reconciliations/exception-queue"));

        AddAlertIfNeeded(alerts, metrics.RetryOutboxCount > 0,
            new InternalOperationalAlertView(
                "INTEGRATION",
                "WARNING",
                "OUTBOX_RETRY_BACKLOG",
                "Outbox retry backlog",
                "Some outbox events are waiting for retry processing.",
                "OPEN",
                metrics.RetryOutboxCount,
                0m,
                "Review retrying outbox events.",
                "/api/v1/integrations/outbox?statusCode=RETRY"));

        AddAlertIfNeeded(alerts, metrics.RetryInboxCount > 0,
            new InternalOperationalAlertView(
                "INTEGRATION",
                "WARNING",
                "INBOX_RETRY_BACKLOG",
                "Inbox retry backlog",
                "Inbound messages are waiting for retry processing.",
                "OPEN",
                metrics.RetryInboxCount,
                0m,
                "Review retrying inbox messages.",
                "/api/v1/integrations/inbox?statusCode=RETRY"));

        AddAlertIfNeeded(alerts, metrics.PendingNotificationCount + metrics.RetryNotificationCount + metrics.FailedNotificationCount >= 5,
            new InternalOperationalAlertView(
                "NOTIFICATIONS",
                "WARNING",
                "NOTIFICATION_DELIVERY_BACKLOG",
                "Notification delivery backlog",
                "Notification queue backlog has reached the warning threshold.",
                "OPEN",
                metrics.PendingNotificationCount + metrics.RetryNotificationCount + metrics.FailedNotificationCount,
                5m,
                "Review pending, retry, and failed notification items.",
                "/api/v1/notifications/queue"));

        AddAlertIfNeeded(alerts, metrics.EtaAttentionCount > 0,
            new InternalOperationalAlertView(
                "COMPLIANCE",
                "WARNING",
                "ETA_ATTENTION_REQUIRED",
                "ETA attention required",
                "Posted ETA-eligible invoices remain unresolved or not yet accepted.",
                "OPEN",
                metrics.EtaAttentionCount,
                0m,
                "Review ETA attention items.",
                "/api/v1/internal/work-queues/exceptions?sourceType=ETA"));

        AddAlertIfNeeded(alerts, metrics.CreditHoldCount > 0,
            new InternalOperationalAlertView(
                "SALES",
                "WARNING",
                "CREDIT_HOLD_OPEN",
                "Customer credit holds open",
                "Customer credit holds remain unresolved and can block fulfillment.",
                "OPEN",
                metrics.CreditHoldCount,
                0m,
                "Review credit-control exception items.",
                "/api/v1/internal/work-queues/exceptions?sourceType=CREDIT_CONTROL"));

        AddAlertIfNeeded(alerts, metrics.OverdueCollectionsCount > 0,
            new InternalOperationalAlertView(
                "COLLECTIONS",
                "WARNING",
                "OVERDUE_COLLECTIONS_ACTION",
                "Overdue collections require action",
                "Overdue posted AR invoices remain outstanding and require collections follow-up.",
                "OPEN",
                metrics.OverdueCollectionsCount,
                0m,
                "Review overdue collections queue items.",
                "/api/v1/finance/collections/queue?actionRequiredOnly=true"));

        return alerts;
    }

    private static void AddAlertIfNeeded(List<InternalOperationalAlertView> alerts, bool shouldAdd, InternalOperationalAlertView alert)
    {
        if (shouldAdd)
        {
            alerts.Add(alert);
        }
    }

    private static int SeverityRank(string severityCode) =>
        severityCode.ToUpperInvariant() switch
        {
            "CRITICAL" => 0,
            "HIGH" => 1,
            "WARNING" => 2,
            _ => 3
        };

    private static long? TryResolveBranchId(string? attributesJson)
    {
        if (string.IsNullOrWhiteSpace(attributesJson))
        {
            return null;
        }

        try
        {
            using var document = System.Text.Json.JsonDocument.Parse(attributesJson);
            if (document.RootElement.TryGetProperty("branchId", out var branchIdElement)
                && branchIdElement.ValueKind == System.Text.Json.JsonValueKind.Number
                && branchIdElement.TryGetInt64(out var branchId))
            {
                return branchId;
            }
        }
        catch (System.Text.Json.JsonException)
        {
        }

        return null;
    }

    private sealed record OperationalMetrics(
        int PendingApprovalCount,
        int TotalExceptionCount,
        int OpenReconciliationExceptionCount,
        int EtaAttentionCount,
        int CreditHoldCount,
        int OpenSettlementExceptionCount,
        int PendingOutboxCount,
        int RetryOutboxCount,
        int DeadLetterOutboxCount,
        int ReceivedInboxCount,
        int RetryInboxCount,
        int DeadLetterInboxCount,
        int PendingNotificationCount,
        int RetryNotificationCount,
        int FailedNotificationCount,
        int DeadLetterNotificationCount,
        int OverdueCollectionsCount);

    private sealed record ApprovalMetadata(
        string DocumentNumber,
        long? BranchId,
        string? PartyCode,
        decimal? Amount);
}
