-- Allow self-mentions. Previously create_mention_notifications() skipped the
-- author (`where p.id is distinct from p_actor_id`), so @mentioning yourself did
-- nothing. rian wants self-pings (e.g. as reminders), so we drop that filter.
-- Everything else is identical to 0014.

create or replace function create_mention_notifications(
  p_content text,
  p_actor_id uuid,
  p_actor_email text,
  p_link text,
  p_src_table text,
  p_src_id uuid
) returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  rec record;
  preview text;
begin
  preview := regexp_replace(coalesce(p_content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  preview := btrim(regexp_replace(preview, '\s+', ' ', 'g'));
  preview := left(preview, 160);

  for rec in
    select distinct p.id as uid, p.email as email
    from regexp_matches(
           coalesce(p_content, ''),
           '@([A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,})',
           'g'
         ) as mm
    join profiles p on lower(p.email) = lower(mm[1])
  loop
    insert into notifications (recipient_id, recipient_email, actor_email, kind, body, link, source_table, source_id)
    values (rec.uid, rec.email, p_actor_email, 'mention', preview, p_link, p_src_table, p_src_id);
  end loop;
end;
$$;
