create table if not exists user_wechat_identities (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  provider text not null default 'miniapp',
  app_id text not null,
  openid text not null,
  unionid text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  last_login_at timestamptz
);

create unique index if not exists idx_user_wechat_identities_provider_app_openid
  on user_wechat_identities(provider, app_id, openid);

create index if not exists idx_user_wechat_identities_user_id
  on user_wechat_identities(user_id);

create table if not exists miniapp_auth_sessions (
  token text primary key,
  app_id text not null,
  openid text not null,
  unionid text not null default '',
  pc_session_id text,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create index if not exists idx_miniapp_auth_sessions_openid
  on miniapp_auth_sessions(app_id, openid);

create table if not exists pc_login_sessions (
  session_id text primary key,
  scene text not null,
  status text not null default 'pending',
  user_id uuid references users(id) on delete set null,
  exchange_code text,
  redirect text not null default '/',
  expires_at timestamptz not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  confirmed_at timestamptz
);

create unique index if not exists idx_pc_login_sessions_exchange_code
  on pc_login_sessions(exchange_code)
  where exchange_code is not null and exchange_code <> '';
