USE model_operations;

CREATE TABLE IF NOT EXISTS ops_rule_versions (
  rule_version_id CHAR(26) NOT NULL,
  workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces',
  category_code VARCHAR(32) NOT NULL DEFAULT '*'
    COMMENT '* 表示全部模型大类',
  version_label VARCHAR(64) NOT NULL,
  rule_status VARCHAR(24) NOT NULL DEFAULT 'draft'
    COMMENT 'draft/published/retired',
  threshold_json JSON NOT NULL COMMENT 'KS/PSI/环比切点快照',
  supersedes_rule_version_id CHAR(26) NULL,
  change_note VARCHAR(1000) NULL,
  created_by CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.users',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  published_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users',
  published_at DATETIME(3) NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (rule_version_id),
  UNIQUE KEY uk_ops_rule_versions_label (
    workspace_id, category_code, version_label
  ),
  KEY fk_ops_rule_versions_supersedes (supersedes_rule_version_id),
  KEY idx_ops_rule_versions_status (
    workspace_id, category_code, rule_status, published_at
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='监控判级规则版本';

CREATE TABLE IF NOT EXISTS ops_rule_items (
  rule_item_id CHAR(26) NOT NULL,
  rule_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_versions',
  sort_order SMALLINT NOT NULL,
  ranking_result VARCHAR(24) NOT NULL COMMENT 'matched/unmatched',
  ks_band_code VARCHAR(32) NOT NULL,
  psi_band_code VARCHAR(32) NOT NULL,
  ks_drop_band_code VARCHAR(32) NOT NULL,
  conditions_json JSON NULL COMMENT '用于跨档或扩展条件',
  abnormal_level VARCHAR(16) NOT NULL
    COMMENT 'normal/level1/level2/level3',
  monitor_grade CHAR(1) NOT NULL COMMENT 'A/B/C',
  secondary_upgrade_threshold SMALLINT NULL
    COMMENT '近6月二级异常累计升级阈值',
  reason_code VARCHAR(64) NOT NULL,
  reason_template VARCHAR(1000) NOT NULL,
  action_text VARCHAR(1000) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (rule_item_id),
  UNIQUE KEY uk_ops_rule_items_order (rule_version_id, sort_order),
  UNIQUE KEY uk_ops_rule_items_reason (rule_version_id, reason_code),
  KEY fk_ops_rule_items_version (rule_version_id),
  CONSTRAINT chk_ops_rule_items_grade
    CHECK (monitor_grade IN ('A', 'B', 'C'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='规则版本下的判级矩阵行';

CREATE TABLE IF NOT EXISTS ops_monitor_evaluations (
  evaluation_id CHAR(26) NOT NULL,
  workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces',
  monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results',
  rule_version_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_versions',
  rule_item_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_rule_items',
  ks_mom_drop_rate DECIMAL(12,8) NULL COMMENT '0-1比率',
  secondary_level2_hits_6m SMALLINT NOT NULL DEFAULT 0,
  abnormal_level VARCHAR(16) NOT NULL
    COMMENT 'normal/level1/level2/level3',
  monitor_grade CHAR(1) NOT NULL COMMENT 'A/B/C',
  reason_code VARCHAR(64) NOT NULL,
  reason_text_snapshot VARCHAR(2000) NOT NULL,
  action_snapshot VARCHAR(2000) NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 1,
  evaluated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  current_monitor_result_id CHAR(26)
    GENERATED ALWAYS AS (
      CASE
        WHEN is_current = 1 AND is_deleted = 0 THEN monitor_result_id
        ELSE NULL
      END
    ) VIRTUAL,
  PRIMARY KEY (evaluation_id),
  UNIQUE KEY uk_ops_evaluations_result_rule (
    monitor_result_id, rule_version_id
  ),
  UNIQUE KEY uk_ops_evaluations_current (current_monitor_result_id),
  KEY fk_ops_evaluations_result (monitor_result_id),
  KEY fk_ops_evaluations_rule_version (rule_version_id),
  KEY fk_ops_evaluations_rule_item (rule_item_id),
  KEY idx_ops_evaluations_grade (
    workspace_id, monitor_grade, abnormal_level, evaluated_at
  ),
  CONSTRAINT chk_ops_evaluations_grade
    CHECK (monitor_grade IN ('A', 'B', 'C'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='按规则版本生成的不可变监控判级快照';

CREATE TABLE IF NOT EXISTS ops_monitor_reviews (
  review_id CHAR(26) NOT NULL,
  workspace_id CHAR(26) NOT NULL COMMENT '逻辑引用 model_platform.workspaces',
  monitor_result_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_results',
  evaluation_id CHAR(26) NOT NULL COMMENT '逻辑引用 ops_monitor_evaluations',
  review_stage VARCHAR(24) NOT NULL
    COMMENT 'model_initial/business_final',
  review_status VARCHAR(24) NOT NULL DEFAULT 'pending'
    COMMENT 'pending/handled/auto_closed',
  decision_code VARCHAR(32) NULL COMMENT 'no_action/tune_or_rebuild',
  handling_note VARCHAR(2000) NULL COMMENT '手工处理时必填，由服务层校验',
  due_at DATETIME(3) NULL,
  handled_by CHAR(26) NULL COMMENT '逻辑引用 model_platform.users',
  handled_at DATETIME(3) NULL,
  auto_closed_at DATETIME(3) NULL,
  state_version INT NOT NULL DEFAULT 0,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (review_id),
  UNIQUE KEY uk_ops_reviews_result_stage (
    monitor_result_id, review_stage
  ),
  KEY fk_ops_reviews_evaluation (evaluation_id),
  KEY fk_ops_reviews_handler (handled_by),
  KEY idx_ops_reviews_pending (
    workspace_id, review_status, review_stage, due_at
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='模型团队初审与业务团队终审';

CREATE TABLE IF NOT EXISTS ops_outbox_events (
  event_id CHAR(26) NOT NULL,
  aggregate_type VARCHAR(64) NOT NULL,
  aggregate_id VARCHAR(128) NOT NULL,
  event_type VARCHAR(128) NOT NULL,
  schema_version SMALLINT NOT NULL DEFAULT 1,
  payload_json JSON NOT NULL,
  event_status VARCHAR(16) NOT NULL DEFAULT 'pending'
    COMMENT 'pending/published/failed',
  available_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  retry_count INT NOT NULL DEFAULT 0,
  trace_id VARCHAR(64) NULL,
  idempotency_key VARCHAR(128) NULL,
  published_at DATETIME(3) NULL,
  last_error VARCHAR(2000) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (event_id),
  UNIQUE KEY uk_ops_outbox_idempotency (idempotency_key),
  KEY idx_ops_outbox_pending (event_status, available_at, created_at),
  KEY idx_ops_outbox_aggregate (aggregate_type, aggregate_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='运维库事务 Outbox；由运维异步执行器轮询';

CREATE TABLE IF NOT EXISTS ops_consumer_inbox (
  consumer_name VARCHAR(128) NOT NULL,
  event_id CHAR(26) NOT NULL,
  process_status VARCHAR(16) NOT NULL DEFAULT 'processing'
    COMMENT 'processing/succeeded/failed',
  message_id VARCHAR(128) NULL,
  processed_at DATETIME(3) NULL,
  error_message VARCHAR(2000) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME(3) NULL,
  PRIMARY KEY (consumer_name, event_id),
  KEY idx_ops_inbox_status (
    consumer_name, process_status, created_at
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='运维异步消费者幂等 Inbox';