<?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">

  <!--
    dict-relationships-v2 epic, Phase 2: precomputed reverse FK index.

    Materialized view `record_dependents_index` сворачивает все active FK
    relationships (source_record + source_field → target_dict + target_value)
    в один таблично-индексированный snapshot. Даёт O(log n) lookup для
    record-level dependents query — без JSONB предикатов в hot path.

    Включение per request: feature flag `ordinis.lineage.mv.enabled`
    (default false). Migration катится всегда — flag решает, использует
    ли service path MV vs прямой JSONB query (Phase 1 implementation).

    Refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY (требует UNIQUE INDEX).
    Throttled на app-side через `MaterializedViewRefreshService` — default
    1 refresh / 60 sec, чтобы избежать storm при bulk close (R2 eng review).

    Active record check сделан query-side (на valid_from/valid_to колонках),
    чтобы записи закрытые между refresh'ами не появились в результатах
    (stale window — "data may be stale: refreshed N min ago").

    Initial population SLO: <5 min на 5k records (тест plan target). На
    pure JSONB indexing (jsonb_each + ?-marker) — не ожидаем больше.
  -->
  <changeSet id="0016-record-dependents-index-create" author="ordinis">
    <preConditions onFail="MARK_RAN">
      <not>
        <sqlCheck expectedResult="1">
          SELECT COUNT(*) FROM pg_matviews WHERE matviewname = 'record_dependents_index'
        </sqlCheck>
      </not>
    </preConditions>
    <comment>MV record_dependents_index: precomputed reverse FK</comment>
    <sql splitStatements="false"><![CDATA[
      CREATE MATERIALIZED VIEW record_dependents_index AS
      SELECT
        r.id                                  AS source_record_id,
        r.dictionary_id                       AS source_dict_id,
        src_def.name                          AS source_dict_name,
        src_def.display_name                  AS source_display_name,
        src_def.scope                         AS source_scope,
        r.business_key                        AS source_business_key,
        r.data_scope                          AS source_data_scope,
        r.valid_from,
        r.valid_to,
        r.created_at,
        prop.key                              AS source_field,
        split_part(prop.value->>'x-references', '.', 1) AS target_dict_name,
        split_part(prop.value->>'x-references', '.', 2) AS target_field,
        UPPER(COALESCE(prop.value->>'x-references-on-close', 'BLOCK')) AS on_close,
        r.data ->> prop.key                   AS target_business_key_value
      FROM dictionary_records r
      JOIN dictionary_definitions src_def ON src_def.id = r.dictionary_id
      JOIN LATERAL jsonb_each(src_def.schema_json -> 'properties') AS prop(key, value) ON true
      -- Note: избегаем JSONB ?-operator — Liquibase прогоняет SQL через
      -- PreparedStatement, который любой ? интерпретирует как $N
      -- placeholder → "syntax error at $1". Заменяем на эквиваленты:
      --   prop.value ? 'x-references'  →  prop.value -> 'x-references' IS NOT NULL
      --   r.data ? prop.key            →  r.data -> prop.key IS NOT NULL
      -- Регекс с $ anchor по той же причине заменён на LIKE — split_part'ы
      -- ниже сами справятся с malformed (вернут empty strings, никогда не
      -- match-ятся с real records).
      WHERE prop.value -> 'x-references' IS NOT NULL
        AND prop.value->>'x-references' LIKE '%.%'
        AND r.data -> prop.key IS NOT NULL
        AND jsonb_typeof(r.data -> prop.key) = 'string'
      WITH NO DATA;
    ]]></sql>
    <rollback>
      <sql>DROP MATERIALIZED VIEW IF EXISTS record_dependents_index;</sql>
    </rollback>
  </changeSet>

  <!--
    UNIQUE INDEX (source_record_id, source_field) — REQUIRED для REFRESH
    CONCURRENTLY. Composite потому что одна запись может иметь несколько
    FK fields в schema (spacecraft.satTypeCode + spacecraft.operatorId).
  -->
  <changeSet id="0016-record-dependents-index-unique" author="ordinis">
    <comment>UNIQUE INDEX для REFRESH CONCURRENTLY</comment>
    <sql>
      CREATE UNIQUE INDEX uniq_record_dependents_index_source
        ON record_dependents_index (source_record_id, source_field);
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS uniq_record_dependents_index_source;</sql>
    </rollback>
  </changeSet>

  <!--
    Main lookup index: (target_dict_name, target_business_key_value).
    Includes valid_from/valid_to для query-side active filter без
    дополнительного table scan.
  -->
  <changeSet id="0016-record-dependents-index-target-lookup" author="ordinis">
    <comment>Composite index для record-level dependents lookup</comment>
    <sql>
      CREATE INDEX idx_record_dependents_target
        ON record_dependents_index
           (target_dict_name, target_business_key_value, valid_from, valid_to);
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS idx_record_dependents_target;</sql>
    </rollback>
  </changeSet>

  <!--
    Schema-level lookup: (target_dict_name) для "какие словари ссылаются".
    Не нужен composite — только префикс target_dict_name + DISTINCT
    source_dict_name fingerprint берётся в SQL.
  -->
  <changeSet id="0016-record-dependents-index-schema-lookup" author="ordinis">
    <comment>Index для schema-level reverse FK list</comment>
    <sql>
      CREATE INDEX idx_record_dependents_schema
        ON record_dependents_index (target_dict_name);
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS idx_record_dependents_schema;</sql>
    </rollback>
  </changeSet>

  <!--
    Initial population. Не блокирует existing reads/writes (REFRESH без
    CONCURRENTLY на пустой view — fast path). На 5k records ожидание <5 min.
  -->
  <changeSet id="0016-record-dependents-index-populate" author="ordinis">
    <comment>Initial REFRESH (no CONCURRENTLY на первый populate)</comment>
    <sql>REFRESH MATERIALIZED VIEW record_dependents_index;</sql>
    <rollback>
      <!-- No-op: rollback drops view целиком. -->
    </rollback>
  </changeSet>

  <!--
    Refresh metadata table — single-row для tracking last refresh timestamp,
    duration, row count. Используется service для exposure через API
    (stale window UX), и admin endpoint для health check.
  -->
  <changeSet id="0016-record-dependents-refresh-meta" author="ordinis">
    <comment>Metadata табличка для last MV refresh tracking</comment>
    <createTable tableName="lineage_mv_meta">
      <column name="id" type="SMALLINT" defaultValueNumeric="1">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="last_refreshed_at" type="TIMESTAMPTZ"/>
      <column name="last_duration_ms" type="BIGINT"/>
      <column name="last_row_count" type="BIGINT"/>
      <column name="last_status" type="VARCHAR(32)" defaultValue="never"/>
      <column name="last_error" type="TEXT"/>
    </createTable>
    <sql>
      ALTER TABLE lineage_mv_meta
      ADD CONSTRAINT lineage_mv_meta_singleton CHECK (id = 1);
    </sql>
    <insert tableName="lineage_mv_meta">
      <column name="id" valueNumeric="1"/>
      <column name="last_status" value="never"/>
    </insert>
  </changeSet>

</databaseChangeLog>
