/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


CREATE TABLE `channel_info` (
  `channel_id` int NOT NULL AUTO_INCREMENT COMMENT '渠道ID',
  `channel_name` varchar(100) NOT NULL COMMENT '渠道名字',
  `channel_type` varchar(50) DEFAULT NULL COMMENT '渠道类型',
  `contact_person` varchar(50) DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `description` text COMMENT '渠道描述',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='渠道信息表';


CREATE TABLE `disabled_bank_card` (
  `card_id` int NOT NULL AUTO_INCREMENT COMMENT '银行卡ID',
  `person_id` int NOT NULL COMMENT '残疾人ID',
  `sub_bank_name` varchar(100) NOT NULL COMMENT '发卡支行',
  `bank_name` varchar(50) NOT NULL COMMENT '银行名称',
  `card_number` varchar(50) NOT NULL COMMENT '卡号',
  `cardholder_name` varchar(50) NOT NULL COMMENT '持卡人姓名',
  `card_photo_url` varchar(255) NOT NULL COMMENT '银行卡照片URL',
  `is_default` tinyint NOT NULL DEFAULT '0' COMMENT '是否默认：1-是，0-否',
  PRIMARY KEY (`card_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `disabled_bank_card_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='残疾人银行卡信息表';


CREATE TABLE `disabled_person` (
  `person_id` int NOT NULL AUTO_INCREMENT COMMENT '残疾人ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `gender` char(1) NOT NULL COMMENT '性别：男/女',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号',
  `disability_id` varchar(50) NOT NULL COMMENT '残疾证号',
  `disability_type` varchar(50) NOT NULL COMMENT '残疾类型',
  `disability_level` varchar(20) NOT NULL COMMENT '残疾等级',
  `id_address` varchar(200) NOT NULL COMMENT '身份证地址',
  `id_valid_date` date DEFAULT NULL COMMENT '身份证有效期',
  `disability_valid_date` date DEFAULT NULL COMMENT '残疾证有效期',
  `phone` varchar(20) NOT NULL COMMENT '联系方式',
  `can_direct_contact` tinyint NOT NULL DEFAULT '1' COMMENT '是否可直接联系：1-是，0-否',
  `is_married` tinyint DEFAULT NULL COMMENT '是否已婚：1-是，0-否',
  `nation` varchar(20) DEFAULT NULL COMMENT '民族',
  `province` varchar(50) NOT NULL COMMENT '省级',
  `city` varchar(50) NOT NULL COMMENT '市级',
  `district` varchar(50) DEFAULT NULL COMMENT '区县级',
  `detailed_address` varchar(200) DEFAULT NULL COMMENT '详细地址',
  `is_in_black_list` tinyint NOT NULL DEFAULT '0' COMMENT '是否在黑名单中：1-是，0-否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `job_status` tinyint NOT NULL DEFAULT '0' COMMENT '在职状态：1-在职 0-不在职',
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `id_card` (`id_card`),
  UNIQUE KEY `disability_id` (`disability_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='残疾人基本信息表';


CREATE TABLE `disabled_photo` (
  `photo_id` int NOT NULL AUTO_INCREMENT COMMENT '照片ID',
  `person_id` int NOT NULL COMMENT '残疾人身份证ID',
  `photo_type` varchar(50) NOT NULL COMMENT '照片类型',
  `photo_url` varchar(255) NOT NULL COMMENT '照片URL',
  `upload_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `can_download` tinyint NOT NULL DEFAULT '1' COMMENT '是否可下载：1-是，0-否',
  PRIMARY KEY (`photo_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `disabled_photo_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='残疾人照片信息表';


CREATE TABLE `disabled_remark` (
  `remark_id` int NOT NULL AUTO_INCREMENT COMMENT '备注ID',
  `person_id` int NOT NULL COMMENT '残疾人身份证ID',
  `remark_content` text NOT NULL COMMENT '备注内容',
  `is_special_needs` tinyint NOT NULL DEFAULT '0' COMMENT '是否特殊需求：1-是，0-否',
  `remark_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '备注时间',
  `operator_id` int NOT NULL COMMENT '操作人ID',
  PRIMARY KEY (`remark_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `disabled_remark_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='残疾人备注信息表';


CREATE TABLE `disabled_visit` (
  `visit_id` int NOT NULL AUTO_INCREMENT COMMENT '回访ID',
  `person_id` int NOT NULL COMMENT '残疾人ID',
  `visit_date` date NOT NULL COMMENT '回访日期',
  `visit_type` varchar(50) NOT NULL COMMENT '回访类型',
  `visit_content` text NOT NULL COMMENT '回访内容',
  `visit_result` varchar(50) DEFAULT NULL COMMENT '回访结果',
  `next_visit_date` date DEFAULT NULL COMMENT '下次回访日期',
  `visitor_id` int NOT NULL COMMENT '回访人ID',
  `visit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`visit_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `disabled_visit_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='残疾人回访记录表';

CREATE TABLE `employer_company` (
  `company_id` int NOT NULL AUTO_INCREMENT COMMENT '公司ID',
  `platform_id` int NOT NULL COMMENT '所属平台ID',
  `company_name` varchar(100) NOT NULL COMMENT '公司名字',
  `contact_person` varchar(50) NOT NULL COMMENT '负责人（非必填）',
  `contact_phone` varchar(20) NOT NULL COMMENT '联系电话',
  `contact_email` varchar(100) DEFAULT NULL COMMENT '联系邮箱',
  `address` varchar(200) DEFAULT NULL COMMENT '公司地址',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`company_id`),
  KEY `platform_id` (`platform_id`),
  CONSTRAINT `employer_company_ibfk_1` FOREIGN KEY (`platform_id`) REFERENCES `employer_platform` (`platform_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用人单位公司信息表';


CREATE TABLE `employer_platform` (
  `platform_id` int NOT NULL AUTO_INCREMENT COMMENT '平台ID',
  `platform_name` varchar(100) NOT NULL COMMENT '平台名字',
  `contact_person` varchar(50) NOT NULL COMMENT '负责人',
  `contact_phone` varchar(20) NOT NULL COMMENT '联系电话',
  `contact_email` varchar(100) DEFAULT NULL COMMENT '联系邮箱',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`platform_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用人单位平台信息表';


CREATE TABLE `employment_order` (
  `order_id` int NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `platform_id` int NOT NULL COMMENT '用人平台ID',
  `company_id` int NOT NULL COMMENT '用人单位ID',
  `channel_id` int DEFAULT NULL COMMENT '渠道ID',
  `expected_start_date` date DEFAULT NULL COMMENT '预计开始日期',
  `actual_start_date` date DEFAULT NULL COMMENT '实际开始日期',
  `actual_end_date` date DEFAULT NULL COMMENT '实际结束日期',
  `order_status` varchar(20) NOT NULL COMMENT '订单状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `order_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`order_id`),
  KEY `platform_id` (`platform_id`),
  KEY `company_id` (`company_id`),
  KEY `channel_id` (`channel_id`),
  CONSTRAINT `employment_order_ibfk_1` FOREIGN KEY (`platform_id`) REFERENCES `employer_platform` (`platform_id`) ON DELETE CASCADE,
  CONSTRAINT `employment_order_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `employer_company` (`company_id`) ON DELETE CASCADE,
  CONSTRAINT `employment_order_ibfk_3` FOREIGN KEY (`channel_id`) REFERENCES `channel_info` (`channel_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单信息表';


CREATE TABLE `order_person` (
  `op_id` int NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `order_id` int NOT NULL COMMENT '订单ID',
  `person_id` int NOT NULL COMMENT '残疾人ID',
  `join_date` date NOT NULL COMMENT '入职日期',
  `leave_date` date DEFAULT NULL COMMENT '离职日期',
  `work_status` varchar(20) NOT NULL COMMENT '工作状态',
  `salary_detail` decimal(10,2) NOT NULL COMMENT '个人薪资',
  PRIMARY KEY (`op_id`),
  UNIQUE KEY `uk_order_person` (`order_id`,`person_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `order_person_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `employment_order` (`order_id`) ON DELETE CASCADE,
  CONSTRAINT `order_person_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单人员关联表';


CREATE TABLE `order_person_asset` (
  `op_id` int NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `order_id` int NOT NULL COMMENT '订单ID',
  `person_id` int NOT NULL COMMENT '残疾人ID',
  `asset_type` varchar(50) NOT NULL COMMENT '资产类型：tax-个税；salary-薪资；job_result-工作成果；contract_sign-合同签约；disability_cert-残疾证；other-其他',
  `asset_file_type` varchar(50) NOT NULL COMMENT '资源文件类型：image-图片；video-视频',
  `asset_url` varchar(255) NOT NULL COMMENT '资产URL',
  `related_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '关联时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`op_id`),
  KEY `order_id` (`order_id`),
  KEY `person_id` (`person_id`),
  CONSTRAINT `order_person_asset_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `employment_order` (`order_id`) ON DELETE CASCADE,
  CONSTRAINT `order_person_asset_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `disabled_person` (`person_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单人员图片视频资源关联表';


CREATE TABLE `salary_level` (
  `salary_id` int NOT NULL AUTO_INCREMENT COMMENT '薪资ID',
  `province` varchar(50) NOT NULL COMMENT '省份',
  `city` varchar(50) NOT NULL COMMENT '城市',
  `district` varchar(50) DEFAULT NULL COMMENT '区县',
  `basic_salary` decimal(10,2) NOT NULL COMMENT '基本工资',
  `allowance` decimal(10,2) DEFAULT '0.00' COMMENT '津贴补贴',
  `insurance` decimal(10,2) DEFAULT '0.00' COMMENT '保险费用',
  `housing_fund` decimal(10,2) DEFAULT '0.00' COMMENT '住房公积金',
  `total_salary` decimal(10,2) NOT NULL COMMENT '总薪资',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`salary_id`),
  UNIQUE KEY `uk_province_city_type` (`province`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='省市薪资水平表';


CREATE TABLE `sys_dict` (
  `dict_id` int NOT NULL AUTO_INCREMENT COMMENT '字典ID',
  `dict_type` varchar(50) NOT NULL COMMENT '字典类型',
  `dict_code` varchar(50) NOT NULL COMMENT '字典编码',
  `dict_value` varchar(100) NOT NULL COMMENT '字典值',
  `sort_order` int NOT NULL DEFAULT '0' COMMENT '排序',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `parent_code` varchar(50) DEFAULT NULL COMMENT '父编码',
  PRIMARY KEY (`dict_id`),
  UNIQUE KEY `uk_dict_type_code` (`dict_type`,`dict_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据字典表';

LOCK TABLES `sys_dict` WRITE;
/*!40000 ALTER TABLE `sys_dict` DISABLE KEYS */;

INSERT INTO `sys_dict` (`dict_id`, `dict_type`, `dict_code`, `dict_value`, `sort_order`, `status`, `create_time`, `update_time`, `parent_code`)
VALUES
	(1,'disability_type','vision','视力残疾',1,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(2,'disability_type','hearing','听力残疾',2,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(3,'disability_type','speech','言语残疾',3,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(4,'disability_type','physical','肢体残疾',4,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(5,'disability_type','intellectual','智力残疾',5,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(6,'disability_type','mental','精神残疾',6,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(7,'disability_type','multiple','多重残疾',7,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(8,'disability_level','1','一级',1,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(9,'disability_level','2','二级',2,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(10,'disability_level','3','三级',3,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(11,'disability_level','4','四级',4,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(12,'order_status','draft','草稿',1,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(13,'order_status','confirmed','已确认',2,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(14,'order_status','in_progress','进行中',3,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(15,'order_status','completed','已完成',4,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(16,'order_status','cancelled','已取消',5,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(17,'work_status','not_working','未入职',1,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(18,'work_status','pre_working','待入职',2,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(19,'work_status','working','在职',3,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(20,'work_status','resigned','离职',4,1,'2025-11-08 09:59:41','2025-11-08 09:59:41',NULL),
	(21,'province','GD','广东省',0,1,'2025-11-08 10:13:14','2025-11-08 10:13:14',NULL),
	(22,'city','SZ','深圳市',0,1,'2025-11-08 10:13:23','2025-11-08 10:13:23','GD'),
	(23,'province','BJ','北京市',0,1,'2025-11-08 10:13:35','2025-11-08 10:13:35',NULL),
	(24,'district','NS','南山区',0,1,'2025-11-08 10:19:44','2025-11-08 10:19:44','SZ'),
	(25,'province','SHANDONG','山东省',0,1,'2025-11-08 10:20:53','2025-11-08 10:20:53',NULL),
	(26,'city','JN','济南市',0,1,'2025-11-08 10:21:18','2025-11-08 10:21:18','SHANDONG'),
	(27,'city','beijingshi','北京市',0,1,'2025-11-22 14:06:04','2025-11-22 14:06:04','BJ'),
	(28,'disability_type','test','测试残疾',0,1,'2025-11-24 23:59:15','2025-11-24 23:59:15',NULL);

/*!40000 ALTER TABLE `sys_dict` ENABLE KEYS */;
UNLOCK TABLES;



/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;