228 lines
7.8 KiB
PL/PgSQL
228 lines
7.8 KiB
PL/PgSQL
-- Function & Trigger for Root Comment Insertions (Approved/Verified)
|
|
CREATE OR REPLACE FUNCTION notify_root_comment_inserted()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
watcher_count INT;
|
|
BEGIN
|
|
RAISE NOTICE '[notify_root_comment_inserted] Trigger fired for comment ID: %', NEW.id;
|
|
WITH watchers AS (
|
|
SELECT np."userId", np."enableNotifyPendingRepliesOnWatch"
|
|
FROM "_onRootCommentCreatedForServices" rc
|
|
JOIN "NotificationPreferences" np ON rc."A" = np."id"
|
|
WHERE rc."B" = NEW."serviceId"
|
|
AND np."userId" <> NEW."authorId"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
SELECT w."userId",
|
|
'ROOT_COMMENT_CREATED',
|
|
NEW."id"
|
|
FROM watchers w
|
|
WHERE (
|
|
NEW.status IN ('APPROVED', 'VERIFIED')
|
|
OR (NEW.status = 'PENDING' AND w."enableNotifyPendingRepliesOnWatch")
|
|
)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
RAISE NOTICE '[notify_root_comment_inserted] Inserted % notifications for comment ID: %', FOUND, NEW.id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_root_comment_inserted ON "Comment";
|
|
CREATE TRIGGER trg_notify_root_comment_inserted
|
|
AFTER INSERT ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW."parentId" IS NULL)
|
|
EXECUTE FUNCTION notify_root_comment_inserted();
|
|
|
|
-- Function & Trigger for Reply Comment Insertions
|
|
CREATE OR REPLACE FUNCTION notify_reply_comment_inserted()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
WITH watchers AS (
|
|
SELECT np."userId", np."enableNotifyPendingRepliesOnWatch"
|
|
FROM "_watchedComments" w
|
|
JOIN "NotificationPreferences" np ON w."B" = np."id"
|
|
WHERE w."A" = NEW."parentId"
|
|
AND np."userId" <> NEW."authorId"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
SELECT w."userId",
|
|
'REPLY_COMMENT_CREATED',
|
|
NEW."id"
|
|
FROM watchers w
|
|
WHERE (
|
|
NEW.status IN ('APPROVED', 'VERIFIED')
|
|
OR (NEW.status = 'PENDING' AND w."enableNotifyPendingRepliesOnWatch")
|
|
)
|
|
ON CONFLICT DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_reply_comment_inserted ON "Comment";
|
|
CREATE TRIGGER trg_notify_reply_comment_inserted
|
|
AFTER INSERT ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW."parentId" IS NOT NULL)
|
|
EXECUTE FUNCTION notify_reply_comment_inserted();
|
|
|
|
-- Function & Trigger for Reply Approval/Verification
|
|
CREATE OR REPLACE FUNCTION notify_reply_approved()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
WITH watchers AS (
|
|
SELECT np."userId"
|
|
FROM "_watchedComments" w
|
|
JOIN "NotificationPreferences" np ON w."B" = np."id"
|
|
WHERE w."A" = NEW."parentId"
|
|
AND np."userId" <> NEW."authorId"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
SELECT w."userId",
|
|
'REPLY_COMMENT_CREATED',
|
|
NEW."id"
|
|
FROM watchers w
|
|
ON CONFLICT DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_reply_approved ON "Comment";
|
|
CREATE TRIGGER trg_notify_reply_approved
|
|
AFTER UPDATE OF status ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW."parentId" IS NOT NULL AND NEW.status IN ('APPROVED', 'VERIFIED') AND OLD.status NOT IN ('APPROVED', 'VERIFIED'))
|
|
EXECUTE FUNCTION notify_reply_approved();
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_root_approved ON "Comment";
|
|
|
|
CREATE OR REPLACE FUNCTION notify_root_approved()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
WITH watchers AS (
|
|
SELECT np."userId"
|
|
FROM "_onRootCommentCreatedForServices" rc
|
|
JOIN "NotificationPreferences" np ON rc."A" = np."id"
|
|
WHERE rc."B" = NEW."serviceId"
|
|
AND np."userId" <> NEW."authorId"
|
|
AND NOT np."enableNotifyPendingRepliesOnWatch"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
SELECT w."userId",
|
|
'ROOT_COMMENT_CREATED',
|
|
NEW."id"
|
|
FROM watchers w
|
|
ON CONFLICT DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_notify_root_approved
|
|
AFTER UPDATE OF status ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW."parentId" IS NULL AND NEW.status IN ('APPROVED', 'VERIFIED') AND OLD.status NOT IN ('APPROVED', 'VERIFIED'))
|
|
EXECUTE FUNCTION notify_root_approved();
|
|
|
|
-- Function & Trigger for Comment Status Changes (Status, Suspicious, AdminReview)
|
|
CREATE OR REPLACE FUNCTION notify_comment_status_changed()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_status_change "CommentStatusChange" := NULL;
|
|
BEGIN
|
|
-- Determine the status change type
|
|
IF NEW.status <> OLD.status THEN
|
|
IF NEW.status = 'APPROVED' THEN v_status_change := 'STATUS_CHANGED_TO_APPROVED';
|
|
ELSIF NEW.status = 'VERIFIED' THEN v_status_change := 'STATUS_CHANGED_TO_VERIFIED';
|
|
ELSIF NEW.status = 'REJECTED' THEN v_status_change := 'STATUS_CHANGED_TO_REJECTED';
|
|
ELSIF (NEW.status = 'PENDING' OR NEW.status = 'HUMAN_PENDING') AND (OLD.status <> 'PENDING' AND OLD.status <> 'HUMAN_PENDING') THEN v_status_change := 'STATUS_CHANGED_TO_PENDING';
|
|
END IF;
|
|
ELSIF NEW.suspicious <> OLD.suspicious THEN
|
|
IF NEW.suspicious = true THEN v_status_change := 'MARKED_AS_SPAM';
|
|
ELSE v_status_change := 'UNMARKED_AS_SPAM';
|
|
END IF;
|
|
ELSIF NEW."requiresAdminReview" <> OLD."requiresAdminReview" THEN
|
|
IF NEW."requiresAdminReview" = true THEN v_status_change := 'MARKED_FOR_ADMIN_REVIEW';
|
|
ELSE v_status_change := 'UNMARKED_FOR_ADMIN_REVIEW';
|
|
END IF;
|
|
END IF;
|
|
|
|
-- If a relevant status change occurred, notify watchers of THIS comment
|
|
IF v_status_change IS NOT NULL THEN
|
|
WITH watchers AS (
|
|
-- Get all watchers excluding author
|
|
SELECT np."userId"
|
|
FROM "_watchedComments" w
|
|
JOIN "NotificationPreferences" np ON w."B" = np."id"
|
|
WHERE w."A" = NEW."id"
|
|
AND np."userId" <> NEW."authorId"
|
|
AND np."enableOnMyCommentStatusChange"
|
|
|
|
UNION ALL
|
|
|
|
-- Add author if they have enabled notifications for their own comments
|
|
SELECT np."userId"
|
|
FROM "NotificationPreferences" np
|
|
WHERE np."userId" = NEW."authorId"
|
|
AND np."enableOnMyCommentStatusChange"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId", "aboutCommentStatusChange")
|
|
SELECT w."userId",
|
|
'COMMENT_STATUS_CHANGE',
|
|
NEW."id",
|
|
v_status_change
|
|
FROM watchers w
|
|
ON CONFLICT DO NOTHING;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_comment_status_changed ON "Comment";
|
|
CREATE TRIGGER trg_notify_comment_status_changed
|
|
AFTER UPDATE OF status, suspicious, "requiresAdminReview" ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW.status <> OLD.status OR NEW.suspicious <> OLD.suspicious OR NEW."requiresAdminReview" <> OLD."requiresAdminReview")
|
|
EXECUTE FUNCTION notify_comment_status_changed();
|
|
|
|
-- Function & Trigger for Community Note Added
|
|
CREATE OR REPLACE FUNCTION notify_community_note_added()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Notify watchers of this specific comment (excluding author)
|
|
WITH watchers AS (
|
|
SELECT np."userId"
|
|
FROM "_watchedComments" w
|
|
JOIN "NotificationPreferences" np ON w."B" = np."id"
|
|
WHERE w."A" = NEW."id"
|
|
AND np."userId" <> NEW."authorId"
|
|
)
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
SELECT w."userId",
|
|
'COMMUNITY_NOTE_ADDED',
|
|
NEW."id"
|
|
FROM watchers w
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- Always notify the author
|
|
INSERT INTO "Notification" ("userId", "type", "aboutCommentId")
|
|
VALUES (NEW."authorId", 'COMMUNITY_NOTE_ADDED', NEW."id")
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_notify_community_note_added ON "Comment";
|
|
CREATE TRIGGER trg_notify_community_note_added
|
|
AFTER UPDATE OF "communityNote" ON "Comment"
|
|
FOR EACH ROW
|
|
WHEN (NEW."communityNote" IS NOT NULL AND NEW."communityNote" <> '' AND (OLD."communityNote" IS NULL OR OLD."communityNote" = ''))
|
|
EXECUTE FUNCTION notify_community_note_added();
|
|
|
|
-- Remove the old monolithic trigger and function definition if they still exist
|
|
DROP TRIGGER IF EXISTS comment_notifications_trigger ON "Comment";
|
|
DROP FUNCTION IF EXISTS trigger_comment_notifications();
|