namespace KmsDAL.Query.KmSpace
{
    public static class KmSpaceQB
    {
        // Index required: PK_MKMSPACE_SPACEID, UK_MKMSPACE_SPACECODE_TENANTID
        public const string GET_KMSPACE = @"
            SELECT  s.SPACEID         AS SpaceId,
                    s.SPACECODE       AS SpaceCode,
                    s.SPACENAME       AS SpaceName,
                    s.DESCRIPTION     AS Description,
                    s.STATUS          AS Status,
                    s.SORTORDER       AS SortOrder,
                    s.CREATEDBYID     AS CreatedById,
                    s.CREATEDON       AS CreatedOn,
                    s.MODIFIEDBYID    AS ModifiedById,
                    s.MODIFIEDON      AS ModifiedOn,
                    s.TENANTID        AS TenantId,
                    s.VERSION         AS Version
            FROM    KMS.MKMSPACE s
            WHERE   s.SPACEID  = @SpaceId
            AND     s.TENANTID = @TenantId
            AND     s.STATUS   NOT IN (2)";

        // Index required: TENANTID, STATUS
        public const string GET_SELECTLIST_KMSPACE = @"
            SELECT  SPACEID    AS SpaceId,
                    SPACECODE  AS SpaceCode,
                    SPACENAME  AS SpaceName
            FROM    KMS.MKMSPACE
            WHERE   TENANTID = @TenantId
            AND     STATUS   = 1
            ORDER BY SPACENAME";

        public const string GET_KMSPACE_COUNT = @"
            SELECT COUNT(1)
            FROM   KMS.MKMSPACE
            WHERE  SPACEID  = @SpaceId
            AND    TENANTID = @TenantId";

        public const string GET_KMSPACE_CODE_COUNT = @"
            SELECT COUNT(1)
            FROM   KMS.MKMSPACE
            WHERE  SPACECODE = @SpaceCode
            AND    TENANTID  = @TenantId
            AND    SPACEID  <> @SpaceId";

        public const string SAVE_KMSPACE = @"
            INSERT INTO KMS.MKMSPACE
                (SPACEID, SPACECODE, SPACENAME, DESCRIPTION,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@SpaceId, @SpaceCode, @SpaceName, @Description,
                 0, 1, @SortOrder, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string UPDATE_KMSPACE = @"
            UPDATE KMS.MKMSPACE
            SET    SPACECODE    = @SpaceCode,
                   SPACENAME    = @SpaceName,
                   DESCRIPTION  = @Description,
                   SORTORDER    = @SortOrder,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE(),
                   VERSION      = VERSION + 1
            WHERE  SPACEID  = @SpaceId
            AND    TENANTID = @TenantId";

        public const string DELETE_KMSPACE = @"
            UPDATE KMS.MKMSPACE
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  SPACEID  = @SpaceId
            AND    TENANTID = @TenantId";
    }
}