<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    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-latest.xsd
                        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <!--
        Added the entity InputData.
    -->
    <changeSet id="20241015092707-1" author="jhipster">
        <createTable tableName="input_data" remarks="Input data Values\nThe values inserted from:\n- Each Organic Unit\n- Variable\n- PeriodVersion">
            <column name="id" type="bigint" autoIncrement="true" startWith="1500">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="source_value" type="decimal(21,2)" remarks="The value of the data in the source Unit. This might be different from the Variable baseUnit. @see variableValue and variableUnit">
                <constraints nullable="false" />
            </column>
            <column name="variable_value" type="decimal(21,2)" remarks="The value of the data converted to the variable baseUnit.">
                <constraints nullable="false" />
            </column>
            <column name="imputed_value" type="decimal(21,2)" remarks="The value that the InputData.owner is accounted for.\nIn case a variableValue is distributed to other Organization&apos;s (InputData.source==DataSources.DISTRIB)\n, this will have the value to consider when calculating the final inventory value for the variable">
                <constraints nullable="false" />
            </column>
            <column name="source_type" type="varchar(255)" remarks="What was the source of this data ?">
                <constraints nullable="false" />
            </column>
            <column name="data_date" type="date" remarks="The date that this data relates to.\nEg. energy consumptium happens every month, this should be the date of invoice\n[optional]">
                <constraints nullable="true" />
            </column>
            <column name="change_date" type="${datetimeType}" remarks="Last change date">
                <constraints nullable="false" />
            </column>
            <column name="change_username" type="varchar(255)" remarks="The actual username logged into the system, that last changed the data">
                <constraints nullable="false" />
            </column>
            <column name="data_source" type="varchar(255)" remarks="Where this data originates from? Where were the data collected ?">
                <constraints nullable="true" />
            </column>
            <column name="data_user" type="varchar(255)" remarks="Who collected the data ?">
                <constraints nullable="true" />
            </column>
            <column name="data_comments" type="varchar(255)" remarks="Comments added by the user">
                <constraints nullable="true" />
            </column>
            <column name="creation_date" type="${datetimeType}" remarks="The creation date (insert). When the data was inserted into the system.">
                <constraints nullable="false" />
            </column>
            <column name="creation_username" type="varchar(255)" remarks="The actual username logged into the system, that inserted the data. NOT a reference to the user, but the login username.">
                <constraints nullable="false" />
            </column>
            <column name="version" type="integer" defaultValueNumeric="-1" remarks="Record version for concurrency control.">
                <constraints nullable="false" />
            </column>
            <column name="variable_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="period_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="period_version_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="source_unit_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="unit_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="owner_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="source_input_data_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <column name="source_input_data_upload_id" type="bigint">
                <constraints nullable="true" />
            </column>
            <!-- jhipster-needle-liquibase-add-column - JHipster will add columns here -->
        </createTable>
        <dropDefaultValue tableName="input_data" columnName="change_date" columnDataType="${datetimeType}"/>
        <dropDefaultValue tableName="input_data" columnName="creation_date" columnDataType="${datetimeType}"/>
    </changeSet>

    <!-- jhipster-needle-liquibase-add-changeset - JHipster will add changesets here -->

    <!--
        Load sample data generated with Faker.js
        - This data can be easily edited using a CSV editor (or even MS Excel) and
          is located in the 'src/main/resources/config/liquibase/fake-data' directory
        - By default this data is applied when running with the JHipster 'dev' profile.
          This can be customized by adding or removing 'faker' in the 'spring.liquibase.contexts'
          Spring Boot configuration key.
    -->
    <changeSet id="20241015092707-1-data" author="jhipster" context="faker">
        <loadData
                  file="config/liquibase/fake-data/input_data.csv"
                  separator=";"
                  tableName="input_data"
                  usePreparedStatements="true">
            <column name="id" type="numeric"/>
            <column name="source_value" type="numeric"/>
            <column name="variable_value" type="numeric"/>
            <column name="imputed_value" type="numeric"/>
            <column name="source_type" type="string"/>
            <column name="data_date" type="date"/>
            <column name="change_date" type="date"/>
            <column name="change_username" type="string"/>
            <column name="data_source" type="string"/>
            <column name="data_user" type="string"/>
            <column name="data_comments" type="string"/>
            <column name="creation_date" type="date"/>
            <column name="creation_username" type="string"/>
            <column name="version" type="numeric"/>
            <!-- jhipster-needle-liquibase-add-loadcolumn - JHipster (and/or extensions) can add load columns here -->
        </loadData>
    </changeSet>
</databaseChangeLog>
