-- 模拟后端查询，测试工单列表是否能正常返回数据
-- 这个查询模拟了 test_work_order_dao.py 中的查询逻辑

SELECT 
    tw.id,
    tw.batch_id,
    tw.batch_name,
    tw.test_eut_id,
    tw.test_category_id,
    tw.creator,
    tw.create_time,
    tw.update_by,
    tw.update_time,
    tw.name,
    tw.test_item_id,
    tw.tester_id,
    tw.reviewer_id,
    tw.second_tester_id,
    tw.third_tester_id,
    tw.tester_result,
    tw.reviewer_result,
    tw.second_tester_result,
    tw.third_tester_result,
    tw.test_status,
    tw.test_step,
    tw.memo,
    creator_user.nick_name AS creator_name,
    updater_user.nick_name AS update_by_name,
    tester_user.user_name AS tester_name,
    reviewer_user.user_name AS reviewer_name,
    second_tester_user.user_name AS second_tester_name,
    third_tester_user.user_name AS third_tester_name,
    tester_user_nick.nick_name AS tester_name_nick,
    reviewer_user_nick.nick_name AS reviewer_name_nick,
    second_tester_user_nick.nick_name AS second_tester_name_nick,
    third_tester_user_nick.nick_name AS third_tester_name_nick,
    eut.sn AS test_eut_name,
    category.name AS test_category_name,
    item.name AS test_item_name,
    item_template.config_json AS test_item_config_json
FROM test_work_order tw
LEFT JOIN sys_user creator_user ON tw.creator = creator_user.user_id
LEFT JOIN sys_user updater_user ON tw.update_by = updater_user.user_id
LEFT JOIN sys_user tester_user ON tw.tester_id = tester_user.user_id
LEFT JOIN sys_user reviewer_user ON tw.reviewer_id = reviewer_user.user_id
LEFT JOIN sys_user second_tester_user ON tw.second_tester_id = second_tester_user.user_id
LEFT JOIN sys_user third_tester_user ON tw.third_tester_id = third_tester_user.user_id
LEFT JOIN sys_user tester_user_nick ON tw.tester_id = tester_user_nick.user_id
LEFT JOIN sys_user reviewer_user_nick ON tw.reviewer_id = reviewer_user_nick.user_id
LEFT JOIN sys_user second_tester_user_nick ON tw.second_tester_id = second_tester_user_nick.user_id
LEFT JOIN sys_user third_tester_user_nick ON tw.third_tester_id = third_tester_user_nick.user_id
LEFT JOIN test_eut eut ON tw.test_eut_id = eut.id
LEFT JOIN test_category category ON tw.test_category_id = category.id
LEFT JOIN test_item item ON tw.test_item_id = item.id
LEFT JOIN test_item item_template ON tw.test_item_id = item_template.id
WHERE tw.order_id IS NULL
ORDER BY tw.id DESC
LIMIT 10;