create type public.app_role as enum ('admin', 'user');

create table public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  role public.app_role not null,
  unique (user_id, role)
);
grant select on public.user_roles to authenticated;
grant all on public.user_roles to service_role;
alter table public.user_roles enable row level security;
create policy "Users read own roles" on public.user_roles for select to authenticated using (auth.uid() = user_id);

create or replace function public.has_role(_user_id uuid, _role public.app_role)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from public.user_roles where user_id = _user_id and role = _role)
$$;

-- The first registered account becomes the admin editor automatically.
create or replace function public.handle_first_admin()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  if not exists (select 1 from public.user_roles where role = 'admin') then
    insert into public.user_roles (user_id, role) values (new.id, 'admin');
  end if;
  return new;
end;
$$;
create trigger on_auth_user_created_admin after insert on auth.users
  for each row execute function public.handle_first_admin();

create or replace function public.admin_exists()
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from public.user_roles where role = 'admin')
$$;
grant execute on function public.admin_exists() to anon, authenticated;

create table public.proposals (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  client_name text not null,
  website text,
  brief text,
  data jsonb not null default '{}'::jsonb,
  published boolean not null default false,
  created_by uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
grant select on public.proposals to anon;
grant select, insert, update, delete on public.proposals to authenticated;
grant all on public.proposals to service_role;
alter table public.proposals enable row level security;
create policy "Public reads published" on public.proposals for select to anon, authenticated using (published = true);
create policy "Admins read all" on public.proposals for select to authenticated using (public.has_role(auth.uid(), 'admin'));
create policy "Admins insert" on public.proposals for insert to authenticated with check (public.has_role(auth.uid(), 'admin'));
create policy "Admins update" on public.proposals for update to authenticated using (public.has_role(auth.uid(), 'admin'));
create policy "Admins delete" on public.proposals for delete to authenticated using (public.has_role(auth.uid(), 'admin'));

create or replace function public.touch_updated_at() returns trigger language plpgsql set search_path = public as $$
begin new.updated_at = now(); return new; end; $$;
create trigger proposals_touch before update on public.proposals for each row execute function public.touch_updated_at();