CREATE OR REPLACE FUNCTION public.icons_renumber_contiguous()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  WITH ordered AS (
    SELECT id, row_number() OVER (ORDER BY number NULLS LAST, created_at ASC, id ASC) AS rn
    FROM public.icons
  )
  UPDATE public.icons i
  SET number = o.rn
  FROM ordered o
  WHERE i.id = o.id;
END;
$$;

CREATE OR REPLACE FUNCTION public.icons_assign_next_number()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  candidate int;
BEGIN
  IF NEW.number IS NOT NULL THEN
    RETURN NEW;
  END IF;

  SELECT min(t.n)
    INTO candidate
  FROM (
    SELECT generate_series(1, coalesce((SELECT max(number) FROM public.icons), 0) + 1) AS n
  ) t
  LEFT JOIN public.icons i ON i.number = t.n
  WHERE i.number IS NULL;

  NEW.number := coalesce(candidate, 1);
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_icons_assign_number ON public.icons;
CREATE TRIGGER trg_icons_assign_number
BEFORE INSERT ON public.icons
FOR EACH ROW
EXECUTE PROCEDURE public.icons_assign_next_number();

CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_icons_updated_at ON public.icons;
CREATE TRIGGER trg_icons_updated_at
BEFORE UPDATE ON public.icons
FOR EACH ROW
EXECUTE PROCEDURE public.set_updated_at();