<?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="012-create-recipe-production-steps-table" author="effigenix">
        <createTable tableName="recipe_production_steps">
            <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="step_number" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="description" type="VARCHAR(500)">
                <constraints nullable="false"/>
            </column>
            <column name="duration_minutes" type="INT"/>
            <column name="temperature_celsius" type="INT"/>
        </createTable>

        <addForeignKeyConstraint baseTableName="recipe_production_steps" baseColumnNames="recipe_id"
                                 referencedTableName="recipes" referencedColumnNames="id"
                                 constraintName="fk_recipe_production_steps_recipe"
                                 onDelete="CASCADE"/>

        <addUniqueConstraint tableName="recipe_production_steps" columnNames="recipe_id, step_number"
                             constraintName="uq_recipe_step_number"/>

        <createIndex tableName="recipe_production_steps" indexName="idx_recipe_production_steps_recipe_id">
            <column name="recipe_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
