create table if not exists coupons (
  id uuid primary key default gen_random_uuid(),
  code text not null unique,
  name text not null,
  discount_type text not null default 'amount',
  discount_value integer not null default 0,
  min_order_amount integer not null default 0,
  total_quantity integer not null default 0,
  used_quantity integer not null default 0,
  starts_at timestamptz not null default now(),
  ends_at timestamptz,
  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 coupon_redemptions (
  id uuid primary key default gen_random_uuid(),
  coupon_id uuid not null references coupons(id) on delete restrict,
  user_id uuid not null references users(id) on delete cascade,
  order_id uuid not null unique references orders(id) on delete cascade,
  discount_amount integer not null default 0,
  created_at timestamptz not null default now()
);

create table if not exists settlement_records (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references orders(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  product_id text not null references products(id),
  beneficiary_type text not null default 'expert',
  beneficiary_ref text not null default '',
  gross_amount integer not null default 0,
  expert_share_amount integer not null default 0,
  platform_share_amount integer not null default 0,
  status text not null default 'pending_settlement',
  policy_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (order_id, product_id)
);

create index if not exists idx_coupons_status_time on coupons(status, starts_at, ends_at);
create index if not exists idx_settlement_records_order on settlement_records(order_id);
create index if not exists idx_settlement_records_status on settlement_records(status, created_at desc);

insert into coupons (
  code, name, discount_type, discount_value, min_order_amount,
  total_quantity, used_quantity, starts_at, ends_at, status
)
values (
  'DLBDI100',
  '本地开发满减券',
  'amount',
  10000,
  10000,
  1000,
  0,
  now() - interval '1 day',
  now() + interval '180 days',
  'active'
)
on conflict (code) do update set
  name = excluded.name,
  discount_type = excluded.discount_type,
  discount_value = excluded.discount_value,
  min_order_amount = excluded.min_order_amount,
  total_quantity = excluded.total_quantity,
  starts_at = excluded.starts_at,
  ends_at = excluded.ends_at,
  status = excluded.status,
  updated_at = now();
