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

  <changeSet id="0003-1-dictionary-records" author="ordinis">
    <comment>Bitemporal dictionary records: tx-time через Envers, effective-time через valid_from/valid_to.</comment>
    <createTable tableName="dictionary_records">
      <column name="id" type="UUID" defaultValueComputed="gen_random_uuid()">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="dictionary_id" type="UUID">
        <constraints nullable="false" foreignKeyName="fk_record_dictionary" references="dictionary_definitions(id)"/>
      </column>
      <column name="business_key" type="VARCHAR(256)">
        <constraints nullable="false"/>
      </column>
      <column name="data" type="JSONB">
        <constraints nullable="false"/>
      </column>
      <column name="geometry" type="GEOMETRY(Geometry, 4326)"/>
      <column name="data_scope" type="VARCHAR(32)">
        <constraints nullable="false"/>
      </column>
      <column name="valid_from" type="TIMESTAMPTZ">
        <constraints nullable="false"/>
      </column>
      <column name="valid_to" type="TIMESTAMPTZ" defaultValue="9999-12-31T23:59:59Z"/>
      <column name="created_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
        <constraints nullable="false"/>
      </column>
      <column name="updated_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
        <constraints nullable="false"/>
      </column>
      <column name="created_by" type="VARCHAR(128)"/>
      <column name="updated_by" type="VARCHAR(128)"/>
    </createTable>

    <sql>
      ALTER TABLE dictionary_records
      ADD CONSTRAINT dictionary_records_scope_check
      CHECK (data_scope IN ('public', 'internal', 'restricted'));
    </sql>

    <!-- EXCLUSION CONSTRAINT: запрещает overlapping valid периоды для одного business_key в одном словаре -->
    <sql>
      ALTER TABLE dictionary_records
      ADD CONSTRAINT dictionary_records_no_overlap
      EXCLUDE USING GIST (
        dictionary_id WITH =,
        business_key WITH =,
        tstzrange(valid_from, valid_to, '[)') WITH &amp;&amp;
      );
    </sql>

    <createIndex indexName="idx_dict_records_dict" tableName="dictionary_records">
      <column name="dictionary_id"/>
    </createIndex>
    <createIndex indexName="idx_dict_records_scope" tableName="dictionary_records">
      <column name="data_scope"/>
    </createIndex>
    <createIndex indexName="idx_dict_records_business_key" tableName="dictionary_records">
      <column name="dictionary_id"/>
      <column name="business_key"/>
    </createIndex>
    <createIndex indexName="idx_dict_records_valid_to" tableName="dictionary_records">
      <column name="valid_to"/>
    </createIndex>

    <sql>
      CREATE INDEX idx_dict_records_geometry ON dictionary_records USING GIST (geometry);
      CREATE INDEX idx_dict_records_data_gin ON dictionary_records USING GIN (data jsonb_path_ops);
    </sql>
  </changeSet>

</databaseChangeLog>
