-- Lexical search over video titles.
--
-- The vector is a generated column rather than something the consumer writes:
-- the catalogue's only writer is SaveVideo, and a title that arrives in an
-- announcement should be searchable because it is stored, not because a second
-- statement remembered to index it. STORED because a GIN index needs the value
-- on disk to index it at all.
--
-- 'arabic' is the text search configuration, not 'english' or 'simple': it
-- stems Arabic (الوثائقي -> وثايق) while leaving Latin-script tokens as written,
-- which is the right trade for a mixed catalogue. The cost is that English
-- words are not stemmed, so "documentaries" does not find "documentary".
--
-- The configuration is named explicitly rather than left to default_text_search_config,
-- which is a per-session GUC — an expression that reads it is only STABLE, and
-- a generated column requires IMMUTABLE. Naming it is also what keeps the
-- column and every query in step: a query built with a different
-- configuration would stem its terms differently and silently match nothing.
ALTER TABLE videos
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('arabic', title)) STORED;

-- GIN, not GiST: the catalogue is read far more than it is written, and GIN
-- answers @@ faster at the cost of a slower update — the right way round here.
CREATE INDEX videos_search_vector_idx ON videos USING GIN (search_vector);