create table if not exists books (
  book_id text primary key
);

create table if not exists chapters (
  id integer primary key autoincrement,
  book_id text,
  chapter_id text,
  text_en text,
  text_zh text,
  foreign key (book_id) references books(book_id),
  unique(book_id, chapter_id)
);

create table if not exists paragraphs (
  id integer primary key autoincrement,
  book_id text not null,
  chapter_id text not null,
  text_en text,
  text_zh text,
  char_count integer,
  foreign key (book_id, chapter_id) references chapters(book_id, chapter_id)
);

create table if not exists paragraph_chunks (
  id integer primary key autoincrement,
  book_id text not null,
  chapter_id text not null,
  chunk_index integer not null,
  text_en text,
  text_zh text,
  char_count integer,
  foreign key (book_id, chapter_id) references chapters(book_id, chapter_id),
  unique(book_id, chapter_id, chunk_index)
);