DROP TRIGGER unhide_posts_with_blocked_words_AFTER_DELETE;

CREATE TRIGGER unhide_posts_with_blocked_words_AFTER_DELETE
AFTER DELETE ON blockedWord
BEGIN
  UPDATE OR IGNORE post
  SET isHidden = 0
  WHERE EXISTS (
    SELECT 1
    FROM blockedWord
    WHERE (post.title LIKE '%' || blockedWord.content || '%' OR
           post.description LIKE '%' || blockedWord.content || '%' OR
           post.rawContent LIKE '%' || blockedWord.content || '%')
  )
  AND isHidden = 1;
END;

DROP TRIGGER hide_post_if_blocked_word_is_present_BEFORE_UPDATE;

CREATE TRIGGER hide_post_if_blocked_word_is_present_BEFORE_UPDATE
BEFORE UPDATE OF title, description, rawContent ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    (new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%' OR
    new.rawContent LIKE '%' || blockedWord.content || '%') AND
    new.isHidden == 0
) IS NOT NULL
BEGIN
  UPDATE post SET isHidden = 1 WHERE id = new.id;
END;

CREATE TRIGGER unhide_post_if_no_blocked_words_present_and_post_is_hidden_BEFORE_UPDATE
UPDATE OF title, description, rawContent ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    (new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%' OR
    new.rawContent LIKE '%' || blockedWord.content || '%')
) IS NULL AND old.isHidden == 1
BEGIN
  UPDATE post SET isHidden = 0 WHERE id = new.id;
END;
