db.sql

-- USERS
create table users (
  id uuid not null primary key,
  email text
);

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();


-- votings and voting_options

create table votings (
  id bigint generated by default as identity primary key,
  title text default 'New voting',
  voting_question text check (char_length(voting_question) > 0),
  description text check (char_length(description) > 0),
  creator_id uuid references auth.users ON DELETE CASCADE not null default auth.uid(),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  public boolean default false
);

create table voting_options (
  id bigint generated by default as identity primary key,
  voting_id bigint references votings ON DELETE CASCADE not null,
  title text check (char_length(title) > 0),
  creator_id uuid references auth.users ON DELETE CASCADE not null default auth.uid(),
  votes int default 0
);


-- votings row level security
alter table votings enable row level security;

create policy "Users can add votings" on votings for
    insert to authenticated with check (true);

create policy "Everyone can view votings" on votings for
    select using (true);

  create policy "Users can update their votings" on votings for
    update using (auth.uid() = creator_id);



-- voting_options row level security
alter table voting_options enable row level security;

create policy "Users can add options" on voting_options for
    insert to authenticated with check (true);

create policy "Everyone can view options" on voting_options for
    select using (true);

create policy "Users can delete their options" on voting_options for
    delete using (auth.uid() = creator_id);


create policy "Users can update their options" on voting_options for
    update using (auth.uid() = creator_id);