<?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="009-create-storage-locations-table" author="effigenix">
        <createTable tableName="storage_locations">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(100)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="storage_type" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="min_temperature" type="DECIMAL(5,1)"/>
            <column name="max_temperature" type="DECIMAL(5,1)"/>
            <column name="active" type="BOOLEAN" defaultValueBoolean="true">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <sql>
            ALTER TABLE storage_locations ADD CONSTRAINT chk_storage_type CHECK (storage_type IN (
                'COLD_ROOM', 'FREEZER', 'DRY_STORAGE', 'DISPLAY_COUNTER', 'PRODUCTION_AREA'
            ));
        </sql>
        <createIndex tableName="storage_locations" indexName="idx_storage_locations_name">
            <column name="name"/>
        </createIndex>
        <createIndex tableName="storage_locations" indexName="idx_storage_locations_type">
            <column name="storage_type"/>
        </createIndex>
        <createIndex tableName="storage_locations" indexName="idx_storage_locations_active">
            <column name="active"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
