namespace MarketingDAL.Query.ContentRef;

/// <summary>SQL constants for MCONTENTREF — MarketMind AI ContentRef data. No inline SQL elsewhere.</summary>
public static class ContentRefQB
{
    public const string GET_CONTENTREF = @"
SELECT
    R.CONTENTREFID       AS ContentRefId,
    R.CONTENTREFCODE     AS ContentRefCode,
    R.CONTENTREFTITLE    AS ContentRefTitle,
    R.JOURNEYSTAGE       AS JourneyStage,
    R.RRRTAG             AS RRRTag,
    R.PERSONAID          AS PersonaId,
    P.PERSONACODE        AS PersonaCode,
    P.PERSONANAME        AS PersonaName,
    R.SOURCECONTENTID    AS SourceContentId,
    R.REMARKS            AS Remarks,

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

    R.SORTORDER          AS SortOrder,
    R.STATUS             AS Status,
    R.VERSION            AS Version,
    R.TENANTID           AS TenantId
FROM MCONTENTREF R
LEFT JOIN MPERSONA P ON P.PERSONAID = R.PERSONAID
LEFT JOIN MUSER CB ON CB.USERID = R.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = R.MODIFIEDBYID
WHERE R.CONTENTREFID = @ContentRefId;
";

    public const string SAVE_CONTENTREF = @"
INSERT INTO MCONTENTREF (
    CONTENTREFID, CONTENTREFCODE, CONTENTREFTITLE, JOURNEYSTAGE, RRRTAG, PERSONAID, SOURCECONTENTID, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @ContentRefId, @ContentRefCode, @ContentRefTitle, @JourneyStage, @RRRTag, @PersonaId, @SourceContentId, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_CONTENTREF = @"
UPDATE MCONTENTREF
SET
    CONTENTREFCODE     = @ContentRefCode,
    CONTENTREFTITLE    = @ContentRefTitle,
    JOURNEYSTAGE       = @JourneyStage,
    RRRTAG             = @RRRTag,
    PERSONAID          = @PersonaId,
    SOURCECONTENTID    = @SourceContentId,
    REMARKS            = @Remarks,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn,
    SORTORDER          = @SortOrder,
    STATUS             = @Status,
    VERSION            = @Version
WHERE CONTENTREFID = @ContentRefId;
";

    public const string DELETE_CONTENTREF = @"
DELETE FROM MCONTENTREF
WHERE CONTENTREFID = @ContentRefId;
";

    public const string GET_SELECTLIST_CONTENTREF = @"
SELECT
    CONTENTREFID     AS Id,
    CONTENTREFCODE   AS Code,
    CONTENTREFTITLE  AS Name
FROM MCONTENTREF
WHERE STATUS = 1
ORDER BY CONTENTREFTITLE;
";
}