-- ==========================================
-- 任务附件同步定时任务配置脚本
-- ==========================================
-- 
-- 功能说明:
-- 将已同步调度单的任务附件同步到旧系统ImageData表
-- 
-- 使用场景:
-- 1. 新系统上传了任务附件
-- 2. 任务的调度单已成功同步到旧系统
-- 3. 附件需要同步到旧系统以便旧系统查看
-- 
-- 同步条件:
-- 1. 任务类型为急救转运 (task_type = 'EMERGENCY_TRANSFER')
-- 2. 调度单已同步成功 (dispatch_sync_status = 2)
-- 3. 有调度单ID和服务单ID (legacy_dispatch_ord_id IS NOT NULL AND legacy_service_ord_id IS NOT NULL)
-- 4. 附件未同步 (synced_to_image_data = 0 OR synced_to_image_data IS NULL)
-- 
-- 执行频率建议:
-- - 生产环境: 每5分钟执行一次 (0 0/5 * * * ?)
-- - 测试环境: 每2分钟执行一次 (0 0/2 * * * ?)
-- 
-- 注意事项:
-- 1. 依赖调度单同步完成
-- 2. 会上传文件并生成缩略图
-- 3. 附件分类映射见TaskAttachmentSyncServiceImpl类
-- ==========================================

-- 插入定时任务配置
INSERT INTO `sys_job` (
    `job_name`,
    `job_group`,
    `invoke_target`,
    `cron_expression`,
    `misfire_policy`,
    `concurrent`,
    `status`,
    `create_by`,
    `create_time`,
    `update_by`,
    `update_time`,
    `remark`
) VALUES (
    '任务附件同步',
    'DEFAULT',
    'legacySystemSyncTask.syncPendingAttachments()',
    '0 0/5 * * * ?',
    '2',
    '1',
    '0',
    'admin',
    NOW(),
    'admin',
    NOW(),
    '将已同步调度单的任务附件同步到旧系统ImageData表，每5分钟执行一次。同步条件：调度单已同步、附件未同步、有服务单和调度单ID。'
);

-- ==========================================
-- 验证查询
-- ==========================================

-- 1. 查看定时任务是否添加成功
SELECT 
    job_id,
    job_name,
    job_group,
    invoke_target,
    cron_expression,
    CASE status 
        WHEN '0' THEN '正常'
        WHEN '1' THEN '暂停'
    END AS job_status,
    remark
FROM sys_job
WHERE job_name = '任务附件同步';

-- 2. 查看需要同步的附件数量
SELECT 
    COUNT(*) AS total_attachments,
    SUM(CASE WHEN a.attachment_category = '1' THEN 1 ELSE 0 END) AS consent_form,
    SUM(CASE WHEN a.attachment_category = '2' THEN 1 ELSE 0 END) AS patient_data,
    SUM(CASE WHEN a.attachment_category = '3' THEN 1 ELSE 0 END) AS operation_record,
    SUM(CASE WHEN a.attachment_category = '4' THEN 1 ELSE 0 END) AS before_departure,
    SUM(CASE WHEN a.attachment_category = '5' THEN 1 ELSE 0 END) AS after_departure,
    SUM(CASE WHEN a.attachment_category = '6' THEN 1 ELSE 0 END) AS seat_belt
FROM sys_task_attachment a
INNER JOIN sys_task t ON a.task_id = t.task_id
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
WHERE t.task_type = 'EMERGENCY_TRANSFER'
  AND e.dispatch_sync_status = 2
  AND e.legacy_dispatch_ord_id IS NOT NULL
  AND e.legacy_service_ord_id IS NOT NULL
  AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL);

-- 3. 查看待同步附件详情（最近10条）
SELECT 
    a.attachment_id,
    t.task_code,
    a.file_name,
    CASE a.attachment_category
        WHEN '1' THEN '知情同意书'
        WHEN '2' THEN '病人资料'
        WHEN '3' THEN '操作记录'
        WHEN '4' THEN '出车前'
        WHEN '5' THEN '出车后'
        WHEN '6' THEN '系安全带'
        ELSE '未分类'
    END AS category_name,
    e.legacy_service_ord_id,
    e.legacy_dispatch_ord_id,
    a.upload_time,
    a.synced_to_image_data,
    a.sync_time
FROM sys_task_attachment a
INNER JOIN sys_task t ON a.task_id = t.task_id
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
WHERE t.task_type = 'EMERGENCY_TRANSFER'
  AND e.dispatch_sync_status = 2
  AND e.legacy_dispatch_ord_id IS NOT NULL
  AND e.legacy_service_ord_id IS NOT NULL
  AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL)
ORDER BY a.upload_time DESC
LIMIT 10;

-- 4. 查看已同步附件统计
SELECT 
    COUNT(*) AS synced_attachments,
    MIN(a.sync_time) AS first_sync_time,
    MAX(a.sync_time) AS last_sync_time
FROM sys_task_attachment a
WHERE a.synced_to_image_data = 1
  AND a.sync_time IS NOT NULL;

-- 5. 按任务统计待同步附件
SELECT 
    t.task_id,
    t.task_code,
    e.legacy_service_ord_id,
    e.legacy_dispatch_ord_id,
    COUNT(a.attachment_id) AS attachment_count,
    GROUP_CONCAT(
        CASE a.attachment_category
            WHEN '1' THEN '知情同意书'
            WHEN '2' THEN '病人资料'
            WHEN '3' THEN '操作记录'
            WHEN '4' THEN '出车前'
            WHEN '5' THEN '出车后'
            WHEN '6' THEN '系安全带'
        END 
        SEPARATOR ', '
    ) AS categories
FROM sys_task t
INNER JOIN sys_task_emergency e ON t.task_id = e.task_id
INNER JOIN sys_task_attachment a ON t.task_id = a.task_id
WHERE t.task_type = 'EMERGENCY_TRANSFER'
  AND e.dispatch_sync_status = 2
  AND e.legacy_dispatch_ord_id IS NOT NULL
  AND e.legacy_service_ord_id IS NOT NULL
  AND (a.synced_to_image_data = 0 OR a.synced_to_image_data IS NULL)
GROUP BY t.task_id, t.task_code, e.legacy_service_ord_id, e.legacy_dispatch_ord_id
ORDER BY attachment_count DESC;

-- ==========================================
-- 附件分类映射规则参考
-- ==========================================
/*
附件分类 -> ImageData类型:
1-知情同意书 -> ImageType: 1
2-病人资料 -> ImageType: 2
3-操作记录 -> ImageType: 3
4-出车前 -> ImageType: 4
5-出车后 -> ImageType: 5
6-系安全带 -> ImageType: 6

详细说明见: com.ruoyi.system.service.impl.TaskAttachmentSyncServiceImpl.getImageTypeByCategory()
*/

-- ==========================================
-- 同步流程说明
-- ==========================================
/*
1. 查询待同步附件列表（符合条件的附件）
2. 按任务分组，获取服务单ID、调度单ID、创建人OA用户ID
3. 读取本地附件文件
4. 上传到文件服务器并生成缩略图
5. 将附件信息写入旧系统ImageData表
   - DOrdIDDt: 调度单ID
   - SOrdIDDt: 服务单ID
   - ImageType: 附件分类对应的类型
   - ImageUrl: 原图路径
   - ImageUrls: 缩略图路径
   - UpImageTime: 上传时间
   - UpImageOAid: 上传者OA用户ID
6. 更新附件同步状态
   - synced_to_image_data = 1
   - sync_time = 当前时间
   - image_data_id = ImageData表ID
*/

-- ==========================================
-- 手动触发测试
-- ==========================================
/*
-- 在定时任务管理页面手动执行一次任务，或者在代码中调用:
-- legacySystemSyncTask.syncPendingAttachments()

-- 查看同步日志（需要查看应用日志文件）
*/

-- ==========================================
-- 故障排查SQL
-- ==========================================

-- 查看同步失败的附件（如果有错误记录）
SELECT 
    a.attachment_id,
    t.task_code,
    a.file_name,
    a.file_path,
    a.upload_time,
    a.synced_to_image_data,
    a.sync_time
FROM sys_task_attachment a
INNER JOIN sys_task t ON a.task_id = t.task_id
WHERE a.synced_to_image_data = 0
  AND a.upload_time < DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY a.upload_time DESC;

-- 查看某个任务的所有附件同步状态
-- SELECT 
--     a.attachment_id,
--     a.file_name,
--     a.attachment_category,
--     a.synced_to_image_data,
--     a.sync_time,
--     a.image_data_id
-- FROM sys_task_attachment a
-- WHERE a.task_id = ? -- 替换为具体的任务ID
-- ORDER BY a.upload_time DESC;