schema.sql

-- Enable required extensions
create extension if not exists "pgcrypto";

-- Entries table
create table public.entries (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null,
  content text not null,
  tags text[] default '{}',
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Indexes
create index entries_user_id_idx on public.entries(user_id);
create index entries_created_at_idx on public.entries(created_at desc);
create index entries_tags_idx on public.entries using gin(tags);

-- RLS
alter table public.entries enable row level security;

create policy "Users can read own entries"
  on public.entries for select
  using (auth.uid() = user_id);

create policy "Users can insert own entries"
  on public.entries for insert
  with check (auth.uid() = user_id);

create policy "Users can update own entries"
  on public.entries for update
  using (auth.uid() = user_id);

create policy "Users can delete own entries"
  on public.entries for delete
  using (auth.uid() = user_id);

-- Function to get all unique tags
create or replace function public.get_all_tags()
returns text[] as $$
  select array_agg(distinct unnested)
  from public.entries, unnest(tags) as unnested;
$$ language sql stable;

-- Auto-update updated_at
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger on_entry_update
  before update on public.entries
  for each row execute function handle_updated_at();