<?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-latest.xsd">

    <changeSet id="034-create-inventory-counts-table" author="effigenix">
        <createTable tableName="inventory_counts">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="storage_location_id" type="VARCHAR(36)">
                <constraints nullable="false"
                             foreignKeyName="fk_inventory_counts_storage_location"
                             references="storage_locations(id)"/>
            </column>
            <column name="count_date" type="DATE">
                <constraints nullable="false"/>
            </column>
            <column name="initiated_by" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="TIMESTAMPTZ">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex tableName="inventory_counts" indexName="idx_inventory_counts_storage_location">
            <column name="storage_location_id"/>
        </createIndex>

        <createIndex tableName="inventory_counts" indexName="idx_inventory_counts_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

    <changeSet id="034-create-count-items-table" author="effigenix">
        <createTable tableName="count_items">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="inventory_count_id" type="VARCHAR(36)">
                <constraints nullable="false"
                             foreignKeyName="fk_count_items_inventory_count"
                             references="inventory_counts(id)"/>
            </column>
            <column name="article_id" type="VARCHAR(36)">
                <constraints nullable="false"
                             foreignKeyName="fk_count_items_article"
                             references="articles(id)"/>
            </column>
            <column name="expected_quantity_amount" type="DECIMAL(12,3)">
                <constraints nullable="false"/>
            </column>
            <column name="expected_quantity_unit" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="actual_quantity_amount" type="DECIMAL(12,3)"/>
            <column name="actual_quantity_unit" type="VARCHAR(20)"/>
        </createTable>

        <addUniqueConstraint tableName="count_items"
                             columnNames="inventory_count_id, article_id"
                             constraintName="uq_count_items_count_article"/>

        <createIndex tableName="count_items" indexName="idx_count_items_inventory_count">
            <column name="inventory_count_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
