namespace CRMSalesDAL.Query.Deal;

/// <summary>
/// SQL constants for TDEAL — SalesMind AI pipeline entity. No inline SQL elsewhere.
/// Covering index note: (LEADID), (REPID), (STAGE) on TDEAL — see this module's migration file.
/// </summary>
public static class DealQB
{
    // DaysInStage is a read-time DATEDIFF, never a stored column (would go stale) — per this
    // entity's brief. RepId is nullable (see DealDTO.RepId doc comment) so the MEMPLOYEE join is
    // LEFT JOIN and RepCode/RepName come back NULL for an unassigned Deal.
    public const string GET_DEAL = @"
SELECT
    D.DEALID                        AS DealId,
    D.DEALCODE                      AS DealCode,
    D.DEALNAME                      AS DealName,
    D.LEADID                        AS LeadId,
    L.LEADCODE                      AS LeadCode,
    L.LEADNAME                      AS LeadName,
    D.STAGE                         AS Stage,
    D.DEALVALUE                     AS DealValue,
    D.TEMPERATURE                   AS Temperature,
    D.MANMONEY                      AS MANMoney,
    D.MANAUTHORITY                  AS MANAuthority,
    D.MANNEED                       AS MANNeed,
    D.STAGECHANGEDON                AS StageChangedOn,
    DATEDIFF(DAY, D.STAGECHANGEDON, GETUTCDATE()) AS DaysInStage,
    D.NEXTACTION                    AS NextAction,
    D.RISKFLAGS                     AS RiskFlags,
    D.REPID                         AS RepId,
    E.EMPLOYEECODE                  AS RepCode,
    E.EMPLOYEENAME                  AS RepName,
    D.REMARKS                       AS Remarks,

    D.CREATEDBYID                   AS CreatedById,
    D.CREATEDON                     AS CreatedOn,
    CB.USERNAME                     AS CreatedByName,
    D.MODIFIEDBYID                  AS ModifiedById,
    D.MODIFIEDON                    AS ModifiedOn,
    MB.USERNAME                     AS ModifiedByName,

    D.SORTORDER                     AS SortOrder,
    D.STATUS                        AS Status,
    D.VERSION                       AS Version,
    D.TENANTID                      AS TenantId
FROM TDEAL D
LEFT JOIN TLEAD L ON L.LEADID = D.LEADID
LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = D.REPID
LEFT JOIN MUSER CB ON CB.USERID = D.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = D.MODIFIEDBYID
WHERE D.DEALID = @DealId;
";

    // Used by DealBLL to compare the incoming Stage against the previously-persisted value before
    // deciding whether to stamp StageChangedOn = UtcNow on Update.
    public const string GET_DEAL_STAGE = @"
SELECT STAGE FROM TDEAL WHERE DEALID = @DealId;
";

    public const string SAVE_DEAL = @"
INSERT INTO TDEAL (
    DEALID, DEALCODE, DEALNAME, LEADID, STAGE, DEALVALUE, TEMPERATURE,
    MANMONEY, MANAUTHORITY, MANNEED, STAGECHANGEDON, NEXTACTION, RISKFLAGS, REPID, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @DealId, @DealCode, @DealName, @LeadId, @Stage, @DealValue, @Temperature,
    @MANMoney, @MANAuthority, @MANNeed, @StageChangedOn, @NextAction, @RiskFlags, @RepId, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_DEAL = @"
UPDATE TDEAL
SET
    DEALCODE        = @DealCode,
    DEALNAME        = @DealName,
    LEADID          = @LeadId,
    STAGE           = @Stage,
    DEALVALUE       = @DealValue,
    TEMPERATURE     = @Temperature,
    MANMONEY        = @MANMoney,
    MANAUTHORITY    = @MANAuthority,
    MANNEED         = @MANNeed,
    STAGECHANGEDON  = @StageChangedOn,
    NEXTACTION      = @NextAction,
    RISKFLAGS       = @RiskFlags,
    REPID           = @RepId,
    REMARKS         = @Remarks,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = @ModifiedOn,
    SORTORDER       = @SortOrder,
    STATUS          = @Status,
    VERSION         = @Version
WHERE DEALID = @DealId;
";

    public const string DELETE_DEAL = @"
DELETE FROM TDEAL
WHERE DEALID = @DealId;
";

    public const string GET_SELECTLIST_DEAL = @"
SELECT
    DEALID    AS Id,
    DEALCODE  AS Code,
    DEALNAME  AS Name
FROM TDEAL
WHERE STATUS = 1
ORDER BY DEALNAME;
";
}