using System.Net.Mail;
using System.Text.Json;
using EntitlementBLL.Common;
using EntitlementBLL.Exceptions;
using EntitlementBLL.Interfaces;
using EntitlementBLL.Legal;
using EntitlementBLL.Options;
using EntitlementDAL.Constants;
using EntitlementDAL.DTOs;
using EntitlementDAL.Enums;
using EntitlementDAL.Interfaces;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.Enums.Client;
using GB5Shared.EventLogPublish;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Query.FrameWork.DbConnection;
using GB5Shared.QueryExecutor;
using GB5Shared.Telemetry;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using static GB5Shared.GB5Constant.Constant;

namespace EntitlementBLL.Onboarding;

public class ClientOnboardingOrchestratorBLL : IClientOnboardingOrchestratorBLL
{
    // SqlWorkbench's ChangeRequestStatus enum (SwDAL.Enums.SwEnums) — mirrored here since
    // Entitlement calls SqlWorkbench over HTTP only, never referencing SwDAL's own enum type.
    private const byte SwCrStatusExecuted = 5;
    private const byte SwCrStatusRejected = 4;
    // SwDAL.Enums.ChangeRequestExecutionStatus.Failed — mirrored as a raw byte, same convention
    // as SwCrStatusExecuted/SwCrStatusRejected above (tracker §37/§43).
    private const byte SwExecutionStatusFailed = 4;

    private readonly IClientProvisioningBLL     _ClientProvisioningBLL;
    private readonly ISubscriptionService       _SubscriptionService;
    private readonly IProvisioningJobDAL        _ProvisioningJobDAL;
    private readonly ISqlWorkbenchClient        _SqlWorkbenchClient;
    private readonly IReferenceDataSyncClient   _ReferenceDataSyncClient;
    private readonly IKeycloakProvisioningClient _KeycloakProvisioningClient;
    private readonly IQueryExecutor             _QueryExecutor;
    private readonly AutoNumber                 _AutoNumber;
    private readonly IEntitlementLoginFactory   _LoginFactory;
    private readonly EventLogPublish            _EventLog;
    private readonly OnboardingOptions          _Options;
    private readonly IProvisioningHubContext?   _HubContext;
    private readonly IAgreementConsentProvider  _AgreementConsentProvider;
    private readonly ILogger<ClientOnboardingOrchestratorBLL> _Logger;

    public ClientOnboardingOrchestratorBLL(
        IClientProvisioningBLL clientProvisioningBLL, ISubscriptionService subscriptionService,
        IProvisioningJobDAL provisioningJobDAL, ISqlWorkbenchClient sqlWorkbenchClient,
        IReferenceDataSyncClient referenceDataSyncClient, IKeycloakProvisioningClient keycloakProvisioningClient,
        IQueryExecutor queryExecutor,
        AutoNumber autoNumber, IEntitlementLoginFactory loginFactory, EventLogPublish eventLog,
        IOptions<OnboardingOptions> options, IAgreementConsentProvider agreementConsentProvider,
        ILogger<ClientOnboardingOrchestratorBLL> logger,
        IProvisioningHubContext? hubContext = null)
    {
        _ClientProvisioningBLL   = clientProvisioningBLL;
        _SubscriptionService     = subscriptionService;
        _ProvisioningJobDAL      = provisioningJobDAL;
        _SqlWorkbenchClient      = sqlWorkbenchClient;
        _ReferenceDataSyncClient = referenceDataSyncClient;
        _KeycloakProvisioningClient = keycloakProvisioningClient;
        _QueryExecutor           = queryExecutor;
        _AutoNumber              = autoNumber;
        _LoginFactory            = loginFactory;
        _EventLog                = eventLog;
        _Options                 = options.Value;
        _AgreementConsentProvider = agreementConsentProvider;
        _Logger                  = logger;
        _HubContext              = hubContext;
    }

    public async Task<OnboardClientResultDTO> StartOnboardingAsync(OnboardClientRequestDTO req, CancellationToken ct)
    {
        if (req is null) throw new ArgumentNullException(nameof(req));
        if (req.PlanId <= 0)
            throw new ArgumentException("PlanId is required.", nameof(req));

        if (_Options.DefaultDbServerId <= 0 || _Options.DefaultDbModelId <= 0)
            throw new InvalidOperationException(
                "Entitlement:Onboarding:DefaultDbServerId/DefaultDbModelId are not fully configured — " +
                "resolve them against real SW.MSWDBSERVER/MSWDBMODEL rows before onboarding a client.");

        // Resolve the DbModel's real, ordered provisioning chain up front — fail fast, before
        // creating any client identity. Falls back to the single DefaultUpgradePackageId only
        // when the DbModel has no chain-registered packages yet (bootstrapping period before
        // the captured base-schema baseline, DB/Migrations/20260824_FullBaseSchema_SqlServer/,
        // is loaded as real MSWDDLSCRIPT/MSWUPGRADEPACKAGE rows). No caller-supplied scope
        // filter exists yet — no UI collects partner/industry/country — so this only ever
        // resolves Universal packages today; scoped overlays are real once that data exists.
        var platformSwLogin = _LoginFactory.Create(-1);
        var resolvedChain = await _SqlWorkbenchClient
            .GetProvisioningChainAsync(_Options.DefaultDbModelId, scope: null, platformSwLogin, ct)
            .ConfigureAwait(false);

        var packageChain = resolvedChain.Count > 0
            ? resolvedChain.Select(p => p.PackageId).ToList()
            : (_Options.DefaultUpgradePackageId > 0 ? new List<int> { _Options.DefaultUpgradePackageId } : new List<int>());

        if (packageChain.Count == 0)
            throw new InvalidOperationException(
                $"No provisioning packages are registered for DbModelId {_Options.DefaultDbModelId}, and " +
                "Entitlement:Onboarding:DefaultUpgradePackageId is not configured as a fallback — resolve " +
                "at least one before onboarding a client.");

        try
        {
            // ── Step 1: client identity (MCLIENT/MUSER) ─────────────────────────────
            // Tracker §49 Decision 9 — a caller (Dedicated-mode demo sessions) may already have a
            // real, lightweight-registered identity for this prospect and just needs a physical
            // DB provisioned on top of it. Reuse it instead of creating a second, duplicate
            // MCLIENT/MUSER pair; every other existing caller leaves ExistingClientId/UserId null,
            // so this branch is never taken for them (behavior-preserving).
            CreateClientResultDTO identity;
            if (req.ExistingClientId is > 0 && req.ExistingUserId is > 0)
            {
                GB5Trace.Step("onboarding-reuse-identity", new { req.ExistingClientId });
                identity = new CreateClientResultDTO
                {
                    ClientId          = req.ExistingClientId.Value,
                    UserId            = req.ExistingUserId.Value,
                    TemporaryPassword = string.Empty,   // already issued at the earlier registration step
                };
            }
            else
            {
                GB5Trace.Step("onboarding-create-identity", new { req.ClientCode });
                identity = await _ClientProvisioningBLL.CreateClientAsync(new CreateClientRequestDTO
                {
                    ClientCode      = req.ClientCode,
                    ClientName      = req.ClientName,
                    ClientShortName = req.ClientShortName,
                    AdminUserCode   = req.AdminUserCode,
                    AdminUserName   = req.AdminUserName,
                    AdminEmail      = req.AdminEmail,
                    AdminMobile     = req.AdminMobile,
                    JurisdictionCode = req.JurisdictionCode,
                    DeploymentType  = req.DeploymentType,
                }, ct).ConfigureAwait(false);
            }

            var clientLogin = _LoginFactory.Create(identity.ClientId, identity.UserId);

            // ── Step 2: MPROVISIONINGJOB row (Running, CLIENT_IDENTITY_CREATED) ─────
            GB5Trace.Step("onboarding-create-job", new { identity.ClientId });
            var jobAuto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.ENTITLEMENTPROVISIONINGJOB, clientLogin)
                .ConfigureAwait(false);
            var jobId = jobAuto.StartNumber;
            var now   = DateTime.UtcNow;

            var completedSteps = new List<string> { ProvisioningStepNames.ClientIdentityCreated };
            var jobDto = new ProvisioningJobDTO
            {
                ProvisioningJobId = jobId,
                ClientId          = identity.ClientId,
                IdmsEngagementRef = req.IdmsEngagementRef ?? string.Empty,
                ProvisioningMode  = 0,   // QuickStart
                TrialMode         = req.TrialMode,
                PlanId            = req.PlanId,
                DeploymentType    = req.DeploymentType,   // tracker §52 — was hardcoded 0/SaaS
                JobStatus         = (byte)ProvisioningJobStatusEnum.Running,
                CurrentStep       = ProvisioningStepNames.ClientIdentityCreated,
                CompletedSteps    = JsonSerializer.Serialize(completedSteps),
                RetryCount        = 0,
                StartedOn         = now,
                RequestPayload    = JsonSerializer.Serialize(req),
                LeadId            = req.LeadId,   // tracker §52.3 — real FK, was RequestPayload-only
                Version           = 1,
                Status            = 1,
                SortOrder         = 1,
                CreatedById       = clientLogin.UserId,
                CreatedOn         = now,
                ModifiedById      = clientLogin.UserId,
                ModifiedOn        = now,
                SourceType        = 0,
                TenantId          = identity.ClientId,
            };

            var jobTx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
            try
            {
                await _ProvisioningJobDAL.SaveAsync(jobDto, clientLogin, jobTx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(jobTx).ConfigureAwait(false);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(jobTx).ConfigureAwait(false);
                throw;
            }

            if (_HubContext is not null)
                await _HubContext.PushStageChangedAsync(
                    identity.ClientId, jobId, ProvisioningStepNames.ClientIdentityCreated,
                    completedSteps.ToArray(), ct).ConfigureAwait(false);

            // ── Step 3: submit (SqlWorkbench, HTTP) a ClientProvisioning ChangeRequest ─
            GB5Trace.Step("onboarding-save-client-database", new { identity.ClientId });
            var swClientDatabaseId = await _SqlWorkbenchClient.SaveClientDatabaseAsync(new SwSaveClientDatabaseRequest
            {
                ClientDbCode   = SanitizeToIdentifier($"{req.ClientCode}DB"),
                ClientDbName   = $"{req.ClientName} Database",
                DbServerId     = _Options.DefaultDbServerId,
                DatabaseName   = SanitizeToIdentifier($"{req.ClientCode}Db"),
                DbModelId      = _Options.DefaultDbModelId,
                ClientDbStatus = 0,   // Pending
                Description    = $"Onboarding-created database for client {req.ClientCode} (ProvisioningJobId={jobId})",
                DatabaseRole   = 0,   // Main
                TenantId       = identity.ClientId,
            }, clientLogin, ct).ConfigureAwait(false);

            // Submit a CR for the FIRST package in the chain only — remaining packages are
            // submitted one at a time as each prior one reaches Executed (see
            // ResumeOneJobAsync's chain-advance branch), never all at once up front.
            var swChangeRequestId = await SubmitPackageChangeRequestAsync(
                packageChain[0], swClientDatabaseId, identity.ClientId, req.ClientName, jobId, clientLogin, ct)
                .ConfigureAwait(false);

            completedSteps.Add(ProvisioningStepNames.DbProvisioningCrSubmitted);
            var pendingChainJson = packageChain.Count > 1
                ? JsonSerializer.Serialize(packageChain.Skip(1).ToList())
                : null;

            var linkTx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
            try
            {
                await _ProvisioningJobDAL.SetSwLinkageAsync(
                    jobId, swChangeRequestId, swClientDatabaseId, pendingChainJson,
                    JsonSerializer.Serialize(completedSteps), clientLogin, linkTx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(linkTx).ConfigureAwait(false);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(linkTx).ConfigureAwait(false);
                throw;
            }

            if (_HubContext is not null)
                await _HubContext.PushStageChangedAsync(
                    identity.ClientId, jobId, ProvisioningStepNames.DbProvisioningCrSubmitted,
                    completedSteps.ToArray(), ct).ConfigureAwait(false);

            GB5Trace.Step("event-publish", new { EventTypeConstant.CLIENTONBOARDINGDBPROVISIONINGSUBMITTEDEVENTTYPEID });
            await _EventLog.PublishEventLogAsync(
                "Client Onboarding — DB Provisioning Submitted",
                new { identity.ClientId, ProvisioningJobId = jobId, SwChangeRequestId = swChangeRequestId },
                EventTypeConstant.CLIENTONBOARDINGDBPROVISIONINGSUBMITTEDEVENTTYPEID,
                identity.ClientId, clientLogin, ct: ct).ConfigureAwait(false);

            return new OnboardClientResultDTO
            {
                ClientId           = identity.ClientId,
                UserId             = identity.UserId,
                TemporaryPassword  = identity.TemporaryPassword,
                ProvisioningJobId  = jobId,
                SwChangeRequestId  = swChangeRequestId,
                SwClientDatabaseId = swClientDatabaseId,
                Message            = _Options.ApprovalMode == OnboardingApprovalMode.AutoApprove
                    ? $"Client identity created; database ChangeRequest 1 of {packageChain.Count} submitted and approved automatically, queued for background execution. Awaiting the completion event to continue provisioning."
                    : $"Client identity created; database ChangeRequest 1 of {packageChain.Count} submitted. Awaiting GOODBOOKS_ADMIN approval in SqlWorkbench before provisioning continues.",
            };
        }
        catch (Exception ex)
        {
            GB5Trace.MarkFailed("onboarding-start-failed", ex);
            throw;
        }
    }

    /// <summary>Submits, submits-for-review, and (in AutoApprove mode) approves+executes a
    /// single ClientProvisioning ChangeRequest for one package in a DbModel's provisioning
    /// chain. Shared by StartOnboardingAsync (the chain's first package) and
    /// ResumeOneJobAsync's chain-advance branch (every subsequent package) — the exact same
    /// submit/approve/execute sequence either way, just called once per package instead of
    /// once per onboarding.</summary>
    private async Task<int> SubmitPackageChangeRequestAsync(
        int packageId, int swClientDatabaseId, int clientId, string clientName,
        int provisioningJobId, LoginDTO clientLogin, CancellationToken ct)
    {
        GB5Trace.Step("onboarding-save-change-request", new { clientId, swClientDatabaseId, packageId });
        var swChangeRequestId = await _SqlWorkbenchClient.SaveChangeRequestAsync(new SwSaveChangeRequestRequest
        {
            Title                    = $"Provision database for {clientName} (onboarding, package {packageId})",
            Description              = $"Auto-submitted by ClientOnboardingOrchestratorBLL for ProvisioningJobId={provisioningJobId}.",
            ClientDatabaseId         = swClientDatabaseId,
            QueryType                = 4,   // DDL
            Category                 = 6,   // ChangeRequestCategory.ClientProvisioning
            ProvisioningMode         = 2,   // ProvisioningMode.FromScripts
            BaselineUpgradePackageId = packageId,
            TenantId                 = clientId,
        }, clientLogin, ct).ConfigureAwait(false);

        await _SqlWorkbenchClient.SubmitChangeRequestAsync(swChangeRequestId, clientLogin, ct).ConfigureAwait(false);

        // AutoApprove mode approves + queues-for-background-execution right here so completion
        // always flows through the same "sqlworkbench.changerequest.executed" event/subscriber
        // path regardless of mode — the only difference is who calls Approve/Queue. Queued
        // (not executed inline) since §36.13 proved a large baseline-package batch can run for
        // several minutes and must never be tied to this HTTP request's own CancellationToken —
        // SqlWorkbench's own ChangeRequestExecutionWorkerJob picks it up on its next tick.
        if (_Options.ApprovalMode == OnboardingApprovalMode.AutoApprove)
        {
            GB5Trace.Step("onboarding-auto-approve-queue", new { swChangeRequestId });
            await _SqlWorkbenchClient.ApproveChangeRequestAsync(
                swChangeRequestId, "Auto-approved by ClientOnboardingOrchestratorBLL.", clientLogin, ct).ConfigureAwait(false);
            await _SqlWorkbenchClient.QueueChangeRequestExecutionAsync(swChangeRequestId, clientLogin, ct).ConfigureAwait(false);
        }

        return swChangeRequestId;
    }

    private const int SelfServiceClientCodeMaxAttempts = 5;

    public async Task<SelfProvisionTrialResultDTO> StartSelfServiceTrialAsync(SelfProvisionTrialRequestDTO req, CancellationToken ct)
    {
        if (req is null) throw new ArgumentNullException(nameof(req));
        if (string.IsNullOrWhiteSpace(req.CompanyName))
            throw new ArgumentException("CompanyName is required.", nameof(req));
        if (string.IsNullOrWhiteSpace(req.ContactName))
            throw new ArgumentException("ContactName is required.", nameof(req));
        if (string.IsNullOrWhiteSpace(req.Email))
            throw new ArgumentException("Email is required.", nameof(req));
        if (string.IsNullOrWhiteSpace(req.Mobile))
            throw new ArgumentException("Mobile is required.", nameof(req));

        try
        {
            _ = new MailAddress(req.Email);
        }
        catch (FormatException)
        {
            throw new ArgumentException("Email is not a valid email address.", nameof(req));
        }

        if (_Options.DefaultTrialPlanId <= 0)
            throw new InvalidOperationException(
                "Entitlement:Onboarding:DefaultTrialPlanId is not configured — set it to a real " +
                "MENTITLEMENTPLAN.PLANID before self-service trial signup can be offered publicly.");

        var companySlug  = SanitizeToIdentifier(req.CompanyName).ToUpperInvariant();
        var companyStub  = companySlug.Length > 6 ? companySlug[..6] : companySlug;
        var emailLocal   = req.Email.Split('@')[0];
        var adminUserCode = SanitizeToIdentifier(emailLocal).ToUpperInvariant();
        if (adminUserCode.Length > 10) adminUserCode = adminUserCode[..10];

        Exception? lastCollision = null;
        for (var attempt = 0; attempt < SelfServiceClientCodeMaxAttempts; attempt++)
        {
            var suffix     = Guid.NewGuid().ToString("N")[..4].ToUpperInvariant();
            var clientCode = $"{companyStub}{suffix}";

            try
            {
                var identity = await StartOnboardingAsync(new OnboardClientRequestDTO
                {
                    ClientCode      = clientCode,
                    ClientName      = req.CompanyName,
                    ClientShortName = clientCode,
                    AdminUserCode   = adminUserCode,
                    AdminUserName   = req.ContactName,
                    AdminEmail      = req.Email,
                    AdminMobile     = req.Mobile,
                    PlanId          = _Options.DefaultTrialPlanId,
                    TrialMode       = 2,   // Blank — no sample-data seeding exists yet (Thread 3)
                    JurisdictionCode = req.JurisdictionCode,
                }, ct).ConfigureAwait(false);

                // Legal/Contract Agreement Consent (tracker §50/§51) — if the caller supplied
                // which agreement versions this visitor accepted (fetched beforehand via
                // Legal/GetApplicableAgreements), record it now against the just-created central
                // account. Deliberately NOT wrapped in try/catch: a caller that explicitly claims
                // "this visitor accepted these versions" gets that claim either genuinely
                // recorded or a real, visible failure — never silently dropped, since the entire
                // point of this mechanism is being able to prove consent was captured. Supplying
                // no version IDs at all (the empty-array default) is fully tolerated and skips
                // this step — nothing is blocked while real, counsel-reviewed content is still
                // pending (§50.2).
                if (req.AcceptedAgreementVersionIds is { Length: > 0 })
                {
                    var clientLogin = _LoginFactory.Create(identity.ClientId);
                    await _AgreementConsentProvider.RecordAcceptanceAsync(
                        AgreementSubjectType.Prospect, identity.ClientId, req.AcceptedAgreementVersionIds,
                        ipAddress: null, userAgent: null, AgreementAcceptanceMethod.Clickwrap,
                        correlationKey: $"self-service-trial-{identity.ClientId}", clientLogin, ct).ConfigureAwait(false);
                }

                return new SelfProvisionTrialResultDTO
                {
                    ClientId          = identity.ClientId,
                    ProvisioningJobId = identity.ProvisioningJobId,
                    TemporaryPassword = identity.TemporaryPassword,
                    Message           = identity.Message,
                };
            }
            catch (ClientCodeAlreadyExistsException ex)
            {
                // Astronomically unlikely with a 4-char random suffix, but a self-service caller
                // has no way to pick a different code themselves — retry with a fresh one rather
                // than surfacing an error they can't act on.
                lastCollision = ex;
            }
        }

        GB5Trace.MarkFailed("self-service-trial-clientcode-exhausted", lastCollision!);
        throw new InvalidOperationException(
            "Could not generate a unique client code after multiple attempts. Please try again.");
    }

    public async Task<TrialProvisioningStatusDTO?> GetTrialProvisioningStatusAsync(int provisioningJobId, string email, CancellationToken ct)
    {
        if (provisioningJobId <= 0 || string.IsNullOrWhiteSpace(email)) return null;

        var platformLogin = _LoginFactory.Create(-1);
        var job = await _ProvisioningJobDAL.GetByIdAsync(provisioningJobId, platformLogin, ct).ConfigureAwait(false);
        if (job is null) return null;

        // Email acts as a lightweight shared secret so a sequential, guessable ProvisioningJobId
        // alone can't be used to enumerate other visitors' signup progress — see this job's own
        // RequestPayload (the exact OnboardClientRequestDTO StartOnboardingAsync was called with).
        string? requestedEmail = null;
        try
        {
            requestedEmail = JsonSerializer.Deserialize<OnboardClientRequestDTO>(job.RequestPayload)?.AdminEmail;
        }
        catch (JsonException)
        {
            // Malformed/legacy RequestPayload — treat as a non-match rather than throwing.
        }

        if (!string.Equals(requestedEmail, email, StringComparison.OrdinalIgnoreCase)) return null;

        return new TrialProvisioningStatusDTO
        {
            JobStatus   = job.JobStatus,
            CurrentStep = job.CurrentStep,
            HasError    = !string.IsNullOrWhiteSpace(job.LastError),
        };
    }

    public async Task<ProvisioningJobDTO?> GetProvisioningJobByIdAsync(int provisioningJobId, CancellationToken ct)
    {
        if (provisioningJobId <= 0) return null;
        var platformLogin = _LoginFactory.Create(-1);
        return await _ProvisioningJobDAL.GetByIdAsync(provisioningJobId, platformLogin, ct).ConfigureAwait(false);
    }

    public async Task ResumeAfterProvisioningExecutedAsync(CancellationToken ct)
    {
        var platformLogin = _LoginFactory.Create(-1);
        var jobs = await _ProvisioningJobDAL.GetAwaitingDbProvisioningAsync(platformLogin, ct).ConfigureAwait(false);

        foreach (var job in jobs)
        {
            if (job.SwChangeRequestId is not > 0) continue;   // defensive — query already filters this

            try
            {
                await ResumeOneJobAsync(job, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                // One job's failure must never stop the rest from being checked.
                GB5Trace.MarkFailed("onboarding-resume-job-failed", ex);
                _Logger.LogError(ex, "ResumeAfterProvisioningExecutedAsync failed for ProvisioningJobId {ProvisioningJobId}",
                    job.ProvisioningJobId);
            }
        }
    }

    private async Task ResumeOneJobAsync(ProvisioningJobDTO job, CancellationToken ct)
    {
        var clientLogin = _LoginFactory.Create(job.ClientId);

        // Re-verify by calling SqlWorkbench directly rather than trusting the Dapr event
        // payload's exact shape — EventLogPublish wraps everything in a PublishDTO/EventLogDTO
        // envelope with the real payload double-JSON-encoded inside .Data, which is fragile to
        // parse reliably. The event is only ever used as a "check now" trigger.
        var status = await _SqlWorkbenchClient.GetChangeRequestStatusAsync(job.SwChangeRequestId!.Value, clientLogin, ct)
            .ConfigureAwait(false);

        if (status is null)
        {
            _Logger.LogWarning("ProvisioningJobId {ProvisioningJobId}: SwChangeRequestId {SwChangeRequestId} not found.",
                job.ProvisioningJobId, job.SwChangeRequestId);
            return;
        }

        if (status.CrStatus == SwCrStatusRejected)
        {
            var rejectedMessage = $"SqlWorkbench ChangeRequest {job.SwChangeRequestId} was rejected.";
            var tx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
            try
            {
                await _ProvisioningJobDAL.MarkFailedAsync(
                    job.ProvisioningJobId, rejectedMessage, clientLogin, tx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
                throw;
            }

            if (_HubContext is not null)
                await _HubContext.PushFailedAsync(job.ClientId, job.ProvisioningJobId, rejectedMessage, ct).ConfigureAwait(false);
            return;
        }

        // A background execution failure (tracker §37/§43) never reaches CrStatus=Executed at
        // all — ExecuteCoreAsync throws before that update, so CrStatus alone can't distinguish
        // "still queued/running, keep waiting" from "genuinely failed." SqlWorkbench's own
        // ProcessQueuedExecutionsAsync fires this same completion event on failure too
        // specifically so this check can happen instead of waiting forever.
        if (status.ExecutionStatus == SwExecutionStatusFailed)
        {
            var failMessage = $"SqlWorkbench ChangeRequest {job.SwChangeRequestId} failed during background execution.";
            var failTx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
            try
            {
                await _ProvisioningJobDAL.MarkFailedAsync(
                    job.ProvisioningJobId, failMessage, clientLogin, failTx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(failTx).ConfigureAwait(false);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(failTx).ConfigureAwait(false);
                throw;
            }

            if (_HubContext is not null)
                await _HubContext.PushFailedAsync(job.ClientId, job.ProvisioningJobId, failMessage, ct).ConfigureAwait(false);
            return;
        }

        if (status.CrStatus != SwCrStatusExecuted)
            return;   // still Submitted/UnderReview/Approved/Queued/Running — check again on the next event

        // If more packages remain in this DbModel's provisioning chain, submit the next one
        // and come back on ITS OWN "sqlworkbench.changerequest.executed" event — subscription
        // creation only happens once every package in the chain has reached Executed, not
        // after just the first.
        var pendingChain = string.IsNullOrWhiteSpace(job.PendingPackageChain)
            ? new List<int>()
            : JsonSerializer.Deserialize<List<int>>(job.PendingPackageChain) ?? new List<int>();

        if (pendingChain.Count > 0)
        {
            var nextPackageId  = pendingChain[0];
            var remainingChain = pendingChain.Skip(1).ToList();

            // The original request (ClientName) is only ever available via RequestPayload —
            // ProvisioningJobDTO itself doesn't carry it. A malformed/legacy payload falls
            // back to a generic label rather than failing the whole chain advance over a
            // cosmetic CR title.
            string clientName;
            try
            {
                clientName = JsonSerializer.Deserialize<OnboardClientRequestDTO>(job.RequestPayload)?.ClientName
                    ?? $"Client {job.ClientId}";
            }
            catch (JsonException)
            {
                clientName = $"Client {job.ClientId}";
            }

            GB5Trace.Step("onboarding-chain-advance", new { job.ProvisioningJobId, nextPackageId });
            var nextCrId = await SubmitPackageChangeRequestAsync(
                nextPackageId, job.SwClientDatabaseId!.Value, job.ClientId, clientName,
                job.ProvisioningJobId, clientLogin, ct).ConfigureAwait(false);

            var chainTx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
            try
            {
                await _ProvisioningJobDAL.SetSwLinkageAsync(
                    job.ProvisioningJobId, nextCrId, job.SwClientDatabaseId!.Value,
                    remainingChain.Count > 0 ? JsonSerializer.Serialize(remainingChain) : null,
                    job.CompletedSteps, clientLogin, chainTx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(chainTx).ConfigureAwait(false);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(chainTx).ConfigureAwait(false);
                throw;
            }

            if (_HubContext is not null)
                await _HubContext.PushStageChangedAsync(
                    job.ClientId, job.ProvisioningJobId, ProvisioningStepNames.DbProvisioningCrSubmitted,
                    JsonSerializer.Deserialize<string[]>(job.CompletedSteps) ?? Array.Empty<string>(), ct)
                    .ConfigureAwait(false);
            return;   // wait for the new CR's own Executed event before continuing
        }

        GB5Trace.Step("onboarding-create-subscription", new { job.ClientId, job.PlanId });
        var now = DateTime.UtcNow;
        await _SubscriptionService.SaveAsync(new SaveSubscriptionRequest
        {
            SubscriptionId    = 0,
            ClientId          = job.ClientId,
            PlanId            = job.PlanId,
            IdmsEngagementRef = job.IdmsEngagementRef,
            TrialFlag         = job.TrialMode != 0,
            StartDate         = now,
            LicenseValidTill  = now.AddYears(1),
            SupportValidTill  = now.AddYears(1),
            HostingValidTill  = now.AddYears(1),
            GracePeriodDays   = 30,
            AutoRenew         = false,
            Remarks           = $"Created by ClientOnboardingOrchestratorBLL for ProvisioningJobId={job.ProvisioningJobId}.",
        }, ct).ConfigureAwait(false);

        var completedSteps = (JsonSerializer.Deserialize<string[]>(job.CompletedSteps) ?? Array.Empty<string>()).ToList();
        completedSteps.Add(ProvisioningStepNames.SubscriptionCreated);

        // Reference-data sync is deliberately best-effort: a failure here must not undo an
        // otherwise-successful onboarding (identity + database + subscription all real) —
        // logged, not thrown, and skipped entirely if no dataset has been configured yet
        // (§31.1 Phase 2 — which tables belong in a "new client baseline" is still open).
        if (_Options.DefaultOnboardingSyncJobId is > 0)
        {
            try
            {
                GB5Trace.Step("onboarding-trigger-sync", new { job.ClientId, SyncJobId = _Options.DefaultOnboardingSyncJobId });
                await _ReferenceDataSyncClient.TriggerSyncJobAsync(
                    _Options.DefaultOnboardingSyncJobId.Value, clientLogin, ct).ConfigureAwait(false);
                completedSteps.Add(ProvisioningStepNames.SyncTriggered);
            }
            catch (Exception ex)
            {
                _Logger.LogWarning(ex, "Reference-data sync trigger failed for ProvisioningJobId {ProvisioningJobId} — " +
                    "onboarding still marked Completed (identity/database/subscription are all real).",
                    job.ProvisioningJobId);
            }
        }
        else
        {
            _Logger.LogInformation(
                "No DefaultOnboardingSyncJobId configured — skipping reference-data sync for ProvisioningJobId {ProvisioningJobId}.",
                job.ProvisioningJobId);
        }

        // Keycloak realm+client auto-provisioning (GB5 Repo-Wide Authentication Hardening plan,
        // "Entitlement provisioning wiring" phase) — same best-effort posture as the
        // reference-data sync above: never undoes an otherwise-successful onboarding. Off by
        // default (KeycloakHost unconfigured) and SaaS-only — OnPrem/BYOCloud/Hybrid tenants stay
        // AuthMode=Native (MSERVERCONFIG's own column default), Keycloak being optional per
        // deployment, not assumed.
        if (!string.IsNullOrWhiteSpace(_Options.KeycloakHost) && job.DeploymentType == (byte)ClientDeploymentType.SaaS)
        {
            try
            {
                GB5Trace.Step("onboarding-provision-keycloak", new { job.ClientId });

                // Gb5System (not EntitlementDb) — MSERVERCONFIG's home, same remapping
                // SqlWorkbenchClient.CreateClient's own helper does for the identical reason.
                var gb5SystemLogin = new LoginDTO { ClientId = job.ClientId, DatabaseName = "Gb5System" };
                string? connectionName = await _QueryExecutor.QuerySingleAsync<string>(
                    gb5SystemLogin, ConnectionQueryBuilder.SELECT_CONNECTIONNAME_BY_CLIENTID,
                    new { ClientId = job.ClientId }, cancellationToken: ct).ConfigureAwait(false);

                if (string.IsNullOrWhiteSpace(connectionName))
                {
                    _Logger.LogWarning(
                        "Keycloak provisioning skipped for ProvisioningJobId {ProvisioningJobId}: no MSERVERCONFIG row found for ClientId {ClientId}.",
                        job.ProvisioningJobId, job.ClientId);
                }
                else
                {
                    string redirectUri = (_Options.KeycloakClientRedirectUriPattern ?? "https://{realm}.goodbookserp.in/*")
                        .Replace("{realm}", connectionName);

                    await _KeycloakProvisioningClient.ProvisionRealmAndClientAsync(
                        realmName: connectionName, clientId: "GB5WEB", clientName: $"GB5WEB ({connectionName})",
                        redirectUriPattern: redirectUri, ct).ConfigureAwait(false);

                    await _QueryExecutor.ExecuteAsync(
                        gb5SystemLogin, ConnectionQueryBuilder.UPDATE_MSERVERCONFIG_KEYCLOAK_AUTH,
                        new { ClientId = job.ClientId, KeycloakHost = _Options.KeycloakHost, KeycloakRealm = connectionName },
                        cancellationToken: ct).ConfigureAwait(false);

                    completedSteps.Add(ProvisioningStepNames.KeycloakProvisioned);
                }
            }
            catch (Exception ex)
            {
                _Logger.LogWarning(ex, "Keycloak provisioning failed for ProvisioningJobId {ProvisioningJobId} — " +
                    "onboarding still marked Completed (identity/database/subscription are all real); tenant stays " +
                    "AuthMode=Native until this is retried.", job.ProvisioningJobId);
            }
        }
        else if (string.IsNullOrWhiteSpace(_Options.KeycloakHost))
        {
            _Logger.LogInformation(
                "No KeycloakHost configured — skipping Keycloak provisioning for ProvisioningJobId {ProvisioningJobId}.",
                job.ProvisioningJobId);
        }

        var completeTx = await _QueryExecutor.BeginTransactionAsync(clientLogin).ConfigureAwait(false);
        try
        {
            await _ProvisioningJobDAL.MarkCompletedAsync(
                job.ProvisioningJobId, ProvisioningStepNames.SubscriptionCreated, JsonSerializer.Serialize(completedSteps),
                clientLogin, completeTx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(completeTx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(completeTx).ConfigureAwait(false);
            throw;
        }

        if (_HubContext is not null)
            await _HubContext.PushCompletedAsync(
                job.ClientId, job.ProvisioningJobId, ProvisioningStepNames.SubscriptionCreated,
                completedSteps.ToArray(), ct).ConfigureAwait(false);

        GB5Trace.Step("event-publish", new { EventTypeConstant.CLIENTONBOARDINGCOMPLETEDEVENTTYPEID });
        await _EventLog.PublishEventLogAsync(
            "Client Onboarding Completed",
            new { job.ClientId, job.ProvisioningJobId },
            EventTypeConstant.CLIENTONBOARDINGCOMPLETEDEVENTTYPEID,
            job.ClientId, clientLogin, ct: ct).ConfigureAwait(false);
    }

    /// <summary>Tracker §37 Decision 2 — called by ClientProvisioningProgressSubscriber on each
    /// "sqlworkbench.changerequest.progress" wake-up. Mirrors ResumeAfterProvisioningExecutedAsync's
    /// own "never trust the event payload, always re-verify via a real call" posture: rather than
    /// parsing EventLogPublish's own double-JSON-encoded envelope for which CR/how far along, this
    /// re-reads every still-waiting job's real ScriptsDone/ScriptsTotal straight from SqlWorkbench
    /// (GetChangeRequestStatusAsync, now enriched for ClientProvisioning CRs) and pushes only the
    /// ones that have real progress to report. A no-op entirely if no hub is registered.</summary>
    public async Task PushDdlProgressForAwaitingJobsAsync(CancellationToken ct)
    {
        if (_HubContext is null) return;

        var platformLogin = _LoginFactory.Create(-1);
        var jobs = await _ProvisioningJobDAL.GetAwaitingDbProvisioningAsync(platformLogin, ct).ConfigureAwait(false);

        foreach (var job in jobs)
        {
            if (job.SwChangeRequestId is not > 0) continue;

            try
            {
                var clientLogin = _LoginFactory.Create(job.ClientId);
                var status = await _SqlWorkbenchClient.GetChangeRequestStatusAsync(job.SwChangeRequestId.Value, clientLogin, ct)
                    .ConfigureAwait(false);

                if (status is { ScriptsTotal: > 0 })
                    await _HubContext.PushDdlProgressAsync(
                        job.ClientId, job.ProvisioningJobId, job.SwChangeRequestId.Value,
                        status.ScriptsDone ?? 0, status.ScriptsTotal.Value, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                // One job's progress-push failure must never stop the rest from being checked,
                // and must never affect the real onboarding state machine — this whole method is
                // best-effort UI feedback layered on top of it.
                _Logger.LogWarning(ex, "PushDdlProgressForAwaitingJobsAsync failed for ProvisioningJobId {ProvisioningJobId}",
                    job.ProvisioningJobId);
            }
        }
    }

    private static string SanitizeToIdentifier(string value)
    {
        var chars = value.Where(c => char.IsLetterOrDigit(c) || c == '_').ToArray();
        var sanitized = new string(chars);
        if (sanitized.Length == 0 || char.IsDigit(sanitized[0]))
            sanitized = "C" + sanitized;
        return sanitized;
    }
}