-- =============================================================================
-- 人事调动/离职填报表 lq_personnel_report
-- 说明：
--   1. 主键 F_Id 须由应用层 YitIdHelper.NextId().ToString() 生成。
--   2. 独立待办填报，不联动 BASE_USER、员工档案、流程、考勤、算薪等主业务。
--   3. 来源组织/门店、提交人由服务端从当前登录用户快照写入。
--   4. 幂等：CREATE TABLE IF NOT EXISTS + 索引通过 information_schema 判断后创建。
-- =============================================================================

CREATE TABLE IF NOT EXISTS `lq_personnel_report` (
  `F_Id` varchar(50) NOT NULL COMMENT '主键',
  `F_ReportNo` varchar(32) NOT NULL COMMENT '填报编号（对外展示，唯一）',
  `F_ApplicationType` int NOT NULL COMMENT '申请类型：1调动 2离职',
  `F_ApplicantUserId` varchar(50) NOT NULL COMMENT '被填报员工用户ID（BASE_USER.F_Id）',
  `F_ApplicantUserName` varchar(128) DEFAULT NULL COMMENT '被填报员工姓名快照',
  `F_ApplicantUserMobile` varchar(32) DEFAULT NULL COMMENT '被填报员工手机号快照',
  `F_SourceOrganizeId` varchar(50) DEFAULT NULL COMMENT '来源组织ID（提交人当前组织快照）',
  `F_SourceOrganizeName` varchar(200) DEFAULT NULL COMMENT '来源组织名称快照',
  `F_SourceMdid` varchar(50) DEFAULT NULL COMMENT '来源门店ID（提交人当前门店快照）',
  `F_SourceMdName` varchar(200) DEFAULT NULL COMMENT '来源门店名称快照',
  `F_SubmitterUserId` varchar(50) DEFAULT NULL COMMENT '提交人用户ID',
  `F_SubmitterUserName` varchar(128) DEFAULT NULL COMMENT '提交人姓名',
  `F_SubmitTime` datetime NOT NULL COMMENT '提交时间',
  `F_TargetMdid` varchar(50) DEFAULT NULL COMMENT '目标门店ID（调动时必填）',
  `F_TargetMdName` varchar(200) DEFAULT NULL COMMENT '目标门店名称快照',
  `F_TransferDate` date DEFAULT NULL COMMENT '调动日期（调动时必填）',
  `F_ResignationDate` date DEFAULT NULL COMMENT '离职日期（离职时必填）',
  `F_ResignationReason` varchar(500) DEFAULT NULL COMMENT '离职原因（离职时必填）',
  `F_Remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `F_HandleStatus` int NOT NULL DEFAULT 0 COMMENT '处理状态：0待处理 1已处理',
  `F_HandleUserId` varchar(50) DEFAULT NULL COMMENT '处理人ID',
  `F_HandleUserName` varchar(128) DEFAULT NULL COMMENT '处理人姓名',
  `F_HandleTime` datetime DEFAULT NULL COMMENT '处理时间',
  `F_HandleRemark` varchar(500) DEFAULT NULL COMMENT '处理备注',
  `F_CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
  `F_UpdateTime` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`F_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人事调动/离职填报（独立待办）';

-- 唯一索引：填报编号
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'UK_lq_personnel_report_No'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_personnel_report` ADD UNIQUE KEY `UK_lq_personnel_report_No` (`F_ReportNo`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：处理状态 + 提交时间（列表/工作台待办）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_Handle_SubmitTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_Handle_SubmitTime` (`F_HandleStatus`, `F_SubmitTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：来源组织 + 提交时间（权限过滤列表）
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_SourceOrg_SubmitTime'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_SourceOrg_SubmitTime` (`F_SourceOrganizeId`, `F_SubmitTime`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 联合索引：申请类型 + 处理状态
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_Type_Handle'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_Type_Handle` (`F_ApplicationType`, `F_HandleStatus`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 普通索引：被填报员工
SET @idx_exists := (
  SELECT COUNT(1) FROM information_schema.statistics
  WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_ApplicantUserId'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_ApplicantUserId` (`F_ApplicantUserId`)',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;