-- 创建数据库
CREATE DATABASE `crowd` DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;

-- 选择数据库
USE `crowd`;

-- 创建管理员表(自行插入假数据)
CREATE TABLE `cf_admin` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account` VARCHAR(255) NOT NULL COMMENT '登录账号',
  `username` VARCHAR(255) NOT NULL COMMENT '昵称',
  -- 后期使用SpringSecurity时使用到了盐值加密算法，所以长度需要扩展至60
  `password` VARCHAR(60) NOT NULL COMMENT '密码',
  `email` VARCHAR(255) NOT NULL COMMENT '邮箱',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`)
) COMMENT '管理员表';

-- 默认管理员账户：admin-0000
INSERT INTO `cf_admin` VALUES (1, 'admin', '系统管理员', '$2a$10$f5vq3tYxcwubpH7HFYSW/OVlZbJffrMpZKenx8Fd0eOavCnv.E93q', 'admin@qq.com', '2021-07-20 02:17:59')

-- 创建角色表(自行插入假数据)
CREATE TABLE `cf_role` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` VARCHAR(20) DEFAULT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`)
) COMMENT '角色表';

INSERT INTO `cf_role` VALUES (1, '系统管理员');
INSERT INTO `cf_role` VALUES (2, '管理员');

-- 创建菜单表
CREATE TABLE `cf_menu` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pid` VARCHAR(19) DEFAULT NULL COMMENT '父节点ID',
  `name` VARCHAR(200) DEFAULT NULL COMMENT '菜单名称',
  `url` VARCHAR(200) DEFAULT NULL COMMENT '菜单对应的URL地址',
  `icon` VARCHAR(200) DEFAULT NULL COMMENT '菜单图标',
  PRIMARY KEY (`id`)
)  COMMENT '菜单表';

-- 菜单表测试数据
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(1, NULL, '系统权限菜单', 'glyphicon glyphicon-th-list', NULL);
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(2, 1, '控制面板', 'glyphicon glyphicon-dashboard', 'main.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(3, 1, '权限管理', 'glyphicon glyphicon glyphicon-tasks', NULL);
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(4, 3, '用户维护', 'glyphicon glyphicon-user', 'user/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(5, 3, '角色维护', 'glyphicon glyphicon-king', 'role/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(6, 3, '菜单维护', 'glyphicon glyphicon-lock', 'permission/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(7, 1, '业务审核', 'glyphicon glyphicon-ok', NULL);
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(8, 7, '实名认证审核', 'glyphicon glyphicon-check', 'auth_cert/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(9, 7, '广告审核', 'glyphicon glyphicon-check', 'auth_adv/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(10, 7, '项目审核', 'glyphicon glyphicon-check', 'auth_project/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(11, 1, '业务管理', 'glyphicon glyphicon-th-large', NULL);
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(12, 11, '资质维护', 'glyphicon glyphicon-picture', 'cert/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(13, 11, '分类管理', 'glyphicon glyphicon-equalizer', 'certtype/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(14, 11, '流程管理', 'glyphicon glyphicon-random', 'process/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(15, 11, '广告管理', 'glyphicon glyphicon-hdd', 'advert/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(16, 11, '消息模板', 'glyphicon glyphicon-comment', 'message/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(17, 11, '项目分类', 'glyphicon glyphicon-list', 'projectType/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(18, 11, '项目标签', 'glyphicon glyphicon-tags', 'tag/index.htm');
INSERT INTO `cf_menu` (`id`, `pid`, `name`, `icon`, `url`) VALUES(19, 1, '参数管理', 'glyphicon glyphicon-list-alt', 'param/index.htm');

-- 管理员关联角色的中间表
CREATE TABLE `cf_admin_role` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `admin_id` INT(11) NOT NULL COMMENT '管理员ID',
  `role_id` INT(11) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`)
) COMMENT '管理员与角色的中间表';

INSERT INTO `cf_admin_role` VALUES(1, 1, 1);

-- 权限表
CREATE TABLE `cf_auth` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` VARCHAR(200) DEFAULT NULL COMMENT '权限名称',
  `name` VARCHAR(200) DEFAULT NULL COMMENT '权限标识',
  `category_id` INT(11) DEFAULT NULL COMMENT '父ID',
  PRIMARY KEY (`id`)
) COMMENT '权限表';

-- 插入测试数据
INSERT INTO `cf_auth` VALUES (1, '用户模块', '', NULL);
INSERT INTO `cf_auth` VALUES (2, '查询', 'user:get', 1);
INSERT INTO `cf_auth` VALUES (3, '添加', 'user:insert', 1);
INSERT INTO `cf_auth` VALUES (4, '编辑', 'user:edit', 1);
INSERT INTO `cf_auth` VALUES (5, '删除', 'user:delete', 1);
INSERT INTO `cf_auth` VALUES (6, '角色模块', '', NULL);
INSERT INTO `cf_auth` VALUES (7, '查询', 'role:get', 6);
INSERT INTO `cf_auth` VALUES (8, '新增', 'role:insert', 6);
INSERT INTO `cf_auth` VALUES (9, '编辑', 'role:edit', 6);
INSERT INTO `cf_auth` VALUES (10, '分配', 'role:assign', 6);
INSERT INTO `cf_auth` VALUES (11, '删除', 'role:delete', 6);
INSERT INTO `cf_auth` VALUES (12, '菜单模块', NULL, NULL);
INSERT INTO `cf_auth` VALUES (13, '查询', 'menu:get', 12);

-- 创建角色与权限的中间表
CREATE TABLE `cf_role_auth`(
  `id` INT AUTO_INCREMENT COMMENT '主键ID',
  `role_id` INT NOT NULL COMMENT '角色ID',
  `auth_id` INT NOT NULL COMMENT '权限ID',
  PRIMARY KEY (`ID`)
) COMMENT '角色与权限的中间表';

-- 插入测试数据
INSERT INTO `cf_role_auth` VALUES (21, 1, 1);
INSERT INTO `cf_role_auth` VALUES (22, 1, 2);
INSERT INTO `cf_role_auth` VALUES (23, 1, 3);
INSERT INTO `cf_role_auth` VALUES (24, 1, 4);
INSERT INTO `cf_role_auth` VALUES (25, 1, 5);
INSERT INTO `cf_role_auth` VALUES (26, 1, 6);
INSERT INTO `cf_role_auth` VALUES (27, 1, 7);
INSERT INTO `cf_role_auth` VALUES (28, 1, 8);
INSERT INTO `cf_role_auth` VALUES (29, 1, 9);
INSERT INTO `cf_role_auth` VALUES (30, 1, 10);
INSERT INTO `cf_role_auth` VALUES (31, 1, 11);
INSERT INTO `cf_role_auth` VALUES (32, 1, 12);
INSERT INTO `cf_role_auth` VALUES (33, 1, 13);
INSERT INTO `cf_role_auth` VALUES (34, 2, 1);
INSERT INTO `cf_role_auth` VALUES (35, 2, 2);
INSERT INTO `cf_role_auth` VALUES (36, 2, 3);
INSERT INTO `cf_role_auth` VALUES (37, 2, 4);
INSERT INTO `cf_role_auth` VALUES (38, 2, 5);
INSERT INTO `cf_role_auth` VALUES (39, 2, 6);
INSERT INTO `cf_role_auth` VALUES (40, 2, 7);
INSERT INTO `cf_role_auth` VALUES (41, 2, 8);
INSERT INTO `cf_role_auth` VALUES (42, 2, 9);
INSERT INTO `cf_role_auth` VALUES (43, 2, 10);
INSERT INTO `cf_role_auth` VALUES (44, 2, 11);
INSERT INTO `cf_role_auth` VALUES (45, 2, 12);
INSERT INTO `cf_role_auth` VALUES (46, 2, 13);