-- ===========================================================================
-- pbl_validation 校验引擎（M2）建表 DDL
-- 2 张表：pbl_validation_rule（14 维规则，阈值配置化）
--         pbl_validation_result（14 维得分 + 5 级质量状态 + 完整报告 JSON）
-- 幂等：CREATE TABLE IF NOT EXISTS；由 init.load_pbl_validation() 自动执行
-- 所有表 tenant_id 打头（多租户强制隔离）
-- ===========================================================================

CREATE TABLE IF NOT EXISTS `pbl_validation_rule` (
  `tenant_id` VARCHAR(64) NOT NULL COMMENT '租户ID（强制打头）',
  `rule_id` VARCHAR(64) NOT NULL COMMENT '规则ID',
  `dim` VARCHAR(8) NOT NULL COMMENT '维度编码 D01~D14',
  `dim_code` VARCHAR(64) DEFAULT NULL COMMENT '维度语义码，如 task_chain',
  `name` VARCHAR(128) DEFAULT NULL COMMENT '规则名称',
  `description` VARCHAR(512) DEFAULT NULL COMMENT '规则说明',
  `weight` DECIMAL(6,3) DEFAULT 1.000 COMMENT '维度权重（0~10）',
  `severity` VARCHAR(16) DEFAULT 'major' COMMENT 'blocker/major/minor/info',
  `script_type` INT DEFAULT 1 COMMENT '1=规则JSON形态',
  `ruleset` VARCHAR(64) DEFAULT NULL COMMENT '规则集编码，内置 builtin-14d',
  `enabled` INT DEFAULT 1 COMMENT '1启用 0停用',
  `builtin` INT DEFAULT 0 COMMENT '1=内置规则（种子），0=租户自定义',
  `rule_json` TEXT COMMENT '规则JSON：{dim,code,name,weight,desc,thresholds:{mcRatio:0.7,...}}',
  `created_at` BIGINT DEFAULT 0 COMMENT '创建时间(ms)',
  `updated_at` BIGINT DEFAULT 0 COMMENT '更新时间(ms)',
  PRIMARY KEY (`rule_id`),
  KEY `idx_pbl_validation_rule_tenant_dim` (`tenant_id`, `dim`),
  KEY `idx_pbl_validation_rule_tenant_enabled` (`tenant_id`, `enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='校验规则（14 维，规则 JSON 形态 script_type=1，阈值配置化）';

CREATE TABLE IF NOT EXISTS `pbl_validation_result` (
  `tenant_id` VARCHAR(64) NOT NULL COMMENT '租户ID（强制打头）',
  `result_id` VARCHAR(64) NOT NULL COMMENT '结果ID vr_xxx（= 报告 report_id）',
  `blueprint_id` VARCHAR(64) DEFAULT NULL COMMENT '蓝图ID',
  `version` VARCHAR(32) DEFAULT NULL COMMENT '蓝图版本',
  `ruleset` VARCHAR(64) DEFAULT NULL COMMENT '规则集编码',
  `quality_state` VARCHAR(24) DEFAULT NULL COMMENT 'Q0_DRAFT/Q1_INCOMPLETE/Q2_BASIC/Q3_GOOD/Q4_EXCELLENT',
  `quality_label` VARCHAR(32) DEFAULT NULL COMMENT '质量状态中文标签',
  `total_score` DECIMAL(6,4) DEFAULT 0 COMMENT '加权总分 0~1',
  `dim_count` INT DEFAULT 0 COMMENT '实际执行维度数（<=14）',
  `pass_count` INT DEFAULT 0 COMMENT 'pass 维度数',
  `warn_count` INT DEFAULT 0 COMMENT 'warn 维度数',
  `fail_count` INT DEFAULT 0 COMMENT 'fail 维度数',
  `skip_count` INT DEFAULT 0 COMMENT 'skip 维度数（规则禁用）',
  `blocker_count` INT DEFAULT 0 COMMENT 'blocker 问题数',
  `major_count` INT DEFAULT 0 COMMENT 'major 问题数',
  `minor_count` INT DEFAULT 0 COMMENT 'minor 问题数',
  `info_count` INT DEFAULT 0 COMMENT 'info 问题数',
  `issue_count` INT DEFAULT 0 COMMENT '问题总数',
  `dim_scores` TEXT COMMENT '各维度状态 JSON：{"D01":"pass",...}',
  `dim_detail` TEXT COMMENT '各维度得分/权重/指标 JSON',
  `fingerprint` VARCHAR(64) DEFAULT NULL COMMENT '载荷+阈值 sha256 前32位（幂等复用）',
  `engine_version` VARCHAR(16) DEFAULT NULL COMMENT '引擎版本',
  `contract` VARCHAR(48) DEFAULT NULL COMMENT '报告契约版本 pbl.validation.report/1.0',
  `report_json` LONGTEXT COMMENT '完整校验报告 JSON（契约结构）',
  `elapsed_ms` INT DEFAULT 0 COMMENT '执行耗时(ms)',
  `operator` VARCHAR(64) DEFAULT NULL COMMENT '操作人',
  `state` VARCHAR(16) DEFAULT 'done' COMMENT 'done/failed',
  `created_at` BIGINT DEFAULT 0 COMMENT '创建时间(ms)',
  `updated_at` BIGINT DEFAULT 0 COMMENT '更新时间(ms)',
  PRIMARY KEY (`result_id`),
  KEY `idx_pbl_validation_result_tenant_bp` (`tenant_id`, `blueprint_id`),
  KEY `idx_pbl_validation_result_tenant_quality` (`tenant_id`, `quality_state`),
  KEY `idx_pbl_validation_result_tenant_created` (`tenant_id`, `created_at`),
  KEY `idx_pbl_validation_result_fingerprint` (`tenant_id`, `fingerprint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='校验结果（14 维得分 + 5 级质量状态 + 完整报告 JSON）';

-- ===========================================================================
-- 内置 14 维规则种子（tenant_id='' 为全局占位；租户级由 seed_builtin_rules 写入）
-- 幂等：INSERT ... ON DUPLICATE KEY UPDATE
-- ===========================================================================
INSERT INTO `pbl_validation_rule`
  (`tenant_id`,`rule_id`,`dim`,`dim_code`,`name`,`description`,`weight`,`severity`,
   `script_type`,`ruleset`,`enabled`,`builtin`,`rule_json`,`created_at`,`updated_at`)
VALUES
 ('','builtin_d01','D01','structure_completeness','结构完整性','7 类子对象齐备性、根对象必填项',1.200,'major',1,'builtin-14d',1,1,'{"dim":"D01","code":"structure_completeness","thresholds":{"minSubObjectKinds":7}}',0,0),
 ('','builtin_d02','D02','naming_quality','命名规范','名称长度与合法字符集',0.800,'minor',1,'builtin-14d',1,1,'{"dim":"D02","code":"naming_quality","thresholds":{"minNameLen":2}}',0,0),
 ('','builtin_d03','D03','objective_alignment','学习目标对齐','目标数量、被任务/评估覆盖率',1.200,'major',1,'builtin-14d',1,1,'{"dim":"D03","code":"objective_alignment","thresholds":{"minObjectives":1,"objectiveCoverageRatio":0.8}}',0,0),
 ('','builtin_d04','D04','driving_question','驱动问题质量','驱动问题存在性、开放性、长度',1.000,'major',1,'builtin-14d',1,1,'{"dim":"D04","code":"driving_question","thresholds":{"drivingQuestionMinLen":8}}',0,0),
 ('','builtin_d05','D05','role_design','角色设计','角色数量、任务分配覆盖率、职责描述',1.000,'major',1,'builtin-14d',1,1,'{"dim":"D05","code":"role_design","thresholds":{"minRoles":1,"roleTaskCoverageRatio":0.8}}',0,0),
 ('','builtin_d06','D06','scene_design','场景设计','场景数量、孤儿场景、悬空引用',0.900,'minor',1,'builtin-14d',1,1,'{"dim":"D06","code":"scene_design","thresholds":{"minScenes":1}}',0,0),
 ('','builtin_d07','D07','entity_model','实体模型','实体数量、标识唯一性、属性定义',0.900,'minor',1,'builtin-14d',1,1,'{"dim":"D07","code":"entity_model","thresholds":{"minEntities":1}}',0,0),
 ('','builtin_d08','D08','task_chain','任务链完整性','任务数量、链路连通、环检测、多路径覆盖率 mcRatio（Q-OPEN-8，默认 0.7）',1.300,'major',1,'builtin-14d',1,1,'{"dim":"D08","code":"task_chain","thresholds":{"mcRatio":0.7,"minTasks":1,"maxTasks":200}}',0,0),
 ('','builtin_d09','D09','rule_integrity','规则引用完整性','规则引用对象存在性、表达式定义',1.100,'major',1,'builtin-14d',1,1,'{"dim":"D09","code":"rule_integrity","thresholds":{"ruleRefIntegrityRatio":1.0}}',0,0),
 ('','builtin_d10','D10','assessment_rubric','评估量规','量规存在性、评分项、权重和=1',1.200,'major',1,'builtin-14d',1,1,'{"dim":"D10","code":"assessment_rubric","thresholds":{"rubricWeightSum":1.0,"rubricWeightTolerance":0.01,"minRubricCriteria":1}}',0,0),
 ('','builtin_d11','D11','evidence_binding','产出物绑定','任务与产出物/证据绑定覆盖率',1.000,'minor',1,'builtin-14d',1,1,'{"dim":"D11","code":"evidence_binding","thresholds":{"taskEvidenceCoverageRatio":0.8}}',0,0),
 ('','builtin_d12','D12','difficulty_balance','难度平衡','难度值范围、声明率、梯度分布',0.700,'minor',1,'builtin-14d',1,1,'{"dim":"D12","code":"difficulty_balance","thresholds":{"difficultyMin":1,"difficultyMax":5}}',0,0),
 ('','builtin_d13','D13','duration_balance','时长平衡','总时长与单任务时长区间',0.700,'minor',1,'builtin-14d',1,1,'{"dim":"D13","code":"duration_balance","thresholds":{"minDurationMinutes":10,"maxDurationMinutes":600,"minutesPerTaskMin":3,"minutesPerTaskMax":60}}',0,0),
 ('','builtin_d14','D14','safety_compliance','安全合规','危险语句/凭据泄露/不适宜内容扫描',1.000,'blocker',1,'builtin-14d',1,1,'{"dim":"D14","code":"safety_compliance","thresholds":{"maxSafetyHits":0}}',0,0)
ON DUPLICATE KEY UPDATE
  `name`=VALUES(`name`), `description`=VALUES(`description`),
  `weight`=VALUES(`weight`), `severity`=VALUES(`severity`),
  `rule_json`=VALUES(`rule_json`), `updated_at`=VALUES(`updated_at`);