-- 图像表添加平安上传文件key
ALTER TABLE `ins_task_images` ADD COLUMN `pingan_key` varchar(200) DEFAULT null COMMENT '平安上传文件key';

-- 子订单表添加  平安流程id    平安通知单号
ALTER TABLE `ins_area_company` ADD COLUMN `flowid` varchar(50) DEFAULT null COMMENT '平安流程id';
ALTER TABLE `ins_area_company` ADD COLUMN `notice_no` varchar(50) DEFAULT null COMMENT '平安通知单号';

-- 添加平安日志表
DROP TABLE IF EXISTS `pingan_api_log`;
CREATE TABLE `pingan_api_log`  (
                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                   `orderno` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '大订单编号',
                                   `agree_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '协议id',
                                   `sub_order_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '小订单编号',
                                   `request_url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '请求路径',
                                   `request_param` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '请求参数',
                                   `request_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '请求类型',
                                   `response_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '响应数据',
                                   `response_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '响应类型',
                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 583 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '平安日志' ROW_FORMAT = Dynamic;

-- add by  hxl   20524-07-31  添加人力成本表
DROP TABLE IF EXISTS `ins_labor_cost`;
CREATE TABLE `ins_labor_cost`  (
                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                   `year_and_month` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '年',
                                   `labor_cost` decimal(10, 2) NULL DEFAULT NULL COMMENT '人力成本',
                                   `fixed_cost` decimal(10, 2) NULL DEFAULT NULL COMMENT '固定成本',
                                   `variable_cost` decimal(10, 2) NULL DEFAULT NULL COMMENT '变动成本',
                                   `operating_profit` decimal(10, 2) NULL DEFAULT NULL COMMENT '经营利润',
                                   `taxation` decimal(10, 2) NULL DEFAULT NULL COMMENT '税费',
                                   `net_profit` decimal(10, 2) NULL DEFAULT NULL COMMENT '净利润',
                                   `create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
                                   `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                   `update_user` datetime NULL DEFAULT NULL COMMENT '修改人',
                                   `update_date` datetime NULL DEFAULT NULL COMMENT '修改时间',
                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '人力成本表' ROW_FORMAT = Dynamic;



--add by caiyaru 20240806
DROP TABLE IF EXISTS `sys_qr_code_relation`;
create table `sys_qr_code_relation`(
                                           `id` bigint auto_increment PRIMARY KEY comment '主键id',
                                           `api_type` tinyint comment 'api类型（1、api对接，2、python对接）',
                                           `ins_company_id` varchar(32) comment '保险公司ID',
                                           `ins_company_name` varchar(255) comment '保险公司名称',
                                           `qr_code_type` tinyint comment '二维码类型（0:Base64,1:内部链接,2:外部链接）',
                                           `create_by` varchar(255) comment '创建操作人',
                                           `create_time` timestamp comment '创建时间',
                                           `last_update_by` varchar(255) comment '更新操作人',
                                           `last_update_time` timestamp comment '更新时间',
                                           CONSTRAINT  `sys_qr_code_relation_uk` unique(`api_type`,`ins_company_id`,`ins_company_name`,`qr_code_type`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin comment='二维码类型关联表' ROW_FORMAT = Dynamic;

INSERT INTO `sys_dict_type`(`id`, `name`, `parent_id`, `itemname`, `description`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ('108', '车险平台', NULL, 'insurancePlatform', NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '0', 0);
INSERT INTO `sys_dict_type`(`id`, `name`, `parent_id`, `itemname`, `description`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ('108001', '二维码', '108', 'QRCode', NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '0', 0);

INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('0', 'Base64', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP);
INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('1', '内部链接', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP);
INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('2', '外部链接', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP);


DROP TABLE IF EXISTS `sys_user_track`;
create table `sys_user_track`(
                                     `id` bigint auto_increment PRIMARY KEY comment '主键id',
                                     `user_id` varchar(30) not null comment '用户id',
                                     `status` tinyint comment '状态  0,"离司",1,"正常",2,"入司待审核",3,"入司审核驳回",4,"离司待审核",5,"离司审核驳回"',
                                     `approve_by` varchar(255) comment '审核人',
                                     `approve_time` timestamp comment '审核时间',
                                     `approve_mess` varchar(255) comment '审核信息',
                                     index `idx_sys_user_track_user_id`(`user_id`),
                                     index `idx_sys_user_track_approve_time`(`approve_time`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin comment='系统用户审核轨迹表' ROW_FORMAT = Dynamic;