-- ========================================
-- 修复GPS分段里程的任务关联
-- 用途：为已有的分段里程数据补充 task_id 和 task_code
-- 优化说明：补充task_id后，统计计算将使用优化方案（直接SQL聚合，更快）
-- ========================================

-- 1. 查看当前未关联任务的分段数量
SELECT COUNT(*) as '未关联任务的分段数' 
FROM tb_vehicle_gps_segment_mileage 
WHERE task_id IS NULL;

-- 2. 查看有多少任务可以被关联
SELECT COUNT(DISTINCT t.task_id) as '可关联的任务数'
FROM sys_task t
INNER JOIN sys_task_vehicle tv ON t.task_id = tv.task_id
WHERE t.del_flag = '0'
  AND t.task_status NOT IN ('PENDING', 'CANCELLED');

-- 3. 更新逻辑：根据车辆ID和时间重叠关联任务
-- 注意：这个查询会比较慢，建议分批执行或在非高峰期执行

UPDATE tb_vehicle_gps_segment_mileage seg
INNER JOIN (
    SELECT 
        seg2.segment_id,
        t.task_id,
        t.task_code
    FROM tb_vehicle_gps_segment_mileage seg2
    INNER JOIN sys_task_vehicle tv ON seg2.vehicle_id = tv.vehicle_id
    INNER JOIN sys_task t ON tv.task_id = t.task_id
    WHERE seg2.task_id IS NULL
      AND t.del_flag = '0'
      -- AND t.task_status NOT IN ('PENDING', 'CANCELLED')
      -- 时间重叠条件：分段开始时间 < 任务结束时间 AND 分段结束时间 > 任务开始时间
      AND seg2.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
      AND seg2.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
    GROUP BY seg2.segment_id, t.task_id, t.task_code
) task_match ON seg.segment_id = task_match.segment_id
SET 
    seg.task_id = task_match.task_id,
    seg.task_code = task_match.task_code;

-- 4. 查看修复结果
SELECT 
    '已关联任务' as '类型',
    COUNT(*) as '数量',
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '占比'
FROM tb_vehicle_gps_segment_mileage 
WHERE task_id IS NOT NULL
UNION ALL
SELECT 
    '未关联任务' as '类型',
    COUNT(*) as '数量',
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tb_vehicle_gps_segment_mileage), 2), '%') as '占比'
FROM tb_vehicle_gps_segment_mileage 
WHERE task_id IS NULL;

-- 5. 查看每个车辆的关联情况
SELECT 
    seg.vehicle_id,
    seg.vehicle_no,
    COUNT(*) as '总分段数',
    SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) as '已关联分段数',
    CONCAT(ROUND(SUM(CASE WHEN seg.task_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') as '关联率'
FROM tb_vehicle_gps_segment_mileage seg
GROUP BY seg.vehicle_id, seg.vehicle_no
ORDER BY COUNT(*) DESC
LIMIT 20;

-- 6. 分析未能关联的原因
-- 查看某个未关联分段的详细信息
SELECT 
    seg.segment_id,
    seg.vehicle_id,
    seg.vehicle_no,
    seg.segment_start_time,
    seg.segment_end_time,
    '该时段该车辆的任务' as '说明',
    t.task_id,
    t.task_code,
    t.task_status,
    t.create_time,
    COALESCE(t.actual_start_time, t.planned_start_time) as '任务开始时间',
    COALESCE(t.actual_end_time, t.planned_end_time) as '任务结束时间'
FROM tb_vehicle_gps_segment_mileage seg
LEFT JOIN sys_task_vehicle tv ON seg.vehicle_id = tv.vehicle_id
LEFT JOIN sys_task t ON tv.task_id = t.task_id
    AND t.del_flag = '0'
    AND seg.segment_start_time < COALESCE(t.actual_end_time, t.planned_end_time, DATE_ADD(t.create_time, INTERVAL 24 HOUR))
    AND seg.segment_end_time > COALESCE(t.actual_start_time, t.planned_start_time, t.create_time)
WHERE seg.task_id IS NULL
ORDER BY seg.segment_start_time DESC
LIMIT 10;

-- ========================================
-- 使用说明：
-- 1. 先执行查询语句（1、2）查看数据情况
-- 2. 在非高峰期执行更新语句（3）
-- 3. 执行结果查询（4、5、6）验证修复效果
-- 4. 如果数据量大，建议添加 LIMIT 分批执行
-- ========================================

-- ========================================
-- 预防措施：确保定时任务正常运行
-- ========================================

-- 7. 检查GPS分段里程计算定时任务状态
SELECT 
    job_id,
    job_name,
    job_group,
    invoke_target,
    cron_expression,
    status as '状态(0=正常,1=暂停)',
    create_time,
    update_time
FROM sys_job
WHERE job_name LIKE '%GPS%' OR job_name LIKE '%里程%'
ORDER BY create_time DESC;

-- 8. 如果定时任务是暂停状态，启动它
-- UPDATE sys_job SET status = '0' WHERE job_name = 'GPS分段里程实时计算';

-- 9. 检查最近的GPS计算记录
SELECT 
    vehicle_id,
    vehicle_no,
    MAX(segment_end_time) as '最后计算时间',
    COUNT(*) as '分段数',
    SUM(CASE WHEN task_id IS NOT NULL THEN 1 ELSE 0 END) as '有任务关联的分段数'
FROM tb_vehicle_gps_segment_mileage
WHERE segment_end_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY vehicle_id, vehicle_no
ORDER BY MAX(segment_end_time) DESC;