-- =============================================================================
-- P2/P3: fixes de escala del advisor de Supabase (sin cambio de comportamiento).
--  1) Indices en las 9 FKs sin cobertura (mejor join/borrado con volumen).
--  2) Envolver auth.uid() en (select auth.uid()) en 7 policies (se evalua UNA vez
--     por consulta en vez de por fila) -> auth_rls_initplan.
--  3) search_path fijo en 2 funciones sueltas.
-- =============================================================================

-- 1) Indices de cobertura de FKs
create index if not exists idx_comensales_mesa_rest    on public.comensales_mesa(restaurante_id);
create index if not exists idx_cuentas_mesa_mesero     on public.cuentas_mesa(mesero_id);
create index if not exists idx_gastos_caja_registrado  on public.gastos_caja(registrado_por);
create index if not exists idx_llamadas_mesa_atendida  on public.llamadas_mesa(atendida_por);
create index if not exists idx_pagos_confirmado        on public.pagos(confirmado_por);
create index if not exists idx_pagos_mesa_comensal     on public.pagos_mesa(comensal_id);
create index if not exists idx_pagos_mesa_mesero       on public.pagos_mesa(mesero_id);
create index if not exists idx_pedido_items_producto   on public.pedido_items(producto_id);
create index if not exists idx_pedidos_mesero          on public.pedidos(mesero_id);

-- 2) Envolver auth.uid() (una evaluacion por consulta)
drop policy if exists perfiles_propio_select on public.perfiles;
create policy perfiles_propio_select on public.perfiles for select to authenticated
  using ((id = (select auth.uid())) or public.fn_es_admin());

drop policy if exists perfiles_propio_update on public.perfiles;
create policy perfiles_propio_update on public.perfiles for update to authenticated
  using (id = (select auth.uid())) with check (id = (select auth.uid()));

drop policy if exists repartidores_propio_select on public.repartidores;
create policy repartidores_propio_select on public.repartidores for select to authenticated
  using (perfil_id = (select auth.uid()));

drop policy if exists repartidores_propio_update on public.repartidores;
create policy repartidores_propio_update on public.repartidores for update to authenticated
  using (perfil_id = (select auth.uid())) with check (perfil_id = (select auth.uid()));

drop policy if exists pedidos_repartidor_select on public.pedidos;
create policy pedidos_repartidor_select on public.pedidos for select to authenticated
  using (repartidor_id in (select id from public.repartidores where perfil_id = (select auth.uid())));

drop policy if exists push_subs_leer_propias on public.push_subs;
create policy push_subs_leer_propias on public.push_subs for select to authenticated
  using (perfil_id = (select auth.uid()));

drop policy if exists push_subs_borrar_propias on public.push_subs;
create policy push_subs_borrar_propias on public.push_subs for delete to authenticated
  using (perfil_id = (select auth.uid()));

-- 3) search_path fijo en las 2 funciones que faltaban
do $$
declare r record;
begin
  for r in select p.oid::regprocedure as sig from pg_proc p join pg_namespace n on n.oid = p.pronamespace
            where n.nspname = 'public' and p.proname in ('derivar_prefijo', 'set_actualizado_en')
  loop
    execute 'alter function ' || r.sig || ' set search_path = ''public''';
  end loop;
end $$;