/*
 Navicat Premium Data Transfer

 Source Server         : 120.78.162.177
 Source Server Type    : MySQL
 Source Server Version : 50734
 Source Host           : 120.78.162.177:3306
 Source Schema         : timer_test

 Target Server Type    : MySQL
 Target Server Version : 50734
 File Encoding         : 65001

 Date: 09/04/2022 18:43:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for insure_applicant
-- ----------------------------
DROP TABLE IF EXISTS `insure_applicant`;
CREATE TABLE `insure_applicant`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `applicant_e_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业/网点名称',
  `applicant_e_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业/网点地址',
  `applicant_employee_list` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员清单',
  `applicant_e_contacts` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业授权操作人姓名',
  `applicant_e_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人的联系电话',
  `applicant_e_no_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业证件类型:3646、统一社会信用代码 3924、组织机构代码',
  `applicant_e_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业证件号码',
  `applicant_e_mail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业邮箱',
  `applicant_province_city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省市区',
  `applicant_invoice_receiving_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票收件地址',
  `applicant_invoice_billing_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票开票电话',
  `applicant_business_bank_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业开户行名称',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `applicant_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '投保类型：1个人 2 企业',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报价请求流水号',
  `currency` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报价号',
  `applicant_invoiced_amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票开票金额',
  `org_code` int(11) NULL DEFAULT NULL,
  `applicant_corporate_bank_account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '银行账号',
  `applicanteaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantecontacts` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantemail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicanteno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicanteno_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantephone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '投保人' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_log
-- ----------------------------
DROP TABLE IF EXISTS `insure_log`;
CREATE TABLE `insure_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_path` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求路径',
  `request_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '请求参数',
  `request_param` varchar(5000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求参数',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '请求时间',
  `return_time` datetime(0) NULL DEFAULT NULL COMMENT '返回时间',
  `return_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '返回编码',
  `return_body` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '返回参数',
  `return_msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '返回编码文本',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求流水号',
  `type` int(11) NULL DEFAULT NULL COMMENT '日志类型:1投保 2增员 3替换 4投保上传文件 5增员上传文件 6替换上传文件 7增员回调 8替换回调 9校验',
  `request_type` tinyint(11) NULL DEFAULT NULL COMMENT '请求方式1 post 2get 3put',
  `policy_id` int(11) NULL DEFAULT NULL COMMENT '保单id',
  `file_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 782 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_policy
-- ----------------------------
DROP TABLE IF EXISTS `insure_policy`;
CREATE TABLE `insure_policy`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scheme_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '方案名称(必选)',
  `policy_date_start` datetime(0) NULL DEFAULT NULL COMMENT '保单生效日',
  `policy_date_end` datetime(0) NULL DEFAULT NULL COMMENT '保单终止日',
  `product_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品代码',
  `plan_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计划代码',
  `partner_pid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分销商pid',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `type` int(11) NULL DEFAULT NULL COMMENT '保单类型：1、年单 2、月单',
  `currency` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '5-人民币，其他币种请参见全局数据字典',
  `total_premium` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '总保费\r\n\r\n',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1-正常 2-等待校验 3-失效',
  `policy_file` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电子保单',
  `kit_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单kit的地址',
  `insure_applicant_id` int(11) NULL DEFAULT NULL COMMENT '投保人id',
  `org_code` int(11) NULL DEFAULT NULL COMMENT '企业id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '投保时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '最新操作时间',
  `product_id` int(11) NULL DEFAULT NULL COMMENT '保险产品id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_polivy_no`(`policy_no`) USING BTREE COMMENT '保单唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 104 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '保单信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_product
-- ----------------------------
DROP TABLE IF EXISTS `insure_product`;
CREATE TABLE `insure_product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品名称',
  `plan_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计划代码',
  `product_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品代码',
  `type` int(11) NULL DEFAULT NULL COMMENT '类型:1年单 2月单',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `is_del` int(11) NULL DEFAULT 0 COMMENT '是否删除 0否 1是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '保险产品' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_province
-- ----------------------------
DROP TABLE IF EXISTS `insure_province`;
CREATE TABLE `insure_province`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '值',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3066 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '省市区' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_user
-- ----------------------------
DROP TABLE IF EXISTS `insure_user`;
CREATE TABLE `insure_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人请求流水号',
  `price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单价',
  `insured_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `batch_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人批次号',
  `premium` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单批次保费',
  `insured_mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人联系电话',
  `insured_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '证件号码',
  `insured_e_contact` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系人姓名',
  `benefit_basic_plan` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '基础计划\r\n30万雇主/3万医疗	36968,50万雇主/5万医疗	36969,80万雇主/10万医疗	36970,80万雇主/20万医疗	36971,100万雇主/10万医疗 36972\r\n',
  `benefit_occupation_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职业类别\r\nA类	63119\r\nB类	63120\r\nC类	63121\r\n',
  `benefit_EL_employee_number_t` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '参保人数',
  `Tricycle_frame_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二、三轮车车架号(必选)',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '参保状态：1参保中 2已失效',
  `type` tinyint(4) NULL DEFAULT NULL COMMENT '被保人类型：1个人 2企业',
  `org_code` int(11) NULL DEFAULT NULL COMMENT '企业id',
  `user_id` int(11) NULL DEFAULT NULL COMMENT '个人id',
  `applicant_employee_list` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员清单',
  `policy_date_start` datetime(0) NULL DEFAULT NULL COMMENT '生效日期',
  `policy_date_end` datetime(0) NULL DEFAULT NULL COMMENT '失效日期',
  `insure_status` int(11) NULL DEFAULT NULL COMMENT '投保状态：1成功 2失效 3待更新 4已被替换',
  `policy_id` int(11) NULL DEFAULT NULL COMMENT '保单id',
  `replace_trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '替换流水号，使用trans_id，RP_开头',
  `apply_type` int(11) NULL DEFAULT NULL COMMENT '申请类型：1新增 2投保 3批增',
  `benefit_el_employee_numbert` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `insuredecontact` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 715 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '被保人' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `timer_test`.`admin_menu_template`(`id`, `parentId`, `menu_code`, `menu_name`, `sort_number`, `path`, `enable`, `distribution`) VALUES (23, 0, 'policy', '保单管理', 23, '/policy', 1, 1);
INSERT INTO `timer_test`.`qyzx_admin_menu`(`id`, `menu_code`, `menu_name`, `sort_number`, `org_code`, `addtime`) VALUES (364, 'policy', '保单管理', 17, 1, 1648712195019);

ALTER TABLE `timer_test`.`insure_user`
ADD COLUMN `reason` varchar(255) NULL COMMENT '批改原因' AFTER `apply_type`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for insure_pay
-- ----------------------------
DROP TABLE IF EXISTS `insure_pay`;
CREATE TABLE `insure_pay`  (
   `id` int(11) NOT NULL COMMENT 'id',
   `pay_status` int(11) NULL DEFAULT NULL COMMENT '1已支付 2未支付 3已超时',
   `amount` double NULL DEFAULT NULL COMMENT '支付金额',
   `pay_time` datetime(0) NULL DEFAULT NULL COMMENT '支付时间',
   `end_time` datetime(0) NULL DEFAULT NULL COMMENT '超时时间',
   `pay_serial_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付流水号',
   `pay_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付类型',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '支付流水表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

ALTER TABLE `insure_product`
    ADD COLUMN `pay_type` int(11) NULL DEFAULT NULL COMMENT '1 在线支付 2预付款 3线下支付' AFTER `is_del`;

ALTER TABLE `insure_policy`
    ADD COLUMN `policy_pay_type` int(11) NOT NULL DEFAULT 1 COMMENT '1在线支付 2预付款 3线下支付' AFTER `create_time`,
ADD COLUMN `pay_id` int(11) NULL DEFAULT NULL COMMENT 'insure_pay表的id' AFTER `policy_pay_type`;