-- Post-level visibility controls

alter table if exists posts
  add column if not exists visibility text not null default 'public';

update posts
set visibility = 'public'
where visibility is null;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'posts_visibility_check'
  ) then
    alter table posts
      add constraint posts_visibility_check
      check (visibility in ('public', 'followers', 'private'));
  end if;
end $$;

drop policy if exists posts_select_private_model on posts;
create policy posts_select_private_model on posts
  for select
  using (
    auth.uid() = author_id
    or visibility = 'public'
    or (
      visibility = 'followers'
      and exists (
        select 1
        from follows f
        where f.follower_id = auth.uid()
          and f.following_id = author_id
          and f.status = 'accepted'
      )
    )
  );