-- 1.0.343 运营指标正式目标配置 + 日值目标血缘 + MDP FILE_EXCEL 批次
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `ado_smart_ops_kpi_target_config` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT NOT NULL,
  `factory_id` BIGINT NOT NULL DEFAULT 0,
  `metric_code` VARCHAR(50) NOT NULL,
  `target_value` DECIMAL(18,6) NOT NULL,
  `effective_from` DATE NOT NULL,
  `effective_to` DATE NULL,
  `source_type` VARCHAR(30) NOT NULL,
  `source_batch_id` VARCHAR(100) NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `remark` VARCHAR(500) NULL,
  `created_by` BIGINT NULL,
  `updated_by` BIGINT NULL,
  `create_time` DATETIME(3) NOT NULL,
  `update_time` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_kpi_target_start` (`tenant_id`, `factory_id`, `metric_code`, `effective_from`),
  KEY `ix_kpi_target_resolve` (`tenant_id`, `factory_id`, `metric_code`, `status`, `effective_from`, `effective_to`),
  KEY `ix_kpi_target_batch` (`source_batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运营指标正式目标配置';

CREATE TABLE IF NOT EXISTS `ado_smart_ops_kpi_target_import_batch` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `batch_no` VARCHAR(64) NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `factory_id` BIGINT NOT NULL DEFAULT 0,
  `file_name` VARCHAR(255) NOT NULL,
  `file_sha256` VARCHAR(64) NOT NULL,
  `status` VARCHAR(32) NOT NULL,
  `total_rows` INT NOT NULL DEFAULT 0,
  `valid_rows` INT NOT NULL DEFAULT 0,
  `error_rows` INT NOT NULL DEFAULT 0,
  `payload_json` MEDIUMTEXT NULL,
  `error_json` MEDIUMTEXT NULL,
  `created_by` VARCHAR(100) NULL,
  `create_time` DATETIME(3) NOT NULL,
  `commit_time` DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_kpi_target_batch_no` (`batch_no`),
  KEY `ix_kpi_target_batch_tenant` (`tenant_id`, `status`),
  KEY `ix_kpi_target_batch_hash` (`tenant_id`, `file_sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI目标Excel导入批次';

CREATE TABLE IF NOT EXISTS `mdp_file_import_batch` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `batch_no` VARCHAR(64) NOT NULL,
  `tenant_id` BIGINT NOT NULL,
  `factory_id` BIGINT NOT NULL DEFAULT 0,
  `source_id` BIGINT NOT NULL,
  `entity_id` BIGINT NOT NULL,
  `entity_code` VARCHAR(100) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_sha256` VARCHAR(64) NOT NULL,
  `file_size` BIGINT NOT NULL DEFAULT 0,
  `sheet_name` VARCHAR(100) NULL,
  `import_mode` VARCHAR(40) NOT NULL DEFAULT 'UPSERT',
  `status` VARCHAR(32) NOT NULL,
  `total_rows` INT NOT NULL DEFAULT 0,
  `valid_rows` INT NOT NULL DEFAULT 0,
  `error_rows` INT NOT NULL DEFAULT 0,
  `written_rows` INT NOT NULL DEFAULT 0,
  `file_object_key` VARCHAR(500) NULL,
  `error_report_key` VARCHAR(500) NULL,
  `transform_status` VARCHAR(32) NULL,
  `transform_time` DATETIME(3) NULL,
  `created_by` VARCHAR(100) NULL,
  `create_time` DATETIME(3) NOT NULL,
  `commit_time` DATETIME(3) NULL,
  `error_message` VARCHAR(1000) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mdp_file_batch_no` (`batch_no`),
  KEY `ix_mdp_file_batch_tenant` (`tenant_id`, `status`),
  KEY `ix_mdp_file_batch_hash` (`tenant_id`, `file_sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP Excel文件导入批次';

CREATE TABLE IF NOT EXISTS `mdp_file_import_row` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT NOT NULL,
  `row_no` INT NOT NULL,
  `business_key` VARCHAR(500) NULL,
  `row_hash` VARCHAR(64) NULL,
  `raw_json` MEDIUMTEXT NULL,
  `mapped_json` MEDIUMTEXT NULL,
  `validation_status` VARCHAR(32) NOT NULL DEFAULT 'OK',
  `write_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING',
  `error_count` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `ix_mdp_file_row_batch` (`batch_id`, `row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP Excel导入原始行';

CREATE TABLE IF NOT EXISTS `mdp_file_import_error` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT NOT NULL,
  `row_no` INT NOT NULL,
  `field_name` VARCHAR(200) NULL,
  `error_code` VARCHAR(64) NOT NULL,
  `error_message` VARCHAR(1000) NOT NULL,
  `raw_value` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  KEY `ix_mdp_file_err_batch` (`batch_id`, `row_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MDP Excel导入错误';

-- 四级日值目标血缘列
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='target_config_id') = 0,
  'ALTER TABLE `ado_s9_kpi_value_l1_day`
     ADD COLUMN `target_config_id` BIGINT NULL,
     ADD COLUMN `target_source` VARCHAR(30) NULL,
     ADD COLUMN `target_resolved_at` DATETIME(3) NULL',
  'SELECT 1');
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='target_config_id') = 0,
  'ALTER TABLE `ado_s9_kpi_value_l2_day`
     ADD COLUMN `target_config_id` BIGINT NULL,
     ADD COLUMN `target_source` VARCHAR(30) NULL,
     ADD COLUMN `target_resolved_at` DATETIME(3) NULL',
  'SELECT 1');
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='target_config_id') = 0,
  'ALTER TABLE `ado_s9_kpi_value_l3_day`
     ADD COLUMN `target_config_id` BIGINT NULL,
     ADD COLUMN `target_source` VARCHAR(30) NULL,
     ADD COLUMN `target_resolved_at` DATETIME(3) NULL',
  'SELECT 1');
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='target_config_id') = 0,
  'ALTER TABLE `ado_s9_kpi_value_l4_day`
     ADD COLUMN `target_config_id` BIGINT NULL,
     ADD COLUMN `target_source` VARCHAR(30) NULL,
     ADD COLUMN `target_resolved_at` DATETIME(3) NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- mdp_entity 文件配置
SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_entity' AND COLUMN_NAME='file_config_json') = 0,
  'ALTER TABLE `mdp_entity` ADD COLUMN `file_config_json` TEXT NULL',
  'SELECT 1');
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='mdp_entity' AND COLUMN_NAME='is_public_template') = 0,
  'ALTER TABLE `mdp_entity` ADD COLUMN `is_public_template` TINYINT NOT NULL DEFAULT 0',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 试点：复制 S1_CUSTOMER_SQLSERVER 为 FILE 公共模板（不写业务目标数据）
INSERT INTO `mdp_source` (
  `tenant_id`, `source_code`, `source_name`, `source_type`, `status`,
  `remark`, `create_time`, `update_time`)
SELECT 0, 'S1_CUSTOMER_FILE', 'S1客户主数据 Excel 入站', 'FILE_EXCEL', 1,
       '由 S1_CUSTOMER_SQLSERVER 复制的 FILE 试点源；is_public_template 见实体', NOW(), NOW()
FROM DUAL
WHERE EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code`='S1_CUSTOMER_SQLSERVER' AND `status`=1)
  AND NOT EXISTS (SELECT 1 FROM `mdp_source` WHERE `source_code`='S1_CUSTOMER_FILE');

INSERT INTO `mdp_entity` (
  `tenant_id`, `source_id`, `entity_code`, `entity_name`, `entity_type`,
  `target_table_name`, `sync_mode`, `batch_size`, `biz_key_expr`, `status`,
  `file_config_json`, `is_public_template`, `remark`, `create_time`, `update_time`)
SELECT
  0,
  (SELECT `id` FROM `mdp_source` WHERE `source_code`='S1_CUSTOMER_FILE' LIMIT 1),
  'S1_CUSTOMER_FILE',
  'S1客户主数据 Excel 入站',
  'FILE',
  src.`target_table_name`,
  'UPSERT',
  5000,
  src.`biz_key_expr`,
  1,
  '{"sheetName":"数据","headerRow":1,"maxRows":50000,"allowedExtensions":[".xlsx"],"importMode":"UPSERT","rebuildable":false,"transformTaskCode":"S1_MDP_SYNC_TRANSFORM"}',
  1,
  'FILE 试点实体，贴源表/业务键与 S1_CUSTOMER_SQLSERVER 一致',
  NOW(), NOW()
FROM `mdp_entity` src
WHERE src.`entity_code`='S1_CUSTOMER_SQLSERVER' AND src.`status`=1
  AND NOT EXISTS (SELECT 1 FROM `mdp_entity` WHERE `entity_code`='S1_CUSTOMER_FILE')
LIMIT 1;

INSERT INTO `mdp_field_mapping` (
  `entity_id`, `source_field`, `target_field`, `field_type`, `is_required`, `sort_order`, `create_time`)
SELECT e.`id`, 'Domain', 'Domain', 'DIRECT', 1, 10, NOW()
FROM `mdp_entity` e
WHERE e.`entity_code`='S1_CUSTOMER_FILE'
  AND NOT EXISTS (SELECT 1 FROM `mdp_field_mapping` m WHERE m.`entity_id`=e.`id` AND m.`source_field`='Domain');

INSERT INTO `mdp_field_mapping` (
  `entity_id`, `source_field`, `target_field`, `field_type`, `is_required`, `sort_order`, `create_time`)
SELECT e.`id`, 'Customer', 'Customer', 'DIRECT', 1, 20, NOW()
FROM `mdp_entity` e
WHERE e.`entity_code`='S1_CUSTOMER_FILE'
  AND NOT EXISTS (SELECT 1 FROM `mdp_field_mapping` m WHERE m.`entity_id`=e.`id` AND m.`source_field`='Customer');

INSERT INTO `mdp_field_mapping` (
  `entity_id`, `source_field`, `target_field`, `field_type`, `is_required`, `sort_order`, `create_time`)
SELECT e.`id`, 'CustomerName', 'CustomerName', 'DIRECT', 0, 30, NOW()
FROM `mdp_entity` e
WHERE e.`entity_code`='S1_CUSTOMER_FILE'
  AND NOT EXISTS (SELECT 1 FROM `mdp_field_mapping` m WHERE m.`entity_id`=e.`id` AND m.`source_field`='CustomerName');