<?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-3.3.xsd">

  <!-- Managing both DB that use sequences and db that use auto increment -->
  <property name="autoIncrement" value="true" dbms="mysql,mssql,h2,hsqldb"/>
  <property name="autoIncrement" value="false" dbms="oracle,postgresql"/>

  <!-- Managing auto generation of timestamp by Database -->
  <property name="now" value="now()" dbms="mysql,hsqldb,postgresql,h2"/>
  <property name="now" value="sysdate" dbms="oracle"/>
  <property name="now" value="CURRENT_TIMESTAMP" dbms="mssql"/>

<!-- SETTINGS -->
  <changeSet author="settings" id="1.0.0-1">
    <createTable tableName="STG_CONTEXTS">
      <column name="CONTEXT_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_STG_CONTEXT_ID"/>
      </column>
      <column name="TYPE" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
      <column name="NAME" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="settings" id="1.0.0-2">
    <createTable tableName="STG_SCOPES">
      <column name="SCOPE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_STG_SCOPE_ID"/>
      </column>
      <column name="TYPE" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
      <column name="NAME" type="NVARCHAR(200)">
        <constraints />
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="settings" id="1.0.0-3">
    <createTable tableName="STG_SETTINGS">
      <column name="SETTING_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_STG_SETTINGS_ID"/>
      </column>
      <column name="NAME" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
      <column name="VALUE" type="CLOB"/>
      <column name="CONTEXT_ID" type="BIGINT">
        <constraints nullable="false"/>
      </column>
      <column name="SCOPE_ID" type="BIGINT">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="settings" id="1.0.0-4" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_STG_SETTINGS_COMMON_ID" startValue="1"/>
  </changeSet>

  <changeSet author="settings" id="1.0.0-5" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_STG_CONTEXT_COMMON_ID" startValue="1"/>
  </changeSet>

  <changeSet author="settings" id="1.0.0-6" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_STG_SCOPE_COMMON_ID" startValue="1"/>
  </changeSet>

  <changeSet author="settings" id="1.0.0-7">
    <addForeignKeyConstraint baseColumnNames="CONTEXT_ID" baseTableName="STG_SETTINGS"
                             constraintName="FK_STG_SETTINGS_CONTEXT_01" deferrable="false" initiallyDeferred="false"
                             onDelete="NO ACTION" onUpdate="NO ACTION"
                             referencedColumnNames="CONTEXT_ID" referencedTableName="STG_CONTEXTS"/>
  </changeSet>
  <changeSet author="settings" id="1.0.0-8">
    <addForeignKeyConstraint baseColumnNames="SCOPE_ID" baseTableName="STG_SETTINGS"
                             constraintName="FK_STG_SETTINGS_SCOPE_01" deferrable="false" initiallyDeferred="false"
                             onDelete="NO ACTION" onUpdate="NO ACTION"
                             referencedColumnNames="SCOPE_ID" referencedTableName="STG_SCOPES"/>
  </changeSet>
  <changeSet author="settings" id="1.0.0-9">
    <addUniqueConstraint columnNames="TYPE, NAME" tableName="STG_CONTEXTS"
                         constraintName="UK_STG_CONTEXT_01"/>
  </changeSet>
  <changeSet author="settings" id="1.0.0-10">
    <addUniqueConstraint columnNames="TYPE, NAME" tableName="STG_SCOPES"
                         constraintName="UK_STG_SCOPE_01"/>
  </changeSet>
  <changeSet author="settings" id="1.0.0-11">
    <addUniqueConstraint columnNames="NAME, SCOPE_ID, CONTEXT_ID" tableName="STG_SETTINGS"
                         constraintName="UK_STG_SETTING_01"/>
  </changeSet>
  <changeSet author="settings" id="1.0.0-12">
    <createIndex tableName="STG_SETTINGS" indexName="IDX_STG_SETTINGS_01">
      <column name="NAME"/>
    </createIndex>
    <createIndex tableName="STG_SETTINGS" indexName="IDX_STG_SETTINGS_02">
      <column name="SCOPE_ID"/>
    </createIndex>
    <createIndex tableName="STG_SETTINGS" indexName="IDX_STG_SETTINGS_03">
      <column name="CONTEXT_ID"/>
    </createIndex>
  </changeSet>

  <changeSet author="settings" id="1.0.0-13" dbms="hsqldb">
    <createSequence sequenceName="SEQ_STG_SETTINGS_COMMON_ID" startValue="1"/>
    <createSequence sequenceName="SEQ_STG_CONTEXT_COMMON_ID" startValue="1"/>
    <createSequence sequenceName="SEQ_STG_SCOPE_COMMON_ID" startValue="1"/>
  </changeSet>

  <changeSet author="settings" id="1.0.0-14" dbms="mysql">
    <sql>
      ALTER TABLE STG_SETTINGS MODIFY COLUMN VALUE longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    </sql>
  </changeSet>

</databaseChangeLog>