-- Spec Section 5, second half: on auth.users insert, automatically
-- create a matching profiles row. Before this, every profiles row was
-- provisioned manually via service role (see 0008's comment) — this
-- migration is what actually closes that gap for anyone created going
-- forward, including everyone invited through /api/invite-user.
--
-- Explicit defaults per this request: agent_id=null, is_admin=false,
-- is_broad_reviewer=false. is_invoice_reviewer/is_invoice_approver are
-- left off the insert entirely and take their own column defaults
-- (false) — nobody gets any elevated access just by having an account
-- exist; every grant still requires a separate, deliberate update.
--
-- SECURITY DEFINER + explicit search_path: the function must run with
-- privileges that bypass profiles' RLS (which has no insert policy at
-- all per 0008 -- by design, since only service role/this trigger
-- should ever create rows here), regardless of who/what triggers the
-- auth.users insert.
--
-- on conflict (id) do nothing: defensive only. Covers the trigger ever
-- firing twice for the same user, or a profiles row already existing
-- from the old manual-provisioning path (e.g. Edwin/Quinty, both
-- seeded before this trigger existed). The trigger itself is NOT a
-- backfill mechanism -- it only fires on new auth.users inserts, so it
-- does nothing for the 10 people already invited via /api/invite-user
-- before this migration existed. That's what the second block below
-- (the one-time backfill insert) is for.

create or replace function public.handle_new_auth_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, agent_id, is_admin, is_broad_reviewer)
  values (new.id, null, false, false)
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function public.handle_new_auth_user();

-- ============================================================
-- One-time backfill: the 10 people invited via /api/invite-user
-- before this trigger existed. Same shape as the trigger's insert
-- (agent_id=null, is_admin=false, is_broad_reviewer=false).
-- on conflict (id) do nothing makes this safe to run even if the
-- trigger above (or a manual insert) already created some of these.
--
-- uuid source: the userId returned by each POST /api/invite-user call
-- earlier this session -- not looked up again here.
-- ============================================================

insert into public.profiles (id, agent_id, is_admin, is_broad_reviewer) values
  ('c04f7a4a-ca51-4d6e-b146-f35e51b57e01', null, false, false), -- Dominic <dominic@khaizenunderwear.com>
  ('fd91642c-71ec-4716-b275-efc08564a625', null, false, false), -- Andrew <andrew@khaizenunderwear.com>
  ('c5bc18b2-99ac-4c8d-b8c0-7b49273c5719', null, false, false), -- Kenn <kenn@khaizen.eu>
  ('2a60b0c9-c88a-4e08-b10f-6cf50f12ce63', null, false, false), -- Bjorn <info@khaizen.eu>
  ('e5f06a25-8e3e-4839-94be-c1ed852fc458', null, false, false), -- Berry <berry@khaizenunderwear.com>
  ('f2c3edc8-4d87-4a37-95c5-6c92e0c71539', null, false, false), -- Mayvel <mayvel@khaizenunderwear.com>
  ('bbbab3f7-e509-4a19-aaa5-b12db95140ac', null, false, false), -- Mon <mon@khaizenunderwear.com>
  ('207a6a18-5825-4e62-ad04-fbf6baf8ab8e', null, false, false), -- Jurina <jurina@khaizenunderwear.com>
  ('6c63980b-777a-4804-9bc0-0c6a4843854f', null, false, false), -- Rubyrose <rubyrose@khaizenunderwear.com>
  ('8d494f82-42a4-4a76-92fc-93bdc8e6ee3d', null, false, false)  -- Kate <ks@khaizenunderwear.com>
on conflict (id) do nothing;