-- 供应商查询缺失结构修复。MySQL 8；仅用于本次明确授权的本机开发库。
-- 固定目标 baiyue_warehouse；不修改供应商、费用、余额或授信数据。
-- 已有结构安全跳过；不推断历史拖车供应商的国内/海外类别。
-- DDL 会隐式提交，请在独立连接以遇错停止模式整份执行。
USE baiyue_warehouse;
SET NAMES utf8mb4;
SELECT DATABASE() AS target_database;

-- 先确认正式供应商主表存在；不存在时 ALTER 会报错并停止，不创建第二份主档。
SET @supplier_query_column_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns
          WHERE table_schema = 'baiyue_warehouse'
            AND table_name = 'wh_supplier' AND column_name = 'trucking_scope'),
  'SELECT ''trucking_scope 已存在，跳过'' AS structure_result',
  'ALTER TABLE baiyue_warehouse.wh_supplier ADD COLUMN trucking_scope VARCHAR(16) NULL COMMENT ''拖车类型：DOMESTIC国内拖车、OVERSEAS海外车队；非拖车为空'' AFTER category'
);
PREPARE supplier_query_statement FROM @supplier_query_column_ddl;
EXECUTE supplier_query_statement;
DEALLOCATE PREPARE supplier_query_statement;

-- 正式列表还会读取船司关联；若未初始化，仅按当前模块基线建立空表。
CREATE TABLE IF NOT EXISTS wh_supplier_carrier (
  supplier_id BIGINT UNSIGNED NOT NULL COMMENT 'BOOKING供应商ID',
  tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '平台公共作用域，固定为0',
  carrier_code VARCHAR(16) NOT NULL COMMENT '船司标准编码',
  created_by BIGINT UNSIGNED NULL COMMENT '创建人ID',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  PRIMARY KEY (supplier_id,carrier_code),
  KEY idx_wh_supplier_carrier_code (tenant_id,carrier_code,supplier_id),
  CONSTRAINT chk_wh_supplier_carrier_tenant CHECK (tenant_id = 0),
  CONSTRAINT chk_wh_supplier_carrier_code CHECK (carrier_code IN ('MATSON','SML','ZIM','COSCO','EMC','HEDE','OTHER'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BOOKING供应商与标准船司编码多对多关联';

-- 执行后只核对结构，不读取供应商联系信息。
SELECT table_name, column_name, column_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'baiyue_warehouse'
  AND ((table_name = 'wh_supplier' AND column_name = 'trucking_scope')
       OR table_name = 'wh_supplier_carrier')
ORDER BY table_name, ordinal_position;