create table if not exists site_page_blocks (
  id uuid primary key default gen_random_uuid(),
  page_key text not null,
  block_key text not null,
  title text not null default '',
  subtitle text not null default '',
  body text not null default '',
  layout text not null default '',
  status text not null default 'published' check (status in ('published', 'hidden')),
  sort_order integer not null default 100,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (page_key, block_key)
);

create table if not exists site_page_block_items (
  id uuid primary key default gen_random_uuid(),
  block_id uuid not null references site_page_blocks(id) on delete cascade,
  item_key text not null default '',
  title text not null default '',
  subtitle text not null default '',
  description text not null default '',
  image_url text not null default '',
  href text not null default '',
  cta_label text not null default '',
  status text not null default 'published' check (status in ('published', 'hidden')),
  sort_order integer not null default 100,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (block_id, item_key)
);

create index if not exists idx_site_page_blocks_page
  on site_page_blocks(page_key, status, sort_order);

create index if not exists idx_site_page_block_items_block
  on site_page_block_items(block_id, status, sort_order);

create table if not exists news_categories (
  code text primary key,
  name text not null unique,
  description text not null default '',
  sort_order integer not null default 100,
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table if exists news_articles
  add column if not exists category_code text not null default '';

alter table if exists news_articles
  drop constraint if exists news_articles_category_check;

insert into news_categories (code, name, description, sort_order, status)
values
  ('policy_interpretation', '政策解读', '政策文件、制度解读与产业机会研判。', 10, 'active'),
  ('institute_news', '院内动态', '研究院动态、成果发布与平台公告。', 20, 'active'),
  ('event_review', '活动回顾', '活动报道、培训复盘与现场纪要。', 30, 'active'),
  ('research_report', '研究报告', '行业研究、专题报告与趋势洞察。', 40, 'active')
on conflict (code) do update set
  name = excluded.name,
  description = excluded.description,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

update news_articles
set category_code = case category
  when '政策解读' then 'policy_interpretation'
  when '活动回顾' then 'event_review'
  when '研究报告' then 'research_report'
  when '院内动态' then 'institute_news'
  else coalesce(nullif(category_code, ''), 'institute_news')
end
where category_code = '';

with upserted as (
  insert into site_page_blocks (page_key, block_key, title, subtitle, body, layout, status, sort_order, metadata)
  values
    ('home', 'partners', '合作单位', '', '', 'marquee', 'published', 10, '{}'::jsonb),
    ('home', 'service_entrances', '按需求进入服务', '', '从前期咨询到学习培训、活动报名、资料购买和智能体交付，按业务场景进入对应服务。', 'service_cards', 'published', 20, '{}'::jsonb),
    ('home', 'agent_workflow', '智能体工作流案例', '从业务问题到持续增长', '一个可复用的增长 workflow，把“找机会、匹配客户、产出内容、触达跟进、复盘优化”串成循环。', 'workflow', 'published', 30, '{"imageAssetKey":"agent_workflow_home"}'::jsonb),
    ('home', 'membership_cards', '会员权益', '按使用频率，选择服务权益', '免费用户先完成浏览和购买；持续学习、咨询或采购时，再选择会员权益降低长期使用成本。', 'membership_cards', 'published', 40, '{}'::jsonb)
  on conflict (page_key, block_key) do update set
    title = excluded.title,
    subtitle = excluded.subtitle,
    body = excluded.body,
    layout = excluded.layout,
    sort_order = excluded.sort_order,
    metadata = site_page_blocks.metadata || excluded.metadata,
    updated_at = now()
  returning id, block_key
)
insert into site_page_block_items (block_id, item_key, title, subtitle, description, image_url, href, cta_label, status, sort_order, metadata)
select b.id, v.item_key, v.title, v.subtitle, v.description, v.image_url, v.href, v.cta_label, 'published', v.sort_order, v.metadata
from upserted b
join (
  values
    ('partners', 'dlut', '大连理工大学', '', '', '', '', '', 10, '{}'::jsonb),
    ('partners', 'dufe', '东北财经大学', '', '', '', '', '', 20, '{}'::jsonb),
    ('partners', 'dlmu', '大连海事大学', '', '', '', '', '', 30, '{}'::jsonb),
    ('partners', 'dl_hightech', '大连高新区', '', '', '', '', '', 40, '{}'::jsonb),
    ('partners', 'software_park', '软件园企业联盟', '', '', '', '', '', 50, '{}'::jsonb),
    ('partners', 'digital_economy', '数字经济协会', '', '', '', '', '', 60, '{}'::jsonb),
    ('partners', 'smart_city', '智慧城市联盟', '', '', '', '', '', 70, '{}'::jsonb),
    ('partners', 'industry_innovation', '产业创新中心', '', '', '', '', '', 80, '{}'::jsonb),
    ('service_entrances', 'consulting', '规划咨询', '', '战略诊断、路径设计与项目转化，适合先把方向和交付边界聊清楚。', '', '/consulting', '查看服务方向', 10, '{"assetKey":"service_consulting","visual":"规划咨询配图：战略研讨、方案共创与产业数字化规划场景"}'::jsonb),
    ('service_entrances', 'courses', '课程培训', '', '面向政府、园区与企业团队的数字化能力课程，免费、会员和付费内容分层进入。', '', '/courses', '浏览课程', 20, '{"assetKey":"service_courses","visual":"课程培训配图：课堂培训、线上学习与企业实训场景"}'::jsonb),
    ('service_entrances', 'events', '活动报名', '', '峰会、沙龙、工作坊与供需对接活动，报名后进入订单和个人中心记录。', '', '/events', '查看活动', 30, '{"assetKey":"service_events","visual":"活动报名配图：论坛现场、沙龙交流与供需对接场景"}'::jsonb),
    ('service_entrances', 'data', '数据服务', '', '提交数据主题、地区范围、口径和交付格式，业务员沟通报价后进入定制服务。', '', '/data', '我要找数据', 40, '{"assetKey":"service_data","visual":"数据服务配图：报告封面、数据看板与政策资料库场景"}'::jsonb),
    ('service_entrances', 'agents', '智能体产品', '', '把研究院的方法论封装成可购买、可配置、可追踪的业务智能体。', '', '/agents', '进入产品库', 50, '{"assetKey":"service_agents","visual":"智能体产品配图：多智能体协作、任务流转与交付看板场景"}'::jsonb),
    ('agent_workflow', 'problem', '业务问题', '问题', '研究院每周都要发现政策热点、找到适合触达的园区和企业、产出内容、组织活动或咨询转化。人工做会变成到处找资料、临时写稿、线索无人跟进。', '', '', '', 10, '{"x":150,"y":275,"size":"wide"}'::jsonb),
    ('agent_workflow', 'agent_0', '任务管家智能体', '智能体', '拆任务、分配智能体、盯节点、提示词优化。', '', '', '', 20, '{"x":430,"y":125,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_1', '情报搜索智能体', '智能体', '持续抓取政策、产业新闻、招标公告和园区动态，找出本周机会点。', '', '', '', 30, '{"x":670,"y":325,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_2', '客户画像智能体', '智能体', '根据行业、区域、规模和数字化阶段匹配潜在客户与合作单位。', '', '', '', 40, '{"x":910,"y":125,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_3', '策略推演智能体', '智能体', '判断哪些机会适合做咨询、课程、活动或数据服务转化。', '', '', '', 50, '{"x":1150,"y":325,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_4', '内容智能体', '智能体', '生成文章、邀约话术、活动主题、方案摘要和销售材料。', '', '', '', 60, '{"x":1390,"y":125,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_5', '运营触达智能体', '智能体', '安排触达节奏，记录谁该跟、什么时候跟、跟进到哪一步。', '', '', '', 70, '{"x":1630,"y":325,"size":"normal"}'::jsonb),
    ('agent_workflow', 'agent_6', '复盘优化智能体', '智能体', '统计打开、报名、咨询、成交数据，反向优化下一轮选题和客群。', '', '', '', 80, '{"x":1870,"y":125,"size":"normal"}'::jsonb),
    ('agent_workflow', 'result', '循环结果', '结果', '结果不是一次性材料，而是一套每周都能重复运行的获客与转化流水线。', '', '/agents', '查看完整智能体页面', 90, '{"x":2150,"y":275,"size":"wide"}'::jsonb),
    ('membership_cards', 'registered', '注册用户', '免费', '浏览公开内容、参加免费活动，按需购买服务。', '', '/login', '查看方案', 10, '{"tone":"base"}'::jsonb),
    ('membership_cards', 'junior', '初级会员', '¥99/月', '会员课程与活动，全站交易 9 折，月度 AI 行业简报。', '', '/membership', '查看方案', 20, '{"tone":"brand","membershipTier":"junior"}'::jsonb),
    ('membership_cards', 'senior', '高级会员', '¥299/月', '高端课程沙龙，全站交易 8 折，专属客户经理。', '', '/membership', '查看方案', 30, '{"tone":"gold","membershipTier":"senior"}'::jsonb)
) as v(block_key, item_key, title, subtitle, description, image_url, href, cta_label, sort_order, metadata)
  on v.block_key = b.block_key
on conflict (block_id, item_key) do update set
  title = excluded.title,
  subtitle = excluded.subtitle,
  description = excluded.description,
  image_url = excluded.image_url,
  href = excluded.href,
  cta_label = excluded.cta_label,
  sort_order = excluded.sort_order,
  metadata = site_page_block_items.metadata || excluded.metadata,
  updated_at = now();

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