-- Initial administrator account support.
-- Create the Auth user manually, then run:
-- select public.bootstrap_owner_profile();

insert into public.companies (
  id,
  name,
  contact_email,
  fiscal_id_label,
  country_code,
  currency_code,
  locale,
  tax_name,
  tax_rate,
  plan_id,
  subscription_status,
  is_demo_data
)
values (
  '00000000-0000-4000-8000-000000000010',
  'Mi Tienda Agil',
  'owner@tiendaagil.test',
  'RUC',
  'PE',
  'PEN',
  'es-PE',
  'IGV',
  0.18,
  '00000000-0000-4000-8000-000000000103',
  'active',
  false
)
on conflict (id) do update set
  name = excluded.name,
  contact_email = excluded.contact_email,
  fiscal_id_label = excluded.fiscal_id_label,
  country_code = excluded.country_code,
  currency_code = excluded.currency_code,
  locale = excluded.locale,
  tax_name = excluded.tax_name,
  tax_rate = excluded.tax_rate,
  plan_id = excluded.plan_id,
  subscription_status = excluded.subscription_status,
  is_demo_data = false;

create or replace function public.sync_profile_email_from_auth_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  update public.profiles
  set email = new.email,
      updated_at = now()
  where id = new.id;

  return new;
end;
$$;

drop trigger if exists on_auth_user_email_updated_tienda_agil on auth.users;
create trigger on_auth_user_email_updated_tienda_agil
after update of email on auth.users
for each row
when (old.email is distinct from new.email)
execute function public.sync_profile_email_from_auth_user();

create or replace function public.bootstrap_owner_profile(
  p_owner_email text default 'owner@tiendaagil.test'
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  v_user_id uuid;
  v_email text;
begin
  select id, email
  into v_user_id, v_email
  from auth.users
  where lower(email) = lower(p_owner_email)
  limit 1;

  if v_user_id is null then
    raise exception 'Create Auth user % first, then run bootstrap_owner_profile().', p_owner_email;
  end if;

  update public.companies
  set contact_email = v_email,
      updated_at = now()
  where id = '00000000-0000-4000-8000-000000000010';

  insert into public.profiles (
    id,
    company_id,
    email,
    full_name,
    role,
    is_demo,
    demo_mode,
    is_active
  )
  values (
    v_user_id,
    '00000000-0000-4000-8000-000000000010',
    v_email,
    'Owner Tienda Agil',
    'admin',
    false,
    'none',
    true
  )
  on conflict (id) do update set
    company_id = excluded.company_id,
    email = excluded.email,
    full_name = excluded.full_name,
    role = 'admin',
    is_demo = false,
    demo_mode = 'none',
    is_active = true,
    updated_at = now();
end;
$$;

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