namespace MarketingDAL.Query.Advertisement;

/// <summary>SQL constants for MADVERTISEMENT — MarketMind AI Advertisement data. No inline SQL elsewhere.</summary>
public static class AdvertisementQB
{
    public const string GET_ADVERTISEMENT = @"
SELECT
    A.ADID              AS AdId,
    A.ADCODE            AS AdCode,
    A.ADNAME            AS AdName,
    A.CAMPAIGNID        AS CampaignId,
    C.CAMPAIGNCODE      AS CampaignCode,
    C.CAMPAIGNNAME      AS CampaignName,
    A.CHANNEL           AS Channel,
    A.CREATIVEREF       AS CreativeRef,
    A.SPENDAMOUNT       AS SpendAmount,
    A.STARTDATE         AS StartDate,
    A.ENDDATE           AS EndDate,
    A.REMARKS           AS Remarks,

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

    A.SORTORDER         AS SortOrder,
    A.STATUS            AS Status,
    A.VERSION           AS Version,
    A.TENANTID          AS TenantId
FROM MADVERTISEMENT A
LEFT JOIN MMKTCAMPAIGN C ON C.CAMPAIGNID = A.CAMPAIGNID
LEFT JOIN MUSER CB ON CB.USERID = A.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = A.MODIFIEDBYID
WHERE A.ADID = @AdId;
";

    public const string SAVE_ADVERTISEMENT = @"
INSERT INTO MADVERTISEMENT (
    ADID, ADCODE, ADNAME, CAMPAIGNID, CHANNEL, CREATIVEREF, SPENDAMOUNT, STARTDATE, ENDDATE, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @AdId, @AdCode, @AdName, @CampaignId, @Channel, @CreativeRef, @SpendAmount, @StartDate, @EndDate, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_ADVERTISEMENT = @"
UPDATE MADVERTISEMENT
SET
    ADCODE        = @AdCode,
    ADNAME        = @AdName,
    CAMPAIGNID    = @CampaignId,
    CHANNEL       = @Channel,
    CREATIVEREF   = @CreativeRef,
    SPENDAMOUNT   = @SpendAmount,
    STARTDATE     = @StartDate,
    ENDDATE       = @EndDate,
    REMARKS       = @Remarks,
    MODIFIEDBYID  = @ModifiedById,
    MODIFIEDON    = @ModifiedOn,
    SORTORDER     = @SortOrder,
    STATUS        = @Status,
    VERSION       = @Version
WHERE ADID = @AdId;
";

    public const string DELETE_ADVERTISEMENT = @"
DELETE FROM MADVERTISEMENT
WHERE ADID = @AdId;
";

    public const string GET_SELECTLIST_ADVERTISEMENT = @"
SELECT
    ADID    AS Id,
    ADCODE  AS Code,
    ADNAME  AS Name
FROM MADVERTISEMENT
WHERE STATUS = 1
ORDER BY ADNAME;
";
}