-- 本地服务结构补齐；仅创建缺失表、添加缺失字段，不删除现有数据。
SET NAMES utf8mb4;
CREATE TABLE fl_customs_broker_submission (
  id bigint unsigned NOT NULL COMMENT '首次发送报关行批次ID',
  customs_container_id bigint unsigned NOT NULL COMMENT '所属整柜报关聚合ID',
  loading_plan_id bigint unsigned NOT NULL COMMENT '装柜计划ID快照',
  batch_no varchar(64) NOT NULL COMMENT '首次发送报关行批次号',
  status varchar(16) NOT NULL COMMENT '发送状态：SENT已发送',
  source_version int unsigned NOT NULL COMMENT '发送时整柜报关聚合版本',
  source_snapshot_json json NOT NULL COMMENT '发送时冻结资料版本与覆盖范围快照',
  sent_by bigint unsigned NOT NULL COMMENT '首次发送操作人ID',
  sent_by_name varchar(64) DEFAULT NULL COMMENT '首次发送操作人名称快照',
  sent_at datetime(3) NOT NULL COMMENT '首次发送时间',
  version int unsigned NOT NULL DEFAULT '1' COMMENT '乐观锁版本号',
  created_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '批次创建时间',
  updated_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '批次更新时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_customs_broker_submission_container (customs_container_id),
  UNIQUE KEY uk_customs_broker_submission_batch (batch_no),
  KEY idx_customs_broker_submission_plan (loading_plan_id,sent_at),
  CONSTRAINT fk_customs_broker_submission_container FOREIGN KEY (customs_container_id) REFERENCES fl_customs_container (id),
  CONSTRAINT chk_customs_broker_submission_status CHECK ((status in (_utf8mb4'SENT')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='冻结退税报关资料首次发送报关行批次事实';

ALTER TABLE `fl_customs_completion_document` ADD COLUMN customer_confirm_status varchar(20) NOT NULL DEFAULT 'PENDING' COMMENT '客户回执确认状态：PENDING/CONFIRMED/REJECTED';

ALTER TABLE `fl_customs_completion_document` ADD COLUMN customer_confirmed_by bigint unsigned DEFAULT NULL COMMENT '客户确认人ID';

ALTER TABLE `fl_customs_completion_document` ADD COLUMN customer_confirmed_by_name varchar(64) DEFAULT NULL COMMENT '客户确认人名称快照';

ALTER TABLE `fl_customs_completion_document` ADD COLUMN customer_confirmed_at datetime(3) DEFAULT NULL COMMENT '客户确认时间';

ALTER TABLE `fl_customs_completion_document` ADD COLUMN customer_reject_reason varchar(500) DEFAULT NULL COMMENT '客户打回原因';

CREATE TABLE fl_customs_completion_action_history (
  id bigint unsigned NOT NULL COMMENT '客户回执动作历史ID',
  completion_document_id bigint unsigned NOT NULL COMMENT '报关行回执当前版本逻辑根ID',
  receipt_version int unsigned NOT NULL COMMENT '动作对应的回执文件版本',
  action varchar(16) NOT NULL COMMENT '客户动作：CONFIRM确认、REJECT打回',
  reason varchar(500) DEFAULT NULL COMMENT '客户打回原因；确认动作为空',
  operator_id bigint unsigned NOT NULL COMMENT '客户操作人ID',
  operator_name varchar(64) DEFAULT NULL COMMENT '客户操作人名称快照',
  created_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '客户动作时间',
  PRIMARY KEY (id),
  KEY idx_customs_completion_action_history (completion_document_id,receipt_version,created_at,id),
  CONSTRAINT fk_customs_completion_action_history_document FOREIGN KEY (completion_document_id) REFERENCES fl_customs_completion_document (id),
  CONSTRAINT chk_customs_completion_action_history_action CHECK ((action in (_utf8mb4'CONFIRM',_utf8mb4'REJECT')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='客户报关行回执确认与打回不可变历史';

CREATE TABLE fl_customs_declaration_batch (
  id BIGINT UNSIGNED NOT NULL COMMENT '正式申报批次ID',
  customs_container_id BIGINT UNSIGNED NOT NULL COMMENT '所属整柜报关聚合ID',
  loading_plan_id BIGINT UNSIGNED NOT NULL COMMENT '装柜计划ID快照',
  batch_no VARCHAR(64) NULL COMMENT '平台正式申报批次号',
  status VARCHAR(32) NOT NULL COMMENT '批次状态：READY/SUBMITTED/ACKNOWLEDGED/RESULT_PENDING/CLOSED/CANCELLED',
  source_version INT UNSIGNED NOT NULL COMMENT '创建时整柜报关聚合版本',
  source_snapshot_json JSON NOT NULL COMMENT '提交时资料、回执和确认版本快照',
  customs_entry_no VARCHAR(64) NULL COMMENT '报关单号',
  submitted_by BIGINT UNSIGNED NULL COMMENT '正式申报提交人',
  submitted_by_name VARCHAR(64) NULL COMMENT '正式申报提交人名称快照',
  submitted_at DATETIME(3) NULL COMMENT '正式申报提交时间',
  acknowledged_by BIGINT UNSIGNED NULL COMMENT '报关行确认受理人',
  acknowledged_by_name VARCHAR(64) NULL COMMENT '报关行确认受理人名称快照',
  acknowledged_at DATETIME(3) NULL COMMENT '报关行确认受理时间',
  remark VARCHAR(500) NULL COMMENT '批次说明',
  version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '批次创建时间',
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '批次更新时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_customs_declaration_batch_container(customs_container_id, id),
  KEY idx_customs_declaration_batch_plan(loading_plan_id,status,created_at),
  CONSTRAINT fk_customs_declaration_batch_container FOREIGN KEY(customs_container_id) REFERENCES fl_customs_container(id),
  CONSTRAINT chk_customs_declaration_batch_status CHECK(status IN ('READY','SUBMITTED','ACKNOWLEDGED','RESULT_PENDING','CLOSED','CANCELLED'))
) ENGINE=InnoDB COMMENT='整柜正式申报批次事实';

CREATE TABLE fl_customs_clearance_result (
  id BIGINT UNSIGNED NOT NULL COMMENT '海关结果事实ID',
  declaration_batch_id BIGINT UNSIGNED NOT NULL COMMENT '所属正式申报批次ID',
  customs_container_id BIGINT UNSIGNED NOT NULL COMMENT '所属整柜报关聚合ID',
  result_type VARCHAR(32) NOT NULL COMMENT '结果类型：INSPECTION/SUPPLEMENT_REQUIRED/RELEASED/DETAINED/OTHER',
  result_status VARCHAR(16) NOT NULL DEFAULT 'OPEN' COMMENT '结果状态：OPEN/RESOLVED/CANCELLED',
  customs_entry_no VARCHAR(64) NULL COMMENT '报关单号快照',
  occurred_at DATETIME(3) NOT NULL COMMENT '海关事实发生时间',
  resolution VARCHAR(1000) NULL COMMENT '处理结果或异常说明',
  evidence_file_id BIGINT UNSIGNED NULL COMMENT '结果凭证File文件ID',
  recorded_by BIGINT UNSIGNED NOT NULL COMMENT '平台录入人',
  recorded_by_name VARCHAR(64) NULL COMMENT '平台录入人名称快照',
  recorded_at DATETIME(3) NOT NULL COMMENT '平台录入时间',
  previous_result_id BIGINT UNSIGNED NULL COMMENT '前序结果事实ID',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '结果创建时间',
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '结果更新时间',
  PRIMARY KEY(id),
  KEY idx_customs_clearance_result_batch(declaration_batch_id,occurred_at,id),
  KEY idx_customs_clearance_result_container(customs_container_id, result_type, result_status),
  CONSTRAINT fk_customs_clearance_result_batch FOREIGN KEY(declaration_batch_id) REFERENCES fl_customs_declaration_batch(id),
  CONSTRAINT chk_customs_clearance_result_type CHECK(result_type IN ('INSPECTION','SUPPLEMENT_REQUIRED','RELEASED','DETAINED','OTHER')),
  CONSTRAINT chk_customs_clearance_result_status CHECK(result_status IN ('OPEN','RESOLVED','CANCELLED'))
) ENGINE=InnoDB COMMENT='正式申报后的海关结果不可变事实';

ALTER TABLE `fl_fee_item` ADD COLUMN default_price decimal(12,2) DEFAULT NULL COMMENT '默认价格CNY；仅选费带入，实际费用可修改';

CREATE TABLE fl_chargeable_weight_confirmation (
 id BIGINT NOT NULL COMMENT '确认记录ID',
 tenant_id BIGINT NOT NULL COMMENT '客户隔离ID',
 inbound_id BIGINT NOT NULL COMMENT '独立来源入仓单ID',
 operator_id BIGINT NOT NULL COMMENT '确认客户成员ID',
 command_key VARCHAR(128) NOT NULL COMMENT '成员确认幂等键',
 fingerprint CHAR(64) NOT NULL COMMENT '计费重及测量轮次摘要',
 chargeable_weight_kg DECIMAL(18,4) NOT NULL COMMENT '客户确认计费重千克',
 confirmed_at DATETIME(3) NOT NULL COMMENT '客户确认时间',
 PRIMARY KEY(id), UNIQUE KEY uk_weight_confirmation_command(tenant_id,operator_id,command_key),
 KEY idx_weight_confirmation_inbound(tenant_id,inbound_id,id)
) ENGINE=InnoDB COMMENT='客户计费重独立确认历史';

CREATE TABLE fl_sailing_channel (
  id BIGINT NOT NULL,
  channel_code VARCHAR(64) NOT NULL,
  channel_name VARCHAR(128) NOT NULL,
  source_type VARCHAR(24) NOT NULL,
  mapped_channel_code VARCHAR(64) NULL,
  default_departure_port_code VARCHAR(32) NULL,
  default_destination_port_code VARCHAR(32) NULL,
  default_cutoff_days TINYINT NOT NULL DEFAULT 3,
  status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
  remark VARCHAR(1000) NULL,
  created_by BIGINT NOT NULL,
  created_by_name VARCHAR(128) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_by BIGINT NOT NULL,
  updated_by_name VARCHAR(128) NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  version INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_fl_sailing_channel_code (channel_code),
  KEY idx_fl_sailing_channel_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fl_sailing_template (
  id BIGINT NOT NULL,
  template_no VARCHAR(64) NOT NULL,
  template_name VARCHAR(128) NOT NULL,
  channel_id BIGINT NOT NULL,
  channel_code_snapshot VARCHAR(64) NOT NULL,
  channel_name_snapshot VARCHAR(128) NOT NULL,
  warehouse_id BIGINT NOT NULL,
  warehouse_name_snapshot VARCHAR(128) NULL,
  departure_port_code VARCHAR(32) NOT NULL,
  destination_port_code VARCHAR(32) NOT NULL,
  weekday TINYINT NOT NULL,
  weekday_mask TINYINT NOT NULL DEFAULT 1,
  etd_local_time TIME NOT NULL,
  cutoff_days TINYINT NOT NULL DEFAULT 3,
  cutoff_local_time TIME NOT NULL DEFAULT '18:00:00',
  valid_from DATE NOT NULL,
  valid_to DATE NULL,
  voyage_rule VARCHAR(64) NULL,
  vessel_name VARCHAR(128) NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
  remark VARCHAR(1000) NULL,
  created_by BIGINT NOT NULL,
  created_by_name VARCHAR(128) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_by BIGINT NOT NULL,
  updated_by_name VARCHAR(128) NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  version INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_fl_sailing_template_no (template_no),
  KEY idx_fl_sailing_template_filter (warehouse_id,channel_id,status,weekday),
  CONSTRAINT ck_fl_sailing_template_weekday CHECK (weekday BETWEEN 1 AND 7),
  CONSTRAINT ck_fl_sailing_template_cutoff_days CHECK (cutoff_days BETWEEN 0 AND 30)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fl_sailing (
  id BIGINT NOT NULL,
  sailing_no VARCHAR(64) NOT NULL,
  template_id BIGINT NULL,
  channel_id BIGINT NOT NULL,
  channel_code_snapshot VARCHAR(64) NOT NULL,
  channel_name_snapshot VARCHAR(128) NOT NULL,
  warehouse_id BIGINT NOT NULL,
  warehouse_name_snapshot VARCHAR(128) NULL,
  departure_port_code VARCHAR(32) NOT NULL,
  destination_port_code VARCHAR(32) NOT NULL,
  vessel_name VARCHAR(128) NULL,
  voyage_no VARCHAR(64) NULL,
  etd_at DATETIME(3) NOT NULL,
  warehouse_cutoff_at DATETIME(3) NOT NULL,
  eta_at DATETIME(3) NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'OPEN',
  source_type VARCHAR(24) NOT NULL DEFAULT 'MANUAL',
  generated_key VARCHAR(192) NULL,
  planned_capacity_cbm DECIMAL(14,3) NULL,
  remark VARCHAR(1000) NULL,
  created_by BIGINT NOT NULL,
  created_by_name VARCHAR(128) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_by BIGINT NOT NULL,
  updated_by_name VARCHAR(128) NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  version INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_fl_sailing_no (sailing_no),
  UNIQUE KEY uk_fl_sailing_generated_key (generated_key),
  UNIQUE KEY uk_fl_sailing_business (warehouse_id,channel_code_snapshot,departure_port_code,destination_port_code,etd_at,voyage_no),
  KEY idx_fl_sailing_calendar (etd_at,warehouse_id,channel_id,status),
  KEY idx_fl_sailing_route (departure_port_code,destination_port_code),
  CONSTRAINT ck_fl_sailing_status CHECK (status IN ('DRAFT','OPEN','CLOSED','DEPARTED','CANCELLED','COMPLETED'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fl_sailing_history (
  id BIGINT NOT NULL,
  sailing_id BIGINT NOT NULL,
  action VARCHAR(32) NOT NULL,
  from_status VARCHAR(16) NULL,
  to_status VARCHAR(16) NULL,
  reason VARCHAR(1000) NULL,
  operator_id BIGINT NULL,
  operator_name VARCHAR(128) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_fl_sailing_history_sailing (sailing_id,created_at,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fl_sailing_receipt_batch_fact (
  id BIGINT UNSIGNED NOT NULL COMMENT '稳定收货批次事实ID（receiptBatchId）',
  tenant_id BIGINT UNSIGNED NOT NULL COMMENT '客户隔离键，继承入库事实',
  receipt_batch_key VARCHAR(64) NOT NULL COMMENT '同一物理收货批次的不可变业务键',
  fact_version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '同一批次复测/冲正版本',
  inbound_id BIGINT UNSIGNED NOT NULL COMMENT '入库单ID',
  measurement_id BIGINT UNSIGNED NULL COMMENT '最终审核测量批次ID',
  receiving_warehouse_id BIGINT UNSIGNED NOT NULL COMMENT '实际收货仓ID',
  channel_code VARCHAR(64) NULL COMMENT '收货渠道编码快照',
  channel_name VARCHAR(128) NULL COMMENT '收货渠道名称快照',
  departure_port_code VARCHAR(32) NULL COMMENT '起运港编码快照',
  destination_port_code VARCHAR(32) NULL COMMENT '目的港编码快照',
  received_at DATETIME(3) NOT NULL COMMENT '实际收货完成时间，不使用入库单inbounded_at替代',
  actual_volume_cbm DECIMAL(18,4) NOT NULL COMMENT '最终审核实测体积m3',
  fact_status VARCHAR(24) NOT NULL DEFAULT 'APPROVED' COMMENT '事实状态：APPROVED/REVERSED/VOIDED/SUPERSEDED',
  reversal_of_fact_id BIGINT UNSIGNED NULL COMMENT '冲正所替代的事实ID',
  source_type VARCHAR(32) NOT NULL DEFAULT 'FIRSTLEG_RECEIPT' COMMENT '事实来源',
  created_by BIGINT UNSIGNED NULL COMMENT '写入人或系统主体',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_sailing_receipt_batch_version (tenant_id, receipt_batch_key, fact_version),
  KEY idx_sailing_receipt_batch_current (tenant_id, receipt_batch_key, fact_status, fact_version),
  KEY idx_sailing_receipt_match (tenant_id, receiving_warehouse_id, channel_code, received_at, id),
  KEY idx_sailing_receipt_inbound (tenant_id, inbound_id, received_at, id),
  CONSTRAINT chk_sailing_receipt_volume CHECK (actual_volume_cbm > 0),
  CONSTRAINT chk_sailing_receipt_status CHECK (fact_status IN ('APPROVED','REVERSED','VOIDED','SUPERSEDED'))
) ENGINE=InnoDB COMMENT='船期日历稳定收货批次实测事实及版本历史';

CREATE TABLE fl_sailing_inbound_volume_forecast (
  id BIGINT UNSIGNED NOT NULL COMMENT '货量预报事实ID（forecastId）',
  tenant_id BIGINT UNSIGNED NOT NULL COMMENT '客户隔离键，继承入库事实',
  forecast_key VARCHAR(64) NOT NULL COMMENT '同一未收货对象的不可变业务键',
  fact_version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '预报修订版本',
  inbound_id BIGINT UNSIGNED NOT NULL COMMENT '入库单ID',
  receiving_warehouse_id BIGINT UNSIGNED NOT NULL COMMENT '预计收货仓ID',
  channel_code VARCHAR(64) NULL COMMENT '预计渠道编码快照',
  channel_name VARCHAR(128) NULL COMMENT '预计渠道名称快照',
  departure_port_code VARCHAR(32) NULL COMMENT '预计起运港编码快照',
  destination_port_code VARCHAR(32) NULL COMMENT '预计目的港编码快照',
  expected_arrival_at DATETIME(3) NOT NULL COMMENT '预计到仓时间',
  forecast_volume_cbm DECIMAL(18,4) NOT NULL COMMENT '未收货预计体积m3，不得使用申报体积代替',
  source_type VARCHAR(32) NOT NULL COMMENT '预报来源，例如OPERATIONS/UPSTREAM_MEASUREMENT；不得使用客户申报体积冒充预报',
  fact_status VARCHAR(24) NOT NULL DEFAULT 'VALID' COMMENT '事实状态：VALID/SUPERSEDED/VOIDED/CONSUMED',
  effective_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '本版本生效时间',
  created_by BIGINT UNSIGNED NULL COMMENT '写入人或系统主体',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_sailing_forecast_version (tenant_id, forecast_key, fact_version),
  KEY idx_sailing_forecast_current (tenant_id, forecast_key, fact_status, fact_version),
  KEY idx_sailing_forecast_match (tenant_id, receiving_warehouse_id, channel_code, expected_arrival_at, id),
  KEY idx_sailing_forecast_inbound (tenant_id, inbound_id, expected_arrival_at, id),
  CONSTRAINT chk_sailing_forecast_volume CHECK (forecast_volume_cbm > 0),
  CONSTRAINT chk_sailing_forecast_status CHECK (fact_status IN ('VALID','SUPERSEDED','VOIDED','CONSUMED'))
) ENGINE=InnoDB COMMENT='船期日历未收货量预报事实及版本历史';

CREATE TABLE fl_sailing_assignment (
  id BIGINT UNSIGNED NOT NULL COMMENT '归班事实ID',
  tenant_id BIGINT UNSIGNED NOT NULL COMMENT '客户隔离键',
  sailing_id BIGINT UNSIGNED NULL COMMENT '船期实例ID，UNMATCHED为空',
  origin_sailing_id BIGINT UNSIGNED NULL COMMENT '异常重排/取消前的原船期实例ID',
  inbound_id BIGINT UNSIGNED NULL COMMENT '归班对象入库单ID',
  inbound_code VARCHAR(64) NULL COMMENT '入仓码快照，仅用于展示',
  receipt_batch_id BIGINT UNSIGNED NULL COMMENT '收货批次事实ID',
  forecast_id BIGINT UNSIGNED NULL COMMENT '货量预报事实ID',
  assignment_source VARCHAR(24) NOT NULL DEFAULT 'AUTO' COMMENT '归班来源：AUTO/MANUAL/ROLLOVER',
  assignment_version INT UNSIGNED NOT NULL COMMENT '同一对象的归班版本',
  match_status VARCHAR(24) NOT NULL COMMENT 'FORECAST_MATCHED/RECEIVED/LATE/UNMATCHED/SUPERSEDED',
  volume_cbm DECIMAL(18,4) NULL COMMENT '已收实测体积',
  forecast_volume_cbm DECIMAL(18,4) NULL COMMENT '未收有效预计体积',
  route_source VARCHAR(32) NULL COMMENT '航线来源：INBOUND_FACT/FORECAST_FACT/CHANNEL_DEFAULT',
  volume_source VARCHAR(32) NULL COMMENT 'MEASURED_ACTUAL或预报来源',
  rule_version VARCHAR(64) NOT NULL COMMENT '自动归班规则版本',
  matched_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  superseded_at DATETIME(3) NULL,
  locked_at DATETIME(3) NULL COMMENT '截止/订舱/关闭后冻结时间',
  lock_reason VARCHAR(32) NULL COMMENT 'CUTOFF/BOOKING_CONFIRMED/CLOSED',
  operator_id BIGINT UNSIGNED NULL,
  reason VARCHAR(500) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_sailing_assignment_current (tenant_id, sailing_id, match_status, matched_at, id),
  KEY idx_sailing_assignment_origin (tenant_id, origin_sailing_id, match_status, id),
  KEY idx_sailing_assignment_receipt (tenant_id, receipt_batch_id, assignment_version, id),
  KEY idx_sailing_assignment_forecast (tenant_id, forecast_id, assignment_version, id),
  KEY idx_sailing_assignment_inbound (tenant_id, inbound_id, match_status, id),
  CONSTRAINT chk_sailing_assignment_status CHECK (match_status IN ('FORECAST_MATCHED','RECEIVED','LATE','UNMATCHED','SUPERSEDED')),
  CONSTRAINT chk_sailing_assignment_source CHECK (assignment_source IN ('AUTO','MANUAL','ROLLOVER'))
) ENGINE=InnoDB COMMENT='船期日历自动归班事实及不可变版本历史';

CREATE TABLE fl_sailing_booking_link (
  id BIGINT UNSIGNED NOT NULL COMMENT '船期-订舱关联ID',
  tenant_id BIGINT UNSIGNED NOT NULL COMMENT '平台关联隔离键',
  sailing_id BIGINT UNSIGNED NOT NULL COMMENT '计划船期实例ID',
  ocean_booking_id BIGINT UNSIGNED NOT NULL COMMENT '海运订舱ID',
  link_status VARCHAR(24) NOT NULL DEFAULT 'ACTIVE' COMMENT '关联状态：ACTIVE/REVOKED',
  active_booking_guard TINYINT NULL COMMENT 'ACTIVE为1，REVOKED为空，用于唯一约束',
  booking_status_snapshot VARCHAR(24) NOT NULL COMMENT '关联时订舱状态快照；只有RELEASED可建立',
  warehouse_id BIGINT UNSIGNED NULL COMMENT '关联仓库快照',
  channel_code VARCHAR(64) NULL COMMENT '关联渠道快照',
  departure_port_code VARCHAR(32) NULL COMMENT '关联起运港快照',
  destination_port_code VARCHAR(32) NULL COMMENT '关联目的港快照',
  linked_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  revoked_at DATETIME(3) NULL,
  operator_id BIGINT UNSIGNED NULL,
  operator_name VARCHAR(64) NULL,
  reason VARCHAR(500) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sailing_booking_active (ocean_booking_id, active_booking_guard),
  KEY idx_sailing_booking_link (sailing_id, link_status, ocean_booking_id),
  KEY idx_sailing_booking_booking (ocean_booking_id, link_status, id),
  CONSTRAINT chk_sailing_booking_link_status CHECK (link_status IN ('ACTIVE','REVOKED')),
  CONSTRAINT chk_sailing_booking_link_guard CHECK ((link_status='ACTIVE' AND active_booking_guard=1) OR (link_status='REVOKED' AND active_booking_guard IS NULL)),
  CONSTRAINT chk_sailing_booking_released CHECK (booking_status_snapshot='RELEASED')
) ENGINE=InnoDB COMMENT='船期计划与已放舱订舱的最小关联及历史';

CREATE TABLE event_delivery_retry_audit (
  request_id VARCHAR(36) NOT NULL COMMENT '人工重试请求UUID和幂等键',
  producer_table VARCHAR(64) NOT NULL COMMENT '本服务发件箱表名',
  entry_id VARCHAR(64) NOT NULL COMMENT '发件箱记录主键',
  event_id VARCHAR(128) NOT NULL COMMENT '原事件全局标识',
  event_type VARCHAR(64) NOT NULL COMMENT '原事件类型',
  payload_hash VARCHAR(71) NOT NULL COMMENT '原事件不可变负载摘要',
  operator_name VARCHAR(128) NOT NULL COMMENT '持有运维凭据的操作人标识',
  reason VARCHAR(500) NOT NULL COMMENT '人工重试原因',
  previous_failures INT NOT NULL COMMENT '重试前累计失败次数',
  previous_error VARCHAR(1000) NULL COMMENT '重试前最后一次脱敏错误',
  requested_at DATETIME(3) NOT NULL COMMENT '重试申请提交时间',
  PRIMARY KEY (request_id),
  KEY idx_event_retry_entry (producer_table,entry_id,requested_at),
  KEY idx_event_retry_event (event_id,requested_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务事件人工重试审计，与恢复待发送状态同事务提交';