-- 补历史入库任务（8767~8770）云仓上报来源字段，并生成待上报记录
-- 执行后会在 man_cloud_wms_notify_log 产生 notify_status=0 的待办，按现有定时任务自动补发到云仓

-- 1) 回填任务明细 plat_order_code / plat_work_code（优先取组托明细）
UPDATE man_task_item ti
LEFT JOIN man_wait_pakin_item wpi ON wpi.id = ti.source
SET ti.plat_order_code = COALESCE(NULLIF(ti.plat_order_code, ''), NULLIF(wpi.plat_order_code, '')),
    ti.plat_work_code = COALESCE(NULLIF(ti.plat_work_code, ''), NULLIF(wpi.plat_work_code, '')),
    ti.update_time = NOW()
WHERE ti.task_id IN (8767, 8768, 8769, 8770)
  AND (ti.deleted = 0 OR ti.deleted = 1);

-- 2) 组托明细仍为空时，按订单明细兜底回填（订单明细表：man_asn_order_item）
UPDATE man_task_item ti
LEFT JOIN man_asn_order_item oi ON oi.id = ti.order_item_id
SET ti.plat_order_code = COALESCE(NULLIF(ti.plat_order_code, ''), NULLIF(oi.plat_order_code, '')),
    ti.plat_work_code = COALESCE(NULLIF(ti.plat_work_code, ''), NULLIF(oi.plat_work_code, '')),
    ti.update_time = NOW()
WHERE ti.task_id IN (8767, 8768, 8769, 8770)
  AND (ti.deleted = 0 OR ti.deleted = 1);

-- 3) 生成云仓上报待办（去重：同 taskId + orderNo + lineId 不重复插入）
INSERT INTO man_cloud_wms_notify_log
(report_type, request_body, notify_status, retry_count, biz_ref, tenant_id, deleted, create_time, update_time)
SELECT
    'IN_OUT_RESULT' AS report_type,
    JSON_OBJECT(
        'orderNo', src.order_no,
        'planNo', src.plan_no,
        'lineId', src.line_id,
        'wareHouseId', src.warehouse_code,
        'locId', src.loc_code,
        'matNr', src.matnr_code,
        'qty', src.qty_txt,
        'batch', src.batch_val,
        'inbound', true,
        'barcode', src.barcode
    ) AS request_body,
    CAST(COALESCE(
            (SELECT val FROM sys_config WHERE flag = 'CLOUD_WMS_NOTIFY_STATUS_PENDING' AND (deleted = 0 OR deleted IS NULL) ORDER BY id DESC LIMIT 1),
            '0'
         ) AS SIGNED) AS notify_status,
    0 AS retry_count,
    CONCAT('taskId=', src.task_id, ',orderNo=', src.order_no, ',lineId=', COALESCE(src.line_id, '')) AS biz_ref,
    src.tenant_id,
    0 AS deleted,
    NOW() AS create_time,
    NOW() AS update_time
FROM (
    SELECT
        t.id AS task_id,
        t.barcode,
        t.targ_loc AS loc_code,
        wh.code AS warehouse_code,
        COALESCE(NULLIF(ti.plat_order_code, ''), NULLIF(ti.plat_work_code, ''), NULLIF(wpi.asn_code, '')) AS order_no,
        NULLIF(ti.plat_work_code, '') AS plan_no,
        ti.plat_item_id AS line_id,
        ti.matnr_code,
        CAST(IFNULL(ti.anfme, 0) AS CHAR) AS qty_txt,
        ti.batch AS batch_val,
        IFNULL(ti.tenant_id, 1) AS tenant_id
    FROM man_task t
    JOIN man_task_item ti ON ti.task_id = t.id
    LEFT JOIN man_wait_pakin_item wpi ON wpi.id = ti.source
    LEFT JOIN man_loc l ON l.code = t.targ_loc
    LEFT JOIN man_warehouse wh ON wh.id = l.warehouse_id
    WHERE t.id IN (8767, 8768, 8769, 8770)
      AND t.task_type = 1
      AND t.task_status = 100
      AND (ti.deleted = 0 OR ti.deleted = 1)
) src
LEFT JOIN man_cloud_wms_notify_log exist_log
       ON exist_log.report_type = 'IN_OUT_RESULT'
      AND exist_log.deleted = 0
      AND exist_log.biz_ref = CONCAT('taskId=', src.task_id, ',orderNo=', src.order_no, ',lineId=', COALESCE(src.line_id, ''))
WHERE src.order_no IS NOT NULL
  AND src.order_no <> ''
  AND src.matnr_code IS NOT NULL
  AND src.matnr_code <> ''
  AND src.warehouse_code IS NOT NULL
  AND src.warehouse_code <> ''
  AND exist_log.id IS NULL;
