<?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="007-create-customers-table" author="effigenix">
        <createTable tableName="customers">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(200)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="type" type="VARCHAR(10)">
                <constraints nullable="false"/>
            </column>
            <column name="phone" type="VARCHAR(50)"/>
            <column name="email" type="VARCHAR(255)"/>
            <column name="contact_person" type="VARCHAR(200)"/>
            <column name="billing_street" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="billing_house_number" type="VARCHAR(20)"/>
            <column name="billing_postal_code" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="billing_city" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="billing_country" type="VARCHAR(2)">
                <constraints nullable="false"/>
            </column>
            <column name="payment_due_days" type="INT"/>
            <column name="payment_description" type="VARCHAR(500)"/>
            <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>
        <sql>
            ALTER TABLE customers ADD CONSTRAINT chk_customer_type CHECK (type IN ('B2C', 'B2B'));
            ALTER TABLE customers ADD CONSTRAINT chk_customer_status CHECK (status IN ('ACTIVE', 'INACTIVE'));
        </sql>
        <createIndex tableName="customers" indexName="idx_customers_name">
            <column name="name"/>
        </createIndex>
        <createIndex tableName="customers" indexName="idx_customers_type">
            <column name="type"/>
        </createIndex>
        <createIndex tableName="customers" indexName="idx_customers_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

    <changeSet id="007-create-delivery-addresses-table" author="effigenix">
        <createTable tableName="delivery_addresses">
            <column name="customer_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="label" type="VARCHAR(100)"/>
            <column name="street" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="house_number" type="VARCHAR(20)"/>
            <column name="postal_code" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="city" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="country" type="VARCHAR(2)">
                <constraints nullable="false"/>
            </column>
            <column name="contact_person" type="VARCHAR(200)"/>
            <column name="delivery_notes" type="VARCHAR(500)"/>
        </createTable>
        <addForeignKeyConstraint baseTableName="delivery_addresses" baseColumnNames="customer_id"
                                 referencedTableName="customers" referencedColumnNames="id"
                                 constraintName="fk_delivery_addresses_customer" onDelete="CASCADE"/>
        <createIndex tableName="delivery_addresses" indexName="idx_delivery_addresses_customer_id">
            <column name="customer_id"/>
        </createIndex>
    </changeSet>

    <changeSet id="007-create-customer-preferences-table" author="effigenix">
        <createTable tableName="customer_preferences">
            <column name="customer_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="preference" type="VARCHAR(30)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey tableName="customer_preferences" columnNames="customer_id, preference"/>
        <addForeignKeyConstraint baseTableName="customer_preferences" baseColumnNames="customer_id"
                                 referencedTableName="customers" referencedColumnNames="id"
                                 constraintName="fk_customer_preferences_customer" onDelete="CASCADE"/>
    </changeSet>

    <changeSet id="007-create-frame-contracts-table" author="effigenix">
        <createTable tableName="frame_contracts">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="customer_id" type="VARCHAR(36)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="valid_from" type="DATE"/>
            <column name="valid_until" type="DATE"/>
            <column name="delivery_rhythm" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint baseTableName="frame_contracts" baseColumnNames="customer_id"
                                 referencedTableName="customers" referencedColumnNames="id"
                                 constraintName="fk_frame_contracts_customer" onDelete="CASCADE"/>
        <createIndex tableName="frame_contracts" indexName="idx_frame_contracts_customer_id">
            <column name="customer_id"/>
        </createIndex>
    </changeSet>

    <changeSet id="007-create-contract-line-items-table" author="effigenix">
        <createTable tableName="contract_line_items">
            <column name="frame_contract_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="article_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="agreed_price_amount" type="DECIMAL(19,2)">
                <constraints nullable="false"/>
            </column>
            <column name="agreed_price_currency" type="VARCHAR(3)">
                <constraints nullable="false"/>
            </column>
            <column name="agreed_quantity" type="DECIMAL(19,4)"/>
            <column name="unit" type="VARCHAR(30)"/>
        </createTable>
        <addPrimaryKey tableName="contract_line_items" columnNames="frame_contract_id, article_id"/>
        <addForeignKeyConstraint baseTableName="contract_line_items" baseColumnNames="frame_contract_id"
                                 referencedTableName="frame_contracts" referencedColumnNames="id"
                                 constraintName="fk_contract_line_items_contract" onDelete="CASCADE"/>
        <createIndex tableName="contract_line_items" indexName="idx_contract_line_items_contract_id">
            <column name="frame_contract_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
