/*
Navicat MySQL Data Transfer

Source Server         : 192.168.1.51
Source Server Version : 50714
Source Host           : 192.168.1.51:3306
Source Database       : face_discern

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2017-03-16 10:54:29
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for calling
-- ----------------------------
DROP TABLE IF EXISTS `calling`;
CREATE TABLE `calling` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '签到事件主键',
  `call_dt` datetime DEFAULT NULL COMMENT '呼叫时间',
  `caller_person_id` int(10) DEFAULT NULL COMMENT '呼叫人员主键',
  `callee_person_id` int(10) DEFAULT NULL COMMENT '被呼叫人员主键',
  `place_id` int(10) DEFAULT NULL COMMENT '呼叫发生所在设备(地点)',
  PRIMARY KEY (`id`),
  KEY `caller_person_id` (`caller_person_id`),
  KEY `callee_person_id` (`callee_person_id`),
  KEY `calling_ibfk_3` (`place_id`),
  CONSTRAINT `calling_ibfk_1` FOREIGN KEY (`caller_person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `calling_ibfk_2` FOREIGN KEY (`callee_person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `calling_ibfk_3` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for checking
-- ----------------------------
DROP TABLE IF EXISTS `checking`;
CREATE TABLE `checking` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '签到事件主键',
  `status_id` tinyint(2) DEFAULT NULL COMMENT '签到状态',
  `status_dt` datetime DEFAULT NULL COMMENT '签到时间',
  `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
  `place_id` int(10) DEFAULT NULL COMMENT '签到位置',
  PRIMARY KEY (`id`),
  KEY `person_id` (`person_id`),
  KEY `place_id` (`place_id`),
  CONSTRAINT `checking_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `checking_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for checking_company
-- ----------------------------
DROP TABLE IF EXISTS `checking_company`;
CREATE TABLE `checking_company` (
  `person_id` int(10) NOT NULL COMMENT '人员主键',
  `company_id` int(10) NOT NULL COMMENT '公司主键',
  PRIMARY KEY (`person_id`,`company_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `checking_company_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `checking_company_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for company
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '公司主键',
  `name` varchar(50) DEFAULT NULL COMMENT '公司名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '部门主键',
  `label` varchar(40) DEFAULT NULL COMMENT '部门名称',
  `label_spell` char(20) DEFAULT NULL COMMENT '部门首字母',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for face_image
-- ----------------------------
DROP TABLE IF EXISTS `face_image`;
CREATE TABLE `face_image` (
  `face_id` int(10) NOT NULL COMMENT '员工图片主键',
  `register_company_id` int(10) NOT NULL COMMENT '注册公司主键',
  `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
  `image_path` varchar(255) DEFAULT NULL COMMENT '图片地址',
  `image_type_id` int(10) DEFAULT NULL COMMENT '图片类型',
  PRIMARY KEY (`face_id`,`register_company_id`),
  KEY `register_company_id` (`register_company_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `face_image_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `face_image_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for image
-- ----------------------------
DROP TABLE IF EXISTS `image`;
CREATE TABLE `image` (
  `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
  `image_type` int(10) DEFAULT NULL COMMENT '图片类型',
  `image_path` varchar(255) DEFAULT NULL COMMENT '图片地址',
  KEY `person_id` (`person_id`),
  CONSTRAINT `image_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '人员主键',
  `name` varchar(40) DEFAULT NULL COMMENT '人员名字',
  `name_spell` char(2) DEFAULT NULL COMMENT '人员名首字母',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别',
  `country` char(3) DEFAULT NULL COMMENT '国籍',
  `province` char(20) DEFAULT NULL COMMENT '省',
  `city` char(20) DEFAULT NULL COMMENT '市',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `phone` char(12) DEFAULT NULL COMMENT '座机电话',
  `mobile_phone` char(15) DEFAULT NULL COMMENT '移动电话',
  `identity_type` int(10) DEFAULT NULL COMMENT '证件类型',
  `identify` varchar(100) DEFAULT NULL COMMENT '证件编号',
  `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
  `department_id` int(10) DEFAULT NULL COMMENT '部门主键',
  `post_id` int(10) DEFAULT NULL COMMENT '职务主键',
  `remark` varchar(255) DEFAULT NULL COMMENT '人员备注消息',
  `warning` tinyint(1) DEFAULT NULL COMMENT '危险人员警告消息',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `post_id` (`post_id`),
  KEY `department_id` (`department_id`),
  CONSTRAINT `person_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `person_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`),
  CONSTRAINT `person_ibfk_4` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for person_ext
-- ----------------------------
DROP TABLE IF EXISTS `person_ext`;
CREATE TABLE `person_ext` (
  `person_id` int(10) DEFAULT NULL COMMENT '人员主键',
  KEY `person_id` (`person_id`),
  CONSTRAINT `person_ext_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for place
-- ----------------------------
DROP TABLE IF EXISTS `place`;
CREATE TABLE `place` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '设备主键',
  `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
  `label` varchar(40) DEFAULT NULL COMMENT '设备地点',
  `authorization_id` varchar(100) NOT NULL COMMENT '设备授权码',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `place_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '职位主键',
  `label` varchar(40) DEFAULT NULL COMMENT '职位名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visit
-- ----------------------------
DROP TABLE IF EXISTS `visit`;
CREATE TABLE `visit` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事件主键',
  `visitor_person_id` int(10) DEFAULT NULL COMMENT '访问人员主键',
  `visitee_person_id` int(10) DEFAULT NULL COMMENT '被访问人员主键',
  `matter_id` int(10) DEFAULT NULL COMMENT '访问事由主键',
  `remark` varchar(255) DEFAULT NULL COMMENT '访问备注',
  `last_status` int(10) DEFAULT NULL COMMENT '最新的访问状态',
  `start_time` datetime DEFAULT NULL COMMENT '来访时间',
  `last_time` datetime DEFAULT NULL COMMENT '签离时间',
  `visitor_company_id` int(10) DEFAULT NULL,
  `visitee_company_id` int(10) DEFAULT NULL,
  `visitor_type_id` int(10) DEFAULT NULL COMMENT '访客类型',
  `warning` tinyint(1) DEFAULT NULL COMMENT '当前访问的警告状态',
  PRIMARY KEY (`id`),
  KEY `visitor_person_id` (`visitor_person_id`),
  KEY `visitee_person_id` (`visitee_person_id`),
  KEY `matter_id` (`matter_id`),
  KEY `visit_ibfk_4` (`visitor_type_id`),
  KEY `visitor_company_id` (`visitor_company_id`),
  KEY `visitee_company_id` (`visitee_company_id`),
  CONSTRAINT `visit_ibfk_1` FOREIGN KEY (`visitor_person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `visit_ibfk_2` FOREIGN KEY (`visitee_person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `visit_ibfk_3` FOREIGN KEY (`matter_id`) REFERENCES `visit_matter` (`id`),
  CONSTRAINT `visit_ibfk_4` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`),
  CONSTRAINT `visit_ibfk_5` FOREIGN KEY (`visitor_company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `visit_ibfk_6` FOREIGN KEY (`visitee_company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visiting
-- ----------------------------
DROP TABLE IF EXISTS `visiting`;
CREATE TABLE `visiting` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事件流水id',
  `visit_id` int(10) DEFAULT NULL COMMENT '访问事件id',
  `status_id` tinyint(2) DEFAULT NULL COMMENT '当前访问状态',
  `status_dt` datetime DEFAULT NULL COMMENT '更新状态时间',
  `place_id` int(10) DEFAULT NULL COMMENT '设备位置主键',
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  KEY `place_id` (`place_id`),
  CONSTRAINT `visiting_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visit` (`id`),
  CONSTRAINT `visiting_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visitor_type
-- ----------------------------
DROP TABLE IF EXISTS `visitor_type`;
CREATE TABLE `visitor_type` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访客类型主键',
  `label` varchar(32) DEFAULT NULL COMMENT '访客类型',
  `register_company_id` int(10) DEFAULT NULL COMMENT '注册公司主键',
  `available` tinyint(1) DEFAULT NULL COMMENT '是否是可用',
  PRIMARY KEY (`id`),
  KEY `register_company_id` (`register_company_id`),
  CONSTRAINT `visitor_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visit_matter
-- ----------------------------
DROP TABLE IF EXISTS `visit_matter`;
CREATE TABLE `visit_matter` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '访问事由主键',
  `company_id` int(10) DEFAULT NULL COMMENT '公司主键',
  `label` varchar(40) NOT NULL COMMENT '访问事由',
  `available` tinyint(1) DEFAULT NULL COMMENT '是否是可用',
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `visit_matter_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visit_person_type
-- ----------------------------
DROP TABLE IF EXISTS `visit_person_type`;
CREATE TABLE `visit_person_type` (
  `register_company_id` int(10) NOT NULL,
  `person_id` int(10) NOT NULL,
  `visitor_type_id` int(10) NOT NULL,
  PRIMARY KEY (`register_company_id`,`person_id`,`visitor_type_id`),
  KEY `person_id` (`person_id`) USING BTREE,
  KEY `visitor_type_id` (`visitor_type_id`) USING BTREE,
  CONSTRAINT `visit_person_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `visit_person_type_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `visit_person_type_ibfk_3` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for visit_stat
-- ----------------------------
DROP TABLE IF EXISTS `visit_stat`;
CREATE TABLE `visit_stat` (
  `person_id` int(10) NOT NULL COMMENT '人员主键',
  `register_company_id` int(10) NOT NULL COMMENT '注册公司主键',
  `total_count` int(10) DEFAULT NULL COMMENT '来访公司次数',
  PRIMARY KEY (`person_id`,`register_company_id`),
  KEY `person_id` (`person_id`),
  KEY `register_company_id` (`register_company_id`),
  CONSTRAINT `visit_stat_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
  CONSTRAINT `visit_stat_ibfk_2` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;