-- ============================================
-- Bug #318 历史手术医嘱数据修复脚本
-- 修复 content_json 中缺失的 surgeryName
-- ============================================

-- 1. 查看需要修复的数据
SELECT 
    'Before Fix - Count' as status,
    COUNT(*) as total_count
FROM wor_service_request 
WHERE category_enum = 4 
AND delete_flag = '0'
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');

-- 2. 查看需要修复的详细记录
SELECT 
    id,
    activity_id,
    content_json::jsonb->>'surgeryName' as current_surgery_name,
    content_json as full_content_json,
    create_time
FROM wor_service_request 
WHERE category_enum = 4 
AND delete_flag = '0'
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '')
ORDER BY create_time DESC
LIMIT 10;

-- 3. 【执行修复】从 cli_surgery 表获取手术名称并更新
-- 注意：此操作会修改历史数据，请先备份
UPDATE wor_service_request wsr
SET content_json = 
    CASE 
        WHEN wsr.content_json IS NULL OR wsr.content_json = '' THEN 
            jsonb_build_object(
                'surgeryName', cs.surgery_name,
                'surgeryCode', cs.surgery_code
            )::text
        ELSE 
            (wsr.content_json::jsonb || jsonb_build_object(
                'surgeryName', cs.surgery_name,
                'surgeryCode', cs.surgery_code
            ))::text
    END
FROM cli_surgery cs
WHERE wsr.category_enum = 4 
AND wsr.delete_flag = '0'
AND (wsr.content_json::jsonb->>'surgeryName' IS NULL OR wsr.content_json::jsonb->>'surgeryName' = '')
AND cs.id = wsr.activity_id;

-- 4. 【备选修复】如果没有关联的 cli_surgery 记录，尝试从 activity_id 获取名称
UPDATE wor_service_request wsr
SET content_json = 
    CASE 
        WHEN wsr.content_json IS NULL OR wsr.content_json = '' THEN 
            jsonb_build_object(
                'surgeryName', '手术项目-' || wsr.activity_id,
                'surgeryCode', ''
            )::text
        ELSE 
            (wsr.content_json::jsonb || jsonb_build_object(
                'surgeryName', '手术项目-' || wsr.activity_id,
                'surgeryCode', ''
            ))::text
    END
WHERE wsr.category_enum = 4 
AND wsr.delete_flag = '0'
AND (wsr.content_json::jsonb->>'surgeryName' IS NULL OR wsr.content_json::jsonb->>'surgeryName' = '')
AND wsr.activity_id IS NOT NULL;

-- 5. 查看修复后的结果
SELECT 
    'After Fix - Count' as status,
    COUNT(*) as remaining_count
FROM wor_service_request 
WHERE category_enum = 4 
AND delete_flag = '0'
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');

-- 6. 验证修复成功
SELECT 
    id,
    content_json::jsonb->>'surgeryName' as surgery_name,
    content_json::jsonb->>'surgeryCode' as surgery_code,
    create_time
FROM wor_service_request 
WHERE category_enum = 4 
AND delete_flag = '0'
ORDER BY create_time DESC
LIMIT 5;