<?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-2.0.xsd">

    <property name="now" value="SYSDATE" dbms="oracle"/>
   	<property name="now" value="NOW()" dbms="mysql"/>
   	<property name="now" value="NOW()" dbms="hsqldb"/>


    <!-- Add Changesets here. -->
    <!--Have to delete and recreate all the appointment request WHERE appointment_request_id LIKE VAMC% due to invalid hex number error for id on oracle DB on test and demo-->
    <changeSet id="delete-var-1" author="hetal.giri" context="test" runAlways="true" runOnChange="true">
        <delete tableName="BEST_TIME_TO_CALL">
            <where>APPOINTMENT_REQUEST_ID IN ('VAMC3AR000001', 'VAMC3AR000002', 'VAMC3AR000003', 'VAMC3AR000004', 'VAMC3AR000005')</where>
        </delete>
        <delete tableName="APPOINTMENT_REQUEST_MESSAGE">
            <where>APPOINTMENT_REQUEST_ID IN ('VAMC3AR000001', 'VAMC3AR000002', 'VAMC3AR000003', 'VAMC3AR000004', 'VAMC3AR000005')</where>
        </delete>
        <delete tableName="AR_DETAIL_CODE">
            <where>APPOINTMENT_REQUEST_ID IN ('VAMC3AR000001', 'VAMC3AR000002', 'VAMC3AR000003', 'VAMC3AR000004', 'VAMC3AR000005')</where>
        </delete>
        <delete tableName="APPOINTMENT_REQUEST">
            <where>APPOINTMENT_REQUEST_ID IN ('VAMC3AR000001', 'VAMC3AR000002', 'VAMC3AR000003', 'VAMC3AR000004', 'VAMC3AR000005')</where>
        </delete>
    </changeSet>

    <changeSet id="delete-var-2" author="hetal.giri" context="test" runAlways="true" runOnChange="true">
        <delete tableName="BEST_TIME_TO_CALL">
            <where>APPOINTMENT_REQUEST_ID IN ('00000000000000000000000000000013', '00000000000000000000000000000014', '00000000000000000000000000000015', '00000000000000000000000000000016', '00000000000000000000000000000017')</where>
        </delete>
        <delete tableName="APPOINTMENT_REQUEST_MESSAGE">
            <where>APPOINTMENT_REQUEST_ID IN ('00000000000000000000000000000013', '00000000000000000000000000000014', '00000000000000000000000000000015', '00000000000000000000000000000016', '00000000000000000000000000000017')</where>
        </delete>
        <delete tableName="AR_DETAIL_CODE">
            <where>APPOINTMENT_REQUEST_ID IN ('00000000000000000000000000000013', '00000000000000000000000000000014', '00000000000000000000000000000015', '00000000000000000000000000000016', '00000000000000000000000000000017')</where>
        </delete>
        <delete tableName="APPOINTMENT_REQUEST">
            <where>APPOINTMENT_REQUEST_ID IN ('00000000000000000000000000000013', '00000000000000000000000000000014', '00000000000000000000000000000015', '00000000000000000000000000000016', '00000000000000000000000000000017')</where>
        </delete>
    </changeSet>

    <changeSet id="insert-var-1" author="hetal.giri" context="test" runAlways="true">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">select count(*) from ${hadbSchema}.APPOINTMENT_REQUEST where APPOINTMENT_REQUEST_ID='00000000000000000000000000000013'</sqlCheck>
        </preConditions>
        <insert tableName="APPOINTMENT_REQUEST">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000013"/>
            <column name="PATIENT_ID" value="P001"/>
            <column name="LAST_UPDATED_DATE" valueComputed="${now}"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="ACTIVE" value="1"/>
            <column name="SECOND_REQUEST" value="0"/>
            <column name="APPOINTMENT_DATE" value=""/>
            <column name="APPOINTMENT_TIME" value=""/>
            <column name="OPTION_DATE_1" value="12/27/2013"/>
            <column name="OPTION_TIME_1" value="AM"/>
            <column name="OPTION_DATE_2" value="12/28/2013"/>
            <column name="OPTION_TIME_2" value="AM"/>
            <column name="OPTION_DATE_3" value="12/29/2013"/>
            <column name="OPTION_TIME_3" value="AM"/>
            <column name="STATUS" value="Submitted"/>
            <column name="APPOINTMENT_TYPE" value="Primary Care"/>
            <column name="FACILITY_CODE" value="777"/>
            <column name="EMAIL" value="test@test.com"/>
            <column name="PHONE_NUMBER" value="123-456-7890"/>
            <column name="TEXT_MESSAGING_ALLOWED" value="0"/>
            <column name="TEXT_MESSAGING_PHONE_NUMBER" value=""/>
            <column name="PURPOSE_OF_VISIT" value="Routine follow-up"/>
            <column name="OTHER_PURPOSE_OF_VISIT" value=""/>
            <column name="VISIT_TYPE" value="Office Visit"/>
            <column name="PROVIDER_ID" value="PROV1"/>
            <column name="PROVIDER_NAME" value="zztest appointment_provider1"/>
            <column name="PROVIDER_PERSON_CLASS" value=""/>
            <column name="SECOND_REQUEST_SUBMITTED" value="0"/>
        </insert>
        <insert tableName="BEST_TIME_TO_CALL">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000013"/>
            <column name="BEST_TIME" value="9 AM - 11 AM"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="0000013"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000013"/>
            <column name="SENDER_ID" value="staffscv"/>
            <column name="MESSAGE_TEXT" value="Office closed due to weather."/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
    </changeSet>

    <changeSet id="insert-var-2" author="hetal.giri" context="test" runAlways="true">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">select count(*) from ${hadbSchema}.APPOINTMENT_REQUEST where APPOINTMENT_REQUEST_ID='00000000000000000000000000000014'</sqlCheck>
        </preConditions>
        <insert tableName="APPOINTMENT_REQUEST">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000014"/>
            <column name="PATIENT_ID" value="P001"/>
            <column name="LAST_UPDATED_DATE" valueComputed="${now}"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="ACTIVE" value="1"/>
            <column name="SECOND_REQUEST" value="0"/>
            <column name="APPOINTMENT_DATE" value="12/27/2013"/>
            <column name="APPOINTMENT_TIME" value="AM"/>
            <column name="OPTION_DATE_1" value="12/27/2013"/>
            <column name="OPTION_TIME_1" value="AM"/>
            <column name="OPTION_DATE_2" value="12/28/2013"/>
            <column name="OPTION_TIME_2" value="AM"/>
            <column name="OPTION_DATE_3" value="12/29/2013"/>
            <column name="OPTION_TIME_3" value="AM"/>
            <column name="STATUS" value="Booked"/>
            <column name="APPOINTMENT_TYPE" value="Primary Care"/>
            <column name="FACILITY_CODE" value="777"/>
            <column name="EMAIL" value="test@test.com"/>
            <column name="PHONE_NUMBER" value="123-456-7890"/>
            <column name="TEXT_MESSAGING_ALLOWED" value="0"/>
            <column name="TEXT_MESSAGING_PHONE_NUMBER" value=""/>
            <column name="PURPOSE_OF_VISIT" value="Routine follow-up"/>
            <column name="OTHER_PURPOSE_OF_VISIT" value=""/>
            <column name="VISIT_TYPE" value="Office Visit"/>
            <column name="PROVIDER_ID" value="PROV1"/>
            <column name="PROVIDER_NAME" value="zztest.appointment_provider1"/>
            <column name="PROVIDER_PERSON_CLASS" value=""/>
            <column name="SECOND_REQUEST_SUBMITTED" value="0"/>
            <column name="HAS_VETERAN_NEW_MESSAGE" value="0"/>
            <column name="HAS_PROVIDER_NEW_MESSAGE" value="1"/>
        </insert>
        <insert tableName="AR_DETAIL_CODE">
            <column name="AR_DETAIL_CODE_ID" value="0000014"/>
            <column name="DETAIL_CODE_ID" value="DETCODE1"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000014"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="USER_ID" value="zztest.appointment_provider1"/>
        </insert>
        <insert tableName="BEST_TIME_TO_CALL">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000014"/>
            <column name="BEST_TIME" value="9 AM - 11 AM"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="0000014"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000014"/>
            <column name="SENDER_ID" value="237"/>
            <column name="MESSAGE_TEXT" value="Please call me before fixing the appt."/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
    </changeSet>

    <changeSet id="insert-var-3" author="hetal.giri" context="test" runAlways="true">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">select count(*) from ${hadbSchema}.APPOINTMENT_REQUEST where APPOINTMENT_REQUEST_ID='00000000000000000000000000000015'</sqlCheck>
        </preConditions>
        <insert tableName="APPOINTMENT_REQUEST">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="PATIENT_ID" value="P001"/>
            <column name="LAST_UPDATED_DATE" valueComputed="${now}"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="ACTIVE" value="1"/>
            <column name="SECOND_REQUEST" value="0"/>
            <column name="APPOINTMENT_DATE" value="01/27/2014"/>
            <column name="APPOINTMENT_TIME" value="AM"/>
            <column name="OPTION_DATE_1" value="01/27/2014"/>
            <column name="OPTION_TIME_1" value="AM"/>
            <column name="OPTION_DATE_2" value="01/28/2014"/>
            <column name="OPTION_TIME_2" value="AM"/>
            <column name="OPTION_DATE_3" value="01/29/2014"/>
            <column name="OPTION_TIME_3" value="AM"/>
            <column name="STATUS" value="Not Booked"/>
            <column name="APPOINTMENT_TYPE" value="Primary Care"/>
            <column name="FACILITY_CODE" value="777"/>
            <column name="EMAIL" value="test@test.com"/>
            <column name="PHONE_NUMBER" value="123-456-7890"/>
            <column name="TEXT_MESSAGING_ALLOWED" value="0"/>
            <column name="TEXT_MESSAGING_PHONE_NUMBER" value=""/>
            <column name="PURPOSE_OF_VISIT" value="Routine follow-up"/>
            <column name="OTHER_PURPOSE_OF_VISIT" value=""/>
            <column name="VISIT_TYPE" value="Office Visit"/>
            <column name="PROVIDER_ID" value="PROV1"/>
            <column name="PROVIDER_NAME" value="zztest.appointment_provider1"/>
            <column name="PROVIDER_PERSON_CLASS" value=""/>
            <column name="SECOND_REQUEST_SUBMITTED" value="0"/>
        </insert>
        <insert tableName="AR_DETAIL_CODE">
            <column name="AR_DETAIL_CODE_ID" value="0000015"/>
            <column name="DETAIL_CODE_ID" value="DETCODE4"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="USER_ID" value="zztest.appointment_provider1"/>
        </insert>
        <insert tableName="BEST_TIME_TO_CALL">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="BEST_TIME" value="9 AM - 11 AM"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="10000015"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="SENDER_ID" value="237"/>
            <column name="MESSAGE_TEXT" value="Msg1-Please cancel appt."/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="20000015"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="SENDER_ID" value="staffscv"/>
            <column name="MESSAGE_TEXT" value="Cancelled."/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="30000015"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000015"/>
            <column name="SENDER_ID" value="777"/>
            <column name="MESSAGE_TEXT" value="Thank You."/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
    </changeSet>

    <changeSet id="insert-var-4" author="hetal.giri" context="test" runAlways="true">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">select count(*) from ${hadbSchema}.APPOINTMENT_REQUEST where APPOINTMENT_REQUEST_ID='00000000000000000000000000000016'</sqlCheck>
        </preConditions>
        <insert tableName="APPOINTMENT_REQUEST">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000016"/>
            <column name="PATIENT_ID" value="P001"/>
            <column name="LAST_UPDATED_DATE" valueComputed="${now}"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="ACTIVE" value="1"/>
            <column name="SECOND_REQUEST" value="0"/>
            <column name="APPOINTMENT_DATE" value=""/>
            <column name="APPOINTMENT_TIME" value=""/>
            <column name="OPTION_DATE_1" value="01/27/2014"/>
            <column name="OPTION_TIME_1" value="AM"/>
            <column name="OPTION_DATE_2" value="01/28/2014"/>
            <column name="OPTION_TIME_2" value="AM"/>
            <column name="OPTION_DATE_3" value="01/29/2014"/>
            <column name="OPTION_TIME_3" value="AM"/>
            <column name="STATUS" value="Submitted"/>
            <column name="APPOINTMENT_TYPE" value="Mental Health"/>
            <column name="FACILITY_CODE" value="777"/>
            <column name="EMAIL" value="test@test.com"/>
            <column name="PHONE_NUMBER" value="123-456-7890"/>
            <column name="TEXT_MESSAGING_ALLOWED" value="0"/>
            <column name="TEXT_MESSAGING_PHONE_NUMBER" value=""/>
            <column name="PURPOSE_OF_VISIT" value="New issue"/>
            <column name="OTHER_PURPOSE_OF_VISIT" value=""/>
            <column name="VISIT_TYPE" value="Office Visit"/>
            <column name="PROVIDER_ID" value="zztest appointment_provider4"/>
            <column name="PROVIDER_NAME" value="Tom Cage"/>
            <column name="PROVIDER_PERSON_CLASS" value=""/>
            <column name="SECOND_REQUEST_SUBMITTED" value="0"/>
            <column name="HAS_VETERAN_NEW_MESSAGE" value="1"/>
            <column name="HAS_PROVIDER_NEW_MESSAGE" value="0"/>
        </insert>
        <insert tableName="BEST_TIME_TO_CALL">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000016"/>
            <column name="BEST_TIME" value="9 AM - 11 AM"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="10000016"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000016"/>
            <column name="SENDER_ID" value="staffscv"/>
            <column name="MESSAGE_TEXT" value="Prefered provider unavailable"/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="20000016"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000016"/>
            <column name="SENDER_ID" value="237"/>
            <column name="MESSAGE_TEXT" value="Fix with available provider"/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
        <insert tableName="APPOINTMENT_REQUEST_MESSAGE">
            <column name="ID" value="30000016"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000016"/>
            <column name="SENDER_ID" value="staffscv"/>
            <column name="MESSAGE_TEXT" value="Done"/>
            <column name="MESSAGE_DATE_TIME" valueComputed="${now}"/>
        </insert>
    </changeSet>
    <changeSet id="insert-var-5" author="hetal.giri" context="test" runAlways="true">
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">select count(*) from ${hadbSchema}.APPOINTMENT_REQUEST where APPOINTMENT_REQUEST_ID='00000000000000000000000000000017'</sqlCheck>
        </preConditions>
        <insert tableName="APPOINTMENT_REQUEST">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000017"/>
            <column name="PATIENT_ID" value="P001"/>
            <column name="LAST_UPDATED_DATE" valueComputed="${now}"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="ACTIVE" value="1"/>
            <column name="SECOND_REQUEST" value="0"/>
            <column name="APPOINTMENT_DATE" value=""/>
            <column name="APPOINTMENT_TIME" value=""/>
            <column name="OPTION_DATE_1" value="12/27/2013"/>
            <column name="OPTION_TIME_1" value="AM"/>
            <column name="OPTION_DATE_2" value="12/28/2013"/>
            <column name="OPTION_TIME_2" value="AM"/>
            <column name="OPTION_DATE_3" value="12/29/2013"/>
            <column name="OPTION_TIME_3" value="AM"/>
            <column name="STATUS" value="Cancelled"/>
            <column name="APPOINTMENT_TYPE" value="Mental Health"/>
            <column name="FACILITY_CODE" value="777"/>
            <column name="EMAIL" value="test@test.com"/>
            <column name="PHONE_NUMBER" value="123-456-7890"/>
            <column name="TEXT_MESSAGING_ALLOWED" value="0"/>
            <column name="TEXT_MESSAGING_PHONE_NUMBER" value=""/>
            <column name="PURPOSE_OF_VISIT" value="New issue"/>
            <column name="OTHER_PURPOSE_OF_VISIT" value=""/>
            <column name="VISIT_TYPE" value="Office Visit"/>
            <column name="PROVIDER_ID" value="PROV4"/>
            <column name="PROVIDER_NAME" value="zztest appointment_provider4"/>
            <column name="PROVIDER_PERSON_CLASS" value=""/>
            <column name="SECOND_REQUEST_SUBMITTED" value="0"/>
        </insert>
        <insert tableName="BEST_TIME_TO_CALL">
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000017"/>
            <column name="BEST_TIME" value="9 AM - 11 AM"/>
        </insert>
        <insert tableName="AR_DETAIL_CODE">
            <column name="AR_DETAIL_CODE_ID" value="10000017"/>
            <column name="DETAIL_CODE_ID" value="DETCODE7"/>
            <column name="APPOINTMENT_REQUEST_ID" value="00000000000000000000000000000017"/>
            <column name="CREATED_DATE" valueComputed="${now}"/>
            <column name="USER_ID" value="zztest appointment_provider4"/>
        </insert>
    </changeSet>
    
    <changeSet id="add-column-1" author="hetal.giri">
        <comment>Adding Requested Phone call column</comment>
        <addColumn tableName="APPOINTMENT_REQUEST">
            <column name="REQUESTED_PHONE_CALL" type="boolean" defaultValueBoolean="false"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>