<?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-production-orders-table" author="effigenix">
        <createTable tableName="production_orders">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="recipe_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)" defaultValue="PLANNED">
                <constraints nullable="false"/>
            </column>
            <column name="planned_quantity_amount" type="DECIMAL(19,6)">
                <constraints nullable="false"/>
            </column>
            <column name="planned_quantity_unit" type="VARCHAR(10)">
                <constraints nullable="false"/>
            </column>
            <column name="planned_date" type="DATE">
                <constraints nullable="false"/>
            </column>
            <column name="priority" type="VARCHAR(10)">
                <constraints nullable="false"/>
            </column>
            <column name="notes" type="VARCHAR(1000)"/>
            <column name="created_at" type="TIMESTAMP WITH TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="TIMESTAMP WITH TIME ZONE">
                <constraints nullable="false"/>
            </column>
            <column name="version" type="BIGINT" defaultValueNumeric="0">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint
                baseTableName="production_orders"
                baseColumnNames="recipe_id"
                constraintName="fk_production_order_recipe"
                referencedTableName="recipes"
                referencedColumnNames="id"/>

        <createIndex tableName="production_orders" indexName="idx_production_order_recipe_id">
            <column name="recipe_id"/>
        </createIndex>

        <createIndex tableName="production_orders" indexName="idx_production_order_planned_date">
            <column name="planned_date"/>
        </createIndex>

        <sql>ALTER TABLE production_orders ADD CONSTRAINT chk_production_order_status CHECK (status IN ('PLANNED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED'));</sql>
    </changeSet>

</databaseChangeLog>
