schema.sql

-- =====================================================================
-- BOOK NOTES APP — SUPABASE SCHEMA
-- Copy this file and run it in: Supabase Dashboard → SQL Editor → New query
-- Safe to run multiple times (idempotent with IF NOT EXISTS / CREATE OR REPLACE)
-- =====================================================================

-- Extensions
create extension if not exists "pgcrypto";

-- ---------------------- TABLES ----------------------
create table if not exists public.books (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  title text not null,
  author text not null,
  cover_url text,
  total_chapters integer,
  started_at date,
  finished_at date,
  global_rating integer check (global_rating between 1 and 5),
  global_summary text,
  top_ideas text[] default '{}',
  next_book text,
  status text not null default 'pending' check (status in ('reading','finished','paused','pending')),
  created_at timestamptz not null default now()
);
create index if not exists idx_books_user on public.books(user_id);
create index if not exists idx_books_status on public.books(status);

create table if not exists public.chapters (
  id uuid primary key default gen_random_uuid(),
  book_id uuid not null references public.books(id) on delete cascade,
  chapter_number integer not null,
  main_idea text,
  highlights text,
  personal_connection text,
  doubts text,
  completed boolean not null default false,
  created_at timestamptz not null default now()
);
create index if not exists idx_chapters_book on public.chapters(book_id);
create unique index if not exists uniq_chapter_number on public.chapters(book_id, chapter_number);

create table if not exists public.tags (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  name text not null,
  color text not null default '#3366FF',
  created_at timestamptz not null default now(),
  unique (user_id, name)
);
create index if not exists idx_tags_user on public.tags(user_id);

create table if not exists public.book_tags (
  book_id uuid not null references public.books(id) on delete cascade,
  tag_id uuid not null references public.tags(id) on delete cascade,
  primary key (book_id, tag_id)
);

-- ---------------------- RLS ----------------------
alter table public.books     enable row level security;
alter table public.chapters  enable row level security;
alter table public.tags      enable row level security;
alter table public.book_tags enable row level security;

-- Books: user owns row
drop policy if exists "books_select_own" on public.books;
create policy "books_select_own" on public.books for select using (auth.uid() = user_id);
drop policy if exists "books_insert_own" on public.books;
create policy "books_insert_own" on public.books for insert with check (auth.uid() = user_id);
drop policy if exists "books_update_own" on public.books;
create policy "books_update_own" on public.books for update using (auth.uid() = user_id);
drop policy if exists "books_delete_own" on public.books;
create policy "books_delete_own" on public.books for delete using (auth.uid() = user_id);

-- Chapters: through book
drop policy if exists "chapters_select_own" on public.chapters;
create policy "chapters_select_own" on public.chapters for select using (
  exists (select 1 from public.books b where b.id = chapters.book_id and b.user_id = auth.uid())
);
drop policy if exists "chapters_insert_own" on public.chapters;
create policy "chapters_insert_own" on public.chapters for insert with check (
  exists (select 1 from public.books b where b.id = chapters.book_id and b.user_id = auth.uid())
);
drop policy if exists "chapters_update_own" on public.chapters;
create policy "chapters_update_own" on public.chapters for update using (
  exists (select 1 from public.books b where b.id = chapters.book_id and b.user_id = auth.uid())
);
drop policy if exists "chapters_delete_own" on public.chapters;
create policy "chapters_delete_own" on public.chapters for delete using (
  exists (select 1 from public.books b where b.id = chapters.book_id and b.user_id = auth.uid())
);

-- Tags
drop policy if exists "tags_crud_own" on public.tags;
create policy "tags_crud_own" on public.tags for all using (auth.uid() = user_id) with check (auth.uid() = user_id);

-- book_tags: via owned book
drop policy if exists "book_tags_crud_own" on public.book_tags;
create policy "book_tags_crud_own" on public.book_tags for all using (
  exists (select 1 from public.books b where b.id = book_tags.book_id and b.user_id = auth.uid())
) with check (
  exists (select 1 from public.books b where b.id = book_tags.book_id and b.user_id = auth.uid())
);

-- ---------------------- GLOSSARY ENTRIES (v1.1) ----------------------
create table if not exists public.glossary_entries (
  id uuid primary key default gen_random_uuid(),
  book_id uuid not null references public.books(id) on delete cascade,
  term text not null,
  definition text not null,
  created_at timestamptz not null default now()
);
create index if not exists idx_glossary_book on public.glossary_entries(book_id);

alter table public.glossary_entries enable row level security;

drop policy if exists "glossary_select_own" on public.glossary_entries;
create policy "glossary_select_own" on public.glossary_entries for select using (
  exists (select 1 from public.books b where b.id = glossary_entries.book_id and b.user_id = auth.uid())
);
drop policy if exists "glossary_insert_own" on public.glossary_entries;
create policy "glossary_insert_own" on public.glossary_entries for insert with check (
  exists (select 1 from public.books b where b.id = glossary_entries.book_id and b.user_id = auth.uid())
);
drop policy if exists "glossary_update_own" on public.glossary_entries;
create policy "glossary_update_own" on public.glossary_entries for update using (
  exists (select 1 from public.books b where b.id = glossary_entries.book_id and b.user_id = auth.uid())
);
drop policy if exists "glossary_delete_own" on public.glossary_entries;
create policy "glossary_delete_own" on public.glossary_entries for delete using (
  exists (select 1 from public.books b where b.id = glossary_entries.book_id and b.user_id = auth.uid())
);

-- ---------------------- CHAPTER QUOTES (v1.1) ----------------------
create table if not exists public.chapter_quotes (
  id uuid primary key default gen_random_uuid(),
  chapter_id uuid not null references public.chapters(id) on delete cascade,
  quote_text text not null,
  created_at timestamptz not null default now()
);
create index if not exists idx_quotes_chapter on public.chapter_quotes(chapter_id);

alter table public.chapter_quotes enable row level security;

drop policy if exists "quotes_select_own" on public.chapter_quotes;
create policy "quotes_select_own" on public.chapter_quotes for select using (
  exists (
    select 1 from public.chapters ch
    join public.books b on b.id = ch.book_id
    where ch.id = chapter_quotes.chapter_id and b.user_id = auth.uid()
  )
);
drop policy if exists "quotes_insert_own" on public.chapter_quotes;
create policy "quotes_insert_own" on public.chapter_quotes for insert with check (
  exists (
    select 1 from public.chapters ch
    join public.books b on b.id = ch.book_id
    where ch.id = chapter_quotes.chapter_id and b.user_id = auth.uid()
  )
);
drop policy if exists "quotes_update_own" on public.chapter_quotes;
create policy "quotes_update_own" on public.chapter_quotes for update using (
  exists (
    select 1 from public.chapters ch
    join public.books b on b.id = ch.book_id
    where ch.id = chapter_quotes.chapter_id and b.user_id = auth.uid()
  )
);
drop policy if exists "quotes_delete_own" on public.chapter_quotes;
create policy "quotes_delete_own" on public.chapter_quotes for delete using (
  exists (
    select 1 from public.chapters ch
    join public.books b on b.id = ch.book_id
    where ch.id = chapter_quotes.chapter_id and b.user_id = auth.uid()
  )
);

-- ---------------------- CHAPTERS: KEY_DATA (v1.1) ----------------------
alter table public.chapters add column if not exists key_data text;