<?xml version="1.0" encoding="UTF-8" ?>
<!--

       Copyright ${license.git.copyrightYears} the original author or authors.

       Licensed under the Apache License, Version 2.0 (the "License");
       you may not use this file except in compliance with the License.
       You may obtain a copy of the License at

          http://www.apache.org/licenses/LICENSE-2.0

       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.

-->
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.apache.ibatis.submitted.column_prefix.MapperNestedQuery">

  <resultMap id="petMapper"
    type="org.apache.ibatis.submitted.column_prefix.Pet">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <association property="room" select="selectRoom" column="room_id" />
  </resultMap>

  <resultMap id="addressMapper"
    type="org.apache.ibatis.submitted.column_prefix.Address">
    <constructor>
      <idArg column="id" javaType="int" />
      <arg column="state" javaType="string" />
    </constructor>
    <result property="city" column="city" />
    <result property="hasPhone" column="has_phone" />
    <association property="stateBird" select="selectStateBird"
      column="state" />
    <association property="zip" select="selectZip"
      column="{state=state,city=city}" />
    <association property="phone1" select="selectPhone"
      column="phone1_id" />
    <association property="phone2" select="selectPhone"
      column="phone2_id" />
    <discriminator column="addr_type" javaType="int">
      <case value="1"
        resultType="org.apache.ibatis.submitted.column_prefix.AddressWithCaution">
        <result property="caution" column="caution" />
      </case>
    </discriminator>
  </resultMap>

  <resultMap id="personMapper"
    type="org.apache.ibatis.submitted.column_prefix.Person">
    <id property="id" column="person_id" />
    <result property="name" column="person_name" />
    <association property="room" select="selectRoom" column="person_room_id" />
    <association property="billingAddress" resultMap="addressMapper"
      notNullColumn="id" columnPrefix="b_" />
    <association property="shippingAddress" resultMap="addressMapper"
      notNullColumn="id" columnPrefix="s_" />
    <collection property="pets" resultMap="petMapper"
      columnPrefix="pet_" />
  </resultMap>

  <select id="selectPets" resultMap="petMapper"><![CDATA[
    select
      id,
      name,
      room_id
    from pet
    order by id
  ]]></select>

  <select id="selectPersons" resultMap="personMapper"><![CDATA[
    select
      person.id as person_id,
      person.name as person_name,
      person.room_id as person_room_id,
      billing_address.id as b_id,
      billing_address.state as b_state,
      billing_address.city as b_city,
      billing_address.phone1_id as b_phone1_id,
      billing_address.phone2_id as b_phone2_id,
      billing_address.addr_type as b_addr_type,
      billing_address.caution as b_caution,
      case
        when billing_address.phone1_id is null
          and billing_address.phone2_id is null
          then false
        else true
      end as b_has_phone,
      shipping_address.id as s_id,
      shipping_address.state as s_state,
      shipping_address.city as s_city,
      shipping_address.phone1_id as s_phone1_id,
      shipping_address.phone2_id as s_phone2_id,
      shipping_address.addr_type as s_addr_type,
      shipping_address.caution as s_caution,
      case
        when shipping_address.phone1_id is null
          and shipping_address.phone2_id is null
          then false
        else true
      end as s_has_phone,
      pet.id as pet_id,
      pet.name as pet_name,
      pet.room_id as pet_room_id
    from person
    left join address billing_address
      on billing_address.id = person.billing_address_id
    left join address shipping_address
      on shipping_address.id = person.shipping_address_id
    left join pet on pet.owner_id = person.id
    order by person.id, pet.id
  ]]></select>

  <select id="selectPhone" parameterType="int"
    resultType="org.apache.ibatis.submitted.column_prefix.Phone"><![CDATA[
    select
      id,
      phone,
      area_code
    from phone
    where id = #{id}
  ]]></select>

  <select id="selectRoom" resultType="org.apache.ibatis.submitted.column_prefix.Room"
    parameterType="int"><![CDATA[
    select
      room_id,
      room_name
    from room
    where room_id = #{id}
  ]]></select>

  <select id="selectZip" resultType="org.apache.ibatis.submitted.column_prefix.Zip"
    parameterType="org.apache.ibatis.submitted.column_prefix.Zip"><![CDATA[
    select
      state,
      city,
      zip_code
    from zip
    where state = #{state}
      and city = #{city}
  ]]></select>

  <select id="selectStateBird" resultType="string" parameterType="string"><![CDATA[
    select
      bird
    from state_bird
    where state = #{state}
  ]]></select>

</mapper>
