-- 1.0.572 · N-3 岗位映射、E-4 指标结果状态与来源能力。幂等。不删行。

CREATE TABLE IF NOT EXISTS mdp_employee_position_map (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  source_system VARCHAR(50) NOT NULL,
  domain VARCHAR(50) NOT NULL DEFAULT '',
  src_position_raw VARCHAR(200) NOT NULL COMMENT '源岗位原文',
  src_position_field VARCHAR(32) NOT NULL COMMENT 'JobTitle/Post/WorkCtr/gw/Position',
  position_code VARCHAR(24) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'WAREHOUSE/PRODUCTION/PLANNER/OTHER/UNKNOWN',
  role_source VARCHAR(24) NOT NULL DEFAULT 'AUTO_KEYWORD' COMMENT 'AUTO_KEYWORD/MANUAL',
  remark VARCHAR(500) NULL,
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_emp_pos_map (tenant_id, source_system, domain, src_position_raw)
);

CREATE TABLE IF NOT EXISTS mdp_source_capability (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  source_system VARCHAR(50) NOT NULL,
  semantic_code VARCHAR(48) NOT NULL COMMENT '附录 F 字段类语义码',
  supported TINYINT NOT NULL,
  reason VARCHAR(300) NOT NULL COMMENT '面向业务用户的原因文本',
  evidence VARCHAR(500) NULL COMMENT '核实依据',
  verified_at DATETIME NOT NULL,
  UNIQUE KEY uk_source_capability (source_system, semantic_code)
);

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l1_day' AND COLUMN_NAME='result_status')=0, 'ALTER TABLE `ado_s9_kpi_value_l1_day` ADD COLUMN `result_status` VARCHAR(24) NULL COMMENT ''OK/NO_DATA/SOURCE_UNSUPPORTED/FAILED''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l1_day' AND COLUMN_NAME='result_reason')=0, 'ALTER TABLE `ado_s9_kpi_value_l1_day` ADD COLUMN `result_reason` VARCHAR(300) NULL COMMENT ''非 OK 时的原因''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l2_day' AND COLUMN_NAME='result_status')=0, 'ALTER TABLE `ado_s9_kpi_value_l2_day` ADD COLUMN `result_status` VARCHAR(24) NULL COMMENT ''OK/NO_DATA/SOURCE_UNSUPPORTED/FAILED''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l2_day' AND COLUMN_NAME='result_reason')=0, 'ALTER TABLE `ado_s9_kpi_value_l2_day` ADD COLUMN `result_reason` VARCHAR(300) NULL COMMENT ''非 OK 时的原因''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l3_day' AND COLUMN_NAME='result_status')=0, 'ALTER TABLE `ado_s9_kpi_value_l3_day` ADD COLUMN `result_status` VARCHAR(24) NULL COMMENT ''OK/NO_DATA/SOURCE_UNSUPPORTED/FAILED''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l3_day' AND COLUMN_NAME='result_reason')=0, 'ALTER TABLE `ado_s9_kpi_value_l3_day` ADD COLUMN `result_reason` VARCHAR(300) NULL COMMENT ''非 OK 时的原因''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l4_day' AND COLUMN_NAME='result_status')=0, 'ALTER TABLE `ado_s9_kpi_value_l4_day` ADD COLUMN `result_status` VARCHAR(24) NULL COMMENT ''OK/NO_DATA/SOURCE_UNSUPPORTED/FAILED''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s9_kpi_value_l4_day' AND COLUMN_NAME='result_reason')=0, 'ALTER TABLE `ado_s9_kpi_value_l4_day` ADD COLUMN `result_reason` VARCHAR(300) NULL COMMENT ''非 OK 时的原因''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE ado_s9_kpi_value_l1_day
SET result_status=IF(metric_value IS NULL,'NO_DATA','OK'),
    result_reason=IF(metric_value IS NULL,'窗口内无业务数据',NULL)
WHERE result_status IS NULL;

UPDATE ado_s9_kpi_value_l2_day
SET result_status=IF(metric_value IS NULL,'NO_DATA','OK'),
    result_reason=IF(metric_value IS NULL,'窗口内无业务数据',NULL)
WHERE result_status IS NULL;

UPDATE ado_s9_kpi_value_l3_day
SET result_status=IF(metric_value IS NULL,'NO_DATA','OK'),
    result_reason=IF(metric_value IS NULL,'窗口内无业务数据',NULL)
WHERE result_status IS NULL;

UPDATE ado_s9_kpi_value_l4_day
SET result_status=IF(metric_value IS NULL,'NO_DATA','OK'),
    result_reason=IF(metric_value IS NULL,'窗口内无业务数据',NULL)
WHERE result_status IS NULL;

INSERT INTO mdp_source_capability (source_system, semantic_code, supported, reason, evidence, verified_at)
SELECT 'T8', 'FG_RECEIPT_TIME', 0, '源系统没有客户签收时间', 'Q2：T8 无客户签收时间字段', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM mdp_source_capability WHERE source_system='T8' AND semantic_code='FG_RECEIPT_TIME');

INSERT INTO mdp_source_capability (source_system, semantic_code, supported, reason, evidence, verified_at)
SELECT 'DOPDEMORQ_SQLSERVER', 'FG_RECEIPT_TIME', 0, '源系统没有客户签收时间', 'Q2：165 无客户签收时间字段', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM mdp_source_capability WHERE source_system='DOPDEMORQ_SQLSERVER' AND semantic_code='FG_RECEIPT_TIME');

INSERT INTO mdp_source_capability (source_system, semantic_code, supported, reason, evidence, verified_at)
SELECT 'DOPDEMORQ_SQLSERVER', 'DOC_QTY_CLOSE', 0, '165 工单行没有关闭标志来源列', 'R9.4：165 无对应列，不把状态码猜成关闭', NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM mdp_source_capability WHERE source_system='DOPDEMORQ_SQLSERVER' AND semantic_code='DOC_QTY_CLOSE');

INSERT INTO mdp_employee_position_map
  (tenant_id, source_system, domain, src_position_raw, src_position_field, position_code, role_source)
SELECT p.tenant_id, 'T8', IFNULL(p.ztid,''), p.gw, 'gw',
  CASE
    WHEN p.gw='计划' THEN 'PLANNER'
    WHEN p.gw='仓管' THEN 'WAREHOUSE'
    WHEN p.gw='生产' THEN 'PRODUCTION'
    WHEN p.gw LIKE '%仓%' OR p.gw LIKE '%库管%' OR p.gw LIKE '%保管%' OR p.gw LIKE '%物料员%' OR p.gw LIKE '%WMS%' THEN 'WAREHOUSE'
    WHEN p.gw LIKE '%生产%' OR p.gw LIKE '%操作%' OR p.gw LIKE '%装配%' OR p.gw LIKE '%车间%' OR p.gw LIKE '%班组%' OR p.gw LIKE '%产线%' OR p.gw LIKE '%工段%' THEN 'PRODUCTION'
    WHEN p.gw LIKE '%计划%' OR p.gw LIKE '%PMC%' OR p.gw LIKE '%调度%' THEN 'PLANNER'
    ELSE 'UNKNOWN' END,
  'AUTO_KEYWORD'
FROM (
  SELECT DISTINCT tenant_id, IFNULL(ztid,'') AS ztid, gw
  FROM mdp_std_t8_sys_pelist
  WHERE gw IS NOT NULL AND gw<>''
) p
ON DUPLICATE KEY UPDATE
  position_code=IF(role_source='MANUAL', position_code, VALUES(position_code)),
  src_position_field=IF(role_source='MANUAL', src_position_field, VALUES(src_position_field));

INSERT INTO mdp_employee_position_map
  (tenant_id, source_system, domain, src_position_raw, src_position_field, position_code, role_source)
SELECT e.tenant_id, 'AIDOP_NATIVE', IFNULL(e.Domain,''), e.raw_pos, 'JobTitle',
  CASE
    WHEN e.raw_pos='计划' THEN 'PLANNER'
    WHEN e.raw_pos='仓管' THEN 'WAREHOUSE'
    WHEN e.raw_pos='生产' THEN 'PRODUCTION'
    WHEN e.raw_pos LIKE '%仓%' OR e.raw_pos LIKE '%库管%' OR e.raw_pos LIKE '%保管%' OR e.raw_pos LIKE '%物料员%' OR e.raw_pos LIKE '%WMS%' THEN 'WAREHOUSE'
    WHEN e.raw_pos LIKE '%生产%' OR e.raw_pos LIKE '%操作%' OR e.raw_pos LIKE '%装配%' OR e.raw_pos LIKE '%车间%' OR e.raw_pos LIKE '%班组%' OR e.raw_pos LIKE '%产线%' OR e.raw_pos LIKE '%工段%' THEN 'PRODUCTION'
    WHEN e.raw_pos LIKE '%计划%' OR e.raw_pos LIKE '%PMC%' OR e.raw_pos LIKE '%调度%' THEN 'PLANNER'
    ELSE 'UNKNOWN' END,
  'AUTO_KEYWORD'
FROM (
  SELECT DISTINCT tenant_id, Domain, COALESCE(NULLIF(JobTitle,''), NULLIF(WorkCtr,'')) AS raw_pos
  FROM EmployeeMaster
  WHERE COALESCE(NULLIF(JobTitle,''), NULLIF(WorkCtr,'')) IS NOT NULL
) e
ON DUPLICATE KEY UPDATE
  position_code=IF(role_source='MANUAL', position_code, VALUES(position_code)),
  src_position_field=IF(role_source='MANUAL', src_position_field, VALUES(src_position_field));

ALTER TABLE mdp_std_employee
  MODIFY COLUMN position_code VARCHAR(32) NOT NULL DEFAULT 'OTHER' COMMENT 'WAREHOUSE/PRODUCTION/PLANNER/OTHER/UNKNOWN';