-- Slice 1 + Slice 2 generic search support for DOC documents.
-- Adds lexical-search support columns/indexes and pg_trgm extension.

CREATE EXTENSION IF NOT EXISTS pg_trgm with schema doc;

ALTER TABLE DOC.doc_text_representation
    ADD COLUMN IF NOT EXISTS search_config VARCHAR(64);

ALTER TABLE DOC.doc_text_representation
    ADD COLUMN IF NOT EXISTS search_vector tsvector;

CREATE INDEX IF NOT EXISTS idx_doc_text_repr_search_vector
    ON DOC.doc_text_representation
    USING GIN (search_vector);

CREATE INDEX IF NOT EXISTS idx_doc_document_title_trgm
    ON DOC.doc_document
    USING GIN (title DOC.gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_doc_document_summary_trgm
    ON DOC.doc_document
    USING GIN (summary DOC.gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_doc_text_repr_text_trgm
    ON DOC.doc_text_representation
    USING GIN (text_body DOC.gin_trgm_ops);
