create table if not exists news_featured_snapshots (
  id text primary key default 'home',
  article_ids text[] not null default array[]::text[],
  updated_at timestamptz not null default now()
);

insert into news_featured_snapshots (id, article_ids)
select 'home', coalesce(array_agg(id::text order by featured_position), array[]::text[])
from news_articles
where status = 'published'
  and featured_position between 1 and 3
having count(*) = 3
on conflict (id) do nothing;

insert into news_featured_snapshots (id, article_ids)
select 'home', coalesce(array_agg(id::text order by published_at desc nulls last, created_at desc), array[]::text[])
from (
  select id, published_at, created_at
  from news_articles
  where status = 'published'
  order by published_at desc nulls last, created_at desc
  limit 3
) seeded
having count(*) = 3
on conflict (id) do nothing;
