setup.sql

drop table if exists cards;
drop table if exists lists;
drop table if exists boards;

create table boards (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null default auth.uid(),
  title text default '',
  position int not null default 0,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);

alter table boards enable row level security;

create policy "Individuals can create boards." on boards for
    insert with check (auth.uid() = user_id);

create policy "Individuals can view their own boards. " on boards for
    select using (auth.uid() = user_id);

create policy "Individuals can update their own boards." on boards for
    update using (auth.uid() = user_id);

create policy "Individuals can delete their own boards." on boards for
    delete using (auth.uid() = user_id);

create table lists (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null default auth.uid(),
  board_id bigint references boards not null,
  title text default '',
  position int not null default 0,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);

alter table lists enable row level security;

create policy "Individuals can create lists." on lists for
    insert with check (auth.uid() = user_id);

create policy "Individuals can view their own lists. " on lists for
    select using (auth.uid() = user_id);

create policy "Individuals can update their own lists." on lists for
    update using (auth.uid() = user_id);

create policy "Individuals can delete their own lists." on lists for
    delete using (auth.uid() = user_id);

create table cards (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null default auth.uid(),
  list_id bigint references lists not null,
  position int not null default 0,
  description text check (char_length(description) > 0),
  completed_at timestamp with time zone,
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);

alter table cards enable row level security;

create policy "Individuals can create cards." on cards for
    insert with check (auth.uid() = user_id);

create policy "Individuals can view their own cards. " on cards for
    select using (auth.uid() = user_id);

create policy "Individuals can update their own cards." on cards for
    update using (auth.uid() = user_id);

create policy "Individuals can delete their own cards." on cards for
    delete using (auth.uid() = user_id);


create or replace function sort_board(board_id bigint, list_ids bigint[]) returns boolean
security invoker
as
$$
#variable_conflict use_variable
declare
  list_id bigint;
begin
  for i in 1 .. array_upper(list_ids, 1)
   loop
      list_id := list_ids[i];

      update lists set position = i - 1
        where lists.board_id = board_id
            and lists.id = list_id;
   end loop;

   return true;
end
$$ language plpgsql;

create or replace function sort_list(new_list_id bigint, card_ids bigint[]) returns boolean
security invoker
as
$$
#variable_conflict use_variable
declare
  card_id bigint;
begin
  for i in 1 .. array_upper(card_ids, 1)
   loop
      card_id := card_ids[i];

      update cards set position = i - 1, list_id = new_list_id
        where cards.id = card_id;
   end loop;

   return true;
end
$$ language plpgsql;