namespace KmsDAL.Query.KmDiscovery
{
    public static class KmDiscoveryQB
    {
        // ── Extraction Job ────────────────────────────────────────────────────
        public const string SAVE_KMJOB = @"
            INSERT INTO KMS.TKMJOB
                (JOBID, CONNECTORID, SOURCEREFERENCE, CONTENTHASH, JOBSTATUS,
                 CHUNKSTOTAL, CHUNKSPROCESSED, CANDIDATESCREATED,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@JobId, @ConnectorId, @SourceReference, @ContentHash, 0,
                 0, 0, 0,
                 0, 1, 9999, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string UPDATE_KMJOB_STATUS = @"
            UPDATE KMS.TKMJOB
            SET    JOBSTATUS       = @JobStatus,
                   CHUNKSTOTAL     = @ChunksTotal,
                   CHUNKSPROCESSED = @ChunksProcessed,
                   CANDIDATESCREATED = @CandidatesCreated,
                   CELERYTASKID    = @CeleryTaskId,
                   STARTEDON       = ISNULL(STARTEDON, CASE WHEN @JobStatus > 0 THEN GETDATE() ELSE NULL END),
                   COMPLETEDON     = CASE WHEN @JobStatus IN (5,6) THEN GETDATE() ELSE NULL END,
                   ERRORMESSAGE    = @ErrorMessage,
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = GETDATE()
            WHERE  JOBID     = @JobId
            AND    TENANTID  = @TenantId";

        public const string GET_KMJOB = @"
            SELECT  JOBID             AS JobId,
                    CONNECTORID       AS ConnectorId,
                    SOURCEREFERENCE   AS SourceReference,
                    CONTENTHASH       AS ContentHash,
                    JOBSTATUS         AS JobStatus,
                    ERRORMESSAGE      AS ErrorMessage,
                    CHUNKSTOTAL       AS ChunksTotal,
                    CHUNKSPROCESSED   AS ChunksProcessed,
                    CANDIDATESCREATED AS CandidatesCreated,
                    CELERYTASKID      AS CeleryTaskId,
                    STARTEDON         AS StartedOn,
                    COMPLETEDON       AS CompletedOn,
                    TENANTID          AS TenantId,
                    CREATEDBYID       AS CreatedById,
                    CREATEDON         AS CreatedOn
            FROM    KMS.TKMJOB
            WHERE   JOBID    = @JobId
            AND     TENANTID = @TenantId";

        public const string GET_KMJOBS_PAGED = @"
            SELECT  JOBID, CONNECTORID, SOURCEREFERENCE, JOBSTATUS,
                    CHUNKSTOTAL, CANDIDATESCREATED, CREATEDON, COMPLETEDON
            FROM    KMS.TKMJOB
            WHERE   TENANTID = @TenantId
            AND     STATUS   NOT IN (2)
            ORDER BY CREATEDON DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // ── Candidate ─────────────────────────────────────────────────────────
        public const string SAVE_KMCANDIDATE = @"
            INSERT INTO KMS.TKMCANDIDATE
                (CANDIDATEID, JOBID, DOMAINID, PROPOSEDTITLE, PROPOSEDTYPE,
                 PROPOSEDCONTENT, PROPOSEDTAGS, PROPOSEDSCOPELEVEL, CONFIDENCESCORE,
                 SOURCECHUNKINDEX, SOURCECHUNKTEXT, LLMMODEL, REVIEWSTATUS,
                 REVIEWEDUSERID, ARTIFACTID,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON,
                 MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@CandidateId, @JobId, @DomainId, @ProposedTitle, @ProposedType,
                 @ProposedContent, @ProposedTags, @ProposedScopeLevel, @ConfidenceScore,
                 @SourceChunkIndex, @SourceChunkText, @LlmModel, 0,
                 -1, -1,
                 0, 1, 9999, @CreatedById, GETDATE(),
                 @ModifiedById, GETDATE(), 5, @TenantId)";

        public const string GET_KMCANDIDATES_PENDING = @"
            SELECT  c.CANDIDATEID       AS CandidateId,
                    c.JOBID             AS JobId,
                    c.DOMAINID          AS DomainId,
                    d.DOMAINNAME        AS DomainName,
                    c.PROPOSEDTITLE     AS ProposedTitle,
                    c.PROPOSEDTYPE      AS ProposedType,
                    c.PROPOSEDCONTENT   AS ProposedContent,
                    c.PROPOSEDTAGS      AS ProposedTags,
                    c.PROPOSEDSCOPELEVEL AS ProposedScopeLevel,
                    c.CONFIDENCESCORE   AS ConfidenceScore,
                    c.SOURCECHUNKINDEX  AS SourceChunkIndex,
                    c.SOURCECHUNKTEXT   AS SourceChunkText,
                    c.LLMMODEL          AS LlmModel,
                    c.REVIEWSTATUS      AS ReviewStatus,
                    c.TENANTID          AS TenantId,
                    c.CREATEDON         AS CreatedOn
            FROM    KMS.TKMCANDIDATE c
            LEFT JOIN KMS.MKMDOMAIN d ON d.DOMAINID = c.DOMAINID
            WHERE   c.TENANTID     = @TenantId
            AND     c.REVIEWSTATUS = 0
            AND     c.STATUS       NOT IN (2)
            ORDER BY c.CONFIDENCESCORE DESC";

        public const string UPDATE_KMCANDIDATE_ACCEPT = @"
            UPDATE KMS.TKMCANDIDATE
            SET    DOMAINID       = @DomainId,
                   PROPOSEDTITLE  = ISNULL(@ModifiedTitle, PROPOSEDTITLE),
                   PROPOSEDCONTENT = ISNULL(@ModifiedContent, PROPOSEDCONTENT),
                   PROPOSEDTAGS   = ISNULL(@ModifiedTags, PROPOSEDTAGS),
                   REVIEWSTATUS   = 1,
                   REVIEWEDUSERID = @ReviewedUserId,
                   REVIEWEDON     = GETDATE(),
                   REVIEWNOTES    = @ReviewNotes,
                   ARTIFACTID     = @ArtifactId,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE()
            WHERE  CANDIDATEID = @CandidateId
            AND    TENANTID    = @TenantId
            AND    REVIEWSTATUS = 0";

        public const string UPDATE_KMCANDIDATE_REJECT = @"
            UPDATE KMS.TKMCANDIDATE
            SET    REVIEWSTATUS   = 2,
                   REVIEWEDUSERID = @ReviewedUserId,
                   REVIEWEDON     = GETDATE(),
                   REVIEWNOTES    = @RejectionReason,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE()
            WHERE  CANDIDATEID = @CandidateId
            AND    TENANTID    = @TenantId
            AND    REVIEWSTATUS = 0";
    }
}