create table if not exists public.mangadex_manga_index (
  anilist_id bigint primary key,
  mangadex_id uuid not null,
  latest_en_chapter_text text,
  latest_en_chapter_number double precision,
  latest_en_volume_text text,
  volume_chapter_boundaries jsonb,
  latest_en_chapter_id uuid,
  latest_chapter_updated_at timestamp with time zone,
  last_seen_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
  last_indexed_at timestamp with time zone,
  is_releasing boolean default true not null,
  created_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
);

alter table public.mangadex_manga_index
  add column if not exists volume_chapter_boundaries jsonb;

create index if not exists mangadex_manga_index_mangadex_id_idx
  on public.mangadex_manga_index (mangadex_id);

create index if not exists mangadex_manga_index_last_indexed_at_idx
  on public.mangadex_manga_index (last_indexed_at);

create table if not exists public.mangadex_resolution_failures (
  anilist_id bigint primary key,
  last_attempted_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
);

create index if not exists mangadex_resolution_failures_last_attempted_at_idx
  on public.mangadex_resolution_failures (last_attempted_at);

create table if not exists public.mangadex_sync_state (
  name text primary key,
  cursor_updated_at timestamp with time zone not null,
  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
);

insert into public.mangadex_sync_state (name, cursor_updated_at)
values ('chapter_en', now() at time zone 'utc'::text)
on conflict (name) do nothing;