using System.ComponentModel.DataAnnotations;
using System.Net;
using System.Text;
using System.Text.Json;
using BizProcessBLL.Interfaces;
using BizProcessDAL.CustomCode;
using BizProcessDAL.DTO;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EventLogPublish;
using GB5Shared.Export.HybridReport;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using HandlebarsDotNet;
using Microsoft.Extensions.Logging;
using static GB5Shared.GB5Constant.Constant;

namespace BizProcessBLL.Implementations
{
    public class BizProcessBLL : IBizProcessBLL
    {
        // Recursion guard for SubProcess drill-in nesting — prevents a runaway/cyclic
        // ChildBizProcessId chain from recursing forever while still supporting a few
        // real levels of nesting (e.g. O2C -> Order Fulfillment -> Pick/Pack).
        private const int MaxSubprocessDepth = 3;

        private readonly IBizProcessDAL _DAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly EventLogPublish _EventLog;
        private readonly ILogger<BizProcessBLL> _Logger;
        private readonly IProcessOperationalDataSource _OperationalDataSource;

        public BizProcessBLL(
            IBizProcessDAL DAL, AutoNumber AutoNumber, IValidation Validation,
            EventLogPublish EventLog, ILogger<BizProcessBLL> Logger,
            IProcessOperationalDataSource OperationalDataSource)
        {
            _DAL = DAL;
            _AutoNumber = AutoNumber;
            _Validation = Validation;
            _EventLog = EventLog;
            _Logger = Logger;
            _OperationalDataSource = OperationalDataSource;
        }

        // Required-foreign-key check for an already-created parent record. GB5's real AutoNumber
        // ids are large negative numbers (see feedback_gb5_negative_autonumber_ids) — 0 is the only
        // "not yet selected" sentinel for a required reference, so this must not range-check on
        // sign/magnitude the way IValidation.Range(1, int.MaxValue, ...) does.
        private static void RequireId(int value, string fieldName)
        {
            if (value == 0)
                throw new ValidationException($"{fieldName} is required.");
        }

        // ── MBIZPROCESS ──────────────────────────────────────────────

        public async Task<BizProcessDTO?> GetProcess(int bizProcessId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process", new { bizProcessId });
            return await _DAL.GetProcess(bizProcessId, login, ct).ConfigureAwait(false);
        }

        public async Task<IEnumerable<dynamic>> GetSelectListProcess(LoginDTO login, CancellationToken ct)
            => await _DAL.GetSelectListProcess(login, ct).ConfigureAwait(false);

        public async Task<string> SaveProcess(BizProcessDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.BizProcessId <= 0;

            GB5Trace.Step("validate-process", new { dto.BizProcessCode });
            try
            {
                await _Validation.NotEmpty(dto.BizProcessCode, nameof(dto.BizProcessCode)).ConfigureAwait(false);
                await _Validation.NotEmpty(dto.BizProcessName, nameof(dto.BizProcessName)).ConfigureAwait(false);

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                GB5Trace.Step("save-process", new { dto.BizProcessId, isNew });
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESS, login).ConfigureAwait(false);
                    dto.BizProcessId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;
                    dto.Status = 1;

                    await _DAL.SaveProcess(dto, login, ct).ConfigureAwait(false);
                }
                else
                {
                    await _DAL.UpdateProcess(dto, login, ct).ConfigureAwait(false);
                }

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSCREATEEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    isNew ? "BizProcess Created" : "BizProcess Updated",
                    dto, EventTypeConstant.BIZPROCESSCREATEEVENTTYPEID,
                    dto.BizProcessId, login, ct: ct).ConfigureAwait(false);

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.BizProcessId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-process-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-process-failed", ex);
                _Logger.LogError(ex, "SaveProcess failed for BizProcessCode {Code}", dto.BizProcessCode);
                throw;
            }
        }

        // ── MBIZPROCESSVARIANT ───────────────────────────────────────

        public async Task<BizProcessVariantDTO?> GetProcessVariant(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-variant", new { bizProcessVariantId });
            return await _DAL.GetProcessVariant(bizProcessVariantId, login, ct).ConfigureAwait(false);
        }

        public async Task<IEnumerable<dynamic>> GetSelectListVariant(int bizProcessId, LoginDTO login, CancellationToken ct)
            => await _DAL.GetSelectListVariant(bizProcessId, login, ct).ConfigureAwait(false);

        public async Task<IEnumerable<dynamic>> GetSelectListAllStages(LoginDTO login, CancellationToken ct)
            => await _DAL.GetSelectListAllStages(login, ct).ConfigureAwait(false);

        public async Task<string> SaveProcessVariant(BizProcessVariantDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.BizProcessVariantId <= 0;

            GB5Trace.Step("validate-process-variant", new { dto.VariantCode });
            try
            {
                await _Validation.NotEmpty(dto.VariantCode, nameof(dto.VariantCode)).ConfigureAwait(false);
                await _Validation.NotEmpty(dto.VariantName, nameof(dto.VariantName)).ConfigureAwait(false);
                RequireId(dto.BizProcessId, nameof(dto.BizProcessId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                GB5Trace.Step("save-process-variant", new { dto.BizProcessVariantId, isNew });
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSVARIANT, login).ConfigureAwait(false);
                    dto.BizProcessVariantId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;
                    dto.LifecycleStatus = 1; // Draft
                    dto.VersionNo = 1;

                    await _DAL.SaveProcessVariant(dto, login, ct).ConfigureAwait(false);

                    // Clone-from-derived: copy the source variant's stages/edges as a starting
                    // point when authoring explicitly requests inheritance. Author overrides
                    // freely afterward — this is a one-time copy, not a live link.
                    if (dto.DerivedFromVariantId != -1)
                    {
                        GB5Trace.Step("clone-from-derived-variant", new { dto.DerivedFromVariantId, dto.BizProcessVariantId });
                        await CloneStagesAndEdgesAsync(dto.DerivedFromVariantId, dto.BizProcessVariantId, login, ct).ConfigureAwait(false);
                    }
                }
                else
                {
                    await _DAL.UpdateProcessVariant(dto, login, ct).ConfigureAwait(false);
                }

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSCREATEEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    isNew ? "BizProcessVariant Created" : "BizProcessVariant Updated",
                    dto, EventTypeConstant.BIZPROCESSCREATEEVENTTYPEID,
                    dto.BizProcessVariantId, login, ct: ct).ConfigureAwait(false);

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessVariantId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.BizProcessVariantId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-process-variant-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-process-variant-failed", ex);
                _Logger.LogError(ex, "SaveProcessVariant failed for VariantCode {Code}", dto.VariantCode);
                throw;
            }
        }

        private async Task CloneStagesAndEdgesAsync(int sourceVariantId, int targetVariantId, LoginDTO login, CancellationToken ct)
        {
            var sourceStages = (await _DAL.GetStagesForVariant(sourceVariantId, login, ct).ConfigureAwait(false)).ToList();
            if (sourceStages.Count == 0) return;

            // Map old stage id -> new stage id so edges can be re-pointed after cloning.
            var stageIdMap = new Dictionary<int, int>();

            foreach (var sourceStage in sourceStages)
            {
                var newStageId = (await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGE, login).ConfigureAwait(false)).StartNumber;
                stageIdMap[sourceStage.BizProcessStageId] = newStageId;

                var clonedStage = new BizProcessStageDTO
                {
                    BizProcessStageId = newStageId,
                    BizProcessVariantId = targetVariantId,
                    StageSequence = sourceStage.StageSequence,
                    StageName = sourceStage.StageName,
                    Description = sourceStage.Description,
                    StageType = sourceStage.StageType,
                    IsOptional = sourceStage.IsOptional,
                    ChildBizProcessId = sourceStage.ChildBizProcessId,
                    BizTransactionClassId = sourceStage.BizTransactionClassId,
                    BizTransactionSubClassId = sourceStage.BizTransactionSubClassId,
                    BizTransactionId = sourceStage.BizTransactionId,
                    BizTransactionTypeId = sourceStage.BizTransactionTypeId,
                    MenuId = sourceStage.MenuId,
                    TenantId = login.ClientId,
                    CreatedById = login.UserId,
                    CreatedOn = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow
                };
                await _DAL.SaveStage(clonedStage, login, ct).ConfigureAwait(false);
            }

            var sourceEdges = await _DAL.GetEdgesForVariant(sourceVariantId, login, ct).ConfigureAwait(false);
            foreach (var sourceEdge in sourceEdges)
            {
                if (!stageIdMap.TryGetValue(sourceEdge.FromStageId, out var newFromId)
                    || !stageIdMap.TryGetValue(sourceEdge.ToStageId, out var newToId))
                    continue; // defensive — shouldn't happen since edges are scoped to the source variant's own stages

                var newEdgeId = (await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGEEDGE, login).ConfigureAwait(false)).StartNumber;
                var clonedEdge = new BizProcessStageEdgeDTO
                {
                    BizProcessStageEdgeId = newEdgeId,
                    FromStageId = newFromId,
                    ToStageId = newToId,
                    Condition = sourceEdge.Condition,
                    ConnectionLabel = sourceEdge.ConnectionLabel,
                    TenantId = login.ClientId,
                    CreatedById = login.UserId,
                    CreatedOn = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow
                };
                await _DAL.SaveStageEdge(clonedEdge, login, ct).ConfigureAwait(false);
            }
        }

        public async Task<string> PublishVariant(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("validate-publish-variant", new { bizProcessVariantId });
            try
            {
                var variant = await _DAL.GetProcessVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)
                    ?? throw new ValidationException($"BizProcessVariantId {bizProcessVariantId} not found.");

                var map = await BuildProcessMapAsync(bizProcessVariantId, login, ct, depth: 0).ConfigureAwait(false);

                // Basic graph validation: every edge must connect two stages that actually
                // exist in this variant's own node set (orphaned-edge guard).
                var nodeIds = new HashSet<string>(map.Nodes.Select(n => n.Id));
                var orphaned = map.Edges.Where(e => !nodeIds.Contains(e.From) || !nodeIds.Contains(e.To)).ToList();
                if (orphaned.Count > 0)
                    throw new ValidationException(
                        $"Cannot publish: {orphaned.Count} edge(s) reference a stage outside this variant.");

                var snapshotJson = JsonSerializer.Serialize(map);

                GB5Trace.Step("publish-variant", new { bizProcessVariantId });
                variant.PublishedSnapshotJson = snapshotJson;
                variant.ModifiedById = login.UserId;
                variant.ModifiedOn = DateTime.UtcNow;
                await _DAL.PublishProcessVariant(variant, login, ct).ConfigureAwait(false);

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSVARIANTPUBLISHEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    "BizProcessVariant Published", variant, EventTypeConstant.BIZPROCESSVARIANTPUBLISHEVENTTYPEID,
                    bizProcessVariantId, login, ct: ct).ConfigureAwait(false);

                return $"{SuccessResponse.UpdateSuccessMessage} {bizProcessVariantId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("publish-variant-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("publish-variant-failed", ex);
                _Logger.LogError(ex, "PublishVariant failed for BizProcessVariantId {Id}", bizProcessVariantId);
                throw;
            }
        }

        // ── MBIZPROCESSSTAGE / MBIZPROCESSSTAGEEDGE ───────────────────

        public async Task<IEnumerable<BizProcessStageDTO>> GetStagesForVariant(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-stages-for-variant", new { bizProcessVariantId });
            return await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false);
        }

        public async Task<string> SaveStage(BizProcessStageDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.BizProcessStageId <= 0;

            GB5Trace.Step("validate-stage", new { dto.StageName });
            try
            {
                await _Validation.NotEmpty(dto.StageName, nameof(dto.StageName)).ConfigureAwait(false);
                RequireId(dto.BizProcessVariantId, nameof(dto.BizProcessVariantId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                GB5Trace.Step("save-stage", new { dto.BizProcessStageId, isNew });
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGE, login).ConfigureAwait(false);
                    dto.BizProcessStageId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;

                    await _DAL.SaveStage(dto, login, ct).ConfigureAwait(false);
                }
                else
                {
                    await _DAL.UpdateStage(dto, login, ct).ConfigureAwait(false);
                }

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    isNew ? "BizProcessStage Created" : "BizProcessStage Updated",
                    dto, EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID,
                    dto.BizProcessStageId, login, ct: ct).ConfigureAwait(false);

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessStageId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.BizProcessStageId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-stage-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-stage-failed", ex);
                _Logger.LogError(ex, "SaveStage failed for StageName {Name}", dto.StageName);
                throw;
            }
        }

        public async Task<string> DeleteStage(int bizProcessStageId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("validate-delete-stage", new { bizProcessStageId });
            try
            {
                RequireId(bizProcessStageId, nameof(bizProcessStageId));

                GB5Trace.Step("delete-stage", new { bizProcessStageId });
                await _DAL.DeleteStage(bizProcessStageId, login, ct).ConfigureAwait(false);

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    "BizProcessStage Deleted", new { BizProcessStageId = bizProcessStageId },
                    EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID, bizProcessStageId, login, ct: ct).ConfigureAwait(false);

                return SuccessResponse.DeleteSuccessMessage;
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("delete-stage-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("delete-stage-failed", ex);
                _Logger.LogError(ex, "DeleteStage failed for BizProcessStageId {Id}", bizProcessStageId);
                throw;
            }
        }

        public async Task<IEnumerable<BizProcessStageEdgeDTO>> GetEdgesForVariant(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-edges-for-variant", new { bizProcessVariantId });
            return await _DAL.GetEdgesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false);
        }

        public async Task<string> SaveStageEdge(BizProcessStageEdgeDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.BizProcessStageEdgeId <= 0;

            GB5Trace.Step("validate-stage-edge", new { dto.FromStageId, dto.ToStageId });
            try
            {
                RequireId(dto.FromStageId, nameof(dto.FromStageId));
                RequireId(dto.ToStageId, nameof(dto.ToStageId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                GB5Trace.Step("save-stage-edge", new { dto.BizProcessStageEdgeId, isNew });
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGEEDGE, login).ConfigureAwait(false);
                    dto.BizProcessStageEdgeId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;

                    await _DAL.SaveStageEdge(dto, login, ct).ConfigureAwait(false);
                }
                else
                {
                    await _DAL.UpdateStageEdge(dto, login, ct).ConfigureAwait(false);
                }

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessStageEdgeId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.BizProcessStageEdgeId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-stage-edge-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-stage-edge-failed", ex);
                _Logger.LogError(ex, "SaveStageEdge failed for FromStageId {From} ToStageId {To}", dto.FromStageId, dto.ToStageId);
                throw;
            }
        }

        public async Task<string> DeleteStageEdge(int bizProcessStageEdgeId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("validate-delete-stage-edge", new { bizProcessStageEdgeId });
            try
            {
                RequireId(bizProcessStageEdgeId, nameof(bizProcessStageEdgeId));

                GB5Trace.Step("delete-stage-edge", new { bizProcessStageEdgeId });
                await _DAL.DeleteStageEdge(bizProcessStageEdgeId, login, ct).ConfigureAwait(false);

                GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID });
                await _EventLog.PublishEventLogAsync(
                    "BizProcessStageEdge Deleted", new { BizProcessStageEdgeId = bizProcessStageEdgeId },
                    EventTypeConstant.BIZPROCESSSTAGESAVEEVENTTYPEID, bizProcessStageEdgeId, login, ct: ct).ConfigureAwait(false);

                return SuccessResponse.DeleteSuccessMessage;
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("delete-stage-edge-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("delete-stage-edge-failed", ex);
                _Logger.LogError(ex, "DeleteStageEdge failed for BizProcessStageEdgeId {Id}", bizProcessStageEdgeId);
                throw;
            }
        }

        // ── GetProcessMap ──────────────────────────────────────────────

        public async Task<ProcessMapDTO> GetProcessMap(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-map", new { bizProcessVariantId });

            var variant = await _DAL.GetProcessVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)
                ?? throw new ValidationException($"BizProcessVariantId {bizProcessVariantId} not found.");

            // Published variants serve their immutable snapshot — draft edits after publish
            // do not alter what's already been published (per this module's versioning design).
            if (variant.LifecycleStatus == 2 && !string.IsNullOrWhiteSpace(variant.PublishedSnapshotJson))
            {
                var snapshot = JsonSerializer.Deserialize<ProcessMapDTO>(variant.PublishedSnapshotJson);
                if (snapshot is not null) return snapshot;
            }

            // Draft (or a published variant whose snapshot somehow failed to deserialize) —
            // project live stage/edge data instead, so preview-before-publish always works.
            return await BuildProcessMapAsync(bizProcessVariantId, login, ct, depth: 0).ConfigureAwait(false);
        }

        public async Task<IEnumerable<WorkflowStepSummaryDTO>> GetStageWorkflowSteps(int workflowId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-stage-workflow-steps", new { workflowId });
            return await _OperationalDataSource.GetWorkflowStepsAsync(workflowId, login, ct).ConfigureAwait(false);
        }

        // Phase 7 -- "where is this document right now". Maps MM's real document chain (walked
        // both directions off a pivot TALLOCATION row) onto this variant's own stages by
        // BizTransactionTypeId: DocumentLevel==0 is the pivot's own stage (current); any other
        // level with a real row is a stage the chain has actually reached (visited, whichever
        // direction); a stage whose BizTransactionTypeId never appears in the chain at all hasn't
        // been reached yet (future).
        public async Task<ProcessInstanceTraceDTO> GetProcessInstanceTrace(
            int bizProcessVariantId, int pivotAllocationId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-instance-trace", new { bizProcessVariantId, pivotAllocationId });

            var stages = (await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)).ToList();
            var traceRows = (await _OperationalDataSource.GetDocumentTraceAsync(pivotAllocationId, login, ct).ConfigureAwait(false)).ToList();

            var stagesByTransactionType = stages
                .Where(s => s.BizTransactionTypeId != -1)
                .GroupBy(s => s.BizTransactionTypeId)
                .ToDictionary(g => g.Key, g => g.Select(s => s.BizProcessStageId.ToString()).ToList());

            var result = new ProcessInstanceTraceDTO();
            var referencedTransactionTypeIds = new HashSet<int>();

            foreach (var row in traceRows)
            {
                if (!stagesByTransactionType.TryGetValue(row.BizTransactionTypeId, out var stageIds)) continue;
                referencedTransactionTypeIds.Add(row.BizTransactionTypeId);

                var target = row.DocumentLevel == 0 ? result.CurrentStageIds : result.VisitedStageIds;
                target.AddRange(stageIds.Except(target));
            }

            // A stage resolved as "current" shouldn't also linger in "visited" -- can happen when
            // the same BizTransactionTypeId is reused at more than one point in the variant.
            result.VisitedStageIds.RemoveAll(id => result.CurrentStageIds.Contains(id));

            // Entity Viewer drill-through -- the pivot row IS the exact document the caller
            // asked to trace, so its own ObjectHeaderTypeId/ObjectHeaderId is the real
            // MENTITY.ENTITYID + PK for that document (see DocumentTraceRowDTO's own comment).
            var pivotRow = traceRows.FirstOrDefault(r => r.DocumentLevel == 0);
            if (pivotRow is not null && pivotRow.ObjectHeaderTypeId != -1 && pivotRow.ObjectHeaderId != -1)
            {
                result.CurrentEntityTypeId = pivotRow.ObjectHeaderTypeId;
                result.CurrentObjectId = pivotRow.ObjectHeaderId;
            }

            foreach (var kvp in stagesByTransactionType)
            {
                if (referencedTransactionTypeIds.Contains(kvp.Key)) continue;
                result.FutureStageIds.AddRange(kvp.Value);
            }

            var currentStage = stages.FirstOrDefault(s => result.CurrentStageIds.Contains(s.BizProcessStageId.ToString()));
            if (currentStage is not null && (currentStage.BizTransactionClassId != -1 || currentStage.BizTransactionId != -1))
            {
                var workflow = await _OperationalDataSource.GetWorkflowConfigAsync(
                    currentStage.BizTransactionClassId, currentStage.BizTransactionId, login, ct).ConfigureAwait(false);
                if (workflow is { IsEnabled: true })
                {
                    result.HasWorkflow = true;
                    result.WorkflowId = workflow.WorkFlowId;
                }
            }

            return result;
        }

        // Phase 8 -- open-documents-per-stage dashboard. Groups the variant's own stages by
        // BizTransactionTypeId (same grouping BizProcessBLL already builds for Phase 7), fetches
        // one open-count aggregate covering all of them in a single MM call, then re-expands back
        // to per-stage results (a stage with no linked transaction type, or no open documents,
        // simply gets 0 -- no "is this open" logic invented here, MM's own PENDINGQUANTITY <> 0
        // rule is the only definition of "open").
        public async Task<IEnumerable<StageOpenCountResultDTO>> GetProcessStageOpenCounts(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-stage-open-counts", new { bizProcessVariantId });

            var stages = (await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)).ToList();
            var typeIds = stages.Where(s => s.BizTransactionTypeId != -1).Select(s => s.BizTransactionTypeId).Distinct().ToList();

            var counts = (await _OperationalDataSource.GetOpenCountsAsync(typeIds, login, ct).ConfigureAwait(false))
                .ToDictionary(c => c.BizTransactionTypeId, c => c.OpenCount);

            return stages
                .Where(s => s.BizTransactionTypeId != -1)
                .Select(s => new StageOpenCountResultDTO
                {
                    StageId = s.BizProcessStageId.ToString(),
                    OpenCount = counts.TryGetValue(s.BizTransactionTypeId, out var c) ? c : 0,
                });
        }

        private async Task<ProcessMapDTO> BuildProcessMapAsync(int bizProcessVariantId, LoginDTO login, CancellationToken ct, int depth)
        {
            var stages = (await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)).ToList();
            var edges = await _DAL.GetEdgesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false);

            var map = new ProcessMapDTO { Type = "flowchart" };

            foreach (var stage in stages)
            {
                var node = new ProcessMapNodeDTO
                {
                    Id = stage.BizProcessStageId.ToString(),
                    Label = stage.StageName,
                    ShapeType = MapStageTypeToShapeType(stage.StageType),
                    MenuId = stage.MenuId,
                    Index = stage.StageSequence
                };

                // Phase 6 — workflow/approval badge. Only meaningful for stages that actually
                // reference a real legacy transaction (BizTransactionClassId/Id set) — an
                // unlinked stage has nothing a workflow could be configured against.
                if (stage.BizTransactionClassId != -1 || stage.BizTransactionId != -1)
                {
                    var workflow = await _OperationalDataSource.GetWorkflowConfigAsync(
                        stage.BizTransactionClassId, stage.BizTransactionId, login, ct).ConfigureAwait(false);
                    if (workflow is { IsEnabled: true })
                    {
                        node.HasWorkflow = true;
                        node.WorkflowId = workflow.WorkFlowId;
                    }
                }

                // SubProcess drill-in: recurse into the child process's latest variant,
                // guarded against runaway/cyclic nesting. Confirmed against the real FE
                // contract: subprocess is always an array (empty when none, single-element
                // when populated) — never a bare object.
                if (stage.StageType == 4 && stage.ChildBizProcessId != -1 && depth < MaxSubprocessDepth)
                {
                    var childVariant = await _DAL.GetLatestVariantForProcess(stage.ChildBizProcessId, login, ct).ConfigureAwait(false);
                    if (childVariant is not null)
                    {
                        var childMap = await BuildProcessMapAsync(childVariant.BizProcessVariantId, login, ct, depth + 1).ConfigureAwait(false);
                        node.Subprocess.Add(childMap);
                    }
                }

                map.Nodes.Add(node);
            }

            foreach (var edge in edges)
            {
                map.Edges.Add(new ProcessMapEdgeDTO
                {
                    From = edge.FromStageId.ToString(),
                    To = edge.ToStageId.ToString(),
                    ConnectionLabel = edge.ConnectionLabel
                });
            }

            return map;
        }

        // ── MBIZPROCESSFRAMEWORK ─────────────────────────────────────

        public async Task<IEnumerable<dynamic>> GetSelectListFramework(LoginDTO login, CancellationToken ct)
            => await _DAL.GetSelectListFramework(login, ct).ConfigureAwait(false);

        // ── MBIZPROCESSSTAGEANNOTATION ─────────────────────────────────

        public async Task<IEnumerable<BizProcessStageAnnotationDTO>> GetStageAnnotation(int bizProcessStageId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-stage-annotation", new { bizProcessStageId });
            return await _DAL.GetAnnotationsForStage(bizProcessStageId, login, ct).ConfigureAwait(false);
        }

        public async Task<string> SaveStageAnnotation(BizProcessStageAnnotationDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.BizProcessStageAnnotationId <= 0;

            GB5Trace.Step("validate-stage-annotation", new { dto.BizProcessStageId, dto.FrameworkId });
            try
            {
                RequireId(dto.BizProcessStageId, nameof(dto.BizProcessStageId));
                RequireId(dto.FrameworkId, nameof(dto.FrameworkId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                GB5Trace.Step("save-stage-annotation", new { dto.BizProcessStageAnnotationId, isNew });
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGEANNOTATION, login).ConfigureAwait(false);
                    dto.BizProcessStageAnnotationId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;
                    await _DAL.SaveStageAnnotation(dto, login, ct).ConfigureAwait(false);
                }
                else
                {
                    await _DAL.UpdateStageAnnotation(dto, login, ct).ConfigureAwait(false);
                }

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessStageAnnotationId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.BizProcessStageAnnotationId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-stage-annotation-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-stage-annotation-failed", ex);
                _Logger.LogError(ex, "SaveStageAnnotation failed for BizProcessStageId {Id}", dto.BizProcessStageId);
                throw;
            }
        }

        // ── MBIZPROCESSSTAGECOMPETENCYREQUIREMENT ───────────────────────

        public async Task<IEnumerable<BizProcessStageCompetencyRequirementDTO>> GetStageCompetencyRequirement(int bizProcessStageId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-stage-competency-requirement", new { bizProcessStageId });
            return await _DAL.GetCompetencyRequirementsForStage(bizProcessStageId, login, ct).ConfigureAwait(false);
        }

        public async Task<string> SaveStageCompetencyRequirement(BizProcessStageCompetencyRequirementDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);
            var isNew = dto.StageCompetencyRequirementId <= 0;

            GB5Trace.Step("validate-stage-competency-requirement", new { dto.BizProcessStageId, dto.CompetencyId });
            try
            {
                RequireId(dto.BizProcessStageId, nameof(dto.BizProcessStageId));
                RequireId(dto.CompetencyId, nameof(dto.CompetencyId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSSTAGECOMPETENCYREQUIREMENT, login).ConfigureAwait(false);
                    dto.StageCompetencyRequirementId = autoNumber.StartNumber;
                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = DateTime.UtcNow;
                    await _DAL.SaveCompetencyRequirement(dto, login, ct).ConfigureAwait(false);
                }
                else
                {
                    await _DAL.UpdateCompetencyRequirement(dto, login, ct).ConfigureAwait(false);
                }

                return isNew
                    ? $"{SuccessResponse.SaveSuccessMessage} {dto.StageCompetencyRequirementId}"
                    : $"{SuccessResponse.UpdateSuccessMessage} {dto.StageCompetencyRequirementId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-stage-competency-requirement-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-stage-competency-requirement-failed", ex);
                _Logger.LogError(ex, "SaveStageCompetencyRequirement failed for BizProcessStageId {Id}", dto.BizProcessStageId);
                throw;
            }
        }

        // ── MBIZPROCESSGOVERNANCE ─────────────────────────────────────

        public async Task<IEnumerable<BizProcessGovernanceDTO>> GetGovernance(int bizProcessId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-governance", new { bizProcessId });
            return await _DAL.GetGovernanceForProcess(bizProcessId, login, ct).ConfigureAwait(false);
        }

        public async Task<string> SaveGovernance(BizProcessGovernanceDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);

            GB5Trace.Step("validate-governance", new { dto.BizProcessId, dto.JobRoleId });
            try
            {
                RequireId(dto.BizProcessId, nameof(dto.BizProcessId));
                RequireId(dto.JobRoleId, nameof(dto.JobRoleId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSGOVERNANCE, login).ConfigureAwait(false);
                dto.BizProcessGovernanceId = autoNumber.StartNumber;
                dto.CreatedById = login.UserId;
                dto.CreatedOn = DateTime.UtcNow;

                GB5Trace.Step("save-governance", new { dto.BizProcessGovernanceId });
                await _DAL.SaveGovernance(dto, login, ct).ConfigureAwait(false);

                return $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessGovernanceId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-governance-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-governance-failed", ex);
                _Logger.LogError(ex, "SaveGovernance failed for BizProcessId {Id}", dto.BizProcessId);
                throw;
            }
        }

        // ── MBIZPROCESSROLEDEVPLAN ─────────────────────────────────────

        public async Task<string> SaveRoleDevPlan(BizProcessRoleDevPlanDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);

            GB5Trace.Step("validate-role-dev-plan", new { dto.JobRoleId, dto.PlanType });
            try
            {
                RequireId(dto.JobRoleId, nameof(dto.JobRoleId));
                await _Validation.NotEmpty(dto.ItemText, nameof(dto.ItemText)).ConfigureAwait(false);

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSROLEDEVPLAN, login).ConfigureAwait(false);
                dto.RoleDevPlanId = autoNumber.StartNumber;
                dto.CreatedById = login.UserId;
                dto.CreatedOn = DateTime.UtcNow;

                GB5Trace.Step("save-role-dev-plan", new { dto.RoleDevPlanId });
                await _DAL.SaveRoleDevPlan(dto, login, ct).ConfigureAwait(false);

                return $"{SuccessResponse.SaveSuccessMessage} {dto.RoleDevPlanId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-role-dev-plan-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-role-dev-plan-failed", ex);
                _Logger.LogError(ex, "SaveRoleDevPlan failed for JobRoleId {Id}", dto.JobRoleId);
                throw;
            }
        }

        // ── MBIZPROCESSVARIANTMETRIC ──────────────────────────────────

        public async Task<string> SaveVariantMetric(BizProcessVariantMetricDTO dto, LoginDTO login, CancellationToken ct)
        {
            ArgumentNullException.ThrowIfNull(dto);

            GB5Trace.Step("validate-variant-metric", new { dto.BizProcessVariantId, dto.MetricCategory });
            try
            {
                RequireId(dto.BizProcessVariantId, nameof(dto.BizProcessVariantId));

                dto.TenantId = login.ClientId;
                dto.ModifiedById = login.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.BIZPROCESSVARIANTMETRIC, login).ConfigureAwait(false);
                dto.BizProcessVariantMetricId = autoNumber.StartNumber;
                dto.CreatedById = login.UserId;
                dto.CreatedOn = DateTime.UtcNow;

                GB5Trace.Step("save-variant-metric", new { dto.BizProcessVariantMetricId });
                await _DAL.SaveVariantMetric(dto, login, ct).ConfigureAwait(false);

                return $"{SuccessResponse.SaveSuccessMessage} {dto.BizProcessVariantMetricId}";
            }
            catch (ValidationException vex)
            {
                GB5Trace.MarkFailed("save-variant-metric-validation-failed", vex);
                throw new Exception(vex.Message);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("save-variant-metric-failed", ex);
                _Logger.LogError(ex, "SaveVariantMetric failed for BizProcessVariantId {Id}", dto.BizProcessVariantId);
                throw;
            }
        }

        // Phase 10b — Efficiency (category 1) actuals are a real, single-instance cycle-time
        // reading (max-min DocumentDate across the traced chain, in days) off Phase 7's
        // GetDocumentTraceAsync, when the caller supplies a pivotAllocationId. This is NOT yet
        // the plan's stated "averaged across recent instances" — that needs a "recent documents
        // for this BizTransactionTypeId" list mechanism that doesn't exist anywhere in MM today,
        // and can't be added without live schema verification (gb5-schema MCP unavailable this
        // session) — flagged as a real follow-up, not silently dropped. Effectiveness/Cost/
        // Quality (2-4) still return null: those need a read against PERM's MMEASURE actual-value
        // tracking, which isn't wired into IProcessOperationalDataSource yet either.
        public async Task<IEnumerable<BizProcessVariantMetricResultDTO>> GetProcessVariantMetrics(
            int bizProcessVariantId, int pivotAllocationId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-variant-metrics", new { bizProcessVariantId, pivotAllocationId });
            var metrics = (await _DAL.GetMetricsForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)).ToList();

            decimal? cycleTimeDays = null;
            if (pivotAllocationId != -1 && metrics.Any(m => m.MetricCategory == 1))
            {
                var traceRows = (await _OperationalDataSource.GetDocumentTraceAsync(pivotAllocationId, login, ct).ConfigureAwait(false))
                    .Where(r => r.DocumentDate != default)
                    .ToList();
                if (traceRows.Count > 0)
                {
                    var minDate = traceRows.Min(r => r.DocumentDate);
                    var maxDate = traceRows.Max(r => r.DocumentDate);
                    cycleTimeDays = (decimal)(maxDate - minDate).TotalDays;
                }
            }

            return metrics.Select(m => new BizProcessVariantMetricResultDTO
            {
                BizProcessVariantMetricId = m.BizProcessVariantMetricId,
                MetricCategory = m.MetricCategory,
                TargetValue = m.TargetValue,
                ActualValue = m.MetricCategory == 1 ? cycleTimeDays : null
            });
        }

        // STAGETYPE: 1=Task, 2=Gateway, 3=Decision, 4=SubProcess, 5=Parallel, 6=Loop
        // shapetype vocabulary matches what ProcessMapViewer's loadProcess() already understands.
        private static string MapStageTypeToShapeType(byte stageType) => stageType switch
        {
            2 or 3 => "rhombus",     // Gateway / Decision
            4 => "subroutine",       // SubProcess
            _ => "round"             // Task / Parallel / Loop / default
        };

        private static string StageTypeName(byte stageType) => stageType switch
        {
            1 => "Task", 2 => "Gateway", 3 => "Decision", 4 => "SubProcess", 5 => "Parallel", 6 => "Loop",
            _ => stageType.ToString()
        };

        private static string LifecycleStatusName(byte status) => status switch
        {
            1 => "Draft", 2 => "Published", 3 => "Retired", _ => status.ToString()
        };

        // ── Phase 9a — Document generation (Process Control Copy / Traceability Report) ──
        //
        // BaseReportEndpoint's own PDF export path is a grid renderer bound to real
        // MREPORTVIEW/MREPORTVIEWFIELDS metadata (see GB5Shared/Export/ReportExport.cs) -- it
        // cannot produce a bespoke letterheaded single-document layout. The only real precedent
        // for that in this repo is TMS's CertificatePdfPipeline (hand-built HTML string ->
        // GB5Shared.Export.HybridReport.PdfRenderer.RenderHtmlToPdfAsync) -- mirrored here, with
        // the addition of composing the shared headerhtml.html/footer.html letterhead templates
        // (ReportTemplateResolver), since PdfRenderer itself has no Handlebars awareness and the
        // only place that composition previously happened was ReportExport.cs's own private
        // RenderTemplateFromFileAsync. GB5Shared.Export.HybridReport.TemplateEngine (Handlebars +
        // Redis) was checked and found to be dead code -- zero callers anywhere -- so it is not
        // used here.

        public async Task<byte[]> GetProcessControlCopyPdf(int bizProcessVariantId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-control-copy-pdf", new { bizProcessVariantId });

            var variant = await _DAL.GetProcessVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)
                ?? throw new ValidationException($"BizProcessVariantId {bizProcessVariantId} not found.");
            var stages = (await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false))
                .OrderBy(s => s.StageSequence).ToList();

            var sb = new StringBuilder();
            sb.Append("<div style=\"font-family:Arial,sans-serif;font-size:12px;\">");
            sb.Append($"<h2 style=\"margin-bottom:2px;\">{WebUtility.HtmlEncode(variant.VariantName)} ({WebUtility.HtmlEncode(variant.VariantCode)})</h2>");
            sb.Append($"<p style=\"margin-top:0;color:#555;\">Version {variant.VersionNo} &mdash; {LifecycleStatusName(variant.LifecycleStatus)}</p>");
            if (!string.IsNullOrWhiteSpace(variant.Description))
                sb.Append($"<p>{WebUtility.HtmlEncode(variant.Description)}</p>");

            foreach (var stage in stages)
            {
                sb.Append("<div style=\"border:1px solid #ccc;border-radius:4px;padding:8px;margin-bottom:10px;page-break-inside:avoid;\">");
                sb.Append($"<h3 style=\"margin:0 0 4px;\">{stage.StageSequence}. {WebUtility.HtmlEncode(stage.StageName)}</h3>");
                sb.Append($"<p style=\"margin:2px 0;color:#555;\">Type: {StageTypeName(stage.StageType)}");
                if (stage.BizTransactionTypeId != -1)
                    sb.Append($" &nbsp;|&nbsp; BizTransactionTypeId: {stage.BizTransactionTypeId}");
                sb.Append("</p>");
                if (!string.IsNullOrWhiteSpace(stage.Description))
                    sb.Append($"<p style=\"margin:2px 0;\">{WebUtility.HtmlEncode(stage.Description)}</p>");

                var annotations = (await _DAL.GetAnnotationsForStage(stage.BizProcessStageId, login, ct).ConfigureAwait(false)).ToList();
                if (annotations.Count > 0)
                {
                    sb.Append("<table style=\"width:100%;border-collapse:collapse;margin-top:6px;font-size:11px;\">");
                    sb.Append("<tr style=\"background:#f0f0f0;\"><th style=\"border:1px solid #ddd;padding:3px;text-align:left;\">Framework Id</th><th style=\"border:1px solid #ddd;padding:3px;text-align:left;\">Role Id</th><th style=\"border:1px solid #ddd;padding:3px;text-align:left;\">Method / Requirement</th><th style=\"border:1px solid #ddd;padding:3px;text-align:left;\">Control Point</th></tr>");
                    foreach (var a in annotations)
                    {
                        sb.Append("<tr>");
                        sb.Append($"<td style=\"border:1px solid #ddd;padding:3px;\">{a.FrameworkId}</td>");
                        sb.Append($"<td style=\"border:1px solid #ddd;padding:3px;\">{(a.JobRoleId != -1 ? a.JobRoleId.ToString() : "-")}</td>");
                        sb.Append($"<td style=\"border:1px solid #ddd;padding:3px;\">{WebUtility.HtmlEncode(a.MethodReference ?? a.RequirementRef ?? "-")}</td>");
                        sb.Append($"<td style=\"border:1px solid #ddd;padding:3px;\">{(a.IsControlPoint ? "Yes" : "No")}</td>");
                        sb.Append("</tr>");
                    }
                    sb.Append("</table>");
                }

                var competencies = (await _DAL.GetCompetencyRequirementsForStage(stage.BizProcessStageId, login, ct).ConfigureAwait(false)).ToList();
                if (competencies.Count > 0)
                {
                    sb.Append("<p style=\"margin:6px 0 2px;font-weight:bold;\">Competency Requirements</p><ul style=\"margin:0;padding-left:18px;\">");
                    foreach (var c in competencies)
                    {
                        sb.Append("<li>");
                        sb.Append($"CompetencyId {c.CompetencyId}");
                        if (c.MinLevelNo.HasValue) sb.Append($" (min level {c.MinLevelNo})");
                        if (c.IsMandatory) sb.Append(" &mdash; mandatory");
                        sb.Append("</li>");
                    }
                    sb.Append("</ul>");
                }

                sb.Append("</div>");
            }
            sb.Append("</div>");

            return await RenderDocumentPdfAsync(sb.ToString(), $"Process Control Copy — {variant.VariantName}").ConfigureAwait(false);
        }

        public async Task<byte[]> GetProcessTraceabilityReportPdf(int bizProcessVariantId, int pivotAllocationId, LoginDTO login, CancellationToken ct)
        {
            GB5Trace.Step("get-process-traceability-report-pdf", new { bizProcessVariantId, pivotAllocationId });

            var variant = await _DAL.GetProcessVariant(bizProcessVariantId, login, ct).ConfigureAwait(false)
                ?? throw new ValidationException($"BizProcessVariantId {bizProcessVariantId} not found.");
            var stages = (await _DAL.GetStagesForVariant(bizProcessVariantId, login, ct).ConfigureAwait(false))
                .OrderBy(s => s.StageSequence).ToList();
            var trace = await GetProcessInstanceTrace(bizProcessVariantId, pivotAllocationId, login, ct).ConfigureAwait(false);

            string BucketFor(string stageId)
            {
                if (trace.CurrentStageIds.Contains(stageId)) return "Current";
                if (trace.VisitedStageIds.Contains(stageId)) return "Visited";
                if (trace.FutureStageIds.Contains(stageId)) return "Future";
                return "Not Linked";
            }

            string ColorFor(string bucket) => bucket switch
            {
                "Current" => "#e65100",
                "Visited" => "#1976d2",
                "Future" => "#9e9e9e",
                _ => "#bdbdbd"
            };

            var sb = new StringBuilder();
            sb.Append("<div style=\"font-family:Arial,sans-serif;font-size:12px;\">");
            sb.Append($"<h2 style=\"margin-bottom:2px;\">{WebUtility.HtmlEncode(variant.VariantName)} ({WebUtility.HtmlEncode(variant.VariantCode)})</h2>");
            sb.Append($"<p style=\"margin-top:0;color:#555;\">Traceability for Allocation Id {pivotAllocationId}</p>");

            sb.Append("<table style=\"width:100%;border-collapse:collapse;font-size:11px;\">");
            sb.Append("<tr style=\"background:#f0f0f0;\"><th style=\"border:1px solid #ddd;padding:4px;text-align:left;\">Seq</th><th style=\"border:1px solid #ddd;padding:4px;text-align:left;\">Stage</th><th style=\"border:1px solid #ddd;padding:4px;text-align:left;\">Status</th></tr>");
            foreach (var stage in stages)
            {
                var bucket = BucketFor(stage.BizProcessStageId.ToString());
                sb.Append("<tr>");
                sb.Append($"<td style=\"border:1px solid #ddd;padding:4px;\">{stage.StageSequence}</td>");
                sb.Append($"<td style=\"border:1px solid #ddd;padding:4px;\">{WebUtility.HtmlEncode(stage.StageName)}</td>");
                sb.Append($"<td style=\"border:1px solid #ddd;padding:4px;color:{ColorFor(bucket)};font-weight:bold;\">{bucket}</td>");
                sb.Append("</tr>");
            }
            sb.Append("</table>");

            if (trace.HasWorkflow)
                sb.Append($"<p style=\"margin-top:10px;color:#e65100;\">Current stage has an approval workflow configured (WorkflowId {trace.WorkflowId}).</p>");

            sb.Append("</div>");

            return await RenderDocumentPdfAsync(sb.ToString(), $"Process Traceability Report — {variant.VariantName}").ConfigureAwait(false);
        }

        // Composes the shared letterhead (headerhtml.html/footer.html, both Handlebars templates
        // -- see this section's header comment) around a caller-supplied body HTML fragment, then
        // renders the whole page via PdfRenderer. Company/branch identity fields are left blank
        // rather than fabricated -- ReportExport.cs's own resolution of those fields depends on a
        // much larger OU/company lookup this module has no reason to duplicate; IsPrintLogo=false
        // keeps the header's logo slots empty rather than referencing a non-existent OULogo1.
        private static async Task<byte[]> RenderDocumentPdfAsync(string bodyHtml, string reportTitle)
        {
            var generatedOn = DateTime.UtcNow;
            var headerFooterContext = new
            {
                Report = new
                {
                    ReportHeader = new { CompanyName = "", BranchName = "", DivisionName = "", IsPrintLogo = false },
                    ReportTitle = reportTitle,
                    OULogo1 = ""
                },
                GeneratedOn = generatedOn
            };

            var hb = Handlebars.Create();
            hb.RegisterHelper("formatDate", (writer, _, parameters) =>
            {
                if (parameters.Length >= 1 && DateTime.TryParse(parameters[0]?.ToString(), out var dt))
                {
                    var fmt = parameters.Length > 1 ? parameters[1]?.ToString() : "dd-MMM-yyyy";
                    writer.WriteSafeString(dt.ToString(fmt));
                }
            });

            var headerSource = await File.ReadAllTextAsync(ReportTemplateResolver.HeaderHtml).ConfigureAwait(false);
            var footerSource = await File.ReadAllTextAsync(ReportTemplateResolver.FooterHtml).ConfigureAwait(false);
            var headerHtml = hb.Compile(headerSource)(headerFooterContext);
            var footerHtml = hb.Compile(footerSource)(headerFooterContext);

            var pageHtml = $"<html><head><meta charset=\"utf-8\"/></head><body>{bodyHtml}</body></html>";
            var metadata = new Dictionary<string, object>
            {
                ["headerTemplate"] = headerHtml,
                ["footerTemplate"] = footerHtml
            };

            return await PdfRenderer.RenderHtmlToPdfAsync(pageHtml, metadata).ConfigureAwait(false);
        }
    }
}