-- Add tsvector column for performance
ALTER TABLE products ADD COLUMN search_vec TSVECTOR;

UPDATE products SET search_vec =
  TO_TSVECTOR('english', COALESCE(name,'') || ' ' || COALESCE(description,''));

CREATE INDEX idx_product_fts ON products USING GIN(search_vec);

-- Create trigger to keep it updated
CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION
    TSVECTOR_UPDATE_TRIGGER(search_vec, 'pg_catalog.english', name, description);

-- Full text search with ranking and highlights
SELECT
  name,
  TS_RANK(search_vec, query) AS relevance,
  TS_HEADLINE('english', name, query, 'StartSel=<b>, StopSel=</b>') AS highlighted
FROM products, TO_TSQUERY('english', 'wireless & bluetooth | earphone') query
WHERE search_vec @@ query
ORDER BY relevance DESC;