-- 检查各个测试步骤的工单数量
-- 用于诊断"测试中"页面为什么是空的

-- 1. 统计各个测试步骤的工单数量
SELECT 
    '1. 各测试步骤工单统计' AS step;
SELECT 
    test_step,
    CASE 
        WHEN test_step = 0 THEN '待领取'
        WHEN test_step = 1 THEN '测试中'
        WHEN test_step = 2 THEN '一审中'
        WHEN test_step = 3 THEN '二审中'
        WHEN test_step = 4 THEN '三审中'
        WHEN test_step = 5 THEN '已完成'
        ELSE '未知状态'
    END AS step_name,
    COUNT(*) AS count,
    COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS unlinked_count,
    COUNT(CASE WHEN order_id IS NOT NULL THEN 1 END) AS linked_count
FROM test_work_order
GROUP BY test_step
ORDER BY test_step;

-- 2. 查看所有未关联订单的工单（应该显示在列表中的）
SELECT 
    '2. 所有未关联订单的工单' AS step;
SELECT 
    id,
    name,
    test_step,
    CASE 
        WHEN test_step = 0 THEN '待领取'
        WHEN test_step = 1 THEN '测试中'
        WHEN test_step = 2 THEN '一审中'
        WHEN test_step = 3 THEN '二审中'
        WHEN test_step = 4 THEN '三审中'
        WHEN test_step = 5 THEN '已完成'
        ELSE '未知状态'
    END AS step_name,
    order_id,
    create_time
FROM test_work_order
WHERE order_id IS NULL
ORDER BY test_step, id DESC;

-- 3. 查看 test_step = 1（测试中）的工单
SELECT 
    '3. 测试中的工单（test_step = 1）' AS step;
SELECT 
    id,
    batch_id,
    batch_name,
    name,
    test_step,
    test_status,
    order_id,
    tester_id,
    create_time
FROM test_work_order
WHERE test_step = 1 AND order_id IS NULL
ORDER BY id DESC;

-- 4. 查看最近创建的工单的 test_step 值
SELECT 
    '4. 最近创建的工单' AS step;
SELECT 
    id,
    name,
    test_step,
    CASE 
        WHEN test_step = 0 THEN '待领取'
        WHEN test_step = 1 THEN '测试中'
        WHEN test_step = 2 THEN '一审中'
        WHEN test_step = 3 THEN '二审中'
        WHEN test_step = 4 THEN '三审中'
        WHEN test_step = 5 THEN '已完成'
        WHEN test_step IS NULL THEN 'NULL'
        ELSE '未知状态'
    END AS step_name,
    order_id,
    create_time
FROM test_work_order
ORDER BY id DESC
LIMIT 20;

-- 5. 检查是否所有工单的 test_step 都是 NULL 或 0
SELECT 
    '5. test_step 为 NULL 或 0 的工单' AS step;
SELECT 
    COUNT(*) AS total_count,
    COUNT(CASE WHEN test_step IS NULL THEN 1 END) AS null_count,
    COUNT(CASE WHEN test_step = 0 THEN 1 END) AS zero_count,
    COUNT(CASE WHEN test_step > 0 THEN 1 END) AS greater_than_zero_count
FROM test_work_order
WHERE order_id IS NULL;

-- 6. 查看是否有工单的 test_step 字段有问题
SELECT 
    '6. test_step 字段定义' AS step;
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'test_work_order'
  AND COLUMN_NAME = 'test_step';