using System.Data.Common;
using System.Net.Mail;
using System.Text.Json;
using EntitlementBLL.Common;
using EntitlementBLL.Exceptions;
using EntitlementBLL.Interfaces;
using EntitlementBLL.Legal;
using EntitlementBLL.Onboarding;
using EntitlementDAL.DTOs;
using EntitlementDAL.Interfaces;
using GB5Shared.ActionProcessor;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EventLogPublish;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.User;
using Microsoft.Extensions.Logging;
using static GB5Shared.GB5Constant.Constant;

namespace EntitlementBLL.Demo;

public class DemoSessionBLL : IDemoSessionBLL
{
    // How far ahead of ExpiresOn a warning fires — a coarse default (24h), not yet exposed as a
    // per-variant/per-session setting (tracker §49 doesn't ask for that level of tuning yet).
    private const int WarningLeadHours = 24;
    private const int RegistrationClientCodeMaxAttempts = 5;

    // Every pooled demo instance is registered as a Main-role client database — never
    // Report/Archive — so its MSERVERCONFIG.CONNECTIONNAME always follows this one fixed shape.
    // Confirmed by reading ClientDatabaseProvisioner.RegisterAndLinkServerConfigAsync directly:
    // ConnectionName = $"{ClientDbCode}-{DatabaseRole}".ToUpperInvariant().
    private const string PoolInstanceConnectionNameSuffix = "-MAIN";

    // Fallback role code for a Pooled checkout that didn't request a specific DemoRoleCode.
    // Convention, not enforced by any schema constraint: every pooled variant's own seed package
    // is expected to register a role with this code (mirrors DemoVariantDTO.AvailableDemoRoleCodes'
    // own documentation-only nature).
    private const string DefaultPooledDemoRoleCode = "DEMO_GUEST";

    // Reuses the exact same signed-outbox email mechanism as ClientAuthBLL.
    // QueuePasswordResetEmailAsync (MMAILTEMPLATE + TEVENTACTIONRUN/TACTIONOUTBOX +
    // EmailActionHandler) — see 20260903_Entitlement_DemoSessionWarningMailTemplate_Seed_*.sql.
    private const int DemoSessionWarningMailTemplateId = -1389001002;

    private readonly IDemoDAL                          _DemoDAL;
    private readonly IClientProvisioningBLL             _ClientProvisioningBLL;
    private readonly IClientProvisioningDAL             _ClientProvisioningDAL;
    private readonly IEntitlementUserDAL                _EntitlementUserDAL;
    private readonly IClientUserProvisioner             _ClientUserProvisioner;
    private readonly IEventActionRunDAL                 _EventActionRunDAL;
    private readonly IActionOutboxDAL                   _ActionOutboxDAL;
    private readonly IClientOnboardingOrchestratorBLL   _Orchestrator;
    private readonly ISqlWorkbenchClient                _SqlWorkbenchClient;
    private readonly IQueryExecutor                     _QueryExecutor;
    private readonly AutoNumber                         _AutoNumber;
    private readonly IEntitlementLoginFactory            _LoginFactory;
    private readonly EventLogPublish                    _EventLog;
    private readonly IAgreementConsentProvider           _AgreementConsentProvider;
    private readonly ILogger<DemoSessionBLL>             _Logger;

    public DemoSessionBLL(
        IDemoDAL demoDAL, IClientProvisioningBLL clientProvisioningBLL, IClientProvisioningDAL clientProvisioningDAL,
        IEntitlementUserDAL entitlementUserDAL, IClientUserProvisioner clientUserProvisioner,
        IEventActionRunDAL eventActionRunDAL, IActionOutboxDAL actionOutboxDAL,
        IClientOnboardingOrchestratorBLL orchestrator, ISqlWorkbenchClient sqlWorkbenchClient,
        IQueryExecutor queryExecutor, AutoNumber autoNumber,
        IEntitlementLoginFactory loginFactory, EventLogPublish eventLog,
        IAgreementConsentProvider agreementConsentProvider, ILogger<DemoSessionBLL> logger)
    {
        _DemoDAL               = demoDAL;
        _ClientProvisioningBLL = clientProvisioningBLL;
        _ClientProvisioningDAL = clientProvisioningDAL;
        _EntitlementUserDAL    = entitlementUserDAL;
        _ClientUserProvisioner = clientUserProvisioner;
        _EventActionRunDAL     = eventActionRunDAL;
        _ActionOutboxDAL       = actionOutboxDAL;
        _Orchestrator          = orchestrator;
        _SqlWorkbenchClient    = sqlWorkbenchClient;
        _QueryExecutor         = queryExecutor;
        _AutoNumber            = autoNumber;
        _LoginFactory          = loginFactory;
        _EventLog              = eventLog;
        _AgreementConsentProvider = agreementConsentProvider;
        _Logger                = logger;
    }

    public async Task<RegisterDemoProspectResultDTO> RegisterProspectAsync(RegisterDemoProspectRequest 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));
        }

        // Same server-side code derivation + bounded collision retry as
        // ClientOnboardingOrchestratorBLL.StartSelfServiceTrialAsync — an anonymous, landing-page
        // caller must never choose (or be able to collide with another client's) ClientCode/
        // AdminUserCode.
        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];

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

            try
            {
                identity = await _ClientProvisioningBLL.CreateClientAsync(new CreateClientRequestDTO
                {
                    ClientCode      = clientCode,
                    ClientName      = req.CompanyName,
                    ClientShortName = clientCode,
                    AdminUserCode   = adminUserCode,
                    AdminUserName   = req.ContactName,
                    AdminEmail      = req.Email,
                    AdminMobile     = req.Mobile,
                    JurisdictionCode = req.JurisdictionCode,
                }, ct).ConfigureAwait(false);
            }
            catch (ClientCodeAlreadyExistsException ex)
            {
                lastCollision = ex;
            }
        }

        if (identity is null)
            throw new InvalidOperationException(
                "Could not generate a unique client code after multiple attempts. Please try again.", lastCollision);

        var login = _LoginFactory.Create(identity.ClientId, identity.UserId);
        var now   = DateTime.UtcNow;
        var preference = new DemoPreferenceDTO
        {
            ClientId                  = identity.ClientId,
            PreferredPartnerProductId = req.PreferredPartnerProductId,
            PreferredPlanId           = req.PreferredPlanId,
            PreferredIndustryCode     = req.PreferredIndustryCode ?? string.Empty,
            PreferredGeographyCode    = req.PreferredGeographyCode ?? string.Empty,
            PreferredFeatureId        = req.PreferredFeatureId,
            RequestedOn               = now,
            CreatedById               = login.UserId,
            CreatedOn                 = now,
            ModifiedById              = login.UserId,
            ModifiedOn                = now,
        };

        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.SavePreferenceAsync(preference, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        // Legal/Contract Agreement Consent (tracker §50/§51) — same posture as
        // ClientOnboardingOrchestratorBLL.StartSelfServiceTrialAsync's identical wiring:
        // 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. Supplying no version IDs (the empty-array default)
        // is fully tolerated and skips this step entirely.
        if (req.AcceptedAgreementVersionIds is { Length: > 0 })
        {
            await _AgreementConsentProvider.RecordAcceptanceAsync(
                AgreementSubjectType.Prospect, identity.ClientId, req.AcceptedAgreementVersionIds,
                ipAddress: null, userAgent: null, AgreementAcceptanceMethod.Clickwrap,
                correlationKey: $"demo-registration-{identity.ClientId}", login, ct).ConfigureAwait(false);
        }

        return new RegisterDemoProspectResultDTO
        {
            ClientId          = identity.ClientId,
            UserId            = identity.UserId,
            TemporaryPassword = identity.TemporaryPassword,
        };
    }

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

    public async Task<DemoSessionDTO> CheckOutPooledSessionAsync(CheckOutPooledSessionRequest req, CancellationToken ct)
    {
        if (req is null) throw new ArgumentNullException(nameof(req));
        if (req.ClientId <= 0)
            throw new ArgumentException("ClientId is required — call RegisterProspectAsync first.", nameof(req));
        if (req.DemoVariantId <= 0) throw new ArgumentException("DemoVariantId is required.", nameof(req));
        if (req.OwnerUserId <= 0) throw new ArgumentException("OwnerUserId is required.", nameof(req));

        var login = _LoginFactory.Create(-1);

        // Optimistic claim, bounded retries — TryClaimPoolInstanceAsync's UPDATE only succeeds
        // if the row is still Available at write time, closing the race window a concurrent
        // checkout could otherwise hit between the SELECT and the claim.
        DemoPoolInstanceDTO? claimed = null;
        for (var attempt = 0; attempt < 5 && claimed is null; attempt++)
        {
            var candidate = await _DemoDAL.GetOneAvailablePoolInstanceAsync(req.DemoVariantId, login, ct).ConfigureAwait(false);
            if (candidate is null) break;

            var claimTx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
            try
            {
                var claimedNow = await _DemoDAL.TryClaimPoolInstanceAsync(candidate.DemoPoolInstanceId, login, claimTx, ct).ConfigureAwait(false);
                await _QueryExecutor.CommitAsync(claimTx).ConfigureAwait(false);
                if (claimedNow) claimed = candidate;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(claimTx).ConfigureAwait(false);
                throw;
            }
        }

        if (claimed is null)
            throw new InvalidOperationException($"No available pooled demo instance for DemoVariantId {req.DemoVariantId} right now.");

        var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.ENTITLEMENTDEMOSESSION, login).ConfigureAwait(false);
        var now  = DateTime.UtcNow;
        var session = new DemoSessionDTO
        {
            DemoSessionId      = auto.StartNumber,
            DemoVariantId      = req.DemoVariantId,
            DemoMode           = (byte)DemoMode.Pooled,
            DemoPoolInstanceId = claimed.DemoPoolInstanceId,
            ClientId           = req.ClientId,
            LeadId             = req.LeadId,
            DemoRoleCode       = req.DemoRoleCode,
            OwnerUserId        = req.OwnerUserId,
            StartedOn          = now,
            ExpiresOn          = now.AddHours(req.DurationHours <= 0 ? 24 : req.DurationHours),
            SessionStatus      = (byte)DemoSessionStatus.Active,
            CreatedById        = login.UserId,
            CreatedOn          = now,
            ModifiedById       = login.UserId,
            ModifiedOn         = now,
        };

        // Per-prospect MUSER creation inside the pool instance's own physical database (Decision
        // 8/9). Deliberately NOT rolled back if this fails — the pool instance is already claimed
        // and the session is still a real, usable record (owner/reporting/expiry all work); a
        // failure here only means this one prospect doesn't get a working login yet, logged for
        // manual follow-up rather than losing the claimed slot or the session altogether.
        try
        {
            session.PooledDemoUserId = await ProvisionPooledDemoUserAsync(claimed, req, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _Logger.LogWarning(ex,
                "CheckOutPooledSessionAsync: per-prospect MUSER creation failed for DemoPoolInstanceId {DemoPoolInstanceId} / ClientId {ClientId} — session created without a working login; needs manual follow-up.",
                claimed.DemoPoolInstanceId, req.ClientId);
        }

        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.SaveSessionAsync(session, login, tx, ct).ConfigureAwait(false);
            await AppendEventAsync(session.DemoSessionId, DemoSessionEventType.CheckedOut, null, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session Checked Out", new { session.DemoSessionId, req.DemoVariantId },
            EventTypeConstant.DEMOSESSIONCHECKEDOUTEVENTTYPEID, session.DemoSessionId, login, ct: ct).ConfigureAwait(false);

        return session;
    }

    public async Task<DemoSessionDTO> StartDedicatedSessionAsync(StartDedicatedSessionRequest req, CancellationToken ct)
    {
        if (req is null) throw new ArgumentNullException(nameof(req));
        if (req.ClientId <= 0 || req.UserId <= 0)
            throw new ArgumentException("ClientId/UserId are required — call RegisterProspectAsync first.", nameof(req));
        if (req.OwnerUserId <= 0) throw new ArgumentException("OwnerUserId is required.", nameof(req));

        // Decision 9 — reuse the identity RegisterProspectAsync already created rather than
        // letting StartOnboardingAsync create a second, duplicate MCLIENT/MUSER for the same
        // prospect. Provisions a real physical DB for that SAME central account.
        req.OnboardRequest.ExistingClientId = req.ClientId;
        req.OnboardRequest.ExistingUserId   = req.UserId;
        var onboardResult = await _Orchestrator.StartOnboardingAsync(req.OnboardRequest, ct).ConfigureAwait(false);

        var login = _LoginFactory.Create(-1);
        var auto  = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.ENTITLEMENTDEMOSESSION, login).ConfigureAwait(false);
        var now   = DateTime.UtcNow;
        var session = new DemoSessionDTO
        {
            DemoSessionId   = auto.StartNumber,
            DemoMode        = (byte)DemoMode.Dedicated,
            ClientId        = onboardResult.ClientId,
            LeadId          = req.LeadId,
            OwnerUserId     = req.OwnerUserId,
            StartedOn       = now,
            ExpiresOn       = now.AddDays(req.DurationDays <= 0 ? 14 : req.DurationDays),
            SessionStatus   = (byte)DemoSessionStatus.Active,
            CreatedById     = login.UserId,
            CreatedOn       = now,
            ModifiedById    = login.UserId,
            ModifiedOn      = now,
        };

        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.SaveSessionAsync(session, login, tx, ct).ConfigureAwait(false);
            await AppendEventAsync(session.DemoSessionId, DemoSessionEventType.CheckedOut, null, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session (Dedicated) Checked Out", new { session.DemoSessionId, onboardResult.ClientId },
            EventTypeConstant.DEMOSESSIONCHECKEDOUTEVENTTYPEID, session.DemoSessionId, login, ct: ct).ConfigureAwait(false);

        return session;
    }

    public async Task<DemoSessionDTO?> GetByIdAsync(int demoSessionId, CancellationToken ct)
        => await _DemoDAL.GetSessionByIdAsync(demoSessionId, _LoginFactory.Create(-1), ct).ConfigureAwait(false);

    public async Task<IEnumerable<DemoSessionDTO>> GetListByOwnerAsync(int ownerUserId, CancellationToken ct)
        => await _DemoDAL.GetSessionListByOwnerAsync(ownerUserId, _LoginFactory.Create(-1), ct).ConfigureAwait(false);

    public async Task RequestExtensionAsync(int demoSessionId, string? comment, CancellationToken ct)
    {
        var login = _LoginFactory.Create(-1);
        _ = await _DemoDAL.GetSessionByIdAsync(demoSessionId, login, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"DemoSessionId {demoSessionId} not found.");

        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            var rows = await _DemoDAL.RequestExtensionAsync(demoSessionId, DateTime.UtcNow, comment, login, tx, ct).ConfigureAwait(false);
            if (rows == 0)
                throw new InvalidOperationException(
                    $"DemoSessionId {demoSessionId} is not in a state that can request an extension (must be Active/Extended/WarningIssued).");
            await AppendEventAsync(demoSessionId, DemoSessionEventType.ExtensionRequested, comment, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session Extension Requested", new { demoSessionId },
            EventTypeConstant.DEMOSESSIONEXTENSIONREQUESTEDEVENTTYPEID, demoSessionId, login, ct: ct).ConfigureAwait(false);
    }

    public async Task ApproveExtensionAsync(int demoSessionId, int approvedById, DateTime newExpiresOn, CancellationToken ct)
    {
        var login = _LoginFactory.Create(-1);
        var session = await _DemoDAL.GetSessionByIdAsync(demoSessionId, login, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"DemoSessionId {demoSessionId} not found.");

        // Per the user's own confirmed choice (tracker §49 Decision 5) — only the sales/CS rep
        // who owns this specific engagement may approve, not any broad admin gate.
        if (session.OwnerUserId != approvedById)
            throw new UnauthorizedAccessException("Only the session's own OwnerUserId (sales/CS rep) may approve an extension.");

        var now = DateTime.UtcNow;
        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            var rows = await _DemoDAL.ApproveExtensionAsync(demoSessionId, newExpiresOn, approvedById, now, login, tx, ct).ConfigureAwait(false);
            if (rows == 0) throw new InvalidOperationException($"DemoSessionId {demoSessionId} is not PendingApproval.");
            await AppendEventAsync(demoSessionId, DemoSessionEventType.ExtensionApproved, null, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session Extension Approved", new { demoSessionId, newExpiresOn },
            EventTypeConstant.DEMOSESSIONEXTENSIONAPPROVEDEVENTTYPEID, demoSessionId, login, ct: ct).ConfigureAwait(false);
    }

    public async Task RejectExtensionAsync(int demoSessionId, int rejectedById, CancellationToken ct)
    {
        var login = _LoginFactory.Create(-1);
        var session = await _DemoDAL.GetSessionByIdAsync(demoSessionId, login, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"DemoSessionId {demoSessionId} not found.");

        if (session.OwnerUserId != rejectedById)
            throw new UnauthorizedAccessException("Only the session's own OwnerUserId (sales/CS rep) may reject an extension.");

        var now = DateTime.UtcNow;
        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            var rows = await _DemoDAL.RejectExtensionAsync(demoSessionId, rejectedById, now, login, tx, ct).ConfigureAwait(false);
            if (rows == 0) throw new InvalidOperationException($"DemoSessionId {demoSessionId} is not PendingApproval.");
            await AppendEventAsync(demoSessionId, DemoSessionEventType.ExtensionRejected, null, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session Extension Rejected", new { demoSessionId },
            EventTypeConstant.DEMOSESSIONEXTENSIONREJECTEDEVENTTYPEID, demoSessionId, login, ct: ct).ConfigureAwait(false);
    }

    public async Task<int> ProcessExpiringAndExpiredSessionsAsync(CancellationToken ct)
    {
        var login = _LoginFactory.Create(-1);
        var now   = DateTime.UtcNow;
        var processed = 0;

        var approaching = await _DemoDAL.GetSessionsApproachingExpiryAsync(now.AddHours(WarningLeadHours), login, ct).ConfigureAwait(false);
        foreach (var session in approaching)
        {
            if (session.ExpiresOn <= now) continue; // already past expiry — the expiry pass below handles it, not this one
            try
            {
                var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
                bool marked;
                try
                {
                    var rows = await _DemoDAL.MarkWarningIssuedAsync(session.DemoSessionId, login, tx, ct).ConfigureAwait(false);
                    marked = rows > 0;
                    if (marked)
                        await AppendEventAsync(session.DemoSessionId, DemoSessionEventType.WarningIssued, null, login, tx, ct).ConfigureAwait(false);
                    await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
                }
                catch
                {
                    await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
                    throw;
                }

                if (marked)
                {
                    await _EventLog.PublishEventLogAsync(
                        "Demo Session Warning Issued", new { session.DemoSessionId, session.ExpiresOn },
                        EventTypeConstant.DEMOSESSIONWARNINGISSUEDEVENTTYPEID, session.DemoSessionId, login, ct: ct).ConfigureAwait(false);

                    // Email channel of Decision 6's two-channel warning (in-app banner is a plain
                    // status read elsewhere, not this job's concern). Non-fatal — a failed email
                    // queue shouldn't undo the already-committed WarningIssued status.
                    try
                    {
                        await QueueWarningEmailAsync(session, login, ct).ConfigureAwait(false);
                    }
                    catch (Exception emailEx)
                    {
                        _Logger.LogWarning(emailEx,
                            "ProcessExpiringAndExpiredSessionsAsync: warning email queue failed for DemoSessionId {DemoSessionId} — status already committed, email skipped.",
                            session.DemoSessionId);
                    }

                    processed++;
                }
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex, "ProcessExpiringAndExpiredSessionsAsync: warning step failed for DemoSessionId {DemoSessionId}", session.DemoSessionId);
            }
        }

        var expired = await _DemoDAL.GetExpiredSessionsAsync(now, login, ct).ConfigureAwait(false);
        foreach (var session in expired)
        {
            try
            {
                await ExpireOneSessionAsync(session, login, ct).ConfigureAwait(false);
                processed++;
            }
            catch (Exception ex)
            {
                _Logger.LogError(ex, "ProcessExpiringAndExpiredSessionsAsync: expiry step failed for DemoSessionId {DemoSessionId}", session.DemoSessionId);
            }
        }

        return processed;
    }

    private async Task ExpireOneSessionAsync(DemoSessionDTO session, LoginDTO login, CancellationToken ct)
    {
        var tx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.MarkExpiredAsync(session.DemoSessionId, login, tx, ct).ConfigureAwait(false);
            await AppendEventAsync(session.DemoSessionId, DemoSessionEventType.Expired, null, login, tx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(tx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(tx).ConfigureAwait(false);
            throw;
        }

        await _EventLog.PublishEventLogAsync(
            "Demo Session Expired", new { session.DemoSessionId },
            EventTypeConstant.DEMOSESSIONEXPIREDEVENTTYPEID, session.DemoSessionId, login, ct: ct).ConfigureAwait(false);

        if (session.DemoMode == (byte)DemoMode.Pooled && session.DemoPoolInstanceId is > 0)
        {
            await ResetPooledInstanceAsync(session.DemoPoolInstanceId.Value, session.DemoVariantId, login, ct).ConfigureAwait(false);
        }
        else if (session.DemoMode == (byte)DemoMode.Dedicated)
        {
            // Dedicated teardown/archival is Trial's own eventual disposition question (tracker
            // §49 Decision 4) — not resolved yet. Marked Expired above; its MCLIENT/database is
            // deliberately left alone rather than guessing at a destructive teardown.
            _Logger.LogInformation(
                "Dedicated DemoSessionId {DemoSessionId} expired — its MCLIENT/database is left as-is (teardown policy not yet decided, tracker §49).",
                session.DemoSessionId);
        }
    }

    private async Task ResetPooledInstanceAsync(int demoPoolInstanceId, int? demoVariantId, LoginDTO login, CancellationToken ct)
    {
        var instance = await _DemoDAL.GetPoolInstanceByIdAsync(demoPoolInstanceId, login, ct).ConfigureAwait(false);
        if (instance is null) return;

        var variant = demoVariantId is > 0
            ? await _DemoDAL.GetVariantByIdAsync(demoVariantId.Value, login, ct).ConfigureAwait(false)
            : null;

        var resettingTx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.UpdatePoolInstanceStateAsync(demoPoolInstanceId, (byte)DemoPoolState.Resetting, login, resettingTx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(resettingTx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(resettingTx).ConfigureAwait(false);
            throw;
        }

        try
        {
            // Re-applies the variant's baseline reference data (real, idempotent — §36's own
            // INSERT...WHERE NOT EXISTS scripts). Does NOT clear occupant-written mutable-table
            // data first — MDEMOVARIANT.MutableTableList exists for this purpose, but no TRUNCATE
            // mechanism has been built yet (tracker §49's own flagged, real gap). A pooled
            // instance's mutable data persists across reuses until that's added.
            if (variant?.SeedPackageId is > 0)
                await _SqlWorkbenchClient.ReapplyUpgradePackageAsync(
                    instance.SwClientDatabaseId, variant.SeedPackageId.Value, login, ct).ConfigureAwait(false);
        }
        catch (Exception ex)
        {
            _Logger.LogWarning(ex,
                "ResetPooledInstanceAsync: reapply failed for DemoPoolInstanceId {DemoPoolInstanceId} — left in Resetting state for manual follow-up.",
                demoPoolInstanceId);
            return; // leave in Resetting rather than falsely marking Available
        }

        var availableTx = await _QueryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);
        try
        {
            await _DemoDAL.UpdatePoolInstanceStateAsync(demoPoolInstanceId, (byte)DemoPoolState.Available, login, availableTx, ct).ConfigureAwait(false);
            await _QueryExecutor.CommitAsync(availableTx).ConfigureAwait(false);
        }
        catch
        {
            await _QueryExecutor.RollbackAsync(availableTx).ConfigureAwait(false);
            throw;
        }
    }

    private async Task AppendEventAsync(int demoSessionId, string eventType, string? comment, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.ENTITLEMENTDEMOSESSIONEVENT, login).ConfigureAwait(false);
        var now  = DateTime.UtcNow;
        await _DemoDAL.InsertSessionEventAsync(new DemoSessionEventDTO
        {
            DemoSessionEventId = auto.StartNumber,
            DemoSessionId      = demoSessionId,
            EventType          = eventType,
            EventOn            = now,
            EventComment       = comment,
            CreatedById        = login.UserId,
            CreatedOn          = now,
        }, login, tx, ct).ConfigureAwait(false);
    }

    /// <summary>Decision 6's email channel — mirrors ClientAuthBLL.QueuePasswordResetEmailAsync's
    /// exact mechanism (real, already-proven signed-outbox delivery, not a new one). Resolves the
    /// session's own ClientId back to its central admin contact (Name/Email) rather than needing
    /// that threaded through DemoSessionDTO itself.</summary>
    private async Task QueueWarningEmailAsync(DemoSessionDTO session, LoginDTO login, CancellationToken ct)
    {
        var contact = await _EntitlementUserDAL.GetUserByClientIdAsync(session.ClientId, login, ct).ConfigureAwait(false);
        if (contact is null)
        {
            _Logger.LogWarning("QueueWarningEmailAsync: no admin contact found for ClientId {ClientId} — skipping.", session.ClientId);
            return;
        }

        var correlationKey = $"entitlement-demowarn-{session.DemoSessionId}-{Guid.NewGuid():N}";

        var actionRunId = await _EventActionRunDAL.InsertAsync(new EventActionRunDTO
        {
            ActionId       = -1,
            JobExecutionId = -1,
            EventTypeId    = EventTypeConstant.DEMOSESSIONWARNINGISSUEDEVENTTYPEID,
            Payload        = JsonSerializer.Serialize(new { contact.UserName, session.DemoSessionId, session.ExpiresOn }),
            CorrelationKey = correlationKey
        }, login, ct).ConfigureAwait(false);

        var actionEventDto = new ActionEventDto
        {
            ActionRunId    = actionRunId,
            ActionId       = -1,
            ActionType     = 0, // Email
            TenantId       = session.ClientId,
            DatabaseName   = login.DatabaseName,
            SendTo         = contact.UserPrimaryMail,
            ToDeliveryType = 3, // direct email address
            TemplateId     = DemoSessionWarningMailTemplateId,
            CorrelationKey = correlationKey,
            Payload        = JsonSerializer.SerializeToElement(new
            {
                FullName     = contact.UserName,
                session.DemoSessionId,
                ExpiresOn    = session.ExpiresOn.ToString("f"),
            })
        };

        var partition = Math.Abs(session.ClientId % 5);
        await _ActionOutboxDAL.InsertAsync(new ActionOutboxDTO
        {
            ActionRunId      = actionRunId,
            DestinationTopic = $"action-exec-p{partition}",
            Payload          = JsonSerializer.Serialize(actionEventDto),
            TenantId         = session.ClientId,
            CorrelationKey   = correlationKey
        }, login, ct).ConfigureAwait(false);
    }

    /// <summary>Tracker §49 Decision 8/9's corrected design — a real per-prospect MUSER, created
    /// via the same function-level IClientUserProvisioner.ProvisionAsync reuse
    /// ClientProvisioningBLL/ClientUserProvisioner already use for real client onboarding, just
    /// pointed at the pool instance's own physical database instead of Entitlement's shared
    /// platform database. Never a raw table-level INSERT via a bespoke SqlWorkbench endpoint —
    /// this reuses the real business-logic function, autonumber allocation and password
    /// generation/hashing included.</summary>
    private async Task<int?> ProvisionPooledDemoUserAsync(
        DemoPoolInstanceDTO claimed, CheckOutPooledSessionRequest req, LoginDTO entitlementLogin, CancellationToken ct)
    {
        var centralUser = await _EntitlementUserDAL.GetUserByIdAsync(req.UserId, entitlementLogin, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"UserId {req.UserId} not found — call RegisterProspectAsync first.");

        var dbInfo = await _SqlWorkbenchClient.GetClientDatabaseAsync(claimed.SwClientDatabaseId, entitlementLogin, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException($"SqlWorkbench has no ClientDbId {claimed.SwClientDatabaseId} for DemoPoolInstanceId {claimed.DemoPoolInstanceId}.");

        var poolLogin = new LoginDTO
        {
            ClientId       = claimed.LocalClientId,
            UserId         = -1,
            DatabaseName   = $"{dbInfo.ClientDbCode}{PoolInstanceConnectionNameSuffix}".ToUpperInvariant(),
            ServerConfigId = dbInfo.ServerConfigId ?? 0,
        };

        var roleCode = string.IsNullOrWhiteSpace(req.DemoRoleCode) ? DefaultPooledDemoRoleCode : req.DemoRoleCode;
        var roleId = await _ClientProvisioningDAL.GetRoleIdByCodeAsync(roleCode, poolLogin, ct).ConfigureAwait(false)
            ?? throw new InvalidOperationException(
                $"RoleCode '{roleCode}' is not seeded in DemoPoolInstanceId {claimed.DemoPoolInstanceId}'s own database — " +
                "every pooled variant's seed package must register it (see MDEMOVARIANT.AvailableDemoRoleCodes).");

        var result = await _ClientUserProvisioner.ProvisionAsync(new ProvisionClientUserRequest
        {
            UserCode          = centralUser.UserCode,
            UserName          = centralUser.UserName,
            UserPrimaryMail   = centralUser.UserPrimaryMail,
            UserPrimaryMobile = centralUser.UserPrimaryMobile,
            RoleId            = roleId,
        }, poolLogin).ConfigureAwait(false);

        return result.UserId;
    }
}