-- pg_tre 4.5.1 -- native index AM for approximate regex matching.
--
-- Phase 0 scope: registers the `tre` access method, the legacy UDFs
-- inherited from 0.1.0, and the handler function.  The opclass is
-- declared so CREATE INDEX ... USING tre succeeds syntactically; scan
-- and insert paths are wired in by later phases and will raise a
-- clear "not yet implemented" error until then.
--
-- Do not run ALTER EXTENSION ... UPDATE on a production index; the
-- on-disk format is stabilized at 1.0.0.

-- ---------------------------------------------------------------------
-- Legacy UDFs (unchanged from 0.1.0)
-- ---------------------------------------------------------------------

CREATE FUNCTION tre_amatch(text, text, int4)
    RETURNS bool
    AS 'MODULE_PATHNAME', 'pg_tre_amatch'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_amatch_cost(text, text, int4)
    RETURNS int4
    AS 'MODULE_PATHNAME', 'pg_tre_amatch_cost'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_amatch(text, text, int4, int4, int4, int4)
    RETURNS bool
    AS 'MODULE_PATHNAME', 'pg_tre_amatch_with_costs'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_amatch_detail(text, text, int4)
    RETURNS TABLE(cost int4, num_ins int4, num_del int4, num_subst int4,
                  match_start int4, match_end int4)
    AS 'MODULE_PATHNAME', 'pg_tre_amatch_detail'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE
    ROWS 1;

CREATE FUNCTION tre_version()
    RETURNS text
    AS 'MODULE_PATHNAME', 'pg_tre_version'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

-- ---------------------------------------------------------------------
-- Debug UDFs (Phase 3)
-- ---------------------------------------------------------------------

CREATE FUNCTION tre_parse_debug(text)
    RETURNS text
    AS 'MODULE_PATHNAME', 'tre_parse_debug'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_extract_debug(text)
    RETURNS text
    AS 'MODULE_PATHNAME', 'tre_extract_debug'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_extract_debug(text, int)
    RETURNS text
    AS 'MODULE_PATHNAME', 'tre_extract_debug_k'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

-- ---------------------------------------------------------------------
-- Access method registration
-- ---------------------------------------------------------------------

CREATE FUNCTION tre_handler(internal)
    RETURNS index_am_handler
    AS 'MODULE_PATHNAME', 'tre_handler'
    LANGUAGE C;

CREATE ACCESS METHOD tre
    TYPE INDEX
    HANDLER tre_handler;

COMMENT ON ACCESS METHOD tre IS
    'approximate-regex index (pg_tre)';

-- ---------------------------------------------------------------------
-- tre_pattern type
-- ---------------------------------------------------------------------

CREATE TYPE tre_pattern;

CREATE FUNCTION tre_pattern_in(cstring)
    RETURNS tre_pattern
    AS 'MODULE_PATHNAME', 'tre_pattern_in'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern_out(tre_pattern)
    RETURNS cstring
    AS 'MODULE_PATHNAME', 'tre_pattern_out'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern_recv(internal)
    RETURNS tre_pattern
    AS 'MODULE_PATHNAME', 'tre_pattern_recv'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern_send(tre_pattern)
    RETURNS bytea
    AS 'MODULE_PATHNAME', 'tre_pattern_send'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE TYPE tre_pattern (
    INPUT = tre_pattern_in,
    OUTPUT = tre_pattern_out,
    RECEIVE = tre_pattern_recv,
    SEND = tre_pattern_send,
    STORAGE = extended
);

-- Constructor functions

CREATE FUNCTION tre_pattern(text)
    RETURNS tre_pattern
    AS 'MODULE_PATHNAME', 'tre_pattern_make'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern(text, int4)
    RETURNS tre_pattern
    AS 'MODULE_PATHNAME', 'tre_pattern_make_k'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern(text, int4, int4, int4, int4)
    RETURNS tre_pattern
    AS 'MODULE_PATHNAME', 'tre_pattern_make_full'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

-- ---------------------------------------------------------------------
-- Operators
-- ---------------------------------------------------------------------

CREATE FUNCTION tre_match_scalar(text, tre_pattern)
    RETURNS bool
    AS 'MODULE_PATHNAME', 'tre_match_scalar'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_pattern_sel(internal, oid, internal, int4)
    RETURNS float8
    AS 'MODULE_PATHNAME', 'tre_pattern_sel'
    LANGUAGE C STRICT;

CREATE OPERATOR %~~ (
    LEFTARG = text,
    RIGHTARG = tre_pattern,
    FUNCTION = tre_match_scalar,
    RESTRICT = tre_pattern_sel
);

COMMENT ON OPERATOR %~~ (text, tre_pattern) IS
    'approximate regex match (pg_tre)';

-- ---------------------------------------------------------------------
-- Opclass for text columns.
--
-- Strategy 1: text %~~ tre_pattern  (approximate regex match)
-- ---------------------------------------------------------------------

DROP OPERATOR CLASS IF EXISTS tre_text_ops USING tre CASCADE;

CREATE OPERATOR CLASS tre_text_ops
    DEFAULT FOR TYPE text USING tre AS
    OPERATOR 1 %~~ (text, tre_pattern),
    STORAGE text;

-- ---------------------------------------------------------------
-- 1.3.0 additions: similarity / distance ranking helpers.
-- ---------------------------------------------------------------

CREATE FUNCTION tre_similarity(text, text, int4)
    RETURNS float8
    AS 'MODULE_PATHNAME', 'pg_tre_similarity'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_similarity(text, text, int4) IS
    'Approximate-match similarity score in [0.0, 1.0]. '
    'Returns 1 - cost/max(len(input), len(pattern)) if the input '
    'matches the pattern within max_cost edits, else 0.0.';

CREATE FUNCTION tre_similarity(text, tre_pattern)
    RETURNS float8
    AS 'MODULE_PATHNAME', 'pg_tre_similarity_pattern'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_similarity(text, tre_pattern) IS
    'Approximate-match similarity against a strongly-typed pattern. '
    'See tre_similarity(text, text, int4) for semantics.';

CREATE FUNCTION tre_distance(text, text, int4)
    RETURNS int4
    AS 'MODULE_PATHNAME', 'pg_tre_distance'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_distance(text, text, int4) IS
    'Approximate-match edit distance (insertions + deletions + '
    'substitutions, each weighted 1).  NULL if the input does not '
    'match the pattern within max_cost edits.';

CREATE FUNCTION tre_distance(text, tre_pattern)
    RETURNS int4
    AS 'MODULE_PATHNAME', 'pg_tre_distance_pattern'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_distance(text, tre_pattern) IS
    'Approximate-match edit distance against a strongly-typed pattern. '
    'See tre_distance(text, text, int4) for semantics.';

CREATE OPERATOR <@> (
    LEFTARG    = text,
    RIGHTARG   = tre_pattern,
    PROCEDURE  = tre_distance
    -- No commutator: tre_pattern is non-commutative (left side
    -- is the input, right side is the pattern).
);

COMMENT ON OPERATOR <@> (text, tre_pattern) IS
    'Approximate-match edit distance.  Returns the integer cost '
    'of the best alignment of input against pattern, or NULL if '
    'no match exists within the pattern''s max_cost.  Use ORDER '
    'BY (text <@> pattern) ASC LIMIT N to rank candidates by '
    'similarity.';

-- ---------------------------------------------------------------
-- 1.5.0 addition: ORDER BY <@> support in the index AM.
--
-- Strategy 2 = ordering operator returning int4 (edit distance).
-- The integer_ops opfamily is the btree int4 ordering family;
-- ASC NULLS LAST yields closest matches first.
-- Combined with amcanorderbyop=true in the AM handler, this lets
--   ORDER BY body <@> tre_pattern('foo', 2) ASC LIMIT N
-- be answered directly by the index without an executor Sort.
-- ---------------------------------------------------------------

ALTER OPERATOR FAMILY tre_text_ops USING tre ADD
    OPERATOR 2 <@> (text, tre_pattern) FOR ORDER BY integer_ops;

-- ---------------------------------------------------------------
-- 3.0.2 (Phase A / A1): LIKE / ILIKE / ~ / ~* / = acceleration.
-- Bind the built-in text pattern operators so the planner uses a
-- pg_tre index for col LIKE '%foo%', col ~ 'fo+o', col = 'foo'.
-- Each lowers to the trigram engine at k=0; the executor rechecks
-- with the built-in operator (lossy candidate filter).
--   3 = ~~ (LIKE)  4 = ~~* (ILIKE)  5 = ~ (regex)
--   6 = ~* (iregex)  7 = = (equality)
-- ---------------------------------------------------------------
ALTER OPERATOR FAMILY tre_text_ops USING tre ADD
    OPERATOR 3 ~~  (text, text),
    OPERATOR 4 ~~* (text, text),
    OPERATOR 5 ~   (text, text),
    OPERATOR 6 ~*  (text, text),
    OPERATOR 7 =   (text, text);

-- ---------------------------------------------------------------
-- 1.5.0 addition: in-place format-upgrade infrastructure.
--
-- Per-page format_version is tracked in the opaque trailer of each
-- pg_tre page; the meta page tracks the minimum across all pages.
-- Future on-disk format bumps can be applied lazily without REINDEX:
--
--    SELECT pg_tre_upgrade_index('my_idx');
--
-- See doc/onpage_format.md.
-- ---------------------------------------------------------------

CREATE FUNCTION pg_tre_upgrade_index(regclass)
    RETURNS void
    AS 'MODULE_PATHNAME', 'pg_tre_upgrade_index'
    LANGUAGE C STRICT VOLATILE PARALLEL UNSAFE;

COMMENT ON FUNCTION pg_tre_upgrade_index(regclass) IS
    'Walk every block of a pg_tre index, rewriting any page below the '
    'current on-disk format version to the latest format in place.  '
    'Per-page exclusive lock is held only for the duration of each '
    'rewrite; readers and writers proceed concurrently.  WAL-logged '
    'as XLOG_PTRE_PAGE_FORMAT_UPGRADE (full-page image, one record '
    'per rewritten page).  Updates the meta page''s '
    'min_page_format_version after a complete sweep.';

CREATE FUNCTION pg_tre_index_format_status(regclass)
    RETURNS TABLE(format_version int4, page_count bigint)
    AS 'MODULE_PATHNAME', 'pg_tre_index_format_status'
    LANGUAGE C STRICT STABLE PARALLEL SAFE
    ROWS 4;

COMMENT ON FUNCTION pg_tre_index_format_status(regclass) IS
    'Return one row per distinct per-page format_version observed in '
    'the index, with a count of pages at that version.  Walks all '
    'blocks under shared locks; safe to run concurrently with other '
    'workloads.';

CREATE FUNCTION pg_tre_index_min_format_version(regclass)
    RETURNS int4
    AS 'MODULE_PATHNAME', 'pg_tre_index_min_format_version'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

COMMENT ON FUNCTION pg_tre_index_min_format_version(regclass) IS
    'Return the meta page''s min_page_format_version field, i.e. the '
    'minimum per-page format_version known to exist in the index.  '
    'Cheap O(1); for the authoritative full-walk view use '
    'pg_tre_index_format_status().';

CREATE FUNCTION pg_tre_index_tid_packing(regclass)
    RETURNS text
    AS 'MODULE_PATHNAME', 'pg_tre_index_tid_packing'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

COMMENT ON FUNCTION pg_tre_index_tid_packing(regclass) IS
    'How the index packs heap TIDs: ''dense'' (format v11: built on a '
    'heap table by pg_tre 4.4 or later) or ''legacy'' (built by 4.3 or '
    'earlier, or on another table access method).  A legacy index works '
    'unchanged; REINDEX rebuilds it dense, 1.5-1.8x smaller on '
    'long-text columns.';

CREATE FUNCTION pg_tre_index_pending(regclass)
    RETURNS bigint
    AS 'MODULE_PATHNAME', 'pg_tre_index_pending'
    LANGUAGE C STRICT VOLATILE PARALLEL SAFE;

COMMENT ON FUNCTION pg_tre_index_pending(regclass) IS
    'Entries (24 bytes each) in the index''s pending list, which every '
    'scan reads in full.  VACUUM merges the list, as does an INSERT that '
    'pushes it past pending_list_limit.';

-- 4.5.0: pg_tre_index_check -- does the index return what a sequential
-- scan returns?  For patterns cut from the indexed column's own values it
-- counts matching rows by sequential scan, by bitmap index scan and by
-- plain index scan of THIS index, for each operator the opclass serves.
--
-- search_path is pinned (pg_catalog, then pg_temp) and every function,
-- operator and type is schema-qualified, so objects someone else creates
-- in a schema on the caller's search_path cannot take over the calls --
-- operators run this as superuser after upgrades.  Dynamic SQL names the
-- table by its regclass (schema-qualified under this search_path) and
-- pg_tre's own objects by @extschema@.
CREATE FUNCTION pg_tre_index_check(idx regclass, samples int DEFAULT 20)
    RETURNS TABLE(op text, pattern text, seq_rows bigint,
                  bitmap_rows bigint, indexscan_rows bigint, ok boolean)
    LANGUAGE plpgsql VOLATILE PARALLEL UNSAFE
    SET search_path = pg_catalog, pg_temp
    SET enable_seqscan = on
    SET enable_indexscan = on
    SET enable_bitmapscan = on
AS $fn$
DECLARE
    am       name;
    tbl      regclass;
    natts    int;
    attnum   int;
    col      text;
    pred     text;
    idxname  text;
    reltup   float8;
    pct      float8;
    v        text;
    lit      text;
    pre      text;
    re_lit   text;
    re_pre   text;
    like_lit text;
    q        text;
    plan     text;
    used     boolean;
    n_vals   int := 0;
    n_checks int := 0;
    n_bad    int := 0;
    n_unused int := 0;
    fmts     text;
    meta     text;
    ext      text := pg_catalog.quote_ident('@extschema@');
BEGIN
    SELECT a.amname, i.indrelid, i.indnatts, i.indkey[0],
           COALESCE(pg_catalog.pg_get_expr(i.indpred, i.indrelid), 'true'),
           c.relname
      INTO am, tbl, natts, attnum, pred, idxname
      FROM pg_catalog.pg_index i
      JOIN pg_catalog.pg_class c ON c.oid OPERATOR(pg_catalog.=) i.indexrelid
      JOIN pg_catalog.pg_am a ON a.oid OPERATOR(pg_catalog.=) c.relam
     WHERE i.indexrelid OPERATOR(pg_catalog.=) idx;
    IF am IS NULL OR am::text OPERATOR(pg_catalog.<>) 'tre' THEN
        RAISE EXCEPTION '"%" is not a tre index', idx;
    END IF;
    IF natts OPERATOR(pg_catalog.<>) 1 OR attnum OPERATOR(pg_catalog.=) 0 THEN
        RAISE EXCEPTION 'pg_tre_index_check supports indexes on a single plain column; "%" is on an expression', idx;
    END IF;
    IF samples OPERATOR(pg_catalog.<) 1 THEN
        RAISE EXCEPTION 'samples must be at least 1';
    END IF;

    -- The column, collated as the index is, so the planner can use it.
    SELECT CASE WHEN ic.indcollation[0] OPERATOR(pg_catalog.<>) 0::oid
                 AND ic.indcollation[0] OPERATOR(pg_catalog.<>) at.attcollation
                THEN pg_catalog.format('(%I COLLATE %I.%I)', at.attname, n.nspname, co.collname)
                ELSE pg_catalog.quote_ident(at.attname) END
      INTO col
      FROM pg_catalog.pg_index ic
      JOIN pg_catalog.pg_attribute at
        ON at.attrelid OPERATOR(pg_catalog.=) ic.indrelid
       AND at.attnum OPERATOR(pg_catalog.=) ic.indkey[0]
      LEFT JOIN pg_catalog.pg_collation co ON co.oid OPERATOR(pg_catalog.=) ic.indcollation[0]
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid OPERATOR(pg_catalog.=) co.collnamespace
     WHERE ic.indexrelid OPERATOR(pg_catalog.=) idx;

    -- About 4x the wanted rows from a Bernoulli sample (all rows when small).
    SELECT c.reltuples INTO reltup FROM pg_catalog.pg_class c
     WHERE c.oid OPERATOR(pg_catalog.=) tbl;
    pct := CASE WHEN reltup OPERATOR(pg_catalog.<=) 0 THEN 100
                ELSE LEAST(100, GREATEST(0.001,
                         samples OPERATOR(pg_catalog.*) 400.0 OPERATOR(pg_catalog./) reltup)) END;

    FOR v IN EXECUTE pg_catalog.format(
        'SELECT %s::pg_catalog.text FROM %s TABLESAMPLE BERNOULLI (%s)'
        ' WHERE %s IS NOT NULL AND pg_catalog.length(%s::pg_catalog.text) OPERATOR(pg_catalog.>=) 3'
        ' AND (%s) LIMIT %s',
        col, tbl, pct, col, col, pred, samples)
    LOOP
        n_vals := n_vals OPERATOR(pg_catalog.+) 1;
        lit := pg_catalog.substr(v,
                   1 OPERATOR(pg_catalog.+) pg_catalog.floor(pg_catalog.random()
                       OPERATOR(pg_catalog.*) GREATEST(pg_catalog.length(v) OPERATOR(pg_catalog.-) 2, 1))::int,
                   3 OPERATOR(pg_catalog.+) pg_catalog.floor(pg_catalog.random() OPERATOR(pg_catalog.*) 6)::int);
        IF pg_catalog.length(lit) OPERATOR(pg_catalog.<) 3 THEN
            lit := pg_catalog.substr(v, 1, 3);
        END IF;
        pre := pg_catalog.substr(v, 1,
                   3 OPERATOR(pg_catalog.+) pg_catalog.floor(pg_catalog.random() OPERATOR(pg_catalog.*) 4)::int);
        re_lit := pg_catalog.regexp_replace(lit, '([.^$*+?(){}|\\\[\]-])', '\\\1', 'g');
        re_pre := pg_catalog.regexp_replace(pre, '([.^$*+?(){}|\\\[\]-])', '\\\1', 'g');
        like_lit := pg_catalog.replace(pg_catalog.replace(pg_catalog.replace(
                        lit, '\', '\\'), '%', '\%'), '_', '\_');

        FOR op, pattern, q IN
            SELECT c.o, c.p, c.w FROM (VALUES
                ('%~~ k=0', re_lit,
                 pg_catalog.format('%s OPERATOR(%s.%%~~) %s.tre_pattern(%L, 0)', col, ext, ext, re_lit)),
                ('%~~ k=1', re_lit,
                 CASE WHEN pg_catalog.length(lit) OPERATOR(pg_catalog.>=) 5
                      THEN pg_catalog.format('%s OPERATOR(%s.%%~~) %s.tre_pattern(%L, 1)', col, ext, ext, re_lit) END),
                ('~',     re_lit, pg_catalog.format('%s OPERATOR(pg_catalog.~) %L', col, re_lit)),
                ('~*',    pg_catalog.upper(re_lit),
                 pg_catalog.format('%s OPERATOR(pg_catalog.~*) %L', col, pg_catalog.upper(re_lit))),
                ('~ ^',   '^' OPERATOR(pg_catalog.||) re_pre,
                 pg_catalog.format('%s OPERATOR(pg_catalog.~) %L', col, '^' OPERATOR(pg_catalog.||) re_pre)),
                ('~* ^',  '^' OPERATOR(pg_catalog.||) pg_catalog.upper(re_pre),
                 pg_catalog.format('%s OPERATOR(pg_catalog.~*) %L', col,
                                   '^' OPERATOR(pg_catalog.||) pg_catalog.upper(re_pre))),
                ('LIKE',  pg_catalog.concat('%', like_lit, '%'),
                 pg_catalog.format('%s OPERATOR(pg_catalog.~~) %L', col, pg_catalog.concat('%', like_lit, '%'))),
                ('ILIKE', pg_catalog.concat('%', pg_catalog.upper(like_lit), '%'),
                 pg_catalog.format('%s OPERATOR(pg_catalog.~~*) %L', col,
                                   pg_catalog.concat('%', pg_catalog.upper(like_lit), '%')))) c(o, p, w)
             WHERE c.w IS NOT NULL
        LOOP
            n_checks := n_checks OPERATOR(pg_catalog.+) 1;
            q := pg_catalog.format('SELECT pg_catalog.count(*) FROM %s WHERE %s AND (%s)', tbl, q, pred);

            PERFORM pg_catalog.set_config('enable_seqscan', 'on', true);
            PERFORM pg_catalog.set_config('enable_indexscan', 'off', true);
            PERFORM pg_catalog.set_config('enable_bitmapscan', 'off', true);
            EXECUTE q INTO seq_rows;

            -- bitmap, then plain index scan; NULL when the plan does not use idx
            PERFORM pg_catalog.set_config('enable_seqscan', 'off', true);
            PERFORM pg_catalog.set_config('enable_bitmapscan', 'on', true);
            used := false;
            FOR plan IN EXECUTE pg_catalog.concat('EXPLAIN (COSTS OFF) ', q) LOOP
                used := used OR pg_catalog.strpos(plan,
                            pg_catalog.concat('Bitmap Index Scan on ', idxname)) OPERATOR(pg_catalog.>) 0;
            END LOOP;
            bitmap_rows := NULL;
            IF used THEN
                EXECUTE q INTO bitmap_rows;
            END IF;

            PERFORM pg_catalog.set_config('enable_bitmapscan', 'off', true);
            PERFORM pg_catalog.set_config('enable_indexscan', 'on', true);
            used := false;
            FOR plan IN EXECUTE pg_catalog.concat('EXPLAIN (COSTS OFF) ', q) LOOP
                used := used OR pg_catalog.strpos(plan,
                            pg_catalog.concat('Index Scan using ', idxname, ' ')) OPERATOR(pg_catalog.>) 0;
            END LOOP;
            indexscan_rows := NULL;
            IF used THEN
                EXECUTE q INTO indexscan_rows;
            END IF;

            IF bitmap_rows IS NULL AND indexscan_rows IS NULL THEN
                ok := NULL;
                n_unused := n_unused OPERATOR(pg_catalog.+) 1;
            ELSE
                ok := COALESCE(bitmap_rows OPERATOR(pg_catalog.=) seq_rows, true)
                      AND COALESCE(indexscan_rows OPERATOR(pg_catalog.=) seq_rows, true);
                IF NOT ok THEN
                    n_bad := n_bad OPERATOR(pg_catalog.+) 1;
                END IF;
            END IF;
            RETURN NEXT;
        END LOOP;
    END LOOP;

    EXECUTE pg_catalog.format(
        'SELECT pg_catalog.string_agg(pg_catalog.concat(''v'', format_version, '': '', page_count, '' pages''), '', '' ORDER BY format_version)'
        ' FROM %s.pg_tre_index_format_status($1)', ext)
        INTO fmts USING idx;
    EXECUTE pg_catalog.format(
        'SELECT pg_catalog.format(''TID packing %%s; %%s pending entries'', %s.pg_tre_index_tid_packing($1), %s.pg_tre_index_pending($1))',
        ext, ext)
        INTO meta USING idx;
    fmts := pg_catalog.concat('Page formats ', fmts, '; ', meta, '.',
                   CASE WHEN pg_catalog.strpos(fmts, ',') OPERATOR(pg_catalog.>) 0
                        THEN '  Mixed page formats are readable; REINDEX rewrites them all at the latest.'
                        ELSE '' END);
    IF n_bad OPERATOR(pg_catalog.>) 0 THEN
        RAISE WARNING 'pg_tre_index_check("%"): % of % checks disagree with a sequential scan',
            idx, n_bad, n_checks
            USING DETAIL = fmts,
                  HINT = 'The index returns different rows than a sequential scan: REINDEX INDEX CONCURRENTLY it, and please report the failing patterns.';
    ELSE
        RAISE NOTICE 'pg_tre_index_check("%"): % checks from % sampled values agree with a sequential scan (% could not use the index)',
            idx, n_checks, n_vals, n_unused
            USING DETAIL = fmts;
    END IF;
END
$fn$;

COMMENT ON FUNCTION pg_tre_index_check(regclass, int) IS
    'Compare the index with a sequential scan: for patterns cut from '
    '`samples` of the column''s own values, count matching rows by '
    'sequential scan, bitmap index scan and plain index scan for %~~ (k=0 '
    'and 1), ~, ~*, anchored ~ / ~*, LIKE and ILIKE.  One row per check; '
    'ok is false where the index disagrees, NULL where the planner did '
    'not use the index.  Costs about 8 sequential scans per sample.';

CREATE FUNCTION tre_run_catalog_status(regclass)
    RETURNS TABLE(run_id bigint, level int4, n_tuples bigint,
                  n_trigrams bigint, min_hash numeric, max_hash numeric)
    AS 'MODULE_PATHNAME', 'tre_run_catalog_status'
    LANGUAGE C STRICT STABLE PARALLEL SAFE
    ROWS 8;

COMMENT ON FUNCTION tre_run_catalog_status(regclass) IS
    'Introspection over the Phase B1 run/level catalog (format v7): '
    'one row per live run, newest run_id first.  A v6 or default-v7 '
    'index reports a single implicit run rooted at the index roots.';

-- ---------------------------------------------------------------
-- 3.0.2: pg_trgm-compatible trigram-set similarity (Phase A / A2).
-- Cheap, stateless Jaccard similarity over pg_trgm's trigram model;
-- distinct from edit-distance tre_similarity / <@>.
-- ---------------------------------------------------------------

CREATE FUNCTION tre_trgm_similarity(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_trgm_similarity'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_trgm_distance(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_trgm_distance'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_trgm_sim_op(text, text)
    RETURNS bool AS 'MODULE_PATHNAME', 'pg_tre_trgm_sim_op'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

-- NOTE: pg_tre does NOT create the bare (text,text) similarity operators
-- %, <->, <%, <<->, <<%, <<<->.  Those symbols are owned by pg_trgm, and
-- operators are global by (symbol, argtypes), so creating them here would
-- make pg_tre and pg_trgm mutually exclusive in one database.  Call the
-- distinctly-named functions instead (tre_trgm_similarity(a,b),
-- tre_word_similarity(a,b), ...), or load pg_trgm for its % / <-> / etc.
-- pg_tre's own index operators (%~~, <@>) do not collide.

-- ---------------------------------------------------------------
-- 3.0.2 (Phase A / A2 remainder): word_similarity operators.
-- pg_trgm-compatible asymmetric word-boundary similarity:
--   word_similarity(a,b)        = best Jaccard of a vs any extent of b
--   strict_word_similarity(a,b) = same, extents pinned to word bounds
-- Operators mirror pg_trgm:
--   a <%  b  = word_similarity(a,b)        >= threshold
--   a <<-> b = 1 - word_similarity(a,b)    (distance)
--   a <<%  b = strict_word_similarity(a,b) >= threshold
--   a <<<-> b = 1 - strict_word_similarity(a,b)
-- ---------------------------------------------------------------
CREATE FUNCTION tre_word_similarity(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_word_similarity'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_strict_word_similarity(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_strict_word_similarity'
    LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION tre_word_sim_op(text, text)
    RETURNS bool AS 'MODULE_PATHNAME', 'pg_tre_word_sim_op'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

CREATE FUNCTION tre_word_dist_op(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_word_dist_op'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

CREATE FUNCTION tre_strict_word_sim_op(text, text)
    RETURNS bool AS 'MODULE_PATHNAME', 'pg_tre_strict_word_sim_op'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

CREATE FUNCTION tre_strict_word_dist_op(text, text)
    RETURNS float4 AS 'MODULE_PATHNAME', 'pg_tre_strict_word_dist_op'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

-- (see note above) the bare <% / <<-> / <<% / <<<-> operators are NOT
-- created -- they collide with pg_trgm.  Use tre_word_similarity(a,b) /
-- tre_strict_word_similarity(a,b) and the *_dist functions instead.


-- Build sizing precheck (customer ask: estimate temp/final size before
-- committing to a large index build).  Samples up to 2000 rows of the
-- target text column and extrapolates.
CREATE FUNCTION tre_estimate_index_build(regclass, int DEFAULT 1)
    RETURNS TABLE(sample_rows bigint, est_rows bigint,
                  est_trigrams bigint, est_temp_mb bigint,
                  est_index_mb bigint)
    AS 'MODULE_PATHNAME', 'tre_estimate_index_build'
    LANGUAGE C STABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_estimate_index_build(regclass, int) IS
    'Estimate the temp-disk and final-index size of a TRE index build '
    'on column `attno` of the table, by sampling rows.  Run before '
    'CREATE INDEX on a large column to size build_max_entries_mb and '
    'the temp tablespace up front.  est_temp_mb uses the same '
    'per-tuple cost as the build_max_entries_mb ceiling.';

-- Phase B1.3 test helper (not supported API): register the index's
-- current roots as an additional run with a trigram-hash range,
-- exercising the crash-safe catalog writer + multi-run scan.  Used by
-- tap/crash_recovery.pl to prove catalog appends survive kill -9.
CREATE FUNCTION tre_debug_append_run(regclass, numeric, numeric)
    RETURNS bigint
    AS 'MODULE_PATHNAME', 'tre_debug_append_run'
    LANGUAGE C STRICT VOLATILE;

COMMENT ON FUNCTION tre_debug_append_run(regclass, numeric, numeric) IS
    'TEST-ONLY (Phase B1.3): append a run sharing the index roots with '
    'the given [min,max] trigram-hash range; exercises the crash-safe '
    'catalog writer and multi-run scan.  Not part of the supported API.';

CREATE FUNCTION tre_coalesced_page_count(regclass)
    RETURNS bigint
    AS 'MODULE_PATHNAME', 'tre_coalesced_page_count'
    LANGUAGE C STRICT STABLE PARALLEL SAFE;

COMMENT ON FUNCTION tre_coalesced_page_count(regclass) IS
    'Count PG_TRE_PAGE_POSTING_COALESCED pages (format v8) in the index. '
    'Zero unless pg_tre.coalesce_enable was on at build time.';

CREATE FUNCTION tre_page_kind_histogram(regclass)
    RETURNS TABLE(page_kind text, n_pages bigint,
                  used_bytes bigint, used_pct numeric)
    AS 'MODULE_PATHNAME', 'tre_page_kind_histogram'
    LANGUAGE C STRICT STABLE PARALLEL SAFE
    ROWS 12;

COMMENT ON FUNCTION tre_page_kind_histogram(regclass) IS
    'Per-page-kind tally (count, used bytes, fill %%) over every block of '
    'the index.  Read-only diagnostic for localizing where an index''s '
    'size lives -- which page kind dominates and whether those pages are '
    'full or mostly empty.';

-- ---------------------------------------------------------------
-- 3.2.0: SuRF range-filter tier (format v10) introspection.
-- ---------------------------------------------------------------
CREATE FUNCTION tre_surf_stats(regclass)
    RETURNS TABLE(n_keys bigint, n_nodes bigint,
                  n_pages int4, image_bytes bigint)
    AS 'MODULE_PATHNAME', 'tre_surf_stats'
    LANGUAGE C STRICT STABLE PARALLEL SAFE
    ROWS 1;

COMMENT ON FUNCTION tre_surf_stats(regclass) IS
    'SuRF range-filter (format v10) stats: distinct trigram-key count, '
    'trie node count, on-disk page count, and serialized image size.  '
    'All zero when the index has no SuRF (built before v10, empty, or '
    'upgraded in place without REINDEX).';