<?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="014-create-stock-batches-table" author="effigenix">
        <createTable tableName="stock_batches">
            <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="batch_id" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="batch_type" type="VARCHAR(20)">
                <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="expiry_date" type="DATE">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)" defaultValue="AVAILABLE">
                <constraints nullable="false"/>
            </column>
            <column name="received_at" type="TIMESTAMP WITH TIME ZONE">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint baseTableName="stock_batches" baseColumnNames="stock_id"
                                 referencedTableName="stocks" referencedColumnNames="id"
                                 constraintName="fk_stock_batches_stock"
                                 onDelete="CASCADE"/>

        <addUniqueConstraint tableName="stock_batches" columnNames="stock_id, batch_id, batch_type"
                             constraintName="uq_stock_batch_reference"/>

        <createIndex tableName="stock_batches" indexName="idx_stock_batches_stock_id">
            <column name="stock_id"/>
        </createIndex>

        <sql>
            ALTER TABLE stock_batches ADD CONSTRAINT chk_stock_batch_type
                CHECK (batch_type IN ('PRODUCED', 'PURCHASED'));
        </sql>

        <sql>
            ALTER TABLE stock_batches ADD CONSTRAINT chk_stock_batch_status
                CHECK (status IN ('AVAILABLE', 'EXPIRING_SOON', 'BLOCKED', 'EXPIRED'));
        </sql>
    </changeSet>

</databaseChangeLog>
