65 lines
4.4 KiB
SQL
65 lines
4.4 KiB
SQL
-- 航空账号（管理员开通：手机号+密码+有效期+设备唯一绑定）
|
||
CREATE TABLE IF NOT EXISTS `member_airline_account` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
||
`mobile` varchar(11) NOT NULL COMMENT '手机号（同一手机号可在不同渠道各有一个账号）',
|
||
`channel` varchar(8) NOT NULL DEFAULT 'CZ' COMMENT '品牌渠道（CZ南航/MU东航/HU海航）',
|
||
`password` varchar(100) NOT NULL DEFAULT '' COMMENT '密码（BCrypt）',
|
||
`start_time` datetime NOT NULL COMMENT '生效开始时间',
|
||
`end_time` datetime NOT NULL COMMENT '生效结束时间',
|
||
`device_id` varchar(128) NULL DEFAULT NULL COMMENT '手机唯一识别号（首次登录绑定；重装/重启应由客户端保持不变）',
|
||
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0开启 1关闭）',
|
||
`remark` varchar(500) NULL DEFAULT NULL COMMENT '备注',
|
||
`user_id` bigint NULL DEFAULT NULL COMMENT '关联会员用户编号 member_user.id（已废弃，登录不再创建会员用户）',
|
||
`tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号',
|
||
`creator` varchar(64) NULL DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater` varchar(64) NULL DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
||
PRIMARY KEY (`id`) USING BTREE,
|
||
UNIQUE KEY `uk_mobile_channel` (`mobile`, `channel`, `deleted`, `tenant_id`) USING BTREE,
|
||
UNIQUE KEY `uk_channel_device` (`channel`, `device_id`, `deleted`, `tenant_id`) USING BTREE,
|
||
KEY `idx_end_time` (`end_time`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='航空账号';
|
||
|
||
-- 菜单：航空账号管理（挂在会员中心 2262 下）
|
||
-- 若 id 冲突，请在管理后台「菜单管理」手动新建，或调整 id
|
||
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
||
VALUES (5800, '航空账号', '', 2, 15, 2262, 'airline-account', 'ep:iphone', 'member/airlineAccount/index', 'MemberAirlineAccount', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
|
||
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `component` = VALUES(`component`);
|
||
|
||
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
||
VALUES
|
||
(5801, '航空账号查询', 'member:airline-account:query', 3, 1, 5800, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
|
||
(5802, '航空账号创建', 'member:airline-account:create', 3, 2, 5800, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
|
||
(5803, '航空账号更新', 'member:airline-account:update', 3, 3, 5800, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
|
||
(5804, '航空账号删除', 'member:airline-account:delete', 3, 4, 5800, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'),
|
||
(5805, '航空账号解绑设备', 'member:airline-account:unbind', 3, 5, 5800, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
|
||
ON DUPLICATE KEY UPDATE `permission` = VALUES(`permission`);
|
||
|
||
-- 测试账号（App 登录）
|
||
-- 手机号：13800138000 密码：123456 有效期约一年 未绑定设备
|
||
-- 密码哈希为 BCrypt(123456)
|
||
INSERT INTO `member_airline_account`
|
||
(`mobile`, `channel`, `password`, `start_time`, `end_time`, `device_id`, `status`, `remark`, `user_id`, `tenant_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
||
SELECT '13800138000',
|
||
'CZ',
|
||
'$2a$10$PplOV8yXcQo9xc32V8vwTuIMbMKgNtjXf3lu6l18LUTOy9YGDdAY6',
|
||
NOW() - INTERVAL 1 DAY,
|
||
NOW() + INTERVAL 365 DAY,
|
||
NULL,
|
||
0,
|
||
'本地联调测试账号',
|
||
NULL,
|
||
1,
|
||
'1',
|
||
NOW(),
|
||
'1',
|
||
NOW(),
|
||
b'0'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM `member_airline_account`
|
||
WHERE `mobile` = '13800138000' AND `channel` = 'CZ' AND `deleted` = b'0' AND `tenant_id` = 1
|
||
);
|