<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
    default-lazy="false"
    auto-import="true"
    default-access="property"
    default-cascade="all"
    package="gov.va.med.esr.common.model.party">

    <class name="Address"
        table="ADDRESS">

        <id name="identifier"
            type="big_decimal"
            column="ADDRESS_ID">
            <generator class="gov.va.med.fw.persistent.hibernate.BigSequenceGenerator">
                <param name="sequence">transaction_s</param>
            </generator>
        </id>

		<version name="version" type="integer" column="RECORD_MODIFIED_COUNT"/>

		<many-to-one name="person" class="gov.va.med.esr.common.model.person.Person"
			cascade="none" insert="true" update="false">
           <column name="PERSON_ID" />
		</many-to-one>

		<many-to-one name="spouse" class="gov.va.med.esr.common.model.person.Spouse"
			cascade="none" insert="true" update="false">
           <column name="PERSON_RELATION_ID" />
		</many-to-one>

	    <many-to-one name="insurancePolicy" class="gov.va.med.esr.common.model.insurance.InsurancePolicy"
			cascade="none" insert="true" update="false">
           <column name="INSURANCE_ID"/>
	    </many-to-one>

        <many-to-one name="type" class="gov.va.med.esr.common.model.lookup.AddressType"
            outer-join="false" cascade="none"  column="STD_ADDRESSTYPE_ID" />

        <property name="internalLine1" type="string" column="ADDRESS_LINE1" length="40"/>
        <property name="internalLine2" type="string" column="ADDRESS_LINE2" length="40"/>
        <property name="internalLine3" type="string" column="ADDRESS_LINE3" length="40"/>
        <property name="internalCity"  type="string" column="CITY" length="50"/>
        <property name="internalCounty" type="string" column="COUNTY_CODE" length="50"/>
        <property name="internalState" type="string"  column="STATE_CODE" length="50"/>
		<property name="internalZipCode" type="string" column="ZIP_CODE" length="5"/>
        <property name="internalZipPlus4" type="string" column="ZIP_PLUS_4" length="4"/>
        <property name="internalPostalCode" type="string" column="POSTAL_CODE" length="20"/>
        <property name="internalProvince" type="string" column="PROVINCE_CODE" length="20"/>
        <property name="internalCountry" type="string" column="COUNTRY_CODE" length="50"/>
        <property name="changeDate" type="java.util.Date" column="ADDRESS_CHANGE_EFFECTIVE_DATE"/>

        <many-to-one
            name="countryObject"
            property-ref="code"
            class="gov.va.med.esr.common.model.lookup.Country"
            cascade="none"
            insert="false"
            update="false">
            <column name="COUNTRY_CODE" />
        </many-to-one>

        <many-to-one name="changeSource" class="gov.va.med.esr.common.model.lookup.AddressChangeSource"
            outer-join="false" cascade="none" column="ADDRESS_CHANGE_SRC_TYPE_ID" />

        <many-to-one name="changeSite" class="gov.va.med.esr.common.model.lookup.VAFacility"
            outer-join="false" cascade="none" column="STD_INSTITUTION_ID" />

        <many-to-one name="badAddressReason" class="gov.va.med.esr.common.model.lookup.BadAddressReason"
             outer-join="false" cascade="none" column="ADDRESS_INVALID_TYPE_ID" />

        <property name="dateCheckedByNCOA" type="date" column="NCOA_ADDRESS_CHECK_DATE"/>

        <property name="ncoaInvalidUndeliverableDate" type="date" column="ADDRESS_INVALIDATED_DATE"/>

        <many-to-one name="ncoaStatus" class="gov.va.med.esr.common.model.lookup.NcoaStatus"
            outer-join="false" cascade="none" column="NCOA_STATUS_TYPE_ID" />

        <many-to-one name="ncoaDeliveryCode" class="gov.va.med.esr.common.model.lookup.NcoaDeliveryCode"
            outer-join="false" cascade="none" column="NCOA_DELIVERY_TYPE_ID" />

        <many-to-one name="ncoaZipPlus4MatchCode" class="gov.va.med.esr.common.model.lookup.NcoaZipMatch"
            outer-join="false" cascade="none" column="NCOA_ZIP_MATCH_TYPE_ID" />

        <many-to-one name="zipPlus4NoMatchReason" class="gov.va.med.esr.common.model.lookup.ZipPlus4NoMatchReason"
            outer-join="false" cascade="none" column="NCOA_ZIP_NO_MATCH_TYPE_ID" />

        <property name="phoneNumber" type="string" column="ADDRESS_PHONE_NUMBER" length="30"/>

        <property name="startDate" column="ADDRESS_START_DATE_TXT"
            type="gov.va.med.esr.common.persistent.hibernate.CombinedImpreciseDateType">
        </property>

        <property name="endDate" column="ADDRESS_END_DATE_TXT"
            type="gov.va.med.esr.common.persistent.hibernate.CombinedImpreciseDateType">
        </property>

        <property name="createdOn" column="RECORD_CREATED_DATE" type="timestamp" not-null="true" update="false"/>
        <property name="modifiedOn" column="RECORD_MODIFIED_DATE" type="timestamp" not-null="true"/>
		<property name="createdBy" column="RECORD_CREATED_BY" update="false" type="gov.va.med.fw.persistent.hibernate.AuditUserType"/>
		<property name="modifiedBy" column="RECORD_MODIFIED_BY" type="gov.va.med.fw.persistent.hibernate.AuditUserType"/>
    </class>


<!-- this hand-written HQL is to retrieve list of PARTIAL addresses given a personId.
	it generates the following SQL:
SELECT
  address0_.ADDRESS_START_DATE_TEXT as   col_0_0_,
  address0_.ADDRESS_END_DATE_TEXT as col_1_0_,
  badaddress1_.ID as col_2_0_,
  address0_.STD_ADDRESSTYPE_ID as col_3_0_,
  address0_.ADDRESS_LINE1 as col_4_0_,
  address0_.ADDRESS_LINE2 as col_5_0_,
  address0_.ADDRESS_LINE3 as col_6_0_,
  address0_.CITY as col_7_0_,
  address0_.STATE_CODE as col_8_0_,
  address0_.PROVINCE_CODE as col_9_0_,
  address0_.POSTAL_CODE as col_10_0_,
  address0_.ZIP_CODE as col_11_0_,
  address0_.ZIP_PLUS_4 as col_12_0_,
  address0_.COUNTY_CODE as col_13_0_,
  address0_.COUNTRY_CODE as col_14_0_,
  address0_.ADDRESS_INVALIDATED_DATE as col_15_0_  ,
  badaddress1_.ID as ID78_0_,
  addresstyp2_.ID as ID78_1_,
  badaddress1_.CODE as CODE78_0_,
  badaddress1_.DESCRIPTION as DESCRIPT3_78_0_,
  badaddress1_.NAME as NAME79_0_,
  addresstyp2_.CODE as CODE78_1_,
  addresstyp2_.DESCRIPTION as DESCRIPT3_78_1_,
  addresstyp2_.NAME as NAME79_1_
FROM
  ADDRESS address0_ left outer join ADDRESS_INVALID_TYPE badaddress1_ on address0_.ADDRESS_INVALID_TYPE_ID=badaddress1_.ID   inner join STD_ADDRESSTYPE addresstyp2_ on address0_.STD_ADDRESSTYPE_ID=addresstyp2_.ID
WHERE
  address0_.PERSON_ID=84379
-->
	<query name ="addressesByPersonId">
    SELECT a.startDate ,a.endDate ,badAddrRsn,
		 a.type, a.internalLine1 ,a.internalLine2 ,a.internalLine3 ,a.internalCity ,a.internalState ,a.internalProvince ,a.internalPostalCode ,a.internalZipCode ,
		 a.internalZipPlus4 ,a.internalCounty ,a.internalCountry, a.ncoaInvalidUndeliverableDate
	FROM Address a left outer join a.badAddressReason as badAddrRsn
	WHERE a.person.identifier = :personId
	</query>

	<query name ="addressByAddressId">
    SELECT a.startDate ,a.endDate ,badAddrRsn,
		 a.type, a.internalLine1 ,a.internalLine2 ,a.internalLine3 ,a.internalCity ,a.internalState ,a.internalProvince ,a.internalPostalCode ,a.internalZipCode ,
		 a.internalZipPlus4 ,a.internalCounty ,a.internalCountry, a.ncoaInvalidUndeliverableDate
	FROM Address a left outer join a.badAddressReason as badAddrRsn
	WHERE a.identifier = :addressId
	</query>


<!--
	<resultset name ="personAddress">
        <return alias="addr" class="gov.va.med.esr.common.model.party.Address"/>
		<rturn-join alias="badAddrRsn" property="addr.badAddressReason"/>
	</resultset>

	<sql-query name="getAddressByPersonId" resultset-ref="personAddress">
		SELECT ADDRESS_START_DATE_TEXT AS {addr.startDate}, ADDRESS_END_DATE_TEXT AS {addr.endDate},
			ADDRESS_LINE1 AS {addr.line1} ,ADDRESS_LINE2 AS {addr.line2} ,ADDRESS_LINE3 AS {addr.line3}, CITY AS {addr.city}, PROVINCE_CODE AS {addr.province} ,POSTAL_CODE AS {addr.postalCode} ,ZIP_CODE AS {addr.zipCode} ,ZIP_PLUS_4 AS {addr.zipPlus4}, COUNTY_CODE AS {addr.county} , COUNTRY_CODE AS {addr.country}  ADDRESS_CHANGE_EFFECTIVE_DATE AS {addr.changeDate},
			badAddrType.NAME AS {badAddrRsn.name}, badAddrType.DESCRIPTION AS {badAddrRsn.description}, badAddrType.CODE AS {badAddrRsn.code},
		FROM ADDRESS a
		JOIN  ADDRESS_INVALID_TYPE badAddrType
			ON a.ADDRESS_INVALID_TYPE_ID = badAddrRsn.ID
	WHERE a.PERSON_ID = :personId
    </sql-query>
-->


</hibernate-mapping>