DROP TRIGGER IF EXISTS hide_posts_with_blocked_words_AFTER_INSERT;
DROP TRIGGER IF EXISTS hide_posts_with_blocked_words_AFTER_UPDATE;
DROP TRIGGER IF EXISTS unhide_posts_with_blocked_words_AFTER_UPDATE;
DROP TRIGGER IF EXISTS unhide_posts_with_blocked_words_AFTER_DELETE;
DROP TRIGGER IF EXISTS hide_post_if_blocked_word_is_present_AFTER_INSERT;
DROP TRIGGER IF EXISTS hide_post_if_blocked_word_is_present_BEFORE_UPDATE;
DROP TRIGGER IF EXISTS unhide_post_if_no_blocked_words_present_and_post_is_hidden_BEFORE_UPDATE;

CREATE TRIGGER hide_posts_with_blocked_words_AFTER_INSERT
AFTER INSERT ON blockedWord
WHEN (new.isDeleted = 0)
BEGIN
  UPDATE OR IGNORE post
  SET flags = flags | 4
  WHERE
    (title LIKE '%' || new.content || '%' OR
    description LIKE '%' || new.content || '%') AND
    (flags & 4) == 0;
END;

CREATE TRIGGER hide_posts_with_blocked_words_AFTER_UPDATE
AFTER UPDATE ON blockedWord
WHEN (new.isDeleted = 0 AND old.isDeleted = 1)
BEGIN
  UPDATE OR IGNORE post
  SET flags = flags | 4
  WHERE
    (title LIKE '%' || new.content || '%' OR
    description LIKE '%' || new.content || '%') AND
    (flags & 4) == 0;
END;

CREATE TRIGGER unhide_posts_with_blocked_words_AFTER_UPDATE
AFTER UPDATE ON blockedWord
WHEN (new.isDeleted = 1 AND old.isDeleted = 0)
BEGIN
  UPDATE OR IGNORE post
  SET flags = flags & ~4
  WHERE NOT EXISTS (
    SELECT 1
    FROM blockedWord
    WHERE (post.title LIKE '%' || blockedWord.content || '%' OR
           post.description LIKE '%' || blockedWord.content || '%')
    AND isDeleted = 0
  )
  AND (flags & 4) != 0;
END;

CREATE TRIGGER unhide_posts_with_blocked_words_AFTER_DELETE
AFTER DELETE ON blockedWord
BEGIN
  UPDATE OR IGNORE post
  SET flags = flags & ~4
  WHERE NOT EXISTS (
    SELECT 1
    FROM blockedWord
    WHERE (post.title LIKE '%' || blockedWord.content || '%' OR
           post.description LIKE '%' || blockedWord.content || '%')
    AND isDeleted = 0
  )
  AND (flags & 4) != 0;
END;

CREATE TRIGGER hide_post_if_blocked_word_is_present_AFTER_INSERT
AFTER INSERT ON post
FOR EACH ROW
WHEN (
  SELECT 1
  FROM blockedWord
  WHERE
    (new.title LIKE '%' || blockedWord.content || '%' OR
    new.description LIKE '%' || blockedWord.content || '%') AND
    blockedWord.isDeleted = 0
) IS NOT NULL
BEGIN
  UPDATE post SET flags = flags | 4 WHERE id = new.id;
END;

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

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