namespace MarketingDAL.Query.MarketingCampaign;

/// <summary>
/// SQL constants for MMKTCAMPAIGN — MarketMind AI execution-layer Marketing Campaign data.
/// No inline SQL elsewhere.
/// </summary>
public static class MarketingCampaignQB
{
    public const string GET_MARKETINGCAMPAIGN = @"
SELECT
    C.CAMPAIGNID          AS CampaignId,
    C.CAMPAIGNCODE        AS CampaignCode,
    C.CAMPAIGNNAME        AS CampaignName,
    C.CAMPAIGNTYPE        AS CampaignType,
    C.CHANNELPLAN         AS ChannelPlan,
    C.SEGMENTID           AS SegmentId,
    S.SEGMENTCODE         AS SegmentCode,
    S.SEGMENTNAME         AS SegmentName,
    C.STARTDATE           AS StartDate,
    C.ENDDATE             AS EndDate,
    C.BUDGETAMOUNT        AS BudgetAmount,
    C.REMARKS             AS Remarks,

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

    C.SORTORDER           AS SortOrder,
    C.STATUS              AS Status,
    C.VERSION             AS Version,
    C.TENANTID            AS TenantId
FROM MMKTCAMPAIGN C
LEFT JOIN MSEGMENT S ON S.SEGMENTID = C.SEGMENTID
LEFT JOIN MUSER CB ON CB.USERID = C.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = C.MODIFIEDBYID
WHERE C.CAMPAIGNID = @CampaignId;
";

    public const string SAVE_MARKETINGCAMPAIGN = @"
INSERT INTO MMKTCAMPAIGN (
    CAMPAIGNID, CAMPAIGNCODE, CAMPAIGNNAME, CAMPAIGNTYPE, CHANNELPLAN, SEGMENTID,
    STARTDATE, ENDDATE, BUDGETAMOUNT, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @CampaignId, @CampaignCode, @CampaignName, @CampaignType, @ChannelPlan, @SegmentId,
    @StartDate, @EndDate, @BudgetAmount, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_MARKETINGCAMPAIGN = @"
UPDATE MMKTCAMPAIGN
SET
    CAMPAIGNCODE   = @CampaignCode,
    CAMPAIGNNAME   = @CampaignName,
    CAMPAIGNTYPE   = @CampaignType,
    CHANNELPLAN    = @ChannelPlan,
    SEGMENTID      = @SegmentId,
    STARTDATE      = @StartDate,
    ENDDATE        = @EndDate,
    BUDGETAMOUNT   = @BudgetAmount,
    REMARKS        = @Remarks,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn,
    SORTORDER      = @SortOrder,
    STATUS         = @Status,
    VERSION        = @Version
WHERE CAMPAIGNID = @CampaignId;
";

    public const string DELETE_MARKETINGCAMPAIGN = @"
DELETE FROM MMKTCAMPAIGN
WHERE CAMPAIGNID = @CampaignId;
";

    public const string GET_SELECTLIST_MARKETINGCAMPAIGN = @"
SELECT
    CAMPAIGNID    AS Id,
    CAMPAIGNCODE  AS Code,
    CAMPAIGNNAME  AS Name
FROM MMKTCAMPAIGN
WHERE STATUS = 1
ORDER BY CAMPAIGNNAME;
";
}