<?xml version="1.0" encoding="UTF-8"?>
<!--
 This file is part of the Meeds project (https://meeds.io/).

 Copyright (C) 2020 - 2025 Meeds Association contact@meeds.io

 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="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="file" id="1.0.0-1">
    <createTable tableName="FILES_NAMESPACES">
      <column name="NAMESPACE_ID" type="INT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_FILES_NAMESPACES_ID"/>
      </column>
      <column name="NAME" type="NVARCHAR(30)">
        <constraints nullable="false"/>
      </column>
      <column name="DESCRIPTION" type="NVARCHAR(100)"/>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="file" id="1.0.0-2">
    <createTable tableName="FILES_FILES">
      <column name="FILE_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_FILES_FILES_ID"/>
      </column>
      <column name="NAMESPACE_ID" type="INT">
        <constraints nullable="false" foreignKeyName="FK_FILES_FILES_NAMESPACE_ID" references="FILES_NAMESPACES(NAMESPACE_ID)"/>
      </column>
      <column name="NAME" type="NVARCHAR(550)">
        <constraints nullable="false"/>
      </column>
      <column name="MIMETYPE" type="NVARCHAR(100)"/>
      <column name="FILE_SIZE" type="BIGINT"/>
      <column name="UPDATED_DATE" type="TIMESTAMP" defaultValueComputed="${now}">
        <constraints nullable="false"/>
      </column>
      <column name="UPDATER" type="NVARCHAR(100)"/>
      <column name="CHECKSUM" type="NVARCHAR(100)"/>
      <column name="DELETED" type="BOOLEAN" defaultValueBoolean="false"/>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

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

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

  <changeSet author="file" id="1.0.0-7">
    <createTable tableName="FILES_BINARY">
      <column name="BLOB_ID" type="BIGINT" autoIncrement="${autoIncrement}" startWith="1">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_FILES_BLOB_ID"/>
      </column>
      <column name="NAME" type="NVARCHAR(100)">
        <constraints nullable="false"  unique="true"/>
      </column>
      <column name="DATA" type="LONGBLOB"/>
      <column name="UPDATED_DATE" type="TIMESTAMP" defaultValueComputed="${now}"/>
    </createTable>
    <modifySql dbms="mysql">
      <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
    </modifySql>
  </changeSet>

  <changeSet author="file" id="1.0.0-8" dbms="oracle,postgresql">
    <createSequence sequenceName="SEQ_FILES_BINARY_BLOB_ID" startValue="1"/>
  </changeSet>

  <changeSet author="file" id="1.0.0-9">
    <createIndex tableName="FILES_FILES" indexName="IDX_FILES_FILES_01">
      <column name="CHECKSUM"/>
    </createIndex>
    <createIndex tableName="FILES_FILES" indexName="IDX_FILES_FILES_02">
      <column name="DELETED"/>
      <column name="UPDATED_DATE"/>
    </createIndex>
  </changeSet>

  <changeSet author="file" id="1.0.0-10">
    <createIndex tableName="FILES_NAMESPACES" indexName="IDX_FILES_NAMESPACES_01">
      <column name="NAME"/>
    </createIndex>
  </changeSet>

  <changeSet author="file" id="1.0.0-12" dbms="mysql,mssql,h2,sybase,db2,hsqldb,postgresql">
    <createIndex tableName="FILES_BINARY" indexName="IDX_FILES_BINARY_01">
      <column name="NAME"/>
    </createIndex>
  </changeSet>

  <changeSet author="file" id="1.0.0-13">
    <modifyDataType columnName="UPDATER"
                    newDataType="NVARCHAR(200)"
                    tableName="FILES_FILES"/>
  </changeSet>

  <changeSet author="file" id="1.0.0-14" dbms="hsqldb">
    <createSequence sequenceName="SEQ_FILES_FILES_FILE_ID" startValue="1" />
    <createSequence sequenceName="SEQ_FILES_NS_NAMESPACE_ID" startValue="1" />
    <createSequence sequenceName="SEQ_FILES_BINARY_BLOB_ID" startValue="1" />
  </changeSet>

  <changeSet author="file" id="1.0.0-15" onValidationFail="MARK_RAN">
    <preConditions onFail="MARK_RAN">
      <tableExists tableName="FILES_ORPHAN_FILES" />
    </preConditions>
    <dropTable tableName="FILES_ORPHAN_FILES" />
  </changeSet>

</databaseChangeLog>
