CREATE OR REPLACE FUNCTION trigger_service_suggestion_notifications()
RETURNS TRIGGER AS $$
DECLARE
  suggestion_status_change "ServiceSuggestionStatusChange";
BEGIN
  IF TG_OP = 'INSERT' AND TG_TABLE_NAME = 'ServiceSuggestion' THEN -- Corresponds to ServiceSuggestion insert
    -- Notify all admins when a new suggestion is created
    INSERT INTO "Notification" ("userId", "type", "aboutServiceSuggestionId")
    SELECT u."id", 'SUGGESTION_CREATED', NEW."id"
    FROM "User" u
    WHERE u."admin" = true
      AND NOT EXISTS (
        SELECT 1 FROM "Notification" n
        WHERE n."userId" = u."id"
          AND n."type" = 'SUGGESTION_CREATED'
          AND n."aboutServiceSuggestionId" = NEW."id"
      );

  ELSIF TG_OP = 'INSERT' AND TG_TABLE_NAME = 'ServiceSuggestionMessage' THEN -- Corresponds to ServiceSuggestionMessage insert
    -- Notify suggestion author (if not the sender)
    INSERT INTO "Notification" ("userId", "type", "aboutServiceSuggestionId", "aboutServiceSuggestionMessageId")
    SELECT s."userId", 'SUGGESTION_MESSAGE', NEW."suggestionId", NEW."id"
    FROM "ServiceSuggestion" s
    WHERE s."id" = NEW."suggestionId"
      AND s."userId" <> NEW."userId"
      AND NOT EXISTS (
        SELECT 1 FROM "Notification" n
        WHERE n."userId" = s."userId"
          AND n."type" = 'SUGGESTION_MESSAGE'
          AND n."aboutServiceSuggestionMessageId" = NEW."id"
      );

    -- Notify all admins (except the sender), but only if sender is not admin
    INSERT INTO "Notification" ("userId", "type", "aboutServiceSuggestionId", "aboutServiceSuggestionMessageId")
    SELECT u."id", 'SUGGESTION_MESSAGE', NEW."suggestionId", NEW."id"
    FROM "User" u
    WHERE u."admin" = true
      AND u."id" <> NEW."userId"
      -- Only notify admins if the message sender is not an admin
      AND NOT EXISTS (SELECT 1 FROM "User" WHERE "id" = NEW."userId" AND "admin" = true)
      AND NOT EXISTS (
        SELECT 1 FROM "Notification" n
        WHERE n."userId" = u."id"
          AND n."type" = 'SUGGESTION_MESSAGE'
          AND n."aboutServiceSuggestionMessageId" = NEW."id"
      );

  ELSIF TG_OP = 'UPDATE' THEN -- Corresponds to ServiceSuggestion status update
    -- Notify suggestion author about status change
    IF NEW.status <> OLD.status THEN
      IF NEW.status = 'PENDING' THEN
        suggestion_status_change := 'STATUS_CHANGED_TO_PENDING';
      ELSIF NEW.status = 'APPROVED' THEN
        suggestion_status_change := 'STATUS_CHANGED_TO_APPROVED';
      ELSIF NEW.status = 'REJECTED' THEN
        suggestion_status_change := 'STATUS_CHANGED_TO_REJECTED';
      ELSIF NEW.status = 'WITHDRAWN' THEN
        suggestion_status_change := 'STATUS_CHANGED_TO_WITHDRAWN';
      END IF;

      INSERT INTO "Notification" ("userId", "type", "aboutServiceSuggestionId", "aboutSuggestionStatusChange")
      VALUES (NEW."userId", 'SUGGESTION_STATUS_CHANGE', NEW."id", suggestion_status_change);
    END IF;
  END IF;

  -- Use RETURN NULL for AFTER triggers as the return value is ignored.
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Trigger for new suggestions
DROP TRIGGER IF EXISTS service_suggestion_created_notifications_trigger ON "ServiceSuggestion";
CREATE TRIGGER service_suggestion_created_notifications_trigger
  AFTER INSERT ON "ServiceSuggestion"
  FOR EACH ROW
  EXECUTE FUNCTION trigger_service_suggestion_notifications();

-- Trigger for new messages
DROP TRIGGER IF EXISTS service_suggestion_message_notifications_trigger ON "ServiceSuggestionMessage";
CREATE TRIGGER service_suggestion_message_notifications_trigger
  AFTER INSERT ON "ServiceSuggestionMessage"
  FOR EACH ROW
  EXECUTE FUNCTION trigger_service_suggestion_notifications();

-- Trigger for status updates
DROP TRIGGER IF EXISTS service_suggestion_status_notifications_trigger ON "ServiceSuggestion";
CREATE TRIGGER service_suggestion_status_notifications_trigger
  AFTER UPDATE OF status ON "ServiceSuggestion"
  FOR EACH ROW
  -- Only run the function if the status actually changed
  WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION trigger_service_suggestion_notifications();