create table if not exists notification_templates (
  template_key text primary key,
  scene text not null,
  category text not null default 'platform',
  title_template text not null,
  body_template text not null,
  action_url_template text not null default '',
  default_channels text[] not null default array['in_app']::text[],
  priority text not null default 'normal' check (priority in ('low', 'normal', 'high', 'urgent')),
  variables_schema jsonb not null default '{}'::jsonb,
  status text not null default 'active' check (status in ('active', 'disabled')),
  sort_order integer not null default 100,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_notification_templates_scene
  on notification_templates(scene, status, sort_order);

create table if not exists notification_batches (
  id uuid primary key default gen_random_uuid(),
  batch_no text not null unique,
  title text not null,
  body text not null,
  action_url text not null default '',
  target_type text not null default 'all' check (target_type in (
    'all', 'user_ids', 'membership_level', 'order_status', 'service_status',
    'course_registrations', 'event_registrations', 'experts'
  )),
  target_filters jsonb not null default '{}'::jsonb,
  channels text[] not null default array['in_app']::text[],
  status text not null default 'draft' check (status in ('draft', 'scheduled', 'sending', 'sent', 'failed', 'cancelled')),
  scheduled_at timestamptz,
  sent_at timestamptz,
  recipient_count integer not null default 0,
  sent_count integer not null default 0,
  skipped_count integer not null default 0,
  created_by text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_notification_batches_status_time
  on notification_batches(status, created_at desc);

alter table if exists notification_messages
  add column if not exists batch_id uuid references notification_batches(id) on delete set null,
  add column if not exists priority text not null default 'normal' check (priority in ('low', 'normal', 'high', 'urgent'));

create index if not exists idx_notification_messages_batch
  on notification_messages(batch_id, created_at desc);

insert into notification_templates (
  template_key, scene, category, title_template, body_template, action_url_template,
  default_channels, priority, variables_schema, sort_order
)
values
  ('profile_incomplete', 'account_profile', 'account', '请完善账号资料', '完善姓名、单位和联系方式后，平台可以更准确地为你匹配服务。', '/account/settings', array['in_app']::text[], 'normal', '{"required":["displayName"]}'::jsonb, 10),
  ('account_wechat_bound', 'account_security', 'account', '微信号已更新', '你的微信联系信息已更新，如非本人操作请及时联系平台。', '/account/settings', array['in_app']::text[], 'high', '{}'::jsonb, 11),
  ('account_closed', 'account_security', 'account', '账号已注销', '账号资料已按你的申请清理，历史订单和服务记录会按履约与财务要求保留。', '/', array['in_app']::text[], 'urgent', '{}'::jsonb, 12),
  ('ai_permissions_updated', 'account_security', 'account', 'AI 助手授权已更新', '你已更新 AI 助手可读取或辅助的范围。', '/account/settings', array['in_app']::text[], 'normal', '{}'::jsonb, 13),

  ('order_pending_payment', 'order', 'order', '订单 {{orderNo}} 待支付', '{{itemTitle}} 还未完成支付，请在订单页手动确认。', '/account/orders/{{orderNo}}', array['in_app']::text[], 'high', '{"required":["orderNo","itemTitle"]}'::jsonb, 100),
  ('order_paid', 'order', 'order', '订单 {{orderNo}} 支付成功', '{{itemTitle}} 已支付成功，可在个人中心查看履约进度。', '/account/orders/{{orderNo}}', array['in_app']::text[], 'normal', '{"required":["orderNo","itemTitle"]}'::jsonb, 101),
  ('order_closed', 'order', 'order', '订单 {{orderNo}} 已关闭', '{{itemTitle}} 的订单已关闭，如仍需服务可重新下单。', '/account/orders/{{orderNo}}', array['in_app']::text[], 'normal', '{"required":["orderNo","itemTitle"]}'::jsonb, 102),
  ('payment_failed', 'payment', 'order', '支付未完成', '{{itemTitle}} 支付未完成，请在订单页重新发起支付。', '/account/orders/{{orderNo}}', array['in_app']::text[], 'high', '{"required":["orderNo","itemTitle"]}'::jsonb, 103),

  ('membership_activated', 'membership', 'membership', '{{tierLabel}}已开通', '会员权益已生效，可查看专属折扣、内容和服务额度。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["tierLabel"]}'::jsonb, 200),
  ('membership_renewed', 'membership', 'membership', '{{tierLabel}}已续费', '续费已完成，会员有效期已更新。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["tierLabel"]}'::jsonb, 201),
  ('membership_upgraded', 'membership', 'membership', '会员已升级为{{tierLabel}}', '升级已完成，新权益已生效。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["tierLabel"]}'::jsonb, 202),
  ('membership_expiring_d7', 'membership_expiry', 'membership', '{{tierLabel}}将在 7 天后到期', '续费后会员权益会连续保留，不影响后续课程、活动和专家预约使用。', '/account/membership', array['in_app']::text[], 'high', '{"required":["tierLabel","expiresAt"]}'::jsonb, 203),
  ('membership_expiring_d3', 'membership_expiry', 'membership', '{{tierLabel}}将在 3 天后到期', '续费后会员权益会连续保留，不影响后续课程、活动和专家预约使用。', '/account/membership', array['in_app']::text[], 'high', '{"required":["tierLabel","expiresAt"]}'::jsonb, 204),
  ('membership_expiring_d1', 'membership_expiry', 'membership', '{{tierLabel}}将在 1 天后到期', '建议今天完成续费，避免明天权益中断。', '/account/membership', array['in_app']::text[], 'urgent', '{"required":["tierLabel","expiresAt"]}'::jsonb, 205),
  ('membership_expiring_today', 'membership_expiry', 'membership', '{{tierLabel}}今天到期', '今天未续费将停止会员折扣、专属活动和优先预约等权益。', '/account/membership', array['in_app']::text[], 'urgent', '{"required":["tierLabel","expiresAt"]}'::jsonb, 206),
  ('membership_expired', 'membership_expiry', 'membership', '{{tierLabel}}已到期', '会员折扣、专属课程活动和优先预约等权益已停止，可重新开通继续使用。', '/account/membership', array['in_app']::text[], 'high', '{"required":["tierLabel"]}'::jsonb, 207),
  ('membership_downgraded', 'membership', 'membership', '会员权益已调整', '会员到期后权益已调整，可重新开通继续使用专属权益。', '/account/membership', array['in_app']::text[], 'high', '{}'::jsonb, 208),
  ('membership_manual_granted', 'membership', 'membership', '{{tierLabel}}已开通', '平台已为你开通会员权益，可在会员中心查看。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["tierLabel"]}'::jsonb, 209),
  ('membership_manager_assigned', 'membership', 'membership', '专属服务经理已分配', '{{managerName}} 将协助你使用会员权益和服务资源。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["managerName"]}'::jsonb, 210),
  ('membership_content_published', 'membership_content', 'membership', '会员内容已更新', '{{title}} 已发布，可前往会员中心查看。', '/account/membership', array['in_app']::text[], 'normal', '{"required":["title"]}'::jsonb, 211),
  ('membership_winback_d1', 'membership_winback', 'membership', '会员权益可重新开通', '重新开通后可继续使用会员折扣、专属内容和优先服务。', '/account/membership', array['in_app']::text[], 'normal', '{}'::jsonb, 212),
  ('membership_winback_d7', 'membership_winback', 'membership', '会员权益可重新开通', '重新开通后可继续使用会员折扣、专属内容和优先服务。', '/account/membership', array['in_app']::text[], 'normal', '{}'::jsonb, 213),
  ('membership_winback_d30', 'membership_winback', 'membership', '会员权益可重新开通', '近期课程、活动和专家服务仍可使用会员价，可按需要重新开通。', '/account/membership', array['in_app']::text[], 'normal', '{}'::jsonb, 214),

  ('consulting_submitted', 'consulting', 'service', '规划咨询需求已提交', '{{direction}} 已提交，顾问会尽快联系你。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["direction","requestNo"]}'::jsonb, 300),
  ('consulting_assigned', 'consulting', 'service', '规划咨询已分配顾问', '{{direction}} 已分配顾问，后续会进入沟通与方案阶段。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["direction","requestNo"]}'::jsonb, 301),
  ('consulting_quote_ready', 'consulting', 'service', '规划咨询报价已生成', '{{title}} 已生成报价，请确认后付款。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'high', '{"required":["title","requestNo"]}'::jsonb, 302),
  ('consulting_payment_required', 'consulting', 'service', '规划咨询待付款', '{{title}} 需要完成付款后进入服务。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'high', '{"required":["title","requestNo"]}'::jsonb, 303),
  ('consulting_in_service', 'consulting', 'service', '规划咨询已进入服务', '{{direction}} 已进入方案服务阶段。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["direction","requestNo"]}'::jsonb, 304),
  ('consulting_completed', 'consulting', 'service', '规划咨询最终方案已反馈', '{{title}} 已完成，最终方案反馈和附件已开放查看。', '/account/consulting/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["title","requestNo"]}'::jsonb, 305),
  ('consulting_cancelled', 'consulting', 'service', '规划咨询已取消', '{{direction}} 已取消，如仍需要服务可重新提交。', '/account/consulting', array['in_app']::text[], 'normal', '{"required":["direction"]}'::jsonb, 306),

  ('data_service_submitted', 'data_service', 'service', '数据服务需求已提交', '{{dataTopic}} 已提交，服务团队会尽快联系你。', '/account/data/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["dataTopic","requestNo"]}'::jsonb, 400),
  ('data_service_contacting', 'data_service', 'service', '数据服务正在联系', '{{dataTopic}} 已进入沟通阶段。', '/account/data/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["dataTopic","requestNo"]}'::jsonb, 401),
  ('data_service_quote_ready', 'data_service', 'service', '数据服务报价已生成', '{{title}} 已生成报价，请确认后付款。', '/account/data/{{requestNo}}', array['in_app']::text[], 'high', '{"required":["title","requestNo"]}'::jsonb, 402),
  ('data_service_payment_required', 'data_service', 'service', '数据服务待付款', '{{title}} 需要完成付款后进入交付。', '/account/data/{{requestNo}}', array['in_app']::text[], 'high', '{"required":["title","requestNo"]}'::jsonb, 403),
  ('data_service_in_service', 'data_service', 'service', '数据服务已进入交付', '{{dataTopic}} 已进入数据交付阶段。', '/account/data/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["dataTopic","requestNo"]}'::jsonb, 404),
  ('data_service_completed', 'data_service', 'service', '数据服务交付已完成', '{{title}} 已完成，交付材料已开放查看。', '/account/data/{{requestNo}}', array['in_app']::text[], 'normal', '{"required":["title","requestNo"]}'::jsonb, 405),
  ('data_service_cancelled', 'data_service', 'service', '数据服务已取消', '{{dataTopic}} 已取消，如仍需要服务可重新提交。', '/account/data', array['in_app']::text[], 'normal', '{"required":["dataTopic"]}'::jsonb, 406),

  ('agent_task_created', 'agent_task', 'service', '智能体交付任务已创建', '{{title}} 已进入交付流程，请按要求提交资料。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'normal', '{"required":["title","taskNo"]}'::jsonb, 500),
  ('agent_material_required', 'agent_task', 'service', '请提交智能体配置资料', '{{title}} 需要补充配置资料后继续处理。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'high', '{"required":["title","taskNo"]}'::jsonb, 501),
  ('agent_material_received', 'agent_task', 'service', '智能体资料已收到', '{{title}} 的资料已提交，团队会进入审核配置。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'normal', '{"required":["title","taskNo"]}'::jsonb, 502),
  ('agent_in_progress', 'agent_task', 'service', '智能体正在配置', '{{title}} 正在配置中。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'normal', '{"required":["title","taskNo"]}'::jsonb, 503),
  ('agent_pending_confirm', 'agent_task', 'service', '智能体待验收', '{{title}} 已进入验收阶段，请打开任务查看。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'high', '{"required":["title","taskNo"]}'::jsonb, 504),
  ('agent_completed', 'agent_task', 'service', '智能体交付已完成', '{{title}} 已完成交付。', '/account/agents/{{taskNo}}', array['in_app']::text[], 'normal', '{"required":["title","taskNo"]}'::jsonb, 505),

  ('expert_booking_pending_payment', 'expert_booking', 'expert', '专家预约待付款', '{{expertName}} 专家预约需要完成付款后提交给专家确认。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 600),
  ('expert_booking_paid', 'expert_booking', 'expert', '专家预约已付款', '{{expertName}} 专家预约已付款，等待专家确认。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'normal', '{"required":["expertName","bookingNo"]}'::jsonb, 601),
  ('expert_booking_confirmed', 'expert_booking', 'expert', '专家预约已确认', '{{expertName}} 已确认预约，请留意会议安排。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'normal', '{"required":["expertName","bookingNo"]}'::jsonb, 602),
  ('expert_booking_rejected', 'expert_booking', 'expert', '专家预约已拒绝', '{{expertName}} 未能确认本次预约，请查看详情。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 603),
  ('expert_meeting_updated', 'expert_booking', 'expert', '专家预约会议安排已更新', '{{expertName}} 的会议安排已更新，请打开预约详情查看。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 604),
  ('expert_reschedule_requested', 'expert_booking', 'expert', '专家预约改期申请', '{{expertName}} 的预约有新的改期申请，请打开详情处理。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 605),
  ('expert_reschedule_accepted', 'expert_booking', 'expert', '专家预约改期已通过', '{{expertName}} 的预约改期已确认。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'normal', '{"required":["expertName","bookingNo"]}'::jsonb, 606),
  ('expert_reschedule_rejected', 'expert_booking', 'expert', '专家预约改期已拒绝', '{{expertName}} 的改期申请未通过，请查看详情。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 607),
  ('expert_completion_pending_user', 'expert_booking', 'expert', '专家服务待确认完成', '{{expertName}} 已提交服务完成，请打开预约详情确认或处理异议。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 608),
  ('expert_booking_disputed', 'expert_booking', 'expert', '专家预约争议处理中', '{{expertName}} 的预约已进入争议处理。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'high', '{"required":["expertName","bookingNo"]}'::jsonb, 609),
  ('expert_booking_completed', 'expert_booking', 'expert', '专家预约已完成', '{{expertName}} 的专家服务已完成。', '/account/expert-bookings/{{bookingNo}}', array['in_app']::text[], 'normal', '{"required":["expertName","bookingNo"]}'::jsonb, 610),

  ('course_registered', 'course', 'learning', '课程报名成功', '{{title}} 已报名成功，请按课程安排参加。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 700),
  ('course_pending_payment', 'course', 'learning', '课程报名待支付', '{{title}} 需要完成支付后确认报名。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 701),
  ('course_waitlisted', 'course', 'learning', '课程候补中', '{{title}} 当前为候补状态，请留意后续通知。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 702),
  ('course_start_reminder', 'course', 'learning', '课程即将开始', '{{title}} 将于 {{startsAt}} 开始，请提前安排。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","startsAt","registrationNo"]}'::jsonb, 703),
  ('course_checkin_reminder', 'course', 'learning', '课程签到提醒', '{{title}} 需要现场签到，请到场后出示通行证。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 704),
  ('course_checked_in', 'course', 'learning', '课程已签到', '{{title}} 已完成签到。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 705),
  ('course_changed', 'course', 'learning', '课程安排有变更', '{{title}} 的时间或地点有更新，请查看详情。', '/account/courses/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 706),
  ('course_cancelled', 'course', 'learning', '课程报名已取消', '{{title}} 的报名已取消。', '/account/courses', array['in_app']::text[], 'normal', '{"required":["title"]}'::jsonb, 707),

  ('event_registered', 'event', 'learning', '活动报名成功', '{{title}} 已报名成功，请按活动安排参加。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 800),
  ('event_pending_payment', 'event', 'learning', '活动报名待支付', '{{title}} 需要完成支付后确认报名。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 801),
  ('event_waitlisted', 'event', 'learning', '活动候补中', '{{title}} 当前为候补状态，请留意后续通知。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 802),
  ('event_start_reminder', 'event', 'learning', '活动即将开始', '{{title}} 将于 {{startsAt}} 开始，请提前安排。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","startsAt","registrationNo"]}'::jsonb, 803),
  ('event_checkin_reminder', 'event', 'learning', '活动签到提醒', '{{title}} 需要现场签到，请到场后出示通行证。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 804),
  ('event_checked_in', 'event', 'learning', '活动已签到', '{{title}} 已完成签到。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'normal', '{"required":["title","registrationNo"]}'::jsonb, 805),
  ('event_changed', 'event', 'learning', '活动安排有变更', '{{title}} 的时间或地点有更新，请查看详情。', '/account/events/{{registrationNo}}', array['in_app']::text[], 'high', '{"required":["title","registrationNo"]}'::jsonb, 806),
  ('event_cancelled', 'event', 'learning', '活动报名已取消', '{{title}} 的报名已取消。', '/account/events', array['in_app']::text[], 'normal', '{"required":["title"]}'::jsonb, 807),

  ('invoice_submitted', 'invoice', 'invoice', '发票申请已提交', '订单 {{orderNo}} 的发票申请已提交。', '/account/invoices', array['in_app']::text[], 'normal', '{"required":["orderNo"]}'::jsonb, 900),
  ('invoice_processing', 'invoice', 'invoice', '发票正在处理', '订单 {{orderNo}} 的发票正在处理中。', '/account/invoices', array['in_app']::text[], 'normal', '{"required":["orderNo"]}'::jsonb, 901),
  ('invoice_issued', 'invoice', 'invoice', '发票已开具', '订单 {{orderNo}} 的发票已开具，可前往发票中心查看。', '/account/invoices', array['in_app']::text[], 'normal', '{"required":["orderNo"]}'::jsonb, 902),
  ('invoice_rejected', 'invoice', 'invoice', '发票申请已驳回', '订单 {{orderNo}} 的发票申请未通过，请查看原因后重新处理。', '/account/invoices', array['in_app']::text[], 'high', '{"required":["orderNo"]}'::jsonb, 903),
  ('invoice_failed', 'invoice', 'invoice', '发票开具失败', '订单 {{orderNo}} 的发票开具失败，请查看详情。', '/account/invoices', array['in_app']::text[], 'high', '{"required":["orderNo"]}'::jsonb, 904),

  ('platform_announcement', 'platform', 'platform', '{{title}}', '{{body}}', '{{actionUrl}}', array['in_app']::text[], 'normal', '{"required":["title","body"]}'::jsonb, 1000),
  ('platform_service_upgrade', 'platform', 'platform', '服务升级通知', '{{body}}', '{{actionUrl}}', array['in_app']::text[], 'normal', '{"required":["body"]}'::jsonb, 1001),
  ('platform_agreement_updated', 'platform', 'platform', '平台协议已更新', '{{body}}', '/account/settings', array['in_app']::text[], 'high', '{"required":["body"]}'::jsonb, 1002),
  ('platform_maintenance', 'platform', 'platform', '系统维护通知', '{{body}}', '{{actionUrl}}', array['in_app']::text[], 'high', '{"required":["body"]}'::jsonb, 1003)
on conflict (template_key) do update set
  scene = excluded.scene,
  category = excluded.category,
  title_template = excluded.title_template,
  body_template = excluded.body_template,
  action_url_template = excluded.action_url_template,
  default_channels = excluded.default_channels,
  priority = excluded.priority,
  variables_schema = excluded.variables_schema,
  sort_order = excluded.sort_order,
  updated_at = now();

update staff_roles
set permissions = array(
  select distinct permission
  from unnest(permissions || array[
    'notification:read',
    'notification:write',
    'notification:dispatch',
    'notification_template:update'
  ]::text[]) as permission
)
where code in ('super_admin', 'operations_admin', 'content_admin');

update staff_roles
set permissions = array(
  select distinct permission
  from unnest(permissions || array['notification:read']::text[]) as permission
)
where code in ('finance_admin', 'agent_staff');
