<?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="024-create-reservations-table" author="effigenix">
        <createTable tableName="reservations">
            <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="reference_type" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="reference_id" type="VARCHAR(100)">
                <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="priority" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="reserved_at" type="TIMESTAMP WITH TIME ZONE">
                <constraints nullable="false"/>
            </column>
        </createTable>

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

        <sql>
            ALTER TABLE reservations ADD CONSTRAINT chk_reservation_reference_type
            CHECK (reference_type IN ('PRODUCTION_ORDER', 'SALE_ORDER'));
        </sql>

        <sql>
            ALTER TABLE reservations ADD CONSTRAINT chk_reservation_priority
            CHECK (priority IN ('URGENT', 'NORMAL', 'LOW'));
        </sql>

        <createIndex tableName="reservations" indexName="idx_reservations_stock_id">
            <column name="stock_id"/>
        </createIndex>

        <createIndex tableName="reservations" indexName="idx_reservations_reference">
            <column name="reference_type"/>
            <column name="reference_id"/>
        </createIndex>
    </changeSet>

    <changeSet id="024-create-stock-batch-allocations-table" author="effigenix">
        <createTable tableName="stock_batch_allocations">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="reservation_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="stock_batch_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="allocated_quantity_amount" type="DECIMAL(19,6)">
                <constraints nullable="false"/>
            </column>
            <column name="allocated_quantity_unit" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint
                baseTableName="stock_batch_allocations" baseColumnNames="reservation_id"
                referencedTableName="reservations" referencedColumnNames="id"
                constraintName="fk_allocations_reservation_id"
                onDelete="CASCADE"/>

        <addForeignKeyConstraint
                baseTableName="stock_batch_allocations" baseColumnNames="stock_batch_id"
                referencedTableName="stock_batches" referencedColumnNames="id"
                constraintName="fk_allocations_stock_batch_id"
                onDelete="RESTRICT"/>

        <createIndex tableName="stock_batch_allocations" indexName="idx_allocations_reservation_id">
            <column name="reservation_id"/>
        </createIndex>

        <createIndex tableName="stock_batch_allocations" indexName="idx_allocations_stock_batch_id">
            <column name="stock_batch_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
