using Dapper;
using GB5Shared.ActionProcessor;
using GB5Shared.FileUpload;
using FrameworkBLL.SystemJob;
using FrameworkDAL.CustomCode.User;
using FrameworkDAL.DTO.SysJobRun;
using FrameworkDAL.DTO.SystemJob;
using FrameworkDAL.DTO.User;
using FrameworkDAL.Query.SysJobRun;
using FrameworkDAL.Query.SystemJob;
using FrameworkSL.Hubs.SysJob;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.CommonConfig;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.ServerConfig;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using Microsoft.AspNetCore.SignalR;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using Quartz;
using System;
using System.Data;
using System.IO;
using Microsoft.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkSL.Controllers.SysJob
{
    /// <summary>
    /// Quartz job — polls TSYSJOB for pending rows (RUNSTATUS=0) and executes them
    /// by calling the configured MWEBSERVICE endpoint.
    ///
    /// Lifecycle per job:
    ///   1. CLAIM_JOB — atomically sets RUNSTATUS=1 (only if still 0; prevents double-pickup)
    ///   2. Create TSYSJOBRUN (StartedAt, Worker)
    ///   3. Call web service via IHttpClientFactory
    ///   4. Update TSYSJOBRUN (EndedAt, Status, Log)
    ///   5. COMPLETE_JOB / FAIL_OR_RETRY_JOB on TSYSJOB
    ///
    /// Register in Program.cs with a trigger interval (e.g. every 30 seconds).
    /// </summary>
    [DisallowConcurrentExecution]
    public class SysJobExecutorQuartzJob : IJob
    {
        private const int BatchSize       = 10;
        private const int HttpTimeoutS    = 120;
        // Mirrors ActionProcessorWorker.MaxParallel — same hardcoded-constant convention this
        // repo already uses for concurrency caps (no IConfiguration wiring elsewhere either).
        private const int MaxParallelJobs = 4;

        private static readonly string WorkerName =
            $"{Environment.MachineName}:{Environment.ProcessId}";

        private readonly IHttpClientFactory _httpFactory;
        private readonly IApplicationConnection _appConnection;
        private readonly IOptionsSnapshot<Gb5SystemDTO> _databaseDTO;
        private readonly IQueryExecutor _queryExecutor;
        private readonly AutoNumber _autoNumber;
        private readonly IConfiguration _config;
        private readonly IHubContext<SysJobHub, ISysJobClient> _hubContext;
        private readonly IFileUploadBLL _fileUploadBLL;
        private readonly ISysJobNotificationDispatcher _notificationDispatcher;
        private readonly IUserDAL _userDAL;
        private readonly IWebhookEndpointResolver _webhookEndpointResolver;
        private readonly ILogger<SysJobExecutorQuartzJob> _logger;

        public SysJobExecutorQuartzJob(
            IHttpClientFactory httpFactory,
            IApplicationConnection appConnection,
            IOptionsSnapshot<Gb5SystemDTO> databaseDTO,
            IQueryExecutor queryExecutor,
            AutoNumber autoNumber,
            IConfiguration config,
            IHubContext<SysJobHub, ISysJobClient> hubContext,
            IFileUploadBLL fileUploadBLL,
            ISysJobNotificationDispatcher notificationDispatcher,
            IUserDAL userDAL,
            IWebhookEndpointResolver webhookEndpointResolver,
            ILogger<SysJobExecutorQuartzJob> logger)
        {
            _httpFactory    = httpFactory;
            _appConnection  = appConnection;
            _databaseDTO    = databaseDTO;
            _queryExecutor  = queryExecutor;
            _autoNumber     = autoNumber;
            _config         = config;
            _hubContext     = hubContext;
            _fileUploadBLL  = fileUploadBLL;
            _notificationDispatcher = notificationDispatcher;
            _userDAL        = userDAL;
            _webhookEndpointResolver = webhookEndpointResolver;
            _logger         = logger;
        }

        public async Task Execute(IJobExecutionContext context)
        {
            var ct = context.CancellationToken;
            try
            {
                var systemConn = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
                int dbType     = _databaseDTO.Value.DataBaseType;

                const string tenantSql = @"
                    SELECT
                        SERVERCONFIG1.CLIENTID        AS ClientId,
                        SERVERCONFIG1.DATABASENAME    AS DatabaseName,
                        SERVERCONFIG1.DATABASETYPE    AS DbType,
                        SERVERCONFIG1.CONNECTIONNAME  AS ConnectionName
                    FROM MSERVERCONFIG SERVERCONFIG1
                    JOIN MSERVER SERVER1 ON SERVERCONFIG1.SERVERID = SERVER1.SERVERID
                    WHERE SERVERCONFIG1.STATUS = 1
                      AND SERVERCONFIG1.CONNECTIONNAME <> 'ACTIVITI'";

                using IDbConnection conn = dbType switch
                {
                    DBTYPE.SQL        => new SqlConnection(systemConn),
                    DBTYPE.POSTGRESQL => new NpgsqlConnection(systemConn),
                    _                 => throw new NotSupportedException($"Unsupported DB type: {dbType}")
                };

                var tenants = (await SqlMapper.QueryAsync<ServerConfigDTO>(conn, tenantSql)
                    .ConfigureAwait(false)).ToList();

                foreach (var tenant in tenants)
                {
                    try
                    {
                        await _appConnection.DBConnectionStringCached(tenant.ConnectionName)
                            .ConfigureAwait(false);
                    }
                    catch (Exception connEx)
                    {
                        // Was previously a bare `catch { LogWarning(...) }` with no exception detail
                        // at all — every real failure reason (bad password decrypt, missing server
                        // config row, transient cache/Vault hiccup) was indistinguishable from any
                        // other. Logging the real exception is required to diagnose a specific
                        // tenant's connection failure instead of guessing.
                        _logger.LogWarning(connEx,
                            "SysJobExecutor: skipping tenant {ClientId} ({ConnectionName}) — connection not configured",
                            tenant.ClientId, tenant.ConnectionName);
                        continue;
                    }

                    var login = BuildLogin(tenant);
                    try
                    {
                        await ProcessTenantAsync(login, ct).ConfigureAwait(false);
                    }
                    catch (Microsoft.Data.SqlClient.SqlException sqlEx) when (sqlEx.Number == 208)
                    {
                        // 208 = Invalid object name — TSYSJOB table not installed for this tenant, skip silently
                        _logger.LogWarning(
                            "SysJobExecutorQuartzJob: skipping tenant {Id} — TSYSJOB table not found (schema not installed)",
                            tenant.ClientId);
                    }
                    catch (Exception ex) when (ex.Message.Contains("No server configuration found") ||
                                               ex.InnerException?.Message.Contains("No server configuration found") == true)
                    {
                        _logger.LogWarning(
                            "SysJobExecutorQuartzJob: skipping tenant {Id} ({Conn}) — connection not configured",
                            tenant.ClientId, tenant.ConnectionName);
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError(ex, "SysJobExecutorQuartzJob failed for tenant {Id}", tenant.ClientId);
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogCritical(ex, "SysJobExecutorQuartzJob outer loop failed");
            }
        }

        // ── Per-tenant processing ─────────────────────────────────────────────

        private async Task ProcessTenantAsync(LoginDTO login, System.Threading.CancellationToken ct)
        {
            var pending = (await _queryExecutor.QueryAsync<PendingSysJobDTO>(
                login,
                SystemJobQB.GET_PENDING_JOBS,
                new { BatchSize, SysJobTenantId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false)).ToList();

            if (pending.Count == 0)
                return;

            _logger.LogInformation("SysJobExecutor: {Count} pending jobs for tenant {Id}",
                pending.Count, login.ClientId);

            // Bounded concurrency, same shape as ActionProcessorWorker's own throttle — a plain
            // sequential foreach here meant one slow report (up to HttpTimeoutS=120s) blocked
            // every other pending job behind it, tenant-wide. GET_PENDING_JOBS already orders by
            // Priority DESC then SubmittedOn ASC, and SemaphoreSlim grants permits in that same
            // FIFO order, so higher-priority jobs still get a worker slot first — this only adds
            // throughput, it doesn't change which job goes first. Each job's own exception is
            // caught individually so one bad job can't fault the others via Task.WhenAll.
            using var throttle = new System.Threading.SemaphoreSlim(MaxParallelJobs, MaxParallelJobs);

            var jobTasks = pending.Select(async job =>
            {
                await throttle.WaitAsync(ct).ConfigureAwait(false);
                try
                {
                    ct.ThrowIfCancellationRequested();
                    await ExecuteJobAsync(job, login, ct).ConfigureAwait(false);
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "SysJobExecutor: unhandled error | SysJobId={Id}", job.SysJobId);
                }
                finally
                {
                    throttle.Release();
                }
            });

            await Task.WhenAll(jobTasks).ConfigureAwait(false);
        }

        private async Task ExecuteJobAsync(PendingSysJobDTO job, LoginDTO login,
            System.Threading.CancellationToken ct)
        {
            // ── 1. Atomically claim the job ──────────────────────────────────
            int claimed = await _queryExecutor.ExecuteAsync(
                login,
                SystemJobQB.CLAIM_JOB,
                new { job.SysJobId, SysJobTenantId = login.ClientId, SysJobModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);

            if (claimed == 0)
            {
                // Could be claimed by another worker or cancelled — check status to log appropriately
                _logger.LogInformation("SysJobExecutor: SysJobId={Id} already claimed or cancelled — skipping",
                    job.SysJobId);
                return;
            }

            // ── 2. Create TSYSJOBRUN entry ───────────────────────────────────
            var runAutoNumber = await _autoNumber.GetAutoNumber(1, "SYSJOBRUN", login).ConfigureAwait(false);
            var sysJobRunId   = runAutoNumber.StartNumber;
            var startedAt     = DateTime.UtcNow;

            var runDto = new SysJobRunDTO
            {
                SysJobRunId   = sysJobRunId,
                SysJobId      = job.SysJobId,
                StartedAt     = startedAt,
                EndedAt       = null,
                Status        = 1,                   // InProgress
                Worker        = WorkerName,
                RunIdentifier = sysJobRunId,
                CreatedById   = login.UserId,
                CreatedOn     = startedAt,
                ModifiedById  = login.UserId,
                ModifiedOn    = startedAt,
                TenantId      = login.ClientId
            };

            await _queryExecutor.ExecuteAsync(login, SysJobRunQB.SAVE_SYSJOBRUN, runDto,
                cancellationToken: ct).ConfigureAwait(false);

            _logger.LogInformation(
                "SysJobExecutor: started | SysJobId={Id} SysJobRunId={RunId} Worker={Worker}",
                job.SysJobId, sysJobRunId, WorkerName);

            await _hubContext.Clients
                .Group(SysJobHub.TenantGroup(login.ClientId))
                .JobStarted(job.SysJobId, sysJobRunId, WorkerName, startedAt)
                .ConfigureAwait(false);

            // ── 3. Execute the web service call ──────────────────────────────
            string? resultLocation = null;
            string? errorMessage   = null;
            bool    success        = false;

            try
            {
                (success, resultLocation, errorMessage) =
                    await CallWebServiceAsync(job, login, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
                _logger.LogError(ex, "SysJobExecutor: web service call threw | SysJobId={Id}", job.SysJobId);
            }

            var endedAt = DateTime.UtcNow;

            // ── 4. Update TSYSJOBRUN ─────────────────────────────────────────
            runDto.EndedAt     = endedAt;
            runDto.Status      = success ? 2 : 3;
            runDto.Log         = errorMessage ?? (success ? "Completed" : "Failed");
            runDto.ModifiedOn  = endedAt;

            await _queryExecutor.ExecuteAsync(login, SysJobRunQB.UPDATE_SYSJOBRUN, runDto,
                cancellationToken: ct).ConfigureAwait(false);

            // ── 5. Update TSYSJOB ────────────────────────────────────────────
            // COMPLETE_JOB and FAIL_OR_RETRY_JOB both include AND RUNSTATUS = 1 in their
            // WHERE clause.  If a cancel arrived while the HTTP call was in flight, RUNSTATUS
            // is already 4 and 0 rows are updated — we must NOT overwrite the cancel.
            if (success)
            {
                int updated = await _queryExecutor.ExecuteAsync(
                    login,
                    SystemJobQB.COMPLETE_JOB,
                    new { job.SysJobId, SysJobTenantId = login.ClientId, ResultLocation = resultLocation,
                          SysJobModifiedById = login.UserId },
                    cancellationToken: ct).ConfigureAwait(false);

                if (updated == 0)
                {
                    _logger.LogInformation(
                        "SysJobExecutor: job cancelled during execution | SysJobId={Id} SysJobRunId={RunId}",
                        job.SysJobId, sysJobRunId);
                    await _hubContext.Clients
                        .Group(SysJobHub.TenantGroup(login.ClientId))
                        .JobCancelled(job.SysJobId, endedAt)
                        .ConfigureAwait(false);
                    return;
                }

                _logger.LogInformation("SysJobExecutor: completed | SysJobId={Id} SysJobRunId={RunId}",
                    job.SysJobId, sysJobRunId);

                await _hubContext.Clients
                    .Group(SysJobHub.TenantGroup(login.ClientId))
                    .JobCompleted(job.SysJobId, sysJobRunId, resultLocation, endedAt)
                    .ConfigureAwait(false);

                if (job.DeliveryChannel.HasValue)
                    await _notificationDispatcher.DispatchAsync(
                        job.SysJobId, sysJobRunId, job.DeliveryChannel.Value, job.DeliveryDestination,
                        job.SubmittedById, runStatus: 2, login, ct).ConfigureAwait(false);
            }
            else
            {
                bool shouldRetry = job.RetryCount < job.MaxRetries;
                int  nextStatus  = shouldRetry ? 0 : 3;  // 0=retry-pending, 3=final-fail

                int updated = await _queryExecutor.ExecuteAsync(
                    login,
                    SystemJobQB.FAIL_OR_RETRY_JOB,
                    new { job.SysJobId, SysJobTenantId = login.ClientId,
                          RunStatus = nextStatus, ErrorMessage = errorMessage,
                          SysJobModifiedById = login.UserId },
                    cancellationToken: ct).ConfigureAwait(false);

                if (updated == 0)
                {
                    _logger.LogInformation(
                        "SysJobExecutor: job cancelled during execution (on failure path) | SysJobId={Id} SysJobRunId={RunId}",
                        job.SysJobId, sysJobRunId);
                    await _hubContext.Clients
                        .Group(SysJobHub.TenantGroup(login.ClientId))
                        .JobCancelled(job.SysJobId, endedAt)
                        .ConfigureAwait(false);
                    return;
                }

                _logger.LogWarning(
                    "SysJobExecutor: {Outcome} | SysJobId={Id} SysJobRunId={RunId} RetryCount={R}/{Max} Error={E}",
                    shouldRetry ? "retry-queued" : "permanently-failed",
                    job.SysJobId, sysJobRunId, job.RetryCount + 1, job.MaxRetries, errorMessage);

                if (shouldRetry)
                {
                    await _hubContext.Clients
                        .Group(SysJobHub.TenantGroup(login.ClientId))
                        .JobRetrying(job.SysJobId, sysJobRunId, job.RetryCount + 1, job.MaxRetries,
                            errorMessage ?? "Unknown error", endedAt)
                        .ConfigureAwait(false);
                }
                else
                {
                    await _hubContext.Clients
                        .Group(SysJobHub.TenantGroup(login.ClientId))
                        .JobFailed(job.SysJobId, sysJobRunId,
                            errorMessage ?? "Unknown error", endedAt)
                        .ConfigureAwait(false);

                    if (job.DeliveryChannel.HasValue)
                        await _notificationDispatcher.DispatchAsync(
                            job.SysJobId, sysJobRunId, job.DeliveryChannel.Value, job.DeliveryDestination,
                            job.SubmittedById, runStatus: 3, login, ct).ConfigureAwait(false);
                }
            }
        }

        // ── HTTP call ─────────────────────────────────────────────────────────

        private async Task<(bool Success, string? ResultLocation, string? Error)> CallWebServiceAsync(
            PendingSysJobDTO job, LoginDTO login, System.Threading.CancellationToken ct)
        {
            // Delegates to the same resolver ActionProcessor/webhooks already use — it prefers
            // MWEBSERVICE.SECONDURITEMPLATE over URITEMPLATE when set (several real reports are
            // wired this way: URITEMPLATE holds the legacy GB4 route, SECONDURITEMPLATE the
            // GB5-native replacement) and substitutes the literal {BaseURI} token every real
            // MWEBSERVICE row carries — this executor's own prior "StartsWith(\"http\")" check
            // never did that substitution, so any row using the standard {BaseURI} convention
            // would have produced a literally-invalid host.
            string? url = await _webhookEndpointResolver.ResolveAsync(
                job.WebServiceId, uriParameterValue: null, login.ClientId, login.ConnectionDatabaseName, ct)
                .ConfigureAwait(false);

            if (string.IsNullOrWhiteSpace(url))
                return (false, null, $"No MWEBSERVICE configured for WebServiceId={job.WebServiceId}");

            // MWEBSERVICE.METHODTYPE only means something for the legacy GB4 URITEMPLATE side —
            // it was never updated when SECONDURITEMPLATE (the GB5-native replacement) was added,
            // and every BaseReportEndpoint-derived report route in this codebase is POST (confirmed
            // across all ~28 of them), never GET. A real report's METHODTYPE=1 (GET), live-tested
            // against AccountProfitLossDashboardReport, produced a hard HTTP 405 for exactly this
            // reason. Once a usable SECONDURITEMPLATE exists, always POST — ignore METHODTYPE.
            bool isUsableTemplate(string? template) =>
                !string.IsNullOrWhiteSpace(template)
                && !string.Equals(template, "NONE", StringComparison.OrdinalIgnoreCase);

            var method = isUsableTemplate(job.WebServiceSecondUriTemplate)
                ? HttpMethod.Post
                : (job.WebServiceMethodType == 1 ? HttpMethod.Get : HttpMethod.Post);

            var http    = _httpFactory.CreateClient("sysjob");
            var request = new HttpRequestMessage(method, url);

            // GB5-native FastEndpoints targets (e.g. BaseReportEndpoint-derived report routes)
            // require a Login header to resolve LoginDTO server-side — without it every such
            // call fails with 400 "This header is missing from the request!". Legacy GB4 .svc
            // targets ignore unknown headers, so this is safe to send unconditionally.
            // Use the submitting user's own WorkOUId/DateFormat/etc — the bare tenant-level
            // `login` has none of these, so any report needing company/branch letterhead data
            // (e.g. PDF export "standard fields") fails; the job should render as if the
            // submitting user ran it themselves, not as a contextless system account.
            var targetLogin = await BuildTargetLoginAsync(job, login, ct).ConfigureAwait(false);
            request.Headers.Add("Login", JsonSerializer.Serialize(targetLogin));

            // BaseReportEndpoint (every GB5-native report route) picks its export format off a
            // ReportFormat header — without it a Job submitted for a PDF/Excel export just gets
            // the default JSON/paged response, not a file. job.ResultFormat uses the frontend's
            // own scheme (gbjobworker.component.ts RESULT_FORMAT_BY_OUTPUT), which does not match
            // GB5Shared's ReportFormat codes, so it must be translated, not forwarded as-is.
            int? reportFormatCode = MapResultFormatToReportFormatHeader(job.ResultFormat);
            if (reportFormatCode.HasValue)
                request.Headers.Add("ReportFormat", reportFormatCode.Value.ToString());
            else if (job.ResultFormat != 0)
                _logger.LogWarning(
                    "SysJobExecutor: SysJobId={Id} requested unsupported ResultFormat={Format} — " +
                    "no ReportFormat header sent, target will return its default JSON response",
                    job.SysJobId, job.ResultFormat);

            if (method == HttpMethod.Post && !string.IsNullOrWhiteSpace(job.Parameters))
                request.Content = new StringContent(job.Parameters, Encoding.UTF8, "application/json");

            using var cts = System.Threading.CancellationTokenSource.CreateLinkedTokenSource(ct);
            cts.CancelAfter(TimeSpan.FromSeconds(HttpTimeoutS));

            // ResponseHeadersRead: check Content-Type before reading the body so we
            // can pick the right read path (binary vs text) without double-buffering.
            using var response = await http.SendAsync(
                request, HttpCompletionOption.ResponseHeadersRead, cts.Token).ConfigureAwait(false);

            if (!response.IsSuccessStatusCode)
            {
                string errorBody = await response.Content.ReadAsStringAsync(cts.Token).ConfigureAwait(false);
                return (false, null, $"HTTP {(int)response.StatusCode}: {errorBody[..Math.Min(errorBody.Length, 2000)]}");
            }

            _logger.LogInformation(
                "SysJobExecutor: HTTP {Status} | SysJobId={Id} Url={Url}",
                (int)response.StatusCode, job.SysJobId, url);

            // ── File response — upload to DMS centrally ──────────────────────
            // The report service just returns the file (Content-Type: application/pdf,
            // Content-Disposition: attachment; filename="..."). No DMS knowledge needed
            // on the service side. The executor owns all file storage concerns.
            if (IsFileResponse(response))
            {
                byte[]  fileBytes = await response.Content.ReadAsByteArrayAsync(cts.Token).ConfigureAwait(false);
                string  mimeType  = response.Content.Headers.ContentType?.MediaType ?? "application/octet-stream";
                string  fileName  = ExtractFileName(response, job);

                string token = await SysJobAttachmentHelper.UploadAsync(
                    _fileUploadBLL, fileBytes, fileName, mimeType, job.SysJobId, login, ct)
                    .ConfigureAwait(false);

                _logger.LogInformation(
                    "SysJobExecutor: file stored in DMS | SysJobId={Id} FileName={Name} Size={Bytes}B Token={Token}",
                    job.SysJobId, fileName, fileBytes.Length, token);

                return (true, token, null);
            }

            // ── Text / JSON response ─────────────────────────────────────────
            // Fire-and-forget jobs return nothing useful. Services that still want to
            // return a pre-built token (att:/redis:/filesystem path) can do so as a
            // short plain-text body — kept for backward compatibility.
            string body = await response.Content.ReadAsStringAsync(cts.Token).ConfigureAwait(false);

            // BaseReportEndpoint's export path never sends a raw file response — it always
            // wraps the export bytes as a base64 string inside a ResponseStandardDTO<object>
            // envelope's Body field (Content-Type stays application/json, so IsFileResponse()
            // above can't see it). Unwrap it here when a downloadable format was requested.
            if (reportFormatCode.HasValue && IsDownloadableReportFormat(reportFormatCode.Value)
                && TryExtractBase64FileFromJsonEnvelope(body, out byte[]? decodedBytes))
            {
                string mimeType = MimeTypeForReportFormat(reportFormatCode.Value);
                string fileName = $"job-{job.SysJobId}{ExtensionForReportFormat(reportFormatCode.Value)}";

                string token = await SysJobAttachmentHelper.UploadAsync(
                    _fileUploadBLL, decodedBytes!, fileName, mimeType, job.SysJobId, login, ct)
                    .ConfigureAwait(false);

                _logger.LogInformation(
                    "SysJobExecutor: file stored in DMS (unwrapped from JSON envelope) | SysJobId={Id} FileName={Name} Size={Bytes}B Token={Token}",
                    job.SysJobId, fileName, decodedBytes!.Length, token);

                return (true, token, null);
            }

            var resultPath = body is { Length: > 0 and <= 500 } && !body.TrimStart().StartsWith('{')
                ? body.Trim()
                : null;
            return (true, resultPath, null);
        }

        // ── Target-user context ─────────────────────────────────────────────────

        // The executor's own `login` (from BuildLogin) is a bare tenant-level context with no
        // WorkOUId/DateFormat — enough for the executor's own TSYSJOB queries, not enough for a
        // report that needs the submitter's company/branch context. Reuses IUserDAL.GetUser,
        // the same lookup AuthenticationBLL uses to populate these fields at real login time.
        private async Task<LoginDTO> BuildTargetLoginAsync(
            PendingSysJobDTO job, LoginDTO systemLogin, System.Threading.CancellationToken ct)
        {
            // Real UserIds in this codebase are large negative sentinel-style numbers (e.g.
            // -1500000000) — only -1/0 mean "no real submitter". A "<= 0" guard here would
            // reject every real user and silently skip enrichment every time.
            if (job.SubmittedById == -1 || job.SubmittedById == 0)
                return systemLogin;

            try
            {
                string json = await _userDAL.GetUser(job.SubmittedById, systemLogin).ConfigureAwait(false);
                var user = Newtonsoft.Json.JsonConvert.DeserializeObject<UserDTO>(json);
                if (user is null)
                    return systemLogin;

                return new LoginDTO
                {
                    UserId                 = job.SubmittedById,
                    UserName               = user.UserName,
                    ClientId               = systemLogin.ClientId,
                    ConnectionDatabaseName = systemLogin.ConnectionDatabaseName,
                    DatabaseName           = systemLogin.DatabaseName,
                    DatabaseType           = systemLogin.DatabaseType,
                    RoleId                 = user.RoleId,
                    WorkOUId               = user.UserWorkOuId,
                    WorkPeriodId           = user.UserWorkPeriodId,
                    WorkPartyBranchId      = user.UserWorkPartyBranchId,
                    WorkStoreId            = user.UserWorkStoreId,
                    DateFormat             = user.UserDateFormat,
                    CurrencyFormat         = user.UserCurrencyFormat,
                    TimeFormat             = user.UserTimeFormat,
                    QuantityFormat         = user.UserQuantityFormat
                };
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex,
                    "SysJobExecutor: could not load submitter context for SysJobId={Id} SubmittedById={UserId} — " +
                    "falling back to bare tenant login (report may fail if it needs OU/branch context)",
                    job.SysJobId, job.SubmittedById);
                return systemLogin;
            }
        }

        // ── Format mapping ────────────────────────────────────────────────────

        // gbjobworker.component.ts RESULT_FORMAT_BY_OUTPUT: pdf=1, csv=2, html=3, excel=4.
        // GB5Shared.GB5Constant.Constant.ReportFormat: Excel=0, Csv=1, Pdf=2, Json=3,
        // ReportData=4, Html=5, NdJsonStream=6. The two schemes share no codes in common —
        // forwarding job.ResultFormat verbatim would request the wrong export format.
        private static int? MapResultFormatToReportFormatHeader(int resultFormat) => resultFormat switch
        {
            1 => ReportFormat.Pdf,
            2 => ReportFormat.Csv,
            3 => ReportFormat.Html,
            4 => ReportFormat.Excel,
            _ => null
        };

        private static bool IsDownloadableReportFormat(int reportFormatCode) =>
            reportFormatCode is ReportFormat.Pdf or ReportFormat.Excel or ReportFormat.Csv or ReportFormat.Html;

        // BaseReportEndpoint's export response is a ResponseStandardDTO<object> whose Body is
        // the export byte[] — System.Text.Json renders a byte[] property as a base64 string.
        private static bool TryExtractBase64FileFromJsonEnvelope(string body, out byte[]? fileBytes)
        {
            fileBytes = null;
            try
            {
                using var doc = JsonDocument.Parse(body);
                if (doc.RootElement.TryGetProperty("Body", out var bodyElem)
                    && bodyElem.ValueKind == JsonValueKind.String)
                {
                    fileBytes = Convert.FromBase64String(bodyElem.GetString()!);
                    return fileBytes.Length > 0;
                }
            }
            catch (Exception)
            {
                // Not a parseable JSON envelope, or Body isn't base64 — fall through to the
                // plain-text-token handling in the caller.
            }
            return false;
        }

        private static string MimeTypeForReportFormat(int reportFormatCode) => reportFormatCode switch
        {
            ReportFormat.Pdf   => "application/pdf",
            ReportFormat.Excel => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            ReportFormat.Csv   => "text/csv",
            ReportFormat.Html  => "text/html",
            _                  => "application/octet-stream"
        };

        private static string ExtensionForReportFormat(int reportFormatCode) => reportFormatCode switch
        {
            ReportFormat.Pdf   => ".pdf",
            ReportFormat.Excel => ".xlsx",
            ReportFormat.Csv   => ".csv",
            ReportFormat.Html  => ".html",
            _                  => ".bin"
        };

        // ── File-detection helpers ────────────────────────────────────────────

        private static bool IsFileResponse(HttpResponseMessage response)
        {
            // Explicit download header is the most reliable signal
            if (response.Content.Headers.ContentDisposition?.DispositionType
                    ?.Equals("attachment", StringComparison.OrdinalIgnoreCase) == true)
                return true;

            string? ct = response.Content.Headers.ContentType?.MediaType;
            if (ct is null) return false;

            return ct is "application/pdf"
                or "application/zip"
                or "application/octet-stream"
                or "text/csv"
                || ct.StartsWith("application/vnd.", StringComparison.OrdinalIgnoreCase);
        }

        private static string ExtractFileName(HttpResponseMessage response, PendingSysJobDTO job)
        {
            // 1. Content-Disposition: attachment; filename*=UTF-8''report.pdf  (preferred)
            //    or                   attachment; filename="report.pdf"
            var cd = response.Content.Headers.ContentDisposition;
            string? name = cd?.FileNameStar ?? cd?.FileName;
            if (!string.IsNullOrWhiteSpace(name))
                return name.Trim('"');

            // 2. Last segment of the request URL
            string? path = response.RequestMessage?.RequestUri?.AbsolutePath;
            if (!string.IsNullOrWhiteSpace(path))
            {
                string seg = Path.GetFileName(path);
                if (!string.IsNullOrWhiteSpace(seg) && seg.Contains('.'))
                    return seg;
            }

            // 3. Fallback: SysJobType + ID + extension derived from MIME type
            string ext = response.Content.Headers.ContentType?.MediaType switch
            {
                "application/pdf"   => ".pdf",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" => ".xlsx",
                "application/vnd.ms-excel" => ".xls",
                "text/csv"          => ".csv",
                "application/zip"   => ".zip",
                _                   => ".bin"
            };
            return $"{job.SysJobType}_{job.SysJobId}{ext}";
        }

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

        private static LoginDTO BuildLogin(ServerConfigDTO tenant) => new()
        {
            UserId                 = -1,
            ClientId               = tenant.ClientId,
            ConnectionDatabaseName = tenant.ConnectionName,
            DatabaseName           = tenant.DatabaseName,
            DatabaseType           = tenant.DbType
        };
    }

    /// <summary>
    /// Lightweight projection from GET_PENDING_JOBS — only fields needed for execution.
    /// </summary>
    internal sealed class PendingSysJobDTO
    {
        public int SysJobId { get; set; }
        public string SysJobType { get; set; } = string.Empty;
        public int WebServiceId { get; set; }
        public string? Parameters { get; set; }
        public int SubmittedById { get; set; }
        public int Priority { get; set; }
        public int RetryCount { get; set; }
        public int MaxRetries { get; set; }
        public string? QueueName { get; set; }
        public int SysJobTenantId { get; set; }
        public SysJobDeliveryChannel? DeliveryChannel { get; set; }
        public string? DeliveryDestination { get; set; }
        public int ResultFormat { get; set; }
        public string? WebServiceUriTemplate { get; set; }
        public string? WebServiceSecondUriTemplate { get; set; }
        public int WebServiceMethodType { get; set; }
    }
}