-- ============================================
-- Bug #318 历史手术医嘱数据修复（执行版本）
-- ============================================

-- 步骤1：先查看有多少条记录需要修复
SELECT 
    '需要修复的记录数' as description,
    COUNT(*) as 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：【执行此语句进行修复】
-- 从 cli_surgery 表关联获取手术名称
UPDATE wor_service_request wsr
SET content_json = COALESCE(
    (wsr.content_json::jsonb || jsonb_build_object(
        'surgeryName', cs.surgery_name,
        'surgeryCode', cs.surgery_code
    ))::text,
    jsonb_build_object(
        'surgeryName', COALESCE(cs.surgery_name, '手术项目'),
        'surgeryCode', COALESCE(cs.surgery_code, '')
    )::text
)
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
AND cs.delete_flag = '0';

-- 步骤3：【备选修复】对于没有关联 cli_surgery 的记录，使用默认值
UPDATE wor_service_request 
SET content_json = COALESCE(
    (content_json::jsonb || jsonb_build_object(
        'surgeryName', '手术项目',
        'surgeryCode', ''
    ))::text,
    jsonb_build_object(
        'surgeryName', '手术项目',
        'surgeryCode', ''
    )::text
)
WHERE category_enum = 4 
AND delete_flag = '0'
AND (content_json::jsonb->>'surgeryName' IS NULL OR content_json::jsonb->>'surgeryName' = '');

-- 步骤4：验证修复结果
SELECT 
    '修复后剩余空记录数' as description,
    COUNT(*) as 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' = '');

-- 步骤5：查看修复后的示例数据
SELECT 
    id,
    activity_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;


-- 查看今天新插入的手术医嘱
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'
  AND create_time >= CURRENT_DATE
ORDER BY create_time DESC
LIMIT 3;