CREATE VIRTUAL TABLE IF NOT EXISTS feed_search USING FTS5(
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  tokenize="trigram"
);

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_BEFORE_DELETE
BEFORE DELETE ON feed
BEGIN DELETE FROM feed_search WHERE id = old.id;
END;

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_AFTER_UPDATE
AFTER UPDATE ON feed
BEGIN UPDATE OR IGNORE feed_search SET name = new.name WHERE id = new.id;
END;

CREATE TRIGGER IF NOT EXISTS
feed_search_fts_AFTER_INSERT
AFTER INSERT ON feed
BEGIN INSERT OR IGNORE INTO feed_search(id, name, link) VALUES (new.id, new.name, new.link);
END;

countSearchResults:
SELECT COUNT(*) FROM feed_search fs
INNER JOIN feed f ON f.id = fs.id
WHERE (
  :searchQuery = '""'
  OR
  fs.id IN (SELECT id FROM feed_search WHERE feed_search MATCH :searchQuery)
) AND f.isDeleted = 0;

search:
SELECT
  f.id,
  f.name,
  f.icon,
  f.description,
  f.link,
  f.homepageLink,
  f.createdAt,
  f.pinnedAt,
  f.lastCleanUpAt,
  f.alwaysFetchSourceArticle,
  f.pinnedPosition,
  f.lastUpdatedAt,
  f.refreshInterval,
  f.isDeleted,
  f.enableNotifications,
  f.remoteId,
  COUNT(CASE WHEN (p.flags & 2) == 0 THEN 1 ELSE NULL END) AS numberOfUnreadPosts,
  f.showFeedFavIcon,
  f.hideFromAllFeeds
FROM feed_search fs
INNER JOIN feed f ON f.id = fs.id
LEFT JOIN post p ON p.sourceId = f.id AND p.postDate > :postsAfter
WHERE fs.name MATCH :searchQuery AND f.isDeleted = 0
GROUP BY f.id
ORDER BY pinnedAt DESC , f.createdAt DESC
LIMIT :limit OFFSET :offset;
