-- Waitlist: run in Supabase → SQL Editor (or ask Lovable to run it).
create table public.waitlist (
  id bigint generated always as identity primary key,
  email text not null unique check (email ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$'),
  referral_code text not null unique default substr(md5(random()::text), 1, 8),
  referred_by text references public.waitlist (referral_code),
  created_at timestamptz not null default now()
);

-- RLS on and no policies: the browser cannot read the list of emails.
alter table public.waitlist enable row level security;

-- The only way in: join (or look up yourself) and get your code and place.
create or replace function public.join_waitlist(p_email text, p_ref text default null)
returns table (code text, queue_position bigint, referrals bigint)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_row public.waitlist%rowtype;
  v_ref text;
begin
  select w.referral_code into v_ref from public.waitlist w where w.referral_code = p_ref;

  insert into public.waitlist (email, referred_by)
  values (lower(trim(p_email)), v_ref)
  on conflict (email) do update set email = excluded.email
  returning * into v_row;

  return query
  select
    v_row.referral_code,
    greatest(
      1,
      (select count(*) from public.waitlist w where w.created_at <= v_row.created_at)
      - 5 * (select count(*) from public.waitlist w where w.referred_by = v_row.referral_code)
    ),
    (select count(*) from public.waitlist w where w.referred_by = v_row.referral_code);
end;
$$;

revoke all on function public.join_waitlist(text, text) from public;
grant execute on function public.join_waitlist(text, text) to anon, authenticated;