create extension if not exists pgcrypto;

create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  display_name text not null default '',
  phone text unique,
  email text not null default '',
  organization text not null default '',
  membership_level text not null default 'registered',
  onboarding_completed boolean not null default false,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists phone_codes (
  phone text primary key,
  code text not null,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table if not exists sessions (
  token text primary key,
  user_id uuid not null references users(id) on delete cascade,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table if not exists products (
  id text primary key,
  slug text not null unique,
  type text not null,
  title text not null,
  summary text not null default '',
  price_amount integer not null default 0,
  purchase_flow text not null,
  fulfillment_flow text not null,
  status text not null default 'published',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists orders (
  id uuid primary key default gen_random_uuid(),
  order_no text not null unique,
  user_id uuid not null references users(id) on delete cascade,
  status text not null,
  subtotal_amount integer not null default 0,
  member_discount_amount integer not null default 0,
  coupon_discount_amount integer not null default 0,
  paid_amount integer not null default 0,
  total_amount integer not null default 0,
  currency text not null default 'CNY',
  settlement_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists order_items (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  product_id text not null references products(id),
  product_type text not null,
  title_snapshot text not null,
  unit_price_amount integer not null default 0,
  quantity integer not null default 1,
  payable_amount integer not null default 0
);

create table if not exists entitlements (
  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_type text not null,
  status text not null default 'active',
  valid_from timestamptz not null default now(),
  valid_to timestamptz,
  remaining_uses integer,
  created_at timestamptz not null default now()
);

create table if not exists fulfillment_tasks (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references orders(id) on delete cascade,
  entitlement_id uuid references entitlements(id) on delete cascade,
  product_id text not null references products(id),
  type text not null,
  title text not null,
  status text not null default 'pending_material',
  priority text not null default 'normal',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_orders_user_created_at on orders(user_id, created_at desc);
create index if not exists idx_entitlements_user_status on entitlements(user_id, status);
create index if not exists idx_tasks_status on fulfillment_tasks(status, created_at desc);

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', '高级会员', '高端课程与沙龙、全站交易 8 折、专属客户经理。', 29900, 'member_checkout', 'entitlement', 'published'),
  ('agent-policy-brief', 'policy-brief-agent', 'agent', '政策解读智能体', '上传政策文本后生成解读摘要、影响范围和行动建议。', 19900, 'cart', 'task', 'published'),
  ('data-index-2025', 'data-index-2025', 'data', '大连数字经济指数报告 2025', '城市数字经济、产业数字化和数据要素发展趋势报告。', 9900, 'cart', 'download', 'published'),
  ('course-city-method', 'city-digital-method', 'course', '城市数字化规划方法课', '面向政府和园区的数字化规划方法、案例和工具模板。', 29900, 'cart', 'entitlement', 'published'),
  ('event-bigdata-summit', 'bigdata-summit', 'event', '大数据产业闭门沙龙', '数据要素、人工智能和产业数字化专题交流活动。', 0, 'free_claim', 'checkin', 'published'),
  ('expert-city-consulting', 'city-consulting-expert', 'expert', '智慧城市专家咨询 1 小时', '围绕数字政府、智慧城市和数据治理路径开展专家咨询。', 100000, 'booking', 'booking', 'published')
on conflict (id) do update set
  slug = excluded.slug,
  type = excluded.type,
  title = excluded.title,
  summary = excluded.summary,
  price_amount = excluded.price_amount,
  purchase_flow = excluded.purchase_flow,
  fulfillment_flow = excluded.fulfillment_flow,
  status = excluded.status,
  updated_at = now();
