-- Notification enhancements: category display, resolved filtering,
-- action-to notifications, thread participation notifications,
-- and resolved-propagation triggers.

-- ---------------------------------------------------------------------------
-- 1. Schema changes
-- ---------------------------------------------------------------------------

ALTER TABLE notifications ADD COLUMN IF NOT EXISTS category text;
ALTER TABLE notifications ADD COLUMN IF NOT EXISTS resolved boolean NOT NULL DEFAULT false;

-- ---------------------------------------------------------------------------
-- 2. Replace create_mention_notifications to accept + store category
-- ---------------------------------------------------------------------------

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,
  p_category text DEFAULT NULL
) RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
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, category, body, link, source_table, source_id)
    VALUES (rec.uid, rec.email, p_actor_email, 'mention', p_category, preview, p_link, p_src_table, p_src_id);
  END LOOP;
END;
$$;

-- ---------------------------------------------------------------------------
-- 3. Update existing 4 triggers to pass category
-- ---------------------------------------------------------------------------

-- 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; v_category text;
BEGIN
  SELECT CASE WHEN n.page_id IS NOT NULL THEN 'page:' || n.page_id ELSE 'feature:' || n.feature_id END,
         pr.slug, n.category
    INTO v_focus, v_slug, v_category
  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, v_category
  );
  RETURN new;
END; $$;

-- 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, new.category
  );
  RETURN new;
END; $$;

-- 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; v_category text;
BEGIN
  SELECT pr.slug, i.category INTO v_slug, v_category
  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, v_category
  );
  RETURN new;
END; $$;

-- 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, new.category
  );
  RETURN new;
END; $$;

-- ---------------------------------------------------------------------------
-- 4. Thread participation: notify note author + past commenters on new comment
--    (skips people who are already getting a @mention notification for this)
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION notify_thread_participants() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_focus text; v_slug text; v_category text;
  v_link text; v_preview text;
  mentioned_emails text[];
  rec record;
BEGIN
  SELECT CASE WHEN n.page_id IS NOT NULL THEN 'page:' || n.page_id ELSE 'feature:' || n.feature_id END,
         pr.slug, n.category
    INTO v_focus, v_slug, v_category
  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;

  v_link := '/' || v_slug || '?focus=' || v_focus || '&note=' || new.note_id || '#comment-' || new.id;
  v_preview := regexp_replace(coalesce(new.content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  v_preview := btrim(regexp_replace(v_preview, '\s+', ' ', 'g'));
  v_preview := left(v_preview, 160);

  SELECT array_agg(DISTINCT lower(mm[1]))
    INTO mentioned_emails
  FROM regexp_matches(coalesce(new.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]);

  FOR rec IN
    SELECT DISTINCT sub.uid, sub.email FROM (
      -- Note author
      SELECT n.author_id AS uid, n.author_email AS email
      FROM notes n WHERE n.id = new.note_id
      UNION
      -- Past commenters
      SELECT c.author_id AS uid, c.author_email AS email
      FROM comments c WHERE c.note_id = new.note_id AND c.id != new.id
    ) sub
    WHERE sub.uid IS DISTINCT FROM new.author_id
      AND (mentioned_emails IS NULL OR NOT lower(sub.email) = ANY(mentioned_emails))
  LOOP
    INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, body, link, source_table, source_id)
    VALUES (rec.uid, rec.email, new.author_email, 'thread_reply', v_category, v_preview, v_link, 'comments', new.id);
  END LOOP;
  RETURN new;
END; $$;

DROP TRIGGER IF EXISTS comments_notify_thread ON comments;
CREATE TRIGGER comments_notify_thread AFTER INSERT ON comments
  FOR EACH ROW EXECUTE FUNCTION notify_thread_participants();

-- Thread participation for issue comments
CREATE OR REPLACE FUNCTION notify_issue_thread_participants() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_slug text; v_category text;
  v_link text; v_preview text;
  mentioned_emails text[];
  rec record;
BEGIN
  SELECT pr.slug, i.category INTO v_slug, v_category
  FROM issues i JOIN projects pr ON pr.id = i.project_id
  WHERE i.id = new.issue_id;

  v_link := '/' || v_slug || '?focus=issue:' || new.issue_id || '#issuecomment-' || new.id;
  v_preview := regexp_replace(coalesce(new.content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  v_preview := btrim(regexp_replace(v_preview, '\s+', ' ', 'g'));
  v_preview := left(v_preview, 160);

  SELECT array_agg(DISTINCT lower(mm[1]))
    INTO mentioned_emails
  FROM regexp_matches(coalesce(new.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]);

  FOR rec IN
    SELECT DISTINCT sub.uid, sub.email FROM (
      -- Issue author
      SELECT i.author_id AS uid, i.author_email AS email
      FROM issues i WHERE i.id = new.issue_id
      UNION
      -- Past commenters
      SELECT ic.author_id AS uid, ic.author_email AS email
      FROM issue_comments ic WHERE ic.issue_id = new.issue_id AND ic.id != new.id
    ) sub
    WHERE sub.uid IS DISTINCT FROM new.author_id
      AND (mentioned_emails IS NULL OR NOT lower(sub.email) = ANY(mentioned_emails))
  LOOP
    INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, body, link, source_table, source_id)
    VALUES (rec.uid, rec.email, new.author_email, 'thread_reply', v_category, v_preview, v_link, 'issue_comments', new.id);
  END LOOP;
  RETURN new;
END; $$;

DROP TRIGGER IF EXISTS issue_comments_notify_thread ON issue_comments;
CREATE TRIGGER issue_comments_notify_thread AFTER INSERT ON issue_comments
  FOR EACH ROW EXECUTE FUNCTION notify_issue_thread_participants();

-- ---------------------------------------------------------------------------
-- 5. Action-to notifications: when someone is assigned to an issue
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION notify_action_to() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_slug text; v_link text; v_preview text;
  v_actor_email text;
  new_assignees text[];
  assignee text;
  rec record;
BEGIN
  IF TG_OP = 'INSERT' THEN
    new_assignees := new.action_to;
  ELSE
    -- Only notify newly added assignees
    SELECT array_agg(e) INTO new_assignees
    FROM unnest(new.action_to) AS e
    WHERE e != ALL(coalesce(old.action_to, ARRAY[]::text[]));
  END IF;

  IF new_assignees IS NULL OR array_length(new_assignees, 1) IS NULL THEN
    RETURN new;
  END IF;

  SELECT pr.slug INTO v_slug FROM projects pr WHERE pr.id = new.project_id;
  v_link := '/' || v_slug || '?focus=issue:' || new.id;

  v_preview := regexp_replace(coalesce(new.content, ''), '!\[[^\]]*\]\([^)]*\)', '', 'g');
  v_preview := btrim(regexp_replace(v_preview, '\s+', ' ', 'g'));
  v_preview := left(v_preview, 160);

  -- Determine actor: on INSERT it's the author; on UPDATE look up the current user
  IF TG_OP = 'INSERT' THEN
    v_actor_email := new.author_email;
  ELSE
    SELECT p.email INTO v_actor_email FROM profiles p WHERE p.id = auth.uid();
    IF v_actor_email IS NULL THEN v_actor_email := new.author_email; END IF;
  END IF;

  FOREACH assignee IN ARRAY new_assignees
  LOOP
    FOR rec IN
      SELECT p.id AS uid, p.email FROM profiles p WHERE lower(p.email) = lower(assignee)
    LOOP
      INSERT INTO notifications (recipient_id, recipient_email, actor_email, kind, category, body, link, source_table, source_id)
      VALUES (rec.uid, rec.email, v_actor_email, 'action_to', new.category, v_preview, v_link, 'issues', new.id);
    END LOOP;
  END LOOP;
  RETURN new;
END; $$;

DROP TRIGGER IF EXISTS issues_notify_action_to ON issues;
CREATE TRIGGER issues_notify_action_to AFTER INSERT OR UPDATE OF action_to ON issues
  FOR EACH ROW EXECUTE FUNCTION notify_action_to();

-- ---------------------------------------------------------------------------
-- 6. Resolved propagation: keep notifications.resolved in sync
-- ---------------------------------------------------------------------------

-- When a note is resolved/reopened, update its notifications + its comments' notifications
CREATE OR REPLACE FUNCTION propagate_note_resolved() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF old.resolved IS DISTINCT FROM new.resolved THEN
    UPDATE notifications SET resolved = new.resolved
    WHERE (source_table = 'notes' AND source_id = new.id)
       OR (source_table = 'comments' AND source_id IN (SELECT id FROM comments WHERE note_id = new.id));
  END IF;
  RETURN new;
END; $$;

DROP TRIGGER IF EXISTS notes_propagate_resolved ON notes;
CREATE TRIGGER notes_propagate_resolved AFTER UPDATE OF resolved ON notes
  FOR EACH ROW EXECUTE FUNCTION propagate_note_resolved();

-- When an issue status changes to/from Resolved, update its notifications + its comments' notifications
CREATE OR REPLACE FUNCTION propagate_issue_resolved() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_resolved boolean;
BEGIN
  IF old.status IS DISTINCT FROM new.status THEN
    v_resolved := (new.status = 'Resolved');
    UPDATE notifications SET resolved = v_resolved
    WHERE (source_table = 'issues' AND source_id = new.id)
       OR (source_table = 'issue_comments' AND source_id IN (SELECT id FROM issue_comments WHERE issue_id = new.id));
  END IF;
  RETURN new;
END; $$;

DROP TRIGGER IF EXISTS issues_propagate_resolved ON issues;
CREATE TRIGGER issues_propagate_resolved AFTER UPDATE OF status ON issues
  FOR EACH ROW EXECUTE FUNCTION propagate_issue_resolved();

-- ---------------------------------------------------------------------------
-- 7. Backfill existing notifications
-- ---------------------------------------------------------------------------

UPDATE notifications n SET category = sub.cat FROM (
  SELECT n2.id AS nid,
    CASE n2.source_table
      WHEN 'notes'          THEN (SELECT nt.category FROM notes nt WHERE nt.id = n2.source_id)
      WHEN 'comments'       THEN (SELECT nt.category FROM notes nt JOIN comments c ON c.note_id = nt.id WHERE c.id = n2.source_id)
      WHEN 'issues'         THEN (SELECT i.category  FROM issues i WHERE i.id = n2.source_id)
      WHEN 'issue_comments' THEN (SELECT i.category  FROM issues i JOIN issue_comments ic ON ic.issue_id = i.id WHERE ic.id = n2.source_id)
    END AS cat
  FROM notifications n2 WHERE n2.category IS NULL
) sub WHERE n.id = sub.nid;

UPDATE notifications n SET resolved = true FROM (
  SELECT n2.id AS nid FROM notifications n2
  WHERE n2.resolved = false AND (
    (n2.source_table = 'notes'          AND EXISTS (SELECT 1 FROM notes nt WHERE nt.id = n2.source_id AND nt.resolved = true))
    OR (n2.source_table = 'comments'    AND EXISTS (SELECT 1 FROM comments c JOIN notes nt ON nt.id = c.note_id WHERE c.id = n2.source_id AND nt.resolved = true))
    OR (n2.source_table = 'issues'      AND EXISTS (SELECT 1 FROM issues i WHERE i.id = n2.source_id AND i.status = 'Resolved'))
    OR (n2.source_table = 'issue_comments' AND EXISTS (SELECT 1 FROM issue_comments ic JOIN issues i ON i.id = ic.issue_id WHERE ic.id = n2.source_id AND i.status = 'Resolved'))
  )
) sub WHERE n.id = sub.nid;
