-- 046_ccod_search_indexes.sql
-- Make the Land Registry title lookup usable.
--
-- landregistry.ccod carries indexes on company_id, district, postcode and
-- property_address, and NONE on title_number - the column the title autocomplete
-- searches and the column every lookup joins on. Every keystroke was a sequential scan
-- of the whole register (Dave, 2026-08-13: "performance slow, title number may need an
-- index"). The trigram index on proprietor_name_1 that does exist is on
-- ccod_company_summary, a different table, which is easy to mistake for cover here.
--
-- THREE INDEXES, BECAUSE THERE ARE THREE DIFFERENT QUERIES:
--
--   btree (title_number)            - equality. wayleave_landreg_save looks up tenure on
--                                     add, wayleave_landreg_load joins per linked title,
--                                     and resolveAutocompleteLabel resolves a stored
--                                     title back to a label. All are title_number = :v.
--   gin (upper(title_number))       - the autocomplete's leading-wildcard LIKE, which no
--   gin (upper(proprietor_name_1))    btree can serve at all.
--
-- The expressions must match the query TEXTUALLY for the planner to use them, so
-- autocomplete.php is written as upper(title_number) LIKE upper(:search_term). varchar
-- normalises to upper((title_number)::text), which is why the cast appears below and not
-- on proprietor_name_1, which is already text.
--
-- NOT CONCURRENTLY, AND THIS ONE TAKES A WHILE. migrate.php wraps each migration in a
-- transaction and CREATE INDEX CONCURRENTLY cannot run inside one. Plain CREATE INDEX
-- holds a SHARE lock, so writes to ccod block until it finishes - minutes, on a register
-- this size. ccod is a bulk-import table that is not written during the working day, so
-- the window is the import job, not users. Run it when no import is running.

-- ── 1. Guard ────────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('landregistry.ccod') IS NULL THEN
        RAISE EXCEPTION '046: landregistry.ccod not found';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
        RAISE EXCEPTION '046: pg_trgm extension not installed - the two gin indexes need it';
    END IF;
END $$;

-- ── 2. Indexes ──────────────────────────────────────────────────────────────

CREATE INDEX IF NOT EXISTS ccod_title_number_idx
    ON landregistry.ccod USING btree (title_number);

CREATE INDEX IF NOT EXISTS ccod_title_number_trgm_upper_idx
    ON landregistry.ccod USING gin (upper((title_number)::text) public.gin_trgm_ops);

CREATE INDEX IF NOT EXISTS ccod_proprietor_name_1_trgm_upper_idx
    ON landregistry.ccod USING gin (upper(proprietor_name_1) public.gin_trgm_ops);

-- The planner will not choose a new index on stale statistics, and this table has never
-- had column stats gathered for title_number.
ANALYZE landregistry.ccod;

-- ── 3. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_missing text;
BEGIN
    SELECT string_agg(v, ', ') INTO v_missing
      FROM unnest(ARRAY['ccod_title_number_idx',
                        'ccod_title_number_trgm_upper_idx',
                        'ccod_proprietor_name_1_trgm_upper_idx']) AS v
     WHERE NOT EXISTS (SELECT 1 FROM pg_indexes
                        WHERE schemaname = 'landregistry' AND tablename = 'ccod'
                          AND indexname = v);
    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '046: index(es) not created: %', v_missing;
    END IF;
END $$;
