namespace JobEngineDAL.Query
{
    public static class JobDefineQB
    {
        public const string GET_JOBS = @"
            SELECT
                j.JOBID,            j.SCHEDULERID,      j.JOBNAME,
                j.WEBSERVICEID,     j.CRITERIACONFIGID, j.LASTRUNTIME,
                j.STATUS,           j.MENUID,           j.TENANTID,
                j.URIPARAMETERVALUE,
                j.MAXRETRIES,       j.TIMEOUTSECONDS,   j.NEXTRUNTIME,
                j.ISCONCURRENT,     j.JOBCATEGORY,      j.SOURCEMODULE,
                j.SOURCEOBJECTID,   j.DAPRPUBSUBTOPIC,
                j.CREATEDBY,        j.CREATEDDATE,      j.UPDATEDBY,    j.UPDATEDDATE,
                s.CRONEXPRESSION,   s.SCHEDULERTYPE,    s.INTERVALSECONDS,
                s.SCHEDULEDDATE,    s.DESCRIPTION       AS SchedulerDescription,
                s.SCHEDULERSTATUS,
                w.URITEMPLATE,
                CASE w.METHODTYPE
                    WHEN 0 THEN 'GET'
                    WHEN 1 THEN 'POST'
                    WHEN 2 THEN 'DELETE'
                    ELSE 'GET'
                END AS MethodType
            FROM   MJOBDEFINE     j
            LEFT JOIN TSCHEDULER  s ON s.SCHEDULERID = j.SCHEDULERID AND s.SCHEDULERID <> -1
            LEFT JOIN MWEBSERVICE w ON w.WEBSERVICEID = j.WEBSERVICEID AND j.WEBSERVICEID <> -1
            WHERE  j.TENANTID = @TenantId
            ORDER  BY j.JOBNAME";

        public const string GET_JOB_DETAIL = @"
            SELECT
                j.JOBID,            j.SCHEDULERID,      j.JOBNAME,
                j.WEBSERVICEID,     j.CRITERIACONFIGID, j.LASTRUNTIME,
                j.STATUS,           j.MENUID,           j.TENANTID,
                j.URIPARAMETERVALUE,
                j.MAXRETRIES,       j.TIMEOUTSECONDS,   j.NEXTRUNTIME,
                j.ISCONCURRENT,     j.JOBCATEGORY,      j.SOURCEMODULE,
                j.SOURCEOBJECTID,   j.DAPRPUBSUBTOPIC,
                j.CREATEDBY,        j.CREATEDDATE,      j.UPDATEDBY,    j.UPDATEDDATE,
                s.CRONEXPRESSION,   s.SCHEDULERTYPE,    s.INTERVALSECONDS,
                s.SCHEDULEDDATE,    s.DESCRIPTION       AS SchedulerDescription,
                s.SCHEDULERSTATUS,
                w.URITEMPLATE,
                CASE w.METHODTYPE
                    WHEN 0 THEN 'GET'
                    WHEN 1 THEN 'POST'
                    WHEN 2 THEN 'DELETE'
                    ELSE 'GET'
                END AS MethodType
            FROM   MJOBDEFINE     j
            LEFT JOIN TSCHEDULER  s ON s.SCHEDULERID = j.SCHEDULERID AND s.SCHEDULERID <> -1
            LEFT JOIN MWEBSERVICE w ON w.WEBSERVICEID = j.WEBSERVICEID AND j.WEBSERVICEID <> -1
            WHERE  j.JOBID    = @JobId
            AND    j.TENANTID = @TenantId";

        public const string SAVE_JOB_INSERT = @"
            INSERT INTO MJOBDEFINE
                (SCHEDULERID, JOBNAME, WEBSERVICEID, CRITERIACONFIGID, LASTRUNTIME,
                 STATUS, MENUID, TENANTID, URIPARAMETERVALUE,
                 MAXRETRIES, TIMEOUTSECONDS, ISCONCURRENT, JOBCATEGORY,
                 SOURCEMODULE, SOURCEOBJECTID, DAPRPUBSUBTOPIC,
                 CREATEDBY, CREATEDDATE)
            VALUES
                (@SchedulerId, @JobName, @WebServiceId, @CriteriaConfigId, NULL,
                 @Status, @MenuId, @TenantId, @UriParameterValue,
                 @MaxRetries, @TimeoutSeconds, @IsConcurrent, @JobCategory,
                 @SourceModule, @SourceObjectId, @DaprPubSubTopic,
                 @CreatedBy, GETUTCDATE());
            SELECT SCOPE_IDENTITY();";

        public const string SAVE_JOB_UPDATE = @"
            UPDATE MJOBDEFINE SET
                SCHEDULERID       = @SchedulerId,
                JOBNAME           = @JobName,
                WEBSERVICEID      = @WebServiceId,
                CRITERIACONFIGID  = @CriteriaConfigId,
                STATUS            = @Status,
                MENUID            = @MenuId,
                URIPARAMETERVALUE = @UriParameterValue,
                MAXRETRIES        = @MaxRetries,
                TIMEOUTSECONDS    = @TimeoutSeconds,
                ISCONCURRENT      = @IsConcurrent,
                JOBCATEGORY       = @JobCategory,
                SOURCEMODULE      = @SourceModule,
                SOURCEOBJECTID    = @SourceObjectId,
                DAPRPUBSUBTOPIC   = @DaprPubSubTopic,
                UPDATEDBY         = @UpdatedBy,
                UPDATEDDATE       = GETUTCDATE()
            WHERE JOBID    = @JobId
            AND   TENANTID = @TenantId";

        public const string DELETE_JOB = @"
            UPDATE MJOBDEFINE SET STATUS = 0, UPDATEDBY = @UpdatedBy, UPDATEDDATE = GETUTCDATE()
            WHERE  JOBID    = @JobId
            AND    TENANTID = @TenantId";

        public const string UPDATE_LAST_RUN = @"
            UPDATE MJOBDEFINE SET
                LASTRUNTIME = GETUTCDATE(),
                NEXTRUNTIME = @NextRunTime,
                UPDATEDDATE = GETUTCDATE()
            WHERE JOBID    = @JobId
            AND   TENANTID = @TenantId";

        public const string UPDATE_NEXT_RUN = @"
            UPDATE MJOBDEFINE SET NEXTRUNTIME = @NextRunTime
            WHERE  JOBID    = @JobId
            AND    TENANTID = @TenantId";

        public const string PAUSE_JOB = @"
            UPDATE MJOBDEFINE SET STATUS = 0, UPDATEDBY = @UpdatedBy, UPDATEDDATE = GETUTCDATE()
            WHERE  JOBID = @JobId AND TENANTID = @TenantId";

        public const string RESUME_JOB = @"
            UPDATE MJOBDEFINE SET STATUS = 1, UPDATEDBY = @UpdatedBy, UPDATEDDATE = GETUTCDATE()
            WHERE  JOBID = @JobId AND TENANTID = @TenantId";

        public const string GET_ALL_ACTIVE_JOBS = @"
            SELECT
                j.JOBID, j.SCHEDULERID, j.JOBNAME, j.WEBSERVICEID, j.CRITERIACONFIGID,
                j.LASTRUNTIME, j.STATUS, j.MENUID, j.TENANTID, j.URIPARAMETERVALUE,
                j.MAXRETRIES, j.TIMEOUTSECONDS, j.NEXTRUNTIME, j.ISCONCURRENT,
                j.JOBCATEGORY, j.SOURCEMODULE, j.SOURCEOBJECTID, j.DAPRPUBSUBTOPIC,
                s.CRONEXPRESSION, s.SCHEDULERTYPE, s.INTERVALSECONDS,
                s.SCHEDULEDDATE, s.DESCRIPTION AS SchedulerDescription, s.SCHEDULERSTATUS,
                w.URITEMPLATE,
                CASE w.METHODTYPE WHEN 0 THEN 'GET' WHEN 1 THEN 'POST' WHEN 2 THEN 'DELETE' ELSE 'GET' END AS MethodType
            FROM   MJOBDEFINE     j
            LEFT JOIN TSCHEDULER  s ON s.SCHEDULERID = j.SCHEDULERID AND s.SCHEDULERID <> -1
            LEFT JOIN MWEBSERVICE w ON w.WEBSERVICEID = j.WEBSERVICEID AND j.WEBSERVICEID <> -1
            WHERE  j.STATUS = 1";
            // Cross-tenant by design — called at startup to seed Quartz with ALL tenants' jobs.
            // Each job's TenantId is embedded in JobDataMap so the executor uses the correct tenant.

        public const string UPSERT_BY_SOURCE = @"
            IF EXISTS (SELECT 1 FROM MJOBDEFINE WHERE SOURCEMODULE = @SourceModule AND SOURCEOBJECTID = @SourceObjectId AND TENANTID = @TenantId)
            BEGIN
                UPDATE MJOBDEFINE SET
                    JOBNAME          = @JobName,
                    SCHEDULERID      = @SchedulerId,
                    WEBSERVICEID     = @WebServiceId,
                    STATUS           = @Status,
                    MAXRETRIES       = @MaxRetries,
                    TIMEOUTSECONDS   = @TimeoutSeconds,
                    DAPRPUBSUBTOPIC  = @DaprPubSubTopic,
                    UPDATEDBY        = @UpdatedBy,
                    UPDATEDDATE      = GETUTCDATE()
                WHERE SOURCEMODULE = @SourceModule AND SOURCEOBJECTID = @SourceObjectId AND TENANTID = @TenantId;
                SELECT JOBID FROM MJOBDEFINE WHERE SOURCEMODULE = @SourceModule AND SOURCEOBJECTID = @SourceObjectId AND TENANTID = @TenantId;
            END
            ELSE
            BEGIN
                INSERT INTO MJOBDEFINE
                    (SCHEDULERID, JOBNAME, WEBSERVICEID, CRITERIACONFIGID, STATUS, MENUID, TENANTID,
                     URIPARAMETERVALUE, MAXRETRIES, TIMEOUTSECONDS, ISCONCURRENT,
                     JOBCATEGORY, SOURCEMODULE, SOURCEOBJECTID, DAPRPUBSUBTOPIC, CREATEDBY, CREATEDDATE)
                VALUES
                    (@SchedulerId, @JobName, @WebServiceId, -1, @Status, -1, @TenantId,
                     NULL, @MaxRetries, @TimeoutSeconds, 0,
                     @JobCategory, @SourceModule, @SourceObjectId, @DaprPubSubTopic, @CreatedBy, GETUTCDATE());
                SELECT SCOPE_IDENTITY();
            END";
    }
}