-- Roundcube Webmail initial database structure


SET FOREIGN_KEY_CHECKS=0;

-- Table structure for table `session`

CREATE TABLE `session` (
 `sess_id` varchar(128) NOT NULL,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `ip` varchar(40) NOT NULL,
 `vars` mediumtext NOT NULL,
 PRIMARY KEY(`sess_id`),
 INDEX `changed_index` (`changed`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `users`

CREATE TABLE `users` (
 `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `username` varchar(128) BINARY NOT NULL,
 `mail_host` varchar(128) NOT NULL,
 `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `last_login` datetime DEFAULT NULL,
 `failed_login` datetime DEFAULT NULL COMMENT '最后尝试登录的时间',
 `failed_login_counter` int(10) unsigned DEFAULT NULL COMMENT '尝试登录的次数，和login_rate_limit配置相关，多少时间内不能超过次',
 `language` varchar(16),
 `preferences` longtext,
 PRIMARY KEY(`user_id`),
 UNIQUE `username` (`username`, `mail_host`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `cache`

CREATE TABLE `cache` (
 `user_id` int(10) UNSIGNED NOT NULL,
 `cache_key` varchar(128) BINARY NOT NULL,
 `expires` datetime DEFAULT NULL,
 `data` longtext NOT NULL,
 PRIMARY KEY (`user_id`, `cache_key`),
 CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `expires_index` (`expires`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `cache_shared`

CREATE TABLE `cache_shared` (
 `cache_key` varchar(64) BINARY NOT NULL,
 `expires` datetime DEFAULT NULL,
 `data` longtext NOT NULL,
 PRIMARY KEY (`cache_key`),
 INDEX `expires_index` (`expires`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `cache_index`

CREATE TABLE `cache_index` (
 `user_id` int(10) UNSIGNED NOT NULL,
 `mailbox` varchar(64) BINARY NOT NULL,
 `expires` datetime DEFAULT NULL,
 `valid` tinyint(1) NOT NULL DEFAULT '0',
 `data` longtext NOT NULL,
 CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `expires_index` (`expires`),
 PRIMARY KEY (`user_id`, `mailbox`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `cache_thread`

CREATE TABLE `cache_thread` (
 `user_id` int(10) UNSIGNED NOT NULL,
 `mailbox` varchar(64) BINARY NOT NULL,
 `expires` datetime DEFAULT NULL,
 `data` longtext NOT NULL,
 CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `expires_index` (`expires`),
 PRIMARY KEY (`user_id`, `mailbox`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `cache_messages`

CREATE TABLE `cache_messages` (
 `user_id` int(10) UNSIGNED NOT NULL,
 `mailbox` varchar(64) BINARY NOT NULL,
 `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
 `expires` datetime DEFAULT NULL,
 `data` longtext NOT NULL,
 `flags` int(11) NOT NULL DEFAULT '0',
 CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `expires_index` (`expires`),
 PRIMARY KEY (`user_id`, `mailbox`, `uid`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `collected_addresses`

CREATE TABLE `collected_addresses` (
 `address_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `name` varchar(255) NOT NULL DEFAULT '',
 `email` varchar(64) NOT NULL,
 `user_id` int(10) UNSIGNED NOT NULL,
 `type` int(10) UNSIGNED NOT NULL,
 PRIMARY KEY(`address_id`),
 CONSTRAINT `user_id_fk_collected_addresses` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 UNIQUE INDEX `user_email_collected_addresses_index` (`user_id`, `type`, `email`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `contacts`

CREATE TABLE `contacts` (
 `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` tinyint(1) NOT NULL DEFAULT '0',
 `name` varchar(128) NOT NULL DEFAULT '',
 `email` text NOT NULL,
 `firstname` varchar(128) NOT NULL DEFAULT '',
 `surname` varchar(128) NOT NULL DEFAULT '',
 `vcard` longtext NULL,
 `words` text NULL,
 `user_id` int(10) UNSIGNED NOT NULL,
 PRIMARY KEY(`contact_id`),
 CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `user_contacts_index` (`user_id`,`del`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `contactgroups`

CREATE TABLE `contactgroups` (
  `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `del` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY(`contactgroup_id`),
  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `contactgroups_user_index` (`user_id`,`del`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `contactgroupmembers`

CREATE TABLE `contactgroupmembers` (
  `contactgroup_id` int(10) UNSIGNED NOT NULL,
  `contact_id` int(10) UNSIGNED NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`contactgroup_id`, `contact_id`),
  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
    REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `contactgroupmembers_contact_index` (`contact_id`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB;


-- Table structure for table `identities`

CREATE TABLE `identities` (
 `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(10) UNSIGNED NOT NULL,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` tinyint(1) NOT NULL DEFAULT '0',
 `standard` tinyint(1) NOT NULL DEFAULT '0',
 `name` varchar(128) NOT NULL,
 `organization` varchar(128) NOT NULL DEFAULT '',
 `email` varchar(128) NOT NULL,
 `reply-to` varchar(128) NOT NULL DEFAULT '',
 `bcc` varchar(128) NOT NULL DEFAULT '',
 `signature` longtext,
 `html_signature` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY(`identity_id`),
 CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `user_identities_index` (`user_id`, `del`),
 INDEX `email_identities_index` (`email`, `del`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `dictionary`

CREATE TABLE `dictionary` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- redundant, for compat. with Galera Cluster
  `user_id` int(10) UNSIGNED DEFAULT NULL, -- NULL here is for "shared dictionaries"
  `language` varchar(16) NOT NULL,
  `data` longtext NOT NULL,
  CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE `uniqueness` (`user_id`, `language`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `searches`

CREATE TABLE `searches` (
 `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(10) UNSIGNED NOT NULL,
 `type` int(3) NOT NULL DEFAULT '0',
 `name` varchar(128) NOT NULL,
 `data` text,
 PRIMARY KEY(`search_id`),
 CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 UNIQUE `uniqueness` (`user_id`, `type`, `name`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table structure for table `filestore`

CREATE TABLE `filestore` (
 `file_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(10) UNSIGNED NOT NULL,
 `context` varchar(32) NOT NULL,
 `filename` varchar(128) NOT NULL,
 `mtime` int(10) NOT NULL,
 `data` longtext NOT NULL,
 PRIMARY KEY (`file_id`),
 CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
   REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 UNIQUE `uniqueness` (`user_id`, `context`, `filename`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table structure for table `system`

CREATE TABLE `system` (
 `name` varchar(64) NOT NULL,
 `value` mediumtext,
 PRIMARY KEY(`name`)
) ROW_FORMAT=DYNAMIC ENGINE=INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Table structure for table `email_server_address`

CREATE TABLE `email_server_address` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mail_suffix` varchar(20) NOT NULL DEFAULT '' COMMENT '邮件后缀',
`imap` varchar(50) NOT NULL DEFAULT '' COMMENT 'imap服务器地址',
`smtp` varchar(50) NOT NULL DEFAULT '' COMMENT 'stmp服务器地址',
`service_provider` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商名称',
PRIMARY KEY (`id`),
KEY `mail_suffix` (`mail_suffix`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='email服务器地址';


-- Table structure for table `platform_users`

CREATE TABLE `platform_users` (
                                  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                  `platform_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT ' platform 表id',
                                  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'user表id',
                                  `platform_user_id` int(10) unsigned NOT NULL COMMENT '接入平台的用户id',
                                  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
                                  PRIMARY KEY (`id`),
                                  KEY `platform` (`platform_id`,`platform_user_id`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接入平台，用户关系表';

-- Table structure for table `platform`

CREATE TABLE `platform` (
                            `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                            `name` varchar(20) NOT NULL DEFAULT '' COMMENT '那个平台',
                            `appid` int(1) unsigned NOT NULL DEFAULT '0' COMMENT 'appid',
                            `appkey` varchar(64) NOT NULL DEFAULT '' COMMENT 'key,来源验证使用',
                            `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1,正常使用，0禁止使用',
                            `ip` varchar(64) NOT NULL DEFAULT '' COMMENT 'ipv6，ipv4,ip白名单',
                            PRIMARY KEY (`id`),
                            UNIQUE KEY `appid` (`appid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='平台';


SET FOREIGN_KEY_CHECKS=1;


--  Insert data

INSERT INTO `system` (`name`, `value`) VALUES ('roundcube-version', '2020122900');

INSERT INTO `roundcubemail`.`email_server_address`(`id`, `mail_suffix`, `imap`, `smtp`, `service_provider`) VALUES (1, 'qq.com', 'imap.qq.com', 'smtp.qq.com', 'QQ邮箱');
INSERT INTO `roundcubemail`.`email_server_address`(`id`, `mail_suffix`, `imap`, `smtp`, `service_provider`) VALUES (2, 'gmail.com', 'imap.gmail.com', 'smtp.gmail.com', 'Gmail 谷歌');