create table if not exists public.book_images (
  id bigint primary key,
  title text not null default '',
  subtitle text not null default '',
  author text not null default '',
  translator text not null default '',
  publisher text not null default '',
  year text not null default '',
  edition text not null default '',
  language text not null default '',
  series text not null default '',
  volume text not null default '',
  condition text not null default '',
  special_features text not null default '',
  other_text text not null default '',
  source_folder text not null default '',
  source_filename text not null default '',
  raw_ocr_text text not null default '',
  description text not null default '',
  review_status text not null default 'pending' check (review_status in ('pending', 'verified', 'flagged', 'corrected')),
  review_updated_at timestamptz,
  image_url text not null default '',
  thumb_url text not null default '',
  storage_path text not null default '',
  thumb_storage_path text not null default '',
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now())
);

alter table public.book_images enable row level security;

create index if not exists idx_book_images_title on public.book_images (title);
create index if not exists idx_book_images_author on public.book_images (author);
create index if not exists idx_book_images_language on public.book_images (language);
create index if not exists idx_book_images_review_status on public.book_images (review_status);

create or replace function public.set_updated_at()
returns trigger
language plpgsql
set search_path = public
as $$
begin
  new.updated_at = timezone('utc', now());
  return new;
end;
$$;

drop trigger if exists trg_book_images_updated_at on public.book_images;
create trigger trg_book_images_updated_at
before update on public.book_images
for each row
execute function public.set_updated_at();

insert into storage.buckets (id, name, public)
values ('catalog-images', 'catalog-images', false)
on conflict (id) do nothing;
