<?xml version="1.0" encoding="UTF-8"?>
<!-- Copyright (C) 2003-2018 eXo Platform SAS. This is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version. This software is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public 
  License for more details. You should have received a copy of the GNU Lesser General Public License along with this software; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA, or see the FSF site: http://www.fsf.org. -->
<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.4.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="agenda" id="1.0.0-0" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_AGENDA_CALENDAR_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_ATTACHMENT_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_ATTENDEE_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_CONFERENCE_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_RECURRENCE_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_REMINDER_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_PROVIDER_ID" startValue="1" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-1">
    <createTable tableName="EXO_AGENDA_CALENDAR">
      <column name="CALENDAR_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_CALENDAR" />
      </column>
      <column name="OWNER_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="IS_SYSTEM" type="BOOLEAN" defaultValueBoolean="false" />
      <column name="DESCRIPTION" type="NVARCHAR(2000)" />
      <column name="COLOR" type="NVARCHAR(20)" />
      <column name="CREATED_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="UPDATED_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-2">
    <createTable tableName="EXO_AGENDA_REMOTE_PROVIDER">
      <column name="AGENDA_PROVIDER_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_PROVIDER" />
      </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="agenda" id="1.0.0-3">
    <createTable tableName="EXO_AGENDA_EVENT">
      <column name="EVENT_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_EVENT" />
      </column>
      <column name="PARENT_EVENT_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_PARENT_EVENT" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="CALENDAR_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_CALENDAR" references="EXO_AGENDA_CALENDAR(CALENDAR_ID)" />
      </column>
      <column name="REMOTE_PROVIDER_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_REMOTE_PROVIDER" references="EXO_AGENDA_REMOTE_PROVIDER(AGENDA_PROVIDER_ID)" />
      </column>
      <column name="REMOTE_ID" type="VARCHAR(500)" />
      <column name="CREATOR_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="CREATED_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="UPDATED_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="START_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="END_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="OCCURRENCE_ID" type="TIMESTAMP" />
      <column name="SUMMARY" type="NVARCHAR(2000)" />
      <column name="DESCRIPTION" type="TEXT" />
      <column name="COLOR" type="NVARCHAR(20)" />
      <column name="LOCATION" type="NVARCHAR(2000)" />
      <column name="AVAILABILITY" type="SMALLINT" />
      <column name="STATUS" type="SMALLINT" />
      <column name="ALL_DAY" type="BOOLEAN" />
      <column name="ALLOW_ATTENDEE_TO_UPDATE" type="BOOLEAN" defaultValueBoolean="false" />
      <column name="ALLOW_ATTENDEE_TO_INVITE" type="BOOLEAN" defaultValueBoolean="false" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-4">
    <createTable tableName="EXO_AGENDA_RECURRENCE">
      <column name="EVENT_RECURRENCE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_EVENT_RECURRENCE" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_AGENDA_EVENT_RECURRENCE" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="RECURRENCE_START_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="RECURRENCE_END_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="RECURRENCE_TYPE" type="SMALLINT" defaultValueNumeric="0" />
      <column name="RECURRENCE_RRULE" type="VARCHAR(1000)">
        <constraints nullable="false" />
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-5">
    <createTable tableName="EXO_AGENDA_CONFERENCE">
      <column name="EVENT_CONFERENCE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_CONFERENCE" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_AGENDA_EVENT_CONFERENCE" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="TYPE" type="VARCHAR(200)" />
      <column name="URI" type="VARCHAR(500)" />
      <column name="PHONE" type="VARCHAR(200)" />
      <column name="ACCESS_CODE" type="VARCHAR(200)" />
      <column name="DESCRIPTION" type="TEXT" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-6">
    <createTable tableName="EXO_AGENDA_ATTENDEE">
      <column name="EVENT_ATTENDEE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_ATTENDEE" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_AGENDA_EVENT_ATTENDEE" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="IDENTITY_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="RESPONSE" type="SMALLINT">
        <constraints nullable="false" />
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-7">
    <createTable tableName="EXO_AGENDA_ATTACHMENT">
      <column name="EVENT_ATTACHMENT_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_ATTACHMENT" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_AGENDA_EVENT_ATTACHMENT" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="FILE_ID" type="VARCHAR(200)">
        <constraints nullable="false" />
      </column>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-8">
    <createTable tableName="EXO_AGENDA_REMINDER">
      <column name="EVENT_REMINDER_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_REMINDER" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_AGENDA_EVENT_REMINDER" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="RECEIVER_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="BEFORE_VALUE" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="BEFORE_PERIOD_TYPE" type="SMALLINT" defaultValueNumeric="0" />
      <column name="TRIGGER_DATE" type="TIMESTAMP" defaultValueComputed="NULL" />
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci" />
    </modifySql>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-9">
    <addColumn tableName="EXO_AGENDA_EVENT">
      <column name="MODIFIER_ID" type="BIGINT" defaultValueNumeric="0"/>
    </addColumn>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-10">
    <addUniqueConstraint tableName="EXO_AGENDA_EVENT" columnNames="EVENT_ID, OCCURRENCE_ID"/>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-11">
    <addColumn tableName="EXO_AGENDA_EVENT">
      <column name="TIMEZONE_ID" type="VARCHAR(50)" defaultValueComputed="NULL" />
    </addColumn>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-12">
    <addColumn tableName="EXO_AGENDA_REMOTE_PROVIDER">
      <column name="ENABLED" type="BOOLEAN" defaultValueBoolean="true" />
    </addColumn>
    <addUniqueConstraint tableName="EXO_AGENDA_REMOTE_PROVIDER" columnNames="NAME" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-13">
    <renameColumn tableName="EXO_AGENDA_CONFERENCE" oldColumnName="URI" newColumnName="URL" columnDataType="VARCHAR(500)" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-14" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_AGENDA_REMOTE_EVENT_ID" startValue="1" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-15">
    <dropForeignKeyConstraint baseTableName="EXO_AGENDA_EVENT" constraintName="FK_AGENDA_REMOTE_PROVIDER"/>
    <dropColumn tableName="EXO_AGENDA_EVENT" columnName="REMOTE_PROVIDER_ID" />
    <dropColumn tableName="EXO_AGENDA_EVENT" columnName="REMOTE_ID" />
    <createTable tableName="EXO_AGENDA_REMOTE_EVENT">
      <column name="REMOTE_EVENT_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_REMOTE_EVENT" />
      </column>
      <column name="REMOTE_PROVIDER_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_REMOTE_EVENT_REMOTE_PROVIDER" references="EXO_AGENDA_REMOTE_PROVIDER(AGENDA_PROVIDER_ID)" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_REMOTE_EVENT" references="EXO_AGENDA_EVENT(EVENT_ID)" />
      </column>
      <column name="IDENTITY_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
      <column name="REMOTE_ID" type="VARCHAR(500)">
        <constraints nullable="false" />
      </column>
    </createTable>
    <addUniqueConstraint tableName="EXO_AGENDA_REMOTE_EVENT" columnNames="EVENT_ID,IDENTITY_ID" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-16">
    <addColumn tableName="EXO_AGENDA_REMOTE_PROVIDER">
      <column name="CLIENT_API" type="NVARCHAR(2000)" />
    </addColumn>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-17">
    <addColumn tableName="EXO_AGENDA_REMINDER">
      <column name="FROM_OCCURRENCE_ID" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="UNTIL_OCCURRENCE_ID" type="TIMESTAMP" defaultValueComputed="NULL" />
    </addColumn>
  </changeSet>
  <changeSet author="agenda" id="1.0.0-18" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_AGENDA_EVENT_DATE_POLL_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_DATE_OPTION_ID" startValue="1" />
    <createSequence sequenceName="SEQ_AGENDA_EVENT_DATE_VOTE_ID" startValue="1" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-19">
    <createTable tableName="EXO_AGENDA_EVENT_DATE_OPTION">
      <column name="EVENT_DATE_OPTION_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_EVENT_DATE_OPTION" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_DATE_OPTION_EVENT" references="EXO_AGENDA_EVENT(EVENT_ID)" nullable="false" />
      </column>
      <column name="START_DATE" type="TIMESTAMP">
        <constraints nullable="false" />
      </column>
      <column name="END_DATE" type="TIMESTAMP">
        <constraints nullable="false" />
      </column>
      <column name="ALL_DAY" type="BOOLEAN" />
    </createTable>
    <createTable tableName="EXO_AGENDA_EVENT_DATE_VOTE">
      <column name="EVENT_DATE_VOTE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_EVENT_DATE_VOTE" />
      </column>
      <column name="EVENT_DATE_OPTION_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_DATE_OPTION_VOTE" references="EXO_AGENDA_EVENT_DATE_OPTION(EVENT_DATE_OPTION_ID)" nullable="false" />
      </column>
      <column name="IDENTITY_ID" type="BIGINT">
        <constraints nullable="false" />
      </column>
    </createTable>
    <createTable tableName="EXO_AGENDA_EVENT_DATE_POLL">
      <column name="EVENT_DATE_POLL_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_AGENDA_EVENT_DATE_POLL" />
      </column>
      <column name="EVENT_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_DATE_POLL_EVENT" references="EXO_AGENDA_EVENT(EVENT_ID)" nullable="false" />
      </column>
      <column name="SELECTED_DATE_OPTION_ID" type="BIGINT">
        <constraints foreignKeyName="FK_AGENDA_EVENT_DATE_POLL_OPTION" references="EXO_AGENDA_EVENT_DATE_OPTION(EVENT_DATE_OPTION_ID)" />
      </column>
    </createTable>
    <addUniqueConstraint tableName="EXO_AGENDA_EVENT_DATE_POLL" columnNames="EVENT_ID" />
    <addUniqueConstraint tableName="EXO_AGENDA_EVENT_DATE_VOTE" columnNames="IDENTITY_ID,EVENT_DATE_OPTION_ID" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-20">
    <addUniqueConstraint tableName="EXO_AGENDA_ATTENDEE" columnNames="EVENT_ID,IDENTITY_ID" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-21">
    <addColumn tableName="EXO_AGENDA_EVENT">
      <column name="OCCURRENCE_PERIOD_CHANGED" type="BOOLEAN" defaultValueBoolean="false" />
    </addColumn>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-22">
    <dropTable tableName="EXO_AGENDA_ATTACHMENT" />
  </changeSet>

  <changeSet author="agenda" id="1.0.0-23" dbms="hsqldb">
    <customChange class="org.exoplatform.agenda.liquibase.DropHSQLDBUniqueConstraint">
      <param name="tableName">EXO_AGENDA_ATTENDEE</param>
    </customChange>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-24" dbms="mysql">
    <dropAllForeignKeyConstraints baseTableName="EXO_AGENDA_ATTENDEE" />
    <customChange class="org.exoplatform.agenda.liquibase.DropMySQLUniqueConstraint">
      <param name="tableName">EXO_AGENDA_ATTENDEE</param>
    </customChange>
    <addForeignKeyConstraint constraintName="FK_AGENDA_EVENT_ATTENDEE" referencedTableName="EXO_AGENDA_EVENT" referencedColumnNames="EVENT_ID" baseColumnNames="EVENT_ID" baseTableName="EXO_AGENDA_ATTENDEE"/>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-25" dbms="postgresql">
    <customChange class="org.exoplatform.agenda.liquibase.DropPostgreSQLUniqueConstraint">
      <param name="tableName">EXO_AGENDA_ATTENDEE</param>
    </customChange>
  </changeSet>

  <changeSet author="agenda" id="1.0.0-26">
    <addColumn tableName="EXO_AGENDA_ATTENDEE">
      <column name="FROM_OCCURRENCE_ID" type="TIMESTAMP" defaultValueComputed="NULL" />
      <column name="UNTIL_OCCURRENCE_ID" type="TIMESTAMP" defaultValueComputed="NULL" />
    </addColumn>
    <addUniqueConstraint tableName="EXO_AGENDA_ATTENDEE" columnNames="EVENT_ID,IDENTITY_ID,FROM_OCCURRENCE_ID" constraintName="UK_EXO_AGENDA_ATTENDEE" />
  </changeSet>

</databaseChangeLog>