<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.27.xsd">

  <!--
    CEO plan v1: smart JSONB search across all dictionaries.

    pg_trgm extension + GIN expression index на data::text для ILIKE с
    trigram match. Запросы вида `data::text ILIKE '%spacecraft%'` идут по
    индексу когда query >= 3 chars (trigram threshold).

    Alternative considered: jsonb_to_tsvector full-text. Rejected because:
    - 'russian' dictionary stem'ит коды (SAR-X → "sar"), теряет точность.
    - 'simple' tsvector работает но требует to_tsquery('q & q2 & q3'),
      слишком сложно для admin search bar (admins пишут free-form).
    - Trigram natively supports ILIKE/contains semantics, что и нужно.

    Index size: ~30-40% от data column на 5k records (~3-5 MB). Acceptable.
  -->
  <changeSet id="0018-pg-trgm-extension" author="ordinis">
    <preConditions onFail="MARK_RAN">
      <not>
        <sqlCheck expectedResult="t">
          SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm')
        </sqlCheck>
      </not>
    </preConditions>
    <comment>Enable pg_trgm для trigram-based ILIKE search</comment>
    <sql>CREATE EXTENSION IF NOT EXISTS pg_trgm;</sql>
    <rollback>
      <!-- Не дропаем extension в rollback'е — может быть использовано other tables. -->
      <sql>SELECT 1;</sql>
    </rollback>
  </changeSet>

  <changeSet id="0018-trgm-search-index" author="ordinis">
    <comment>GIN trigram index на dictionary_records.data::text для smart search</comment>
    <sql>
      CREATE INDEX idx_dict_records_data_trgm
        ON dictionary_records
        USING GIN ((data::text) gin_trgm_ops);
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS idx_dict_records_data_trgm;</sql>
    </rollback>
  </changeSet>

</databaseChangeLog>
