-- Feedback board schema. Run in Supabase -> SQL Editor.

create table public.profiles (
  id uuid primary key references auth.users on delete cascade,
  display_name text,
  is_admin boolean not null default false,
  created_at timestamptz not null default now()
);

create table public.ideas (
  id bigint generated always as identity primary key,
  author_id uuid not null references public.profiles(id) on delete cascade,
  title text not null check (char_length(title) between 5 and 120),
  body text not null default '' check (char_length(body) <= 4000),
  status text not null default 'under_review'
    check (status in ('under_review','planned','in_progress','done','closed')),
  created_at timestamptz not null default now()
);

create table public.votes (
  idea_id bigint not null references public.ideas(id) on delete cascade,
  user_id uuid not null references public.profiles(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (idea_id, user_id)
);

create table public.comments (
  id bigint generated always as identity primary key,
  idea_id bigint not null references public.ideas(id) on delete cascade,
  author_id uuid not null references public.profiles(id) on delete cascade,
  body text not null check (char_length(body) between 1 and 2000),
  created_at timestamptz not null default now()
);

create view public.idea_scores with (security_invoker = on) as
  select i.*, (select count(*) from public.votes v where v.idea_id = i.id) as votes
  from public.ideas i;

-- New users get a profile automatically.
create function public.handle_new_user() returns trigger
language plpgsql security definer set search_path = '' as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, split_part(new.email, '@', 1));
  return new;
end; $$;
create trigger on_auth_user_created after insert on auth.users
  for each row execute function public.handle_new_user();

create function public.is_admin() returns boolean
language sql stable security definer set search_path = '' as $$
  select coalesce((select is_admin from public.profiles where id = (select auth.uid())), false);
$$;

alter table public.profiles enable row level security;
alter table public.ideas    enable row level security;
alter table public.votes    enable row level security;
alter table public.comments enable row level security;

create policy "profiles readable" on public.profiles for select using (true);
create policy "update own profile" on public.profiles for update
  using ((select auth.uid()) = id) with check ((select auth.uid()) = id);
-- Users may only change their display name, never is_admin.
revoke update on public.profiles from authenticated, anon;
grant update (display_name) on public.profiles to authenticated;

create policy "ideas readable" on public.ideas for select using (true);
create policy "post own idea" on public.ideas for insert
  with check ((select auth.uid()) = author_id and status = 'under_review');
create policy "admin updates ideas" on public.ideas for update using (public.is_admin());
create policy "admin deletes ideas" on public.ideas for delete using (public.is_admin());

create policy "votes readable" on public.votes for select using (true);
create policy "vote as self" on public.votes for insert with check ((select auth.uid()) = user_id);
create policy "remove own vote" on public.votes for delete using ((select auth.uid()) = user_id);

create policy "comments readable" on public.comments for select using (true);
create policy "comment as self" on public.comments for insert with check ((select auth.uid()) = author_id);
create policy "delete own or admin" on public.comments for delete
  using ((select auth.uid()) = author_id or public.is_admin());

-- After you sign up once, make yourself admin:
-- update public.profiles set is_admin = true where id = (select id from auth.users where email = 'you@example.com');