create table if not exists site_recommendation_slots (
  id uuid primary key default gen_random_uuid(),
  placement_key text not null,
  slot_key text not null,
  source_type text not null,
  source_id text not null default '',
  status text not null default 'active' check (status in ('active', 'hidden')),
  sort_order integer not null default 100,
  title_override text not null default '',
  subtitle_override text not null default '',
  image_url_override text not null default '',
  cta_label_override text not null default '',
  href_override text not null default '',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (placement_key, slot_key)
);

create index if not exists idx_site_recommendation_slots_placement
  on site_recommendation_slots(placement_key, status, sort_order);

insert into site_recommendation_slots (placement_key, slot_key, source_type, sort_order, status)
values
  ('home.news', 'primary', 'news', 10, 'active'),
  ('home.news', 'secondary_1', 'news', 20, 'active'),
  ('home.news', 'secondary_2', 'news', 30, 'active'),
  ('home.courses', 'slot_1', 'course', 10, 'active'),
  ('home.courses', 'slot_2', 'course', 20, 'active'),
  ('home.courses', 'slot_3', 'course', 30, 'active'),
  ('home.events', 'slot_1', 'event', 10, 'active'),
  ('home.events', 'slot_2', 'event', 20, 'active'),
  ('home.events', 'slot_3', 'event', 30, 'active'),
  ('home.events', 'slot_4', 'event', 40, 'active'),
  ('home.experts', 'slot_1', 'expert', 10, 'active'),
  ('home.experts', 'slot_2', 'expert', 20, 'active'),
  ('home.experts', 'slot_3', 'expert', 30, 'active'),
  ('home.experts', 'slot_4', 'expert', 40, 'active'),
  ('news.carousel', 'slide_1', 'news', 10, 'active'),
  ('news.carousel', 'slide_2', 'news', 20, 'active'),
  ('news.carousel', 'slide_3', 'news', 30, 'active'),
  ('courses.carousel', 'slide_1', 'course', 10, 'active'),
  ('courses.carousel', 'slide_2', 'course', 20, 'active'),
  ('courses.carousel', 'slide_3', 'course', 30, 'active'),
  ('courses.carousel', 'slide_4', 'course', 40, 'active'),
  ('courses.carousel', 'slide_5', 'course', 50, 'active'),
  ('events.carousel', 'slide_1', 'event', 10, 'active'),
  ('events.carousel', 'slide_2', 'event', 20, 'active'),
  ('events.carousel', 'slide_3', 'event', 30, 'active'),
  ('events.carousel', 'slide_4', 'event', 40, 'active'),
  ('events.carousel', 'slide_5', 'event', 50, 'active')
on conflict (placement_key, slot_key) do update set
  source_type = excluded.source_type,
  sort_order = excluded.sort_order,
  updated_at = now();

with ranked as (
  select
    n.id::text as source_id,
    row_number() over (
      order by
        case when n.featured_position between 1 and 3 then 0 else 1 end,
        n.featured_position nulls last,
        n.published_at desc nulls last,
        n.created_at desc
    ) as rn
  from news_articles n
  where n.status = 'published'
  limit 3
)
update site_recommendation_slots s
set source_id = ranked.source_id,
    updated_at = now()
from ranked
where s.placement_key in ('home.news', 'news.carousel')
  and s.slot_key = case ranked.rn
    when 1 then case when s.placement_key = 'home.news' then 'primary' else 'slide_1' end
    when 2 then case when s.placement_key = 'home.news' then 'secondary_1' else 'slide_2' end
    when 3 then case when s.placement_key = 'home.news' then 'secondary_2' else 'slide_3' end
  end
  and coalesce(s.source_id, '') = '';

with ranked as (
  select
    p.id::text as source_id,
    row_number() over (order by d.sort_order, p.created_at desc, p.id) as rn
  from products p
  join course_product_details d on d.product_id = p.id
  where p.type = 'course'
    and p.status = 'published'
    and d.status = 'published'
    and d.recommended = true
  order by d.sort_order, p.created_at desc, p.id
  limit 5
)
update site_recommendation_slots s
set source_id = ranked.source_id,
    updated_at = now()
from ranked
where s.placement_key in ('home.courses', 'courses.carousel')
  and s.slot_key = case
    when s.placement_key = 'home.courses' and ranked.rn <= 3 then 'slot_' || ranked.rn
    when s.placement_key = 'courses.carousel' then 'slide_' || ranked.rn
    else ''
  end
  and coalesce(s.source_id, '') = '';

with ranked as (
  select
    p.id::text as source_id,
    row_number() over (order by d.sort_order, d.starts_at nulls last, p.created_at desc, p.id) as rn
  from products p
  join event_product_details d on d.product_id = p.id
  where p.type = 'event'
    and p.status = 'published'
    and d.status = 'published'
    and d.recommended = true
  order by d.sort_order, d.starts_at nulls last, p.created_at desc, p.id
  limit 5
)
update site_recommendation_slots s
set source_id = ranked.source_id,
    updated_at = now()
from ranked
where s.placement_key in ('home.events', 'events.carousel')
  and s.slot_key = case
    when s.placement_key = 'home.events' and ranked.rn <= 4 then 'slot_' || ranked.rn
    when s.placement_key = 'events.carousel' then 'slide_' || ranked.rn
    else ''
  end
  and coalesce(s.source_id, '') = '';

with ranked as (
  select
    fe.expert_id::text as source_id,
    row_number() over (order by fe.sort_order, e.updated_at desc, e.created_at desc, e.id) as rn
  from site_home_featured_experts fe
  join expert_profiles e on e.id = fe.expert_id
  where e.status = 'published'
    and e.deleted_at is null
  order by fe.sort_order, e.updated_at desc, e.created_at desc, e.id
  limit 4
)
update site_recommendation_slots s
set source_id = ranked.source_id,
    updated_at = now()
from ranked
where s.placement_key = 'home.experts'
  and s.slot_key = 'slot_' || ranked.rn
  and coalesce(s.source_id, '') = '';

update staff_roles
set permissions = array(
  select distinct unnest(permissions || array[
    'site_recommendation:read',
    'site_recommendation:write'
  ])
)
where code in ('super_admin', 'operations_admin', 'content_admin');
