<?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="028-create-stock-movements-table" author="effigenix">
        <comment>Create stock_movements table for immutable stock movement audit trail</comment>

        <createTable tableName="stock_movements">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="stock_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="article_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="stock_batch_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="batch_id" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="batch_type" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="movement_type" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="direction" type="VARCHAR(10)">
                <constraints nullable="false"/>
            </column>
            <column name="quantity_amount" type="DECIMAL(19,6)">
                <constraints nullable="false"/>
            </column>
            <column name="quantity_unit" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="reason" type="VARCHAR(500)"/>
            <column name="reference_document_id" type="VARCHAR(100)"/>
            <column name="performed_by" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="performed_at" type="TIMESTAMP WITH TIME ZONE">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint baseTableName="stock_movements" baseColumnNames="stock_id"
                                 referencedTableName="stocks" referencedColumnNames="id"
                                 constraintName="fk_stock_movements_stock"/>

        <addForeignKeyConstraint baseTableName="stock_movements" baseColumnNames="article_id"
                                 referencedTableName="articles" referencedColumnNames="id"
                                 constraintName="fk_stock_movements_article"/>

        <addForeignKeyConstraint baseTableName="stock_movements" baseColumnNames="stock_batch_id"
                                 referencedTableName="stock_batches" referencedColumnNames="id"
                                 constraintName="fk_stock_movements_stock_batch"/>

        <sql>
            ALTER TABLE stock_movements ADD CONSTRAINT chk_stock_movements_batch_type
                CHECK (batch_type IN ('PRODUCED', 'PURCHASED'));

            ALTER TABLE stock_movements ADD CONSTRAINT chk_stock_movements_movement_type
                CHECK (movement_type IN ('GOODS_RECEIPT', 'PRODUCTION_OUTPUT', 'PRODUCTION_CONSUMPTION',
                    'SALE', 'INTER_BRANCH_TRANSFER', 'WASTE', 'ADJUSTMENT', 'RETURN'));

            ALTER TABLE stock_movements ADD CONSTRAINT chk_stock_movements_direction
                CHECK (direction IN ('IN', 'OUT'));
        </sql>

        <createIndex tableName="stock_movements" indexName="idx_stock_movements_stock_id">
            <column name="stock_id"/>
        </createIndex>

        <createIndex tableName="stock_movements" indexName="idx_stock_movements_article_id">
            <column name="article_id"/>
        </createIndex>

        <createIndex tableName="stock_movements" indexName="idx_stock_movements_movement_type">
            <column name="movement_type"/>
        </createIndex>

        <createIndex tableName="stock_movements" indexName="idx_stock_movements_performed_at">
            <column name="performed_at"/>
        </createIndex>

        <createIndex tableName="stock_movements" indexName="idx_stock_movements_stock_batch_id">
            <column name="stock_batch_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
