-- 检查样品状态与工单的一致性
-- 用于诊断"删除工单后仍提示已有工单"的问题

-- 1. 查看样品状态分布
SELECT 
    '样品状态分布' AS 检查项,
    status AS 状态值,
    CASE 
        WHEN status = '0' THEN '待测试'
        WHEN status = '1' THEN '测试中'
        WHEN status = '2' THEN '已完成'
        ELSE '未知'
    END AS 状态名称,
    COUNT(*) AS 数量
FROM warehouse_sample
GROUP BY status
ORDER BY status;

-- 2. 查看工单数量
SELECT 
    '工单数量' AS 检查项,
    COUNT(*) AS 总数,
    COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS 未汇总数量,
    COUNT(CASE WHEN order_id IS NOT NULL THEN 1 END) AS 已汇总数量
FROM test_work_order;

-- 3. 查找状态为"测试中"但没有工单的样品（数据不一致）
SELECT 
    '不一致的样品' AS 检查项,
    s.sample_id AS 样品ID,
    s.sample_sn AS 样品SN,
    s.status AS 状态,
    CASE 
        WHEN s.status = '0' THEN '待测试'
        WHEN s.status = '1' THEN '测试中'
        WHEN s.status = '2' THEN '已完成'
        ELSE '未知'
    END AS 状态名称,
    COUNT(wo.id) AS 工单数量
FROM warehouse_sample s
LEFT JOIN test_eut te ON s.sample_sn = te.sn
LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
WHERE s.status != '0'  -- 非待测试状态
GROUP BY s.sample_id, s.sample_sn, s.status
HAVING COUNT(wo.id) = 0;

-- 4. 查看最近的样品和工单关联情况
SELECT 
    '最近样品工单关联' AS 检查项,
    s.sample_id AS 样品ID,
    s.sample_sn AS 样品SN,
    s.status AS 样品状态,
    te.id AS TestEutID,
    COUNT(wo.id) AS 工单数量,
    GROUP_CONCAT(wo.id) AS 工单ID列表
FROM warehouse_sample s
LEFT JOIN test_eut te ON s.sample_sn = te.sn
LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
GROUP BY s.sample_id, s.sample_sn, s.status, te.id
ORDER BY s.sample_id DESC
LIMIT 10;

-- 5. 统计结果
SELECT 
    '统计结果' AS 检查项,
    (SELECT COUNT(*) FROM warehouse_sample) AS 样品总数,
    (SELECT COUNT(*) FROM warehouse_sample WHERE status = '0') AS 待测试样品数,
    (SELECT COUNT(*) FROM warehouse_sample WHERE status = '1') AS 测试中样品数,
    (SELECT COUNT(*) FROM warehouse_sample WHERE status = '2') AS 已完成样品数,
    (SELECT COUNT(*) FROM test_work_order WHERE order_id IS NULL) AS 未汇总工单数,
    (SELECT COUNT(DISTINCT s.sample_id) 
     FROM warehouse_sample s
     LEFT JOIN test_eut te ON s.sample_sn = te.sn
     LEFT JOIN test_work_order wo ON te.id = wo.test_eut_id AND wo.order_id IS NULL
     WHERE s.status != '0' AND wo.id IS NULL) AS 不一致样品数;