-- User @mention notifications.
--
-- When someone writes "@somebody@email.com" in a comment, issue-comment, note,
-- or issue, we create a notification row for that person (looked up in profiles
-- by email). The notification carries a deep-link back to the exact place. All
-- done server-side via AFTER-INSERT triggers (security definer) so it can't be
-- bypassed and needs no client changes. The bell UI reads these over Realtime.

create table if not exists notifications (
  id             uuid primary key default gen_random_uuid(),
  recipient_id   uuid not null references auth.users (id) on delete cascade,
  recipient_email text not null,
  actor_email    text,
  kind           text not null default 'mention',
  body           text not null default '',   -- short preview of the message
  link           text,                        -- app path to open (e.g. /brentwood?focus=page:<id>&note=<id>#comment-<id>)
  source_table   text,                        -- 'comments' | 'issue_comments' | 'notes' | 'issues'
  source_id      uuid,
  read           boolean not null default false,
  created_at     timestamptz not null default now()
);
create index if not exists notifications_recipient_idx
  on notifications (recipient_id, read, created_at desc);

alter table notifications enable row level security;

-- Recipients see and update (mark read) only their own. No client INSERT: the
-- triggers below are security definer and do the writing.
drop policy if exists "own notifications read" on notifications;
create policy "own notifications read" on notifications
  for select to authenticated using (recipient_id = auth.uid());

drop policy if exists "own notifications update" on notifications;
create policy "own notifications update" on notifications
  for update to authenticated using (recipient_id = auth.uid()) with check (recipient_id = auth.uid());

do $$ begin
  alter publication supabase_realtime add table notifications;
exception when duplicate_object then null;
end $$;

-- ---------------------------------------------------------------------------
-- Shared helper: parse @email mentions out of `p_content`, match them to
-- profiles, and insert one notification per distinct mentioned user (never the
-- author themselves).
-- ---------------------------------------------------------------------------
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
  -- strip markdown image tags, collapse whitespace, trim for a clean preview
  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])
    where p.id is distinct from p_actor_id
  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;
$$;

-- ---------------------------------------------------------------------------
-- Per-source triggers. Each computes the project slug + deep-link, then calls
-- the shared helper. AFTER insert, so the set_author() BEFORE trigger has
-- already populated author_id / author_email.
-- ---------------------------------------------------------------------------

-- Comments (on page or feature notes)
create or replace function notify_comment_mentions() returns trigger
language plpgsql security definer set search_path = public as $$
declare v_focus text; v_slug text;
begin
  select case when n.page_id is not null then 'page:' || n.page_id else 'feature:' || n.feature_id end,
         pr.slug
    into v_focus, v_slug
  from notes n
  left join pages pg on pg.id = n.page_id
  left join features ft on ft.id = n.feature_id
  join projects pr on pr.id = coalesce(pg.project_id, ft.project_id)
  where n.id = new.note_id;

  perform create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=' || v_focus || '&note=' || new.note_id || '#comment-' || new.id,
    'comments', new.id
  );
  return new;
end; $$;
drop trigger if exists comments_notify_mentions on comments;
create trigger comments_notify_mentions after insert on comments
  for each row execute function notify_comment_mentions();

-- Notes themselves
create or replace function notify_note_mentions() returns trigger
language plpgsql security definer set search_path = public as $$
declare v_focus text; v_slug text;
begin
  v_focus := case when new.page_id is not null then 'page:' || new.page_id else 'feature:' || new.feature_id end;
  select pr.slug into v_slug
  from projects pr
  left join pages pg on pg.id = new.page_id
  left join features ft on ft.id = new.feature_id
  where pr.id = coalesce(pg.project_id, ft.project_id);

  perform create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=' || v_focus || '&note=' || new.id || '#note-' || new.id,
    'notes', new.id
  );
  return new;
end; $$;
drop trigger if exists notes_notify_mentions on notes;
create trigger notes_notify_mentions after insert on notes
  for each row execute function notify_note_mentions();

-- Issue comments
create or replace function notify_issue_comment_mentions() returns trigger
language plpgsql security definer set search_path = public as $$
declare v_slug text;
begin
  select pr.slug into v_slug from issues i join projects pr on pr.id = i.project_id where i.id = new.issue_id;
  perform create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=issue:' || new.issue_id || '#issuecomment-' || new.id,
    'issue_comments', new.id
  );
  return new;
end; $$;
drop trigger if exists issue_comments_notify_mentions on issue_comments;
create trigger issue_comments_notify_mentions after insert on issue_comments
  for each row execute function notify_issue_comment_mentions();

-- Issues themselves
create or replace function notify_issue_mentions() returns trigger
language plpgsql security definer set search_path = public as $$
declare v_slug text;
begin
  select pr.slug into v_slug from projects pr where pr.id = new.project_id;
  perform create_mention_notifications(
    new.content, new.author_id, new.author_email,
    '/' || v_slug || '?focus=issue:' || new.id,
    'issues', new.id
  );
  return new;
end; $$;
drop trigger if exists issues_notify_mentions on issues;
create trigger issues_notify_mentions after insert on issues
  for each row execute function notify_issue_mentions();
