<?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="005-create-product-categories-table" author="effigenix">
        <createTable tableName="product_categories">
            <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="description" type="TEXT"/>
        </createTable>
        <createIndex tableName="product_categories" indexName="idx_product_categories_name">
            <column name="name"/>
        </createIndex>
    </changeSet>

    <changeSet id="005-create-articles-table" author="effigenix">
        <createTable tableName="articles">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="article_number" type="VARCHAR(50)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="category_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)" defaultValue="ACTIVE">
                <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>
        <addForeignKeyConstraint baseTableName="articles" baseColumnNames="category_id"
                                 referencedTableName="product_categories" referencedColumnNames="id"
                                 constraintName="fk_articles_category"/>
        <sql>
            ALTER TABLE articles ADD CONSTRAINT chk_article_status CHECK (status IN ('ACTIVE', 'INACTIVE'));
        </sql>
        <createIndex tableName="articles" indexName="idx_articles_article_number">
            <column name="article_number"/>
        </createIndex>
        <createIndex tableName="articles" indexName="idx_articles_category_id">
            <column name="category_id"/>
        </createIndex>
        <createIndex tableName="articles" indexName="idx_articles_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

    <changeSet id="005-create-sales-units-table" author="effigenix">
        <createTable tableName="sales_units">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="article_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="unit" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="price_model" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
            <column name="price_amount" type="DECIMAL(19,2)">
                <constraints nullable="false"/>
            </column>
            <column name="price_currency" type="VARCHAR(3)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint baseTableName="sales_units" baseColumnNames="article_id"
                                 referencedTableName="articles" referencedColumnNames="id"
                                 constraintName="fk_sales_units_article" onDelete="CASCADE"/>
        <createIndex tableName="sales_units" indexName="idx_sales_units_article_id">
            <column name="article_id"/>
        </createIndex>
    </changeSet>

    <changeSet id="005-create-article-suppliers-table" author="effigenix">
        <createTable tableName="article_suppliers">
            <column name="article_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="supplier_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey tableName="article_suppliers" columnNames="article_id, supplier_id"/>
        <addForeignKeyConstraint baseTableName="article_suppliers" baseColumnNames="article_id"
                                 referencedTableName="articles" referencedColumnNames="id"
                                 constraintName="fk_article_suppliers_article" onDelete="CASCADE"/>
        <createIndex tableName="article_suppliers" indexName="idx_article_suppliers_article_id">
            <column name="article_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
