namespace AMPDAL.Query.ApiRequestDuplicateCheck
{
    public static class ApiRequestDuplicateCheckQB
    {
        // Required indexes:
        // IX_MAPIREQUESTDUPLICATECHECK_SOURCE_REVIEW on (SOURCEAPIREQUESTID, REVIEWSTATUS)
        // IX_MAPIREQUESTDUPLICATECHECK_MATCHEDAPI on (MATCHEDAPIID)
        // IX_MAPIREQUESTDUPLICATECHECK_TENANT_REVIEW on (TENANTID, REVIEWSTATUS)

        // =========================================================
        // GET ALL CHECKS FOR A REQUEST
        // =========================================================
        public const string GET_BY_REQUEST = @"
SELECT DUPLICATECHECKID    AS DuplicateCheckId,
       TENANTID            AS TenantId,
       SOURCEAPIREQUESTID  AS SourceApiRequestId,
       MATCHSOURCE         AS MatchSource,
       MATCHEDAPIID        AS MatchedApiId,
       MATCHEDREQUESTID    AS MatchedRequestId,
       MATCHEDINTERFACEID  AS MatchedInterfaceId,
       OVERALLSCORE        AS OverallScore,
       NAMESCORE           AS NameScore,
       DOMAINSCORE         AS DomainScore,
       INTENTSCORE         AS IntentScore,
       SCHEMASCORE         AS SchemaScore,
       SUGGESTIONTYPE      AS SuggestionType,
       SUGGESTEDCHANGE     AS SuggestedChange,
       MATCHREASON         AS MatchReason,
       REVIEWSTATUS        AS ReviewStatus,
       REVIEWEDBYID        AS ReviewedById,
       REVIEWEDON          AS ReviewedOn,
       REVIEWREMARKS       AS ReviewRemarks,
       CREATEDON           AS CreatedOn
FROM   MAPIREQUESTDUPLICATECHECK
WHERE  SOURCEAPIREQUESTID = @ApiRequestId
AND    TENANTID           = @TenantId
ORDER  BY OVERALLSCORE DESC";

        // =========================================================
        // GET PENDING REVIEWS (tenant-wide governance dashboard)
        // =========================================================
        public const string GET_PENDING = @"
SELECT dc.DUPLICATECHECKID    AS DuplicateCheckId,
       dc.TENANTID            AS TenantId,
       dc.SOURCEAPIREQUESTID  AS SourceApiRequestId,
       dc.MATCHSOURCE         AS MatchSource,
       dc.MATCHEDAPIID        AS MatchedApiId,
       dc.MATCHEDREQUESTID    AS MatchedRequestId,
       dc.MATCHEDINTERFACEID  AS MatchedInterfaceId,
       dc.OVERALLSCORE        AS OverallScore,
       dc.NAMESCORE           AS NameScore,
       dc.DOMAINSCORE         AS DomainScore,
       dc.INTENTSCORE         AS IntentScore,
       dc.SCHEMASCORE         AS SchemaScore,
       dc.SUGGESTIONTYPE      AS SuggestionType,
       dc.SUGGESTEDCHANGE     AS SuggestedChange,
       dc.MATCHREASON         AS MatchReason,
       dc.REVIEWSTATUS        AS ReviewStatus,
       dc.REVIEWEDBYID        AS ReviewedById,
       dc.REVIEWEDON          AS ReviewedOn,
       dc.REVIEWREMARKS       AS ReviewRemarks,
       dc.CREATEDON           AS CreatedOn
FROM   MAPIREQUESTDUPLICATECHECK dc
WHERE  dc.TENANTID     = @TenantId
AND    dc.REVIEWSTATUS = 0
ORDER  BY dc.OVERALLSCORE DESC, dc.CREATEDON DESC";

        // =========================================================
        // SAVE DUPLICATE CHECK ROW
        // =========================================================
        public const string SAVE_DUPLICATE_CHECK = @"
INSERT INTO MAPIREQUESTDUPLICATECHECK (
    DUPLICATECHECKID, TENANTID, SOURCEAPIREQUESTID,
    MATCHSOURCE, MATCHEDAPIID, MATCHEDREQUESTID, MATCHEDINTERFACEID,
    OVERALLSCORE, NAMESCORE, DOMAINSCORE, INTENTSCORE, SCHEMASCORE,
    SUGGESTIONTYPE, SUGGESTEDCHANGE, MATCHREASON,
    REVIEWSTATUS, CREATEDON
) VALUES (
    @DuplicateCheckId, @TenantId, @SourceApiRequestId,
    @MatchSource, @MatchedApiId, @MatchedRequestId, @MatchedInterfaceId,
    @OverallScore, @NameScore, @DomainScore, @IntentScore, @SchemaScore,
    @SuggestionType, @SuggestedChange, @MatchReason,
    0, GETDATE()
)";

        // =========================================================
        // UPDATE REVIEW STATUS
        // =========================================================
        public const string UPDATE_REVIEW_STATUS = @"
UPDATE MAPIREQUESTDUPLICATECHECK SET
    REVIEWSTATUS  = @ReviewStatus,
    REVIEWEDBYID  = @ReviewedById,
    REVIEWEDON    = GETDATE(),
    REVIEWREMARKS = @ReviewRemarks
WHERE DUPLICATECHECKID = @DuplicateCheckId
AND   TENANTID         = @TenantId";

        // =========================================================
        // FIND SIMILARITY AGAINST LIVE APIs (MAPI)
        // Scoring: Name=15, Domain=20, ApiType=10, Keywords=35(7×5), Operations=20
        // Threshold applied in BLL: only store rows where OverallScore >= 45
        // =========================================================
        public const string FIND_SIMILARITY_API = @"
;WITH Scored AS (
    SELECT
        m.APIID            AS MatchedId,
        m.APINAME          AS MatchedName,
        m.APIDOMAINID      AS MatchedApiDomainId,
        m.APITYPE          AS MatchedApiType,
        CAST(
            -- Name similarity (15 pts max)
            CASE WHEN UPPER(m.APINAME) = UPPER(@Title) THEN 15
                 WHEN DIFFERENCE(m.APINAME, @Title) >= 3 THEN 10
                 WHEN DIFFERENCE(m.APINAME, @Title) = 2 THEN 5
                 ELSE 0 END
            -- Same domain (20 pts)
          + CASE WHEN m.APIDOMAINID = @TargetDomainId AND @TargetDomainId <> -1 THEN 20 ELSE 0 END
            -- Same API type (10 pts)
          + CASE WHEN m.APITYPE = @ProposedApiType THEN 10 ELSE 0 END
            -- Keyword overlap in KEYFEATURES + USECASES + ABOUTAPI (7 pts each, up to 35)
          + CASE WHEN @KW1 <> '' AND CHARINDEX(@KW1, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW2 <> '' AND CHARINDEX(@KW2, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW3 <> '' AND CHARINDEX(@KW3, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW4 <> '' AND CHARINDEX(@KW4, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW5 <> '' AND CHARINDEX(@KW5, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
            -- Operation/path overlap (20 pts)
          + CASE WHEN EXISTS (
                SELECT 1 FROM MAPIOPERATION op
                INNER JOIN MAPIINTERFACE mi ON op.APIINTERFACEID = mi.APIINTERFACEID
                WHERE mi.APIID = m.APIID
                AND   mi.TENANTID = m.TENANTID
                AND   CHARINDEX(@ResourceNoun, op.RELATIVEPATH) > 0
            ) THEN 20 ELSE 0 END
        AS TINYINT) AS OverallScore,
        -- Component scores for storage
        CAST(
            CASE WHEN UPPER(m.APINAME) = UPPER(@Title) THEN 15
                 WHEN DIFFERENCE(m.APINAME, @Title) >= 3 THEN 10
                 WHEN DIFFERENCE(m.APINAME, @Title) = 2 THEN 5
                 ELSE 0 END
        AS TINYINT) AS NameScore,
        CAST(CASE WHEN m.APIDOMAINID = @TargetDomainId AND @TargetDomainId <> -1 THEN 20 ELSE 0 END AS TINYINT) AS DomainScore,
        CAST(
            CASE WHEN @KW1 <> '' AND CHARINDEX(@KW1, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW2 <> '' AND CHARINDEX(@KW2, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW3 <> '' AND CHARINDEX(@KW3, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW4 <> '' AND CHARINDEX(@KW4, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW5 <> '' AND CHARINDEX(@KW5, ISNULL(m.KEYFEATURES,'') + ' ' + ISNULL(m.USECASES,'') + ' ' + ISNULL(m.ABOUTAPI,'')) > 0 THEN 7 ELSE 0 END
        AS TINYINT) AS IntentScore,
        CAST(CASE WHEN EXISTS (
                SELECT 1 FROM MAPIOPERATION op
                INNER JOIN MAPIINTERFACE mi ON op.APIINTERFACEID = mi.APIINTERFACEID
                WHERE mi.APIID = m.APIID
                AND   mi.TENANTID = m.TENANTID
                AND   CHARINDEX(@ResourceNoun, op.RELATIVEPATH) > 0
            ) THEN 20 ELSE 0 END AS TINYINT) AS SchemaScore
    FROM MAPI m
    WHERE m.TENANTID = @TenantId
    AND   m.STATUS   <> 5
)
SELECT MatchedId, MatchedName, MatchedApiDomainId, MatchedApiType,
       OverallScore, NameScore, DomainScore, IntentScore, SchemaScore
FROM   Scored
WHERE  OverallScore >= 45
ORDER  BY OverallScore DESC";

        // =========================================================
        // FIND SIMILARITY AGAINST OPEN REQUESTS (MAPIREQUEST)
        // =========================================================
        public const string FIND_SIMILARITY_REQUEST = @"
;WITH Scored AS (
    SELECT
        r.APIREQUESTID     AS MatchedId,
        r.REQUESTTITLE     AS MatchedName,
        r.TARGETDOMAINID   AS MatchedApiDomainId,
        r.PROPOSEDAPITYPE  AS MatchedApiType,
        CAST(
            CASE WHEN UPPER(r.REQUESTTITLE) = UPPER(@Title) THEN 15
                 WHEN DIFFERENCE(r.REQUESTTITLE, @Title) >= 3 THEN 10
                 WHEN DIFFERENCE(r.REQUESTTITLE, @Title) = 2 THEN 5
                 ELSE 0 END
          + CASE WHEN r.TARGETDOMAINID = @TargetDomainId AND @TargetDomainId <> -1 THEN 20 ELSE 0 END
          + CASE WHEN r.PROPOSEDAPITYPE = @ProposedApiType THEN 10 ELSE 0 END
          + CASE WHEN @KW1 <> '' AND CHARINDEX(@KW1, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW2 <> '' AND CHARINDEX(@KW2, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW3 <> '' AND CHARINDEX(@KW3, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW4 <> '' AND CHARINDEX(@KW4, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW5 <> '' AND CHARINDEX(@KW5, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
        AS TINYINT) AS OverallScore,
        CAST(
            CASE WHEN UPPER(r.REQUESTTITLE) = UPPER(@Title) THEN 15
                 WHEN DIFFERENCE(r.REQUESTTITLE, @Title) >= 3 THEN 10
                 WHEN DIFFERENCE(r.REQUESTTITLE, @Title) = 2 THEN 5
                 ELSE 0 END
        AS TINYINT) AS NameScore,
        CAST(CASE WHEN r.TARGETDOMAINID = @TargetDomainId AND @TargetDomainId <> -1 THEN 20 ELSE 0 END AS TINYINT) AS DomainScore,
        CAST(
            CASE WHEN @KW1 <> '' AND CHARINDEX(@KW1, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW2 <> '' AND CHARINDEX(@KW2, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW3 <> '' AND CHARINDEX(@KW3, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW4 <> '' AND CHARINDEX(@KW4, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
          + CASE WHEN @KW5 <> '' AND CHARINDEX(@KW5, ISNULL(r.REQUESTDESCRIPTION,'') + ' ' + ISNULL(r.USECASES,'') + ' ' + ISNULL(r.BUSINESSJUSTIFICATION,'')) > 0 THEN 7 ELSE 0 END
        AS TINYINT) AS IntentScore,
        CAST(0 AS TINYINT) AS SchemaScore   -- requests have no operations yet
    FROM MAPIREQUEST r
    WHERE r.TENANTID      = @TenantId
    AND   r.APIREQUESTID  <> @ApiRequestId
    AND   r.REQUESTSTATUS NOT IN (3, 12)    -- exclude Rejected and Cancelled
)
SELECT MatchedId, MatchedName, MatchedApiDomainId, MatchedApiType,
       OverallScore, NameScore, DomainScore, IntentScore, SchemaScore
FROM   Scored
WHERE  OverallScore >= 45
ORDER  BY OverallScore DESC";

        // =========================================================
        // GET OPERATION COUNT for a matched API
        // (BLL uses this to decide SuggestionType)
        // =========================================================
        public const string GET_OPERATION_COUNT = @"
SELECT COUNT(1)
FROM   MAPIOPERATION op
INNER  JOIN MAPIINTERFACE mi ON op.APIINTERFACEID = mi.APIINTERFACEID
WHERE  mi.APIID    = @ApiId
AND    mi.TENANTID = @TenantId";

        // =========================================================
        // GET AVG PARAMETER COUNT PER OPERATION for a matched API
        // =========================================================
        public const string GET_AVG_PARAMETER_COUNT = @"
SELECT ISNULL(AVG(CAST(pc.ParamCount AS FLOAT)), 0) AS AvgParamCount
FROM (
    SELECT op.APIOPERATIONID,
           COUNT(p.PARAMETERID) AS ParamCount
    FROM   MAPIOPERATION op
    INNER  JOIN MAPIINTERFACE mi ON op.APIINTERFACEID = mi.APIINTERFACEID
    LEFT   JOIN MAPIPARAMETER p  ON p.APIOPERATIONID  = op.APIOPERATIONID
    WHERE  mi.APIID    = @ApiId
    AND    mi.TENANTID = @TenantId
    GROUP  BY op.APIOPERATIONID
) pc";

        // =========================================================
        // PostgreSQL Variants
        // =========================================================
        public const string PG_GET_BY_REQUEST = @"
SELECT ""DUPLICATECHECKID""   AS DuplicateCheckId,
       ""TENANTID""           AS TenantId,
       ""SOURCEAPIREQUESTID"" AS SourceApiRequestId,
       ""MATCHSOURCE""        AS MatchSource,
       ""MATCHEDAPIID""       AS MatchedApiId,
       ""MATCHEDREQUESTID""   AS MatchedRequestId,
       ""MATCHEDINTERFACEID"" AS MatchedInterfaceId,
       ""OVERALLSCORE""       AS OverallScore,
       ""NAMESCORE""          AS NameScore,
       ""DOMAINSCORE""        AS DomainScore,
       ""INTENTSCORE""        AS IntentScore,
       ""SCHEMASCORE""        AS SchemaScore,
       ""SUGGESTIONTYPE""     AS SuggestionType,
       ""SUGGESTEDCHANGE""    AS SuggestedChange,
       ""MATCHREASON""        AS MatchReason,
       ""REVIEWSTATUS""       AS ReviewStatus,
       ""REVIEWEDBYID""       AS ReviewedById,
       ""REVIEWEDON""         AS ReviewedOn,
       ""REVIEWREMARKS""      AS ReviewRemarks,
       ""CREATEDON""          AS CreatedOn
FROM   ""MAPIREQUESTDUPLICATECHECK""
WHERE  ""SOURCEAPIREQUESTID"" = @ApiRequestId
AND    ""TENANTID""           = @TenantId
ORDER  BY ""OVERALLSCORE"" DESC";

        public const string PG_SAVE_DUPLICATE_CHECK = @"
INSERT INTO ""MAPIREQUESTDUPLICATECHECK"" (
    ""DUPLICATECHECKID"", ""TENANTID"", ""SOURCEAPIREQUESTID"",
    ""MATCHSOURCE"", ""MATCHEDAPIID"", ""MATCHEDREQUESTID"", ""MATCHEDINTERFACEID"",
    ""OVERALLSCORE"", ""NAMESCORE"", ""DOMAINSCORE"", ""INTENTSCORE"", ""SCHEMASCORE"",
    ""SUGGESTIONTYPE"", ""SUGGESTEDCHANGE"", ""MATCHREASON"",
    ""REVIEWSTATUS"", ""CREATEDON""
) VALUES (
    @DuplicateCheckId, @TenantId, @SourceApiRequestId,
    @MatchSource, @MatchedApiId, @MatchedRequestId, @MatchedInterfaceId,
    @OverallScore, @NameScore, @DomainScore, @IntentScore, @SchemaScore,
    @SuggestionType, @SuggestedChange, @MatchReason,
    0, NOW()
)";

        public const string PG_UPDATE_REVIEW_STATUS = @"
UPDATE ""MAPIREQUESTDUPLICATECHECK"" SET
    ""REVIEWSTATUS""  = @ReviewStatus,
    ""REVIEWEDBYID""  = @ReviewedById,
    ""REVIEWEDON""    = NOW(),
    ""REVIEWREMARKS"" = @ReviewRemarks
WHERE ""DUPLICATECHECKID"" = @DuplicateCheckId
AND   ""TENANTID""         = @TenantId";
    }
}