alter table news_articles
  add column if not exists author_name text not null default '大连市大数据研究院',
  add column if not exists featured_position integer,
  add column if not exists related_article_ids text[] not null default array[]::text[];

create unique index if not exists idx_news_articles_featured_position
  on news_articles (featured_position)
  where featured_position is not null;

update news_articles
set featured_position = ranked.position
from (
  select id, row_number() over (order by published_at desc nulls last, created_at desc) as position
  from news_articles
  where status = 'published'
  order by published_at desc nulls last, created_at desc
  limit 3
) ranked
where news_articles.id = ranked.id
  and news_articles.featured_position is null
  and not exists (
    select 1 from news_articles existing
    where existing.featured_position = ranked.position
      and existing.id <> news_articles.id
  );
