namespace EAIAdminDAL.Query
{
    // Cross-schema joins are fine — EAI, KMS, and dbo (MEMPLOYEE) all live in the same physical
    // tenant database, just different schemas.
    public static class EAIBotKnowledgeSourceQB
    {
        public const string GET_LIST_BY_BOT = @"
            SELECT
                L.EAIBOTKNOWLEDGESOURCEID AS EAIBotKnowledgeSourceId,
                L.EAIBOTID                AS EAIBotId,
                L.EAICAPABILITYID         AS EAICapabilityId,
                L.KMSPACEID               AS KmSpaceId,
                L.KMDOMAINID              AS KmDomainId,
                L.TENANTID                AS TenantId,
                L.VERSION                 AS Version,
                L.STATUS                  AS Status,
                L.CREATEDBYID             AS CreatedById,
                L.CREATEDON               AS CreatedOn,
                L.MODIFIEDBYID            AS ModifiedById,
                L.MODIFIEDON              AS ModifiedOn,
                SP.SPACENAME              AS SpaceName,
                DM.DOMAINNAME             AS DomainName
            FROM EAI.MEAIBOTKNOWLEDGESOURCE L
            LEFT JOIN KMS.MKMSPACE  SP ON L.KMSPACEID  = SP.SPACEID
            LEFT JOIN KMS.MKMDOMAIN DM ON L.KMDOMAINID = DM.DOMAINID
            WHERE L.EAIBOTID = @EAIBotId
              AND L.TENANTID = @TenantId
              AND L.STATUS   = 1
            ORDER BY L.EAIBOTKNOWLEDGESOURCEID ASC";

        public const string GET_LIST_BY_CAPABILITY = @"
            SELECT
                L.EAIBOTKNOWLEDGESOURCEID AS EAIBotKnowledgeSourceId,
                L.EAIBOTID                AS EAIBotId,
                L.EAICAPABILITYID         AS EAICapabilityId,
                L.KMSPACEID               AS KmSpaceId,
                L.KMDOMAINID              AS KmDomainId,
                L.TENANTID                AS TenantId,
                L.VERSION                 AS Version,
                L.STATUS                  AS Status,
                L.CREATEDBYID             AS CreatedById,
                L.CREATEDON               AS CreatedOn,
                L.MODIFIEDBYID            AS ModifiedById,
                L.MODIFIEDON              AS ModifiedOn,
                SP.SPACENAME              AS SpaceName,
                DM.DOMAINNAME             AS DomainName
            FROM EAI.MEAIBOTKNOWLEDGESOURCE L
            LEFT JOIN KMS.MKMSPACE  SP ON L.KMSPACEID  = SP.SPACEID
            LEFT JOIN KMS.MKMDOMAIN DM ON L.KMDOMAINID = DM.DOMAINID
            WHERE L.EAICAPABILITYID = @EAICapabilityId
              AND L.TENANTID        = @TenantId
              AND L.STATUS          = 1
            ORDER BY L.EAIBOTKNOWLEDGESOURCEID ASC";

        // EAIBOTKNOWLEDGESOURCEID is a plain INT PK (NOT IDENTITY) — generated via AutoNumber.
        public const string INSERT_LINK = @"
            INSERT INTO EAI.MEAIBOTKNOWLEDGESOURCE (
                EAIBOTKNOWLEDGESOURCEID, EAIBOTID, EAICAPABILITYID, KMSPACEID, KMDOMAINID,
                TENANTID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES (
                @EAIBotKnowledgeSourceId, @EAIBotId, @EAICapabilityId, @KmSpaceId, @KmDomainId,
                @TenantId, @Version, 1, @CreatedById, @CreatedOn, @CreatedById, @CreatedOn
            )";

        public const string DELETE_LINK = @"
            UPDATE EAI.MEAIBOTKNOWLEDGESOURCE
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE EAIBOTKNOWLEDGESOURCEID = @EAIBotKnowledgeSourceId
              AND TENANTID                = @TenantId
              AND STATUS                  = 1";
    }
}