namespace FrameworkDAL.Query.SchedulerTaskGenerator
{
    /// <summary>
    /// ActionType values used in MACTION.ACTIONTYPE:
    ///   0  = Mail
    ///   1  = SMS
    ///   2  = Report Mail
    ///   3  = Notify (Push Notification)
    ///   4  = Doc Merge
    ///   5  = Web Service
    ///   6  = File Based
    ///   7  = WhatsApp
    ///   8  = Teams
    ///   9  = Telegram
    ///  10  = Slack
    /// </summary>
    public static class SchedulerTaskGeneratorQB
    {
        // =====================================================
        // Load READY jobs — only those DUE NOW and unpublished
        // =====================================================
        public const string LoadReadyJobs = @"
SELECT
    j.JOBID            AS JobId,
    j.JOBNAME          AS JobName,
    j.TENANTID         AS TenantId,
    j.WEBSERVICEID     AS WebServiceId,
    j.CRITERIACONFIGID AS CriteriaConfigId,
    j.STATUS           AS Status,
    j.MAXRETRIES       AS MaxRetry,
    j.TIMEOUTSECONDS   AS TimeoutSeconds,
    j.ISCONCURRENT     AS IsConcurrent,
    j.JOBCATEGORY      AS JobCategory,
    j.SOURCEMODULE     AS SourceModule,
    j.SOURCEOBJECTID   AS SourceObjectId,
    j.DAPRPUBSUBTOPIC  AS DaprPubSubTopic,

    s.SCHEDULERID      AS SchedulerId,
    s.CRONEXPRESSION   AS CronExpression,
    s.SCHEDULERNAME    AS SchedulerName,
    s.EVERYNOOFHOURS   AS EveryNoOfHours,
    s.EVERYNOOFMINUTES AS EveryNoOfMinutes,
    s.OCCURSATTIME     AS OccursAtTime,
    s.SCHEDULERTYPE    AS SchedulerType,
    s.INTERVALSECONDS  AS IntervalSeconds,
    s.SCHEDULEDDATE    AS ScheduledDate,
    s.DESCRIPTION      AS SchedulerDescription,

    e.JOBEXECUTIONID   AS JobExecutionId,
    e.LASTRUNON        AS LastRunOn,
    e.NEXTRUNON        AS NextRunOn,
    e.RETRYNUMBER      AS RetryCount
FROM MJOBDEFINE j
INNER JOIN TSCHEDULER s
    ON s.SCHEDULERID = j.SCHEDULERID
-- ✅ Most recent execution row per job, REGARDLESS of status. Previously this
-- restricted to STATUS IN (0,1), which meant LASTRUNON went permanently NULL
-- the moment a job's only row reached Success/Failed/DLQ — making the BLL
-- treat every already-run job as never-run on every subsequent poll.
OUTER APPLY (
    SELECT TOP 1
        JOBEXECUTIONID,
        LASTRUNON,
        NEXTRUNON,
        RETRYNUMBER,
        STATUS
    FROM TJOBEXECUTION
    WHERE JOBID = j.JOBID
    ORDER BY CREATEDON DESC
) e
WHERE j.STATUS   = 1               -- 1 = active job (CORRECTED, tracker 46 - GB5 standard entity status enum, 0 Pending 1 Active 2 Deleted 3 Amended 4 Inactive 5 Archived; live-verified against real fleet data, not the previously-inverted 0=active convention)
  AND s.STATUS   = 1               -- 1 = active scheduler
  AND j.TENANTID = @TenantId
  -- ✅ Skip jobs currently InProgress(2) unless the job explicitly allows
  -- concurrent execution. Real due-ness (has the schedule's interval/cron/
  -- fixed-time elapsed since LASTRUNON) is computed in the BLL — it depends
  -- on cron/interval logic that can't be expressed in SQL, and NEXTRUNON is
  -- not a reliable stored value for this (never correctly maintained across
  -- IST/UTC boundaries here).
  AND (e.STATUS IS NULL OR e.STATUS <> 'PROCESSING' OR j.ISCONCURRENT = 1)
";

        // =====================================================
        // Load a SINGLE job (by JobId) regardless of active/paused status —
        // used by QuartzSyncService to (re)build or tear down that job's
        // native Quartz trigger. Same shape as LoadReadyJobs but keyed by
        // JobId instead of "which jobs are due now".
        // =====================================================
        public const string LoadJobForSync = @"
SELECT
    j.JOBID            AS JobId,
    j.JOBNAME          AS JobName,
    j.TENANTID         AS TenantId,
    j.WEBSERVICEID     AS WebServiceId,
    j.CRITERIACONFIGID AS CriteriaConfigId,
    j.STATUS           AS Status,
    j.MAXRETRIES       AS MaxRetry,
    j.TIMEOUTSECONDS   AS TimeoutSeconds,
    j.ISCONCURRENT     AS IsConcurrent,
    j.JOBCATEGORY      AS JobCategory,
    j.SOURCEMODULE     AS SourceModule,
    j.SOURCEOBJECTID   AS SourceObjectId,
    j.DAPRPUBSUBTOPIC  AS DaprPubSubTopic,

    s.SCHEDULERID      AS SchedulerId,
    s.CRONEXPRESSION   AS CronExpression,
    s.SCHEDULERNAME    AS SchedulerName,
    s.EVERYNOOFHOURS   AS EveryNoOfHours,
    s.EVERYNOOFMINUTES AS EveryNoOfMinutes,
    s.OCCURSATTIME     AS OccursAtTime,
    s.SCHEDULERTYPE    AS SchedulerType,
    s.INTERVALSECONDS  AS IntervalSeconds,
    s.SCHEDULEDDATE    AS ScheduledDate,
    s.DESCRIPTION      AS SchedulerDescription,
    s.STATUS           AS SchedulerStatus,

    e.JOBEXECUTIONID   AS JobExecutionId,
    e.LASTRUNON        AS LastRunOn,
    e.NEXTRUNON        AS NextRunOn,
    e.RETRYNUMBER      AS RetryCount
FROM MJOBDEFINE j
INNER JOIN TSCHEDULER s
    ON s.SCHEDULERID = j.SCHEDULERID
OUTER APPLY (
    SELECT TOP 1
        JOBEXECUTIONID,
        LASTRUNON,
        NEXTRUNON,
        RETRYNUMBER,
        STATUS
    FROM TJOBEXECUTION
    WHERE JOBID = j.JOBID
    ORDER BY CREATEDON DESC
) e
WHERE j.JOBID    = @JobId
  AND j.TENANTID = @TenantId
";

        // =====================================================
        // JobIds pointing at a given SchedulerId — used when a TSCHEDULER
        // row is edited (e.g. cron changed) so every MJOBDEFINE job using
        // it gets its Quartz trigger resynced too, even though MJOBDEFINE
        // itself didn't change.
        // =====================================================
        public const string GetJobIdsBySchedulerId = @"
SELECT JOBID
FROM MJOBDEFINE
WHERE SCHEDULERID = @SchedulerId
  AND TENANTID     = @TenantId
";

        // =====================================================
        // Pause/Resume a job — toggles MJOBDEFINE.STATUS.
        // Used by JobDefineOperationsBLL; QuartzSyncService/RemoveJobAsync
        // is called separately by the BLL after this succeeds.
        // =====================================================
        public const string SetJobStatus = @"
UPDATE MJOBDEFINE
SET STATUS = @Status,
    MODIFIEDBYID = @UserId,
    MODIFIEDON = GETUTCDATE()
WHERE JOBID    = @JobId
  AND TENANTID = @TenantId
";

        // =====================================================
        // Existence check — does this job have an active MACTION row linked via the
        // scheduler's fixed EVENTTYPEID (SchedulerActionEventConstants.SchedulerJobEventTypeId)?
        // Used by SchedulerExecutionDeliveryService to decide whether to enqueue a
        // SCHEDULER_ACTION_EVENT to TJOBQUEUE at all after a successful job completion —
        // avoids a queue-insert+Dapr-publish round trip for jobs with no configured action.
        // =====================================================
        public const string HasActiveSchedulerAction = @"
SELECT TOP 1 1
FROM MACTION
WHERE EVENTTYPEID = @EventTypeId
  AND JOBID       = @JobId
  AND STATUS      = 1
  AND TENANTID    = @TenantId
";

        // =====================================================
        // Actions — load by job IDs
        // Required index: MACTION (JOBID, STATUS, TENANTID)
        //
        // ActionType values:
        //   0=Mail, 1=SMS, 2=ReportMail, 3=Notify, 4=DocMerge,
        //   5=WebService, 6=FileBased, 7=WhatsApp, 8=Teams,
        //   9=Telegram, 10=Slack
        // =====================================================
        public const string LoadActionsByJobIds = @"
SELECT
    a.ACTIONID          AS ActionId,
    a.JOBID             AS JobId,
    a.ACTIONTYPE        AS ActionType,
    a.SENDTO            AS SendTo,
    a.REPLYTO           AS ReplyTo,
    a.TEMPLATEID        AS TemplateId,
    a.TODELIVERYTYPE    AS ToDeliveryType,
    a.TOCONTENTTYPE     AS ToContentType,
    a.MAILCC            AS MailCc,
    a.MAILBCC           AS MailBcc,
    a.WEBSERVICEID      AS WebServiceId,
    a.URIPARAMETERVALUE AS UriParameterValue,
    a.REPORTID          AS ReportId,
    a.CRITERIACONFIGID  AS CriteriaConfigId,
    a.TENANTID          AS TenantId,
    a.STATUS            AS Status
FROM MACTION a
WHERE a.STATUS   = 1
  AND a.TENANTID = @TenantId
  AND a.JOBID   IN @JobIds
";

        // =====================================================
        // Insert new execution record — STATUS=Pending(0), due immediately.
        // Called by SchedulerJobExecutorQuartzJob when Quartz fires — this
        // no longer marks the row InProgress directly (that happens when
        // SchedulerExecutionPoller actually claims it, see ClaimDueExecutions
        // below). Delivery is now driven straight off TJOBEXECUTION — there
        // is no TJOBQUEUE hop for scheduled jobs any more.
        // ✅ NEXTRUNON is the BLL-computed, informational "job's next scheduled
        //    run" estimate (unrelated to delivery retries — shown on the
        //    monitor UI). NEXTATTEMPTON is the functional field the poller
        //    actually reads: when this row is next eligible to be claimed.
        //    Set to @RunOn (now) here so a freshly-fired job is claimed on
        //    the very next poll tick.
        // ✅ Concurrency guard now blocks on STATUS IN (0,2) — not just 2 —
        //    so a job with ISCONCURRENT=0 can't get a second overlapping
        //    execution created while an earlier one is still waiting out a
        //    retry backoff (STATUS=0) rather than actively InProgress (2).
        // =====================================================
        public const string CreatePendingExecution = @"
INSERT INTO TJOBEXECUTION
(
    JOBID,
    FIRERUNON,
    LASTRUNON,
    NEXTRUNON,
    NEXTATTEMPTON,
    STATUS,
    RETRYNUMBER,
    TRIGGERTYPE,
    CORRELATIONID,
    HOSTINSTANCE,
    TRACEPARENT,
    TRACESTATE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    VERSION,
    SOURCETYPE,
    TENANTID
)
OUTPUT INSERTED.JOBEXECUTIONID
SELECT
    M.JOBID,
    @RunOn,                     -- FIRERUNON     = when Quartz fired
    @RunOn,                     -- LASTRUNON     = same as fire time on insert
    @NowIst,                    -- NEXTRUNON     = informational next-scheduled-run estimate (IST)
    @RunOn,                     -- NEXTATTEMPTON = due immediately — poller claims on next tick
    'PENDING',                  -- STATUS
    0,                          -- RETRYNUMBER
    @TriggerType,               -- SCHEDULED | MANUAL | API
    @CorrelationId,              -- Generated app-side (SchedulerJobExecutorQuartzJob) BEFORE
                                  -- this insert, so the jobscheduler root span can be named
                                  -- after it and both TJOBEXECUTION and TJOBQUEUE end up with
                                  -- the exact same value — carried through SignalR/logs too
    @HostInstance,               -- Pod/machine name — cluster attribution
    @TraceParent,                -- W3C traceparent of the JobScheduler root span, so the
                                  -- poller can reconstruct it later as a child span
    @TraceState,
    @UserId,
    GETUTCDATE(),
    @UserId,
    GETUTCDATE(),
    1,
    1,
    M.SOURCETYPE,
    M.TENANTID
FROM MJOBDEFINE M
WHERE M.JOBID    = @JobId
  AND M.TENANTID = @TenantId   -- Tenant guard: prevents cross-tenant INSERT
  AND (
      M.ISCONCURRENT = 1
      OR NOT EXISTS (
          SELECT 1
          FROM TJOBEXECUTION
          WHERE JOBID  = M.JOBID
            AND STATUS IN ('PENDING', 'PROCESSING')    -- a prior attempt is still Pending or InProgress
      )
  )
";

        // =====================================================
        // Atomically claim due TJOBEXECUTION rows for delivery — the
        // TJOBEXECUTION equivalent of TJOBQUEUE's claim query in
        // GB5Shared/QueueReader/QueueReader.cs. Flips STATUS 0->2 and
        // stamps STARTRUNON (the actual per-attempt timing baseline used
        // by UpdateExecutionResult's DURATIONMS calc and by the timeout
        // reaper), and returns everything SchedulerExecutionDeliveryService
        // needs to make the call in one round trip — no separate
        // LoadJobDetailsAsync call needed.
        // =====================================================
        public const string ClaimDueExecutions = @"
UPDATE TOP(@BatchSize) e
SET
    e.STATUS     = 'PROCESSING',
    e.STARTRUNON = GETUTCDATE()
OUTPUT
    INSERTED.JOBEXECUTIONID   AS JobExecutionId,
    j.JOBID                   AS JobId,
    j.JOBNAME                 AS JobName,
    j.TENANTID                AS TenantId,
    j.MAXRETRIES              AS MaxRetry,
    j.URIPARAMETERVALUE       AS UriParameterValue,
    j.WEBSERVICEID            AS WebServiceId,
    j.CRITERIACONFIGID        AS CriteriaConfigId,
    ws.URITEMPLATE            AS EndpointUrl,
    ws.METHODTYPE             AS HttpMethod,
    INSERTED.CORRELATIONID    AS CorrelationId,
    INSERTED.TRACEPARENT      AS TraceParent,
    INSERTED.TRACESTATE       AS TraceState,
    j.RUNASUSEID              AS RunAsUserId,
    j.RUNASROLEID             AS RunAsRoleId,
    j.RUNASOUID               AS RunAsOUId,
    j.RUNASBRANCHID           AS RunAsBranchId,
    j.RUNASWORKPARTYID        AS RunAsWorkPartyId,
    j.RUNASSTOREID            AS RunAsStoreId,
    j.RUNASPERIODID           AS RunAsPeriodId,
    u.USERNAME                AS RunAsUserName
FROM TJOBEXECUTION e WITH (ROWLOCK, READPAST)
INNER JOIN MJOBDEFINE j ON j.JOBID = e.JOBID
LEFT JOIN MWEBSERVICE ws ON ws.WEBSERVICEID = j.WEBSERVICEID
LEFT JOIN MUSER u ON u.USERID = j.RUNASUSEID
WHERE e.STATUS       = 'PENDING'
  AND e.TENANTID     = @TenantId
  AND (e.NEXTATTEMPTON IS NULL OR e.NEXTATTEMPTON <= GETUTCDATE())
  AND (
      j.RUNASUSEID IS NULL
      OR EXISTS (
          SELECT 1 FROM MUSER mu WHERE mu.USERID = j.RUNASUSEID AND mu.STATUS = 1
      )
  )
";

        // =====================================================
        // Bulk-deactivate all active jobs for a user who has
        // been deactivated in MUSER — called by AutoSchedulerBLL
        // (or a UserBLL hook) when MUSER.STATUS goes inactive.
        // CORRECTED (tracker §46) — MJOBDEFINE uses GB5's standard entity status enum
        // (1=Active, 4=Inactive), not the inverted "0=active,1=inactive" this file
        // previously claimed; live-verified against real fleet data. Returns rows-affected count.
        // =====================================================
        public const string DeactivateJobsByUserId = @"
UPDATE MJOBDEFINE
SET STATUS       = 4,
    MODIFIEDON   = GETUTCDATE(),
    MODIFIEDBYID = @RequestingUserId
WHERE RUNASUSEID = @UserId
  AND TENANTID   = @TenantId
  AND STATUS     = 1
";

        // =====================================================
        // Load flat criteria rows for one TCRITERIACONFIG config —
        // used by SchedulerExecutionDeliveryService to build the
        // POST body (ReportCallingDTO) for report endpoints.
        // Ordered so grouping by section is stable.
        // Required index: TCRITERIACONFIGSECTION (CRITERIACONFIGID)
        //                 TCRITERIACONFIGATTRIBUTE (CRITERIASECTIONID)
        // =====================================================
        public const string GetCriteriaAttributesForExecution = @"
SELECT
    CCS.CRITERIACONFIGSECTIONID        AS SectionId,
    CCS.SLNO                           AS SectionSlNo,
    CCS.SECTIONJOIN                    AS SectionJoin,
    CCA.CRITERIAATTRIBUTEID            AS AttributeId,
    CA.CRITERIAATTRIBUTENAME           AS FieldName,
    CA.CRITERIAATTRIBUTETYPE           AS AttributeType,
    CA.FILTERTYPE                      AS FilterType,
    CCA.OPERATIONTYPE                  AS OperationType,
    CCA.CRITERIAFIELDVALUE             AS FieldValue,
    CCA.CRITERIAFIELDVALUEIN           AS FieldValueIn,
    CCA.CRITERIAJOIN                   AS JoinType,
    CCA.CRITERIAFIELDDISPLAYVALUE      AS FieldDisplayValue,
    CCA.CRITERIAVARIABLEFIELD          AS VariableField,
    CC.PERIODFILTER                    AS PeriodFilter,
    CC.CRITERIAMENUID                  AS MenuId,
    CC.REPORTFORMATID                  AS ReportFormatId,
    CC.DEFAULTREPORTVIEWID             AS ReportViewId
FROM TCRITERIACONFIG CC
INNER JOIN TCRITERIACONFIGSECTION CCS   ON CCS.CRITERIACONFIGID  = CC.CRITERIACONFIGID
INNER JOIN TCRITERIACONFIGATTRIBUTE CCA ON CCA.CRITERIASECTIONID = CCS.CRITERIACONFIGSECTIONID
LEFT  JOIN MCRITERIAATTRIBUTE CA        ON CA.CRITERIAATTRIBUTEID = CCA.CRITERIAATTRIBUTEID
WHERE CC.CRITERIACONFIGID = @CriteriaConfigId
ORDER BY CCS.SLNO, CCA.SLNO
";

        // =====================================================
        // Save the exact outbound request snapshot — called right before the HTTP
        // call, independent of success/failure (this is the request, not the result).
        // =====================================================
        public const string SaveRequestPayload = @"
UPDATE TJOBEXECUTION
SET REQUESTPAYLOAD = @Payload
WHERE JOBEXECUTIONID = @JobExecutionId
  AND TENANTID       = @TenantId
";

        // =====================================================
        // Update Result — COMPLETED or FAILED
        // Called by consumer after job execution completes.
        // =====================================================
        // ✅ Routes @Message to the correct column based on @Status — previously this
        // always wrote into ERRORMESSAGE regardless of outcome, so SUCCESSMESSAGE was
        // never populated by any code path, even for genuinely successful executions.
        // ✅ DURATIONMS is measured from STARTRUNON (stamped at claim time by
        // ClaimDueExecutions), not LASTRUNON (the original Quartz fire time) — a
        // failed attempt's retry can sit at STATUS='PENDING' for its whole backoff
        // window before being reclaimed, and that wait time is not part of "how long
        // did this attempt take".
        public const string UpdateExecutionResult = @"
UPDATE TJOBEXECUTION
SET
    ENDRUNON       = @EndRunOn,
    STATUS         = @Status,     -- 'COMPLETED' or 'FAILED'
    SUCCESSMESSAGE = CASE WHEN @Status = 'COMPLETED' THEN @Message ELSE NULL END,
    ERRORMESSAGE   = CASE WHEN @Status = 'FAILED' THEN @Message ELSE NULL END,
    DURATIONMS     = DATEDIFF(MILLISECOND, STARTRUNON, @EndRunOn)
WHERE JOBEXECUTIONID = @JobExecutionId
  AND TENANTID       = @TenantId
";

        // =====================================================
        // Retry state lookup — RETRYNUMBER (current) + MAXRETRIES
        // (from owning MJOBDEFINE). Read before deciding retry vs DLQ.
        // =====================================================
        public const string GetRetryState = @"
SELECT
    e.RETRYNUMBER AS RetryNumber,
    j.MAXRETRIES  AS MaxRetries,
    e.JOBID       AS JobId
FROM TJOBEXECUTION e
INNER JOIN MJOBDEFINE j ON j.JOBID = e.JOBID
WHERE e.JOBEXECUTIONID = @JobExecutionId
  AND e.TENANTID        = @TenantId
";

        // =====================================================
        // Apply retry-or-DLQ decision after a FAILED execution.
        // STATUS='PENDING' (requeued after backoff) or 'DLQ' (exhausted).
        // NEXTATTEMPTON — not NEXTRUNON — is the field SchedulerExecutionPoller's
        // ClaimDueExecutions actually reads, so it's the one touched on the
        // requeue path. NEXTRUNON stays the informational "next scheduled run"
        // estimate shown on the monitor UI, untouched by retries. DLQ rows keep
        // their failed NEXTATTEMPTON so they never get re-picked by the poller.
        // =====================================================
        public const string ApplyRetryDecision = @"
UPDATE TJOBEXECUTION
SET
    RETRYNUMBER   = @NewRetryNumber,
    STATUS        = @NewStatus,           -- 'PENDING' (retry) or 'DLQ'
    NEXTATTEMPTON = CASE WHEN @NewStatus = 'PENDING' THEN @NextAttemptOn ELSE NEXTATTEMPTON END
WHERE JOBEXECUTIONID = @JobExecutionId
  AND TENANTID        = @TenantId
";

        // =====================================================
        // Timeout reaper — finds InProgress(2) executions whose
        // STARTRUNON exceeds the owning job's TIMEOUTSECONDS, marks
        // them FAILED, and returns the affected JobExecutionIds so
        // the caller can apply the same retry-or-DLQ decision.
        // =====================================================
        public const string ReapTimedOutExecutions = @"
UPDATE e
SET
    e.STATUS       = 'FAILED',   -- timeout
    e.ENDRUNON     = GETUTCDATE(),
    e.ERRORMESSAGE = CONCAT('Execution timed out after ', j.TIMEOUTSECONDS, ' second(s)'),
    e.DURATIONMS   = DATEDIFF(MILLISECOND, e.STARTRUNON, GETUTCDATE())
OUTPUT INSERTED.JOBEXECUTIONID
FROM TJOBEXECUTION e
INNER JOIN MJOBDEFINE j ON j.JOBID = e.JOBID
WHERE e.STATUS       = 'PROCESSING'
  AND e.STARTRUNON  IS NOT NULL
  AND DATEDIFF(SECOND, e.STARTRUNON, GETUTCDATE()) > j.TIMEOUTSECONDS
  AND e.TENANTID     = @TenantId
";

        // =====================================================
        // Load SINGLE Job with full details (by JobExecutionId)
        // Used by consumer after picking up from queue.
        //
        // Note: j.STATUS aliased as Status, e.STATUS aliased as
        //       ExecutionStatus — prevents Dapper column collision.
        // =====================================================
        public const string LoadJobDetails = @"
SELECT
    -- Job Master
    j.JOBID               AS JobId,
    j.JOBNAME             AS JobName,
    j.TENANTID            AS TenantId,
    j.STATUS              AS Status,
    j.REMARKS             AS Remarks,
    j.MAXRETRIES          AS MaxRetry,
    j.TIMEOUTSECONDS      AS TimeoutSeconds,
    j.ISCONCURRENT        AS IsConcurrent,
    j.JOBCATEGORY         AS JobCategory,
    j.SOURCEMODULE        AS SourceModule,
    j.SOURCEOBJECTID      AS SourceObjectId,
    j.DAPRPUBSUBTOPIC     AS DaprPubSubTopic,

    -- Scheduler
    s.SCHEDULERID         AS SchedulerId,
    s.SCHEDULERNAME       AS SchedulerName,
    s.CRONEXPRESSION      AS CronExpression,
    s.SELECTEDMONTH       AS SelectedMonth,
    s.SELECTEDDAYS        AS SelectedDays,
    s.DATETYPE            AS DateType,
    s.SELECTEDDATES       AS SelectedDates,
    s.OCCURENCENO         AS OccurenceNo,
    s.OCCURENCEDAY        AS OccurenceDay,
    s.EVERYNOOFMONTHS     AS EveryNoOfMonths,
    s.EVERYNOOFDAYS       AS EveryNoOfDays,
    s.EVERYNOOFHOURS      AS EveryNoOfHours,
    s.EVERYNOOFMINUTES    AS EveryNoOfMinutes,
    s.OCCURSATTIME        AS OccursAtTime,
    s.OCCURSATMULTIPLETIME AS OccursAtMultipleTime,
    s.SCHEDULERTYPE       AS SchedulerType,
    s.INTERVALSECONDS     AS IntervalSeconds,
    s.SCHEDULEDDATE       AS ScheduledDate,
    s.DESCRIPTION         AS SchedulerDescription,

    -- Web Service
    j.WEBSERVICEID        AS WebServiceId,
    ws.WEBSERVICENAME     AS WebServiceName,
    ws.URITEMPLATE        AS EndpointUrl,
    ws.METHODTYPE         AS HttpMethod,
    j.URIPARAMETERVALUE   AS UriParameterValue,

    j.CRITERIACONFIGID    AS CriteriaConfigId,

    -- Execution
    e.JOBEXECUTIONID      AS JobExecutionId,
    e.LASTRUNON           AS LastRunOn,
    e.NEXTRUNON           AS NextRunOn,
    e.STATUS              AS ExecutionStatus,
    e.RETRYNUMBER         AS RetryCount,
    e.ERRORMESSAGE        AS FailureReason,
    e.SUCCESSMESSAGE      AS ExecutionResponse,
    e.TRIGGERTYPE         AS TriggerType,
    e.CORRELATIONID       AS CorrelationId,
    e.HOSTINSTANCE        AS HostInstance

FROM TJOBEXECUTION e
INNER JOIN MJOBDEFINE j
    ON j.JOBID = e.JOBID
INNER JOIN TSCHEDULER s
    ON s.SCHEDULERID = j.SCHEDULERID
LEFT JOIN MWEBSERVICE ws
    ON ws.WEBSERVICEID = j.WEBSERVICEID

WHERE e.JOBEXECUTIONID = @JobExecutionId
  AND j.TENANTID       = @TenantId
";

        // =====================================================
        // Insert next PENDING execution row after job completes
        // Called by BLL after SUCCESS to seed the next run.
        // STATUS=PENDING — picked up next poll.
        // =====================================================
        public const string InsertNextPendingExecution = @"
INSERT INTO TJOBEXECUTION
(
    JOBID,
    NEXTRUNON,
    STATUS,
    RETRYNUMBER,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    VERSION,
    SOURCETYPE,
    TENANTID
)
SELECT
    M.JOBID,
    @NextRunOn,                 -- ✅ Computed next run time from BLL (IST)
    'PENDING',                  -- STATUS — ready for next poll
    0,                          -- RETRYNUMBER
    @UserId,
    GETUTCDATE(),
    @UserId,
    GETUTCDATE(),
    1,
    1,
    M.SOURCETYPE,
    M.TENANTID
FROM MJOBDEFINE M
WHERE M.JOBID    = @JobId
  AND M.TENANTID = @TenantId
  -- ✅ Idempotency: only insert if no pending row already exists for this next run time
  AND NOT EXISTS (
      SELECT 1
      FROM TJOBEXECUTION
      WHERE JOBID     = M.JOBID
        AND STATUS    = 'PENDING'
        AND NEXTRUNON = @NextRunOn
  )
";
            // =====================================================
            // Scheduler Monitor Query
            // Returns all active jobs with their latest execution
            // status, next run time, action type list, retry count,
            // duration, and last error.
            //
            // Used by: GET /Scheduler/Monitor
            // Ordered: Running → Failed → DLQ → Pending → Success
            // =====================================================
            public const string LoadSchedulerMonitor = @"
SELECT
    -- Job identity
    j.JOBID              AS JobId,
    j.JOBNAME            AS JobName,
    j.TENANTID           AS TenantId,
    j.STATUS             AS JobStatus,

    -- Scheduler config
    s.SCHEDULERNAME      AS SchedulerName,
    s.CRONEXPRESSION     AS CronExpression,
    s.OCCURSATTIME       AS OccursAtTime,
    s.EVERYNOOFHOURS     AS EveryNoOfHours,
    s.EVERYNOOFMINUTES   AS EveryNoOfMinutes,

    -- Latest execution row (null if no execution yet)
    e.JOBEXECUTIONID     AS JobExecutionId,
    e.STATUS             AS ExecutionStatus,
    e.NEXTRUNON          AS NextRunOn,
    e.LASTRUNON          AS LastRunOn,
    e.FIRERUNON          AS FireRunOn,
    e.ENDRUNON           AS EndRunOn,
    e.DURATIONMS         AS DurationMs,
    e.RETRYNUMBER        AS RetryCount,
    e.ERRORMESSAGE       AS LastError,
    e.SUCCESSMESSAGE     AS LastSuccess,

    -- Aggregated action types collapsed into one row per job
    act.ActionTypes      AS ActionTypes,
    act.ActionCount      AS ActionCount

FROM MJOBDEFINE j
INNER JOIN TSCHEDULER s
    ON s.SCHEDULERID = j.SCHEDULERID

-- Latest execution row per job regardless of status
OUTER APPLY (
    SELECT TOP 1
        JOBEXECUTIONID,
        STATUS,
        NEXTRUNON,
        LASTRUNON,
        FIRERUNON,
        ENDRUNON,
        DURATIONMS,
        RETRYNUMBER,
        ERRORMESSAGE,
        SUCCESSMESSAGE
    FROM TJOBEXECUTION
    WHERE JOBID = j.JOBID
    ORDER BY CREATEDON DESC
) e

-- All active actions for this job collapsed into one row.
-- ✅ STRING_AGG without WITHIN GROUP — compatible with SQL Server 2017+
--    WITHIN GROUP (ORDER BY) requires SQL Server 2022+ and caused Msg 102.
--    Action type order is not significant for display purposes.
OUTER APPLY (
    SELECT
        COUNT(*)                                        AS ActionCount,
        STRING_AGG(CAST(a.ACTIONTYPE AS VARCHAR(10)), ',') AS ActionTypes
    FROM MACTION a
    WHERE a.JOBID    = j.JOBID
      AND a.STATUS   = 1
      AND a.TENANTID = -1
) act

WHERE j.TENANTID = -1
  AND j.STATUS   = 1               -- 1 = active (CORRECTED, tracker §46 — see LoadReadyJobs's own comment above)
  AND s.STATUS   = 1

ORDER BY
    CASE e.STATUS
        WHEN 'PROCESSING' THEN 0   -- Running first
        WHEN 'FAILED'     THEN 1
        WHEN 'DLQ'        THEN 2
        WHEN 'PENDING'    THEN 3
        WHEN 'COMPLETED'  THEN 4   -- Success last
        ELSE 5                     -- Not started
    END,
    e.NEXTRUNON ASC

";
        }
    }