-- Assigns real agent_id values to the 11 people whose profiles.agent_id
-- was still NULL — confirmed live before writing this (not assumed):
-- profiles.agent_id was NULL for all 11 (only Edwin had one set, from
-- earlier test work — 'agent_042'). invoice_profiles has zero rows.
-- invoices/schedule only ever reference 'agent_042'. There was no
-- existing real agent_id anywhere to reuse for these 11 — this
-- migration is what actually creates them.
--
-- Scheme: name-based slug, matching legacy's idOf(name)
-- (JavaScript.html:814: name.toLowerCase().replace(/[^a-z0-9]/g,'')).
--
-- Edwin's existing 'agent_042' is deliberately left unchanged, NOT
-- renamed to 'edwin' for consistency with the other 11 — confirmed
-- explicitly: it's already referenced throughout tonight's verified
-- invoices/schedule/rate_history data, and renaming it risks breaking
-- something already confirmed working for a purely cosmetic gain. This
-- is a known, intentional inconsistency (documented in C3_SPEC.md), not
-- an oversight.
--
-- Matched by auth.users.email, not hardcoded profile ids — self-
-- documenting and independently re-verifiable directly from this file.
-- Kate's email is deliberately ks@khaizenunderwear.com, not
-- kate@khaizenunderwear.com (see C3_SPEC.md's roster note on that).

update public.profiles p set agent_id = 'jurina'   from auth.users u where u.id = p.id and u.email = 'jurina@khaizenunderwear.com';
update public.profiles p set agent_id = 'kenn'     from auth.users u where u.id = p.id and u.email = 'kenn@khaizen.eu';
update public.profiles p set agent_id = 'dominic'  from auth.users u where u.id = p.id and u.email = 'dominic@khaizenunderwear.com';
update public.profiles p set agent_id = 'berry'    from auth.users u where u.id = p.id and u.email = 'berry@khaizenunderwear.com';
update public.profiles p set agent_id = 'rubyrose' from auth.users u where u.id = p.id and u.email = 'rubyrose@khaizenunderwear.com';
update public.profiles p set agent_id = 'quinty'   from auth.users u where u.id = p.id and u.email = 'quinty@khaizen.eu';
update public.profiles p set agent_id = 'kate'     from auth.users u where u.id = p.id and u.email = 'ks@khaizenunderwear.com';
update public.profiles p set agent_id = 'mayvel'   from auth.users u where u.id = p.id and u.email = 'mayvel@khaizenunderwear.com';
update public.profiles p set agent_id = 'andrew'   from auth.users u where u.id = p.id and u.email = 'andrew@khaizenunderwear.com';
update public.profiles p set agent_id = 'bjorn'    from auth.users u where u.id = p.id and u.email = 'info@khaizen.eu';
update public.profiles p set agent_id = 'mon'      from auth.users u where u.id = p.id and u.email = 'mon@khaizenunderwear.com';

-- ============================================================
-- Self-verification guard
-- ============================================================

do $$
declare
  bad_count integer;
begin
  select count(*) into bad_count
  from public.profiles p
  join auth.users u on u.id = p.id
  where u.email in (
    'jurina@khaizenunderwear.com', 'kenn@khaizen.eu', 'dominic@khaizenunderwear.com',
    'berry@khaizenunderwear.com', 'rubyrose@khaizenunderwear.com', 'quinty@khaizen.eu',
    'ks@khaizenunderwear.com', 'mayvel@khaizenunderwear.com', 'andrew@khaizenunderwear.com',
    'info@khaizen.eu', 'mon@khaizenunderwear.com'
  )
  and p.agent_id is null;

  if bad_count > 0 then
    raise exception 'Aborting 0022: % of the 11 people still have a null agent_id after the update', bad_count;
  end if;
end $$;