schema.sql

-- Run this in the Supabase SQL editor (dashboard.supabase.com > SQL Editor)

create extension if not exists "uuid-ossp";

-- ─── Messages ────────────────────────────────────────────────────────────────

create table public.messages (
  id            uuid        default uuid_generate_v4() primary key,
  lat           double precision not null,
  lng           double precision not null,
  content       text        not null check (char_length(content) between 1 and 280),
  author_id     uuid        references auth.users(id) on delete set null,
  author_name   text        not null default 'Anonymous',
  fingerprint_id text,
  created_at    timestamptz default now(),
  likes_count   integer     default 0 check (likes_count >= 0)
);

-- ─── Likes ───────────────────────────────────────────────────────────────────

create table public.likes (
  id             uuid        default uuid_generate_v4() primary key,
  message_id     uuid        not null references public.messages(id) on delete cascade,
  user_id        uuid        references auth.users(id) on delete cascade,
  fingerprint_id text,
  created_at     timestamptz default now()
);

-- Prevent duplicate likes
create unique index likes_user_unique
  on public.likes (message_id, user_id)
  where user_id is not null;

create unique index likes_fp_unique
  on public.likes (message_id, fingerprint_id)
  where fingerprint_id is not null;

-- ─── Saves ───────────────────────────────────────────────────────────────────

create table public.saves (
  id         uuid        default uuid_generate_v4() primary key,
  message_id uuid        not null references public.messages(id) on delete cascade,
  user_id    uuid        not null references auth.users(id) on delete cascade,
  created_at timestamptz default now(),
  unique (message_id, user_id)
);

-- ─── RLS ─────────────────────────────────────────────────────────────────────

alter table public.messages enable row level security;
alter table public.likes    enable row level security;
alter table public.saves    enable row level security;

-- messages
create policy "messages_select" on public.messages for select using (true);
create policy "messages_insert" on public.messages for insert with check (true);
create policy "messages_update" on public.messages for update using (auth.uid() = author_id);

-- likes
create policy "likes_select" on public.likes for select using (true);
create policy "likes_insert" on public.likes for insert with check (true);
create policy "likes_delete" on public.likes for delete
  using (auth.uid() = user_id or user_id is null);

-- saves
create policy "saves_select" on public.saves for select using (auth.uid() = user_id);
create policy "saves_insert" on public.saves for insert with check (auth.uid() = user_id);
create policy "saves_delete" on public.saves for delete using (auth.uid() = user_id);

-- ─── Triggers: auto-update likes_count ───────────────────────────────────────

create or replace function public.on_like_insert()
returns trigger language plpgsql security definer as $$
begin
  update public.messages set likes_count = likes_count + 1 where id = new.message_id;
  return new;
end;
$$;

create or replace function public.on_like_delete()
returns trigger language plpgsql security definer as $$
begin
  update public.messages set likes_count = greatest(0, likes_count - 1) where id = old.message_id;
  return old;
end;
$$;

create trigger trg_like_insert after insert on public.likes
  for each row execute function public.on_like_insert();

create trigger trg_like_delete after delete on public.likes
  for each row execute function public.on_like_delete();

-- ─── Realtime ─────────────────────────────────────────────────────────────────

alter publication supabase_realtime add table public.messages;
alter publication supabase_realtime add table public.likes;