BEGIN TRANSACTION;

CREATE VIRTUAL TABLE IF NOT EXISTS post_search USING FTS5(
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    link TEXT NOT NULL PRIMARY KEY UNINDEXED,
    tokenize="trigram"
);

INSERT INTO post_search SELECT title, description, link FROM post;

COMMIT;

CREATE TRIGGER IF NOT EXISTS
post_search_fts_BEFORE_DELETE
BEFORE DELETE ON post
BEGIN DELETE FROM post_search WHERE link = old.link;
END;

CREATE TRIGGER IF NOT EXISTS
post_search_fts_AFTER_UPDATE
AFTER UPDATE ON post
BEGIN UPDATE post_search SET title = new.title, description = new.description WHERE link = new.link;
END;

CREATE TRIGGER IF NOT EXISTS
post_search_fts_AFTER_INSERT
AFTER INSERT ON post
BEGIN INSERT OR REPLACE INTO post_search(title, description, link) VALUES (new.title, new.description, new.link);
END;
