-- Adding `read` column to post table and marking all exisiting posts as read --
ALTER TABLE post ADD COLUMN read INTEGER NOT NULL DEFAULT 0;

UPDATE post SET read = 1;

-- Adding `read` column to bookmarks table and updating triggers --

DROP TRIGGER IF EXISTS post_bookmarked;
DROP TRIGGER IF EXISTS post_content_update;

ALTER TABLE bookmark ADD COLUMN read INTEGER NOT NULL DEFAULT 0;

UPDATE bookmark SET read = 1;

CREATE TRIGGER IF NOT EXISTS
post_bookmarked
AFTER UPDATE OF bookmarked ON post WHEN new.bookmarked == 1
BEGIN
  INSERT OR REPLACE INTO bookmark(title, description, imageUrl, date, link, bookmarked, commentsLink, feedName, feedIcon, feedLink, read)
  SELECT new.title, new.description, new.imageUrl, new.date, new.link, new.bookmarked, new.commentsLink, feed.name, feed.icon, feed.link, new.read
  FROM feed WHERE link == new.feedLink;
END;

CREATE TRIGGER IF NOT EXISTS
post_content_update
AFTER UPDATE OF title, description, imageUrl, date, read ON post WHEN new.bookmarked == 1
BEGIN
  UPDATE OR IGNORE bookmark SET title = new.title, description = new.description, imageUrl = new.imageUrl, date = new.date, commentsLink = new.commentsLink, read = new.read
  WHERE link = new.link;
END;
