<?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="001-create-roles-table" author="effigenix">
        <createTable tableName="roles">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="VARCHAR(50)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="description" type="VARCHAR(500)"/>
        </createTable>
        <createIndex tableName="roles" indexName="idx_roles_name">
            <column name="name"/>
        </createIndex>
        <sql>
            ALTER TABLE roles ADD CONSTRAINT chk_role_name CHECK (name IN (
                'ADMIN', 'PRODUCTION_MANAGER', 'PRODUCTION_WORKER',
                'QUALITY_MANAGER', 'QUALITY_INSPECTOR', 'PROCUREMENT_MANAGER',
                'WAREHOUSE_WORKER', 'SALES_MANAGER', 'SALES_STAFF'
            ));
        </sql>
    </changeSet>

    <changeSet id="001-create-role-permissions-table" author="effigenix">
        <createTable tableName="role_permissions">
            <column name="role_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="permission" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey tableName="role_permissions" columnNames="role_id, permission"/>
        <addForeignKeyConstraint baseTableName="role_permissions" baseColumnNames="role_id"
                                 referencedTableName="roles" referencedColumnNames="id"
                                 constraintName="fk_role_permissions_role" onDelete="CASCADE"/>
        <createIndex tableName="role_permissions" indexName="idx_role_permissions_role_id">
            <column name="role_id"/>
        </createIndex>
    </changeSet>

    <changeSet id="001-create-users-table" author="effigenix">
        <createTable tableName="users">
            <column name="id" type="VARCHAR(36)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(100)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="password_hash" type="VARCHAR(60)">
                <constraints nullable="false"/>
            </column>
            <column name="branch_id" type="VARCHAR(36)"/>
            <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="last_login" type="TIMESTAMP"/>
        </createTable>
        <sql>
            ALTER TABLE users ADD CONSTRAINT chk_user_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'LOCKED'));
        </sql>
        <createIndex tableName="users" indexName="idx_users_username">
            <column name="username"/>
        </createIndex>
        <createIndex tableName="users" indexName="idx_users_email">
            <column name="email"/>
        </createIndex>
        <createIndex tableName="users" indexName="idx_users_branch_id">
            <column name="branch_id"/>
        </createIndex>
        <createIndex tableName="users" indexName="idx_users_status">
            <column name="status"/>
        </createIndex>
    </changeSet>

    <changeSet id="001-create-user-roles-table" author="effigenix">
        <createTable tableName="user_roles">
            <column name="user_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="role_id" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey tableName="user_roles" columnNames="user_id, role_id"/>
        <addForeignKeyConstraint baseTableName="user_roles" baseColumnNames="user_id"
                                 referencedTableName="users" referencedColumnNames="id"
                                 constraintName="fk_user_roles_user" onDelete="CASCADE"/>
        <addForeignKeyConstraint baseTableName="user_roles" baseColumnNames="role_id"
                                 referencedTableName="roles" referencedColumnNames="id"
                                 constraintName="fk_user_roles_role" onDelete="CASCADE"/>
        <createIndex tableName="user_roles" indexName="idx_user_roles_user_id">
            <column name="user_id"/>
        </createIndex>
        <createIndex tableName="user_roles" indexName="idx_user_roles_role_id">
            <column name="role_id"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>
