-- hr-roster 花名册字段体系(F03) DDL
-- 5张表: roster_field_group/roster_field_def/roster_type_rule/roster_empno_rule/roster_op_log
-- 含唯一索引、普通索引、软删除、审计字段、外键、初始化数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `roster_field_group`;
CREATE TABLE `roster_field_group` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_code` VARCHAR(64) NOT NULL,
  `group_name` VARCHAR(128) NOT NULL,
  `group_desc` VARCHAR(512) NULL DEFAULT NULL,
  `is_builtin` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_group_code` (`group_code`),
  KEY `idx_sort` (`sort_order`), KEY `idx_enabled` (`is_enabled`), KEY `idx_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花名册字段分组';

INSERT INTO `roster_field_group` (`group_code`,`group_name`,`group_desc`,`is_builtin`,`sort_order`,`is_enabled`) VALUES
('work_info','工作信息','组织/岗位/职级/工号等',1,10,1),
('personal_info','个人信息','姓名/证件/联系方式等',1,20,1),
('contract_info','合同信息','合同/入离职/试用期等',1,30,1),
('payroll_info','薪酬信息','薪资/社保/公积金等',1,40,1);

DROP TABLE IF EXISTS `roster_field_def`;
CREATE TABLE `roster_field_def` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` BIGINT UNSIGNED NOT NULL,
  `field_code` VARCHAR(64) NOT NULL,
  `field_name` VARCHAR(128) NOT NULL,
  `field_type` VARCHAR(32) NOT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `is_unique` TINYINT(1) NOT NULL DEFAULT 0,
  `is_sensitive` TINYINT(1) NOT NULL DEFAULT 0,
  `default_value` VARCHAR(512) NULL DEFAULT NULL,
  `options_json` JSON NULL,
  `validation_rule` VARCHAR(512) NULL DEFAULT NULL,
  `placeholder` VARCHAR(256) NULL DEFAULT NULL,
  `help_text` VARCHAR(512) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_field_code` (`field_code`),
  KEY `idx_group` (`group_id`), KEY `idx_sort` (`group_id`,`sort_order`),
  KEY `idx_enabled` (`is_enabled`), KEY `idx_deleted` (`deleted_at`),
  CONSTRAINT `fk_field_group` FOREIGN KEY (`group_id`) REFERENCES `roster_field_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花名册字段定义';

DROP TABLE IF EXISTS `roster_type_rule`;
CREATE TABLE `roster_type_rule` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rule_code` VARCHAR(64) NOT NULL,
  `rule_name` VARCHAR(128) NOT NULL,
  `emp_type` VARCHAR(32) NOT NULL,
  `required_field_codes` JSON NULL,
  `extra_config` JSON NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rule_code` (`rule_code`),
  KEY `idx_emp_type` (`emp_type`), KEY `idx_enabled` (`is_enabled`), KEY `idx_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工类型必填规则';

INSERT INTO `roster_type_rule` (`rule_code`,`rule_name`,`emp_type`,`required_field_codes`) VALUES
('type_formal','正式工必填规则','正式工','["emp_no","emp_name","dept_id","position_id","join_date"]'),
('type_temp','临时工必填规则','临时工','["emp_no","emp_name","dept_id","join_date"]'),
('type_intern','实习生必填规则','实习生','["emp_no","emp_name","join_date","end_date"]');

DROP TABLE IF EXISTS `roster_empno_rule`;
CREATE TABLE `roster_empno_rule` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rule_code` VARCHAR(64) NOT NULL,
  `rule_name` VARCHAR(128) NOT NULL,
  `subsidiary_code` VARCHAR(32) NULL DEFAULT NULL,
  `prefix` VARCHAR(32) NULL DEFAULT NULL,
  `date_format` VARCHAR(32) NULL DEFAULT NULL,
  `seq_length` INT NOT NULL DEFAULT 4,
  `start_seq` INT NOT NULL DEFAULT 1,
  `current_seq` BIGINT NOT NULL DEFAULT 0,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rule_code` (`rule_code`),
  KEY `idx_subsidiary` (`subsidiary_code`), KEY `idx_enabled` (`is_enabled`), KEY `idx_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工号生成规则';

INSERT INTO `roster_empno_rule` (`rule_code`,`rule_name`,`subsidiary_code`,`prefix`,`date_format`,`seq_length`,`start_seq`,`current_seq`) VALUES
('empno_default','默认工号规则',NULL,'E','%Y%m',5,1,0),
('empno_subs_a','子公司A工号规则','001','A','%Y%m',4,1,0);

DROP TABLE IF EXISTS `roster_op_log`;
CREATE TABLE `roster_op_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `op_type` VARCHAR(32) NOT NULL,
  `target_table` VARCHAR(64) NOT NULL,
  `target_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `before_json` JSON NULL,
  `after_json` JSON NULL,
  `operator_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `operator_name` VARCHAR(128) NULL DEFAULT NULL,
  `client_ip` VARCHAR(64) NULL DEFAULT NULL,
  `remark` VARCHAR(512) NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_target` (`target_table`,`target_id`), KEY `idx_op_type` (`op_type`),
  KEY `idx_operator` (`operator_id`), KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花名册操作审计日志';

SET FOREIGN_KEY_CHECKS = 1;