<?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,sybase,db2,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"/>

  <changeSet author="customer_space" id="1.0.0-1">
    <createTable tableName="CS_ADDON_SPACES">
      <column name="SPACE_ID" type="NVARCHAR(250)">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_CS_ADDON_SPACES_ID"/>
      </column>
      <column name="TASK_DEFAULT_ASSIGNEE" type="NVARCHAR(50)">
      	<constraints nullable="false" />
      </column>
      <column name="TASK_PRIORITY" type="NVARCHAR(50)">
      	<constraints nullable="false" />
      </column>
      <column name="TAG_PREFIX" type="NVARCHAR(50)">
      	<constraints nullable="false" />
      </column>
      <column name="MANAGERS_GROUP_ID" type="NVARCHAR(50)">
      </column>
      <column name="INVITED_MEMBERS_GROUP_ID" type="NVARCHAR(50)">
      </column>
      <column name="TAG_INDEX" type="BIGINT">
      	<constraints nullable="false" />
      </column>
      <column name="TICKET_LIMIT" type="BIGINT"/>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="customer_space" id="1.0.0-2">
    <createTable tableName="CS_ADDON_TYPES">
      <column name="TYPE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_CS_ADDON_TYPES_ID"/>
      </column>
      <column name="TYPE_LABEL" type="NVARCHAR(255)">
      	<constraints nullable="true" />
      </column>
      <column name="TYPE_OTHER" type="BOOLEAN">
      	<constraints nullable="false" />
      </column>
      <column name="TYPE_NAME" type="NVARCHAR(20)">
      	<constraints nullable="false" />
      </column>
    </createTable>
    <createTable tableName="CS_ADDON_ENVIRONMENTS">
      <column name="ENVIRONMENT_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_CS_ADDON_ENVIRONMENTS_ID"/>
      </column>
      <column name="NAME" type="NVARCHAR(550)"/>
      <column name="SPACE_ID" type="NVARCHAR(250)">
        <constraints nullable="true" foreignKeyName="FK_CS_SPACES_ENVIRONMENTS" references="CS_ADDON_SPACES(SPACE_ID)" />
      </column>

      <column name="DEFAULT_ENVIRONMENT" type="BOOLEAN"/>

      <column name="ENV_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_ENV" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>

      <column name="PRODUCT_VERSION" type="NVARCHAR(50)"/>

      <column name="OS_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_OS" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>

      <column name="OS_VERSION" type="NVARCHAR(50)"/>

      <column name="JVM_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_JVM" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>

      <column name="JVM_VERSION" type="NVARCHAR(50)"/>

      <column name="AS_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_AS" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>

      <column name="AS_VERSION" type="NVARCHAR(50)"/>

      <column name="DB_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_DB" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>

      <column name="DB_VERSION" type="NVARCHAR(50)"/>
      <column name="DB_ENCODING" type="NVARCHAR(50)"/>

      <column name="MORE_INFORMATION" type="NVARCHAR(250)"/>

      <column name="SSO_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_TYPE_SSO" references="CS_ADDON_TYPES(TYPE_ID)" />
      </column>
      <column name="SSO_NAME" type="NVARCHAR(50)"/>
      <column name="SSO_NAME_OTHER" type="NVARCHAR(50)"/>
      <column name="SSO_VERSION" type="NVARCHAR(50)"/>

    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="customer_space" id="1.0.0-3">
    <createTable tableName="CS_ADDON_TOPICS">
      <column name="TOPIC_ID" type="NVARCHAR(250)">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_CS_ADDON_TOPICS_ID"/>
      </column>
      <column name="ENVIRONMENT_ID" type="BIGINT">
        <constraints nullable="true" foreignKeyName="FK_CS_ENVIRONMENTS_TOPICS" references="CS_ADDON_ENVIRONMENTS(ENVIRONMENT_ID)" />
      </column>
      <column name="SPACE_ID" type="NVARCHAR(250)">
        <constraints nullable="true" foreignKeyName="FK_CS_SPACES_TOPICS" references="CS_ADDON_SPACES(SPACE_ID)" />
      </column>
      <column name="TOPIC_ASSIGNEE" type="NVARCHAR(50)" />
      <column name="TYPE" type="NVARCHAR(50)" />
      <column name="INFO_TYPE" type="NVARCHAR(50)" />
      <column name="SEVERITY" type="NVARCHAR(50)" />
      <column name="LINK" type="NVARCHAR(255)" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="customer_space" id="1.0.0-4">
    <createTable tableName="CS_ADDON_BROWSERS">
      <column name="BROWSER" type="NVARCHAR(50)">
        <constraints nullable="false"/>
      </column>
      <column name="ENVIRONMENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_CS_ENVIRONMENTS_BROWSERS" references="CS_ADDON_ENVIRONMENTS(ENVIRONMENT_ID)" />
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>
  <changeSet author="customer_space" id="1.0.0-5">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="STATUS" type="NVARCHAR(50)">
        <constraints nullable="true"/>
      </column>
    </addColumn>
  </changeSet>
  <changeSet author="customer_space" id="1.0.0-6">
    <createTable tableName="CS_ADDON_LOG">
      <column name="LOG_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_CS_ADDON_LOGS_ID"/>
      </column>
      <column name="TICKET_ID" type="NVARCHAR(250)">
        <constraints nullable="true" foreignKeyName="FK_CS_TOPICS_LOGS" references="CS_ADDON_TOPICS(TOPIC_ID)" />
      </column>
      <column name="TYPE" type="NVARCHAR(50)" />
      <column name="USER" type="NVARCHAR(50)" />
      <column name="DATE" type="DATETIME" />
      <column name="ORIGINAL_VALUE" type="NVARCHAR(50)" />
      <column name="NEW_VALUE" type="NVARCHAR(255)" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>
  <changeSet author="customer_space" id="1.0.0-7">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="UPDATEDATE" type="DATETIME"/>
    </addColumn>
  </changeSet>
  <changeSet author="customer_space" id="1.0.0-8">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="TITLE" type="NVARCHAR(250)"/>
    </addColumn>
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="CREATIONDATE" type="DATETIME"/>
    </addColumn>
  </changeSet>
  <changeSet author="customer_space" id="1.0.0-9">
    <addColumn tableName="CS_ADDON_SPACES">
      <column name="INFORMATION_FLOW" type="NVARCHAR(50)" defaultValue="INFORMATION"/>
      <column name="INCIDENT_FLOW" type="NVARCHAR(50)" defaultValue="INCIDENT"/>
    </addColumn>
  </changeSet>
  <changeSet id="1.0.0-10" author="customer-space">
    <addColumn tableName="CS_ADDON_SPACES">
      <column name="HOSTED" type="BOOLEAN" defaultValueBoolean="false">
        <constraints nullable="false"></constraints>
      </column>
    </addColumn>
  </changeSet>
  <changeSet id="1.0.0-11" author="customer-space">
    <addColumn tableName="CS_ADDON_SPACES">
      <column name="LANG" type="NVARCHAR(10)" defaultValue="EN"/> 
    </addColumn>
  </changeSet>
  <changeSet id="1.0.0-12" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="TICKET_ID" type="NVARCHAR(50)"/>
    </addColumn>
  </changeSet>

  <changeSet id="1.0.0-13" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="CREATOR" type="NVARCHAR(50)"/>
    </addColumn>
  </changeSet>

  <changeSet id="1.0.0-14" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="OWNER" type="NVARCHAR(50)"/>
    </addColumn>
  </changeSet>

  <changeSet id="1.0.0-15" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="ATTACHED_JIRA" type="NVARCHAR(50)"/>
    </addColumn>
  </changeSet>


  <changeSet author="customer_space" id="1.0.0-16">
    <createTable tableName="CS_ADDON_STAT_LOG">
      <column name="STAT_LOG_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true"/>
      </column>
      <column name="STAT_DATE" type="DATETIME"/>
      <column name="STAT_TYPE" type="NVARCHAR(50)" />
      <column name="STATUS" type="NVARCHAR(50)" />
      <column name="TICKETS_NUMBER" type="BIGINT" />
      <column name="WEEKLY" type="BOOLEAN" />
      <column name="MONTHLY" type="BOOLEAN" />
        <column name="SPACE_ID" type="NVARCHAR(50)" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet id="1.0.0-17" author="customer-space">
    <addColumn tableName="CS_ADDON_ENVIRONMENTS">
      <column name="UPDATEDATE" type="DATETIME"/>
    </addColumn>
  </changeSet>

  <changeSet id="1.0.0-18" author="customer-space">
    <addColumn tableName="CS_ADDON_SPACES">
      <column name="SLA" type="NVARCHAR(50)"/>
    </addColumn>
  </changeSet>


  <changeSet id="1.0.0-19" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="FIRST_WARNING_DATE" type="DATETIME"/>
      <column name="LAST_WARNING_DATE" type="DATETIME"/>
    </addColumn>
  </changeSet>


  <changeSet id="1.0.0-20" author="customer-space">
    <addColumn tableName="CS_ADDON_SPACES">
      <column name="SUBSCRIPTION" type="NVARCHAR(50)"/>
      <column name="SUBSCRIPTION_START_DATE" type="DATETIME"/>
      <column name="SUBSCRIPTION_END_DATE" type="DATETIME"/>
      <column name="SUBSCRIPTION_ACTIVE" type="BOOLEAN"/>
    </addColumn>
  </changeSet>

  <changeSet id="1.0.0-21" author="customer-space">
    <update tableName="CS_ADDON_SPACES">
      <column name="SUBSCRIPTION_ACTIVE" valueBoolean="true" />
      <where>SUBSCRIPTION_ACTIVE IS NULL</where>
    </update>
  </changeSet>

  <changeSet id="1.0.0-22" author="customer-space">
    <addColumn tableName="CS_ADDON_TOPICS">
      <column name="TIME_TO_FIRST_RESPONSE" type="BIGINT"/>
      <column name="TIME_TO_RESOLUTION" type="BIGINT"/>
      <column name="CUSTOMER_SATISFIED" type="BOOLEAN"/>
    </addColumn>
  </changeSet>


  <changeSet id="1.0.0-23" author="customer-space">
    <addColumn tableName="CS_ADDON_LOG">
      <column name="TIME_TO_FIRST_RESPONSE" type="BIGINT"/>
      <column name="TIME_TO_RESOLUTION" type="BIGINT"/>
    </addColumn>
  </changeSet>

</databaseChangeLog>



