-- 短信模板
ALTER TABLE system_sms_template ADD node_value varchar(100) comment '节点',add transport_id int comment '运输方式',add message_type tinyint default 1 comment '发送类型',add language varchar(100) comment '语言';
ALTER TABLE system_sms_channel MODIFY api_secret VARCHAR(200) COMMENT '短信 API 的秘钥';

-- 新增数据字典渠道类型
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (3, 'Sendchamp', 'SENDCHAMP', 'system_sms_channel_code', 0, 'primary', '', NULL, '2741', '2024-10-28 09:11:18', '2741', '2024-10-28 09:12:13', b'0', 'Sendchamp');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (4, 'Ycloud', 'YCLOUD', 'system_sms_channel_code', 0, 'primary', '', NULL, '2741', '2024-10-28 09:12:55', '2741', '2024-10-28 09:12:55', b'0', 'Ycloud');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (5, 'Bulk', 'BULK', 'system_sms_channel_code', 0, 'primary', '', NULL, '2741', '2024-10-28 09:12:55', '2741', '2024-10-28 09:12:55', b'0', 'Bulk');

-- 新增渠道
INSERT INTO `jiedao`.`system_sms_channel`(`signature`, `code`, `status`, `remark`, `api_key`, `api_secret`, `callback_url`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `signature_en`) VALUES ('sendchamp', 'SENDCHAMP', 0, '', 'ecit@ewchina.net', 'sendchamp_live_$2a$10$vQPdaDjl96Ybc5tzFmZYg.nqGirXuJBGDqJArthZnFR8P9mM5Z/JO', '', '2741', '2024-10-26 17:06:44', '2741', '2024-10-28 15:16:39', b'0', 'sendchamp');
INSERT INTO `jiedao`.`system_sms_channel`(`signature`, `code`, `status`, `remark`, `api_key`, `api_secret`, `callback_url`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `signature_en`) VALUES ('ycloud', 'YCLOUD', 0, NULL, 'ecit@ewchina.net', '9dbd912f56c101e53b23cb7b758ffda8', NULL, '2741', '2024-10-26 17:12:50', '2741', '2024-10-28 15:16:18', b'0', 'ycloud');
INSERT INTO `jiedao`.`system_sms_channel`(`signature`, `code`, `status`, `remark`, `api_key`, `api_secret`, `callback_url`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `signature_en`) VALUES ('bulk', 'BULK', 0, NULL, 'ecit@ewchina.net', '0z@@vtj1h', NULL, '2741', '2024-10-26 17:12:50', '2741', '2024-10-28 15:16:18', b'0', 'bulk');

-- 短信日志新增字段
alter table system_sms_log add node_id bigint comment '节点id',add node_template_sn int default 1 comment '节点模板序列号',add message_type tinyint comment '发送类型';

-- 短信验证码
ALTER TABLE system_sms_code CHANGE scene node_value varchar(100) not NULL comment '节点';
ALTER TABLE ecw_box_order_sms_log CHANGE scene node_value varchar(100) not NULL comment '节点';

/*==============================================================*/
/* Table: system_sms_node                                       */
/*==============================================================*/
create table system_sms_node
(
   id                   bigint(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
   node_value           varchar(100) not null comment '节点',
   is_transport			tinyint not null comment '是否匹配运输方式(0:否，1：是)',
   transport_id         tinyint not null comment '运输方式ID',
   is_orders			tinyint not null comment '多订单(0:否，1：是)',
   country_id           varchar(600) not null comment '国家区号id',
   country_code         varchar(600) not null comment '国家区号，和订单中国家区号保持一致',
   status               tinyint not null comment '启用状态(0：开启，1：关闭)',
   template_id_one      bigint not null comment '模板1',
   template_id_two      bigint comment '模板2',
   template_id_three    bigint comment '模板3',
   template_id_four     bigint comment '模板4',
   `extra` json NULL COMMENT '扩展字段',
   remark varchar(255) NULL COMMENT '备注',
   `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
   primary key (id) USING BTREE
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '短信节点表';


-- 菜单 SQL
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点管理', '', 2, 3, 1093,
    'sms-node', 'phone', 'system/sms/smsNode', 0
);

-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();

-- 按钮 SQL
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点查询', 'system:sms-node:query', 3, 1, @parentId,
    '', '', '', 0
);
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点创建', 'system:sms-node:create', 3, 2, @parentId,
    '', '', '', 0
);
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点更新', 'system:sms-node:update', 3, 3, @parentId,
    '', '', '', 0
);
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点删除', 'system:sms-node:delete', 3, 4, @parentId,
    '', '', '', 0
);
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`,
    `path`, `icon`, `component`, `status`
)
VALUES (
    '短信节点导出', 'system:sms-node:export', 3, 5, @parentId,
    '', '', '', 0
);

INSERT INTO `jiedao`.`system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ('短信节点', 'system_sms_node_node', 0, NULL, '1', '2024-10-31 10:08:50', '1', '2024-10-31 10:08:50', b'0');

INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (1, '会员用户 - 手机号注册', 'user-sms-reg', 'system_sms_node_node', 0, 'default', '', '0', '2740', '2024-11-05 14:22:53', '2740', '2024-11-05 14:24:33', b'0', '会员用户 - 手机号注册');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2, '会员用户 - 手机号登陆', 'user-sms-login', 'system_sms_node_node', 0, 'default', '', '1', '2740', '2024-11-05 14:23:27', '2740', '2024-11-05 14:24:39', b'0', 'user-sms-login');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (3, '会员用户 - 解绑手机', 'user-sms-update-mobile', 'system_sms_node_node', 0, 'default', '', '2', '2740', '2024-11-05 14:23:43', '2740', '2024-11-05 14:24:41', b'0', '会员用户 - 解绑手机');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (4, '会员用户 - 忘记密码', 'user-sms-reset-password', 'system_sms_node_node', 0, 'default', '', '3', '2740', '2024-11-05 14:24:05', '2740', '2024-11-05 14:24:43', b'0', '会员用户 - 忘记密码');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (5, '订单 - 控货权转移', 'transfer-control-goods', 'system_sms_node_node', 0, 'default', '', '4', '2740', '2024-11-05 14:26:08', '2740', '2024-11-05 14:26:20', b'0', '订单 - 控货权转移');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (6, '订单 - 放货验证短信', 'delivery-verification-sms', 'system_sms_node_node', 0, 'default', '', '5', '2740', '2024-11-05 14:26:36', '2740', '2024-11-05 14:26:36', b'0', '订单 - 放货验证短信');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (7, '订单 - 放货成功通知', 'notification-successful-delivery', 'system_sms_node_node', 0, 'default', '', '6', '2740', '2024-11-05 14:32:40', '2740', '2024-11-05 14:32:44', b'0', '订单 - 放货成功通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (8, '订单 - 取消放货通知', 'notification-successful-cancel-delivery', 'system_sms_node_node', 0, 'default', '', '7', '2740', '2024-11-05 14:33:12', '2740', '2024-11-05 14:33:12', b'0', '订单 - 取消放货通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (9, '出货 - 到港通知', 'shipment-arrival', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:13:30', '2740', '2024-11-12 17:13:30', b'0', '出货 - 到港通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (10, '出货 - 清关通知', 'shipment-customs-clearance', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:13:52', '2740', '2024-11-12 17:13:52', b'0', '出货 - 清关通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (11, '出货 - 装柜通知', 'shipment-cabinet', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:20:24', '2740', '2024-11-12 17:20:24', b'0', '出货 - 装柜通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (12, '出货 - 封柜反审', 'shipment-close-container', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:22:02', '2740', '2024-11-12 17:22:02', b'0', '出货 - 封柜反审');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (13, '入仓控货通知', 'warehouse-in-control', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:22:30', '2740', '2024-11-12 17:22:30', b'0', '入仓控货通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (14, '入仓通知', 'warehouse-in', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:23:11', '2740', '2024-11-12 17:23:11', b'0', '入仓通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (15, '追加通知', 'warehouse-in-append', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:23:52', '2740', '2024-11-12 17:23:52', b'0', '追加通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (16, '入仓-非控货代收货款', 'warehouse-in-collect-of-goods', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-12 17:24:22', '2740', '2024-11-12 17:24:22', b'0', '入仓-非控货代收货款');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (17, '出仓通知', 'air-shipment-warehouse', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-14 15:15:58', '2740', '2024-11-14 17:55:52', b'0', '出仓通知');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (18, '会员用户 - 兑换礼品', 'user-sms-redeem-reward', 'system_sms_node_node', 0, 'default', '', NULL, '2740', '2024-11-20 09:52:56', '2740', '2024-11-20 09:52:56', b'0', '会员用户 - 兑换礼品');


INSERT INTO `jiedao`.`system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ('发送类型', 'system_sms_template_message_type', 0, NULL, '2740', '2024-11-04 17:07:41', '2740', '2024-11-04 17:07:41', b'0');
INSERT INTO `jiedao`.`system_dict_data`( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES ( 3, 'email', '3', 'system_sms_template_message_type', 0, 'default', '', NULL, '2740', '2024-11-04 17:20:21', '2740', '2024-11-04 17:20:21', b'0', 'email');
INSERT INTO `jiedao`.`system_dict_data`( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES ( 2, 'whatsapp', '2', 'system_sms_template_message_type', 0, 'default', '', NULL, '2740', '2024-11-04 17:20:00', '2740', '2024-11-04 17:20:00', b'0', 'whatsapp');
INSERT INTO `jiedao`.`system_dict_data`( `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES ( 1, '短信', '1', 'system_sms_template_message_type', 0, 'default', '', NULL, '2740', '2024-11-04 17:17:48', '2740', '2024-11-04 17:18:16', b'0', 'message');

INSERT INTO `jiedao`.`system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ('模板语言', 'system_sms_template_language', 0, NULL, '2740', '2024-11-04 17:38:08', '2740', '2024-11-04 17:38:08', b'0');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (1, '英文', 'en', 'system_sms_template_language', 0, 'default', '', NULL, '2740', '2024-11-04 17:40:55', '2741', '2024-11-22 15:30:55', b'0', 'en');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (3, '中文', 'zh_CN', 'system_sms_template_language', 0, 'default', '', NULL, '2741', '2024-11-22 15:30:09', '2741', '2024-11-22 15:31:05', b'0', 'zh_CN');
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2, '法文', 'fr', 'system_sms_template_language', 0, 'default', '', NULL, '2741', '2024-11-22 15:30:46', '2741', '2024-11-22 15:31:01', b'0', 'fr');


--客户操作日志类型
INSERT INTO `jiedao`.`system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`, `label_fr`) VALUES (34, '验证码发送', '34', 'customer_operate_type', 0, 'default', '', NULL, '2741', '2024-12-13 14:05:59', '2741', '2024-12-13 14:05:59', b'0', 'Verification code', 'CAPTCHA');

--控货详情列表
INSERT INTO `jiedao`.`system_menu`(`name`, `permission`, `menu_type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `is_show_in_menu_bar`, `name_en`, `keepalive`, `redirect`, `badge_field`) VALUES ('控货详情列表', '', 2, 7, 1559, 'cargo_control/detail_list', 'component', 'ecw/order/cargoControlDetailList', 0, '2741', '2024-12-16 14:01:21', '2741', '2024-12-20 09:35:58', b'0', b'1', 'Control Details', b'0', NULL, NULL);