-- 本地验收数据：为 17909216591 对应客户建立 3 张已完成入库的一件代发订单。
-- 可重复执行；候选数据覆盖海外实物托盘、订单、入库计划、库存余额/仓位、入库实收、一件代发档案、SKU 与箱唛。
USE baiyue_lastlag_warehouse;

INSERT INTO wh_pallet (
  id,customer_id,warehouse_id,location_id,pallet_code,pallet_type,capacity_cbm,unit_price,
  status,remark,fba_warehouse_code,created_by,created_at,updated_by,updated_at,deleted,version
) VALUES
  (2191000000000909901,0,193000000000200003,NULL,'DS-LAX-PALLET-01','STANDARD',2.0000,0.00,
   'OCCUPIED','一件代发本地验收固定托盘','ONT8',1000001,NOW(3),1000001,NOW(3),0,0),
  (2191000000000909902,0,193000000000200003,NULL,'DS-LAX-PALLET-02','STANDARD',2.0000,0.00,
   'OCCUPIED','一件代发本地验收固定托盘','ABE2',1000001,NOW(3),1000001,NOW(3),0,0),
  (2191000000000909903,0,193000000000200003,NULL,'DS-LAX-PALLET-03','STANDARD',2.0000,0.00,
   'OCCUPIED','一件代发本地验收固定托盘','ABE3',1000001,NOW(3),1000001,NOW(3),0,0)
ON DUPLICATE KEY UPDATE
  warehouse_id=VALUES(warehouse_id),pallet_code=VALUES(pallet_code),pallet_type=VALUES(pallet_type),
  status=VALUES(status),fba_warehouse_code=VALUES(fba_warehouse_code),deleted=0,updated_at=NOW(3);

USE baiyue_lastleg;

DROP TEMPORARY TABLE IF EXISTS _tmp_dropship_fixture;
CREATE TEMPORARY TABLE _tmp_dropship_fixture (
  seq_no INT NOT NULL PRIMARY KEY,
  order_id BIGINT NOT NULL,
  plan_id BIGINT NOT NULL,
  plan_pallet_id BIGINT NOT NULL,
  balance_id BIGINT NOT NULL,
  position_id BIGINT NOT NULL,
  profile_id BIGINT NOT NULL,
  sku_inventory_id BIGINT NOT NULL,
  receipt_id BIGINT NOT NULL,
  warehouse_pallet_id BIGINT NOT NULL,
  package_count INT NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  inbound_code VARCHAR(128) NOT NULL,
  plan_no VARCHAR(32) NOT NULL,
  pallet_no VARCHAR(64) NOT NULL,
  sku_code VARCHAR(128) NOT NULL,
  product_name VARCHAR(255) NOT NULL
);

INSERT INTO _tmp_dropship_fixture VALUES
  (1,2191000000000909101,2191000000000909201,2191000000000909301,2191000000000909401,2191000000000909501,2191000000000909601,2191000000000909701,2191000000000909801,2191000000000909901, 6,'DS-IN-20260909-01','DS-RK-20260909-01','DS-PLAN-20260909-01','DS-LAX-PALLET-01','DS-SKU-STORAGE-01','折叠收纳箱'),
  (2,2191000000000909102,2191000000000909202,2191000000000909302,2191000000000909402,2191000000000909502,2191000000000909602,2191000000000909702,2191000000000909802,2191000000000909902, 8,'DS-IN-20260909-02','DS-RK-20260909-02','DS-PLAN-20260909-02','DS-LAX-PALLET-02','DS-SKU-LAMP-02','桌面灯具'),
  (3,2191000000000909103,2191000000000909203,2191000000000909303,2191000000000909403,2191000000000909503,2191000000000909603,2191000000000909703,2191000000000909803,2191000000000909903,10,'DS-IN-20260909-03','DS-RK-20260909-03','DS-PLAN-20260909-03','DS-LAX-PALLET-03','DS-SKU-RACK-03','厨房置物架');

INSERT INTO ll_order (
  id,tenant_id,order_no,customer_id,order_type,lx_code,destination_type,inbound_code,
  source_region,source_inbound_id,source_order_id,source_order_no,customer_name,
  overseas_warehouse_id,overseas_warehouse_code,overseas_warehouse_name,
  fba_warehouse_code,container_no,container_status,pallet_count,package_count,product_quantity,
  inbound_order_no,current_status,source_event_id,source_loading_plan_id,source_payload_hash,created_at,updated_at
)
SELECT order_id,'BAIYUE',order_no,'2093269089353961474','ONE_CLICK_DROPSHIP',CONCAT('LX-',order_no),
       'ONE_CLICK_DROPSHIP',inbound_code,'FIRST_LEG',CONCAT('SEED-DROPSHIP-',seq_no),
       CAST(order_id AS CHAR),order_no,'17909216591测试客户','193000000000200003','US-LAX-01','洛杉矶仓',
       CASE seq_no WHEN 1 THEN 'ONT8' WHEN 2 THEN 'ABE2' ELSE 'ABE3' END,
       CONCAT('DS-CONT-20260909-',LPAD(seq_no,2,'0')),'已拆柜',1,package_count,package_count * 10,
       plan_no,'INBOUND_COMPLETED',CONCAT('SEED-DROPSHIP-EVENT-',seq_no),
       CONCAT('SEED-DROPSHIP-LOAD-',seq_no),CONCAT('sha256:seed-dropship-',seq_no),NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE current_status='INBOUND_COMPLETED',package_count=VALUES(package_count),
  product_quantity=VALUES(product_quantity),updated_at=NOW(3);

INSERT INTO ll_inbound_plan (
  id,tenant_id,plan_no,customer_id,source_order_id,container_no,overseas_warehouse_id,
  planned_arrival_date,pallet_type,assigned_flag,merge_required,urgent_flag,creation_mode,
  plan_remark,status,line_count,pallet_count,total_volume_cbm,created_at,updated_at,version
)
SELECT plan_id,'BAIYUE',plan_no,'2093269089353961474',order_id,
       CONCAT('DS-CONT-20260909-',LPAD(seq_no,2,'0')),193000000000200003,
       CURRENT_DATE,'STANDARD',1,0,0,'AUTO','一件代发出库分页验收数据','COMPLETED',1,1,1.0000,NOW(3),NOW(3),0
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE status='COMPLETED',assigned_flag=1,updated_at=NOW(3);

INSERT INTO ll_inbound_plan_pallet (
  id,tenant_id,customer_id,plan_id,pallet_no,source_pallet_no,pallet_type,
  destination_warehouse_code,destination_warehouse_name,capacity_cbm,allocated_volume_cbm,
  sequence_no,warehouse_pallet_id,assignment_status,locked_at,actual_volume_cbm,actual_package_count
)
SELECT plan_pallet_id,'BAIYUE','2093269089353961474',plan_id,pallet_no,
       CONCAT('DS-SOURCE-PALLET-',LPAD(seq_no,2,'0')),'STANDARD',
       CASE seq_no WHEN 1 THEN 'ONT8' WHEN 2 THEN 'ABE2' ELSE 'ABE3' END,
       'Amazon FBA',2.0000,1.0000,1,warehouse_pallet_id,'ASSIGNED',NOW(3),1.0000,package_count
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE warehouse_pallet_id=VALUES(warehouse_pallet_id),assignment_status='ASSIGNED',
  actual_package_count=VALUES(actual_package_count);

INSERT INTO ll_inventory_balance (
  id,tenant_id,customer_id,customer_name,overseas_warehouse_id,overseas_warehouse_code,
  overseas_warehouse_name,destination_warehouse_code,destination_warehouse_name,
  inbound_order_id,inbound_order_no,inbound_code,sku_code,sku_name_zh,product_name_zh,
  available_packages,available_quantity,version,created_at,updated_at
)
SELECT balance_id,'BAIYUE','2093269089353961474','17909216591测试客户','193000000000200003',
       'US-LAX-01','洛杉矶仓',CASE seq_no WHEN 1 THEN 'ONT8' WHEN 2 THEN 'ABE2' ELSE 'ABE3' END,
       'Amazon FBA',order_id,plan_no,inbound_code,sku_code,product_name,product_name,
       package_count,package_count * 10,0,NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE customer_name=VALUES(customer_name),sku_code=VALUES(sku_code),
  available_packages=VALUES(available_packages),available_quantity=VALUES(available_quantity),updated_at=NOW(3);

INSERT INTO ll_inventory_position (
  id,tenant_id,customer_id,balance_id,overseas_warehouse_id,destination_warehouse_code,
  sku_code,pallet_id,pallet_no,batch_no,warehouse_location_code,available_packages,
  product_quantity,status,version,created_at,updated_at
)
SELECT position_id,'BAIYUE','2093269089353961474',balance_id,'193000000000200003',
       CASE seq_no WHEN 1 THEN 'ONT8' WHEN 2 THEN 'ABE2' ELSE 'ABE3' END,
       sku_code,warehouse_pallet_id,pallet_no,CONCAT('DS-BATCH-20260909-',seq_no),
       CONCAT('LAX-A-',LPAD(seq_no,2,'0')),package_count,package_count * 10,'OCCUPIED',0,NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE available_packages=VALUES(available_packages),product_quantity=VALUES(product_quantity),
  status='OCCUPIED',updated_at=NOW(3);

INSERT INTO ll_dropship_order_profile (
  id,customer_id,order_id,warehouse_pallet_id,pallet_no,source_pallet_no,
  inventory_position_id,status,idempotency_key,version,created_at,updated_at
)
SELECT profile_id,'2093269089353961474',order_id,warehouse_pallet_id,pallet_no,
       CONCAT('DS-SOURCE-PALLET-',LPAD(seq_no,2,'0')),position_id,'ACTIVE',
       CONCAT('SEED-DROPSHIP-PROFILE-',seq_no),0,NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE warehouse_pallet_id=VALUES(warehouse_pallet_id),pallet_no=VALUES(pallet_no),
  inventory_position_id=VALUES(inventory_position_id),status='ACTIVE',updated_at=NOW(3);

INSERT INTO ll_dropship_sku_inventory (
  id,customer_id,order_id,profile_id,sku_code,product_name,package_count,product_quantity,created_at,updated_at
)
SELECT sku_inventory_id,'2093269089353961474',order_id,profile_id,sku_code,product_name,
       package_count,package_count * 10,NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE product_name=VALUES(product_name),package_count=VALUES(package_count),
  product_quantity=VALUES(product_quantity),updated_at=NOW(3);

INSERT INTO ll_dropship_carton (
  id,customer_id,order_id,profile_id,warehouse_pallet_id,pallet_no,sku_code,
  carton_mark,carton_sequence,status,version,created_at,updated_at
)
SELECT 2191000000001000000 + fixture.seq_no * 100 + numbers.n,
       '2093269089353961474',fixture.order_id,fixture.profile_id,fixture.warehouse_pallet_id,
       fixture.pallet_no,fixture.sku_code,
       CONCAT('DS-CARTON-20260909-',LPAD(fixture.seq_no,2,'0'),'-',LPAD(numbers.n,3,'0')),
       numbers.n,'AVAILABLE',0,NOW(3),NOW(3)
FROM _tmp_dropship_fixture fixture
JOIN (
  SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) numbers ON numbers.n <= fixture.package_count
ON DUPLICATE KEY UPDATE warehouse_pallet_id=VALUES(warehouse_pallet_id),pallet_no=VALUES(pallet_no),
  sku_code=VALUES(sku_code),status='AVAILABLE',outbound_order_no=NULL,updated_at=NOW(3);

INSERT INTO ll_inbound_stock_receipt (
  id,tenant_id,customer_id,plan_id,task_id,allocation_id,line_id,order_id,inbound_code,
  sku_code,product_name_zh,sku_name_zh,warehouse_pallet_id,pallet_no,source_pallet_no,
  warehouse_location_code,batch_no,received_packages,received_quantity,inventory_balance_id,
  inventory_position_id,inventory_operation_key,received_at,created_at
)
SELECT receipt_id,'BAIYUE','2093269089353961474',plan_id,plan_id + 10000,plan_id + 20000,
       plan_id + 30000,order_id,inbound_code,sku_code,product_name,product_name,warehouse_pallet_id,
       pallet_no,CONCAT('DS-SOURCE-PALLET-',LPAD(seq_no,2,'0')),CONCAT('LAX-A-',LPAD(seq_no,2,'0')),
       CONCAT('DS-BATCH-20260909-',seq_no),package_count,package_count * 10,balance_id,position_id,
       CONCAT('SEED_PDA_STOCK_IN:',plan_id),NOW(3),NOW(3)
FROM _tmp_dropship_fixture
ON DUPLICATE KEY UPDATE received_packages=VALUES(received_packages),received_quantity=VALUES(received_quantity);

SELECT fixture.order_no,fixture.inbound_code,fixture.pallet_no,fixture.sku_code,
       fixture.package_count AS available_packages
FROM _tmp_dropship_fixture fixture
ORDER BY fixture.seq_no;

DROP TEMPORARY TABLE IF EXISTS _tmp_dropship_fixture;