\pset pager off
select version();
create table t_channels (id int primary key, owner_id int not null, visibility text not null);
create table t_posts (id int primary key, channel_id int not null, status text not null);
create table t_follows (channel_id int not null, user_id int not null, primary key (channel_id, user_id));
create table t_sessions (id int primary key, post_id int not null, viewer_id int not null);
insert into t_channels values (1, 100, 'private'), (2, 200, 'private');
insert into t_posts values (10, 1, 'published'), (20, 2, 'published');
insert into t_follows values (1, 501), (2, 502);
insert into t_sessions values (1, 10, 501), (2, 20, 502);
analyze t_channels, t_posts, t_follows, t_sessions;

\echo '== V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,t)'
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or c.visibility = 'public'
                  or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id))) as allowed
  from t_sessions s order by s.id;

\echo '== V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t'
prepare allowed(int, int) as
select exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
                where p.id = $1
                  and (c.owner_id = $2
                       or (p.status = 'published'
                           and (c.visibility = 'public'
                                or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = $2))))) as allowed;
execute allowed(10, 501);
execute allowed(20, 502);

\echo '== V3: Q1 restricted to session 2 alone (single outer row); expected t'
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or (p.status = 'published'
                      and (c.visibility = 'public'
                           or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id))))) as allowed
  from t_sessions s where s.id = 2;

\echo '== V4: Q1 with the outer rows in reverse order; expected (2,t), (1,t)'
select s.id,
  exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
           where p.id = s.post_id
             and (c.owner_id = s.viewer_id
                  or (p.status = 'published'
                      and (c.visibility = 'public'
                           or exists (select 1 from t_follows f where f.channel_id = c.id and f.user_id = s.viewer_id))))) as allowed
  from (select * from t_sessions order by id desc offset 0) s;