-- Agent 商品支持三档访问权限与分档价格（未登录按注册用户口径）
alter table if exists agent_product_details
  add column if not exists registered_access text not null default 'paid',
  add column if not exists junior_access text not null default 'paid',
  add column if not exists senior_access text not null default 'paid',
  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 detail_description text not null default '';

update agent_product_details
set
  registered_access = case when registered_access in ('closed', 'free', 'paid') then registered_access else 'paid' end,
  junior_access = case when junior_access in ('closed', 'free', 'paid') then junior_access else 'paid' end,
  senior_access = case when senior_access in ('closed', 'free', 'paid') then senior_access else 'paid' end
where registered_access is not null;

update agent_product_details
set
  registered_price_amount = case
    when coalesce(registered_access, 'paid') = 'paid' and coalesce(registered_price_amount, 0) = 0
      then (select price_amount from products where products.id = agent_product_details.product_id)
    else coalesce(registered_price_amount, 0)
  end,
  junior_price_amount = case
    when coalesce(junior_access, 'paid') = 'paid' and coalesce(junior_price_amount, 0) = 0
      then (select price_amount from products where products.id = agent_product_details.product_id)
    else coalesce(junior_price_amount, 0)
  end,
  senior_price_amount = case
    when coalesce(senior_access, 'paid') = 'paid' and coalesce(senior_price_amount, 0) = 0
      then (select price_amount from products where products.id = agent_product_details.product_id)
    else coalesce(senior_price_amount, 0)
  end
where registered_access in ('paid', 'free', 'closed')
  or junior_access in ('paid', 'free', 'closed')
  or senior_access in ('paid', 'free', 'closed');

update products p
set price_amount = coalesce((
  select min(price_amount)
  from (
    values
      (case when ap.registered_access = 'closed' then null when ap.registered_access = 'free' then 0 else ap.registered_price_amount end),
      (case when ap.junior_access = 'closed' then null when ap.junior_access = 'free' then 0 else ap.junior_price_amount end),
      (case when ap.senior_access = 'closed' then null when ap.senior_access = 'free' then 0 else ap.senior_price_amount end)
  ) as prices(price_amount)
), 0),
  updated_at = now()
from agent_product_details ap
where ap.product_id = p.id
  and p.type = 'agent';

update agent_product_details
set
  detail_description = coalesce(nullif(detail_description, ''), '购买后在个人中心提交资料，团队会进入需求审核、配置和交付验收流程。');

create or replace function agent_product_json(p products, d agent_product_details)
returns json
language sql
stable
as $function$
  select json_build_object(
    'id', p.id,
    'slug', p.slug,
    'type', p.type,
    'title', p.title,
    'summary', p.summary,
    'priceAmount', p.price_amount,
    'purchaseFlow', p.purchase_flow,
    'fulfillmentFlow', p.fulfillment_flow,
    'status', p.status,
    'coverUrl', d.cover_url,
    'scenarioTags', to_json(d.scenario_tags),
    'detailDescription', d.detail_description,
    'deliveryNote', d.delivery_note,
    'redeemableByMember', d.redeemable_by_member,
    'recommended', d.recommended,
    'sortOrder', d.sort_order,
    'registeredAccess', d.registered_access,
    'juniorAccess', d.junior_access,
    'seniorAccess', d.senior_access,
    'registeredPriceAmount', d.registered_price_amount,
    'juniorPriceAmount', d.junior_price_amount,
    'seniorPriceAmount', d.senior_price_amount
  );
$function$;

alter table agent_product_details
  drop constraint if exists agent_product_details_registered_access_check,
  drop constraint if exists agent_product_details_junior_access_check,
  drop constraint if exists agent_product_details_senior_access_check;

alter table agent_product_details
  add constraint agent_product_details_registered_access_check check (registered_access in ('closed', 'free', 'paid')),
  add constraint agent_product_details_junior_access_check check (junior_access in ('closed', 'free', 'paid')),
  add constraint agent_product_details_senior_access_check check (senior_access in ('closed', 'free', 'paid'));
