-- ============================================================================
-- Bootstrap: Head Office branch + first Super Admin
-- Idempotent. Pass credentials as psql variables (never hardcode secrets):
--
--   psql "$DATABASE_URL" \
--     -v admin_email="admin@eduadvise.in" \
--     -v admin_password="YOUR-PASSWORD" \
--     -f db/seed/0002_bootstrap_admin.sql
-- ============================================================================

\if :{?admin_email}
\else
  \set admin_email 'admin@eduadvise.in'
\endif
\if :{?admin_password}
\else
  \echo '*** ERROR: pass -v admin_password=...'
  \quit
\endif

insert into public.branches (name, code, is_head_office, city, state)
values ('Head Office', 'HO', true, 'Margao', 'Goa')
on conflict (code) do nothing;

-- Token columns must be '' (not NULL) or GoTrue throws "Database error querying schema".
insert into auth.users (
  instance_id, id, aud, role, email, encrypted_password,
  email_confirmed_at, created_at, updated_at,
  raw_app_meta_data, raw_user_meta_data,
  confirmation_token, recovery_token, email_change_token_new, email_change,
  email_change_token_current, phone_change, phone_change_token, reauthentication_token
)
select
  '00000000-0000-0000-0000-000000000000',
  gen_random_uuid(),
  'authenticated', 'authenticated',
  :'admin_email',
  crypt(:'admin_password', gen_salt('bf')),
  now(), now(), now(),
  '{"provider":"email","providers":["email"]}'::jsonb,
  '{"full_name":"EduAdvise Admin"}'::jsonb,
  '', '', '', '', '', '', '', ''
where not exists (
  select 1 from auth.users where email = :'admin_email'
);

insert into auth.identities (provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
select
  u.id::text, u.id,
  jsonb_build_object('sub', u.id::text, 'email', :'admin_email', 'email_verified', true),
  'email', now(), now(), now()
from auth.users u
where u.email = :'admin_email'
  and not exists (
    select 1 from auth.identities i
    where i.provider = 'email' and i.provider_id = u.id::text
  );

insert into public.users (id, full_name, email, role_id, primary_branch_id, status)
select
  u.id, 'EduAdvise Admin', :'admin_email',
  (select id from public.roles where slug = 'super-admin'),
  (select id from public.branches where code = 'HO'),
  'active'
from auth.users u
where u.email = :'admin_email'
on conflict (id) do update set
  role_id = excluded.role_id,
  primary_branch_id = excluded.primary_branch_id,
  status = 'active';

insert into public.user_branches (user_id, branch_id)
select u.id, (select id from public.branches where code = 'HO')
from auth.users u
where u.email = :'admin_email'
on conflict do nothing;