-- backend/scripts/migrate_20260731_bx_reimburse_invoice_attachment_id.sql
-- 报销关联发票：记录勾选的发票附件 ID，查看态只展示勾选附件

ALTER TABLE `bx_reimburse_invoice`
  ADD COLUMN `attachment_id` varchar(64) DEFAULT NULL COMMENT '勾选的发票附件ID' AFTER `invoice_id`;

-- 历史数据：无 attachment_id 时回填该发票下排序第一的附件
UPDATE `bx_reimburse_invoice` ri
SET ri.`attachment_id` = (
  SELECT a.`id`
  FROM `bx_attachment` a
  WHERE a.`biz_type` = 'invoice'
    AND a.`biz_id` = ri.`invoice_id`
    AND a.`tenant_no` = ri.`tenant_no`
    AND a.`del_flag` = 0
  ORDER BY a.`sort_order` ASC, a.`create_time` ASC, a.`id` ASC
  LIMIT 1
)
WHERE ri.`del_flag` = 0
  AND (ri.`attachment_id` IS NULL OR ri.`attachment_id` = '');