namespace FrameworkDAL.Query.CodeDefine
{
    public static class CodeDefineSegmentQB
    {
        // Required index: IX_MCODEDEFINESEGMENT_CODEDEFINEID ON MCODEDEFINESEGMENT (CODEDEFINEID, SLNO)
        public const string GET_SEGMENTS_BY_CODEDEFINE_ID = @"
            SELECT
                S.SEGMENTID         AS CodeDefineSegmentId,
                S.CODEDEFINEID      AS CodeDefineId,
                S.SLNO              AS CodeDefineSegmentSlNo,
                S.SEGMENTTYPE       AS CodeDefineSegmentType,
                S.SOURCEFIELD       AS CodeDefineSegmentSourceField,
                S.LOOKUPTABLE       AS CodeDefineSegmentLookupTable,
                S.PREFIX            AS CodeDefineSegmentPrefix,
                S.SUFFIX            AS CodeDefineSegmentSuffix,
                S.ISMANDATORY       AS CodeDefineSegmentIsMandatory,
                S.DEFAULTVALUE      AS CodeDefineSegmentDefaultValue,
                S.FORMAT            AS CodeDefineSegmentFormat,
                S.SEPARATOR         AS CodeDefineSegmentSeparator,
                S.STATUS            AS CodeDefineSegmentStatus,
                S.CREATEDBYID       AS CodeDefineSegmentCreatedById,
                S.CREATEDON         AS CodeDefineSegmentCreatedOn,
                S.MODIFIEDBYID      AS CodeDefineSegmentModifiedById,
                S.MODIFIEDON        AS CodeDefineSegmentModifiedOn
            FROM MCODEDEFINESEGMENT S
            WHERE S.CODEDEFINEID = @codedefineId
              AND S.STATUS = 1
            ORDER BY S.SLNO;";

        public const string GET_SEGMENT_BY_ID = @"
            SELECT
                S.SEGMENTID         AS CodeDefineSegmentId,
                S.CODEDEFINEID      AS CodeDefineId,
                S.SLNO              AS CodeDefineSegmentSlNo,
                S.SEGMENTTYPE       AS CodeDefineSegmentType,
                S.SOURCEFIELD       AS CodeDefineSegmentSourceField,
                S.LOOKUPTABLE       AS CodeDefineSegmentLookupTable,
                S.PREFIX            AS CodeDefineSegmentPrefix,
                S.SUFFIX            AS CodeDefineSegmentSuffix,
                S.ISMANDATORY       AS CodeDefineSegmentIsMandatory,
                S.DEFAULTVALUE      AS CodeDefineSegmentDefaultValue,
                S.FORMAT            AS CodeDefineSegmentFormat,
                S.SEPARATOR         AS CodeDefineSegmentSeparator,
                S.STATUS            AS CodeDefineSegmentStatus,
                S.CREATEDBYID       AS CodeDefineSegmentCreatedById,
                S.CREATEDON         AS CodeDefineSegmentCreatedOn,
                S.MODIFIEDBYID      AS CodeDefineSegmentModifiedById,
                S.MODIFIEDON        AS CodeDefineSegmentModifiedOn
            FROM MCODEDEFINESEGMENT S
            WHERE S.SEGMENTID = @segmentId;";

        public const string SAVE_CODEDEFINESEGMENT = @"
            INSERT INTO MCODEDEFINESEGMENT
            (
                SEGMENTID, CODEDEFINEID, SLNO, SEGMENTTYPE, SOURCEFIELD,
                LOOKUPTABLE, PREFIX, SUFFIX, ISMANDATORY, DEFAULTVALUE,
                FORMAT, SEPARATOR, STATUS, CREATEDBYID, CREATEDON,
                MODIFIEDBYID, MODIFIEDON
            )
            VALUES
            (
                @CodeDefineSegmentId, @CodeDefineId, @CodeDefineSegmentSlNo, @CodeDefineSegmentType, @CodeDefineSegmentSourceField,
                @CodeDefineSegmentLookupTable, @CodeDefineSegmentPrefix, @CodeDefineSegmentSuffix, @CodeDefineSegmentIsMandatory, @CodeDefineSegmentDefaultValue,
                @CodeDefineSegmentFormat, @CodeDefineSegmentSeparator, @CodeDefineSegmentStatus, @CodeDefineSegmentCreatedById, @CodeDefineSegmentCreatedOn,
                @CodeDefineSegmentModifiedById, @CodeDefineSegmentModifiedOn
            );";

        public const string UPDATE_CODEDEFINESEGMENT = @"
            UPDATE MCODEDEFINESEGMENT
            SET
                CODEDEFINEID    = @CodeDefineId,
                SLNO            = @CodeDefineSegmentSlNo,
                SEGMENTTYPE     = @CodeDefineSegmentType,
                SOURCEFIELD     = @CodeDefineSegmentSourceField,
                LOOKUPTABLE     = @CodeDefineSegmentLookupTable,
                PREFIX          = @CodeDefineSegmentPrefix,
                SUFFIX          = @CodeDefineSegmentSuffix,
                ISMANDATORY     = @CodeDefineSegmentIsMandatory,
                DEFAULTVALUE    = @CodeDefineSegmentDefaultValue,
                FORMAT          = @CodeDefineSegmentFormat,
                SEPARATOR       = @CodeDefineSegmentSeparator,
                STATUS          = @CodeDefineSegmentStatus,
                MODIFIEDBYID    = @CodeDefineSegmentModifiedById,
                MODIFIEDON      = @CodeDefineSegmentModifiedOn
            WHERE SEGMENTID = @CodeDefineSegmentId;";

        public const string DELETE_CODEDEFINESEGMENT = @"
            DELETE FROM MCODEDEFINESEGMENT
            WHERE SEGMENTID = @segmentId;";

        public const string DELETE_SEGMENTS_BY_CODEDEFINE_ID = @"
            DELETE FROM MCODEDEFINESEGMENT
            WHERE CODEDEFINEID = @codedefineId;";

        // Required index: same IX_MCODEDEFINESEGMENT_CODEDEFINEID covers this
        public const string GET_SELECTLIST_CODEDEFINESEGMENT = @"
            WITH Segments AS (
                SELECT
                    S.SEGMENTID     AS Id,
                    S.CODEDEFINEID  AS CodeDefineId,
                    S.SLNO          AS SlNo,
                    S.SEGMENTTYPE   AS SegmentType,
                    S.SOURCEFIELD   AS SourceField,
                    S.SEPARATOR     AS Separator,
                    ROW_NUMBER() OVER (ORDER BY S.CODEDEFINEID, S.SLNO) AS RowNum
                FROM MCODEDEFINESEGMENT S
                WHERE S.STATUS = 1
                  AND (@codedefineId = -1 OR S.CODEDEFINEID = @codedefineId)
            )
            SELECT Id, CodeDefineId, SlNo, SegmentType, SourceField, Separator
            FROM Segments
            WHERE
                (@firstNumber = -1 AND @maxResult = -1)
                OR (RowNum BETWEEN @firstNumber AND @maxResult);";
    }
}