namespace IDMSDAL.QueryBuilders;

// Required indexes:
//   LENGAGEMENTSIGNAL: (ENGAGEMENTID, TENANTID, ISACTIVE) INCLUDE (SIGNALWEIGHT, DETECTEDON)
//
// Note: SIGNALID is IDENTITY — use ExecuteInsertReturnIdentityAsync on INSERT.

public static class SignalQB
{
    public const string GET_ACTIVE_BY_ENGAGEMENT = @"
SELECT SIG.SIGNALID      AS SignalId,
       SIG.ENGAGEMENTID  AS EngagementId,
       SIG.TENANTID      AS TenantId,
       SIG.SIGNALTYPE    AS SignalType,
       SIG.SIGNALWEIGHT  AS SignalWeight,
       SIG.ISACTIVE      AS IsActive,
       SIG.DETECTEDON    AS DetectedOn,
       SIG.RESOLVEDON    AS ResolvedOn,
       SIG.RESOLVEDBYID  AS ResolvedById,
       SIG.CREATEDBYID   AS CreatedById,
       SIG.CREATEDON     AS CreatedOn
FROM   LENGAGEMENTSIGNAL SIG
WHERE  SIG.ENGAGEMENTID = @EngagementId
  AND  SIG.ISACTIVE     = 1
  AND  SIG.TENANTID     = @TenantId
ORDER BY SIG.DETECTEDON DESC";

    // SIGNALID is IDENTITY — excluded from INSERT column list.
    // Use ExecuteInsertReturnIdentityAsync to retrieve the generated ID.
    public const string INSERT = @"
INSERT INTO LENGAGEMENTSIGNAL
    (ENGAGEMENTID, TENANTID, SIGNALTYPE, SIGNALWEIGHT,
     ISACTIVE, DETECTEDON, CREATEDBYID, CREATEDON)
VALUES
    (@EngagementId, @TenantId, @SignalType, @SignalWeight,
     1, GETDATE(), @CreatedById, @CreatedOn)";

    public const string RESOLVE = @"
UPDATE LENGAGEMENTSIGNAL SET
    ISACTIVE     = 0,
    RESOLVEDON   = @ResolvedOn,
    RESOLVEDBYID = @ResolvedById
WHERE  SIGNALID  = @SignalId AND TENANTID = @TenantId";

    public const string GET_SCORE_BY_ENGAGEMENT = @"
SELECT ISNULL(SUM(SIG.SIGNALWEIGHT), 0) AS TotalScore
FROM   LENGAGEMENTSIGNAL SIG
WHERE  SIG.ENGAGEMENTID = @EngagementId
  AND  SIG.ISACTIVE     = 1
  AND  SIG.TENANTID     = @TenantId";
}