baiyue-firstleg/deploy/sql/reset-test-fba-appointments.sql

-- TEST ONLY. Rebuild the explicitly authorized appointment header and item tables.
-- The command-history and cargo-verification tables MUST NOT already exist; check both before any DROP.
-- If either exists, stop and inspect the prior outcome. Do not clear history or blindly retry.
-- Requires explicit approval; never run against production or as automatic startup SQL.
-- Caller must verify the TEST connection before executing.
USE baiyue_lastleg;
DROP PROCEDURE IF EXISTS assert_test_appointment_reset_preconditions;
DELIMITER $$
CREATE PROCEDURE assert_test_appointment_reset_preconditions()
BEGIN
  IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE()
      AND table_name IN ('ll_fba_appointment_command','ll_appointment_cargo_verification',
        'll_appointment_work','ll_dispatch_appointment_allocation','ll_fba_receiving_agreement',
        'll_fba_receiving_member','ll_fba_receiving_change','ll_fba_receiving_revision','ll_fba_receiving_plan')) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Stop: appointment history or work/allocation schema already exists; inspect before any reset';
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE()
      AND table_name IN ('ll_inbound_fba_appointment','ll_inbound_fba_appointment_item')) <> 2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Stop: expected both legacy appointment tables; inspect partial or new schema';
  END IF;
END$$
DELIMITER ;
CALL assert_test_appointment_reset_preconditions();
DROP PROCEDURE assert_test_appointment_reset_preconditions;
DROP TABLE ll_inbound_fba_appointment_item;
DROP TABLE ll_inbound_fba_appointment;
CREATE TABLE ll_inbound_fba_appointment (
  id BIGINT NOT NULL COMMENT '主键ID',
  appointment_no VARCHAR(64) NOT NULL COMMENT '系统预约业务单号，不是外部约号',
  confirmation_no VARCHAR(128) NULL COMMENT '收货方正式外部确认号',
  planned_date DATE NULL COMMENT '计划当地日期，可只有日期而无时段',
  planned_at DATETIME(3) NULL COMMENT '计划开始当地时间，待定可空',
  planned_end_at DATETIME(3) NULL COMMENT '计划结束当地时间',
  appointment_at DATETIME(3) NULL COMMENT '对方确认开始当地时间',
  appointment_end_at DATETIME(3) NULL COMMENT '对方确认结束当地时间',
  appointment_start_utc DATETIME(3) NULL COMMENT '确认开始UTC时间点',
  appointment_end_utc DATETIME(3) NULL COMMENT '确认结束UTC时间点',
  timezone VARCHAR(64) NULL COMMENT '目的仓权威IANA时区，未维护不得确认',
  origin_warehouse_id VARCHAR(64) NOT NULL COMMENT '实际起运仓',
  version INT NOT NULL DEFAULT 0 COMMENT '并发版本',
  appointment_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING安排/SUCCESS对方确认/FAILED/CANCELLED',
  appointment_remark VARCHAR(1000) NULL COMMENT '备注或取消原因',
  fba_warehouse_code VARCHAR(128) NOT NULL COMMENT 'FBA目的仓编码',
  selected_count INT NOT NULL DEFAULT 0 COMMENT '真实货源票数',
  replacement_nos_json LONGTEXT NULL COMMENT '分批或合并后的新预约业务号列表，非空时原批次只读',
  replacement_keys_json LONGTEXT NULL COMMENT '替代批次不可变键列表，只用于内部业务关联',
  repartition_reason VARCHAR(1000) NULL COMMENT '分批或合并原因，原安排及来源明细保留',
  created_by VARCHAR(128) NOT NULL COMMENT '登录操作人',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建UTC时间',
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '更新UTC时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_ll_fba_appointment_no (appointment_no),
  KEY idx_ll_fba_appointment_list (appointment_at,appointment_status,fba_warehouse_code),
  KEY idx_ll_fba_appointment_planned (planned_at,appointment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='FBA预约安排与正式确认唯一主记录';

CREATE TABLE ll_inbound_fba_appointment_item (
  id BIGINT NOT NULL COMMENT '记录主键',
  appointment_id BIGINT NOT NULL COMMENT '预约主记录标识',
  source_key VARCHAR(160) NOT NULL COMMENT '稳定ORDER或BOOKING货源键',
  source_version VARCHAR(160) NULL COMMENT '保存时核实的来源版本',
  customer_id VARCHAR(128) NOT NULL COMMENT '本票所属客户',
  order_id BIGINT NULL COMMENT '尚未形成尾程订单时为空',
  inbound_code VARCHAR(128) NULL COMMENT '真实入仓码，不伪造',
  fba_warehouse_code VARCHAR(128) NOT NULL COMMENT '目的FBA仓编码',
  package_count INT NULL COMMENT '本批分配箱数，未知保持空；同一原来源总量守恒',
  cargo_verification_version INT NULL COMMENT '采用的预约货物核实版本，未核实时为空',
  shipment_references_json LONGTEXT NULL COMMENT '本次采用的货件或追踪号核实快照',
  active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '当前批次数量分配有效；取消、未约成和完成均保留，仅替代时释放',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建UTC时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_ll_fba_item_source (appointment_id,source_key),
  KEY idx_ll_fba_active_source (customer_id,source_key,active),
  KEY idx_ll_fba_active_order (customer_id,order_id,active),
  KEY idx_ll_fba_item_source_lookup (source_key,customer_id),
  KEY idx_ll_fba_item_customer_order (customer_id,order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='入仓前后共用的预约真实货源';

CREATE TABLE ll_appointment_cargo_verification (
  id BIGINT NOT NULL COMMENT '核实记录主键',
  customer_id VARCHAR(128) NOT NULL COMMENT '原货源所属客户',
  source_key VARCHAR(160) NOT NULL COMMENT '稳定ORDER或BOOKING原来源键',
  destination_key VARCHAR(160) NOT NULL COMMENT '核实时的原目的地',
  source_version VARCHAR(160) NULL COMMENT '核实时的原来源版本',
  source_packages INT NULL COMMENT '核实时的原来源箱数，未知为空',
  version INT NOT NULL COMMENT '同一来源的核实版本，从一递增',
  package_count INT NULL COMMENT '独立核实箱数，不回写原订单',
  shipment_references_json LONGTEXT NOT NULL COMMENT '本票货件号或追踪号列表，不回写原订单',
  remark VARCHAR(1000) NULL COMMENT '核实说明',
  operator_id VARCHAR(128) NOT NULL COMMENT '登录操作人',
  operator_name VARCHAR(200) NULL COMMENT '操作人名称快照',
  command_key VARCHAR(64) NOT NULL COMMENT '来源内幂等命令键',
  request_hash CHAR(64) NOT NULL COMMENT '来源、操作人及请求摘要',
  created_at DATETIME(3) NOT NULL COMMENT '核实UTC时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_ll_cargo_verification_version (customer_id,source_key,version),
  UNIQUE KEY uk_ll_cargo_verification_command (customer_id,source_key,command_key),
  CONSTRAINT chk_ll_cargo_verification_quantity CHECK (package_count IS NULL OR package_count > 0),
  CONSTRAINT chk_ll_cargo_verification_version CHECK (version > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预约货物核实资料及不可覆盖的版本历史';

CREATE TABLE ll_fba_appointment_command (
  command_key VARCHAR(128) NOT NULL COMMENT '持久化幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '操作、操作者和请求摘要',
  operation VARCHAR(96) NOT NULL COMMENT '操作类型及目标业务号',
  operator_id VARCHAR(128) NOT NULL COMMENT '登录操作人',
  appointment_no VARCHAR(64) NULL COMMENT '系统预约业务单号',
  response_json LONGTEXT NULL COMMENT '原命令返回快照，与业务写入同事务',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建UTC时间',
  PRIMARY KEY(command_key),
  KEY idx_ll_fba_command_appointment(appointment_no,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='预约命令与变更历史';