-- =====================================================
-- Roles, permissions & admin profile management upgrade
-- =====================================================

-- 1. Add email + status columns to admin_profiles
alter table public.admin_profiles add column if not exists email text;
alter table public.admin_profiles add column if not exists status text not null default 'active';

-- backfill email from auth.users
update public.admin_profiles ap
set email = u.email
from auth.users u
where u.id = ap.id and ap.email is null;

-- 2. Update the auto-create trigger to also store email
create or replace function public.handle_new_admin_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.admin_profiles (id, email, full_name, role, avatar_initials)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data ->> 'full_name', split_part(new.email, '@', 1)),
    coalesce((new.raw_user_meta_data ->> 'role')::admin_role, 'super_admin'::admin_role),
    upper(left(coalesce(new.raw_user_meta_data ->> 'full_name', new.email), 2))
  )
  on conflict (id) do update set email = excluded.email;
  return new;
end;
$$;

-- 3. Helper: is current user a super admin?
create or replace function public.is_super_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.admin_profiles
    where id = auth.uid() and role = 'super_admin'
  );
$$;

-- 4. RLS for roles table
alter table public.roles enable row level security;

drop policy if exists "roles_select_all" on public.roles;
create policy "roles_select_all" on public.roles
  for select using (auth.uid() is not null);

drop policy if exists "roles_super_admin_write" on public.roles;
create policy "roles_super_admin_write" on public.roles
  for all using (public.is_super_admin()) with check (public.is_super_admin());

-- 5. RLS for role_permissions table
alter table public.role_permissions enable row level security;

drop policy if exists "rp_select_all" on public.role_permissions;
create policy "rp_select_all" on public.role_permissions
  for select using (auth.uid() is not null);

drop policy if exists "rp_super_admin_write" on public.role_permissions;
create policy "rp_super_admin_write" on public.role_permissions
  for all using (public.is_super_admin()) with check (public.is_super_admin());

-- 6. Allow super admins to update / delete any admin_profile
drop policy if exists "admin_profiles_super_admin_update" on public.admin_profiles;
create policy "admin_profiles_super_admin_update" on public.admin_profiles
  for update using (public.is_super_admin() or auth.uid() = id);

drop policy if exists "admin_profiles_super_admin_delete" on public.admin_profiles;
create policy "admin_profiles_super_admin_delete" on public.admin_profiles
  for delete using (public.is_super_admin());

-- 7. Expand permission matrix seed to cover all 19 modules for every existing role
insert into public.role_permissions (role_id, module, can_view, can_create, can_edit, can_delete, can_execute)
select r.id, m.module,
  -- super admin: all true
  case when r.name = 'Super Admin' then true else false end,
  case when r.name = 'Super Admin' then true else false end,
  case when r.name = 'Super Admin' then true else false end,
  case when r.name = 'Super Admin' then true else false end,
  case when r.name = 'Super Admin' then true else false end
from public.roles r
cross join (values
  ('overview'),('customers'),('kyc'),('risk_aml'),('cash_wallet'),('gold_wallet'),
  ('orders'),('transfers'),('redemptions'),('inventory'),('pricing'),('fees'),
  ('ledger'),('reconciliation'),('audit_logs'),('notifications'),('support'),
  ('reports'),('roles'),('settings')
) as m(module)
on conflict (role_id, module) do nothing;

-- Operations Admin: read most + execute on wallets/orders/transfers
update public.role_permissions set can_view = true
where role_id = (select id from public.roles where name = 'Operations Admin');

update public.role_permissions set can_edit = true, can_create = true, can_execute = true
where role_id = (select id from public.roles where name = 'Operations Admin')
  and module in ('cash_wallet','gold_wallet','orders','transfers','redemptions','inventory');

-- Compliance Officer: KYC/risk/audit
update public.role_permissions set can_view = true
where role_id = (select id from public.roles where name = 'Compliance Officer')
  and module in ('overview','customers','kyc','risk_aml','audit_logs','reports','support');
update public.role_permissions set can_edit = true, can_execute = true
where role_id = (select id from public.roles where name = 'Compliance Officer')
  and module in ('kyc','risk_aml','customers');

-- Finance Admin: ledger/reconciliation/fees/pricing/reports
update public.role_permissions set can_view = true
where role_id = (select id from public.roles where name = 'Finance Admin')
  and module in ('overview','ledger','reconciliation','fees','pricing','reports','audit_logs');
update public.role_permissions set can_edit = true, can_create = true
where role_id = (select id from public.roles where name = 'Finance Admin')
  and module in ('fees','pricing','reconciliation');

-- Support Agent: read-only customer/support
update public.role_permissions set can_view = true
where role_id = (select id from public.roles where name = 'Support Agent')
  and module in ('overview','customers','support','notifications');
update public.role_permissions set can_edit = true, can_create = true
where role_id = (select id from public.roles where name = 'Support Agent')
  and module = 'support';

-- 8. Auto-recalc roles.user_count from admin_profiles
update public.roles r set user_count = (
  select count(*) from public.admin_profiles ap
  where lower(replace(r.name, ' ', '_')) = lower(ap.role::text)
);
