namespace CRMSalesDAL.Query.LeadScoring;

/// <summary>
/// SQL constants backing LeadScoringBLL.RecalculateLeadScoreAsync. Reads/writes TLEAD directly —
/// a Marketing-owned table — which is consistent with this repo's established cross-module
/// table-access pattern (Marketing already reads PayRoll's MCONTACT directly the same way).
/// </summary>
public static class LeadScoringQB
{
    // "Most recent open Deal" = highest DEALID among Stage NOT IN (5,6) [ClosedWon/ClosedLost] for
    // this Lead. OUTER APPLY so a Lead with zero Deals still returns one row with NULL MAN fields.
    public const string GET_LEAD_SCORING_INPUT = @"
SELECT
    L.LEADID     AS LeadId,
    L.SEGMENTID  AS SegmentId,
    L.ICPID      AS ICPId,
    D.MANMONEY     AS LatestOpenDealMANMoney,
    D.MANAUTHORITY AS LatestOpenDealMANAuthority,
    D.MANNEED      AS LatestOpenDealMANNeed
FROM TLEAD L
OUTER APPLY (
    SELECT TOP 1 DL.MANMONEY, DL.MANAUTHORITY, DL.MANNEED
    FROM TDEAL DL
    WHERE DL.LEADID = L.LEADID
      AND DL.STAGE NOT IN (5, 6)
    ORDER BY DL.DEALID DESC
) D
WHERE L.LEADID = @LeadId;
";

    // Narrow, single-column write — deliberately NOT routed through BaseEntityAppService.
    // ExecuteSaveAsync: this is not a Sales entity save, it's Sales writing one derived column
    // back onto a Marketing-owned table (LEADSCORE), same narrow-write shape as any other direct
    // cross-module column update in this repo.
    public const string UPDATE_LEAD_SCORE = @"
UPDATE TLEAD
SET LEADSCORE = @Score
WHERE LEADID = @LeadId;
";
}