create table if not exists membership_plans (
  product_id text primary key references products(id) on delete cascade,
  level text not null unique,
  name text not null,
  badge text not null default '',
  description text not null default '',
  cta_label text not null default '',
  tone text not null default 'brand',
  monthly_price_amount integer not null default 0,
  yearly_price_amount integer not null default 0,
  recommended boolean not null default false,
  sort_order integer not null default 0,
  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 membership_benefit_groups (
  id text primary key,
  plan_product_id text not null references membership_plans(product_id) on delete cascade,
  title text not null,
  sort_order integer not null default 0
);

create table if not exists membership_benefit_items (
  id text primary key,
  group_id text not null references membership_benefit_groups(id) on delete cascade,
  label text not null,
  sort_order integer not null default 0
);

create table if not exists membership_compare_rows (
  id text primary key,
  label text not null,
  registered_value text not null default '',
  junior_value text not null default '',
  senior_value text not null default '',
  sort_order integer not null default 0
);

create table if not exists membership_faqs (
  id text primary key,
  question text not null,
  answer text not null,
  sort_order integer not null default 0,
  status text not null default 'published'
);

create table if not exists agent_categories (
  id text primary key,
  name text not null unique,
  sort_order integer not null default 0,
  status text not null default 'published'
);

create table if not exists agent_product_details (
  product_id text primary key references products(id) on delete cascade,
  cover_url text not null default '',
  scenario_tags text[] not null default '{}',
  detail_description text not null default '',
  delivery_note text not null default '',
  redeemable_by_member boolean not null default false,
  recommended boolean not null default false,
  sort_order integer not null default 0,
  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 agent_workflow_cases (
  id text primary key,
  title text not null,
  scene text not null,
  result text not null,
  sort_order integer not null default 0,
  status text not null default 'published'
);

create table if not exists agent_workflow_nodes (
  id text primary key,
  case_id text not null references agent_workflow_cases(id) on delete cascade,
  label text not null,
  title text not null,
  detail text not null,
  x integer not null default 0,
  y integer not null default 0,
  size text not null default 'normal',
  sort_order integer not null default 0
);

create table if not exists material_templates (
  id text primary key,
  product_id text not null references products(id) on delete cascade,
  version integer not null default 1,
  fields_json jsonb not null default '[]'::jsonb,
  required_files_json jsonb not null default '[]'::jsonb,
  status text not null default 'published',
  created_at timestamptz not null default now(),
  unique(product_id, version)
);

create or replace function agent_product_json(p products, d agent_product_details)
returns json
language sql
stable
as $$
  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
  );
$$;

insert into products (id, slug, type, title, summary, price_amount, purchase_flow, fulfillment_flow, status)
values
  ('membership-junior', 'membership-junior', 'membership', '初级会员', '适合持续学习、定期参加活动并需要专家咨询折扣的专业用户。', 9900, 'member_checkout', 'entitlement', 'published'),
  ('membership-senior', 'membership-senior', 'membership', '高级会员', '适合高频咨询、参与高端闭门活动并需要专属对接的机构客户。', 29900, 'member_checkout', 'entitlement', 'published')
on conflict (id) do update set
  title = excluded.title,
  summary = excluded.summary,
  price_amount = excluded.price_amount,
  status = excluded.status,
  updated_at = now();

insert into membership_plans (product_id, level, name, badge, description, cta_label, tone, monthly_price_amount, yearly_price_amount, recommended, sort_order, status)
values
  ('membership-junior', 'junior', '初级会员', '推荐', '适合持续学习、定期参加活动并需要专家咨询折扣的专业用户。', '开通初级会员', 'brand', 9900, 99800, true, 10, 'published'),
  ('membership-senior', 'senior', '高级会员', '高级', '适合高频咨询、参与高端闭门活动并需要专属对接的机构客户。', '开通高级会员', 'gold', 29900, 298800, false, 20, 'published')
on conflict (product_id) do update set
  name = excluded.name,
  badge = excluded.badge,
  description = excluded.description,
  cta_label = excluded.cta_label,
  tone = excluded.tone,
  monthly_price_amount = excluded.monthly_price_amount,
  yearly_price_amount = excluded.yearly_price_amount,
  recommended = excluded.recommended,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

insert into membership_benefit_groups (id, plan_product_id, title, sort_order)
values
  ('junior-basic', 'membership-junior', '基础访问', 10),
  ('junior-products', 'membership-junior', '专家与产品', 20),
  ('junior-service', 'membership-junior', '专属服务', 30),
  ('senior-basic', 'membership-senior', '基础访问', 10),
  ('senior-products', 'membership-senior', '专家与产品', 20),
  ('senior-service', 'membership-senior', '专属服务', 30)
on conflict (id) do update set title = excluded.title, sort_order = excluded.sort_order;

insert into membership_benefit_items (id, group_id, label, sort_order)
values
  ('junior-basic-1', 'junior-basic', '全部注册用户权益', 10),
  ('junior-basic-2', 'junior-basic', '会员专属课程', 20),
  ('junior-basic-3', 'junior-basic', '会员专属活动', 30),
  ('junior-products-1', 'junior-products', '专家咨询 9 折', 10),
  ('junior-products-2', 'junior-products', '数据产品 9 折', 20),
  ('junior-products-3', 'junior-products', '智能体产品 9 折', 30),
  ('junior-service-1', 'junior-service', '每月 AI 行业报告', 10),
  ('junior-service-2', 'junior-service', '每月赠送智能体名额', 20),
  ('junior-service-3', 'junior-service', '会员专属服务群', 30),
  ('senior-basic-1', 'senior-basic', '全部初级会员权益', 10),
  ('senior-basic-2', 'senior-basic', '高级会员专属高端课程', 20),
  ('senior-basic-3', 'senior-basic', '高级会员专属高端沙龙', 30),
  ('senior-products-1', 'senior-products', '专家咨询 8 折', 10),
  ('senior-products-2', 'senior-products', '智能体产品 8 折', 20),
  ('senior-products-3', 'senior-products', '优先专家预约', 30),
  ('senior-service-1', 'senior-service', '专属客户经理', 10),
  ('senior-service-2', 'senior-service', '优先客服响应', 20),
  ('senior-service-3', 'senior-service', '年度峰会优先席位', 30)
on conflict (id) do update set label = excluded.label, sort_order = excluded.sort_order;

insert into membership_compare_rows (id, label, registered_value, junior_value, senior_value, sort_order)
values
  ('case-access', '浏览案例库完整内容', 'true', 'true', 'true', 10),
  ('free-events', '参加免费活动', 'true', 'true', 'true', 20),
  ('member-courses', '会员专属课程', 'false', 'true', 'true', 30),
  ('senior-courses', '高级会员专属高端课程', 'false', 'false', 'true', 40),
  ('course-discount', '付费课程折扣', '原价', '9 折', '8 折', 50),
  ('event-discount', '付费活动折扣', '原价', '9 折', '8 折', 60),
  ('expert-discount', '专家咨询折扣', '原价', '9 折', '8 折', 70),
  ('agent-discount', '智能体产品折扣', '原价', '9 折', '8 折', 80),
  ('ai-report', '每月 AI 行业报告', 'false', 'true', 'true', 90),
  ('expert-priority', '优先专家预约', 'false', 'false', 'true', 100),
  ('manager', '专属客户经理', 'false', 'false', 'true', 110)
on conflict (id) do update set
  label = excluded.label,
  registered_value = excluded.registered_value,
  junior_value = excluded.junior_value,
  senior_value = excluded.senior_value,
  sort_order = excluded.sort_order;

insert into membership_faqs (id, question, answer, sort_order, status)
values
  ('subscription-cart', '会员订阅是否进入购物车？', '不进入购物车。会员订阅采用即时单独支付流程，支付成功后立即激活。', 10, 'published'),
  ('upgrade', '初级会员可以升级到高级会员吗？', '可以。升级时系统会按初级会员剩余有效天数折算补差价，不需要重复购买整期。', 20, 'published'),
  ('expire-reminder', '会员到期如何提醒？', '系统会在到期前 7 天通过站内信、短信或服务通知提醒，可手动续费或后续开启自动续费。', 30, 'published'),
  ('organization-buy', '机构能否统一采购？', '支持机构批量采购和统一开票，后台会保留组织、成员、订单与发票字段。', 40, 'published')
on conflict (id) do update set question = excluded.question, answer = excluded.answer, sort_order = excluded.sort_order, status = excluded.status;

insert into agent_categories (id, name, sort_order, status)
values
  ('planning', '规划咨询', 10, 'published'),
  ('policy', '政策研究', 20, 'published'),
  ('procurement', '采购决策', 30, 'published'),
  ('governance', '数据治理', 40, 'published'),
  ('security', '数据安全', 50, 'published'),
  ('industry', '产业服务', 60, 'published')
on conflict (id) do update set name = excluded.name, sort_order = excluded.sort_order, status = excluded.status;

insert into products (id, slug, type, title, summary, price_amount, purchase_flow, fulfillment_flow, status)
values
  ('agent-data-planning-report-agent', 'data-planning-report-agent', 'agent', '大数据规划报告生成智能体', '基于研究院积累的规划方法论，自动生成符合国家标准的城市/企业大数据发展规划报告初稿，支持多轮迭代优化。', 498000, 'cart', 'task', 'published'),
  ('agent-policy-monitoring-agent', 'policy-monitoring-agent', 'agent', '数字政策监测与解读智能体', '持续监控国家及地方数字化政策动态，自动推送与您业务相关的政策摘要，并提供对企业影响的初步解读分析。', 198000, 'cart', 'task', 'published'),
  ('agent-vendor-procurement-agent', 'vendor-procurement-agent', 'agent', '数字化采购辅助决策智能体', '输入采购需求，智能体自动从供应商数据库中筛选匹配方案，生成对比分析报告，辅助采购委员会决策。', 298000, 'cart', 'task', 'published'),
  ('agent-data-compliance-audit-agent', 'data-compliance-audit-agent', 'agent', '数据合规自检智能体', '依据《数据安全法》《个人信息保护法》等法规，对企业数据处理活动进行自动化合规检查，生成问题清单与整改建议。', 0, 'cart', 'task', 'published'),
  ('agent-industrial-policy-matching-agent', 'industrial-policy-matching-agent', 'agent', '产业政策匹配智能体', '根据企业行业、规模与项目方向匹配可申报政策，形成政策清单、适配理由与申报提醒。', 128000, 'cart', 'task', 'published'),
  ('agent-park-operation-briefing-agent', 'park-operation-briefing-agent', 'agent', '园区运营简报智能体', '面向园区运营团队，将企业数据、活动数据和招商进展转化为月度运营简报。', 268000, 'cart', 'task', 'published'),
  ('agent-smart-city-task-agent', 'smart-city-task-agent', 'agent', '智慧城市任务拆解智能体', '把智慧城市建设目标拆解为任务、责任单位、阶段节点与交付材料清单。', 398000, 'cart', 'task', 'published'),
  ('agent-data-asset-inventory-agent', 'data-asset-inventory-agent', 'agent', '数据资产盘点智能体', '辅助梳理数据资源目录、字段说明、使用场景和资产化准备情况。', 298000, 'cart', 'task', 'published'),
  ('agent-training-outline-agent', 'training-outline-agent', 'agent', '培训大纲生成智能体', '按培训对象、课时和主题生成数字化培训大纲、课程结构与案例建议。', 98000, 'cart', 'task', 'published'),
  ('agent-meeting-summary-agent', 'meeting-summary-agent', 'agent', '会议纪要整理智能体', '整理会议录音或纪要要点，形成行动项、责任人和后续跟进清单。', 68000, 'cart', 'task', 'published')
on conflict (id) do update set
  slug = excluded.slug,
  title = excluded.title,
  summary = excluded.summary,
  price_amount = excluded.price_amount,
  status = excluded.status,
  updated_at = now();

insert into agent_product_details (product_id, cover_url, scenario_tags, detail_description, delivery_note, redeemable_by_member, recommended, sort_order, status)
values
  ('agent-data-planning-report-agent', '', array['规划咨询','报告生成','政府机构'], '面向城市、园区和企业数字化规划场景，结合研究院方法论生成结构化规划报告初稿。', '提交需求问卷后，智能体24小时内生成初稿，人工专家3个工作日内完成审核并交付终稿。', true, true, 10, 'published'),
  ('agent-policy-monitoring-agent', '', array['政策研究','实时监测','企业合规'], '持续监测国家和地方数字化政策，形成摘要、影响分析和行动建议。', '订阅后即可使用，政策更新后2小时内推送解读报告至您的账户。', true, true, 20, 'published'),
  ('agent-vendor-procurement-agent', '', array['采购决策','供应商评估','政企机构'], '围绕采购需求筛选供应商和方案，输出对比分析报告。', '提交采购需求说明后，智能体1个工作日内生成供应商对比分析报告。', false, false, 30, 'published'),
  ('agent-data-compliance-audit-agent', '', array['数据安全','合规审查','企业自查'], '对数据处理活动进行合规自查，形成风险清单和整改建议。', '填写自检问卷后，实时生成合规诊断报告，可免费使用基础版。', false, false, 40, 'published'),
  ('agent-industrial-policy-matching-agent', '', array['政策研究','产业服务','企业申报'], '根据企业基础信息匹配可申报政策，输出适配理由和提醒。', '购买后填写企业基础信息，系统生成匹配结果，顾问团队保留人工复核入口。', true, false, 50, 'published'),
  ('agent-park-operation-briefing-agent', '', array['园区运营','数据看板','月报生成'], '把园区运营数据转化为可汇报的月度简报。', '购买后提交数据字段模板，团队协助完成简报结构配置。', false, false, 60, 'published'),
  ('agent-smart-city-task-agent', '', array['城市治理','任务拆解','项目管理'], '将智慧城市建设目标拆成任务、节点和交付材料。', '提交建设目标与现有材料后，生成任务拆解表和交付节点建议。', true, false, 70, 'published'),
  ('agent-data-asset-inventory-agent', '', array['数据资产','数据治理','目录编制'], '辅助梳理数据资源目录、字段说明和资产化准备情况。', '购买后上传或录入目录字段，由团队协助校准分类口径。', false, false, 80, 'published'),
  ('agent-training-outline-agent', '', array['培训课程','知识体系','内容设计'], '按对象、课时和主题生成数字化培训大纲。', '填写培训对象与主题后生成大纲，可由课程团队继续优化。', true, false, 90, 'published'),
  ('agent-meeting-summary-agent', '', array['会议纪要','行动项','协同办公'], '整理会议内容并产出行动项和跟进清单。', '上传会议材料后生成纪要初稿，支持人工复核。', false, false, 100, 'published')
on conflict (product_id) do update set
  cover_url = excluded.cover_url,
  scenario_tags = excluded.scenario_tags,
  detail_description = excluded.detail_description,
  delivery_note = excluded.delivery_note,
  redeemable_by_member = excluded.redeemable_by_member,
  recommended = excluded.recommended,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

insert into agent_workflow_cases (id, title, scene, result, sort_order, status)
values (
  'agent-growth-workflow',
  '智能体工作流案例',
  '研究院每周都要发现政策热点、找到适合触达的园区和企业、产出内容、组织活动或咨询转化。一个可复用的增长 workflow，把“找机会、匹配客户、产出内容、触达跟进、复盘优化”串成循环。',
  '机会、客户、内容、触达、复盘持续更新，形成可反复运行的业务增长流。',
  10,
  'published'
)
on conflict (id) do update set title = excluded.title, scene = excluded.scene, result = excluded.result, sort_order = excluded.sort_order, status = excluded.status;

insert into material_templates (id, product_id, version, fields_json, required_files_json, status)
select 'material-' || p.id, p.id, 1,
  '[{"name":"organization","label":"使用机构","type":"text","required":true},{"name":"contact","label":"联系手机号","type":"phone","required":true},{"name":"background","label":"业务场景 / 配置要求","type":"textarea","required":true}]'::jsonb,
  '[{"label":"需求文档、样表或业务说明","required":false}]'::jsonb,
  'published'
from products p
where p.type = 'agent'
on conflict (product_id, version) do update set
  fields_json = excluded.fields_json,
  required_files_json = excluded.required_files_json,
  status = excluded.status;
