-- 退货托盘签收箱内清点；MySQL 8；仅在目标尾程库执行，可重复运行。
USE baiyue_lastleg;
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='count_status'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN count_status VARCHAR(16) NOT NULL DEFAULT ''CONFIRMED'' COMMENT ''箱内清点状态 PENDING待清点 CONFIRMED已确认''');
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_at'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_at DATETIME(3) NULL COMMENT ''箱内清点确认时间''');
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_by'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_by VARCHAR(128) NULL COMMENT ''箱内清点确认操作人''');
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND index_name='idx_ll_return_count'),'SELECT 1','CREATE INDEX idx_ll_return_count ON ll_return_inventory_carton(overseas_warehouse_id,count_status,status,source_receipt_id)');
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
-- 历史箱：只迁移整批仍可用、没有换标/合箱占用、未确认清点的托盘签收箱。
-- 已进入履约或合箱的批次不回退；后续需要逐批人工核实。
UPDATE ll_return_inventory_carton c
JOIN (SELECT source_receipt_id FROM (SELECT a.source_receipt_id
      FROM ll_return_inventory_carton a WHERE a.source_receipt_id IS NOT NULL AND a.source_unit_type='PALLET'
      GROUP BY a.source_receipt_id
      HAVING SUM(a.status<>'AVAILABLE' OR a.relabel_application_id IS NOT NULL OR a.relabel_no IS NOT NULL OR a.counted_at IS NOT NULL)=0
      AND SUM(EXISTS(SELECT 1 FROM ll_return_inventory_carton_item i WHERE i.carton_id=a.id AND COALESCE(i.packing_reserved_quantity,0)>0))=0
      AND SUM(EXISTS(SELECT 1 FROM ll_return_packing_trace t WHERE t.source_carton_mark=a.carton_mark OR t.target_carton_mark=a.carton_mark))=0
) pending) p ON p.source_receipt_id=c.source_receipt_id
SET c.count_status='PENDING' WHERE c.source_unit_type='PALLET' AND c.counted_at IS NULL;

-- 退货箱内清点作业会话：每位操作员只能认领一个批次，每个批次只能被一人认领。
CREATE TABLE IF NOT EXISTS ll_return_carton_count_session (
  operator_id VARCHAR(128) NOT NULL COMMENT '平台PDA操作员标识',
  warehouse_id VARCHAR(128) NOT NULL COMMENT '认领时绑定海外仓标识',
  receipt_id BIGINT NULL COMMENT '正在清点的签收批次，完成后释放为NULL',
  started_at DATETIME(3) NULL COMMENT '本次开始清点时间',
  created_at DATETIME(3) NOT NULL COMMENT '会话创建时间',
  updated_at DATETIME(3) NOT NULL COMMENT '会话更新时间',
  PRIMARY KEY(operator_id),
  UNIQUE KEY uk_ll_return_count_session_receipt(receipt_id)
) ENGINE=InnoDB COMMENT='退货箱内清点单人单任务持久会话';