-- Portal del fundador (CEO): estadisticas de plataforma. SIN datos de ventas de los negocios.
alter table public.perfiles add column if not exists es_fundador boolean not null default false;
update public.perfiles set es_fundador = true
 where id = (select id from auth.users where email = 'zafraclic@gmail.com');

create or replace function public.ceo_estadisticas()
returns jsonb
language plpgsql
security definer
set search_path to 'public'
as $$
declare
  v jsonb;
begin
  if not exists (select 1 from public.perfiles where id = auth.uid() and es_fundador) then
    raise exception 'Solo el fundador puede ver esto';
  end if;

  select jsonb_build_object(
    'total_restaurantes', (select count(*) from public.restaurantes),
    'publicados',         (select count(*) from public.restaurantes where publicado),
    'borradores',         (select count(*) from public.restaurantes where not publicado),
    'duenos',             (select count(*) from public.perfiles where rol = 'dueno' and restaurante_id is not null and not es_fundador),
    'repartidores',       (select count(*) from public.repartidores),
    'nuevos_30d',         (select count(*) from public.restaurantes where creado_en >= now() - interval '30 days'),
    'por_estado', (
      select coalesce(jsonb_agg(jsonb_build_object('estado', estado, 'n', n) order by n desc, estado), '[]'::jsonb)
      from (select coalesce(nullif(trim(estado_ve), ''), 'Sin estado') estado, count(*) n
            from public.restaurantes group by 1) s
    ),
    'por_rubro', (
      select coalesce(jsonb_agg(jsonb_build_object('rubro', rubro, 'n', n) order by n desc, rubro), '[]'::jsonb)
      from (select coalesce(nullif(trim(rubro), ''), 'Otro') rubro, count(*) n
            from public.restaurantes group by 1) s
    ),
    'por_mes', (
      select coalesce(jsonb_agg(jsonb_build_object('mes', mes, 'n', n) order by mes), '[]'::jsonb)
      from (select to_char(creado_en, 'YYYY-MM') mes, count(*) n
            from public.restaurantes group by 1) s
    ),
    'recientes', (
      select coalesce(jsonb_agg(jsonb_build_object(
               'nombre', nombre, 'estado', estado_ve, 'municipio', municipio,
               'creado_en', creado_en, 'publicado', publicado) order by creado_en desc), '[]'::jsonb)
      from (select nombre, estado_ve, municipio, creado_en, publicado
            from public.restaurantes order by creado_en desc limit 25) s
    )
  ) into v;
  return v;
end;
$$;
grant execute on function public.ceo_estadisticas() to authenticated;