<?xml version="1.0" encoding="UTF-8"?>
<!--

  This file is part of the Meeds project (https://meeds.io/).

  Copyright (C) 2022 Meeds Lab contact@meedslab.com

  This program 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 3 of the License, or (at your option) any later version.
  This program 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 program; if not, write to the Free Software Foundation,
  Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

-->
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

  <property name="autoIncrement" value="true" dbms="mysql,mssql,h2,sybase,db2,hsqldb"/>
  <property name="autoIncrement" value="false" dbms="oracle,postgresql"/>

  <!-- Definition of TWITTER_ACCOUNTS table -->
  <changeSet author="twitter-connector" id="1.0.0-1">
    <createTable tableName="TWITTER_ACCOUNTS">
      <column name="ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_TWITTER_ACCOUNTS"/>
      </column>
      <column name="REMOTE_ID" type="BIGINT">
        <constraints nullable="false"/>
      </column>
      <column name="IDENTIFIER" type="NVARCHAR(250)">
        <constraints nullable="false"/>
      </column>
      <column name="NAME" type="NVARCHAR(250)">
        <constraints nullable="false"/>
      </column>
      <column name="WATCHED_DATE" type="DATE">
        <constraints nullable="false"/>
      </column>
      <column name="WATCHED_BY" type="BIGINT">
        <constraints nullable="false"/>
      </column>
      <column name="UPDATED_DATE" type="DATE">
        <constraints nullable="false"/>
      </column>
      <column name="REFRESH_DATE" type="DATE">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>
  <changeSet author="twitter-connector" id="1.0.0-2" dbms="oracle,postgresql,hsqldb" onValidationFail="MARK_RAN" failOnError="false">
    <validCheckSum>ANY</validCheckSum>
    <preConditions>
      <not>
        <sequenceExists sequenceName="SEQ_TWITTER_ACCOUNTS_ID" />
      </not>
    </preConditions>
    <createSequence sequenceName="SEQ_TWITTER_ACCOUNTS_ID" startValue="1"/>
  </changeSet>

  <changeSet author="twitter-connector" id="1.0.0-3">
    <addColumn tableName="TWITTER_ACCOUNTS">
      <column name="LAST_MENTION_TWEET_ID" type="BIGINT" defaultValueNumeric="0"/>
    </addColumn>
  </changeSet>

  <changeSet author="twitter-connector" id="1.0.0-4">
    <createTable tableName="TWITTER_TWEETS">
      <column name="TWEET_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_TWITTER_TWEETS"/>
      </column>
      <column name="TWEET_LINK" type="NVARCHAR(250)">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <createTable tableName="TWITTER_TWEET_LIKERS">
      <column name="LIKER_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_TWITTER_TWEET_LIKER_ID" />
      </column>
      <column name="TWEET_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_TWITTER_TWEET_LIKER_ID" references="TWITTER_TWEETS(TWEET_ID)" deleteCascade="true" />
      </column>
      <column name="LIKER_USERNAME" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <createTable tableName="TWITTER_TWEET_RETWEETERS">
      <column name="RETWEETER_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_TWITTER_TWEET_RETWEETER_ID" />
      </column>
      <column name="TWEET_ID" type="BIGINT">
        <constraints nullable="false" foreignKeyName="FK_TWITTER_TWEET_RETWEETER_ID" references="TWITTER_TWEETS(TWEET_ID)" deleteCascade="true" />
      </column>
      <column name="RETWEETER_USERNAME" type="NVARCHAR(200)">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>
  <changeSet author="twitter-connector" id="1.0.0-5" dbms="oracle,postgresql,hsqldb" onValidationFail="MARK_RAN" failOnError="false">
    <preConditions>
      <not>
        <sequenceExists sequenceName="SEQ_TWITTER_TWEETS_ID" />
      </not>
    </preConditions>
    <createSequence sequenceName="SEQ_TWITTER_TWEETS_ID" startValue="1"/>
  </changeSet>
</databaseChangeLog>


