<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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-3.5.xsd">
    <changeSet author="R3.Corda" id="create-external-id-to-state-party-view">
        <createTable tableName="state_party">
            <column name="output_index" type="INT"/>
            <column name="transaction_id" type="NVARCHAR(64)"/>
            <column name="public_key_hash" type="NVARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="x500_name" type="NVARCHAR(255)"/>
        </createTable>
        <createIndex indexName="state_pk_hash_idx" tableName="state_party">
            <column name="public_key_hash"/>
        </createIndex>
        <createTable tableName="pk_hash_to_ext_id_map">
            <column name="id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="external_id" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="public_key_hash" type="NVARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <createIndex indexName="pk_hash_to_xid_idx" tableName="pk_hash_to_ext_id_map">
            <column name="public_key_hash"/>
        </createIndex>
        <createView viewName="v_pkey_hash_ex_id_map">
            select
                state_party.public_key_hash,
                state_party.transaction_id,
                state_party.output_index,
                pk_hash_to_ext_id_map.external_id
            from state_party
            join pk_hash_to_ext_id_map
            on state_party.public_key_hash = pk_hash_to_ext_id_map.public_key_hash
        </createView>
    </changeSet>
</databaseChangeLog>
