<?xml version="1.0" encoding="UTF-8" ?>
<!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.lookup">

    <class
        name="ZipCode"
        table="STD_ZIPCODE"
        mutable="false">
        <cache usage="read-only" />

        <id
            name="identifier"
            type="big_decimal"
            column="ID">
            <generator class="assigned"/>
        </id>

        <property
            name="code"
            type="string"
            not-null="true"
            insert="false"
            update="false"
            column="ZIPCODE"
            length="10"
            unique="true">
        </property>

        <property
            name="zipCode"
            type="string"
            not-null="true"
            insert="false"
            update="false"
            column="ZIPCODE"
            length="10"
            unique="true">
        </property>

        <many-to-one
            name="state"
            class="State"
            cascade="none"
            not-null="true"
            insert="false"
            update="false">
            <column name="STATE_ID" />
        </many-to-one>

        <many-to-one
            name="county"
            property-ref="countyNumberStateId"
            class="County"
            cascade="none"
            not-null="true"
            insert="false"
            update="false">
            <column name="COUNTYNUMBER" />
            <column name="STATE_ID" />
        </many-to-one>

        <many-to-one
            name="preferredCity"
            class="City"
            cascade="none"
            not-null="true"
            insert="false"
            update="false">
            <column name="PREFERREDZIPPLACE_ID" />
        </many-to-one>

        <set
            name="cities"
            table="STD_ZIPASSIGNMENT"
            lazy="false">
            <key column="ZIPCODE_ID" />
            <many-to-many class="City" column="ZIPPLACE_ID"/>
        </set>
    </class>

    <query name="zipCodeQuery_GetZipCodeByCityState">
        SELECT DISTINCT z.id, z.code
        FROM   ZipCode z
          LEFT JOIN z.state s
          LEFT JOIN z.cities ci
        WHERE  UPPER(s.postalName) = UPPER(:state)
          AND  ((UPPER(ci.name) = UPPER(:city)) OR
                (UPPER(ci.abbreviatedName) = UPPER(:city)))
		  AND ci.approvedMailingName = true
    </query>

    <query name="zipCodeQuery_GetZipCodeByCity">
        SELECT DISTINCT z.id, z.code
        FROM   ZipCode z
          LEFT JOIN z.cities ci
        WHERE  ((UPPER(ci.name) = UPPER(:city)) OR
                (UPPER(ci.abbreviatedName) = UPPER(:city)))
			AND ci.approvedMailingName = true
    </query>

    <query name="zipCodeQuery_GetPartialByCode">
        SELECT DISTINCT z.id, z.code, s.postalName, co.name, nvl(ci.abbreviatedName, ci.name)
        FROM   ZipCode z
          LEFT JOIN z.state s
          LEFT JOIN z.county co
          LEFT JOIN z.cities ci
        WHERE  z.code = :code 
			AND ci.approvedMailingName = true
    </query>

    <query name="zipCodeQuery_getFipsCode">
        SELECT s.fipsCode || c.countyNumber
        FROM   ZipCode z
        LEFT JOIN z.state s
        LEFT JOIN z.county c
        WHERE  z.code = :code
    </query>
</hibernate-mapping>