alter table if exists payment_orders
  add column if not exists provider text not null default 'wechat',
  add column if not exists prepay_id text,
  add column if not exists code_url text,
  add column if not exists channel_trade_no text,
  add column if not exists payer_openid text,
  add column if not exists request_payload jsonb not null default '{}'::jsonb,
  add column if not exists response_payload jsonb not null default '{}'::jsonb,
  add column if not exists notify_payload jsonb not null default '{}'::jsonb,
  add column if not exists paid_at timestamptz,
  add column if not exists closed_at timestamptz,
  add column if not exists failure_code text,
  add column if not exists failure_message text;

create index if not exists idx_payment_orders_channel_status_created
  on payment_orders(channel, status, created_at desc);

create index if not exists idx_payment_orders_channel_trade_no
  on payment_orders(channel_trade_no)
  where channel_trade_no is not null and channel_trade_no <> '';

create table if not exists payment_transactions (
  id uuid primary key default gen_random_uuid(),
  payment_order_id uuid not null references payment_orders(id) on delete cascade,
  channel_trade_no text,
  amount integer not null default 0,
  status text not null,
  raw_payload jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_payment_transactions_payment_order
  on payment_transactions(payment_order_id);
