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

namespace FrameworkDAL.Query.JobDefine
{
    public static class JobDefineQB
    {
        // Criteria (ApplyCriteria) is appended directly after "WHERE 1 = 1" below,
        // so this constant must NOT close the FilteredJobDefine CTE.
        public const string GET_SELECTLIST_JOBDEFINE_BASE = @"
    WITH FilteredJobDefine AS (
        SELECT
            JOBID   AS JobdefinePicklistId,
            JOBNAME AS JobdefinePicklistName
        FROM MJOBDEFINE
        WHERE 1 = 1";

        public const string GET_SELECTLIST_JOBDEFINE_PAGING_SUFFIX = @"
    ),
    PagedJobDefine AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY JobdefinePicklistId) AS RowNum
        FROM FilteredJobDefine
    )
    SELECT
        JobdefinePicklistId,
        JobdefinePicklistName
    FROM PagedJobDefine
    WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_JOBDEFINE_COUNT_BASE = @"
    SELECT COUNT(*)
    FROM MJOBDEFINE
    WHERE 1 = 1";

        // Admin list + single-record fetch. @JobId = 0 → all jobs for this tenant.
        // Required index: IX_MJOBDEFINE_TENANTID (TENANTID) — low-cardinality covering scan.
        public const string GET_JOB_DEFINE = @"
    SELECT
        j.JOBID            AS JobId,
        j.JOBNAME          AS JobName,
        j.STATUS           AS Status,
        j.SCHEDULERID      AS SchedulerId,
        j.WEBSERVICEID     AS WebServiceId,
        j.CRITERIACONFIGID AS CriteriaConfigId,
        j.MENUID           AS MenuId,
        ISNULL(j.RUNASUSEID,       -1) AS RunAsUserId,
        ISNULL(j.RUNASROLEID,      -1) AS RunAsRoleId,
        ISNULL(j.RUNASOUID,        -1) AS RunAsOUId,
        ISNULL(j.RUNASBRANCHID,    -1) AS RunAsBranchId,
        ISNULL(j.RUNASWORKPARTYID, -1) AS RunAsWorkPartyId,
        ISNULL(j.RUNASSTOREID,     -1) AS RunAsStoreId,
        ISNULL(j.RUNASPERIODID,    -1) AS RunAsPeriodId,
        s.SCHEDULERNAME             AS SchedulerName
    FROM  MJOBDEFINE j
    LEFT  JOIN TSCHEDULER s ON s.SCHEDULERID = j.SCHEDULERID
    WHERE j.TENANTID = @TenantId
      AND (@JobId = 0 OR j.JOBID = @JobId)
    ORDER BY j.JOBID DESC";

        // Delete child MACTION rows before removing the parent MJOBDEFINE row.
        public const string DELETE_JOB_ACTIONS = @"
    DELETE FROM MACTION WHERE JOBID = @JobId";

        public const string DELETE_JOB_DEFINE = @"
    DELETE FROM MJOBDEFINE WHERE JOBID = @JobId AND TENANTID = @TenantId";
    }
}