-- CEREBRO DE COSTOS v1.1: recetas POR OPCION (caso real de Arepa Power).
-- La "Arepa Completa" a $5 lleva masa (base) + proteinas y contornos A ELECCION:
-- el costo real esta en las opciones, no en la arepa cruda. Ahora cada renglon
-- de receta puede pertenecer a la BASE (opcion = '') o a UNA opcion del producto
-- (opcion = nombre exacto de la opcion, ej 'Mechada').
-- El costo mostrado es el del PLATO TIPICO: base + promedio de cada grupo de
-- opciones multiplicado por cuantas elige el cliente (max del grupo).

alter table public.recetas_productos add column if not exists opcion text not null default '';

alter table public.recetas_productos drop constraint recetas_productos_pkey;
alter table public.recetas_productos add primary key (producto_id, ingrediente_id, opcion);

-- costos_productos v2: costo del plato tipico + cobertura de opciones.
drop function public.costos_productos(uuid);

create or replace function public.costos_productos(p_restaurante_id uuid)
returns table (
  producto_id uuid,
  nombre text,
  precio_usd numeric,
  costo_usd numeric,
  margen_usd numeric,
  margen_pct numeric,
  ingredientes int,
  sin_precio int,
  opciones_con_receta int,
  opciones_total int
)
language plpgsql stable security definer set search_path to 'public'
as $$
declare
  r record;
  g jsonb;
  o jsonb;
  v_costo numeric;
  v_grupo_costos numeric[];
  v_costo_opcion numeric;
  v_tiene boolean;
  v_elige int;
  v_op_con int;
  v_op_tot int;
begin
  if not (fn_es_dueno_de(p_restaurante_id) or fn_es_encargado_de(p_restaurante_id) or fn_es_admin()) then
    return;
  end if;

  for r in
    select pr.id, pr.nombre, pr.precio_usd, coalesce(pr.opciones, '[]'::jsonb) as opciones
      from public.productos pr
     where pr.restaurante_id = p_restaurante_id
     order by pr.nombre
  loop
    -- Base del plato (lo que SIEMPRE lleva).
    select coalesce(sum(re.cantidad_base * i.costo_por_unidad), 0)
      into v_costo
      from public.recetas_productos re
      join public.ingredientes i on i.id = re.ingrediente_id
     where re.producto_id = r.id and re.opcion = '';

    v_op_con := 0;
    v_op_tot := 0;

    -- Grupos de opciones: promedio de las opciones costeadas x cuantas se eligen.
    for g in select * from jsonb_array_elements(r.opciones)
    loop
      v_grupo_costos := '{}';
      for o in select * from jsonb_array_elements(coalesce(g->'opciones', '[]'::jsonb))
      loop
        v_op_tot := v_op_tot + 1;
        select coalesce(sum(re.cantidad_base * i.costo_por_unidad), 0), count(*) > 0
          into v_costo_opcion, v_tiene
          from public.recetas_productos re
          join public.ingredientes i on i.id = re.ingrediente_id
         where re.producto_id = r.id and re.opcion = (o->>'nombre');
        if v_tiene then
          v_op_con := v_op_con + 1;
          v_grupo_costos := v_grupo_costos || v_costo_opcion;
        end if;
      end loop;
      if array_length(v_grupo_costos, 1) > 0 then
        v_elige := greatest(coalesce(nullif(g->>'max','')::int, 1), 1);
        v_costo := v_costo + (select avg(x) from unnest(v_grupo_costos) x) * v_elige;
      end if;
    end loop;

    producto_id := r.id;
    nombre := r.nombre;
    precio_usd := r.precio_usd;
    costo_usd := round(v_costo, 2);
    margen_usd := round(r.precio_usd - v_costo, 2);
    margen_pct := case when r.precio_usd > 0 then round((r.precio_usd - v_costo) / r.precio_usd * 100, 1) else null end;
    select count(*)::int,
           (count(*) filter (where i.costo_por_unidad <= 0))::int
      into ingredientes, sin_precio
      from public.recetas_productos re
      join public.ingredientes i on i.id = re.ingrediente_id
     where re.producto_id = r.id;
    opciones_con_receta := v_op_con;
    opciones_total := v_op_tot;
    return next;
  end loop;
end;
$$;
grant execute on function public.costos_productos(uuid) to authenticated;