create table if not exists event_product_details (
  product_id text primary key references products(id) on delete cascade,
  event_type text not null default 'public',
  topic text not null default '峰会论坛',
  event_format text not null default '线下活动',
  cover_url text not null default '',
  cover_label text not null default '',
  audience text not null default '',
  highlights text[] not null default '{}',
  agenda text[] not null default '{}',
  starts_at timestamptz,
  ends_at timestamptz,
  location text not null default '',
  is_online boolean not null default false,
  online_url text not null default '',
  capacity integer not null default 0,
  initial_registered_count integer not null default 0,
  registered_access text not null default 'paid',
  junior_access text not null default 'paid',
  senior_access text not null default 'paid',
  registered_price_amount integer not null default 0,
  junior_price_amount integer not null default 0,
  senior_price_amount integer not null default 0,
  member_level_required integer not null default 0,
  recommended boolean not null default false,
  sort_order integer not null default 10,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists event_registrations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  product_id text not null references products(id),
  order_id uuid references orders(id) on delete cascade,
  entitlement_id uuid references entitlements(id) on delete set null,
  checkin_code text not null unique,
  status text not null default 'registered',
  checked_in_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists idx_event_registrations_user_product_active
  on event_registrations(user_id, product_id)
  where status <> 'cancelled';

create index if not exists idx_event_registrations_product_status
  on event_registrations(product_id, status, created_at desc);

alter table event_product_details
  drop constraint if exists event_product_details_event_type_check,
  drop constraint if exists event_product_details_registered_access_check,
  drop constraint if exists event_product_details_junior_access_check,
  drop constraint if exists event_product_details_senior_access_check,
  drop constraint if exists event_product_details_status_check;

alter table event_product_details
  add constraint event_product_details_event_type_check check (event_type in ('public', 'pro', 'max', 'free')),
  add constraint event_product_details_registered_access_check check (registered_access in ('closed', 'free', 'paid')),
  add constraint event_product_details_junior_access_check check (junior_access in ('closed', 'free', 'paid')),
  add constraint event_product_details_senior_access_check check (senior_access in ('closed', 'free', 'paid')),
  add constraint event_product_details_status_check check (status in ('draft', 'published', 'offline', 'deleted'));

alter table event_registrations
  drop constraint if exists event_registrations_status_check;

alter table event_registrations
  add constraint event_registrations_status_check check (status in ('registered', 'checked_in', 'cancelled'));

update products
set status = 'offline',
    updated_at = now()
where id = 'event-bigdata-summit'
  and type = 'event';

insert into event_product_details (
  product_id, event_type, topic, event_format, cover_label, audience,
  highlights, agenda, starts_at, ends_at, location, is_online, online_url,
  capacity, initial_registered_count, registered_access, junior_access, senior_access,
  registered_price_amount, junior_price_amount, senior_price_amount, member_level_required,
  recommended, sort_order, status
)
values
  (
    'event-2026-dalian-bigdata-summit', 'free', '峰会论坛', '线下活动',
    '活动封面：大数据与数字经济峰会、主旨演讲和产业对接现场',
    '适合政府部门、园区平台、产业企业、数字经济服务机构和项目合作伙伴参加。',
    array['数字经济与数据要素趋势分享','城市数字化建设案例交流','政产学研嘉宾圆桌对话','重点项目与服务资源对接'],
    array['嘉宾签到与开场致辞','主旨演讲与政策趋势解读','圆桌对话和案例分享','产业项目交流与供需对接'],
    '2026-06-28T09:00:00+08:00', '2026-06-28T18:00:00+08:00', '大连国际会议中心', false, '',
    500, 387, 'free', 'free', 'free', 0, 0, 0, 0, true, 10, 'published'
  ),
  (
    'event-data-security-workshop-jun', 'pro', '数据安全', '工作坊',
    '活动封面：数据安全合规工作坊、案例演练和分组实操场景',
    '适合数据安全、法务合规、信息化、数据治理和业务系统负责人参加。',
    array['数据分级分类实操演练','安全评估和合规流程拆解','真实案例复盘','形成企业自查清单'],
    array['法规要求与风险场景导入','分级分类和权限管理演练','安全评估与应急响应案例','企业合规清单整理'],
    '2026-06-15T13:30:00+08:00', '2026-06-15T17:30:00+08:00', '大连市大数据研究院', false, '',
    40, 36, 'closed', 'free', 'free', 0, 0, 0, 1, true, 20, 'published'
  ),
  (
    'event-ai-application-webinar-jun', 'free', 'AI应用', '线上活动',
    '活动封面：AI 大模型线上研讨会、在线直播和专家答疑场景',
    '适合关注 AI 落地、政企数字化、业务创新和智能体应用的管理者参加。',
    array['政务、教育、医疗场景案例','大模型落地路径和边界','应用试点经验分享','专家在线答疑'],
    array['线上开场和主题导入','典型行业应用案例分享','落地风险和治理建议','在线问答与资料发放'],
    '2026-06-12T19:00:00+08:00', '2026-06-12T21:00:00+08:00', '线上', true, '',
    1000, 634, 'free', 'free', 'free', 0, 0, 0, 0, true, 30, 'published'
  ),
  (
    'event-smart-manufacturing-tour', 'max', '企业参访', '参访交流',
    '活动封面：智能制造标杆企业参访、产线观摩和小班交流场景',
    '适合制造企业、园区运营方、产业数字化服务团队和项目投资人员参加。',
    array['标杆企业生产线参观','数字化改造路径讲解','小班闭门交流','产业合作机会对接'],
    array['集合签到与行程说明','标杆产线参观','企业负责人专题分享','小班交流和合作讨论'],
    '2026-07-08T09:00:00+08:00', '2026-07-08T16:00:00+08:00', '大连开发区', false, '',
    20, 12, 'closed', 'closed', 'free', 0, 0, 0, 2, true, 40, 'published'
  ),
  (
    'event-data-asset-valuation-seminar', 'public', '政策研讨', '线下活动',
    '活动封面：数据资产入表政策研讨、圆桌交流和实务解读场景',
    '适合企业财务、数据管理、战略投资、法务合规和政策研究人员参加。',
    array['数据资产入表政策解读','财务和法律视角拆解','企业资产化案例分享','实操路径和风险提示'],
    array['政策背景和规则解读','数据资产确认与计量讨论','企业案例与难点答疑','交流讨论和后续服务对接'],
    '2026-07-15T14:00:00+08:00', '2026-07-15T17:00:00+08:00', '大连希尔顿酒店', false, '',
    150, 89, 'paid', 'paid', 'paid', 20000, 18000, 16000, 0, false, 50, 'published'
  )
on conflict (product_id) do update set
  event_type = excluded.event_type,
  topic = excluded.topic,
  event_format = excluded.event_format,
  cover_label = excluded.cover_label,
  audience = excluded.audience,
  highlights = excluded.highlights,
  agenda = excluded.agenda,
  starts_at = excluded.starts_at,
  ends_at = excluded.ends_at,
  location = excluded.location,
  is_online = excluded.is_online,
  online_url = excluded.online_url,
  capacity = excluded.capacity,
  initial_registered_count = excluded.initial_registered_count,
  registered_access = excluded.registered_access,
  junior_access = excluded.junior_access,
  senior_access = excluded.senior_access,
  registered_price_amount = excluded.registered_price_amount,
  junior_price_amount = excluded.junior_price_amount,
  senior_price_amount = excluded.senior_price_amount,
  member_level_required = excluded.member_level_required,
  recommended = excluded.recommended,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

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