create table transactions (
  transaction_id uuid primary key,
  idempotency_key uuid unique,
  from_account bigint not null,
  to_account bigint not null,
  amount numeric(18,2) not null,
  status text not null,
  created_at timestamptz not null default now()
);

create table ledger_entries (
  entry_id bigserial primary key,
  transaction_id uuid not null references transactions(transaction_id),
  account_id bigint not null,
  direction text not null,
  amount numeric(18,2) not null,
  created_at timestamptz not null default now()
);

create table transaction_outbox (
  outbox_id bigserial primary key,
  transaction_id uuid not null,
  effect_type text not null,
  payload jsonb not null,
  created_at timestamptz not null default now(),
  delivered_at timestamptz
);

begin;

insert into transactions (
  transaction_id, idempotency_key, from_account, to_account, amount, status
) values ($1, $2, $3, $4, $5, 'COMMITTED');

insert into ledger_entries (transaction_id, account_id, direction, amount)
values
  ($1, $3, 'DEBIT',  $5),
  ($1, $4, 'CREDIT', $5);

insert into transaction_outbox (
  transaction_id, effect_type, payload
) values (
  $1, 'SEND_TRANSFER_WEBHOOK', $6
);

commit;