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

namespace FrameworkDAL.Query.SysJobRun
{
    public static class SysJobRunQB
    {
        public const string GET_SYSJOBRUN = @"
    SELECT
        S.SYSJOBRUNID AS SysJobRunId,
        S.SYSJOBID AS SysJobId,
        --J.JOBNAME AS SysJobName,      
        --J.JOBCODE AS SysJobCode,      

        S.STARTEDAT AS StartedAt,
        S.ENDEDAT AS EndedAt,
        S.STATUS AS Status,
        S.LOG AS Log,
        S.WORKER AS Worker,
        S.RUNIDENTIFIER AS RunIdentifier
    FROM TSYSJOBRUN S
    LEFT JOIN TSYSJOB J ON S.SYSJOBID = J.SYSJOBID   
    WHERE S.SYSJOBRUNID = @SysJobRunId;
";


        public const string SAVE_SYSJOBRUN = @"
    INSERT INTO TSYSJOBRUN(
        SYSJOBID,
        STARTEDAT,
        ENDEDAT,
        STATUS,
        LOG,
        WORKER,
        RUNIDENTIFIER
    )
    VALUES
    (
        @SysJobId,
        @StartedAt,
        @EndedAt,
        @Status,
        @Log,
        @Worker,
        @RunIdentifier
    );
 SELECT CAST(SCOPE_IDENTITY() AS INT);
";

        public const string UPDATE_SYSJOBRUN = @"
    UPDATE TSYSJOBRUN
    SET
        SYSJOBID = @SysJobId,
        STARTEDAT = @StartedAt,
        ENDEDAT = @EndedAt,
        STATUS = @Status,
        LOG = @Log,
        WORKER = @Worker,
        RUNIDENTIFIER = @RunIdentifier
    WHERE SYSJOBRUNID = @SysJobRunId;
";

        public const string DELETE_SYSJOBRUN = @"DELETE FROM TSYSJOBRUN
                                                WHERE SYSJOBRUNID = @SysJobRunId;";

        /// <summary>
        /// Appends a line to an existing run's LOG without touching any other column.
        /// Used by SysJobNotificationDispatcher to record a delivery attempt.
        /// </summary>
        public const string APPEND_SYSJOBRUN_LOG = @"
    UPDATE TSYSJOBRUN
    SET LOG = COALESCE(LOG, '') + @LogSuffix
    WHERE SYSJOBRUNID = @SysJobRunId;
";

        /// <summary>
        /// Paginated run history. Filter by job, status, and date range.
        /// Required index: IX_TSYSJOBRUN_SysJobId_TenantId (SYSJOBID, TENANTID)
        /// </summary>
        public const string GET_LIST_SYSJOBRUN = @"
WITH PagedRuns AS (
    SELECT
        S.SYSJOBRUNID   AS SysJobRunId,
        S.SYSJOBID      AS SysJobId,
        S.STARTEDAT     AS StartedAt,
        S.ENDEDAT       AS EndedAt,
        S.STATUS        AS Status,
        S.LOG           AS Log,
        S.WORKER        AS Worker,
        S.RUNIDENTIFIER AS RunIdentifier,
        ROW_NUMBER() OVER (ORDER BY S.STARTEDAT DESC, S.SYSJOBRUNID DESC) AS RowNum
    FROM TSYSJOBRUN S
    WHERE (@SysJobId IS NULL OR @SysJobId = -1 OR S.SYSJOBID  = @SysJobId)
      AND (@Status   IS NULL OR @Status   = -1 OR S.STATUS    = @Status)
      AND (@DateFrom IS NULL OR S.STARTEDAT >= @DateFrom)
      AND (@DateTo   IS NULL OR S.STARTEDAT <= @DateTo)
)
SELECT
    SysJobRunId, SysJobId, StartedAt, EndedAt, Status, Log, Worker,
    RunIdentifier
FROM PagedRuns
WHERE (@FirstNumber = -1 AND @MaxResult = -1)
   OR (RowNum BETWEEN @FirstNumber AND @MaxResult);";

        /// <summary>
        /// Total count for GET_LIST_SysJobRun (same WHERE, no pagination).
        /// </summary>
        public const string GET_LIST_COUNT_SYSJOBRUN = @"SELECT COUNT(*)
                                                FROM TSYSJOBRUN S
                                                WHERE (@SysJobId IS NULL OR @SysJobId = -1 OR S.SYSJOBID  = @SysJobId)
                                                  AND (@Status   IS NULL OR @Status   = -1 OR S.STATUS    = @Status)
                                                  AND (@DateFrom IS NULL OR S.STARTEDAT >= @DateFrom)
                                                  AND (@DateTo   IS NULL OR S.STARTEDAT <= @DateTo);";

    }
}