namespace KmsDAL.Query.KmDomain
{
    public static class KmDomainQB
    {
        // Index required: FK_MKMDOMAIN_SPACEID, UK_MKMDOMAIN_DOMAINCODE_TENANTID
        public const string GET_KMDOMAIN = @"
            SELECT  d.DOMAINID     AS DomainId,
                    d.SPACEID      AS SpaceId,
                    s.SPACECODE    AS SpaceCode,
                    s.SPACENAME    AS SpaceName,
                    d.DOMAINCODE   AS DomainCode,
                    d.DOMAINNAME   AS DomainName,
                    d.DESCRIPTION  AS Description,
                    d.STATUS       AS Status,
                    d.SORTORDER    AS SortOrder,
                    d.CREATEDBYID  AS CreatedById,
                    d.CREATEDON    AS CreatedOn,
                    d.MODIFIEDBYID AS ModifiedById,
                    d.MODIFIEDON   AS ModifiedOn,
                    d.TENANTID     AS TenantId,
                    d.VERSION      AS Version
            FROM    KMS.MKMDOMAIN d
            JOIN    KMS.MKMSPACE  s ON s.SPACEID = d.SPACEID
            WHERE   d.DOMAINID = @DomainId
            AND     d.TENANTID = @TenantId
            AND     d.STATUS   NOT IN (2)";

        public const string GET_SELECTLIST_KMDOMAIN = @"
            SELECT  d.DOMAINID   AS DomainId,
                    d.DOMAINCODE AS DomainCode,
                    d.DOMAINNAME AS DomainName,
                    d.SPACEID    AS SpaceId,
                    s.SPACENAME  AS SpaceName
            FROM    KMS.MKMDOMAIN d
            JOIN    KMS.MKMSPACE  s ON s.SPACEID = d.SPACEID
            WHERE   d.TENANTID = @TenantId
            AND     d.STATUS   = 1
            ORDER BY d.DOMAINNAME";

        public const string GET_SELECTLIST_KMDOMAIN_BY_SPACE = @"
            SELECT  DOMAINID   AS DomainId,
                    DOMAINCODE AS DomainCode,
                    DOMAINNAME AS DomainName,
                    SPACEID    AS SpaceId
            FROM    KMS.MKMDOMAIN
            WHERE   SPACEID  = @SpaceId
            AND     TENANTID = @TenantId
            AND     STATUS   = 1
            ORDER BY DOMAINNAME";

        public const string GET_KMDOMAIN_CODE_COUNT = @"
            SELECT COUNT(1)
            FROM   KMS.MKMDOMAIN
            WHERE  DOMAINCODE = @DomainCode
            AND    TENANTID   = @TenantId
            AND    DOMAINID  <> @DomainId";

        public const string SAVE_KMDOMAIN = @"
            INSERT INTO KMS.MKMDOMAIN
                (DOMAINID, SPACEID, DOMAINCODE, DOMAINNAME, DESCRIPTION,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@DomainId, @SpaceId, @DomainCode, @DomainName, @Description,
                 0, 1, @SortOrder, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string UPDATE_KMDOMAIN = @"
            UPDATE KMS.MKMDOMAIN
            SET    SPACEID      = @SpaceId,
                   DOMAINCODE   = @DomainCode,
                   DOMAINNAME   = @DomainName,
                   DESCRIPTION  = @Description,
                   SORTORDER    = @SortOrder,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE(),
                   VERSION      = VERSION + 1
            WHERE  DOMAINID = @DomainId
            AND    TENANTID = @TenantId";

        public const string DELETE_KMDOMAIN = @"
            UPDATE KMS.MKMDOMAIN
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  DOMAINID = @DomainId
            AND    TENANTID = @TenantId";
    }
}