using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.SystemJob
{
    public class SystemJobQB
    {

        public const string GET_SYSTEMJOB = @"SELECT
                                            S.SYSJOBID AS SysJobId,
                                            S.SYSJOBTYPE AS SysJobType,
                                            S.MENUID AS MenuId,
                                            S.WEBSERVICEID AS WebServiceId,
                                            S.PARAMETERS AS Parameters,
                                            S.SUBMITTEDBYID AS SubmittedById,
                                            S.SUBMITTEDON AS SubmittedOn,
                                            S.RUNSTATUS AS RunStatus,
                                            S.PRIORITY AS Priority,
                                            S.RETRYCOUNT AS RetryCount,
                                            S.MAXRETRIES AS MaxRetries,
                                            S.QUEUENAME AS QueueName,
                                            S.LASTUPDATED AS LastUpdate,
                                            S.PINNED AS Pinned,
                                            S.PUBLISH AS Publish,
                                            S.RESULTLOCATION AS ResultLocation,
                                            S.RESULTFORMAT AS ResultFormat,
                                            S.STARTTIME AS StartTime,
                                            S.ENDTIME AS EndTime,
                                            S.EXPIRYAT AS ExpiryAt,
                                            S.ERRORMESSAGE AS ErrorMessage,
                                            S.CREATEDBYID AS SysJobCreatedById,
                                            S.CREATEDON AS SysJobCreatedOn,
                                            S.MODIFIEDBYID AS SysJobModifiedById,
                                            S.MODIFIEDON AS SysJobModifiedOn,
                                            S.SORTORDER AS SysJobSortOrder,
                                            S.STATUS AS SysJobStatus,
                                            S.VERSION AS SysJobVersion,
                                            S.SOURCETYPE AS SysJobSourceType,
                                            S.TENANTID AS SysJobTenantId,
                                            S.DELIVERYCHANNEL AS DeliveryChannel,
                                            S.DELIVERYDESTINATION AS DeliveryDestination
                                            FROM TSYSJOB S
                                            WHERE S.SYSJOBID = @SysJobId;";


        public const string GET_SELECTLIST_SYSTEMJOB = @"
                            SELECT
                                S.SYSJOBID AS SysJobId,
                                S.SYSJOBTYPE AS SysJobType,
                                S.MENUID AS MenuId,
                                S.RUNSTATUS AS RunStatus,
                                S.PRIORITY AS Priority,
                                S.QUEUENAME AS QueueName
                            FROM TSYSJOB S
                            WHERE S.STATUS = 1
                            ORDER BY S.SORTORDER;";

        public const string SAVE_SYSTEMJOB = @"INSERT INTO TSYSJOB (
                                                    SYSJOBTYPE,
                                                    MENUID,
                                                    WEBSERVICEID,
                                                    PARAMETERS,
                                                    SUBMITTEDBYID,
                                                    SUBMITTEDON,
                                                    RUNSTATUS,
                                                    PRIORITY,
                                                    RETRYCOUNT,
                                                    MAXRETRIES,
                                                    QUEUENAME,
                                                    LASTUPDATED,
                                                    PINNED,
                                                    PUBLISH,
                                                    RESULTLOCATION,
                                                    RESULTFORMAT,
                                                    STARTTIME,
                                                    ENDTIME,
                                                    EXPIRYAT,
                                                    ERRORMESSAGE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    TENANTID,
                                                    DELIVERYCHANNEL,
                                                    DELIVERYDESTINATION )
                                               VALUES (
                                                    @SysJobType,
                                                    @MenuId,
                                                    @WebServiceId,
                                                    @Parameters,
                                                    @SubmittedById,
                                                    @SubmittedOn,
                                                    @RunStatus,
                                                    @Priority,
                                                    @RetryCount,
                                                    @MaxRetries,
                                                    @QueueName,
                                                    @LastUpdate,
                                                    @Pinned,
                                                    @Publish,
                                                    @ResultLocation,
                                                    @ResultFormat,
                                                    @StartTime,
                                                    @EndTime,
                                                    @ExpiryAt,
                                                    @ErrorMessage,
                                                    @SysJobCreatedById,
                                                    @SysJobCreatedOn,
                                                    @SysJobModifiedById,
                                                    @SysJobModifiedOn,
                                                    @SysJobSortOrder,
                                                    @SysJobStatus,
                                                    @SysJobVersion,
                                                    @SysJobSourceType,
                                                    @SysJobTenantId,
                                                    @DeliveryChannel,
                                                    @DeliveryDestination );
                                               SELECT CAST(SCOPE_IDENTITY() AS INT);";



        public const string UPDATE_SYSTEMJOB= @"UPDATE TSYSJOB SET
                                                    SYSJOBTYPE =@SysJobType,
                                                    MENUID = @MenuId,
                                                    WEBSERVICEID = @WebServiceId,
                                                    PARAMETERS = @Parameters,
                                                    SUBMITTEDBYID = @SubmittedById,
                                                    SUBMITTEDON = @SubmittedOn,
                                                    RUNSTATUS = @RunStatus,
                                                    PRIORITY = @Priority,
                                                    RETRYCOUNT = @RetryCount,
                                                    MAXRETRIES = @MaxRetries,
                                                    QUEUENAME = @QueueName,
                                                    LASTUPDATED = @LastUpdate,
                                                    PINNED = @Pinned,
                                                    PUBLISH = @Publish,
                                                    RESULTLOCATION = @ResultLocation,
                                                    RESULTFORMAT = @ResultFormat,
                                                    STARTTIME = @StartTime,
                                                    ENDTIME = @EndTime,
                                                    EXPIRYAT = @ExpiryAt,
                                                    ERRORMESSAGE = @ErrorMessage,
                                                    MODIFIEDBYID = @SysJobModifiedById,
                                                    MODIFIEDON = @SysJobModifiedOn,
                                                    SORTORDER = @SysJobSortOrder,
                                                    STATUS = @SysJobStatus,
                                                    VERSION = @SysJobVersion,
                                                    SOURCETYPE = @SysJobSourceType,
                                                    TENANTID = @SysJobTenantId,
                                                    DELIVERYCHANNEL = @DeliveryChannel,
                                                    DELIVERYDESTINATION = @DeliveryDestination
                                                     WHERE SYSJOBID = @SysJobId; ";
                          


        public const string DELETE_SYSTEMJOB = @"DELETE FROM TSYSJOB
                                WHERE SYSJOBID = @SysJobId
                                AND TENANTID = @SysJobTenantId;";

        // ── Executor queries ─────────────────────────────────────────────────

        /// <summary>
        /// Load pending TSYSJOB rows for the executor (RUNSTATUS=0).
        /// Also loads the web service URL template for immediate dispatch.
        /// Required index: TSYSJOB (TENANTID, RUNSTATUS, PRIORITY)
        /// </summary>
        public const string GET_PENDING_JOBS = @"
SELECT TOP (@BatchSize)
    S.SYSJOBID          AS SysJobId,
    S.SYSJOBTYPE        AS SysJobType,
    S.WEBSERVICEID      AS WebServiceId,
    S.PARAMETERS        AS Parameters,
    S.SUBMITTEDBYID     AS SubmittedById,
    S.PRIORITY          AS Priority,
    S.RETRYCOUNT        AS RetryCount,
    S.MAXRETRIES        AS MaxRetries,
    S.QUEUENAME         AS QueueName,
    S.TENANTID          AS SysJobTenantId,
    S.DELIVERYCHANNEL   AS DeliveryChannel,
    S.DELIVERYDESTINATION AS DeliveryDestination,
    S.RESULTFORMAT      AS ResultFormat,
    W.URITEMPLATE       AS WebServiceUriTemplate,
    W.SECONDURITEMPLATE AS WebServiceSecondUriTemplate,
    W.METHODTYPE        AS WebServiceMethodType
FROM TSYSJOB S
LEFT JOIN MWEBSERVICE W ON W.WEBSERVICEID = S.WEBSERVICEID
WHERE S.RUNSTATUS = 0          -- Pending only (excludes Cancelled=4)
AND   S.TENANTID  = @SysJobTenantId
AND   (S.STARTTIME IS NULL OR S.STARTTIME <= GETUTCDATE())   -- honor a future schedule time
ORDER BY S.PRIORITY DESC, S.SUBMITTEDON ASC";

        /// <summary>
        /// Whether a WebServiceId is actually wired for background execution — a real,
        /// non-sentinel MWEBSERVICE row with a usable URITEMPLATE. Lets the frontend disable
        /// the "Job" button with an honest message instead of accepting a submission that the
        /// executor can only ever fail on (see SysJobExecutorQuartzJob.CallWebServiceAsync's
        /// "No MWEBSERVICE configured" branch).
        /// </summary>
        public const string CHECK_JOB_ELIGIBILITY = @"
SELECT WEBSERVICEID AS WebServiceId, URITEMPLATE AS UriTemplate, SECONDURITEMPLATE AS SecondUriTemplate
FROM   MWEBSERVICE
WHERE  WEBSERVICEID = @WebServiceId";

        /// <summary>
        /// Claim a job atomically (mark InProgress) for a specific worker.
        /// Only claims if still Pending — prevents double-pickup under concurrency.
        /// </summary>
        public const string CLAIM_JOB = @"
UPDATE TSYSJOB SET
    RUNSTATUS    = 1,
    STARTTIME    = GETUTCDATE(),
    LASTUPDATED   = GETUTCDATE(),
    MODIFIEDBYID = @SysJobModifiedById,
    MODIFIEDON   = GETUTCDATE()
WHERE SYSJOBID   = @SysJobId
AND   TENANTID   = @SysJobTenantId
AND   RUNSTATUS  = 0";

        /// <summary>
        /// Mark job Completed (RUNSTATUS=2) with result location.
        /// RESULTLOCATION conventions:
        ///   att:{attachmentId}  — file stored in DMS/TATTACHMENT (preferred for report outputs)
        ///   redis:{key}         — file stored in IDistributedCache (short-lived results)
        ///   /path/to/file       — legacy filesystem path
        ///   NULL                — no file output (fire-and-forget jobs)
        /// Only updates if still InProgress (RUNSTATUS=1) — prevents overwriting a cancel.
        /// </summary>
        public const string COMPLETE_JOB = @"
UPDATE TSYSJOB SET
    RUNSTATUS      = 2,
    ENDTIME        = GETUTCDATE(),
    LASTUPDATED     = GETUTCDATE(),
    RESULTLOCATION = @ResultLocation,
    ERRORMESSAGE   = NULL,
    MODIFIEDBYID   = @SysJobModifiedById,
    MODIFIEDON     = GETUTCDATE()
WHERE SYSJOBID    = @SysJobId
AND   TENANTID    = @SysJobTenantId
AND   RUNSTATUS   = 1;";

        /// <summary>
        /// Mark job Failed (RUNSTATUS=3) or reset to Pending (RUNSTATUS=0) for retry.
        /// </summary>
        public const string FAIL_OR_RETRY_JOB = @"
UPDATE TSYSJOB SET
    RUNSTATUS    = @RunStatus,
    ENDTIME      = GETUTCDATE(),
    LASTUPDATED   = GETUTCDATE(),
    RETRYCOUNT   = RETRYCOUNT + 1,
    ERRORMESSAGE = @ErrorMessage,
    MODIFIEDBYID = @SysJobModifiedById,
    MODIFIEDON   = GETUTCDATE()
WHERE SYSJOBID   = @SysJobId
AND   TENANTID   = @SysJobTenantId
AND   RUNSTATUS  = 1;";

        // ── List / filter queries ─────────────────────────────────────────────

        /// <summary>
        /// Paginated, filtered job list.
        /// Shows own jobs plus jobs shared with the requesting user.
        /// Required indexes: IX_TSYSJOB_RunStatus_TenantId, IX_TSYSJOB_SubmittedOn_TenantId
        /// </summary>
        public const string GET_LIST_SYSTEMJOB = @"
WITH PagedJobs AS (
    SELECT
        S.SYSJOBID          AS SysJobId,
        S.SYSJOBTYPE        AS SysJobType,
        S.MENUID            AS MenuId,
        M.MENUNAME          AS MenuName,
        S.QUEUENAME         AS QueueName,
        S.PRIORITY          AS Priority,
        S.RUNSTATUS         AS RunStatus,
        S.SUBMITTEDON       AS SubmittedOn,
        U.USERNAME          AS SubmittedByName,
        S.STARTTIME         AS StartTime,
        S.ENDTIME           AS EndTime,
        S.ERRORMESSAGE      AS ErrorMessage,
        S.RETRYCOUNT        AS RetryCount,
        S.MAXRETRIES        AS MaxRetries,
        S.RESULTLOCATION    AS ResultLocation,
        S.RESULTFORMAT      AS ResultFormat,
        S.CANCELLEDAT       AS CancelledAt,
        S.DELIVERYCHANNEL   AS DeliveryChannel,
        S.DELIVERYDESTINATION AS DeliveryDestination,
        CASE WHEN S.SUBMITTEDBYID = @UserId THEN 0 ELSE 1 END AS IsShared,
        SHU.USERNAME        AS SharedByName,
        SH.CANEXECUTE       AS CanExecute,
        SH.CANVIEWRESULT    AS CanViewResult,
        ROW_NUMBER() OVER (ORDER BY S.SUBMITTEDON DESC, S.SYSJOBID DESC) AS RowNum
    FROM TSYSJOB S
    LEFT JOIN MUSER U ON U.USERID = S.SUBMITTEDBYID
    LEFT JOIN MMENU M ON M.MENUID = S.MENUID
    LEFT JOIN TSYSJOBSHARE SH ON SH.SYSJOBID = S.SYSJOBID AND SH.SHAREDWITHUSERID = @UserId
    LEFT JOIN MUSER SHU ON SHU.USERID = SH.SHAREDBYID
    WHERE (
            S.SUBMITTEDBYID = @UserId
            OR EXISTS (
                SELECT 1 FROM TSYSJOBSHARE SH
                WHERE SH.SYSJOBID         = S.SYSJOBID
                  AND SH.SHAREDWITHUSERID = @UserId
            )
          )
      AND (@RunStatus  IS NULL OR @RunStatus = -1  OR S.RUNSTATUS  = @RunStatus)
      AND (@SysJobType IS NULL OR @SysJobType = '' OR S.SYSJOBTYPE = @SysJobType)
      AND (@QueueName  IS NULL OR @QueueName  = '' OR S.QUEUENAME  = @QueueName)
      AND (@DateFrom   IS NULL OR S.SUBMITTEDON >= @DateFrom)
      AND (@DateTo     IS NULL OR S.SUBMITTEDON <= @DateTo)
      AND (@SearchText IS NULL OR @SearchText = '' OR S.PARAMETERS  LIKE '%' + @SearchText + '%'
                                                    OR S.ERRORMESSAGE LIKE '%' + @SearchText + '%')
)
SELECT
    SysJobId, SysJobType, MenuId, MenuName, QueueName, Priority, RunStatus,
    SubmittedOn, SubmittedByName, StartTime, EndTime, ErrorMessage,
    RetryCount, MaxRetries, ResultLocation, ResultFormat, CancelledAt, IsShared,
    DeliveryChannel, DeliveryDestination, SharedByName, CanExecute, CanViewResult
FROM PagedJobs
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum BETWEEN @FirstNumber AND @MaxResult);";

        /// <summary>
        /// Total count for GET_LIST_SYSTEMJOB (same WHERE, no pagination).
        /// </summary>
        public const string GET_LIST_COUNT_SYSTEMJOB = @"
SELECT COUNT(*)
FROM TSYSJOB S
WHERE S.TENANTID = @SysJobTenantId
  AND (
        S.SUBMITTEDBYID = @UserId
        OR EXISTS (
            SELECT 1 FROM TSYSJOBSHARE SH
            WHERE SH.SYSJOBID         = S.SYSJOBID
              AND SH.SHAREDWITHUSERID = @UserId
              AND SH.TENANTID         = @SysJobTenantId
        )
      )
  AND (@RunStatus  IS NULL OR @RunStatus = -1  OR S.RUNSTATUS  = @RunStatus)
  AND (@SysJobType IS NULL OR @SysJobType = '' OR S.SYSJOBTYPE = @SysJobType)
  AND (@QueueName  IS NULL OR @QueueName  = '' OR S.QUEUENAME  = @QueueName)
  AND (@DateFrom   IS NULL OR S.SUBMITTEDON >= @DateFrom)
  AND (@DateTo     IS NULL OR S.SUBMITTEDON <= @DateTo)
  AND (@SearchText IS NULL OR @SearchText = '' OR S.PARAMETERS  LIKE '%' + @SearchText + '%'
                                                OR S.ERRORMESSAGE LIKE '%' + @SearchText + '%');";

        // ── Cancel query ──────────────────────────────────────────────────────

        /// <summary>
        /// Cancel a job — only if still Pending (0) or InProgress (1).
        /// Sets RUNSTATUS=4 (Cancelled).
        /// </summary>
        public const string CANCEL_JOB = @"
UPDATE TSYSJOB SET
    RUNSTATUS    = 4,
    CANCELLEDAT  = GETUTCDATE(),
    LASTUPDATED   = GETUTCDATE(),
    MODIFIEDBYID = @SysJobModifiedById,
    MODIFIEDON   = GETUTCDATE()
WHERE SYSJOBID  = @SysJobId
AND   TENANTID  = @SysJobTenantId
AND   RUNSTATUS IN (0, 1);";

        // ── Summary / dashboard query ─────────────────────────────────────────

        /// <summary>
        /// Counts jobs by status for the dashboard summary tile.
        /// </summary>
        public const string GET_JOB_SUMMARY = @"
SELECT
    COUNT(CASE WHEN RUNSTATUS = 0 THEN 1 END) AS Pending,
    COUNT(CASE WHEN RUNSTATUS = 1 THEN 1 END) AS InProgress,
    COUNT(CASE WHEN RUNSTATUS = 2 THEN 1 END) AS Completed,
    COUNT(CASE WHEN RUNSTATUS = 3 THEN 1 END) AS Failed,
    COUNT(CASE WHEN RUNSTATUS = 4 THEN 1 END) AS Cancelled,
    COUNT(*)                                   AS Total
FROM TSYSJOB
WHERE TENANTID = @SysJobTenantId
  AND (@DateFrom IS NULL OR SUBMITTEDON >= @DateFrom)
  AND (@DateTo   IS NULL OR SUBMITTEDON <= @DateTo);";
    }
}