<?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.SysVehicleLocationMapper">
    
    <resultMap type="SysVehicleLocation" id="SysVehicleLocationResult">
        <result property="id"           column="id"           />
        <result property="vehicleId"    column="vehicle_id"   />
        <result property="licensePlate" column="license_plate" />
        <result property="vehicleType"  column="type_name" />
        <result property="brand"        column="brand"        />
        <result property="owningUnit"   column="owning_unit"  />
        <result property="location"     column="location"     />
        <result property="longitude"    column="longitude"    />
        <result property="latitude"     column="latitude"     />
        <result property="altitude"     column="altitude"     />
        <result property="speed"        column="speed"        />
        <result property="heading"      column="heading"      />
        <result property="dataQuality"  column="data_quality" />
        <result property="timestamp"    column="timestamp"    />
        <result property="createTime"   column="created_at"   />
        <result property="updateTime"   column="updated_at"   />
    </resultMap>

    <sql id="selectSysVehicleLocationVo">
        select 
            vl.id, 
            vl.vehicle_id, 
            vi.license_plate, 
            vt.type_name,
            vi.brand,
            vi.owning_unit,
            ST_AsText(vl.location) as location,
            ST_X(vl.location) as longitude,
            ST_Y(vl.location) as latitude,
            vl.altitude, 
            vl.speed, 
            vl.heading, 
            vl.data_quality, 
            vl.timestamp, 
            vl.created_at, 
            vl.updated_at 
        from vehicle_locations vl
        left join sys_vehicle_info vi on vl.vehicle_id = vi.vehicle_id
        left join sys_vehicle_type vt on vi.type_code = vt.type_code
    </sql>

    <select id="selectSysVehicleLocationList" parameterType="SysVehicleLocation" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        <where>  
            <if test="vehicleId != null "> and vl.vehicle_id = #{vehicleId}</if>
            <if test="licensePlate != null  and licensePlate != ''"> and vi.license_plate like concat('%', #{licensePlate}, '%')</if>
            <if test="vehicleType != null  and vehicleType != ''"> and vt.type_name = #{vehicleType}</if>
            <if test="brand != null  and brand != ''"> and vi.brand like concat('%', #{brand}, '%')</if>
            <if test="owningUnit != null  and owningUnit != ''"> and vi.owning_unit like concat('%', #{owningUnit}, '%')</if>
            <if test="dataQuality != null  and dataQuality != ''"> and vl.data_quality = #{dataQuality}</if>
            <if test="startTime != null and endTime != null "> and vl.timestamp between #{startTime} and #{endTime}</if>
            <if test="startTime != null and endTime == null "> and vl.timestamp &gt;= #{startTime}</if>
            <if test="startTime == null and endTime != null "> and vl.timestamp &lt;= #{endTime}</if>
        </where>
        order by vl.timestamp desc
    </select>
    
    <select id="selectSysVehicleLocationByVehicleId" parameterType="Long" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        where vl.vehicle_id = #{vehicleId}
        order by vl.timestamp desc
    </select>

    <select id="selectSysVehicleLocationByLicensePlate" parameterType="String" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        where vi.license_plate = #{licensePlate}
        order by vl.timestamp desc
    </select>

    <select id="selectLatestSysVehicleLocationByVehicleId" parameterType="Long" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        where vl.vehicle_id = #{vehicleId}
        order by vl.timestamp desc
        limit 1
    </select>

    <select id="selectLatestSysVehicleLocationByLicensePlate" parameterType="String" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        where vi.license_plate = #{licensePlate}
        order by vl.timestamp desc
        limit 1
    </select>

    <!-- 轨迹查询（支持采样优化） -->
    <select id="selectVehicleTrajectory" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        WHERE vl.vehicle_id = #{vehicleId}
        AND vl.timestamp BETWEEN #{startTime} AND #{endTime}
        <if test="simplified == true and maxPoints != null">
            AND vl.id IN (
                SELECT id FROM (
                    SELECT id, ROW_NUMBER() OVER (ORDER BY timestamp) as rn,
                           COUNT(*) OVER() as total_count
                    FROM vehicle_locations
                    WHERE vehicle_id = #{vehicleId}
                    AND timestamp BETWEEN #{startTime} AND #{endTime}
                ) t
                WHERE rn % GREATEST(1, total_count / #{maxPoints}) = 1
            )
        </if>
        ORDER BY vl.timestamp
    </select>

    <!-- 轨迹统计信息查询 -->
    <resultMap type="com.qaup.system.domain.vo.TrajectoryStatistics" id="TrajectoryStatisticsResult">
        <result property="pointCount"        column="point_count"        />
        <result property="firstPointTime"    column="first_point_time"   />
        <result property="lastPointTime"     column="last_point_time"    />
        <result property="averageSpeed"      column="average_speed"      />
        <result property="maxSpeed"          column="max_speed"          />
        <result property="minSpeed"          column="min_speed"          />
        <result property="durationSeconds"   column="duration_seconds"   />
        <result property="totalDistance"     column="total_distance"     />
        <result property="maxAltitude"       column="max_altitude"       />
        <result property="minAltitude"       column="min_altitude"       />
        <result property="averageAltitude"   column="average_altitude"   />
    </resultMap>

    <select id="selectTrajectoryStatistics" resultMap="TrajectoryStatisticsResult">
        SELECT
            COUNT(*) as point_count,
            MIN(timestamp) as first_point_time,
            MAX(timestamp) as last_point_time,
            AVG(speed) as average_speed,
            MAX(speed) as max_speed,
            MIN(speed) as min_speed,
            EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp))) as duration_seconds,
            MAX(altitude) as max_altitude,
            MIN(altitude) as min_altitude,
            AVG(altitude) as average_altitude,
            -- 使用PostGIS计算总距离
            COALESCE(
                ST_Length(
                    ST_MakeLine(
                        location ORDER BY timestamp
                    )::geography
                ) / 1000, 0
            ) as total_distance
        FROM vehicle_locations
        WHERE vehicle_id = #{vehicleId}
        AND timestamp BETWEEN #{startTime} AND #{endTime}
    </select>

    <!-- 轨迹回放数据查询 -->
    <select id="selectTrajectoryPlaybackData" resultMap="SysVehicleLocationResult">
        WITH trajectory_with_intervals AS (
            SELECT vl.*,
                   ROW_NUMBER() OVER (ORDER BY vl.timestamp) as seq_num,
                   EXTRACT(EPOCH FROM (vl.timestamp - LAG(vl.timestamp) OVER (ORDER BY vl.timestamp))) as interval_seconds
            FROM vehicle_locations vl
            WHERE vl.vehicle_id = #{vehicleId}
            AND vl.timestamp BETWEEN #{startTime} AND #{endTime}
        ),
        sampled_trajectory AS (
            SELECT * FROM trajectory_with_intervals
            WHERE seq_num = 1
            OR interval_seconds >= #{intervalSeconds}
            OR seq_num = (SELECT MAX(seq_num) FROM trajectory_with_intervals)
        )
        SELECT
            st.id,
            st.vehicle_id,
            vi.license_plate,
            vt.type_name,
            vi.brand,
            vi.owning_unit,
            ST_AsText(st.location) as location,
            ST_X(st.location) as longitude,
            ST_Y(st.location) as latitude,
            st.altitude,
            st.speed,
            st.heading,
            st.data_quality,
            st.timestamp,
            st.created_at,
            st.updated_at
        FROM sampled_trajectory st
        LEFT JOIN sys_vehicle_info vi ON st.vehicle_id = vi.vehicle_id
        LEFT JOIN sys_vehicle_type vt ON vi.type_code = vt.type_code
        ORDER BY st.timestamp
    </select>

    <!-- 区域内轨迹查询 -->
    <select id="selectVehicleTrajectoryInArea" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        WHERE vl.vehicle_id = #{vehicleId}
        AND vl.timestamp BETWEEN #{startTime} AND #{endTime}
        AND ST_Contains(ST_GeomFromText(#{areaWkt}, 4326), vl.location)
        ORDER BY vl.timestamp
    </select>

    <!-- 批量车辆轨迹查询 -->
    <select id="selectBatchVehicleTrajectory" resultMap="SysVehicleLocationResult">
        <include refid="selectSysVehicleLocationVo"/>
        WHERE vl.vehicle_id IN
        <foreach item="vehicleId" collection="vehicleIds" open="(" separator="," close=")">
            #{vehicleId}
        </foreach>
        AND vl.timestamp BETWEEN #{startTime} AND #{endTime}
        <if test="simplified == true and maxPoints != null">
            AND vl.id IN (
                SELECT id FROM (
                    SELECT id, ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY timestamp) as rn,
                           COUNT(*) OVER(PARTITION BY vehicle_id) as total_count
                    FROM vehicle_locations
                    WHERE vehicle_id IN
                    <foreach item="vehicleId" collection="vehicleIds" open="(" separator="," close=")">
                        #{vehicleId}
                    </foreach>
                    AND timestamp BETWEEN #{startTime} AND #{endTime}
                ) t
                WHERE rn % GREATEST(1, total_count / #{maxPoints}) = 1
            )
        </if>
        ORDER BY vl.vehicle_id, vl.timestamp
    </select>

</mapper>