<?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="010-create-recipes-table" author="effigenix">
        <createTable tableName="recipes">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="version" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="type" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="description" type="VARCHAR(2000)"/>
            <column name="yield_percentage" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="shelf_life_days" type="INT"/>
            <column name="output_quantity" type="DECIMAL(19,6)">
                <constraints nullable="false"/>
            </column>
            <column name="output_uom" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)" defaultValue="DRAFT">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addUniqueConstraint tableName="recipes" columnNames="name, version"
                             constraintName="uq_recipe_name_version"/>

        <sql>
            ALTER TABLE recipes ADD CONSTRAINT chk_recipe_type CHECK (type IN ('RAW_MATERIAL', 'INTERMEDIATE', 'FINISHED_PRODUCT'));
            ALTER TABLE recipes ADD CONSTRAINT chk_recipe_status CHECK (status IN ('DRAFT', 'ACTIVE', 'ARCHIVED'));
            ALTER TABLE recipes ADD CONSTRAINT chk_recipe_yield CHECK (yield_percentage BETWEEN 1 AND 200);
        </sql>

        <createIndex tableName="recipes" indexName="idx_recipes_name_version">
            <column name="name"/>
            <column name="version"/>
        </createIndex>

        <createIndex tableName="recipes" indexName="idx_recipes_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
