<?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.delivery.mapper.DeliveryMapper">
  <select id="findById" resultType="com.zioinfo.mall.delivery.MallDelivery">SELECT * FROM mall_delivery WHERE id = #{id}</select>
  <select id="findByOrderId" resultType="com.zioinfo.mall.delivery.MallDelivery">SELECT * FROM mall_delivery WHERE order_id = #{orderId} ORDER BY id DESC LIMIT 1</select>
  <select id="findAll" resultType="com.zioinfo.mall.delivery.MallDelivery">
    SELECT * FROM mall_delivery
    <where><if test="status != null and status != ''">status = #{status}</if></where>
    ORDER BY id DESC LIMIT #{limit}
  </select>
  <insert id="insert" parameterType="com.zioinfo.mall.delivery.MallDelivery" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_delivery (order_id, carrier, tracking_no, status, receiver_name, address, shipped_at)
    VALUES (#{orderId}, #{carrier}, #{trackingNo}, COALESCE(#{status},'READY'), #{receiverName}, #{address}, #{shippedAt})
  </insert>
  <update id="update">
    UPDATE mall_delivery SET carrier=#{carrier}, tracking_no=#{trackingNo}, status=COALESCE(#{status},status),
      receiver_name=#{receiverName}, address=#{address} WHERE id=#{id}
  </update>
  <update id="updateStatus">
    UPDATE mall_delivery SET status=#{status},
      shipped_at = CASE WHEN #{status}='IN_TRANSIT' AND shipped_at IS NULL THEN NOW() ELSE shipped_at END,
      delivered_at = CASE WHEN #{status}='DELIVERED' THEN NOW() ELSE delivered_at END
    WHERE id=#{id}
  </update>
</mapper>
