create table if not exists public.soulwall_ledger (
  id uuid primary key default gen_random_uuid(),
  event_id text not null unique,
  client_id text not null default 'public-widget',
  integration_origin text,
  provider text not null default 'unknown',
  project_slug text,
  project_name text,
  tx_hash text,
  currency text not null default 'USD',
  amount_usd numeric,
  amount_native numeric,
  network text,
  status text not null default 'confirmed',
  access_expires_at timestamptz,
  confirmed_at timestamptz not null default now(),
  raw_event jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.soulwall_ledger enable row level security;

drop policy if exists "Public can read SoulWall ledger" on public.soulwall_ledger;

create policy "Public can read SoulWall ledger"
on public.soulwall_ledger
for select
to anon, authenticated
using (true);

create index if not exists soulwall_ledger_status_idx on public.soulwall_ledger (status);
create index if not exists soulwall_ledger_confirmed_at_idx on public.soulwall_ledger (confirmed_at desc);
create index if not exists soulwall_ledger_provider_idx on public.soulwall_ledger (provider);
create index if not exists soulwall_ledger_client_id_idx on public.soulwall_ledger (client_id);
create index if not exists soulwall_ledger_tx_hash_idx on public.soulwall_ledger (tx_hash);