定制商品方案end

投票功能

1. 先发布定制产品，然后，接受投票，然后预定，
2. 不需要收藏
3. 朋友帮忙支付分享链接

-- =========================================================
-- 004_phase1_mvp_feature_tables.sql
-- Phase-1 feature tables (incremental)
-- Depends on: 001_base_schema.sql + 002_approval_extension.sql + 003_simple_supplier_sync.sql
-- =========================================================

create extension if not exists pgcrypto;

-- =========================
-- 1) Enums
-- =========================
do $$
begin
  if not exists (select 1 from pg_type where typname = 'admin_role') then
    create type admin_role as enum ('admin', 'operator', 'viewer');
  end if;
end
$$;
comment on type admin_role is '后台角色：admin=全权限，operator=运营，viewer=只读';

do $$
begin
  if not exists (select 1 from pg_type where typname = 'template_channel') then
    create type template_channel as enum ('email', 'sms', 'push');
  end if;
end
$$;
comment on type template_channel is '消息模板渠道类型';

do $$
begin
  if not exists (select 1 from pg_type where typname = 'message_send_status') then
    create type message_send_status as enum ('queued', 'sending', 'sent', 'failed', 'cancelled');
  end if;
end
$$;
comment on type message_send_status is '消息发送状态';

do $$
begin
  if not exists (select 1 from pg_type where typname = 'refund_status') then
    create type refund_status as enum ('pending', 'processing', 'succeeded', 'failed', 'cancelled');
  end if;
end
$$;
comment on type refund_status is '退款状态';

do $$
begin
  if not exists (select 1 from pg_type where typname = 'customization_draft_status') then
    create type customization_draft_status as enum ('draft', 'validated', 'submitted', 'archived');
  end if;
end
$$;
comment on type customization_draft_status is '定制草稿状态';

-- =========================
-- 2) Admin user / role / audit
-- =========================
create table if not exists admin_users (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references auth.users(id) on delete cascade,
  admin_email text not null,
  role admin_role not null default 'operator',
  is_active boolean not null default true,
  last_login_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table admin_users is '后台管理员账户映射（基于 Supabase auth.users 扩展）';
comment on column admin_users.id is '主键ID';
comment on column admin_users.user_id is '关联 auth.users.id';
comment on column admin_users.admin_email is '后台登录邮箱';
comment on column admin_users.role is '管理员角色';
comment on column admin_users.is_active is '是否启用';
comment on column admin_users.last_login_at is '最近登录时间';
comment on column admin_users.created_at is '创建时间';
comment on column admin_users.updated_at is '更新时间';

create unique index if not exists uq_admin_users_email
  on admin_users(lower(admin_email));

create table if not exists admin_permissions (
  id uuid primary key default gen_random_uuid(),
  role admin_role not null,
  resource text not null,
  action text not null,
  allowed boolean not null default true,
  created_at timestamptz not null default now(),
  unique(role, resource, action)
);
comment on table admin_permissions is '后台角色权限矩阵（菜单/按钮/接口粒度）';
comment on column admin_permissions.role is '角色';
comment on column admin_permissions.resource is '资源标识（如 orders、mail_templates）';
comment on column admin_permissions.action is '动作标识（如 read/create/approve）';
comment on column admin_permissions.allowed is '是否允许';

create table if not exists admin_login_events (
  id uuid primary key default gen_random_uuid(),
  admin_user_id uuid references admin_users(id) on delete set null,
  login_identifier text,
  ip inet,
  user_agent text,
  device_info jsonb not null default '{}'::jsonb,
  success boolean not null,
  failure_reason text,
  created_at timestamptz not null default now()
);
comment on table admin_login_events is '管理员登录审计日志（用于安全审计与异常排查）';
comment on column admin_login_events.admin_user_id is '关联管理员ID';
comment on column admin_login_events.login_identifier is '登录标识（邮箱或账号）';
comment on column admin_login_events.ip is '登录IP';
comment on column admin_login_events.user_agent is '浏览器UA';
comment on column admin_login_events.device_info is '设备信息快照';
comment on column admin_login_events.success is '是否登录成功';
comment on column admin_login_events.failure_reason is '失败原因';
comment on column admin_login_events.created_at is '事件时间';

create index if not exists idx_admin_login_events_admin_created
  on admin_login_events(admin_user_id, created_at desc);

create index if not exists idx_admin_login_events_success_created
  on admin_login_events(success, created_at desc);

-- =========================
-- 3) C-end account/address
-- =========================
create table if not exists user_addresses (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  receiver_name text not null,
  receiver_phone text,
  country_code text not null references countries(code),
  state text,
  city text,
  district text,
  address_line1 text not null,
  address_line2 text,
  postal_code text,
  label text,
  is_default boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table user_addresses is '用户地址簿（对应 C 端地址管理）';
comment on column user_addresses.user_id is '地址归属用户';
comment on column user_addresses.receiver_name is '收件人姓名';
comment on column user_addresses.receiver_phone is '收件人电话';
comment on column user_addresses.country_code is '国家代码';
comment on column user_addresses.state is '省/州';
comment on column user_addresses.city is '城市';
comment on column user_addresses.district is '区/县';
comment on column user_addresses.address_line1 is '地址主行';
comment on column user_addresses.address_line2 is '地址补充行';
comment on column user_addresses.postal_code is '邮编';
comment on column user_addresses.label is '地址标签（如 Home/Office）';
comment on column user_addresses.is_default is '是否默认地址';

create index if not exists idx_user_addresses_user_created
  on user_addresses(user_id, created_at desc);

create unique index if not exists uq_user_addresses_default
  on user_addresses(user_id)
  where is_default = true;

-- =========================
-- 4) Product customization template/draft
-- =========================
create table if not exists product_custom_templates (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null unique references products(id) on delete cascade,
  template_key text not null,
  template_path text not null,
  config_path text,
  schema_version int not null default 1,
  preview_image text,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table product_custom_templates is '商品定制模板绑定（对应 custom-pages 与 custom-config 路由）';
comment on column product_custom_templates.product_id is '本地商品ID';
comment on column product_custom_templates.template_key is '模板唯一键（业务可读）';
comment on column product_custom_templates.template_path is '模板文件路径（如 src/custom-pages/xxx.html）';
comment on column product_custom_templates.config_path is '配置文件路径（如 src/custom-config/xxx.ts）';
comment on column product_custom_templates.schema_version is '模板参数版本号';
comment on column product_custom_templates.preview_image is '模板预览图';
comment on column product_custom_templates.is_active is '是否启用';

create unique index if not exists uq_product_custom_templates_key
  on product_custom_templates(template_key);

create table if not exists customization_drafts (
  id uuid primary key default gen_random_uuid(),
  draft_key text not null unique,
  user_id uuid references auth.users(id) on delete set null,
  session_id text,
  product_id uuid not null references products(id) on delete cascade,
  template_id uuid references product_custom_templates(id) on delete set null,
  options_payload jsonb not null default '{}'::jsonb,
  render_meta jsonb not null default '{}'::jsonb,
  validation_result jsonb not null default '{}'::jsonb,
  status customization_draft_status not null default 'draft',
  submitted_order_item_id uuid references order_items(id) on delete set null,
  expires_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table customization_drafts is '定制草稿表（支持 validate/draft 接口与跨端续作）';
comment on column customization_drafts.draft_key is '草稿幂等键（前端可复用）';
comment on column customization_drafts.user_id is '登录用户ID（游客为空）';
comment on column customization_drafts.session_id is '游客会话ID';
comment on column customization_drafts.product_id is '商品ID';
comment on column customization_drafts.template_id is '模板ID';
comment on column customization_drafts.options_payload is '定制参数快照';
comment on column customization_drafts.render_meta is '渲染元数据（图层/缩放/资源引用）';
comment on column customization_drafts.validation_result is '校验结果快照';
comment on column customization_drafts.status is '草稿状态';
comment on column customization_drafts.submitted_order_item_id is '提交后关联订单项ID';
comment on column customization_drafts.expires_at is '草稿过期时间';

create index if not exists idx_customization_drafts_user_updated
  on customization_drafts(user_id, updated_at desc);

create index if not exists idx_customization_drafts_session_updated
  on customization_drafts(session_id, updated_at desc);

create index if not exists idx_customization_drafts_product_status
  on customization_drafts(product_id, status);

-- =========================
-- 5) Order note / refund / payment webhook
-- =========================
create table if not exists order_notes (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  note_type text not null default 'customer_service',
  note text not null,
  is_private boolean not null default true,
  actor_admin_id uuid references admin_users(id) on delete set null,
  actor_user_id uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);
comment on table order_notes is '订单备注表（客服备注/内部沟通）';
comment on column order_notes.order_id is '订单ID';
comment on column order_notes.note_type is '备注类型';
comment on column order_notes.note is '备注内容';
comment on column order_notes.is_private is '是否仅后台可见';
comment on column order_notes.actor_admin_id is '操作管理员';
comment on column order_notes.actor_user_id is '操作用户（用户留言场景）';

create index if not exists idx_order_notes_order_created
  on order_notes(order_id, created_at desc);

create table if not exists order_refunds (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  payment_id uuid references payments(id) on delete set null,
  idempotency_key text,
  provider text not null,
  provider_refund_id text,
  reason text,
  amount numeric(12,2) not null check (amount > 0),
  currency text not null,
  status refund_status not null default 'pending',
  raw_payload jsonb not null default '{}'::jsonb,
  requested_by_admin_id uuid references admin_users(id) on delete set null,
  requested_at timestamptz not null default now(),
  settled_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table order_refunds is '订单退款记录（支持幂等重试与状态追踪）';
comment on column order_refunds.idempotency_key is '退款请求幂等键';
comment on column order_refunds.provider is '支付通道';
comment on column order_refunds.provider_refund_id is '支付侧退款ID';
comment on column order_refunds.reason is '退款原因';
comment on column order_refunds.amount is '退款金额';
comment on column order_refunds.currency is '币种';
comment on column order_refunds.status is '退款状态';
comment on column order_refunds.raw_payload is '支付侧原始响应';
comment on column order_refunds.requested_by_admin_id is '发起退款管理员';
comment on column order_refunds.requested_at is '发起时间';
comment on column order_refunds.settled_at is '退款完成时间';

create unique index if not exists uq_order_refunds_idempotency_key
  on order_refunds(idempotency_key)
  where idempotency_key is not null;

create index if not exists idx_order_refunds_order_created
  on order_refunds(order_id, created_at desc);

create table if not exists payment_webhook_events (
  id uuid primary key default gen_random_uuid(),
  provider text not null,
  event_id text not null,
  event_type text not null,
  signature text,
  payload jsonb not null,
  verified boolean not null default false,
  processed boolean not null default false,
  processed_at timestamptz,
  created_at timestamptz not null default now(),
  unique(provider, event_id)
);
comment on table payment_webhook_events is '支付 webhook 事件去重表（验签与幂等入口）';
comment on column payment_webhook_events.provider is '支付通道';
comment on column payment_webhook_events.event_id is '通道事件ID（幂等键）';
comment on column payment_webhook_events.event_type is '事件类型';
comment on column payment_webhook_events.signature is '回调签名原文或摘要';
comment on column payment_webhook_events.payload is '回调负载';
comment on column payment_webhook_events.verified is '是否通过验签';
comment on column payment_webhook_events.processed is '是否完成业务处理';
comment on column payment_webhook_events.processed_at is '处理完成时间';

create index if not exists idx_payment_webhook_events_processed_created
  on payment_webhook_events(processed, created_at desc);

-- =========================
-- 6) Abandoned checkout + mail templates
-- =========================
create table if not exists abandoned_checkout_events (
  id uuid primary key default gen_random_uuid(),
  checkout_draft_id uuid not null references checkout_drafts(id) on delete cascade,
  user_id uuid references auth.users(id) on delete set null,
  contact_email text,
  contact_phone text,
  event_type text not null default 'detected',
  reason text,
  recovered_order_id uuid references orders(id) on delete set null,
  created_at timestamptz not null default now()
);
comment on table abandoned_checkout_events is '弃单事件记录（识别、提醒、恢复）';
comment on column abandoned_checkout_events.checkout_draft_id is '结算草稿ID';
comment on column abandoned_checkout_events.user_id is '归属用户';
comment on column abandoned_checkout_events.contact_email is '提醒邮箱';
comment on column abandoned_checkout_events.contact_phone is '提醒手机号';
comment on column abandoned_checkout_events.event_type is '事件类型（detected/reminded/recovered）';
comment on column abandoned_checkout_events.reason is '原因说明';
comment on column abandoned_checkout_events.recovered_order_id is '恢复后订单ID';

create index if not exists idx_abandoned_checkout_events_draft_created
  on abandoned_checkout_events(checkout_draft_id, created_at desc);

create table if not exists mail_templates (
  id uuid primary key default gen_random_uuid(),
  code text not null unique,
  name text not null,
  channel template_channel not null default 'email',
  subject_template text,
  body_html text,
  body_text text,
  variables jsonb not null default '[]'::jsonb,
  is_active boolean not null default true,
  version int not null default 1,
  created_by uuid references admin_users(id) on delete set null,
  updated_by uuid references admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table mail_templates is '消息模板表（弃单提醒/审批通知/订单状态通知）';
comment on column mail_templates.code is '模板编码（业务唯一）';
comment on column mail_templates.name is '模板名称';
comment on column mail_templates.channel is '模板渠道';
comment on column mail_templates.subject_template is '标题模板';
comment on column mail_templates.body_html is 'HTML 模板体';
comment on column mail_templates.body_text is '纯文本模板体';
comment on column mail_templates.variables is '变量定义列表';
comment on column mail_templates.is_active is '是否启用';
comment on column mail_templates.version is '模板版本号';
comment on column mail_templates.created_by is '创建人';
comment on column mail_templates.updated_by is '更新人';

create table if not exists message_send_logs (
  id uuid primary key default gen_random_uuid(),
  template_id uuid references mail_templates(id) on delete set null,
  channel template_channel not null default 'email',
  biz_type text not null,
  biz_id uuid,
  recipient text not null,
  send_status message_send_status not null default 'queued',
  provider text,
  provider_message_id text,
  variables_payload jsonb not null default '{}'::jsonb,
  provider_response jsonb not null default '{}'::jsonb,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
comment on table message_send_logs is '消息发送日志（用于模板测试、弃单提醒、通知追踪）';
comment on column message_send_logs.template_id is '关联模板ID';
comment on column message_send_logs.channel is '发送渠道';
comment on column message_send_logs.biz_type is '业务类型（abandoned_checkout/order_status 等）';
comment on column message_send_logs.biz_id is '业务对象ID';
comment on column message_send_logs.recipient is '接收方（邮箱/手机号/推送标识）';
comment on column message_send_logs.send_status is '发送状态';
comment on column message_send_logs.provider is '发送服务商';
comment on column message_send_logs.provider_message_id is '服务商消息ID';
comment on column message_send_logs.variables_payload is '渲染变量快照';
comment on column message_send_logs.provider_response is '发送响应原文';
comment on column message_send_logs.error_message is '失败信息';

create index if not exists idx_message_send_logs_biz_created
  on message_send_logs(biz_type, biz_id, created_at desc);

create index if not exists idx_message_send_logs_status_created
  on message_send_logs(send_status, created_at desc);

-- =========================
-- 7) updated_at triggers
-- =========================
create or replace function set_updated_at_common()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists tr_admin_users_set_updated_at on admin_users;
create trigger tr_admin_users_set_updated_at
before update on admin_users
for each row execute function set_updated_at_common();

drop trigger if exists tr_user_addresses_set_updated_at on user_addresses;
create trigger tr_user_addresses_set_updated_at
before update on user_addresses
for each row execute function set_updated_at_common();

drop trigger if exists tr_product_custom_templates_set_updated_at on product_custom_templates;
create trigger tr_product_custom_templates_set_updated_at
before update on product_custom_templates
for each row execute function set_updated_at_common();

drop trigger if exists tr_customization_drafts_set_updated_at on customization_drafts;
create trigger tr_customization_drafts_set_updated_at
before update on customization_drafts
for each row execute function set_updated_at_common();

drop trigger if exists tr_order_refunds_set_updated_at on order_refunds;
create trigger tr_order_refunds_set_updated_at
before update on order_refunds
for each row execute function set_updated_at_common();

drop trigger if exists tr_mail_templates_set_updated_at on mail_templates;
create trigger tr_mail_templates_set_updated_at
before update on mail_templates
for each row execute function set_updated_at_common();

drop trigger if exists tr_message_send_logs_set_updated_at on message_send_logs;
create trigger tr_message_send_logs_set_updated_at
before update on message_send_logs
for each row execute function set_updated_at_common();