CREATE INDEX index_name ON table_name USING fts (column1, column2, ...);

-- All columns use the default tokenizer
CREATE INDEX idx_articles ON articles USING fts (title, body);

-- Per-column tokenizer configuration
CREATE INDEX idx_articles ON articles USING fts (
    title WITH tokenizer=simple,
    body WITH tokenizer=ngram
);

-- Global tokenizer for all columns
CREATE INDEX idx_tags ON tags USING fts (tag) WITH (tokenizer = 'raw');

CREATE INDEX idx_articles ON articles USING fts (title, body)
    WITH (weights = 'title=2.0,body=1.0');

fts_match(column1, column2, ..., query)

SELECT id, title FROM articles
WHERE fts_match(title, body, 'database');

-- Single column
SELECT id, title FROM articles
WHERE fts_match(body, 'machine learning');

fts_score(column1, column2, ..., query)

SELECT
    id,
    title,
    fts_score(title, body, 'database') AS score
FROM articles
ORDER BY score DESC
LIMIT 10;

fts_highlight(column1, column2, ..., open_tag, close_tag, query)

SELECT fts_highlight(title, '<b>', '</b>', 'database') AS highlighted
FROM articles
WHERE fts_match(title, body, 'database');
-- <b>Database</b> Design Patterns

-- Multiple columns are concatenated
SELECT fts_highlight(title, body, '<mark>', '</mark>', 'database') AS highlighted
FROM articles
WHERE fts_match(title, body, 'database');

-- Standalone usage (without an FTS index)
SELECT fts_highlight('The quick brown fox', '<em>', '</em>', 'quick fox');
-- The <em>quick</em> brown <em>fox</em>

-- Simple term search
SELECT * FROM articles WHERE fts_match(title, body, 'database');

-- Phrase search: match "full text search" as an exact phrase
SELECT * FROM articles WHERE fts_match(title, body, '"full text search"');

-- Boolean AND: both terms must be present
SELECT * FROM articles WHERE fts_match(title, body, 'database AND performance');

-- Prefix search: match "optim", "optimize", "optimization", etc.
SELECT * FROM articles WHERE fts_match(title, body, 'optim*');

-- Column-specific: only match "rust" in the title field
SELECT * FROM articles WHERE fts_match(title, body, 'title:rust');

-- Boosted search: title matches count 2x more toward the score
SELECT id, title, fts_score(title, body, 'title:database^2 body:database') AS score
FROM articles
ORDER BY score DESC;

-- Exclusion: match "database" but not "nosql"
SELECT * FROM articles WHERE fts_match(title, body, 'database NOT nosql');

-- Create a table
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    author TEXT
);

-- Create an FTS index on title and body with field weights
CREATE INDEX idx_articles_fts ON articles USING fts (title, body)
    WITH (weights = 'title=2.0,body=1.0');

-- Insert sample data
INSERT INTO articles VALUES (1, 'Introduction to Databases', 'Databases store and organize data for efficient retrieval.', 'Alice');
INSERT INTO articles VALUES (2, 'Full-Text Search in Practice', 'Full-text search allows finding documents by content.', 'Bob');
INSERT INTO articles VALUES (3, 'Database Performance Tuning', 'Optimizing database queries requires understanding indexes.', 'Alice');
INSERT INTO articles VALUES (4, 'Getting Started with Rust', 'Rust is a systems programming language focused on safety.', 'Carol');

-- Simple search: find articles mentioning "database"
SELECT id, title FROM articles
WHERE fts_match(title, body, 'database');
-- 1 | Introduction to Databases
-- 3 | Database Performance Tuning

-- Ranked search: order by relevance
SELECT
    id,
    title,
    fts_score(title, body, 'database') AS score
FROM articles
WHERE fts_match(title, body, 'database')
ORDER BY score DESC
LIMIT 10;

-- Highlighted results
SELECT
    id,
    fts_highlight(title, '<b>', '</b>', 'database') AS title,
    fts_highlight(body, '<b>', '</b>', 'database') AS body
FROM articles
WHERE fts_match(title, body, 'database');

-- Combine FTS with regular SQL filters
SELECT id, title, author,
    fts_score(title, body, 'database') AS score
FROM articles
WHERE fts_match(title, body, 'database')
  AND author = 'Alice'
ORDER BY score DESC;

-- Optimize a specific FTS index
OPTIMIZE INDEX idx_articles_fts;

-- Optimize all FTS indexes in the database
OPTIMIZE INDEX;

-- All of these automatically update the FTS index
INSERT INTO articles VALUES (5, 'New Article', 'Content here.', 'Dave');
UPDATE articles SET body = 'Updated content.' WHERE id = 5;
DELETE FROM articles WHERE id = 5;