namespace FrameworkDAL.Query.DataSync
{
    public static class SyncRunLogQB
    {
        // Required index: IX_TDSYNCRUNLOG_SYNCJOBID_START on (SYNCJOBID, STARTEDAT DESC)
        //                 INCLUDE (STATUS, TOTALROWS, INSERTEDROWS, UPDATEDROWS, DELETEDROWS)
        public const string GET_SYNCRUNLOG = @"
            SELECT
                RL.RUNID               AS RunId,
                RL.SYNCJOBID           AS SyncJobId,
                SJ.DESCRIPTION         AS SyncJobDescription,
                RL.TRIGGEREDBY         AS TriggeredBy,
                RL.TRIGGEREDBYUSERID   AS TriggeredByUserId,
                U.USERNAME             AS TriggeredByUserName,
                RL.STARTEDAT           AS StartedAt,
                RL.ENDEDAT             AS EndedAt,
                RL.STATUS              AS Status,
                RL.TOTALROWS           AS TotalRows,
                RL.INSERTEDROWS        AS InsertedRows,
                RL.UPDATEDROWS         AS UpdatedRows,
                RL.DELETEDROWS         AS DeletedRows,
                RL.SKIPPEDROWS         AS SkippedRows,
                RL.ERRORDETAIL         AS ErrorDetail
            FROM  TDSYNCRUNLOG RL
            JOIN  TDSYNCJOB    SJ ON RL.SYNCJOBID          = SJ.SYNCJOBID
            LEFT JOIN MUSER    U  ON RL.TRIGGEREDBYUSERID  = U.USERID
            WHERE RL.RUNID = @RunId";

        public const string GET_SYNCRUNLOG_LIST = @"
            SELECT
                RL.RUNID             AS RunId,
                RL.SYNCJOBID         AS SyncJobId,
                SJ.DESCRIPTION       AS SyncJobDescription,
                RL.TRIGGEREDBY       AS TriggeredBy,
                RL.STARTEDAT         AS StartedAt,
                RL.ENDEDAT           AS EndedAt,
                RL.STATUS            AS Status,
                RL.TOTALROWS         AS TotalRows,
                RL.INSERTEDROWS      AS InsertedRows,
                RL.UPDATEDROWS       AS UpdatedRows,
                RL.DELETEDROWS       AS DeletedRows,
                RL.SKIPPEDROWS       AS SkippedRows
            FROM  TDSYNCRUNLOG RL
            JOIN  TDSYNCJOB    SJ ON RL.SYNCJOBID = SJ.SYNCJOBID
            WHERE RL.SYNCJOBID = @SyncJobId
            ORDER BY RL.STARTEDAT DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_SYNCRUNLOG_LIST_COUNT = @"
            SELECT COUNT(*) FROM TDSYNCRUNLOG WHERE SYNCJOBID = @SyncJobId";

        // Called at run start; STATUS=1 (Running) and row counts are NULL until completion.
        public const string SAVE_SYNCRUNLOG = @"
            INSERT INTO TDSYNCRUNLOG (
                RUNID, SYNCJOBID,
                TRIGGEREDBY, TRIGGEREDBYUSERID,
                STARTEDAT, STATUS
            ) VALUES (
                @RunId, @SyncJobId,
                @TriggeredBy, @TriggeredByUserId,
                GETUTCDATE(), 1
            )";

        // Called on run completion (success, failure, or partial failure).
        public const string UPDATE_SYNCRUNLOG_COMPLETE = @"
            UPDATE TDSYNCRUNLOG SET
                ENDEDAT      = GETUTCDATE(),
                STATUS       = @Status,
                TOTALROWS    = @TotalRows,
                INSERTEDROWS = @InsertedRows,
                UPDATEDROWS  = @UpdatedRows,
                DELETEDROWS  = @DeletedRows,
                SKIPPEDROWS  = @SkippedRows,
                ERRORDETAIL  = @ErrorDetail
            WHERE RUNID = @RunId";
    }
}