namespace KmsDAL.Query.KmConnector
{
    public static class KmConnectorQB
    {
        // Index required: PK_MKMCONNECTOR_CONNECTORID, UK_MKMCONNECTOR_CONNECTORCODE_TENANTID,
        // FK_MKMCONNECTOR_SPACEID, FK_MKMCONNECTOR_DOMAINID
        public const string GET_KMCONNECTOR = @"
            SELECT  c.CONNECTORID     AS ConnectorId,
                    c.CONNECTORCODE   AS ConnectorCode,
                    c.CONNECTORNAME   AS ConnectorName,
                    c.CONNECTORTYPE   AS ConnectorType,
                    c.SPACEID         AS SpaceId,
                    s.SPACENAME       AS SpaceName,
                    c.DOMAINID        AS DomainId,
                    d.DOMAINNAME      AS DomainName,
                    c.CONFIGJSON      AS ConfigJson,
                    c.SCHEDULECRON    AS ScheduleCron,
                    c.LASTRUNON       AS LastRunOn,
                    c.LASTRUNSTATUS   AS LastRunStatus,
                    c.STATUS          AS Status,
                    c.SORTORDER       AS SortOrder,
                    c.CREATEDBYID     AS CreatedById,
                    c.CREATEDON       AS CreatedOn,
                    c.MODIFIEDBYID    AS ModifiedById,
                    c.MODIFIEDON      AS ModifiedOn,
                    c.TENANTID        AS TenantId,
                    c.VERSION         AS Version
            FROM    KMS.MKMCONNECTOR c
            LEFT JOIN KMS.MKMSPACE   s ON s.SPACEID  = c.SPACEID
            LEFT JOIN KMS.MKMDOMAIN  d ON d.DOMAINID = c.DOMAINID
            WHERE   c.CONNECTORID = @ConnectorId
            AND     c.TENANTID    = @TenantId
            AND     c.STATUS      NOT IN (2)";

        // Index required: TENANTID, STATUS
        public const string GET_KMCONNECTORS = @"
            SELECT  c.CONNECTORID     AS ConnectorId,
                    c.CONNECTORCODE   AS ConnectorCode,
                    c.CONNECTORNAME   AS ConnectorName,
                    c.CONNECTORTYPE   AS ConnectorType,
                    c.SPACEID         AS SpaceId,
                    s.SPACENAME       AS SpaceName,
                    c.DOMAINID        AS DomainId,
                    d.DOMAINNAME      AS DomainName,
                    c.SCHEDULECRON    AS ScheduleCron,
                    c.LASTRUNON       AS LastRunOn,
                    c.LASTRUNSTATUS   AS LastRunStatus,
                    c.STATUS          AS Status,
                    c.SORTORDER       AS SortOrder,
                    c.TENANTID        AS TenantId,
                    c.CREATEDON       AS CreatedOn
            FROM    KMS.MKMCONNECTOR c
            LEFT JOIN KMS.MKMSPACE   s ON s.SPACEID  = c.SPACEID
            LEFT JOIN KMS.MKMDOMAIN  d ON d.DOMAINID = c.DOMAINID
            WHERE   c.TENANTID = @TenantId
            AND     c.STATUS   NOT IN (2)
            ORDER BY c.CONNECTORNAME
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_SELECTLIST_KMCONNECTOR = @"
            SELECT  CONNECTORID   AS ConnectorId,
                    CONNECTORCODE AS ConnectorCode,
                    CONNECTORNAME AS ConnectorName
            FROM    KMS.MKMCONNECTOR
            WHERE   TENANTID = @TenantId
            AND     STATUS   = 1
            ORDER BY CONNECTORNAME";

        public const string GET_KMCONNECTOR_CODE_COUNT = @"
            SELECT COUNT(1)
            FROM   KMS.MKMCONNECTOR
            WHERE  CONNECTORCODE = @ConnectorCode
            AND    TENANTID      = @TenantId
            AND    CONNECTORID  <> @ConnectorId";

        public const string SAVE_KMCONNECTOR = @"
            INSERT INTO KMS.MKMCONNECTOR
                (CONNECTORID, CONNECTORCODE, CONNECTORNAME, CONNECTORTYPE, SPACEID, DOMAINID,
                 CONFIGJSON, SCHEDULECRON, LASTRUNSTATUS,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@ConnectorId, @ConnectorCode, @ConnectorName, @ConnectorType, @SpaceId, @DomainId,
                 @ConfigJson, @ScheduleCron, 0,
                 0, 1, @SortOrder, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string UPDATE_KMCONNECTOR = @"
            UPDATE KMS.MKMCONNECTOR
            SET    CONNECTORCODE = @ConnectorCode,
                   CONNECTORNAME = @ConnectorName,
                   CONNECTORTYPE = @ConnectorType,
                   SPACEID       = @SpaceId,
                   DOMAINID      = @DomainId,
                   CONFIGJSON    = @ConfigJson,
                   SCHEDULECRON  = @ScheduleCron,
                   SORTORDER     = @SortOrder,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  CONNECTORID = @ConnectorId
            AND    TENANTID    = @TenantId";

        public const string DELETE_KMCONNECTOR = @"
            UPDATE KMS.MKMCONNECTOR
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  CONNECTORID = @ConnectorId
            AND    TENANTID    = @TenantId";
    }
}