using GB5Shared.GOP.GBQueryExecutor;
using GB5Shared.GOP.GBQueryExecutor.Models;
using GB5Shared.GOP.GBQueryExecutor.DTOs;
using GB5Shared.DirectAction;
using GB5Shared.DTO.DirectAction;
using FrameworkBLL.DirectAction;
using FrameworkDAL.CustomCode.DirectAction;
using FrameworkDAL.CustomCode.MailTemplate;
using GB5Shared.ActionProcessor;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Telemetry;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;

namespace FrameworkBLL.ActionProcessor.Handlers
{
    /// <summary>
    /// ActionType = 0 — Email handler.
    ///
    /// Recipient resolution (SendTo / CC / BCC) uses DeliveryType from MACTION:
    ///   ToDeliveryType = 3  → direct email address, use as-is
    ///   ToDeliveryType = 2  → field name (e.g. "MailId") — resolve from EntityPayload first,
    ///                         then cascade to ContextBag (DB_ENRICH enriched values)
    ///   ToDeliveryType = 12 → EnrichQualifier: MACTION.SENDTO holds a MGBCONFIGUREDQUERY QueryCode;
    ///                         run it with entity-payload parameters → first result column IS the email
    ///   Same logic applies to MailCcDeliveryType and MailBccDeliveryType.
    ///
    /// ContextBag resolution:
    ///   When a field name (e.g. "MailId") is not found in the entity DTO payload, the handler
    ///   automatically looks it up in ActionEventDto.ContextJson → Bag, which contains every
    ///   value written by DB_ENRICH qualifier rules during the save pipeline (e.g. ReportingToEmployeeId,
    ///   EmployeeCode, MailId, DepartmentId). This makes recipient resolution fully dynamic and
    ///   independent of which fields happen to be present in the DTO itself.
    ///
    /// Template placeholders use ##FieldName## syntax (matching MMAILTEMPLATE.BODY).
    /// Values are substituted from the EntityPayload JSON object.
    ///
    /// If dto.DirectActionId > -1, action button URLs (##PLACEHOLDER##) are resolved from
    /// MDIRECTACTIONDETAIL and embedded in the body as signed /Action/Execute?token=... links.
    ///
    /// Flow:
    ///   1. Parse EntityPayload from the outer Payload JSON.
    ///   2. Parse ContextBag from ContextJson (DB_ENRICH bag values).
    ///   3. Resolve SendTo using ToDeliveryType → EntityPayload → ContextBag cascade.
    ///   4. Fetch Subject + Body from MMAILTEMPLATE (using dto.TemplateId).
    ///   5. Replace ##FieldName## tokens in Subject/Body using EntityPayload values.
    ///   6. If DirectActionId > -1, embed signed action URLs.
    ///   7. Resolve CC / BCC using same cascade.
    ///   8. Send via SMTP.
    /// </summary>
    public sealed class EmailActionHandler : IActionHandler
    {
        public int ActionType => 0;

        private readonly SmtpSettings                  _smtp;
        private readonly IMailTemplateDAL               _mailTemplateDAL;
        private readonly IDirectActionTokenService      _tokenService;
        private readonly IDirectActionConfigDAL         _configDAL;
        private readonly IGBQueryExecutor               _gbQueryExecutor;
        private readonly IConfiguration                 _config;
        private readonly GB5Shared.FileUpload.IFileUploadBLL _fileUploadBLL;
        private readonly ILogger<EmailActionHandler>    _logger;

        public EmailActionHandler(
            IOptions<SmtpSettings>          smtp,
            IMailTemplateDAL                mailTemplateDAL,
            IDirectActionTokenService       tokenService,
            IDirectActionConfigDAL          configDAL,
            IGBQueryExecutor                gbQueryExecutor,
            IConfiguration                  config,
            GB5Shared.FileUpload.IFileUploadBLL fileUploadBLL,
            ILogger<EmailActionHandler>     logger)
        {
            _smtp            = smtp.Value;
            _mailTemplateDAL = mailTemplateDAL;
            _tokenService    = tokenService;
            _configDAL       = configDAL;
            _gbQueryExecutor = gbQueryExecutor;
            _config          = config;
            _fileUploadBLL   = fileUploadBLL;
            _logger          = logger;
        }

        public async Task<ActionResult> HandleAsync(ActionEventDto dto, CancellationToken ct = default)
        {
            if (dto == null)
                return ActionResult.Permanent("ActionEventDto cannot be null");

            using var emailActivity = GB5ActivitySources.ActionProcessor.StartActivity(
                "action.email.send", ActivityKind.Internal);
            emailActivity?.SetTag("action.type",         "Email");
            emailActivity?.SetTag("gb5.action.run_id",   dto.ActionRunId);
            emailActivity?.SetTag("gb5.action.id",        dto.ActionId);
            emailActivity?.SetTag("gb5.tenant.id",        dto.TenantId);
            emailActivity?.SetTag("gb5.tenant.db",        dto.DatabaseName);
            emailActivity?.SetTag("email.template_id",   dto.TemplateId);
            emailActivity?.SetTag("email.to_config",     dto.SendTo ?? "(empty)");
            emailActivity?.SetTag("email.to_type",       dto.ToDeliveryType);
            emailActivity?.SetTag("email.cc_config",     dto.MailCc  ?? "(none)");
            emailActivity?.SetTag("email.smtp.host",     _smtp.Host);
            emailActivity?.SetTag("email.smtp.port",     _smtp.Port);

            _logger.LogInformation(
                "EmailActionHandler [START] | ActionRunId={RunId} TemplateId={Tid} SendTo={To} CC={CC}",
                dto.ActionRunId, dto.TemplateId, dto.SendTo ?? "(empty)", dto.MailCc ?? "(none)");

            // ── STEP 1: Parse EntityPayload from Payload ──────────────────────
            // Workflow outcome events (APPROVEWORKFLOW / REJECTWORKFLOW / RESENDWORKFLOW)
            // wrap the entity DTO two levels deep:
            //   dto.Payload.EntityPayload = workflow JSON  {"WorkflowInstanceId":..., "EntityPayload": "<DTO json>"}
            // Regular entity events have one level:
            //   dto.Payload.EntityPayload = DTO JSON  {"FieldA":..., "FieldB":...}
            // We unwrap up to two levels so ##FieldName## tokens always resolve against
            // the actual entity DTO fields regardless of event source.
            JsonDocument? entityDoc = null;
            string entityPayloadRaw = string.Empty;
            string entityParseStatus = "not_started";

            try
            {
                if (dto.Payload.ValueKind == JsonValueKind.Object &&
                    dto.Payload.TryGetProperty("EntityPayload", out var entityEl))
                {
                    entityPayloadRaw = entityEl.GetString() ?? string.Empty;
                    if (!string.IsNullOrWhiteSpace(entityPayloadRaw))
                    {
                        var firstDoc = JsonDocument.Parse(entityPayloadRaw);

                        // Workflow events: first parse is the outer workflow JSON which itself
                        // has an EntityPayload containing the actual DTO. Unwrap one more level.
                        if (firstDoc.RootElement.TryGetProperty("EntityPayload", out var innerEl))
                        {
                            var innerJson = innerEl.GetString() ?? string.Empty;
                            if (!string.IsNullOrWhiteSpace(innerJson))
                            {
                                firstDoc.Dispose();
                                entityDoc     = JsonDocument.Parse(innerJson);
                                entityParseStatus = "two-level-unwrap";
                                _logger.LogInformation(
                                    "EmailActionHandler [1/6] ✅ EntityPayload unwrapped (workflow event) | InnerLength={Len}",
                                    innerJson.Length);
                            }
                            else
                            {
                                entityDoc     = firstDoc;
                                entityParseStatus = "single-level";
                                _logger.LogInformation(
                                    "EmailActionHandler [1/6] ✅ EntityPayload parsed (single level) | Length={Len}",
                                    entityPayloadRaw.Length);
                            }
                        }
                        else
                        {
                            entityDoc     = firstDoc;
                            entityParseStatus = "single-level-no-inner";
                            _logger.LogInformation(
                                "EmailActionHandler [1/6] ✅ EntityPayload parsed | Length={Len}",
                                entityPayloadRaw.Length);
                        }
                    }
                    else
                    {
                        entityParseStatus = "entity-payload-empty";
                        _logger.LogWarning(
                            "EmailActionHandler [1/6] ⚠️ EntityPayload property exists but is empty — placeholder replacement will be skipped | ActionRunId={RunId}",
                            dto.ActionRunId);
                    }
                }
                else
                {
                    // Payload itself is the entity data (direct publish)
                    entityDoc     = JsonDocument.Parse(dto.Payload.GetRawText());
                    entityParseStatus = "direct-payload";
                    _logger.LogInformation("EmailActionHandler [1/6] ✅ Payload used directly as EntityPayload");
                }
            }
            catch (Exception ex)
            {
                entityParseStatus = "exception";
                _logger.LogWarning(ex, "EmailActionHandler [1/6] ⚠️ Could not parse EntityPayload — placeholder replacement will be skipped");
            }

            // ── STEP 1b: Unwrap GB5's standard API response envelope ──────────
            // Every GB5 webservice call (ResponseStandardDTO<T> / GB4ResponseStandardDTO<T>)
            // wraps the real entity DTO inside a "Body" property that is itself a
            // JSON-ENCODED STRING, alongside transport metadata (Id/To/From/Status/ETag/
            // Total/CacheLevel/CacheKey/...). Scheduler-triggered actions receive exactly
            // this envelope as their Payload (see SchedulerExecutionDeliveryService — the
            // raw HTTP response body), so without this step ##FieldName##/SendTo field-name
            // resolution only ever sees transport metadata and never the real entity data —
            // for ANY job whose webservice follows this platform-standard convention, not
            // just this one. Purely additive on top of whatever STEP 1 already produced;
            // the ContextBag/EntityPayload cascade below is untouched.
            if (entityDoc != null && TryUnwrapResponseEnvelope(entityDoc, out var unwrappedDoc))
            {
                entityDoc.Dispose();
                entityDoc = unwrappedDoc;
                entityParseStatus += "+envelope-unwrap";
                _logger.LogInformation(
                    "EmailActionHandler [1b/6] ✅ Unwrapped GB5 response envelope (Body) — real entity fields now available | ActionRunId={RunId}",
                    dto.ActionRunId);
            }

            // Entity parse diagnostic — visible in Zipkin on every action.email.send span.
            emailActivity?.SetTag("email.entity.parse_status", entityParseStatus);
            emailActivity?.SetTag("email.entity.field_count",
                entityDoc?.RootElement.EnumerateObject().Count() ?? 0);

            // ── STEP 2: Parse ContextBag ──────────────────────────────────────
            // The bag holds every DB_ENRICH qualifier value written during the save pipeline
            // (e.g. MailId, ReportingToEmployeeId, EmployeeCode, DepartmentId, WorkflowInfo).
            // Recipient fields configured in MACTION (e.g. SendTo="MailId") are looked up here
            // when they are absent from the entity DTO payload.
            IReadOnlyDictionary<string, string?> contextBag = ParseContextBag(dto.ContextJson, dto.ActionRunId);

            emailActivity?.SetTag("email.context.json_length",    dto.ContextJson?.Length ?? 0);
            emailActivity?.SetTag("email.context.bag_available",  contextBag.Count > 0);
            emailActivity?.SetTag("email.context.bag_key_count",  contextBag.Count);
            if (contextBag.Count > 0)
            {
                // Show enriched (non-DTO) keys as key=value for readable Zipkin debugging;
                // DTO.* fields are already visible via entity_field_count.
                var enrichPairs = contextBag
                    .Where(kvp => !kvp.Key.StartsWith("DTO.", StringComparison.OrdinalIgnoreCase))
                    .Select(kvp => $"{kvp.Key}={kvp.Value ?? "(null)"}");
                emailActivity?.SetTag("email.context.bag_keys",
                    string.Join("; ", enrichPairs));
                emailActivity?.SetTag("email.context.bag_dto_count",
                    contextBag.Keys.Count(k => k.StartsWith("DTO.", StringComparison.OrdinalIgnoreCase)));
            }
            else
            {
                // Visible warning in Zipkin: helps diagnose when DB_ENRICH did not run
                // or when ContextJson was not propagated from the save pipeline.
                emailActivity?.SetTag("email.context.bag_warning",
                    "ContextBag is empty — DB_ENRICH result missing; recipient will be resolved from EntityPayload or DB qualifier");
                _logger.LogWarning(
                    "EmailActionHandler [2/6] ⚠️ ContextBag EMPTY | ContextJsonLen={Len} — DB_ENRICH may not have run or ContextJson was not propagated | ActionRunId={RunId}",
                    dto.ContextJson?.Length ?? 0, dto.ActionRunId);
            }

            _logger.LogInformation(
                "EmailActionHandler [2/6] ContextBag | Available={Available} KeyCount={Count} Keys=[{Keys}]",
                contextBag.Count > 0, contextBag.Count, string.Join(", ", contextBag.Keys));

            // ── STEP 3: Resolve SendTo ────────────────────────────────────────
            var   login  = BuildLogin(dto);
            string? sendTo = await ResolveRecipientAsync(
                dto.SendTo, dto.ToDeliveryType, entityDoc, contextBag, "SendTo", dto.ActionRunId, login, ct)
                .ConfigureAwait(false);

            if (string.IsNullOrWhiteSpace(sendTo))
            {
                emailActivity?.SetTag("email.sent",     false);
                emailActivity?.SetTag("email.error",    "SendTo could not be resolved");
                emailActivity?.SetStatus(ActivityStatusCode.Error, "SendTo empty");
                _logger.LogError(
                    "EmailActionHandler [3/6] ❌ SendTo is empty and could not be resolved | DeliveryType={DT} BagAvailable={BagAvail} EntityFields={Fields} ActionRunId={RunId}",
                    dto.ToDeliveryType, contextBag.Count > 0, entityDoc?.RootElement.EnumerateObject().Count() ?? 0, dto.ActionRunId);
                return ActionResult.Permanent("SendTo could not be resolved — email not sent");
            }
            emailActivity?.SetTag("email.to_resolved", sendTo);

            _logger.LogInformation(
                "EmailActionHandler [3/6] ✅ SendTo resolved: {To}", sendTo);

            // ── STEP 4: Fetch mail template ───────────────────────────────────
            string subject = "GoodBooks ERP Notification";
            string body    = string.Empty;
            bool   isHtml  = false;

            if (dto.TemplateId != 0 && dto.TemplateId != -1)
            {
                try
                {
                    _logger.LogInformation(
                        "EmailActionHandler [4/6] Fetching template | TemplateId={Tid} DB={Db}",
                        dto.TemplateId, dto.DatabaseName);

                    var template = await _mailTemplateDAL.GetMailTemplate(dto.TemplateId, login)
                        .ConfigureAwait(false);

                    if (template == null)
                    {
                        _logger.LogWarning(
                            "EmailActionHandler [4/6] ⚠️ Template {Tid} not found in DB — will use Payload Subject/Body",
                            dto.TemplateId);
                    }
                    else
                    {
                        subject = template.MailTemplateSubject ?? subject;
                        body    = template.MailTemplateBody    ?? string.Empty;
                        isHtml  = true;

                        _logger.LogInformation(
                            "EmailActionHandler [4/6] ✅ Template loaded | Subject={Subject} BodyLength={Len}",
                            subject, body.Length);
                    }
                }
                catch (Exception ex)
                {
                    _logger.LogWarning(ex,
                        "EmailActionHandler [4/6] ⚠️ Template fetch failed for TemplateId={Tid} — will use Payload Subject/Body",
                        dto.TemplateId);
                }
            }
            else
            {
                _logger.LogInformation("EmailActionHandler [4/6] No TemplateId — reading Subject/Body from Payload");
            }

            // Payload fallback if template not loaded
            if (string.IsNullOrWhiteSpace(body) && dto.Payload.ValueKind == JsonValueKind.Object)
            {
                if (dto.Payload.TryGetProperty("Subject", out var subEl))
                    subject = subEl.GetString() ?? subject;
                if (dto.Payload.TryGetProperty("Body", out var bodyEl))
                    body = bodyEl.GetString() ?? body;
                if (dto.Payload.TryGetProperty("IsHtml", out var htmlEl))
                    isHtml = htmlEl.GetBoolean();
            }

            // Raw template snapshot — visible in Zipkin before any substitution runs.
            emailActivity?.SetTag("email.subject.raw",          subject);
            emailActivity?.SetTag("email.body.template_length", body.Length);

            // ── STEP 5: Replace ##Placeholders## / @Model.Field with values ──
            // Pass 1 — entity DTO fields (unwrapped in step 1)
            if (entityDoc != null)
            {
                _logger.LogInformation("EmailActionHandler [5/6] Pass1: replacing from EntityPayload | FieldCount={Count}",
                    entityDoc.RootElement.EnumerateObject().Count());
                subject = ReplacePlaceholders(subject, entityDoc.RootElement);
                body    = ReplacePlaceholders(body,    entityDoc.RootElement);
            }
            else
            {
                _logger.LogWarning("EmailActionHandler [5/6] Pass1 SKIPPED — entityDoc is null (parse_status={Status}) | ActionRunId={RunId}",
                    entityParseStatus, dto.ActionRunId);
            }

            emailActivity?.SetTag("email.replacement.pass1_ran",    entityDoc != null);
            emailActivity?.SetTag("email.subject.after_pass1",      subject);

            // Pass 2 — ContextBag fallback for any tokens still unreplaced.
            // Bag keys from DB_ENRICH are stored with a "DTO." prefix (e.g. "DTO.DisplayValue").
            // We match both the exact key (##DTO.DisplayValue##) and the stripped short form
            // (##DisplayValue##) so templates don't need to know the prefix convention.
            if (contextBag.Count > 0)
            {
                subject = ReplacePlaceholdersFromBag(subject, contextBag);
                body    = ReplacePlaceholdersFromBag(body,    contextBag);
            }
            else
            {
                _logger.LogWarning("EmailActionHandler [5/6] Pass2 SKIPPED — ContextBag is empty (json_length={Len}) | ActionRunId={RunId}",
                    dto.ContextJson?.Length ?? 0, dto.ActionRunId);
            }

            var tokensRemaining = CountHashTokens(subject) + CountHashTokens(body);
            emailActivity?.SetTag("email.replacement.pass2_ran",         contextBag.Count > 0);
            emailActivity?.SetTag("email.replacement.tokens_remaining",  tokensRemaining);

            _logger.LogInformation(
                "EmailActionHandler [5/6] ✅ Replacement done | Subject={Subject} | TokensRemaining={Remaining}",
                subject, tokensRemaining);

            // ── STEP 5b: Embed direct action button URLs ──────────────────────
            if (dto.DirectActionId > -1)
            {
                try
                {
                    var details = await _configDAL.GetDetailsByDirectActionIdAsync(dto.DirectActionId, login, ct)
                        .ConfigureAwait(false);
                    // ApprovalBaseUrl is the Framework service base URL captured dynamically
                    // from the incoming HTTP request (scheme+host) in EventActionSubscribeController —
                    // same pattern as WIP using login.RequestUrl. Stored in ActionEventDto outbox payload.
                    var baseUrl = (!string.IsNullOrWhiteSpace(dto.ApprovalBaseUrl) ? dto.ApprovalBaseUrl : string.Empty).TrimEnd('/');
                    if (string.IsNullOrWhiteSpace(baseUrl))
                    {
                        emailActivity?.SetTag("email.action.base_url_warning",
                            "ApprovalBaseUrl is empty — action button URLs will not be embedded. Check EventActionSubscribeController captures Request.Scheme+Host.");
                        _logger.LogWarning(
                            "EmailActionHandler [5b] ⚠️ ApprovalBaseUrl is empty — token URLs cannot be built | DirectActionId={Id} ActionRunId={RunId}",
                            dto.DirectActionId, dto.ActionRunId);
                    }
                    var expiry  = TimeSpan.FromHours(48);

                    foreach (var detail in details)
                    {
                        if (string.IsNullOrWhiteSpace(detail.EmailPlaceholder)) continue;

                        var token = _tokenService.Generate(
                            detail.ActionCode, dto.ContextId, dto.TenantId, dto.AssigneeUserId,
                            login.DatabaseName, expiry);
                        var url = $"{baseUrl}/Action/Execute?token={token}";
                        body = body.Replace($"##{detail.EmailPlaceholder}##", url, StringComparison.OrdinalIgnoreCase);
                    }

                    emailActivity?.SetTag("email.action.base_url",    baseUrl);
                    emailActivity?.SetTag("email.action.token_count", details.Count());
                    emailActivity?.SetTag("email.action.context_id",  dto.ContextId);
                    emailActivity?.SetTag("email.action.assignee_id", dto.AssigneeUserId);
                    _logger.LogInformation(
                        "EmailActionHandler [5b] ✅ Action links embedded | DirectActionId={Id} ContextId={Ctx} BaseUrl={Url} TokenCount={Count}",
                        dto.DirectActionId, dto.ContextId, baseUrl, details.Count());
                }
                catch (Exception ex)
                {
                    _logger.LogWarning(ex,
                        "EmailActionHandler [5b] ⚠️ Failed to embed action links | DirectActionId={Id}",
                        dto.DirectActionId);
                }
            }

            // ── STEP 6: Send email ────────────────────────────────────────────
            _logger.LogInformation(
                "EmailActionHandler [6/6] Sending email | To={To} CC={CC} BCC={BCC} Subject={Subject} IsHtml={Html} SmtpHost={Host}:{Port}",
                sendTo, dto.MailCc ?? "(none)", dto.MailBcc ?? "(none)", subject, isHtml, _smtp.Host, _smtp.Port);

            try
            {
#pragma warning disable SYSLIB0006
                using var client = new SmtpClient(_smtp.Host, _smtp.Port)
                {
                    Credentials = new NetworkCredential(_smtp.User, _smtp.Password),
                    EnableSsl   = _smtp.EnableSsl
                };
#pragma warning restore SYSLIB0006

                using var mail = new MailMessage
                {
                    From       = new MailAddress(_smtp.User, "GoodBooks ERP"),
                    Subject    = subject,
                    Body       = body,
                    IsBodyHtml = isHtml
                };

                mail.To.Add(sendTo);

                // CC — resolve via delivery type → EntityPayload → ContextBag cascade
                var resolvedCc = await ResolveRecipientAsync(
                    dto.MailCc, dto.MailCcDeliveryType, entityDoc, contextBag, "MailCc", dto.ActionRunId, login, ct)
                    .ConfigureAwait(false);
                if (!string.IsNullOrWhiteSpace(resolvedCc))
                {
                    foreach (var cc in resolvedCc.Split(';', StringSplitOptions.RemoveEmptyEntries))
                    {
                        var trimmed = cc.Trim();
                        if (!string.IsNullOrWhiteSpace(trimmed))
                        {
                            mail.CC.Add(trimmed);
                            _logger.LogInformation("EmailActionHandler [6/6] CC added: {CC}", trimmed);
                        }
                    }
                }

                // BCC — resolve via delivery type → EntityPayload → ContextBag cascade
                var resolvedBcc = await ResolveRecipientAsync(
                    dto.MailBcc, dto.MailBccDeliveryType, entityDoc, contextBag, "MailBcc", dto.ActionRunId, login, ct)
                    .ConfigureAwait(false);
                if (!string.IsNullOrWhiteSpace(resolvedBcc))
                {
                    foreach (var bcc in resolvedBcc.Split(';', StringSplitOptions.RemoveEmptyEntries))
                    {
                        var trimmed = bcc.Trim();
                        if (!string.IsNullOrWhiteSpace(trimmed))
                            mail.Bcc.Add(trimmed);
                    }
                }

                if (!string.IsNullOrWhiteSpace(dto.ReplyTo))
                    mail.ReplyToList.Add(new MailAddress(dto.ReplyTo));

                // Attach a generated document if AttachmentId is set — generic support (added for
                // Correspondence Phase 2, but usable by any ActionType/MACTION-configured action
                // that wants to attach a file, platform-wide, not just Correspondence sends).
                if (dto.AttachmentId is > 0)
                {
                    try
                    {
                        var attachmentData = await _fileUploadBLL
                            .DownloadAttachmentAsBase64(dto.AttachmentId.Value, login)
                            .ConfigureAwait(false);

                        var attachmentBytes = Convert.FromBase64String(attachmentData.Base64Content);
                        var attachmentStream = new MemoryStream(attachmentBytes);
                        var attachment = new System.Net.Mail.Attachment(attachmentStream, attachmentData.FileName, attachmentData.MimeType);
                        mail.Attachments.Add(attachment); // disposed when `mail` is disposed below

                        emailActivity?.SetTag("email.attachment.id",       dto.AttachmentId.Value);
                        emailActivity?.SetTag("email.attachment.filename", attachmentData.FileName);
                        emailActivity?.SetTag("email.attachment.size",     attachmentBytes.LongLength);
                        _logger.LogInformation(
                            "EmailActionHandler: attachment loaded | AttachmentId={AttachmentId} FileName={FileName} Size={Size} | ActionRunId={RunId}",
                            dto.AttachmentId.Value, attachmentData.FileName, attachmentBytes.LongLength, dto.ActionRunId);
                    }
                    catch (Exception ex)
                    {
                        // Attachment failure should not silently drop the whole email — log and
                        // continue sending without the attachment rather than failing the action.
                        emailActivity?.SetTag("email.attachment.error", ex.Message);
                        _logger.LogWarning(ex,
                            "EmailActionHandler: failed to load attachment AttachmentId={AttachmentId} — sending without it | ActionRunId={RunId}",
                            dto.AttachmentId.Value, dto.ActionRunId);
                    }
                }

                emailActivity?.SetTag("email.subject", subject);

                await client.SendMailAsync(mail, ct).ConfigureAwait(false);

                emailActivity?.SetTag("email.sent",      true);
                emailActivity?.SetTag("email.is_html",   isHtml);
                emailActivity?.SetStatus(ActivityStatusCode.Ok);

                _logger.LogInformation("Email sent To={To} CC={CC} Subject={Subject} ActionRunId={ActionRunId} Via={Host}:{Port}",
                    sendTo, dto.MailCc ?? "none", subject, dto.ActionRunId, _smtp.Host, _smtp.Port);

                return ActionResult.Ok($"sent to {sendTo}");
            }
            catch (Exception ex)
            {
                emailActivity?.AddException(ex);
                emailActivity?.SetTag("email.sent",      false);
                emailActivity?.SetTag("email.error",     ex.Message);
                emailActivity?.SetStatus(ActivityStatusCode.Error, ex.Message);

                _logger.LogWarning("Email failed To={To} ActionRunId={ActionRunId} Host={Host}:{Port}", sendTo, dto.ActionRunId, _smtp.Host, _smtp.Port);

                return ActionResult.Transient(ex.Message);
            }
            finally
            {
                entityDoc?.Dispose();
            }
        }

        // ── Helpers ───────────────────────────────────────────────────────────

        /// <summary>
        /// Resolves a recipient address using the MACTION delivery type.
        /// <para>
        /// DeliveryType = 3  → value is a direct email address, use as-is.<br/>
        /// DeliveryType = 2  → value is a field name; resolve from EntityPayload first,
        ///                     then cascade to ContextBag (DB_ENRICH enriched values).<br/>
        /// DeliveryType = 12 → EnrichQualifier: value is a MGBCONFIGUREDQUERY QueryCode;
        ///                     run the query with entity-payload parameters, first column = email.<br/>
        /// Other / fallback  → if value contains '@' treat as direct email,
        ///                     otherwise cascade EntityPayload → ContextBag (backward-compatible).
        /// </para>
        /// <para>
        /// ContextBag cascade: when a field name is not found in the entity DTO payload, the handler
        /// automatically looks it up in <paramref name="contextBag"/>, which contains every value
        /// written by DB_ENRICH qualifier rules (e.g. MailId = "arul@myunisoft.com").
        /// </para>
        /// </summary>
        private async Task<string?> ResolveRecipientAsync(
            string? value,
            int deliveryType,
            JsonDocument? entityDoc,
            IReadOnlyDictionary<string, string?> contextBag,
            string fieldLabel,
            int actionRunId,
            LoginDTO login,
            CancellationToken ct)
        {
            using var recipientActivity = GB5ActivitySources.ActionProcessor.StartActivity(
                "action.email.recipient_resolve", ActivityKind.Internal);

            var entityFieldCount = entityDoc?.RootElement.EnumerateObject().Count() ?? 0;
            var deliveryMode     = deliveryType switch
            {
                3  => "Direct",
                2  => "FieldName",
                12 => "Qualifier",
                _  => "Inferred"
            };

            recipientActivity?.SetTag("email.recipient.field_label",       fieldLabel);
            recipientActivity?.SetTag("email.recipient.delivery_type",     deliveryType);
            recipientActivity?.SetTag("email.recipient.delivery_mode",     deliveryMode);
            recipientActivity?.SetTag("email.recipient.configured_value",  value ?? "(null)");
            recipientActivity?.SetTag("email.recipient.entity_available",  entityDoc != null);
            recipientActivity?.SetTag("email.recipient.entity_field_count",entityFieldCount);
            recipientActivity?.SetTag("email.recipient.bag_available",     contextBag.Count > 0);
            recipientActivity?.SetTag("email.recipient.bag_key_count",     contextBag.Count);
            recipientActivity?.SetTag("gb5.action.run_id",                 actionRunId);
            recipientActivity?.SetTag("gb5.tenant.id",                     login.ClientId);
            recipientActivity?.SetTag("gb5.tenant.db",                     login.DatabaseName);

            if (string.IsNullOrWhiteSpace(value))
            {
                recipientActivity?.SetTag("email.recipient.resolution_source", "None");
                recipientActivity?.SetTag("email.recipient.resolved",          "(null — input empty)");
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                return null;
            }

            string? resolved;

            // ── DeliveryType 3: direct email, no lookup needed ────────────────
            if (deliveryType == 3)
            {
                resolved = value.Trim();
                recipientActivity?.SetTag("email.recipient.resolution_source", "DirectEmail");
                recipientActivity?.SetTag("email.recipient.resolved",          resolved);
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                return resolved;
            }

            // ── DeliveryType 2: field name → EntityPayload then ContextBag ────
            if (deliveryType == 2)
            {
                var fieldName = value.Trim();

                // Step 1: Try EntityPayload
                resolved = ResolveFromPayload(fieldName, entityDoc, fieldLabel, actionRunId);
                if (resolved != null)
                {
                    recipientActivity?.SetTag("email.recipient.resolution_source", "EntityPayload");
                    recipientActivity?.SetTag("email.recipient.resolved",          resolved);
                    recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                    _logger.LogInformation(
                        "EmailActionHandler: {Label} '{Field}' resolved from EntityPayload | ActionRunId={RunId}",
                        fieldLabel, fieldName, actionRunId);
                    return resolved;
                }

                // Step 2: Cascade to ContextBag
                recipientActivity?.SetTag("email.recipient.payload_miss", true);
                _logger.LogInformation(
                    "EmailActionHandler: '{Field}' not in EntityPayload — cascading to ContextBag | Label={Label} ActionRunId={RunId}",
                    fieldName, fieldLabel, actionRunId);

                var (bagValue, bagKey, bagStatus) = ResolveFromContextBag(fieldName, contextBag, fieldLabel, actionRunId);
                recipientActivity?.SetTag("email.recipient.bag_lookup_status", bagStatus);
                recipientActivity?.SetTag("email.recipient.bag_matched_key",   bagKey ?? "(none)");

                if (bagValue != null)
                {
                    recipientActivity?.SetTag("email.recipient.resolution_source", "ContextBag");
                    recipientActivity?.SetTag("email.recipient.resolved",          bagValue);
                    recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                    return bagValue;
                }

                // Unresolvable — surface all available bag keys for easy debugging
                var bagKeys = contextBag.Count > 0 ? string.Join(",", contextBag.Keys) : "(bag empty)";
                recipientActivity?.SetTag("email.recipient.resolution_source", "Unresolved");
                recipientActivity?.SetTag("email.recipient.resolved",          "(null)");
                recipientActivity?.SetTag("email.recipient.bag_keys_available",bagKeys);
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);

                _logger.LogWarning(
                    "EmailActionHandler: '{Field}' not resolved for {Label} | EntityPayload={PayloadAvail} BagKeys=[{Keys}] | ActionRunId={RunId}",
                    fieldName, fieldLabel, entityDoc != null, bagKeys, actionRunId);
                return null;
            }

            // ── DeliveryType 12: ContextBag first, DB qualifier fallback ─────
            // The DB_ENRICH qualifier already ran at save-pipeline time and stored its
            // result in the ContextBag (e.g. "MailId" → "arul@myunisoft.com").
            // Check the bag first — avoids a redundant DB round-trip in every case
            // where publish-time enrichment already captured the value.
            // Only if the key is absent from the bag do we fall back to the DB query.
            if (deliveryType == 12)
            {
                var fieldName12 = value.Trim();

                // Step 1: ContextBag lookup (pre-computed at publish time — zero DB cost)
                var (bag12Value, bag12Key, bag12Status) = ResolveFromContextBag(
                    fieldName12, contextBag, fieldLabel, actionRunId);

                recipientActivity?.SetTag("email.recipient.bag_lookup_status", bag12Status);
                recipientActivity?.SetTag("email.recipient.bag_matched_key",   bag12Key ?? "(none)");

                if (bag12Value != null)
                {
                    recipientActivity?.SetTag("email.recipient.resolution_source", "ContextBag");
                    recipientActivity?.SetTag("email.recipient.resolved",          bag12Value);
                    recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                    _logger.LogInformation(
                        "EmailActionHandler: {Label} '{Field}' resolved from ContextBag (DeliveryType=12, no DB query needed) → '{Value}' | ActionRunId={RunId}",
                        fieldLabel, fieldName12, bag12Value, actionRunId);
                    return bag12Value;
                }

                // Step 1.5: Entity payload fallback (runs before the DB round-trip).
                // When DB_ENRICH didn't fire or ContextJson was not propagated, the
                // entity DTO itself often carries the field (e.g. MailId on TLeaveDTO).
                var entityFallback12 = ResolveFromPayload(fieldName12, entityDoc, fieldLabel, actionRunId);
                if (entityFallback12 != null)
                {
                    recipientActivity?.SetTag("email.recipient.resolution_source", "EntityPayload");
                    recipientActivity?.SetTag("email.recipient.resolved",          entityFallback12);
                    recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                    _logger.LogInformation(
                        "EmailActionHandler: {Label} '{Field}' resolved from EntityPayload (DeliveryType=12, bag was empty) → '{Value}' | ActionRunId={RunId}",
                        fieldLabel, fieldName12, entityFallback12, actionRunId);
                    return entityFallback12;
                }

                // Step 2: Bag miss + entity miss → fall back to DB qualifier query
                recipientActivity?.SetTag("email.recipient.qualifier_fallback", true);
                _logger.LogInformation(
                    "EmailActionHandler: '{Field}' not in ContextBag or EntityPayload (status={Status}) — falling back to DB qualifier | Label={Label} ActionRunId={RunId}",
                    fieldName12, bag12Status, fieldLabel, actionRunId);

                resolved = await ResolveFromQualifierAsync(
                    fieldName12, entityDoc, fieldLabel, actionRunId, login, ct)
                    .ConfigureAwait(false);

                if (resolved != null)
                {
                    recipientActivity?.SetTag("email.recipient.resolution_source", "Qualifier");
                    recipientActivity?.SetTag("email.recipient.resolved",          resolved);
                    recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                }
                else
                {
                    var bagKeys12 = contextBag.Count > 0 ? string.Join(",", contextBag.Keys) : "(bag empty)";
                    recipientActivity?.SetTag("email.recipient.resolution_source",   "Unresolved");
                    recipientActivity?.SetTag("email.recipient.resolved",            "(null)");
                    recipientActivity?.SetTag("email.recipient.bag_keys_available",  bagKeys12);
                    recipientActivity?.SetStatus(ActivityStatusCode.Error,
                        $"'{fieldName12}' not found in ContextBag or DB qualifier");
                }
                return resolved;
            }

            // ── Fallback: infer from the value itself ─────────────────────────
            // Supports legacy data where DeliveryType was not explicitly set.
            if (value.Contains('@'))
            {
                resolved = value.Trim();
                recipientActivity?.SetTag("email.recipient.resolution_source", "InferredDirectEmail");
                recipientActivity?.SetTag("email.recipient.resolved",          resolved);
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                return resolved;
            }

            // Inferred field-name: same EntityPayload → ContextBag cascade
            var inferredField = value.Trim();
            resolved = ResolveFromPayload(inferredField, entityDoc, fieldLabel, actionRunId);
            if (resolved != null)
            {
                recipientActivity?.SetTag("email.recipient.resolution_source", "InferredEntityPayload");
                recipientActivity?.SetTag("email.recipient.resolved",          resolved);
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                return resolved;
            }

            var (ibagValue, ibagKey, ibagStatus) = ResolveFromContextBag(inferredField, contextBag, fieldLabel, actionRunId);
            recipientActivity?.SetTag("email.recipient.bag_lookup_status", ibagStatus);
            recipientActivity?.SetTag("email.recipient.bag_matched_key",   ibagKey ?? "(none)");

            if (ibagValue != null)
            {
                recipientActivity?.SetTag("email.recipient.resolution_source", "InferredContextBag");
                recipientActivity?.SetTag("email.recipient.resolved",          ibagValue);
                recipientActivity?.SetStatus(ActivityStatusCode.Ok);
                return ibagValue;
            }

            var ibagKeys = contextBag.Count > 0 ? string.Join(",", contextBag.Keys) : "(bag empty)";
            recipientActivity?.SetTag("email.recipient.resolution_source", "Unresolved");
            recipientActivity?.SetTag("email.recipient.resolved",          "(null)");
            recipientActivity?.SetTag("email.recipient.bag_keys_available",ibagKeys);
            recipientActivity?.SetStatus(ActivityStatusCode.Ok);
            return null;
        }

        /// <summary>
        /// Parses <see cref="ActionEventDto.ContextJson"/> and extracts the <c>Bag</c> dictionary
        /// as a flat case-insensitive string map.
        /// <para>
        /// Only scalar values (string, number, bool, null) are extracted.
        /// Complex objects (e.g. WorkflowInfo) and arrays are skipped — they cannot be email addresses.
        /// The document is parsed and disposed immediately, leaving no dangling JsonElement references.
        /// </para>
        /// Returns an empty dictionary (never null) when ContextJson is absent, malformed, or has no Bag.
        /// </summary>
        private IReadOnlyDictionary<string, string?> ParseContextBag(string? contextJson, int actionRunId)
        {
            var result = new Dictionary<string, string?>(StringComparer.OrdinalIgnoreCase);

            if (string.IsNullOrWhiteSpace(contextJson))
            {
                _logger.LogDebug(
                    "EmailActionHandler: ContextJson is empty — ContextBag unavailable | ActionRunId={RunId}",
                    actionRunId);
                return result;
            }

            try
            {
                using var doc = JsonDocument.Parse(contextJson);
                var root = doc.RootElement;

                if (!root.TryGetProperty("Bag", out var bag) || bag.ValueKind != JsonValueKind.Object)
                {
                    _logger.LogDebug(
                        "EmailActionHandler: ContextJson has no 'Bag' object | ActionRunId={RunId}",
                        actionRunId);
                    return result;
                }

                foreach (var prop in bag.EnumerateObject())
                {
                    // Extract only scalar values — complex objects (WorkflowInfo) and arrays
                    // are never valid as email recipients and are intentionally skipped.
                    string? val = prop.Value.ValueKind switch
                    {
                        JsonValueKind.String => prop.Value.GetString(),
                        JsonValueKind.Number => prop.Value.GetRawText(),
                        JsonValueKind.True   => "true",
                        JsonValueKind.False  => "false",
                        JsonValueKind.Null   => null,
                        _                    => null   // Object / Array: skip
                    };
                    result[prop.Name] = val;
                }

                _logger.LogDebug(
                    "EmailActionHandler: ContextBag parsed | Keys=[{Keys}] | ActionRunId={RunId}",
                    string.Join(", ", result.Keys), actionRunId);
            }
            catch (JsonException ex)
            {
                _logger.LogWarning(ex,
                    "EmailActionHandler: ContextJson is malformed — ContextBag resolution unavailable | ActionRunId={RunId}",
                    actionRunId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex,
                    "EmailActionHandler: Unexpected error parsing ContextJson | ActionRunId={RunId}",
                    actionRunId);
            }

            return result;
        }

        /// <summary>
        /// Looks up <paramref name="fieldName"/> in the pre-parsed ContextBag (case-insensitive).
        /// Returns a tuple of (resolvedValue, matchedKey, statusCode) for clean OTel tagging.
        /// <para>
        /// Status codes: <c>bag_unavailable</c>, <c>key_not_found</c>, <c>null_value</c>,
        /// <c>empty_value</c>, <c>found</c>.
        /// </para>
        /// </summary>
        private (string? value, string? matchedKey, string status) ResolveFromContextBag(
            string fieldName,
            IReadOnlyDictionary<string, string?> contextBag,
            string fieldLabel,
            int actionRunId)
        {
            if (contextBag.Count == 0)
            {
                _logger.LogDebug(
                    "EmailActionHandler: ContextBag is empty — cannot resolve '{Field}' for {Label} | ActionRunId={RunId}",
                    fieldName, fieldLabel, actionRunId);
                return (null, null, "bag_unavailable");
            }

            if (!contextBag.TryGetValue(fieldName, out var bagValue))
            {
                _logger.LogWarning(
                    "EmailActionHandler: key '{Field}' not found in ContextBag for {Label} | AvailableKeys=[{Keys}] | ActionRunId={RunId}",
                    fieldName, fieldLabel, string.Join(", ", contextBag.Keys), actionRunId);
                return (null, null, "key_not_found");
            }

            if (bagValue == null)
            {
                _logger.LogWarning(
                    "EmailActionHandler: ContextBag['{Field}'] is null for {Label} | ActionRunId={RunId}",
                    fieldName, fieldLabel, actionRunId);
                return (null, fieldName, "null_value");
            }

            if (string.IsNullOrWhiteSpace(bagValue))
            {
                _logger.LogWarning(
                    "EmailActionHandler: ContextBag['{Field}'] is empty/whitespace for {Label} | ActionRunId={RunId}",
                    fieldName, fieldLabel, actionRunId);
                return (null, fieldName, "empty_value");
            }

            _logger.LogInformation(
                "EmailActionHandler: '{Field}' resolved from ContextBag → '{Value}' for {Label} | ActionRunId={RunId}",
                fieldName, bagValue, fieldLabel, actionRunId);
            return (bagValue, fieldName, "found");
        }

        /// <summary>
        /// Runs a MGBCONFIGUREDQUERY by <paramref name="queryCode"/> using all scalar properties
        /// from <paramref name="entityDoc"/> as parameters, plus TenantId from <paramref name="login"/>.
        /// The first non-null column value in the result row is returned as the email address.
        /// Emits a dedicated OTel span so every qualifier call is visible in Zipkin.
        /// </summary>
        private async Task<string?> ResolveFromQualifierAsync(
            string queryCode,
            JsonDocument? entityDoc,
            string fieldLabel,
            int actionRunId,
            LoginDTO login,
            CancellationToken ct)
        {
            using var qualifierActivity = GB5ActivitySources.ActionProcessor.StartActivity(
                "action.email.qualifier_resolve", ActivityKind.Internal);
            qualifierActivity?.SetTag("email.qualifier.code",           queryCode);
            qualifierActivity?.SetTag("email.qualifier.field_label",    fieldLabel);
            qualifierActivity?.SetTag("email.qualifier.entity_present", entityDoc != null);
            qualifierActivity?.SetTag("gb5.action.run_id",              actionRunId);
            qualifierActivity?.SetTag("gb5.tenant.id",                  login.ClientId);
            qualifierActivity?.SetTag("gb5.tenant.db",                  login.DatabaseName);

            var parameters = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase)
            {
                ["TenantId"] = login.ClientId
            };

            var entityFields = new List<string>();

            if (entityDoc != null)
            {
                foreach (var prop in entityDoc.RootElement.EnumerateObject())
                {
                    entityFields.Add(prop.Name);
                    switch (prop.Value.ValueKind)
                    {
                        case JsonValueKind.String:
                            parameters[prop.Name] = prop.Value.GetString();
                            break;
                        case JsonValueKind.Number:
                            parameters[prop.Name] = prop.Value.TryGetInt64(out var l)
                                ? (object?)l
                                : prop.Value.GetDecimal();
                            break;
                        case JsonValueKind.True:
                            parameters[prop.Name] = true;
                            break;
                        case JsonValueKind.False:
                            parameters[prop.Name] = false;
                            break;
                        case JsonValueKind.Null:
                            parameters[prop.Name] = null;
                            break;
                    }
                }
            }

            var paramKeys          = string.Join(",", parameters.Keys);
            var entityFieldsJoined = entityFields.Count > 0 ? string.Join(",", entityFields) : "(none)";

            qualifierActivity?.SetTag("email.qualifier.param_count",   parameters.Count);
            qualifierActivity?.SetTag("email.qualifier.param_keys",    paramKeys);
            qualifierActivity?.SetTag("email.qualifier.entity_fields", entityFieldsJoined);
            qualifierActivity?.SetTag("email.qualifier.tenant_id_value", login.ClientId.ToString());

            _logger.LogInformation(
                "EmailActionHandler [EnrichQualifier] Resolving {Label} via QueryCode={Code} | TenantId={TenantId} ParamCount={Count} | ActionRunId={RunId}",
                fieldLabel, queryCode, login.ClientId, parameters.Count, actionRunId);

            try
            {
                var result = await _gbQueryExecutor.ExecuteRowAsync(
                    queryCode,
                    parameters,
                    login,
                    callerModule: "EmailAction.EnrichQualifier",
                    ct: ct).ConfigureAwait(false);

                qualifierActivity?.SetTag("email.qualifier.row_found",   result.Found);
                qualifierActivity?.SetTag("email.qualifier.duration_ms", result.DurationMs);

                if (!result.Found || result.Row.Count == 0)
                {
                    qualifierActivity?.SetTag("email.qualifier.result", "(no row)");
                    qualifierActivity?.SetStatus(ActivityStatusCode.Ok);
                    _logger.LogWarning(
                        "EmailActionHandler [EnrichQualifier] No row returned | QueryCode={Code} Field={Label} | ActionRunId={RunId}",
                        queryCode, fieldLabel, actionRunId);
                    return null;
                }

                var email = result.Row.Values
                    .Select(v => v?.ToString())
                    .FirstOrDefault(v => !string.IsNullOrWhiteSpace(v));

                qualifierActivity?.SetTag("email.qualifier.result",         email ?? "(null)");
                qualifierActivity?.SetTag("email.qualifier.column_used",    result.Row.Keys.FirstOrDefault() ?? "(unknown)");
                qualifierActivity?.SetTag("email.qualifier.result_columns", string.Join(",", result.Row.Keys));
                qualifierActivity?.SetStatus(ActivityStatusCode.Ok);

                _logger.LogInformation(
                    "EmailActionHandler [EnrichQualifier] ✅ {Label} resolved | QueryCode={Code} → {Email} | ActionRunId={RunId}",
                    fieldLabel, queryCode, email ?? "(null)", actionRunId);

                return email;
            }
            catch (Exception ex)
            {
                qualifierActivity?.AddException(ex);
                qualifierActivity?.SetTag("email.qualifier.error",             ex.Message);
                qualifierActivity?.SetTag("email.qualifier.error_type",        ex.GetType().Name);
                qualifierActivity?.SetTag("email.qualifier.param_keys_at_error", paramKeys);
                qualifierActivity?.SetStatus(ActivityStatusCode.Error, ex.Message);

                _logger.LogError(ex,
                    "EmailActionHandler [EnrichQualifier] ❌ QueryCode={Code} failed for {Label} | TenantId={TenantId} ParamCount={Count} ParamKeys={Keys} EntityFields={EntityFields} | ActionRunId={RunId}",
                    queryCode, fieldLabel, login.ClientId, parameters.Count, paramKeys, entityFieldsJoined, actionRunId);

                return null;
            }
        }

        /// <summary>
        /// Detects GB5's standard API response envelope (<c>ResponseStandardDTO&lt;T&gt;</c> /
        /// <c>GB4ResponseStandardDTO&lt;T&gt;</c> — Id/To/From/Status/ReplyTo/ETag/Body/Total/.../
        /// CacheLevel/CacheKey/CacheStatus) and, if matched, parses its "Body" property (a
        /// JSON-encoded string holding the real entity DTO) as a new document.
        /// <para>
        /// Requires several of the envelope's own distinguishing fields together (Body as a
        /// STRING, plus Status/CacheLevel/CacheKey) — any single one of those could plausibly
        /// appear on an unrelated entity, but this exact combination is unique to the response
        /// wrapper, so a normal entity DTO is never misread as one.
        /// </para>
        /// </summary>
        private static bool TryUnwrapResponseEnvelope(JsonDocument entityDoc, out JsonDocument? unwrapped)
        {
            unwrapped = null;
            var root = entityDoc.RootElement;

            if (root.ValueKind != JsonValueKind.Object) return false;
            if (!root.TryGetProperty("Body", out var bodyEl) || bodyEl.ValueKind != JsonValueKind.String) return false;
            if (!root.TryGetProperty("Status", out _)) return false;
            if (!root.TryGetProperty("CacheLevel", out _)) return false;
            if (!root.TryGetProperty("CacheKey", out _)) return false;

            var bodyJson = bodyEl.GetString();
            if (string.IsNullOrWhiteSpace(bodyJson)) return false;

            try
            {
                var parsed = JsonDocument.Parse(bodyJson);
                if (parsed.RootElement.ValueKind != JsonValueKind.Object)
                {
                    parsed.Dispose();
                    return false;
                }

                unwrapped = parsed;
                return true;
            }
            catch (JsonException)
            {
                return false;
            }
        }

        private string? ResolveFromPayload(string fieldName, JsonDocument? entityDoc, string fieldLabel, int actionRunId)
        {
            if (entityDoc == null)
            {
                _logger.LogDebug(
                    "EmailActionHandler: {Label}='{Field}' needs payload lookup but EntityPayload is null | ActionRunId={RunId}",
                    fieldLabel, fieldName, actionRunId);
                return null;
            }

            if (entityDoc.RootElement.TryGetProperty(fieldName, out var fieldEl))
            {
                var resolved = fieldEl.ValueKind == JsonValueKind.String
                    ? fieldEl.GetString()
                    : fieldEl.ToString();

                if (!string.IsNullOrWhiteSpace(resolved))
                    return resolved;
            }

            return null;
        }

        /// <summary>
        /// Counts unreplaced ##TOKEN## placeholders in text.
        /// Each token has exactly two "##" markers (opening + closing), so we count "##" occurrences
        /// and divide by two.  Used for the email.replacement.tokens_remaining Zipkin tag.
        /// </summary>
        private static int CountHashTokens(string text)
        {
            if (string.IsNullOrEmpty(text)) return 0;
            int count = 0, idx = 0;
            while ((idx = text.IndexOf("##", idx, StringComparison.Ordinal)) >= 0) { count++; idx += 2; }
            return count / 2;
        }

        /// <summary>
        /// Replaces any remaining ##token## / @Model.token placeholders using ContextBag values.
        /// Bag keys from DB_ENRICH carry a "DTO." prefix (e.g. "DTO.DisplayValue").
        /// Both the exact key (##DTO.DisplayValue##) and the stripped form (##DisplayValue##) are tried
        /// so template authors don't need to know the prefix convention.
        /// Only tokens still containing ## / @Model. after entity-payload replacement are affected.
        /// </summary>
        private static string ReplacePlaceholdersFromBag(string text, IReadOnlyDictionary<string, string?> bag)
        {
            if (string.IsNullOrEmpty(text)) return text;

            foreach (var kvp in bag)
            {
                var value = kvp.Value ?? string.Empty;

                // Exact key match: ##DTO.DisplayValue## and @Model.DTO.DisplayValue
                var hashExact  = $"##{kvp.Key}##";
                var modelExact = $"@Model.{kvp.Key}";
                if (text.Contains(hashExact,  StringComparison.OrdinalIgnoreCase))
                    text = text.Replace(hashExact,  value, StringComparison.OrdinalIgnoreCase);
                if (text.Contains(modelExact, StringComparison.OrdinalIgnoreCase))
                    text = text.Replace(modelExact, value, StringComparison.OrdinalIgnoreCase);

                // Short key (strip "DTO." prefix): ##DisplayValue## and @Model.DisplayValue
                if (kvp.Key.StartsWith("DTO.", StringComparison.OrdinalIgnoreCase))
                {
                    var shortKey   = kvp.Key.Substring(4);
                    var hashShort  = $"##{shortKey}##";
                    var modelShort = $"@Model.{shortKey}";
                    if (text.Contains(hashShort,  StringComparison.OrdinalIgnoreCase))
                        text = text.Replace(hashShort,  value, StringComparison.OrdinalIgnoreCase);
                    if (text.Contains(modelShort, StringComparison.OrdinalIgnoreCase))
                        text = text.Replace(modelShort, value, StringComparison.OrdinalIgnoreCase);
                }
            }
            return text;
        }

        /// <summary>
        /// Replaces ##FieldName## and @Model.FieldName tokens in text with values from entityPayload.
        /// Template syntax matches MMAILTEMPLATE.BODY / SUBJECT in the database.
        /// </summary>
        private static string ReplacePlaceholders(string text, JsonElement entity)
        {
            if (string.IsNullOrEmpty(text)) return text;

            foreach (var prop in entity.EnumerateObject())
            {
                var value = prop.Value.ValueKind switch
                {
                    JsonValueKind.String => prop.Value.GetString() ?? string.Empty,
                    JsonValueKind.Null   => string.Empty,
                    _                   => prop.Value.ToString()
                };

                var hashToken  = $"##{prop.Name}##";
                var modelToken = $"@Model.{prop.Name}";

                if (text.Contains(hashToken, StringComparison.OrdinalIgnoreCase))
                    text = text.Replace(hashToken, value, StringComparison.OrdinalIgnoreCase);

                if (text.Contains(modelToken, StringComparison.OrdinalIgnoreCase))
                    text = text.Replace(modelToken, value, StringComparison.OrdinalIgnoreCase);
            }
            return text;
        }

        private static LoginDTO BuildLogin(ActionEventDto dto) => new()
        {
            ClientId               = dto.TenantId,
            DatabaseName           = dto.DatabaseName,
            ConnectionDatabaseName = dto.DatabaseName,
            UserId                 = -1
        };
    }
}