<?xml version="1.0" encoding="utf-8" ?>
<database Name="otrs">

<!-- Drop unneeded fields in the ticket table -->
<TableAlter Name="ticket">
    <ColumnDrop Name="group_read"/>
    <ColumnDrop Name="group_write"/>
    <ColumnDrop Name="other_read"/>
    <ColumnDrop Name="other_write"/>
    <IndexDrop Name="ticket_answered"/>
    <ColumnDrop Name="ticket_answered"/>
</TableAlter>

<!-- Drop unneeded indices in the article_flags table -->
<TableAlter Name="article_flag">
    <IndexDrop Name="article_flag_create_by"/>
    <IndexDrop Name="article_flag_article_id_article_key"/>
</TableAlter>

<!-- Drop unneeded field group_id from ticket table -->
<TableAlter Name="ticket">
    <!-- First drop index using it -->
    <IndexDrop Name="ticket_queue_view"/>

    <!-- Now drop column -->
    <ColumnDrop Name="group_id"/>

    <!-- Now recreate index without group_id column -->
    <IndexCreate Name="ticket_queue_view">
        <IndexColumn Name="ticket_state_id"/>
        <IndexColumn Name="ticket_lock_id"/>
    </IndexCreate>
</TableAlter>

<!--  Create new tables for Process Management -->
<TableCreate Name="pm_process">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- entity_id is a unique identifier independent from id -->
    <Column Name="entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <Column Name="state_entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <!-- layout stored as YAML -->
    <Column Name="layout" Required="true" Type="LONGBLOB"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="create_by" Required="true" Type="INTEGER"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Column Name="change_by" Required="true" Type="INTEGER"/>
    <Unique Name="pm_process_entity_id">
        <UniqueColumn Name="entity_id"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="pm_activity">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- entity_id is a unique identifier independent from id -->
    <Column Name="entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="create_by" Required="true" Type="INTEGER"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Column Name="change_by" Required="true" Type="INTEGER"/>
    <Unique Name="pm_activity_entity_id">
        <UniqueColumn Name="entity_id"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="pm_activity_dialog">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- entity_id is a unique identifier independent from id -->
    <Column Name="entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="create_by" Required="true" Type="INTEGER"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Column Name="change_by" Required="true" Type="INTEGER"/>
    <Unique Name="pm_activity_dialog_entity_id">
        <UniqueColumn Name="entity_id"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="pm_transition">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- entity_id is a unique identifier independent from id -->
    <Column Name="entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="create_by" Required="true" Type="INTEGER"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Column Name="change_by" Required="true" Type="INTEGER"/>
    <Unique Name="pm_transition_entity_id">
        <UniqueColumn Name="entity_id"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="pm_transition_action">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="INTEGER"/>
    <!-- entity_id is a unique identifier independent from id -->
    <Column Name="entity_id" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="name" Required="true" Type="VARCHAR" Size="200"/>
    <!-- config stored as YAML like in the web service configuration -->
    <Column Name="config" Required="true" Type="LONGBLOB"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="create_by" Required="true" Type="INTEGER"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Column Name="change_by" Required="true" Type="INTEGER"/>
    <Unique Name="pm_transition_action_entity_id">
        <UniqueColumn Name="entity_id"/>
    </Unique>
    <ForeignKey ForeignTable="users">
        <Reference Local="create_by" Foreign="id"/>
        <Reference Local="change_by" Foreign="id"/>
    </ForeignKey>
</TableCreate>

<TableCreate Name="pm_entity">
    <Column Name="entity_type" Required="true" Type="VARCHAR" Size="50"/>
    <Column Name="entity_counter" Required="true" Type="INTEGER"/>
</TableCreate>

<TableCreate Name="pm_entity_sync">
    <Column Name="entity_type" Required="true" Size="30" Type="VARCHAR"/>
    <Column Name="entity_id" Required="true" Size="50" Type="VARCHAR"/>
    <Column Name="sync_state" Required="true" Size="30" Type="VARCHAR"/>
    <Column Name="create_time" Required="true" Type="DATE"/>
    <Column Name="change_time" Required="true" Type="DATE"/>
    <Unique Name="pm_entity_sync_list">
        <UniqueColumn Name="entity_type"/>
        <UniqueColumn Name="entity_id"/>
    </Unique>
</TableCreate>

<TableAlter Name="dynamic_field">
    <ColumnAdd Name="internal_field" Required="true" Type="SMALLINT" Default="0"/>/>
</TableAlter>

<!-- Internal dynamic fields for ProcessManagement -->
<Insert Table="dynamic_field">
    <Data Key="internal_field">1</Data>
    <Data Key="name" Type="Quote">ProcessManagementProcessID</Data>
    <Data Key="label" Type="Quote">ProcessManagementProcessID</Data>
    <Data Key="field_order">1</Data>
    <Data Key="field_type" Type="Quote">Text</Data>
    <Data Key="object_type" Type="Quote">Ticket</Data>
    <Data Key="config" Type="Quote">---
DefaultValue: ''
</Data>
    <Data Key="valid_id">1</Data>
    <Data Key="create_by">1</Data>
    <Data Key="create_time">current_timestamp</Data>
    <Data Key="change_by">1</Data>
    <Data Key="change_time">current_timestamp</Data>
</Insert>
<Insert Table="dynamic_field">
    <Data Key="internal_field">1</Data>
    <Data Key="name" Type="Quote">ProcessManagementActivityID</Data>
    <Data Key="label" Type="Quote">ProcessManagementActivityID</Data>
    <Data Key="field_order">1</Data>
    <Data Key="field_type" Type="Quote">Text</Data>
    <Data Key="object_type" Type="Quote">Ticket</Data>
    <Data Key="config" Type="Quote">---
DefaultValue: ''
</Data>
    <Data Key="valid_id">1</Data>
    <Data Key="create_by">1</Data>
    <Data Key="create_time">current_timestamp</Data>
    <Data Key="change_by">1</Data>
    <Data Key="change_time">current_timestamp</Data>
</Insert>

<!-- Drop old sessions table and create the new one -->
<TableDrop Name="sessions"/>
<TableCreate Name="sessions">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="session_id" Required="true" Size="100" Type="VARCHAR"/>
    <Column Name="data_key" Required="true" Size="100" Type="VARCHAR"/>
    <Column Name="data_value" Required="false" Size="10000" Type="VARCHAR"/>
    <Column Name="serialized" Required="true" Type="SMALLINT"/>
    <Index Name="sessions_session_id_data_key">
        <IndexColumn Name="session_id"/>
        <IndexColumn Name="data_key"/>
    </Index>
    <Index Name="sessions_data_key">
        <IndexColumn Name="data_key"/>
    </Index>
</TableCreate>

</database>
