-- CEREBRO DE COSTOS (fase 1): cuanto le VALE cada plato al negocio y cuanto
-- gana. La IA solo captura (facturas por foto, recetas conversadas); la
-- matematica es de la BD. Costo del ingrediente = costo de REPOSICION (la
-- ultima compra): con la inflacion venezolana es el unico costo que importa.
--
-- Lectura y escritura: dueno + encargado + admin (los margenes son informacion
-- sensible del negocio; los meseros no tienen por que verlos).

-- ===== Ingredientes (por restaurante, en unidad base g/ml/unidad) =====
create table public.ingredientes (
  id uuid primary key default gen_random_uuid(),
  restaurante_id uuid not null references public.restaurantes(id) on delete cascade,
  nombre text not null,
  unidad_base text not null default 'g' check (unidad_base in ('g','ml','unidad')),
  costo_por_unidad numeric(12,6) not null default 0 check (costo_por_unidad >= 0),
  costo_actualizado_en timestamptz,
  activo boolean not null default true,
  creado_en timestamptz not null default now(),
  unique (restaurante_id, nombre)
);

-- ===== Compras (renglones de factura; cada una actualiza el costo) =====
create table public.compras_ingredientes (
  id uuid primary key default gen_random_uuid(),
  restaurante_id uuid not null references public.restaurantes(id) on delete cascade,
  ingrediente_id uuid not null references public.ingredientes(id) on delete cascade,
  descripcion_original text,
  cantidad_base numeric(14,3) not null check (cantidad_base > 0),
  costo_total_usd numeric(12,2) not null check (costo_total_usd >= 0),
  fuente text not null default 'manual' check (fuente in ('manual','factura_ia')),
  creado_por uuid,
  creado_en timestamptz not null default now()
);
create index compras_ingredientes_rest_idx on public.compras_ingredientes (restaurante_id, creado_en desc);

-- ===== Recetas: que lleva cada plato (en la unidad base del ingrediente) =====
create table public.recetas_productos (
  producto_id uuid not null references public.productos(id) on delete cascade,
  ingrediente_id uuid not null references public.ingredientes(id) on delete cascade,
  cantidad_base numeric(14,3) not null check (cantidad_base > 0),
  nota text,
  creado_en timestamptz not null default now(),
  primary key (producto_id, ingrediente_id)
);

-- ===== RLS =====
alter table public.ingredientes enable row level security;
alter table public.compras_ingredientes enable row level security;
alter table public.recetas_productos enable row level security;

create policy ingredientes_gestion on public.ingredientes
  for all to authenticated
  using (fn_es_dueno_de(restaurante_id) or fn_es_encargado_de(restaurante_id) or fn_es_admin())
  with check (fn_es_dueno_de(restaurante_id) or fn_es_encargado_de(restaurante_id) or fn_es_admin());

create policy compras_gestion on public.compras_ingredientes
  for all to authenticated
  using (fn_es_dueno_de(restaurante_id) or fn_es_encargado_de(restaurante_id) or fn_es_admin())
  with check (fn_es_dueno_de(restaurante_id) or fn_es_encargado_de(restaurante_id) or fn_es_admin());

create policy recetas_gestion on public.recetas_productos
  for all to authenticated
  using (exists (
    select 1 from public.productos p
     where p.id = producto_id
       and (fn_es_dueno_de(p.restaurante_id) or fn_es_encargado_de(p.restaurante_id) or fn_es_admin())
  ))
  with check (exists (
    select 1 from public.productos p
     where p.id = producto_id
       and (fn_es_dueno_de(p.restaurante_id) or fn_es_encargado_de(p.restaurante_id) or fn_es_admin())
  ));

-- ===== RPC: registrar una compra y actualizar el costo de reposicion =====
create or replace function public.registrar_compra_ingrediente(
  p_ingrediente_id uuid,
  p_cantidad_base numeric,
  p_costo_total_usd numeric,
  p_descripcion text default null,
  p_fuente text default 'manual'
) returns uuid
language plpgsql security definer set search_path to 'public'
as $$
declare
  v_ing public.ingredientes%rowtype;
  v_id uuid;
begin
  select * into v_ing from public.ingredientes where id = p_ingrediente_id for update;
  if not found then raise exception 'Ingrediente no encontrado'; end if;
  if not (fn_es_dueno_de(v_ing.restaurante_id) or fn_es_encargado_de(v_ing.restaurante_id) or fn_es_admin()) then
    raise exception 'Sin permiso';
  end if;
  if coalesce(p_cantidad_base, 0) <= 0 or coalesce(p_costo_total_usd, -1) < 0 then
    raise exception 'Cantidad o costo invalido';
  end if;
  if p_fuente not in ('manual','factura_ia') then raise exception 'Fuente invalida'; end if;

  insert into public.compras_ingredientes (restaurante_id, ingrediente_id, descripcion_original, cantidad_base, costo_total_usd, fuente, creado_por)
    values (v_ing.restaurante_id, p_ingrediente_id, nullif(trim(coalesce(p_descripcion,'')),''), p_cantidad_base, p_costo_total_usd, p_fuente, auth.uid())
    returning id into v_id;

  -- Costo de reposicion: la ULTIMA compra manda.
  update public.ingredientes
     set costo_por_unidad = round(p_costo_total_usd / p_cantidad_base, 6),
         costo_actualizado_en = now()
   where id = p_ingrediente_id;

  return v_id;
end;
$$;
grant execute on function public.registrar_compra_ingrediente(uuid, numeric, numeric, text, text) to authenticated;

-- ===== RPC: costos y margenes por plato =====
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
)
language sql stable security definer set search_path to 'public'
as $$
  select pr.id,
         pr.nombre,
         pr.precio_usd,
         round(coalesce(sum(r.cantidad_base * i.costo_por_unidad), 0), 2) as costo_usd,
         round(pr.precio_usd - coalesce(sum(r.cantidad_base * i.costo_por_unidad), 0), 2) as margen_usd,
         case when pr.precio_usd > 0
              then round((pr.precio_usd - coalesce(sum(r.cantidad_base * i.costo_por_unidad), 0)) / pr.precio_usd * 100, 1)
              else null end as margen_pct,
         count(r.ingrediente_id)::int as ingredientes,
         (count(r.ingrediente_id) filter (where i.costo_por_unidad <= 0))::int as sin_precio
    from public.productos pr
    left join public.recetas_productos r on r.producto_id = pr.id
    left join public.ingredientes i on i.id = r.ingrediente_id
   where pr.restaurante_id = p_restaurante_id
     and (fn_es_dueno_de(p_restaurante_id) or fn_es_encargado_de(p_restaurante_id) or fn_es_admin())
   group by pr.id, pr.nombre, pr.precio_usd
   order by pr.nombre;
$$;
grant execute on function public.costos_productos(uuid) to authenticated;