alter table course_product_details
  add column if not exists access_policy text not null default 'free_all',
  add column if not exists registered_price_amount integer not null default 0,
  add column if not exists junior_price_amount integer not null default 0,
  add column if not exists senior_price_amount integer not null default 0,
  add column if not exists cover_url text not null default '',
  add column if not exists live_url text not null default '',
  add column if not exists replay_url text not null default '';

create table if not exists course_modules (
  id text primary key,
  product_id text not null references products(id) on delete cascade,
  title text not null default '',
  description text not null default '',
  video_url text not null default '',
  material_url text not null default '',
  duration_minutes integer not null default 0,
  sort_order integer not null default 0,
  status text not null default 'published',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

insert into course_modules (id, product_id, title, sort_order, status)
select
  product_id || '-module-' || row_number() over (partition by product_id order by ordinality),
  product_id,
  module_title,
  ordinality * 10,
  'published'
from course_product_details
cross join unnest(modules) with ordinality as module_item(module_title, ordinality)
where not exists (
  select 1 from course_modules cm where cm.product_id = course_product_details.product_id
);

update course_product_details
set
  access_policy = case
    when course_type = 'free' and member_level_required = 0 then 'free_all'
    when course_type = 'paid' and member_level_required = 0 then 'paid_all'
    when member_level_required = 1 and price_amount = 0 then 'members_only_free'
    when member_level_required = 2 and price_amount = 0 then 'senior_only_free'
    else access_policy
  end
from products
where products.id = course_product_details.product_id;

update course_product_details
set
  registered_price_amount = case when access_policy in ('paid_all', 'member_free_guest_paid') then products.price_amount else 0 end,
  junior_price_amount = case
    when access_policy = 'paid_all' then products.price_amount
    when access_policy = 'junior_paid_senior_free' then products.price_amount
    else 0
  end,
  senior_price_amount = case when access_policy = 'paid_all' then products.price_amount else 0 end
from products
where products.id = course_product_details.product_id;
