-- 历史数据修复：已退回报销申请仍挂靠的购买记录，解绑并重置为「未审批」
-- 使申请人无需逐条点「更新」即可在物品购买中重新选用
-- 执行前请先备份相关表

-- 1) 预览：将被解绑的购买记录
SELECT
  pr.F_Id AS purchaseId,
  pr.F_ApplicationId AS applicationId,
  pr.F_ApproveStatus AS purchaseStatus,
  app.F_ApprovalStatus AS applicationStatus,
  app.F_ApplicationUserName AS applicant,
  pr.F_ReimbursementCategoryName AS itemName,
  pr.F_Amount AS amount
FROM lq_purchase_records pr
INNER JOIN lq_reimbursement_application app ON app.F_Id = pr.F_ApplicationId
WHERE (app.F_ApprovalStatus = '已退回' OR app.F_ApproveStatus = '已退回')
  AND pr.F_ApplicationId IS NOT NULL
  AND pr.F_ApplicationId <> '';

-- 2) 解绑购买记录并重置状态
UPDATE lq_purchase_records pr
INNER JOIN lq_reimbursement_application app ON app.F_Id = pr.F_ApplicationId
SET
  pr.F_ApplicationId = NULL,
  pr.F_ApproveStatus = '未审批',
  pr.F_ApproveTime = NULL,
  pr.F_ApproveUser = NULL
WHERE (app.F_ApprovalStatus = '已退回' OR app.F_ApproveStatus = '已退回')
  AND pr.F_ApplicationId IS NOT NULL
  AND pr.F_ApplicationId <> '';

-- 3) 清空已退回申请单上的购买关联串（明细已解绑）
UPDATE lq_reimbursement_application
SET F_PurchaseRecordsId = NULL
WHERE (F_ApprovalStatus = '已退回' OR F_ApproveStatus = '已退回')
  AND F_PurchaseRecordsId IS NOT NULL
  AND F_PurchaseRecordsId <> '';