namespace CMSDAL.Query.Channel;
public static class ChannelQB
{
    public const string GET_LIST = @"
        SELECT CHANNELID AS ChannelId, CHANNELCODE AS ChannelCode, CHANNELNAME AS ChannelName,
               DESCRIPTION AS Description, VISIBILITY AS Visibility, COLORCODE AS ColorCode,
               ICONNAME AS IconName, STATUS AS Status
        FROM DBO.MCMSCHANNEL
        WHERE TENANTID = @TenantId AND STATUS = 1
        ORDER BY CHANNELNAME";

    public const string GET_BY_ID = @"
        SELECT CHANNELID AS ChannelId, CHANNELCODE AS ChannelCode, CHANNELNAME AS ChannelName,
               DESCRIPTION AS Description, VISIBILITY AS Visibility, COLORCODE AS ColorCode,
               ICONNAME AS IconName, DEFAULTFILTERSJSON AS DefaultFiltersJson, STATUS AS Status, TENANTID AS TenantId
        FROM DBO.MCMSCHANNEL
        WHERE CHANNELID = @ChannelId AND TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO DBO.MCMSCHANNEL (CHANNELCODE, CHANNELNAME, DESCRIPTION, VISIBILITY, COLORCODE, ICONNAME, DEFAULTFILTERSJSON, STATUS, TENANTID, CREATEDBYID, MODIFIEDBYID)
        VALUES (@ChannelCode, @ChannelName, @Description, @Visibility, @ColorCode, @IconName, @DefaultFiltersJson, @Status, @TenantId, @CreatedById, @ModifiedById)";

    public const string UPDATE = @"
        UPDATE DBO.MCMSCHANNEL SET CHANNELNAME=@ChannelName, DESCRIPTION=@Description,
               VISIBILITY=@Visibility, COLORCODE=@ColorCode, ICONNAME=@IconName,
               DEFAULTFILTERSJSON=@DefaultFiltersJson, STATUS=@Status,
               MODIFIEDBYID=@ModifiedById, MODIFIEDON=GETDATE()
        WHERE CHANNELID=@ChannelId AND TENANTID=@TenantId";

    public const string GET_CHANNEL_CONTENT = @"
        SELECT cc.CHANNELCONTENTID AS ChannelContentId, cc.CHANNELID AS ChannelId,
               cc.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
               c.CONTENTSTATUS AS ContentStatus, cc.PINNED AS Pinned, cc.POSITIONNO AS PositionNo
        FROM DBO.TCMSCHANNELCONTENT cc
        JOIN DBO.TCONTENT c ON c.CONTENTID = cc.CONTENTID
        WHERE cc.CHANNELID = @ChannelId AND cc.TENANTID = @TenantId
        ORDER BY cc.PINNED DESC, cc.POSITIONNO";

    public const string ADD_CHANNEL_CONTENT = @"
        INSERT INTO DBO.TCMSCHANNELCONTENT (CHANNELID, CONTENTID, PINNED, POSITIONNO, ADDEDBYID, TENANTID)
        VALUES (@ChannelId, @ContentId, @Pinned, @PositionNo, @AddedById, @TenantId)";

    // Published-only (CONTENTSTATUS = 3) variant of GET_CHANNEL_CONTENT — backs the Show<->CMS
    // Channel live-pull integration (Part B, Slice 3): a Show linked to a CMS Channel shows
    // whatever's currently published under it, with zero propagation delay.
    public const string GET_PUBLISHED_CHANNEL_CONTENT = @"
        SELECT cc.CHANNELCONTENTID AS ChannelContentId, cc.CHANNELID AS ChannelId,
               cc.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
               c.CONTENTSTATUS AS ContentStatus, cc.PINNED AS Pinned, cc.POSITIONNO AS PositionNo
        FROM DBO.TCMSCHANNELCONTENT cc
        JOIN DBO.TCONTENT c ON c.CONTENTID = cc.CONTENTID
        WHERE cc.CHANNELID = @ChannelId AND cc.TENANTID = @TenantId AND c.CONTENTSTATUS = 3
        ORDER BY cc.PINNED DESC, cc.POSITIONNO";
}