-- Rounds device_category and manufacturer out to full parity with the
-- other catalog entity types: submission/review workflow (already halfway
-- there — catalog_submissions.entity_type has allowed both since the
-- initial schema, and the app-side plumbing for device_category already
-- exists in the review queue and My Submissions), plus direct rename of
-- your own private entry / an already-public one by an Admin.
--
-- No new tables or RLS policies — device_categories/manufacturers already
-- have full is_public/owner_id CRUD policies from the initial schema
-- (device_categories is explicitly the "representative case" the RLS test
-- suite already covers for this shape), just never exercised by app code
-- for anything beyond insert. This migration only extends the one function
-- that didn't already know about manufacturers.

create or replace function public.catalog_entity_usage_impact(p_entity_type text, p_entity_id text)
returns table(diagram_count integer, sample jsonb)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.is_admin() then
    raise exception 'insufficient_privilege' using errcode = '42501';
  end if;

  return query
    select count(*)::int, coalesce(jsonb_agg(jsonb_build_object('id', s.id, 'name', s.name, 'ownerUsername', s.owner_username) order by s.rn) filter (where s.rn <= 5), '[]'::jsonb)
    from (
      select d.id, d.name, p.username as owner_username,
             row_number() over (order by d.updated_at desc) as rn
      from public.diagrams d
      join public.profiles p on p.id = d.owner_id
      where case p_entity_type
        when 'device_template' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev
          where dev ->> 'templateId' = p_entity_id
        )
        when 'device_category' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev
          where dev ->> 'category' = p_entity_id
        )
        when 'manufacturer' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev
          where dev ->> 'manufacturerId' = p_entity_id
        )
        when 'port_type' then exists (
          select 1
          from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev,
               jsonb_array_elements(coalesce(dev -> 'ports', '[]'::jsonb)) port
          where port ->> 'portTypeId' = p_entity_id
        )
        when 'cable_type' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'connections', '[]'::jsonb)) conn
          where conn ->> 'cableTypeId' = p_entity_id
        )
        else false
      end
    ) s;
end;
$$;