peixue-dev/Archive/[一次性]订单状态字段定义检查.sql

-- 检查订单状态字段的定义和使用情况

-- ========================================
-- 订单状态字段说明
-- ========================================
-- pay_status（支付状态）：
--   0 = 未支付
--   1 = 已支付
--   2 = 已退款
--
-- status（订单状态）：
--   0 = 待派单（已支付，等待管理师分配陪伴员）
--   1 = 已派单/待接单（已分配陪伴员，等待陪伴员接单）
--   2 = 待服务（陪伴员已接单，等待服务开始）
--   3 = 服务中（服务进行中）
--   4 = 已完成（服务已完成）
--   5 = 已取消（订单已取消）
-- ========================================

-- 1. 查看 pay_status 和 status 的组合分布
SELECT 
    pay_status,
    CASE 
        WHEN pay_status = 0 THEN '未支付'
        WHEN pay_status = 1 THEN '已支付'
        WHEN pay_status = 2 THEN '已退款'
        ELSE CONCAT('未知支付状态(', pay_status, ')')
    END as pay_status_name,
    status,
    CASE 
        WHEN status = 0 THEN '待派单'
        WHEN status = 1 THEN '已派单/待接单'
        WHEN status = 2 THEN '待服务'
        WHEN status = 3 THEN '服务中'
        WHEN status = 4 THEN '已完成'
        WHEN status = 5 THEN '已取消'
        WHEN status = -1 THEN '未知状态(-1)'
        WHEN status IS NULL THEN 'NULL'
        ELSE CONCAT('未知订单状态(', status, ')')
    END as status_name,
    teacher_id IS NOT NULL as has_teacher,
    COUNT(*) as count,
    -- 检查是否合理
    CASE 
        -- 未支付的订单应该是待派单或已取消
        WHEN pay_status = 0 AND status NOT IN (0, 5) THEN '❌ 异常：未支付但状态不是待派单或已取消'
        -- 已支付但未分配陪伴员应该是待派单
        WHEN pay_status = 1 AND teacher_id IS NULL AND status != 0 THEN '❌ 异常：已支付未派单但状态不是0'
        -- 已支付且已分配陪伴员应该是已派单或更后面的状态
        WHEN pay_status = 1 AND teacher_id IS NOT NULL AND status = 0 THEN '❌ 异常：已派单但状态还是待派单'
        ELSE '✅ 正常'
    END as check_result
FROM `order`
WHERE deleted = 0
GROUP BY pay_status, status, has_teacher
ORDER BY pay_status, status;

-- 2. 检查可能存在的状态冲突
-- 情况1：已支付但未分配陪伴员，status不是0（应该是待派单）
SELECT 
    '已支付未派单但status不是0' as issue_type,
    COUNT(*) as count
FROM `order`
WHERE pay_status = 1 
  AND teacher_id IS NULL 
  AND status != 0
  AND deleted = 0;

-- 3. 列出具体的异常订单（已支付未派单但status不是0）
SELECT 
    id,
    order_no,
    pay_status,
    status,
    teacher_id,
    create_time,
    update_time,
    CASE 
        WHEN status = 0 THEN '待派单'
        WHEN status = 1 THEN '已派单/待接单'
        WHEN status = 2 THEN '待服务'
        WHEN status = 3 THEN '服务中'
        WHEN status = 4 THEN '已完成'
        WHEN status = 5 THEN '已取消'
        ELSE CONCAT('未知(', status, ')')
    END as status_name
FROM `order`
WHERE pay_status = 1 
  AND teacher_id IS NULL 
  AND status != 0
  AND deleted = 0
ORDER BY create_time DESC
LIMIT 20;

-- 4. 检查另一种冲突：已分配陪伴员但status还是0
SELECT 
    '已派单但status还是0' as issue_type,
    COUNT(*) as count
FROM `order`
WHERE pay_status = 1 
  AND teacher_id IS NOT NULL 
  AND status = 0
  AND deleted = 0;

-- 5. 列出具体的异常订单（已派单但status还是0）
SELECT 
    id,
    order_no,
    pay_status,
    status,
    teacher_id,
    create_time,
    update_time
FROM `order`
WHERE pay_status = 1 
  AND teacher_id IS NOT NULL 
  AND status = 0
  AND deleted = 0
ORDER BY create_time DESC
LIMIT 20;

-- 6. 统计各种状态组合的数量
SELECT 
    CONCAT(
        'pay_status=', pay_status, 
        ', status=', IFNULL(status, 'NULL'),
        ', teacher_id=', IF(teacher_id IS NULL, 'NULL', 'NOT NULL')
    ) as state_combination,
    COUNT(*) as count,
    -- 判断是否合理
    CASE 
        WHEN pay_status = 0 AND status = 0 AND teacher_id IS NULL THEN '✅ 未支付待派单'
        WHEN pay_status = 1 AND status = 0 AND teacher_id IS NULL THEN '✅ 已支付待派单'
        WHEN pay_status = 1 AND status = 1 AND teacher_id IS NOT NULL THEN '✅ 已派单待接单'
        WHEN pay_status = 1 AND status = 2 AND teacher_id IS NOT NULL THEN '✅ 待服务'
        WHEN pay_status = 1 AND status = 3 AND teacher_id IS NOT NULL THEN '✅ 服务中'
        WHEN pay_status = 1 AND status = 4 AND teacher_id IS NOT NULL THEN '✅ 已完成'
        WHEN pay_status = 1 AND status = 5 THEN '✅ 已取消'
        ELSE '❌ 异常状态组合'
    END as status_check
FROM `order`
WHERE deleted = 0
GROUP BY pay_status, status, teacher_id IS NOT NULL
ORDER BY count DESC;

-- 7. 查看所有不同的status值（包括异常值）
SELECT DISTINCT 
    status,
    COUNT(*) as count
FROM `order`
WHERE deleted = 0
GROUP BY status
ORDER BY status;