-- 打单价卡独立四表 + 重量段 fee_type 字典（可重复执行）
-- 依据 docs/price/prc_label_schema.md
-- 重量段：fee_type / calc_param；charge_mode 对齐 calc_rule；无 handle_*
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `prc_label_product` (
  `product_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '产品ID',
  `product_name` varchar(200) NOT NULL COMMENT '产品名称',
  `dict_value` varchar(100) NOT NULL COMMENT '字典 payment_product_type',
  `country_scope` varchar(200) DEFAULT NULL COMMENT '国家或口岸',
  `charge_type` varchar(50) DEFAULT NULL COMMENT '订单收费类型，字典 order_charge_type',
  `extra_per_kg` varchar(40) DEFAULT NULL COMMENT '续重每公斤加价文案',
  `service_type` varchar(100) DEFAULT NULL COMMENT '服务类型',
  `classification` varchar(100) DEFAULT NULL COMMENT '目录分类',
  `catalog_remark` varchar(1000) DEFAULT NULL COMMENT '目录备注',
  `update_content` varchar(500) DEFAULT NULL COMMENT '目录更新内容',
  `weight_limit` varchar(100) DEFAULT NULL COMMENT '目录重量限制',
  `sheet_name` varchar(200) NOT NULL COMMENT '报价工作表名',
  `related_sheet` varchar(500) DEFAULT NULL COMMENT '关联分区或附加表名',
  `source_file` varchar(500) NOT NULL COMMENT '来源文件名',
  `file_path` varchar(500) DEFAULT NULL COMMENT '源文件OSS地址，oss://bucket/key',
  `file_md5` char(32) NOT NULL COMMENT '来源文件MD5',
  `supplier_name` varchar(100) DEFAULT NULL COMMENT '价卡归属',
  `currency` varchar(20) DEFAULT NULL COMMENT '报价币种',
  `service_note` varchar(4000) DEFAULT NULL COMMENT '服务说明',
  `parse_status` varchar(20) NOT NULL DEFAULT 'OK' COMMENT '识别结果：OK/EMPTY/MISSING',
  `weight_count` int(11) NOT NULL DEFAULT 0 COMMENT '重量段行数缓存',
  `surcharge_count` int(11) NOT NULL DEFAULT 0 COMMENT '附加费行数缓存',
  `created_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`product_id`),
  KEY `idx_lbl_prod_dict` (`dict_value`),
  KEY `idx_lbl_prod_name` (`product_name`),
  KEY `idx_lbl_prod_file` (`file_md5`, `sheet_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='打单价卡产品';

CREATE TABLE IF NOT EXISTS `prc_label_product_version` (
  `version_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '版本周期ID',
  `price_set_id` bigint(20) NOT NULL COMMENT '打单套餐ID，对应 prc_price_set.price_set_id',
  `product_id` bigint(20) NOT NULL COMMENT '产品ID，对应 prc_label_product.product_id',
  `dict_value` varchar(100) NOT NULL COMMENT '字典 payment_product_type',
  `version_no` int(11) NOT NULL DEFAULT 1 COMMENT '套餐级版本号',
  `effective_start` date DEFAULT NULL COMMENT '生效开始',
  `effective_end` date DEFAULT NULL COMMENT '生效结束，空表示不限',
  `file_path` varchar(500) DEFAULT NULL COMMENT '本版源文件OSS地址',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '0待生效 1生效 2已失效',
  `created_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`version_id`),
  KEY `idx_lbl_ver_set_prod_time` (`price_set_id`, `product_id`, `effective_start`, `effective_end`),
  KEY `idx_lbl_ver_set_ver` (`price_set_id`, `version_no`),
  KEY `idx_lbl_ver_prod_set` (`product_id`, `price_set_id`),
  KEY `idx_lbl_ver_dict_status` (`dict_value`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='打单价卡产品版本周期';

CREATE TABLE IF NOT EXISTS `prc_label_weight_band` (
  `band_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '重量段ID',
  `product_id` bigint(20) NOT NULL COMMENT '产品ID',
  `version_id` bigint(20) NOT NULL COMMENT '版本周期ID',
  `fee_type` varchar(30) NOT NULL COMMENT '费用类型，字典 bms_fee_type（FREIGHT/处理费REGISTRATION/偏远REMOTE）',
  `country_name` varchar(200) DEFAULT NULL COMMENT '国家',
  `country_code` varchar(32) DEFAULT NULL COMMENT '国家二字码',
  `zone_code` varchar(64) DEFAULT NULL COMMENT '分区编码',
  `zone_name` varchar(200) DEFAULT NULL COMMENT '分区名称',
  `postcode_param` varchar(1000) DEFAULT NULL COMMENT '邮编匹配规则，如07或35,38',
  `weight_label` varchar(100) DEFAULT NULL COMMENT '重量段原文',
  `weight_min` decimal(10,3) DEFAULT NULL COMMENT '重量下限KG',
  `weight_max` decimal(10,3) DEFAULT NULL COMMENT '重量上限KG或首重阈值',
  `volume_text` varchar(40) DEFAULT NULL COMMENT '体积或尺寸限制原文',
  `charge_name` varchar(200) DEFAULT NULL COMMENT '费用项名',
  `charge_mode` varchar(30) NOT NULL DEFAULT 'ITEM' COMMENT '计费条件，字典 lbl_charge_mode：ITEM/按票 PER_KG/单价*公斤 EXTRA_KG/首续重 POSTCODE/邮编',
  `calc_param` varchar(1000) DEFAULT NULL COMMENT '条件参数，对齐 surcharge.calc_param',
  `price` decimal(12,4) DEFAULT NULL COMMENT '金额：一票价/公斤单价/首重价',
  `price_unit` varchar(40) DEFAULT NULL COMMENT '金额单位',
  `extra_price` decimal(12,4) DEFAULT NULL COMMENT '续重单价，charge_mode=EXTRA_KG 时使用',
  `extra_price_unit` varchar(40) DEFAULT NULL COMMENT '续重单位',
  `sort_no` int(11) NOT NULL DEFAULT 0 COMMENT '排序',
  PRIMARY KEY (`band_id`),
  UNIQUE KEY `uk_lbl_band_dim` (`version_id`, `product_id`, `country_code`, `zone_code`, `fee_type`, `weight_min`, `weight_max`),
  KEY `idx_lbl_band_version` (`version_id`),
  KEY `idx_lbl_band_prod_ver` (`product_id`, `version_id`),
  KEY `idx_lbl_band_fee_type` (`fee_type`),
  KEY `idx_lbl_band_dim` (`version_id`, `product_id`, `country_code`, `zone_code`, `fee_type`, `weight_min`, `weight_max`),
  KEY `idx_lbl_band_charge_mode` (`charge_mode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='打单价卡重量段明细';

CREATE TABLE IF NOT EXISTS `prc_label_surcharge` (
  `surcharge_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '附加费行ID',
  `product_id` bigint(20) NOT NULL COMMENT '产品ID',
  `version_id` bigint(20) NOT NULL COMMENT '版本周期ID',
  `fee_name` varchar(200) NOT NULL COMMENT '费用名称',
  `sc_id` bigint(20) DEFAULT NULL COMMENT '附加费配置ID，对应 prc_surcharge.sc_id',
  `zone_code` varchar(64) DEFAULT NULL COMMENT '适用分区编码',
  `zone_name` varchar(200) DEFAULT NULL COMMENT '适用分区名称',
  `charge_text` varchar(500) DEFAULT NULL COMMENT '收费标准原文',
  `amount` decimal(12,4) DEFAULT NULL COMMENT '金额或百分数',
  `currency` varchar(20) DEFAULT NULL COMMENT '币种',
  `price_unit` varchar(40) DEFAULT NULL COMMENT '货币和计费单位',
  `fee_desc` varchar(2000) DEFAULT NULL COMMENT '费用说明',
  `period_text` varchar(500) DEFAULT NULL COMMENT '时效原文',
  `effective_start` date DEFAULT NULL COMMENT '附加费生效开始',
  `effective_end` date DEFAULT NULL COMMENT '附加费生效结束',
  `charge_timing` varchar(40) DEFAULT NULL COMMENT '收取时点',
  `calc_rule` varchar(20) NOT NULL DEFAULT 'MANUAL' COMMENT '计费条件',
  `calc_param` varchar(1000) DEFAULT NULL COMMENT '条件参数',
  `postcode_param` varchar(1000) DEFAULT NULL COMMENT '偏远邮编规则',
  `weight_min` decimal(12,3) DEFAULT NULL COMMENT '适用重量下限KG',
  `weight_max` decimal(12,3) DEFAULT NULL COMMENT '适用重量上限KG',
  `sort_no` int(11) NOT NULL DEFAULT 0 COMMENT '排序',
  PRIMARY KEY (`surcharge_id`),
  KEY `idx_lbl_sc_version` (`version_id`),
  KEY `idx_lbl_sc_prod_ver` (`product_id`, `version_id`),
  KEY `idx_lbl_sc_scid` (`sc_id`),
  KEY `idx_lbl_sc_zone` (`zone_code`),
  KEY `idx_lbl_sc_period` (`effective_start`, `effective_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='打单价卡附加费明细';

-- 字典 bms_fee_type：打单重量段用 FREIGHT / REGISTRATION / REMOTE
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 10, '运费', 'FREIGHT', 'bms_fee_type', '', 'primary', 'N', '0', 'admin', NOW(), '打单重量段运费（与 BASIC_FREIGHT 并存）'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type='bms_fee_type')
  AND NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type='bms_fee_type' AND dict_value='FREIGHT');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 20, '处理费', 'REGISTRATION', 'bms_fee_type', '', 'success', 'N', '0', 'admin', NOW(), '打单重量段处理费'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type='bms_fee_type')
  AND NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type='bms_fee_type' AND dict_value='REGISTRATION');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 40, '偏远费', 'REMOTE', 'bms_fee_type', '', 'warning', 'N', '0', 'admin', NOW(), '打单重量段偏远费'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type='bms_fee_type')
  AND NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type='bms_fee_type' AND dict_value='REMOTE');

-- 字典 lbl_charge_mode：打单重量段计费条件
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '打单计费条件', 'lbl_charge_mode', '0', 'admin', NOW(), 'prc_label_weight_band.charge_mode'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'lbl_charge_mode');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT v.dict_sort, v.dict_label, v.dict_value, 'lbl_charge_mode', '', v.list_class, v.is_default, '0', 'admin', NOW(), v.remark
FROM (
  SELECT 1 AS dict_sort, '按票' AS dict_label, 'ITEM' AS dict_value, 'primary' AS list_class, 'Y' AS is_default, '一票固定价' AS remark
  UNION ALL SELECT 2, '单价*公斤数', 'PER_KG', 'success', 'N', '单价×计费重'
  UNION ALL SELECT 3, '首重+续重', 'EXTRA_KG', 'warning', 'N', 'price+续重单价×超出公斤'
  UNION ALL SELECT 4, '邮编', 'POSTCODE', 'info', 'N', 'postcode_param 命中后按票收'
) v
WHERE NOT EXISTS (
  SELECT 1 FROM sys_dict_data d WHERE d.dict_type = 'lbl_charge_mode' AND d.dict_value = v.dict_value
);