<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mall.zone.mapper.ZoneMapper">
  <select id="findByZip" resultType="com.zioinfo.mall.zone.MallZone">
    SELECT * FROM mall_zone WHERE zip = #{zip} AND active = TRUE ORDER BY store_id
  </select>
  <select id="findByStore" resultType="com.zioinfo.mall.zone.MallZone">
    SELECT * FROM mall_zone WHERE store_id = #{storeId} ORDER BY zip
  </select>
  <select id="findAll" resultType="com.zioinfo.mall.zone.MallZone">SELECT * FROM mall_zone ORDER BY zip</select>
  <insert id="insert" parameterType="com.zioinfo.mall.zone.MallZone" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_zone (store_id, zip, city, state, radius_mi, surge_eligible, active)
    VALUES (#{storeId}, #{zip}, #{city}, #{state}, COALESCE(#{radiusMi},15.0), COALESCE(#{surgeEligible},TRUE), COALESCE(#{active},TRUE))
  </insert>
  <delete id="delete">DELETE FROM mall_zone WHERE id = #{id}</delete>

  <!-- 배송지역(Delivery Areas) — 도시 단위 당일배송 권역 -->
  <select id="findAllAreas" resultType="com.zioinfo.mall.zone.MallDeliveryArea">
    SELECT * FROM mall_delivery_area WHERE active = TRUE ORDER BY city
  </select>
  <select id="findAreaByZip" resultType="com.zioinfo.mall.zone.MallDeliveryArea">
    SELECT * FROM mall_delivery_area WHERE zip = #{zip} AND active = TRUE LIMIT 1
  </select>
  <select id="findAreaByCity" resultType="com.zioinfo.mall.zone.MallDeliveryArea">
    SELECT * FROM mall_delivery_area WHERE LOWER(city) = LOWER(#{city}) AND active = TRUE LIMIT 1
  </select>
</mapper>
