SET NAMES utf8mb4;

-- DROP TABLE IF EXISTS `zd_action`;
CREATE TABLE `zd_action` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `objectType` varchar(30) NOT NULL DEFAULT '',
  `objectID` mediumint unsigned NOT NULL DEFAULT '0',
  `product` varchar(255) NOT NULL,
  `project` mediumint unsigned NOT NULL,
  `execution` mediumint unsigned NOT NULL,
  `actor` varchar(100) NOT NULL DEFAULT '',
  `action` varchar(80) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `comment` text NOT NULL,
  `extra` text,
  `read` enum('0','1') NOT NULL DEFAULT '0',
  `vision` varchar(10) NOT NULL DEFAULT 'rnd',
  `efforted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `actor` (`actor`),
  KEY `project` (`project`),
  KEY `action` (`action`),
  KEY `objectID` (`objectID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;


-- DROP TABLE IF EXISTS `zd_company`;
CREATE TABLE `zd_company` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `name` char(120) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `fax` char(20) DEFAULT NULL,
  `address` char(120) DEFAULT NULL,
  `zipcode` char(10) DEFAULT NULL,
  `website` char(120) DEFAULT NULL,
  `backyard` char(120) DEFAULT NULL,
  `guest` enum('1','0') NOT NULL DEFAULT '0',
  `admins` char(255) DEFAULT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;


-- DROP TABLE IF EXISTS `zd_config`;
CREATE TABLE `zd_config` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `vision` varchar(10) NOT NULL DEFAULT '',
  `owner` char(30) NOT NULL DEFAULT '',
  `module` varchar(30) NOT NULL,
  `section` char(30) NOT NULL DEFAULT '',
  `key` char(30) NOT NULL DEFAULT '',
  `value` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`vision`,`owner`,`module`,`section`,`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;


-- DROP TABLE IF EXISTS `zd_group`;
CREATE TABLE `zd_group` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `project` mediumint unsigned NOT NULL DEFAULT '0',
  `vision` varchar(10) NOT NULL DEFAULT 'rnd',
  `name` char(30) NOT NULL,
  `role` char(30) NOT NULL DEFAULT '',
  `desc` char(255) NOT NULL DEFAULT '',
  `acl` text,
  `developer` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;


-- DROP TABLE IF EXISTS `zd_grouppriv`;
CREATE TABLE `zd_grouppriv` (
  `group` mediumint unsigned NOT NULL DEFAULT '0',
  `module` char(30) NOT NULL DEFAULT '',
  `method` char(30) NOT NULL DEFAULT '',
  UNIQUE KEY `group` (`group`,`module`,`method`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

-- DROP TABLE IF EXISTS `zd_space`;
CREATE TABLE `zd_space` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  `desc` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `createdBy` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  `createdDate` datetime NOT NULL,
  `deleted` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


-- DROP TABLE IF EXISTS `zd_user`;
CREATE TABLE `zd_user` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `company` mediumint unsigned NOT NULL,
  `type` char(30) CHARACTER SET utf8 NOT NULL DEFAULT 'inside',
  `dept` mediumint unsigned NOT NULL DEFAULT '0',
  `account` char(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `password` char(32) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `role` char(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `realname` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `pinyin` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `nickname` char(60) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `commiter` varchar(100) CHARACTER SET utf8 NOT NULL,
  `avatar` text CHARACTER SET utf8 NOT NULL,
  `birthday` date NOT NULL DEFAULT '0000-00-00',
  `gender` enum('f','m') CHARACTER SET utf8 NOT NULL DEFAULT 'f',
  `email` char(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `skype` char(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `qq` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `mobile` char(11) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `phone` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `weixin` varchar(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `dingding` varchar(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `slack` varchar(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `whatsapp` varchar(90) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `address` char(120) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `zipcode` char(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `nature` text CHARACTER SET utf8 NOT NULL,
  `analysis` text CHARACTER SET utf8 NOT NULL,
  `strategy` text CHARACTER SET utf8 NOT NULL,
  `join` date NOT NULL DEFAULT '0000-00-00',
  `visits` mediumint unsigned NOT NULL DEFAULT '0',
  `visions` varchar(20) CHARACTER SET utf8 NOT NULL,
  `ip` char(15) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `last` int unsigned NOT NULL DEFAULT '0',
  `fails` tinyint NOT NULL DEFAULT '0',
  `locked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `feedback` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `ranzhi` char(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ldap` char(30) CHARACTER SET utf8 NOT NULL,
  `score` int NOT NULL DEFAULT '0',
  `scoreLevel` int NOT NULL DEFAULT '0',
  `deleted` enum('0','1') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `clientStatus` enum('online','away','busy','offline','meeting') CHARACTER SET utf8 NOT NULL DEFAULT 'offline',
  `clientLang` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT 'zh-cn',
  `lastActive` datetime,
  `finger` char(20),
  PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`),
  KEY `dept` (`dept`),
  KEY `email` (`email`),
  KEY `commiter` (`commiter`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


-- DROP TABLE IF EXISTS `zd_usergroup`;
CREATE TABLE `zd_usergroup` (
  `account` char(30) NOT NULL DEFAULT '',
  `group` mediumint unsigned NOT NULL DEFAULT '0',
  `project` text NOT NULL,
  UNIQUE KEY `account` (`account`,`group`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

-- DROP TABLE IF EXISTS `zd_datasource`;
CREATE TABLE `zd_datasource` (
  `id` mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `type` varchar(100) NOT NULL,
  `name` varchar(100) COLLATE 'utf8mb4_bin' NOT NULL,
  `acl` enum('open','private') NOT NULL default 'open',
  `whitelist` text NOT NULL,
  `desc` mediumtext NOT NULL,
  `extension` char(30) NOT NULL default 'xlsx',
  `size` int(10) unsigned NOT NULL default '0',
  `connection` mediumtext NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `deleted` tinyint NOT NULL
);

-- DROP TABLE IF EXISTS `zd_dataframe`;
CREATE TABLE `zd_dataframe` (
  `id` mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `source` mediumint NOT NULL,
  `schema` mediumtext NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL
) ENGINE=MyISAM COLLATE 'utf8mb4_bin';

-- DROP TABLE IF EXISTS `zd_project`;
CREATE TABLE `zd_project` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  `acl` enum('open','private') NOT NULL default 'open',
  `whitelist` text NOT NULL,
  `source` mediumint NOT NULL,
  `table` varchar(100) NOT NULL,
  `schema` mediumtext COLLATE 'utf8mb4_bin' NOT NULL,
  `desc` mediumtext COLLATE 'utf8mb4_bin' NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `deleted` tinyint NOT NULL DEFAULT '0'
) ENGINE=MyISAM COLLATE 'utf8mb4_bin';

-- DROP TABLE IF EXISTS `zd_analysis`;
CREATE TABLE `zd_analysis` (
  `id` mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL,
  `project` mediumint NOT NULL,
  `schema` mediumtext COLLATE 'utf8mb4_bin' NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `deleted` tinyint NOT NULL DEFAULT '0'
) ENGINE=MyISAM COLLATE 'utf8mb4_bin';

-- DROP TABLE IF EXISTS `zd_method`;
CREATE TABLE `zd_method` (
  `id` mediumint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(255) NOT NULL,
  `analysis` mediumint NOT NULL,
  `name` varchar(100) NOT NULL,
  `config` mediumtext COLLATE 'utf8mb4_bin' DEFAULT NULL,
  `settings` mediumtext COLLATE 'utf8mb4_bin' NOT NULL,
  `results` mediumtext NOT NULL,
  `resultIDList` text NOT NULL,
  `draft` enum('0', '1') DEFAULT '0',
  `deleted` tinyint NOT NULL DEFAULT '0'
) COLLATE 'utf8mb4_bin';

-- DROP TABLE IF EXISTS `zd_userquery`;
CREATE TABLE IF NOT EXISTS `zd_userquery` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `account` char(30) NOT NULL,
  `module` varchar(30) NOT NULL,
  `title` varchar(90) NOT NULL,
  `form` text NOT NULL,
  `sql` text NOT NULL,
  `shortcut` enum('0','1') NOT NULL DEFAULT '0',
  `common` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `account` (`account`),
  KEY `module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `zd_acl`;
CREATE TABLE IF NOT EXISTS `zd_acl` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `account` char(30) NOT NULL,
  `objectType` char(30) NOT NULL,
  `objectID` mediumint(9) NOT NULL DEFAULT '0',
  `type` char(40) NOT NULL DEFAULT 'whitelist',
  `source` char(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `zd_userview`;
CREATE TABLE IF NOT EXISTS `zd_userview` (
  `account` char(30) NOT NULL,
  `projects` mediumtext NOT NULL,
  `datasources` mediumtext NOT NULL,
  UNIQUE KEY `account` (`account`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS `zd_resultnotes`;
CREATE TABLE `zd_resultnotes` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `analysis` mediumint(8) unsigned NOT NULL,
  `method` mediumint(8) unsigned NOT NULL,
  `result` varchar(255) NOT NULL,
  `desc` text NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `editedBy` varchar(30) NOT NULL,
  `editedDate` datetime NOT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

INSERT INTO `zd_config` (`id`, `vision`, `owner`, `module`, `section`, `key`, `value`) VALUES
(1,    '',    'system',    'custom',    '',    'hourPoint',    '0'),
(2,    '',    'system',    'common',    '',    'CRProduct',    '1'),
(3,    '',    'system',    'common',    '',    'CRExecution',    '1'),
(4,    '',    'system',    'custom',    '',    'URSR',    '2'),
(5,    '',    'system',    'common',    'global',    'mode',    'new'),
(6,    '',    'system',    'project',    '',    'unitList',    'CNY,USD'),
(7,    '',    'system',    'project',    '',    'defaultCurrency',    'CNY'),
(8,    '',    'system',    'story',    '',    'reviewRules',    'allpass'),
(9,    '',    'system',    'common',    'global',    'flow',    'full'),
(10,    '',    'system',    'common',    'global',    'cron',    '1'),
(11,    '',    'system',    'common',    'safe',    'mode',    '1'),
(12,    '',    'system',    'common',    'safe',    'changeWeak',    '1'),
(105,    '',    'system',    'common',    'global',    'version',    '3.1stable'),
(14,    '',    'system',    'common',    'xuanxuan',    'turnon',    '1'),
(15,    '',    'system',    'common',    'xuanxuan',    'key',    'ed41be3ce7de8b7b65808f4ae847f00b'),
(16,    '',    'system',    'common',    'xuanxuan',    'chatPort',    '11444'),
(17,    '',    'system',    'common',    'xuanxuan',    'commonPort',    '11443'),
(18,    '',    'system',    'common',    'xuanxuan',    'ip',    '0.0.0.0'),
(19,    '',    'system',    'common',    'xuanxuan',    'uploadFileSize',    '20'),
(20,    '',    'system',    'common',    'xuanxuan',    'https',    'off'),
(35,    '',    'system',    'common',    'global',    'latestVersionList',    '[]'),
(22,    '',    'admin',    'common',    '',    'URSR',    '2'),
(23,    '',    'admin',    'common',    '',    'programLink',    'program-browse'),
(24,    '',    'admin',    'common',    '',    'productLink',    'product-all'),
(25,    '',    'admin',    'common',    '',    'projectLink',    'project-browse'),
(26,    '',    'admin',    'common',    '',    'executionLink',    'execution-task'),
(27,    '',    'admin',    'common',    '',    'preferenceSetted',    '1'),
(28,    '',    'admin',    'my',    'common',    'blockInited',    '1'),
(109,    '',    'admin',    'my',    'block',    'initVersion',    '2'),
(30,    '',    'system',    'cron',    'run',    'status',    'running'),
(31,    '',    'system',    'common',    'global',    'skipIntroduction',    ',admin'),
(32,    '',    'system',    'common',    'global',    'skipTutorial',    ',admin'),
(33,    '',    'system',    'common',    'global',    'skipYoungBlueTheme',    ',admin'),
(36,    'rnd',    'admin',    'project',    'scrumcommon',    'blockInited',    '1'),
(91,    '',    'admin',    'project',    'block',    'initVersion',    '2'),
(106,    'qucheng',    'admin',    'my',    'common',    'blockInited',    '1'),
(38,    '',    'system',    'common',    'global',    'skipVisions',    ',admin'),
(39,    'rnd',    'admin',    'qa',    'common',    'blockInited',    '1'),
(40,    '',    'admin',    'qa',    'block',    'initVersion',    '2'),
(41,    '',    'system',    'common',    'global',    'sn',    'bed0f795d61630b5760210c1378a6ef0'),
(103,    '',    'admin',    'execution',    '',    'recentExecutions',    '2,6'),
(104,    '',    'admin',    'execution',    '',    'lastExecution',    '2'),
(76,    '',    'admin',    'execution',    'task',    'unfoldTasks',    '{\"2\":{\"9\":\"9\"}}'),
(98,    '',    'admin',    'common',    'global',    'vision',    'rnd'),
(88,    'lite',    'admin',    'my',    'common',    'blockInited',    '1'),
(90,    'lite',    'admin',    'project',    'kanbancommon',    'blockInited',    '1'),
(108,    'zendas',    'admin',    'my',    'common',    'blockInited',    '1');

INSERT INTO `zd_group` (`id`, `project`, `vision`, `name`, `role`, `desc`, `acl`, `developer`) VALUES
(1, 0, 'rnd', '管理员', 'admin', '系统管理员', NULL, 1);

CREATE TABLE IF NOT EXISTS `zd_dataview` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `source` mediumint(8) NOT NULL,
  `name` varchar(155) NOT NULL,
  `acl` enum('open','private') NOT NULL default 'open',
  `whitelist` text NOT NULL,
  `sql` text NOT NULL,
  `fields` mediumtext NOT NULL,
  `createdBy` varchar(30) NOT NULL,
  `createdDate` datetime NOT NULL,
  `editedBy` varchar(30) NOT NULL,
  `editedDate` datetime NOT NULL,
  `deleted` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `zd_userreset` (
  `id`      mediumint(9) NOT NULL AUTO_INCREMENT,
  `account` char(30)     NOT NULL,
  `token`   char(32)     NOT NULL,
  `date`    datetime     NOT NULL,
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
