<?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="006-create-suppliers-table" author="effigenix">
        <createTable tableName="suppliers">
            <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="phone" type="VARCHAR(50)"/>
            <column name="email" type="VARCHAR(255)"/>
            <column name="contact_person" type="VARCHAR(200)"/>
            <column name="street" type="VARCHAR(200)"/>
            <column name="house_number" type="VARCHAR(20)"/>
            <column name="postal_code" type="VARCHAR(20)"/>
            <column name="city" type="VARCHAR(100)"/>
            <column name="country" type="VARCHAR(2)"/>
            <column name="payment_due_days" type="INT"/>
            <column name="payment_description" type="VARCHAR(500)"/>
            <column name="quality_score" type="INT"/>
            <column name="delivery_score" type="INT"/>
            <column name="price_score" type="INT"/>
            <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 suppliers ADD CONSTRAINT chk_supplier_status CHECK (status IN ('ACTIVE', 'INACTIVE'));
        </sql>
        <createIndex tableName="suppliers" indexName="idx_suppliers_name">
            <column name="name"/>
        </createIndex>
        <createIndex tableName="suppliers" indexName="idx_suppliers_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

    <changeSet id="006-create-quality-certificates-table" author="effigenix">
        <createTable tableName="quality_certificates">
            <column name="supplier_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="certificate_type" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="issuer" type="VARCHAR(200)"/>
            <column name="valid_from" type="DATE">
                <constraints nullable="false"/>
            </column>
            <column name="valid_until" type="DATE"/>
        </createTable>
        <addPrimaryKey tableName="quality_certificates"
                       columnNames="supplier_id, certificate_type, valid_from"
                       constraintName="pk_quality_certificates"/>
        <addForeignKeyConstraint baseTableName="quality_certificates" baseColumnNames="supplier_id"
                                 referencedTableName="suppliers" referencedColumnNames="id"
                                 constraintName="fk_quality_certificates_supplier" onDelete="CASCADE"/>
        <createIndex tableName="quality_certificates" indexName="idx_quality_certificates_supplier_id">
            <column name="supplier_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
