--Full-text search: tsvector_ops, tsquery_ops
--PostgreSQL core provides GiST support for text search via tsvector and tsquery. GiST is valid here, though GIN is often more common for pure FTS workloads.

--5.1 tsvector_ops — document search
DROP TABLE IF EXISTS legal_docs;
CREATE TABLE legal_docs
(
    doc_id        bigserial PRIMARY KEY,
    title         text NOT NULL,
    body          text NOT NULL,
    search_vector tsvector
);

INSERT INTO legal_docs (title, body, search_vector)
SELECT
    'Contract ' || g,
    'This agreement contains clauses about loan interest collateral risk and repayment schedule number ' || g,
    to_tsvector(
        'english',
        'Contract ' || g || ' This agreement contains clauses about loan interest collateral risk and repayment schedule number ' || g
    )
FROM generate_series(1, 150000) g;

CREATE INDEX ix_legal_docs_gist
    ON legal_docs
 USING gist (search_vector);
--Search
SELECT doc_id, title
FROM legal_docs
WHERE search_vector @@ plainto_tsquery('english', 'loan collateral risk');
---------------------------------------
---------------------------------------

--tsquery_ops — querying stored query rules
--This is more niche: store tsquery expressions and find which rules match a new search expression. PostgreSQL includes tsquery_ops in GiST built-ins.

DROP TABLE IF EXISTS alert_rules;
CREATE TABLE alert_rules
(
    rule_id      bigserial PRIMARY KEY,
    rule_name    text NOT NULL,
    rule_query   tsquery NOT NULL
);

INSERT INTO alert_rules (rule_name, rule_query)
VALUES
('Loan Risk', to_tsquery('english', 'loan & risk')),
('Collateral Alert', to_tsquery('english', 'collateral & (default | overdue)')),
('AML Pattern', to_tsquery('english', 'money & laundering'));

CREATE INDEX ix_alert_rules_gist
    ON alert_rules
 USING gist (rule_query);
--Stored query containment / relation checks
SELECT rule_id, rule_name
FROM alert_rules
WHERE rule_query @> to_tsquery('english', 'loan & risk');