PRAGMA encoding=utf8;

-- DROP TABLE IF EXISTS `zd_action`;
CREATE TABLE `zd_action` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `objectType` TEXT NOT NULL DEFAULT '',
  `objectID` INTEGER NOT NULL DEFAULT '0',
  `product` TEXT NOT NULL DEFAULT '',
  `project` INTEGER NOT NULL DEFAULT '0',
  `execution` INTEGER NOT NULL DEFAULT '0',
  `actor` TEXT NOT NULL DEFAULT '',
  `action` TEXT NOT NULL DEFAULT '',
  `date` DATETIME NOT NULL DEFAULT '0',
  `comment` TEXT NOT NULL DEFAULT '',
  `extra` TEXT,
  `read` TEXT NOT NULL CHECK(`read` IN ('0', '1')) DEFAULT '0',
  `vision` TEXT NOT NULL DEFAULT 'rnd',
  `efforted` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_company`;
CREATE TABLE `zd_company` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT DEFAULT NULL,
  `phone` TEXT DEFAULT NULL,
  `fax` TEXT DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `zipcode` TEXT DEFAULT NULL,
  `website` TEXT DEFAULT NULL,
  `backyard` TEXT DEFAULT NULL,
  `guest` INTEGER NOT NULL CHECK(`guest` IN ('0', '1')) DEFAULT 0,
  `admins` TEXT DEFAULT NULL,
  `deleted` INTEGER NOT NULL DEFAULT 0
);

-- DROP TABLE IF EXISTS `zd_config`;
CREATE TABLE `zd_config` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `vision` TEXT NOT NULL DEFAULT '',
  `owner` TEXT NOT NULL DEFAULT '',
  `module` TEXT NOT NULL DEFAULT '',
  `section` TEXT NOT NULL DEFAULT '',
  `key` TEXT NOT NULL DEFAULT '',
  `value` TEXT NOT NULL DEFAULT '',
  UNIQUE(`vision`,`owner`,`module`,`section`,`key`)
);

-- DROP TABLE IF EXISTS `zd_group`;
CREATE TABLE `zd_group` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `project` INTEGER NOT NULL DEFAULT '0',
  `vision` INTEGER NOT NULL DEFAULT 'rnd',
  `name` TEXT NOT NULL DEFAULT '',
  `role` TEXT NOT NULL DEFAULT '',
  `desc` TEXT NOT NULL DEFAULT '',
  `acl` TEXT,
  `developer` INTEGER NOT NULL DEFAULT '1'
);

-- DROP TABLE IF EXISTS `zd_grouppriv`;
CREATE TABLE `zd_grouppriv` (
  `group` INTEGER NOT NULL DEFAULT '0',
  `module` TEXT NOT NULL DEFAULT '',
  `method` TEXT NOT NULL DEFAULT '',
  UNIQUE(`group`,`module`,`method`)
);

-- DROP TABLE IF EXISTS `zd_space`;
CREATE TABLE `zd_space` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `desc` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `createdBy` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_user`;
CREATE TABLE `zd_user` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `company` INTEGER NOT NULL DEFAULT '0',
  `type` TEXT NOT NULL DEFAULT 'inside',
  `dept` INTEGER NOT NULL DEFAULT '0',
  `account` TEXT NOT NULL UNIQUE DEFAULT '',
  `password` TEXT NOT NULL DEFAULT '',
  `role` TEXT NOT NULL DEFAULT '',
  `realname` TEXT NOT NULL DEFAULT '',
  `pinyin` TEXT NOT NULL DEFAULT '',
  `nickname` TEXT NOT NULL DEFAULT '',
  `commiter` TEXT NOT NULL DEFAULT '',
  `avatar` TEXT NOT NULL DEFAULT '',
  `birthday` DATE NOT NULL DEFAULT '0000-00-00',
  `gender` TEXT CHECK(`gender` IN ('f', 'm')) NOT NULL DEFAULT 'f',
  `email` TEXT NOT NULL DEFAULT '',
  `skype` TEXT NOT NULL DEFAULT '',
  `qq` TEXT NOT NULL DEFAULT '',
  `mobile` TEXT NOT NULL DEFAULT '',
  `phone` TEXT NOT NULL DEFAULT '',
  `weixin` TEXT NOT NULL DEFAULT '',
  `dingding` TEXT NOT NULL DEFAULT '',
  `slack` TEXT NOT NULL DEFAULT '',
  `whatsapp` TEXT NOT NULL DEFAULT '',
  `address` TEXT NOT NULL DEFAULT '',
  `zipcode` TEXT NOT NULL DEFAULT '',
  `nature` TEXT NOT NULL DEFAULT '',
  `analysis` TEXT NOT NULL DEFAULT '',
  `strategy` TEXT NOT NULL DEFAULT '',
  `join` DATE NOT NULL DEFAULT '0000-00-00',
  `visits` INTEGER NOT NULL DEFAULT '0',
  `visions` TEXT NOT NULL DEFAULT '',
  `ip` TEXT NOT NULL DEFAULT '',
  `last` INTEGER NOT NULL DEFAULT '0',
  `fails` INTEGER NOT NULL DEFAULT '0',
  `locked` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `feedback` INTEGER NOT NULL DEFAULT '0',
  `ranzhi` TEXT NOT NULL DEFAULT '',
  `ldap` TEXT NOT NULL DEFAULT '',
  `score` INTEGER NOT NULL DEFAULT '0',
  `scoreLevel` INTEGER NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0',
  `clientStatus` TEXT CHECK(`clientStatus` IN ('online','away','busy','offline','meeting')) NOT NULL DEFAULT 'offline',
  `clientLang` TEXT NOT NULL DEFAULT 'zh-cn',
  `lastActive` DATETIME,
  `finger` TEXT
);

-- DROP TABLE IF EXISTS `zd_usergroup`;
CREATE TABLE `zd_usergroup` (
  `account` TEXT NOT NULL DEFAULT '',
  `group` INTEGER NOT NULL DEFAULT '0',
  `project` TEXT NOT NULL DEFAULT '',
  UNIQUE(`account`,`group`)
);

-- DROP TABLE IF EXISTS `zd_datasource`;
CREATE TABLE `zd_datasource` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `type` TEXT NOT NULL DEFAULT '',
  `name` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `acl` TEXT CHECK(`acl` IN ('open','private')) NOT NULL DEFAULT 'open',
  `whitelist` TEXT NOT NULL DEFAULT '',
  `desc` TEXT NOT NULL DEFAULT '',
  `extension` TEXT NOT NULL DEFAULT 'xlsx',
  `size` INTEGER NOT NULL DEFAULT '0',
  `connection` TEXT NOT NULL DEFAULT '',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_dataframe`;
CREATE TABLE `zd_dataframe` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `source` INTEGER NOT NULL DEFAULT '0',
  `schema` TEXT NOT NULL DEFAULT '',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_project`;
CREATE TABLE `zd_project` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `acl` TEXT CHECK(`acl` IN ('open','private')) NOT NULL DEFAULT 'open',
  `whitelist` TEXT NOT NULL DEFAULT '',
  `source` INTEGER NOT NULL DEFAULT '0',
  `table` TEXT NOT NULL DEFAULT '',
  `schema` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `desc` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` DATETIME NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_analysis`;
CREATE TABLE `zd_analysis` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT NOT NULL DEFAULT '',
  `project` INTEGER NOT NULL DEFAULT '0',
  `schema` INTEGER COLLATE BINARY NOT NULL DEFAULT '0',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_method`;
CREATE TABLE `zd_method` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `title` TEXT NOT NULL DEFAULT '',
  `analysis` INTEGER NOT NULL DEFAULT '0',
  `name` INTEGER NOT NULL DEFAULT '0',
  `config` TEXT COLLATE BINARY DEFAULT NULL,
  `settings` TEXT COLLATE BINARY NOT NULL DEFAULT '',
  `results` TEXT NOT NULL DEFAULT '',
  `resultIDList` TEXT NOT NULL DEFAULT '',
  `draft` INTEGER DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_userquery`;
CREATE TABLE IF NOT EXISTS `zd_userquery` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `account` INTEGER NOT NULL DEFAULT '0',
  `module` INTEGER NOT NULL DEFAULT '0',
  `title` INTEGER NOT NULL DEFAULT '0',
  `form` TEXT NOT NULL DEFAULT '',
  `sql` TEXT NOT NULL DEFAULT '',
  `shortcut` INTEGER NOT NULL DEFAULT '0',
  `common` INTEGER NOT NULL DEFAULT '0'
);

-- DROP TABLE IF EXISTS `zd_acl`;
CREATE TABLE IF NOT EXISTS `zd_acl` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `account` TEXT NOT NULL DEFAULT '',
  `objectType` TEXT NOT NULL DEFAULT '',
  `objectID` TEXT NOT NULL DEFAULT '0',
  `type` TEXT NOT NULL DEFAULT 'whitelist',
  `source` TEXT NOT NULL DEFAULT ''
);

-- DROP TABLE IF EXISTS `zd_userview`;
CREATE TABLE IF NOT EXISTS `zd_userview` (
  `account` TEXT NOT NULL DEFAULT '',
  `projects` TEXT NOT NULL DEFAULT '',
  `datasources` TEXT NOT NULL DEFAULT '',
  UNIQUE(`account`)
);

-- DROP TABLE IF EXISTS `zd_resultnotes`;
CREATE TABLE `zd_resultnotes` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `analysis` INTEGER NOT NULL DEFAULT '0',
  `method` INTEGER NOT NULL DEFAULT '0',
  `result` TEXT NOT NULL DEFAULT '',
  `desc` TEXT NOT NULL DEFAULT '',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `editedBy` TEXT NOT NULL DEFAULT '',
  `editedDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

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',    '2.0.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` INTEGER PRIMARY KEY AUTOINCREMENT,
  `source` INTEGER NOT NULL DEFAULT '0',
  `name` TEXT NOT NULL DEFAULT '',
  `acl` TEXT CHECK(`acl` IN ('open','private')) NOT NULL DEFAULT 'open',
  `whitelist` TEXT NOT NULL DEFAULT '',
  `sql` TEXT NOT NULL DEFAULT '',
  `fields` TEXT NOT NULL DEFAULT '',
  `createdBy` TEXT NOT NULL DEFAULT '',
  `createdDate` DATETIME NOT NULL DEFAULT '0',
  `editedBy` TEXT NOT NULL DEFAULT '',
  `editedDate` DATETIME NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);

CREATE TABLE IF NOT EXISTS `zd_userreset` (
  `id`      INTEGER PRIMARY KEY AUTOINCREMENT,
  `account` TEXT     NOT NULL DEFAULT '',
  `token`   TEXT     NOT NULL DEFAULT '',
  `date`    DATETIME     NOT NULL DEFAULT '0',
  `deleted` INTEGER NOT NULL DEFAULT '0'
);