create table if not exists webmention (
  id integer auto_increment primary key,
  hash text not null unique,
  wm_source text not null,
  wm_target text not null,
  wm_source_domain text not null,
  wm_target_domain text not null,
  wm_source_path text not null,
  wm_target_path text not null,
  lastmod bigint not null
);

create table if not exists jf2 (
  id integer auto_increment primary key,
  wm_id integer not null,
  jf2_type text not null,
  jf2_data text not null, /* schema on read, store EDN string here */

  FOREIGN KEY (wm_id)
    references webmention(id)
    on delete cascade
);

create table if not exists proxied_photo (
  id integer auto_increment primary key,
  wm_id integer not null,
  url text not null,

  FOREIGN KEY (wm_id)
    references webmention(id)
    on delete cascade
);

create index if not exists idx_webmention_hash on webmention (hash);

create index if not exists idx_webmention_pair on webmention (wm_source, wm_target);

create index if not exists idx_webmention_domain on webmention (wm_target_domain);

create index if not exists idx_webmention_uri on webmention (wm_target_domain, wm_target_path);