namespace KmsDAL.Query.KmArtifact
{
    public static class KmArtifactQB
    {
        // Index required: IX_MKMARTIFACT_TENANTID_DOMAINID, IX_MKMARTIFACT_GOVERNANCESTATE
        public const string GET_KMARTIFACT = @"
            SELECT  a.ARTIFACTID        AS ArtifactId,
                    a.DOMAINID          AS DomainId,
                    d.DOMAINNAME        AS DomainName,
                    d.SPACEID           AS SpaceId,
                    s.SPACENAME         AS SpaceName,
                    a.ARTIFACTCODE      AS ArtifactCode,
                    a.TITLE             AS Title,
                    a.SUMMARY           AS Summary,
                    a.ARTIFACTTYPE      AS ArtifactType,
                    a.SOURCEKIND        AS SourceKind,
                    a.GOVERNANCESTATE   AS GovernanceState,
                    a.SCOPELEVEL        AS ScopeLevel,
                    a.SCOPEVALUE        AS ScopeValue,
                    a.VERSIONNUMBER     AS VersionNumber,
                    a.PARENTARTIFACTID  AS ParentArtifactId,
                    a.OWNERUSERID       AS OwnerUserId,
                    a.REVIEWERUSERID    AS ReviewerUserId,
                    a.CONFIDENCESCORE   AS ConfidenceScore,
                    a.EFFECTIVEFROM     AS EffectiveFrom,
                    a.EFFECTIVETO       AS EffectiveTo,
                    a.SOURCEREFERENCE   AS SourceReference,
                    a.SOURCEMETADATA    AS SourceMetadata,
                    a.QDRANTPOINTID     AS QdrantPointId,
                    a.EMBEDDINGMODEL    AS EmbeddingModel,
                    a.LASTEMBEDDEDON    AS LastEmbeddedOn,
                    a.EXTRACTIONJOBID   AS ExtractionJobId,
                    a.TAGS              AS Tags,
                    a.STATUS            AS Status,
                    a.SORTORDER         AS SortOrder,
                    a.CREATEDBYID       AS CreatedById,
                    a.CREATEDON         AS CreatedOn,
                    a.MODIFIEDBYID      AS ModifiedById,
                    a.MODIFIEDON        AS ModifiedOn,
                    a.TENANTID          AS TenantId,
                    a.VERSION           AS Version
            FROM    KMS.MKMARTIFACT a
            JOIN    KMS.MKMDOMAIN   d ON d.DOMAINID = a.DOMAINID
            JOIN    KMS.MKMSPACE    s ON s.SPACEID  = d.SPACEID
            WHERE   a.ARTIFACTID = @ArtifactId
            AND     a.TENANTID   = @TenantId
            AND     a.STATUS     NOT IN (2)";

        public const string GET_KMARTIFACT_CONTENTS = @"
            SELECT  CONTENTID    AS ContentId,
                    ARTIFACTID   AS ArtifactId,
                    SLNO         AS Slno,
                    CONTENTBODY  AS ContentBody,
                    CONTENTTYPE  AS ContentType,
                    LANGUAGECODE AS LanguageCode,
                    ISPRIMARY    AS IsPrimary,
                    CHUNKINDEX   AS ChunkIndex,
                    CHUNKTOTAL   AS ChunkTotal,
                    CREATEDON    AS CreatedOn
            FROM    KMS.MKMARTIFACTCONTENT
            WHERE   ARTIFACTID = @ArtifactId
            ORDER BY SLNO";

        // Paged list — used by GetArtifacts endpoint
        public const string GET_KMARTIFACTS_PAGED = @"
            SELECT  a.ARTIFACTID        AS ArtifactId,
                    a.ARTIFACTCODE      AS ArtifactCode,
                    a.TITLE             AS Title,
                    a.ARTIFACTTYPE      AS ArtifactType,
                    a.GOVERNANCESTATE   AS GovernanceState,
                    a.SCOPELEVEL        AS ScopeLevel,
                    a.CONFIDENCESCORE   AS ConfidenceScore,
                    a.TAGS              AS Tags,
                    a.DOMAINID          AS DomainId,
                    d.DOMAINNAME        AS DomainName,
                    d.SPACEID           AS SpaceId,
                    s.SPACENAME         AS SpaceName,
                    a.VERSIONNUMBER     AS VersionNumber,
                    a.CREATEDON         AS CreatedOn,
                    a.MODIFIEDON        AS ModifiedOn
            FROM    KMS.MKMARTIFACT a
            JOIN    KMS.MKMDOMAIN   d ON d.DOMAINID = a.DOMAINID
            JOIN    KMS.MKMSPACE    s ON s.SPACEID  = d.SPACEID
            WHERE   a.TENANTID        = @TenantId
            AND     a.STATUS          NOT IN (2)
            AND     (@DomainId        = 0 OR a.DOMAINID = @DomainId)
            AND     (@GovernanceState = 255 OR a.GOVERNANCESTATE = @GovernanceState)
            AND     (@ArtifactType    = 255 OR a.ARTIFACTTYPE    = @ArtifactType)
            ORDER BY a.MODIFIEDON DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_KMARTIFACT_CODE_COUNT = @"
            SELECT COUNT(1)
            FROM   KMS.MKMARTIFACT
            WHERE  ARTIFACTCODE = @ArtifactCode
            AND    TENANTID     = @TenantId
            AND    ARTIFACTID  <> @ArtifactId";

        public const string SAVE_KMARTIFACT = @"
            INSERT INTO KMS.MKMARTIFACT
                (ARTIFACTID, DOMAINID, ARTIFACTCODE, TITLE, SUMMARY,
                 ARTIFACTTYPE, SOURCEKIND, GOVERNANCESTATE, SCOPELEVEL, SCOPEVALUE,
                 VERSIONNUMBER, PARENTARTIFACTID, OWNERUSERID, REVIEWERUSERID,
                 CONFIDENCESCORE, EFFECTIVEFROM, EFFECTIVETO,
                 SOURCEREFERENCE, SOURCEMETADATA, EXTRACTIONJOBID, TAGS,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@ArtifactId, @DomainId, @ArtifactCode, @Title, @Summary,
                 @ArtifactType, @SourceKind, 0, @ScopeLevel, @ScopeValue,
                 1, @ParentArtifactId, @OwnerUserId, @ReviewerUserId,
                 @ConfidenceScore, @EffectiveFrom, @EffectiveTo,
                 @SourceReference, @SourceMetadata, @ExtractionJobId, @Tags,
                 0, 1, @SortOrder, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string UPDATE_KMARTIFACT = @"
            UPDATE KMS.MKMARTIFACT
            SET    DOMAINID       = @DomainId,
                   TITLE          = @Title,
                   SUMMARY        = @Summary,
                   ARTIFACTTYPE   = @ArtifactType,
                   SCOPELEVEL     = @ScopeLevel,
                   SCOPEVALUE     = @ScopeValue,
                   OWNERUSERID    = @OwnerUserId,
                   REVIEWERUSERID = @ReviewerUserId,
                   EFFECTIVEFROM  = @EffectiveFrom,
                   EFFECTIVETO    = @EffectiveTo,
                   TAGS           = @Tags,
                   SORTORDER      = @SortOrder,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE(),
                   VERSION        = VERSION + 1
            WHERE  ARTIFACTID = @ArtifactId
            AND    TENANTID   = @TenantId";

        public const string UPDATE_KMARTIFACT_GOVERNANCE_STATE = @"
            UPDATE KMS.MKMARTIFACT
            SET    GOVERNANCESTATE = @GovernanceState,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE(),
                   VERSION        = VERSION + 1
            WHERE  ARTIFACTID = @ArtifactId
            AND    TENANTID   = @TenantId";

        public const string UPDATE_KMARTIFACT_QDRANT = @"
            UPDATE KMS.MKMARTIFACT
            SET    QDRANTPOINTID  = @QdrantPointId,
                   EMBEDDINGMODEL = @EmbeddingModel,
                   LASTEMBEDDEDON = GETDATE()
            WHERE  ARTIFACTID = @ArtifactId
            AND    TENANTID   = @TenantId";

        public const string DELETE_KMARTIFACT = @"
            UPDATE KMS.MKMARTIFACT
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  ARTIFACTID = @ArtifactId
            AND    TENANTID   = @TenantId
            AND    GOVERNANCESTATE IN (0, 4)";
    }
}