<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qaup.system.mapper.SysVehicleInfoMapper">
    
    <resultMap type="SysVehicleInfo" id="SysVehicleInfoResult">
        <result property="vehicleId"       column="vehicle_id"        />
        <result property="licensePlate"    column="license_plate"     />
        <result property="vinNumber"       column="vin_number"        />
        <result property="typeCode"        column="type_code"         />
        <result property="typeName"        column="type_name"         />
        <result property="typeDisplayName" column="type_display_name" />
        <result property="brand"           column="brand"             />
        <result property="owningUnit"      column="owning_unit"       />
        <result property="contactPerson"   column="contact_person"    />
        <result property="phoneNumber"     column="phone_number"      />
        <result property="imageUrl"        column="image_url"         />
        <result property="createBy"        column="create_by"         />
        <result property="createTime"      column="create_time"       />
        <result property="updateBy"        column="update_by"         />
        <result property="updateTime"      column="update_time"       />
        <result property="remark"          column="remark"            />
    </resultMap>

    <!-- 基础查询SQL -->
    <sql id="selectSysVehicleInfoVo">
        select 
            vi.vehicle_id, vi.license_plate, vi.vin_number, vi.type_code,
            vi.brand, vi.owning_unit, vi.contact_person, vi.phone_number, vi.image_url, 
            vi.create_by, vi.create_time, vi.update_by, vi.update_time, vi.remark
        from sys_vehicle_info vi
    </sql>

    <!-- 关联查询SQL（包含车辆类型信息） -->
    <sql id="selectSysVehicleInfoWithTypeVo">
        select 
            vi.vehicle_id, vi.license_plate, vi.vin_number, vi.type_code,
            vi.brand, vi.owning_unit, vi.contact_person, vi.phone_number, vi.image_url, 
            vi.create_by, vi.create_time, vi.update_by, vi.update_time, vi.remark,
            vtn.type_name,
            vtn.display_name_cn as type_display_name
        from sys_vehicle_info vi
        left join sys_vehicle_type vtn on vi.type_code = vtn.type_code
    </sql>

    <select id="selectSysVehicleInfoList" parameterType="SysVehicleInfo" resultMap="SysVehicleInfoResult">
        <include refid="selectSysVehicleInfoWithTypeVo"/>
        <where>  
            <if test="vehicleId != null "> and vi.vehicle_id = #{vehicleId}</if>
            <if test="licensePlate != null  and licensePlate != ''"> and vi.license_plate = #{licensePlate}</if>
            <if test="typeCode != null  and typeCode != ''"> and vi.type_code = #{typeCode}</if>
            <if test="brand != null  and brand != ''"> and vi.brand = #{brand}</if>
            <if test="owningUnit != null  and owningUnit != ''"> and vi.owning_unit = #{owningUnit}</if>
            <if test="contactPerson != null  and contactPerson != ''"> and vi.contact_person = #{contactPerson}</if>
            <if test="phoneNumber != null  and phoneNumber != ''"> and vi.phone_number = #{phoneNumber}</if>
            <if test="imageUrl != null  and imageUrl != ''"> and vi.image_url = #{imageUrl}</if>
            <!-- 支持按车辆类型一级分类查询 -->
            <if test="typeName != null and typeName != ''">
                and (vtn.display_name_cn like concat('%', #{typeName}, '%') 
                     or vtn.type_name like concat('%', #{typeName}, '%'))
            </if>
        </where>
        order by vi.vehicle_id
    </select>
    
    <select id="selectSysVehicleInfoByVehicleId" parameterType="Long" resultMap="SysVehicleInfoResult">
        <include refid="selectSysVehicleInfoWithTypeVo"/>
        where vi.vehicle_id = #{vehicleId}
    </select>

    <insert id="insertSysVehicleInfo" parameterType="SysVehicleInfo" useGeneratedKeys="true" keyProperty="vehicleId">
        insert into sys_vehicle_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="licensePlate != null and licensePlate != ''">license_plate,</if>
            <if test="vinNumber != null">vin_number,</if>
            <if test="typeCode != null and typeCode != ''">type_code,</if>
            <if test="brand != null">brand,</if>
            <if test="owningUnit != null">owning_unit,</if>
            <if test="contactPerson != null">contact_person,</if>
            <if test="phoneNumber != null">phone_number,</if>
            <if test="imageUrl != null">image_url,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="licensePlate != null and licensePlate != ''">#{licensePlate},</if>
            <if test="vinNumber != null">#{vinNumber},</if>
            <if test="typeCode != null and typeCode != ''">#{typeCode},</if>
            <if test="brand != null">#{brand},</if>
            <if test="owningUnit != null">#{owningUnit},</if>
            <if test="contactPerson != null">#{contactPerson},</if>
            <if test="phoneNumber != null">#{phoneNumber},</if>
            <if test="imageUrl != null">#{imageUrl},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateSysVehicleInfo" parameterType="SysVehicleInfo">
        update sys_vehicle_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="licensePlate != null and licensePlate != ''">license_plate = #{licensePlate},</if>
            <if test="vinNumber != null">vin_number = #{vinNumber},</if>
            <if test="typeCode != null and typeCode != ''">type_code = #{typeCode},</if>
            <if test="brand != null">brand = #{brand},</if>
            <if test="owningUnit != null">owning_unit = #{owningUnit},</if>
            <if test="contactPerson != null">contact_person = #{contactPerson},</if>
            <if test="phoneNumber != null">phone_number = #{phoneNumber},</if>
            <if test="imageUrl != null">image_url = #{imageUrl},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where vehicle_id = #{vehicleId}
    </update>

    <delete id="deleteSysVehicleInfoByVehicleId" parameterType="Long">
        delete from sys_vehicle_info where vehicle_id = #{vehicleId}
    </delete>

    <delete id="deleteSysVehicleInfoByVehicleIds" parameterType="String">
        delete from sys_vehicle_info where vehicle_id in 
        <foreach item="vehicleId" collection="array" open="(" separator="," close=")">
            #{vehicleId}
        </foreach>
    </delete>

    <select id="selectExistingLicensePlates" parameterType="java.util.List" resultType="String">
        select distinct license_plate 
        from sys_vehicle_info 
        where license_plate in
        <foreach item="plateNumber" collection="list" open="(" separator="," close=")">
            #{plateNumber}
        </foreach>
    </select>
</mapper>