namespace MarketingDAL.Query.NDPHypothesis;

/// <summary>SQL constants for MNDPHYPOTHESIS — MarketMind AI Need/Desire/Problem hypothesis data.</summary>
public static class NDPHypothesisQB
{
    public const string GET_NDPHYPOTHESIS = @"
SELECT
    N.NDPHYPOTHESISID     AS NDPHypothesisId,
    N.PERSONAID           AS PersonaId,
    P.PERSONACODE         AS PersonaCode,
    P.PERSONANAME         AS PersonaName,
    N.NDPTYPE             AS NDPType,
    N.NDPTEXT             AS NDPText,
    N.DIMENSION           AS Dimension,
    N.INTENSITYRATING     AS IntensityRating,

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

    N.SORTORDER           AS SortOrder,
    N.STATUS              AS Status,
    N.VERSION             AS Version,
    N.TENANTID            AS TenantId
FROM MNDPHYPOTHESIS N
LEFT JOIN MPERSONA P ON P.PERSONAID = N.PERSONAID
LEFT JOIN MUSER CB ON CB.USERID = N.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = N.MODIFIEDBYID
WHERE N.NDPHYPOTHESISID = @NDPHypothesisId;
";

    public const string SAVE_NDPHYPOTHESIS = @"
INSERT INTO MNDPHYPOTHESIS (
    NDPHYPOTHESISID, PERSONAID, NDPTYPE, NDPTEXT, DIMENSION, INTENSITYRATING,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @NDPHypothesisId, @PersonaId, @NDPType, @NDPText, @Dimension, @IntensityRating,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_NDPHYPOTHESIS = @"
UPDATE MNDPHYPOTHESIS
SET
    PERSONAID         = @PersonaId,
    NDPTYPE           = @NDPType,
    NDPTEXT           = @NDPText,
    DIMENSION         = @Dimension,
    INTENSITYRATING   = @IntensityRating,
    MODIFIEDBYID      = @ModifiedById,
    MODIFIEDON        = @ModifiedOn,
    SORTORDER         = @SortOrder,
    STATUS            = @Status,
    VERSION           = @Version
WHERE NDPHYPOTHESISID = @NDPHypothesisId;
";

    public const string DELETE_NDPHYPOTHESIS = @"
DELETE FROM MNDPHYPOTHESIS
WHERE NDPHYPOTHESISID = @NDPHypothesisId;
";

    public const string GET_SELECTLIST_NDPHYPOTHESIS = @"
SELECT
    NDPHYPOTHESISID                                          AS Id,
    CASE NDPTYPE WHEN 1 THEN 'NEED' WHEN 2 THEN 'DESIRE' WHEN 3 THEN 'PROBLEM' ELSE '' END AS Code,
    NDPTEXT                                                   AS Name
FROM MNDPHYPOTHESIS
WHERE STATUS = 1
ORDER BY NDPHYPOTHESISID;
";
}