namespace FrameworkDAL.Query.DataSync
{
    public static class SyncJobQB
    {
        // Required index: IX_TDSYNCJOB_ISENABLED_STATUS on (ISENABLED, STATUS) INCLUDE (SYNCJOBID, SCHEDULERID, LASTRUNSTATUS, LASTRUNAT)
        public const string GET_SYNCJOB = @"
            SELECT
                SJ.SYNCJOBID          AS SyncJobId,
                SJ.DESCRIPTION        AS Description,
                SJ.SOURCEDBINSTANCEID AS SourceDBInstanceId,
                SRC.DBINSTANCENAME    AS SourceDBInstanceName,
                SRC.DBINSTANCEDATABASE AS SourceDBName,
                SJ.TARGETDBINSTANCEID AS TargetDBInstanceId,
                TGT.DBINSTANCENAME    AS TargetDBInstanceName,
                TGT.DBINSTANCEDATABASE AS TargetDBName,
                SJ.DATASETID          AS DatasetId,
                DS.DATASETCODE        AS DatasetCode,
                DS.DATASETNAME        AS DatasetName,
                SJ.SCHEDULERID        AS SchedulerId,
                SCH.SCHEDULERNAME     AS SchedulerName,
                SJ.CONFLICTPOLICY     AS ConflictPolicy,
                SJ.DIRECTION          AS Direction,
                SJ.ISENABLED          AS IsEnabled,
                SJ.QUERYCONDITION     AS QueryCondition,
                SJ.LASTINSERTEDTILL   AS LastInsertedTill,
                SJ.LASTUPDATEDTILL    AS LastUpdatedTill,
                SJ.LASTDELETETILL     AS LastDeleteTill,
                SJ.LASTRUNSTATUS      AS LastRunStatus,
                SJ.LASTRUNAT          AS LastRunAt,
                SJ.LASTRUNID          AS LastRunId,
                SJ.SORTORDER          AS SortOrder,
                SJ.STATUS             AS Status,
                SJ.VERSION            AS Version,
                SJ.SOURCETYPE         AS SourceType,
                SJ.CREATEDBYID        AS CreatedById,
                SJ.CREATEDON          AS CreatedOn,
                UC.USERNAME           AS CreatedByName,
                SJ.MODIFIEDBYID       AS ModifiedById,
                SJ.MODIFIEDON         AS ModifiedOn,
                UM.USERNAME           AS ModifiedByName
            FROM  TDSYNCJOB SJ
            LEFT JOIN DBINSTANCE  SRC ON SJ.SOURCEDBINSTANCEID = SRC.DBINSTANCEID
            LEFT JOIN DBINSTANCE  TGT ON SJ.TARGETDBINSTANCEID = TGT.DBINSTANCEID
            LEFT JOIN MDATASET    DS  ON SJ.DATASETID           = DS.DATASETID
            LEFT JOIN TSCHEDULER  SCH ON SJ.SCHEDULERID         = SCH.SCHEDULERID
            LEFT JOIN MUSER       UC  ON SJ.CREATEDBYID         = UC.USERID
            LEFT JOIN MUSER       UM  ON SJ.MODIFIEDBYID        = UM.USERID
            WHERE SJ.SYNCJOBID = @SyncJobId";

        // Required index: IX_TDSYNCJOB_ISENABLED_STATUS (see above)
        public const string GET_SYNCJOB_LIST = @"
            SELECT
                SJ.SYNCJOBID          AS SyncJobId,
                SJ.DESCRIPTION        AS Description,
                SRC.DBINSTANCENAME    AS SourceDBInstanceName,
                TGT.DBINSTANCENAME    AS TargetDBInstanceName,
                DS.DATASETNAME        AS DatasetName,
                SJ.CONFLICTPOLICY     AS ConflictPolicy,
                SJ.DIRECTION          AS Direction,
                SJ.ISENABLED          AS IsEnabled,
                SJ.LASTRUNSTATUS      AS LastRunStatus,
                SJ.LASTRUNAT          AS LastRunAt,
                SJ.STATUS             AS Status,
                SJ.SORTORDER          AS SortOrder
            FROM  TDSYNCJOB SJ
            LEFT JOIN DBINSTANCE  SRC ON SJ.SOURCEDBINSTANCEID = SRC.DBINSTANCEID
            LEFT JOIN DBINSTANCE  TGT ON SJ.TARGETDBINSTANCEID = TGT.DBINSTANCEID
            LEFT JOIN MDATASET    DS  ON SJ.DATASETID           = DS.DATASETID
            WHERE SJ.STATUS != 2
            ORDER BY SJ.SORTORDER, SJ.SYNCJOBID
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_SYNCJOB_LIST_COUNT = @"
            SELECT COUNT(*) FROM TDSYNCJOB SJ WHERE SJ.STATUS != 2";

        public const string GET_SELECTLIST_SYNCJOB = @"
            WITH Paged AS (
                SELECT
                    SJ.SYNCJOBID   AS Id,
                    SJ.SYNCJOBID   AS Code,
                    SJ.DESCRIPTION AS Name,
                    ROW_NUMBER() OVER (ORDER BY SJ.SORTORDER, SJ.SYNCJOBID) AS RowNum
                FROM TDSYNCJOB SJ
                WHERE SJ.STATUS = 1 AND SJ.ISENABLED = 1
            )
            SELECT Id, Code, Name FROM Paged
            WHERE (@FirstNumber = -1 AND @MaxResult = -1)
               OR (RowNum BETWEEN @FirstNumber AND @MaxResult)";

        public const string SAVE_SYNCJOB = @"
            INSERT INTO TDSYNCJOB (
                SYNCJOBID, DESCRIPTION,
                SOURCEDBINSTANCEID, TARGETDBINSTANCEID,
                DATASETID, SCHEDULERID,
                CONFLICTPOLICY, DIRECTION, ISENABLED,
                QUERYCONDITION,
                LASTINSERTEDTILL, LASTUPDATEDTILL, LASTDELETETILL,
                SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            ) VALUES (
                @SyncJobId, @Description,
                @SourceDBInstanceId, @TargetDBInstanceId,
                @DatasetId, @SchedulerId,
                @ConflictPolicy, @Direction, @IsEnabled,
                @QueryCondition,
                @LastInsertedTill, @LastUpdatedTill, @LastDeleteTill,
                @SortOrder, @Status, @Version, @SourceType,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
            )";

        public const string UPDATE_SYNCJOB = @"
            UPDATE TDSYNCJOB SET
                DESCRIPTION        = @Description,
                SOURCEDBINSTANCEID = @SourceDBInstanceId,
                TARGETDBINSTANCEID = @TargetDBInstanceId,
                DATASETID          = @DatasetId,
                SCHEDULERID        = @SchedulerId,
                CONFLICTPOLICY     = @ConflictPolicy,
                DIRECTION          = @Direction,
                ISENABLED          = @IsEnabled,
                QUERYCONDITION     = @QueryCondition,
                SORTORDER          = @SortOrder,
                STATUS             = @Status,
                VERSION            = @Version,
                SOURCETYPE         = @SourceType,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = @ModifiedOn
            WHERE SYNCJOBID = @SyncJobId";

        public const string DELETE_SYNCJOB = @"
            DELETE FROM TDSYNCJOB WHERE SYNCJOBID = @SyncJobId";

        // Returns enabled, active jobs that have a scheduler configured.
        // Caller uses Cronos to evaluate CRONEXPRESSION against the current time.
        // Returns minimal fields needed for cron evaluation — never loads credentials here.
        public const string GET_ENABLED_JOBS_FOR_SCHEDULER = @"
            SELECT
                SJ.SYNCJOBID          AS SyncJobId,
                SJ.SCHEDULERID        AS SchedulerId,
                SCH.CRONEXPRESSION    AS SchedulerCronExpression,
                SJ.LASTRUNSTATUS      AS LastRunStatus,
                SJ.LASTRUNAT          AS LastRunAt
            FROM  TDSYNCJOB SJ
            JOIN  TSCHEDULER SCH ON SJ.SCHEDULERID = SCH.SCHEDULERID
            WHERE SJ.ISENABLED = 1
              AND SJ.STATUS    = 1";

        // Concurrency guard: sets LASTRUNSTATUS=1 (Running) only if the job is not already running.
        // Returns rows affected: 0 = already running (caller must skip), 1 = lock acquired.
        public const string LOCK_SYNCJOB_FOR_RUN = @"
            UPDATE TDSYNCJOB
            SET    LASTRUNSTATUS = 1
            WHERE  SYNCJOBID = @SyncJobId
              AND (LASTRUNSTATUS IS NULL OR LASTRUNSTATUS != 1)";

        public const string UPDATE_LASTRUN = @"
            UPDATE TDSYNCJOB SET
                LASTRUNID     = @LastRunId,
                LASTRUNSTATUS = @LastRunStatus,
                LASTRUNAT     = @LastRunAt,
                MODIFIEDBYID  = @ModifiedById,
                MODIFIEDON    = GETUTCDATE()
            WHERE SYNCJOBID = @SyncJobId";

        // Update watermarks only for tables that completed successfully;
        // failed tables are re-processed next run from the previous watermark.
        public const string UPDATE_WATERMARKS = @"
            UPDATE TDSYNCJOB SET
                LASTINSERTEDTILL = @LastInsertedTill,
                LASTUPDATEDTILL  = @LastUpdatedTill,
                LASTDELETETILL   = @LastDeleteTill,
                MODIFIEDBYID     = @ModifiedById,
                MODIFIEDON       = GETUTCDATE()
            WHERE SYNCJOBID = @SyncJobId";
    }
}