-- ============================================================
-- 设备使用记录历史数据回填（数据修复，非结构变更）
-- 生成日期：2026-11-06
--
-- 背景：旧版本自动生成使用记录时只写设备、样品和使用人，
--   没有写使用日期、起止时间和温湿度，导致使用记录页面这些列为空。
--   新版本在保存原始记录时会自动带出，但不会回头修改历史数据。
--
-- 回填口径（按客户确认的规则）：
--   1. 只处理 use_start_date 为空的历史记录，已手工填写的不动；
--   2. 通过 ins_order_template_device 找到该订单、该设备对应的
--      原始记录快照 ins_order_standard_template；
--   3. 同一设备在同一订单对应多个模板时，取检测日期最早的一条；
--   4. 使用开始日期 = 检测开始日期 10:00:00；
--      使用结束日期 = 检测结束日期 19:30:00；
--      没有检测结束日期时按开始日期当天 19:30:00；
--   5. 温度取原始记录快照温度，已有的不覆盖；
--   6. 原始记录没有湿度字段，湿度不回填；
--   7. 找不到原始记录设备关联的手工记录保持为空，由实验人员补填。
--
-- 数据来源均为库中已存在的原始记录快照，仅 10:00 / 19:30
-- 为按客户规则填入的默认时间。
--
-- 脚本可重复执行（幂等）。
-- ============================================================

-- 1. 备份使用记录表（首次执行创建，重复执行不覆盖）
CREATE TABLE IF NOT EXISTS `device_record_bak_20261106` AS
SELECT * FROM `device_record`;

-- 2. 回填前统计
-- 2.1 空记录总数
SELECT COUNT(*) AS blank_total
FROM device_record
WHERE use_start_date IS NULL;

-- 2.2 能匹配到原始记录条件的空记录数
SELECT COUNT(*) AS matchable_total
FROM device_record dr
WHERE dr.use_start_date IS NULL
  AND EXISTS (
      SELECT 1
      FROM ins_order_template_device t
      JOIN ins_order_standard_template s
        ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
      WHERE t.ins_order_id = dr.ins_order_id
        AND t.device_id = dr.device_id
        AND s.detection_time IS NOT NULL
  );

-- 2.3 匹配不到、将保持为空的记录（人工补填）
SELECT dr.id, dr.ins_order_id, dr.device_id, dr.sample_code
FROM device_record dr
WHERE dr.use_start_date IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM ins_order_template_device t
      JOIN ins_order_standard_template s
        ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
      WHERE t.ins_order_id = dr.ins_order_id
        AND t.device_id = dr.device_id
        AND s.detection_time IS NOT NULL
  );

-- 3. 回填
START TRANSACTION;

UPDATE device_record dr
JOIN (
    SELECT t.ins_order_id, t.device_id,
           s.temperature, s.detection_time, s.detection_end_time
    FROM ins_order_template_device t
    JOIN ins_order_standard_template s
      ON s.ins_order_id = t.ins_order_id AND s.template_id = t.template_id
    WHERE s.detection_time IS NOT NULL
      AND s.id = (
          SELECT s2.id
          FROM ins_order_template_device t2
          JOIN ins_order_standard_template s2
            ON s2.ins_order_id = t2.ins_order_id AND s2.template_id = t2.template_id
          WHERE t2.ins_order_id = t.ins_order_id
            AND t2.device_id = t.device_id
            AND s2.detection_time IS NOT NULL
          ORDER BY s2.detection_time ASC, s2.id ASC
          LIMIT 1
      )
) picked ON picked.ins_order_id = dr.ins_order_id AND picked.device_id = dr.device_id
SET dr.use_start_date = TIMESTAMP(picked.detection_time, '10:00:00'),
    dr.use_end_date = TIMESTAMP(COALESCE(picked.detection_end_time, picked.detection_time), '19:30:00'),
    dr.temperature = COALESCE(NULLIF(dr.temperature, ''), picked.temperature)
WHERE dr.use_start_date IS NULL;

COMMIT;

-- 4. 校验（执行完可人工核对）
-- 4.1 回填前后对比：备份表空格数 - 现表空格数 = 本次回填条数
-- SELECT
--     (SELECT COUNT(*) FROM device_record_bak_20261106 WHERE use_start_date IS NULL) AS blank_before,
--     (SELECT COUNT(*) FROM device_record WHERE use_start_date IS NULL) AS blank_after;
--
-- 4.2 抽查某订单的回填结果
-- SELECT id, device_id, ins_order_id, sample_code, temperature, use_start_date, use_end_date, use_person
-- FROM device_record
-- WHERE ins_order_id = ?;