namespace MarketingDAL.Query.Communication;

/// <summary>SQL constants for MCOMMUNICATION — MarketMind AI Communication data. No inline SQL elsewhere.</summary>
public static class CommunicationQB
{
    public const string GET_COMMUNICATION = @"
SELECT
    M.COMMUNICATIONID     AS CommunicationId,
    M.COMMUNICATIONCODE   AS CommunicationCode,
    M.COMMUNICATIONNAME   AS CommunicationName,
    M.CHANNEL             AS Channel,
    M.CAMPAIGNID          AS CampaignId,
    C.CAMPAIGNCODE        AS CampaignCode,
    C.CAMPAIGNNAME        AS CampaignName,
    M.SEGMENTID           AS SegmentId,
    S.SEGMENTCODE         AS SegmentCode,
    S.SEGMENTNAME         AS SegmentName,
    M.PERSONAID           AS PersonaId,
    P.PERSONACODE         AS PersonaCode,
    P.PERSONANAME         AS PersonaName,
    M.SUBJECT             AS Subject,
    M.BODYTEXT            AS BodyText,
    M.COMMUNICATIONSTATUS AS CommunicationStatus,
    M.SCHEDULEDON         AS ScheduledOn,
    M.SENTON              AS SentOn,
    M.REMARKS             AS Remarks,

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

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

    public const string SAVE_COMMUNICATION = @"
INSERT INTO MCOMMUNICATION (
    COMMUNICATIONID, COMMUNICATIONCODE, COMMUNICATIONNAME, CHANNEL, CAMPAIGNID, SEGMENTID, PERSONAID,
    SUBJECT, BODYTEXT, COMMUNICATIONSTATUS, SCHEDULEDON, SENTON, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @CommunicationId, @CommunicationCode, @CommunicationName, @Channel, @CampaignId, @SegmentId, @PersonaId,
    @Subject, @BodyText, @CommunicationStatus, @ScheduledOn, @SentOn, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_COMMUNICATION = @"
UPDATE MCOMMUNICATION
SET
    COMMUNICATIONCODE     = @CommunicationCode,
    COMMUNICATIONNAME     = @CommunicationName,
    CHANNEL               = @Channel,
    CAMPAIGNID            = @CampaignId,
    SEGMENTID             = @SegmentId,
    PERSONAID             = @PersonaId,
    SUBJECT               = @Subject,
    BODYTEXT              = @BodyText,
    COMMUNICATIONSTATUS   = @CommunicationStatus,
    SCHEDULEDON           = @ScheduledOn,
    SENTON                = @SentOn,
    REMARKS               = @Remarks,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn,
    SORTORDER             = @SortOrder,
    STATUS                = @Status,
    VERSION               = @Version
WHERE COMMUNICATIONID = @CommunicationId;
";

    public const string DELETE_COMMUNICATION = @"
DELETE FROM MCOMMUNICATION
WHERE COMMUNICATIONID = @CommunicationId;
";

    public const string GET_SELECTLIST_COMMUNICATION = @"
SELECT
    COMMUNICATIONID    AS Id,
    COMMUNICATIONCODE  AS Code,
    COMMUNICATIONNAME  AS Name
FROM MCOMMUNICATION
WHERE STATUS = 1
ORDER BY COMMUNICATIONNAME;
";

    /// <summary>
    /// Resolves the send audience for a Communication's target Segment: every active Lead in
    /// that segment with a linked Contact, returning the Contact's email/mobile. TLEAD has no
    /// TENANTID column (predates this repo's tenant convention — see the MarketMind master-data
    /// migration's flagged deviation) and this module's existing precedent
    /// (LeadSourceQB.GET_SELECTLIST_LEADSOURCE) already doesn't filter by tenant either — tenant
    /// isolation here is via the per-tenant physical database connection, not a WHERE clause.
    /// Covering index note: (SEGMENTID, STATUS) on TLEAD, (CONTACTID) on MCONTACT (PK, already
    /// indexed).
    /// </summary>
    public const string GET_COMMUNICATION_AUDIENCE = @"
SELECT
    C.MAILID    AS Mail,
    C.MOBILENO  AS Mobile
FROM TLEAD L
JOIN MCONTACT C ON C.CONTACTID = L.CONTACTID
WHERE L.SEGMENTID = @SegmentId
  AND L.STATUS = 1;
";
}