namespace MarketingDAL.Query.LeadMagnet;

/// <summary>SQL constants for MLEADMAGNET — MarketMind AI LeadMagnet data. No inline SQL elsewhere.</summary>
public static class LeadMagnetQB
{
    public const string GET_LEADMAGNET = @"
SELECT
    L.LEADMAGNETID       AS LeadMagnetId,
    L.LEADMAGNETCODE     AS LeadMagnetCode,
    L.LEADMAGNETNAME     AS LeadMagnetName,
    L.OFFERTYPE          AS OfferType,
    L.CAMPAIGNID         AS CampaignId,
    C.CAMPAIGNCODE       AS CampaignCode,
    C.CAMPAIGNNAME       AS CampaignName,
    L.GATINGFIELDSJSON   AS GatingFieldsJson,
    L.REMARKS            AS Remarks,

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

    L.SORTORDER          AS SortOrder,
    L.STATUS             AS Status,
    L.VERSION            AS Version,
    L.TENANTID           AS TenantId
FROM MLEADMAGNET L
LEFT JOIN MMKTCAMPAIGN C ON C.CAMPAIGNID = L.CAMPAIGNID
LEFT JOIN MUSER CB ON CB.USERID = L.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = L.MODIFIEDBYID
WHERE L.LEADMAGNETID = @LeadMagnetId;
";

    public const string SAVE_LEADMAGNET = @"
INSERT INTO MLEADMAGNET (
    LEADMAGNETID, LEADMAGNETCODE, LEADMAGNETNAME, OFFERTYPE, CAMPAIGNID, GATINGFIELDSJSON, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @LeadMagnetId, @LeadMagnetCode, @LeadMagnetName, @OfferType, @CampaignId, @GatingFieldsJson, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_LEADMAGNET = @"
UPDATE MLEADMAGNET
SET
    LEADMAGNETCODE     = @LeadMagnetCode,
    LEADMAGNETNAME     = @LeadMagnetName,
    OFFERTYPE          = @OfferType,
    CAMPAIGNID         = @CampaignId,
    GATINGFIELDSJSON   = @GatingFieldsJson,
    REMARKS            = @Remarks,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn,
    SORTORDER          = @SortOrder,
    STATUS             = @Status,
    VERSION            = @Version
WHERE LEADMAGNETID = @LeadMagnetId;
";

    public const string DELETE_LEADMAGNET = @"
DELETE FROM MLEADMAGNET
WHERE LEADMAGNETID = @LeadMagnetId;
";

    public const string GET_SELECTLIST_LEADMAGNET = @"
SELECT
    LEADMAGNETID    AS Id,
    LEADMAGNETCODE  AS Code,
    LEADMAGNETNAME  AS Name
FROM MLEADMAGNET
WHERE STATUS = 1
ORDER BY LEADMAGNETNAME;
";
}