#!/usr/bin/env bash
set -euo pipefail

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
RESET_SCRIPT="$ROOT_DIR/scripts/reset-dev-db.sh"
LEARNING_CLOSURE_MIGRATION="backend/script/sql/update/aihr_20260717_learning_closure_mysql8.sql"
PRACTICE_GROWTH_MIGRATION="backend/script/sql/update/aihr_20260724_practice_growth_curriculum_mysql8.sql"
LEARNING_TASK_EXAM_BANK_MIGRATION="backend/script/sql/update/aihr_20260802_learning_task_exam_bank_mysql8.sql"
PRACTICE_BASE_SCHEMA="backend/script/sql/aihr_practice_mysql8.sql"
CASE_GOVERNANCE_MIGRATION="backend/script/sql/update/aihr_20260802_case_experience_governance_mysql8.sql"
WEB_AI_BASE_MIGRATION="backend/script/sql/update/aihr_20260717_web_ai_question_reward_mysql8.sql"
WEB_AI_SECRET_MIGRATION="backend/script/sql/update/aihr_20260719_web_ai_database_secret_mysql8.sql"
WEB_SEARCH_QWEN_MIGRATION="backend/script/sql/update/aihr_20260719_web_search_qwen_mysql8.sql"

learning_closure_line="$(grep -nF "$LEARNING_CLOSURE_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
practice_growth_line="$(grep -nF "$PRACTICE_GROWTH_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
learning_task_exam_bank_line="$(grep -nF "$LEARNING_TASK_EXAM_BANK_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
if [[ -z "$learning_closure_line" || -z "$practice_growth_line" || -z "$learning_task_exam_bank_line" || "$learning_closure_line" -ge "$practice_growth_line" || "$practice_growth_line" -ge "$learning_task_exam_bank_line" ]]; then
  echo "FAIL: reset-dev-db.sh must apply learning closure before practice growth curriculum" >&2
  exit 1
fi

practice_base_line="$(grep -nF "$PRACTICE_BASE_SCHEMA" "$RESET_SCRIPT" | cut -d: -f1 || true)"
case_governance_line="$(grep -nF "$CASE_GOVERNANCE_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
if [[ -z "$practice_base_line" || -z "$case_governance_line" || "$practice_base_line" -ge "$case_governance_line" ]]; then
  echo "FAIL: reset-dev-db.sh must apply case experience governance after the practice base schema" >&2
  exit 1
fi

web_ai_base_line="$(grep -nF "$WEB_AI_BASE_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
web_ai_secret_line="$(grep -nF "$WEB_AI_SECRET_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
web_search_qwen_line="$(grep -nF "$WEB_SEARCH_QWEN_MIGRATION" "$RESET_SCRIPT" | cut -d: -f1 || true)"
if [[ -z "$web_ai_base_line" || -z "$web_ai_secret_line" || -z "$web_search_qwen_line"
      || "$web_ai_base_line" -ge "$web_ai_secret_line" || "$web_ai_secret_line" -ge "$web_search_qwen_line" ]]; then
  echo "FAIL: reset-dev-db.sh must apply the web AI base, secret, and Qwen migrations in order" >&2
  exit 1
fi

while IFS= read -r migration; do
  migration_name="$(basename "$migration")"
  grep -Fq "backend/script/sql/update/$migration_name" "$ROOT_DIR/docs/BRD_PRODUCTION_MIGRATION_RUNBOOK.md" || {
    echo "FAIL: AIHR migration missing from production Runbook: $migration_name" >&2
    exit 1
  }
done < <(find "$ROOT_DIR/backend/script/sql/update" -maxdepth 1 -type f -name 'aihr_*_mysql8.sql' -print | sort)

if ! command -v docker >/dev/null || ! docker ps --format '{{.Names}}' | grep -qx 'wygj-mysql'; then
  echo "PASS: AIHR migration inventory is documented"
  echo "SKIP: AIHR schema migration test requires wygj-mysql"
  exit 0
fi

MYSQL_CONTAINER="wygj-mysql"
DB="wygj_aihr_schema_test_$$"
LEGACY_DB="wygj_aihr_candidate_legacy_test_$$"

mysql() {
  docker exec -i "$MYSQL_CONTAINER" mysql -uroot -proot --default-character-set=utf8mb4 "$@"
}

cleanup() {
  mysql -e "DROP DATABASE IF EXISTS \`$DB\`;" >/dev/null 2>&1 || true
  mysql -e "DROP DATABASE IF EXISTS \`$LEGACY_DB\`;" >/dev/null 2>&1 || true
}
trap cleanup EXIT

mysql -e "CREATE DATABASE \`$DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"

mysql "$DB" <<'SQL'
CREATE TABLE aihr_knowledge_upload_item (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  staging_path varchar(500) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SQL
media_reprocess_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260729_media_reprocess_mysql8.sql"
mysql "$DB" < "$media_reprocess_migration"
mysql "$DB" < "$media_reprocess_migration"
media_retry_column="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_upload_item' AND column_name='source_attach_id';")"
test "$media_retry_column" = '1'
media_retry_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_upload_item' AND index_name='idx_aihr_upload_item_source';")"
test "$media_retry_index" = 'tenant_id,source_attach_id,id'
mysql "$DB" -e "DROP TABLE aihr_knowledge_upload_item;"

web_ai_base_migration="$ROOT_DIR/$WEB_AI_BASE_MIGRATION"
web_ai_secret_migration="$ROOT_DIR/$WEB_AI_SECRET_MIGRATION"
web_search_qwen_migration="$ROOT_DIR/$WEB_SEARCH_QWEN_MIGRATION"
mysql "$DB" < "$web_ai_base_migration"
mysql "$DB" < "$web_ai_base_migration"
mysql "$DB" < "$web_ai_secret_migration"
mysql "$DB" < "$web_ai_secret_migration"
mysql "$DB" < "$web_search_qwen_migration"
mysql "$DB" < "$web_search_qwen_migration"
web_search_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_web_search_provider' AND column_name IN ('api_key','agent_id','agent_version','last_test_ok','last_test_time');")"
test "$web_search_columns" = 'api_key,agent_id,agent_version,last_test_ok,last_test_time'
web_ai_secret_count="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM aihr_web_ai_secret WHERE config_key='provider-encryption-v1' AND CHAR_LENGTH(config_value) > 0;")"
test "$web_ai_secret_count" = '1'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_case_media_mysql8.sql"
mysql "$DB" <<'SQL'
CREATE TABLE aihr_case_record (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  case_id varchar(80) NOT NULL,
  summary varchar(1000) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_aihr_case_record_case (tenant_id, case_id)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_case_media_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_case_media_mysql8.sql"
case_media_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_case_record' AND column_name IN ('media_oss_id','media_url');")"
test "$case_media_columns" = 'media_oss_id,media_url'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_case_learning_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_case_learning_mysql8.sql"
case_learning_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_case_record' AND column_name IN ('learning_points','supervisor_comment');")"
test "$case_learning_columns" = 'learning_points,supervisor_comment'

case_governance_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260802_case_experience_governance_mysql8.sql"
test -f "$case_governance_migration"
mysql "$DB" < "$case_governance_migration"
mysql "$DB" < "$case_governance_migration"
case_governance_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_case_record' AND column_name IN ('knowledge_status','source_type','applicable_positions','content_version','source_authorized','deidentified','effective_from','effective_to','release_owner','release_note','released_by','released_time','retire_reason');")"
test "$case_governance_columns" = 'knowledge_status,source_type,applicable_positions,content_version,source_authorized,deidentified,effective_from,effective_to,release_owner,release_note,released_by,released_time,retire_reason'
case_governance_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_case_record' AND index_name='idx_aihr_case_knowledge_status';")"
test "$case_governance_index" = 'tenant_id,knowledge_status,effective_from,effective_to'

mysql "$DB" <<'SQL'
CREATE TABLE aihr_prompt_template (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  code varchar(80) NOT NULL,
  name varchar(100) NOT NULL,
  category varchar(50) NOT NULL,
  output_format varchar(50) NOT NULL,
  system_prompt text,
  template text NOT NULL,
  variables varchar(1000),
  version varchar(30) DEFAULT 'v1',
  enabled tinyint DEFAULT 1,
  description varchar(500),
  create_time datetime,
  update_time datetime,
  PRIMARY KEY (id),
  UNIQUE KEY uk_aihr_prompt_template (tenant_id, code, output_format, version)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_prompt_template_seed_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_prompt_template_seed_mysql8.sql"
partial_evidence_prompt_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260725_sop_answer_partial_evidence_prompt_mysql8.sql"
mysql "$DB" < "$partial_evidence_prompt_migration"
mysql "$DB" < "$partial_evidence_prompt_migration"
prompt_template_count="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM aihr_prompt_template WHERE tenant_id='000000';")"
test "$prompt_template_count" = '7'
partial_evidence_prompt_count="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM aihr_prompt_template WHERE tenant_id='000000' AND code='sop_answer' AND version='v2' AND enabled=1 AND system_prompt LIKE '%只要片段包含与问题相关的操作%';")"
test "$partial_evidence_prompt_count" = '1'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/aihr_model_mysql8.sql"
mysql "$DB" <<'SQL'
INSERT INTO aihr_model_config
  (tenant_id, category, model_name, provider_code, model_show, enabled, create_time, update_time)
VALUES
  ('000000', 'chat', 'deepseek-chat', 'deepseek', 'Y', 1, NOW(), NOW()),
  ('100001', 'chat', 'deepseek-chat', 'deepseek', 'Y', 1, NOW(), NOW()),
  ('100001', 'chat', 'deepseek-v4-flash', 'deepseek', 'N', 0, NOW(), NOW());
SQL
deepseek_model_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260725_deepseek_v4_model_mysql8.sql"
mysql "$DB" < "$deepseek_model_migration"
mysql "$DB" < "$deepseek_model_migration"
deepseek_model_rows="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(CONCAT(tenant_id, ':', model_name, ':', enabled, ':', model_show) ORDER BY tenant_id, model_name) FROM aihr_model_config WHERE provider_code='deepseek';")"
test "$deepseek_model_rows" = '000000:deepseek-v4-flash:1:Y,100001:deepseek-chat:0:N,100001:deepseek-v4-flash:1:Y'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_knowledge_feedback_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_knowledge_feedback_mysql8.sql"
knowledge_feedback_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_knowledge_answer_feedback','aihr_knowledge_gap');")"
test "$knowledge_feedback_tables" = 'aihr_knowledge_answer_feedback,aihr_knowledge_gap'

mysql "$DB" -e "DROP TABLE aihr_knowledge_answer_feedback"
mysql "$DB" <<'SQL'
CREATE TABLE aihr_knowledge_answer_feedback (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  query_text varchar(1000) NOT NULL,
  query_norm varchar(500) NOT NULL,
  fragment_id bigint NOT NULL,
  verdict varchar(10) NOT NULL,
  category varchar(100),
  position varchar(80),
  source varchar(50),
  review_status varchar(30),
  create_time datetime,
  update_time datetime,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_knowledge_feedback_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_knowledge_feedback_mysql8.sql"
feedback_review_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_answer_feedback' AND column_name IN ('review_id','reviewer','reviewed_time');")"
test "$feedback_review_columns" = 'review_id,reviewer,reviewed_time'
feedback_question_bridge_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260802_feedback_question_bridge_mysql8.sql"
mysql "$DB" < "$feedback_question_bridge_migration"
mysql "$DB" < "$feedback_question_bridge_migration"
feedback_bridge_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_answer_feedback' AND column_name IN ('submitter_user_id','request_id');")"
test "$feedback_bridge_columns" = 'submitter_user_id,request_id'
feedback_bridge_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_answer_feedback' AND index_name='uk_aihr_answer_feedback_user_request';")"
test "$feedback_bridge_unique" = '0|tenant_id,submitter_user_id,request_id,fragment_id|0'
question_bridge_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_community_question' AND column_name IN ('source_type','source_request_id','submit_idempotency_key');")"
test "$question_bridge_columns" = 'source_type,source_request_id,submit_idempotency_key'
question_bridge_source_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_community_question' AND index_name='uk_aihr_community_question_source';")"
test "$question_bridge_source_unique" = '0|tenant_id,author_user_id,source_type,source_request_id|0'
question_bridge_submit_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_community_question' AND index_name='uk_aihr_community_question_submit_key';")"
test "$question_bridge_submit_unique" = '0|tenant_id,author_user_id,submit_idempotency_key|0'

learning_task_exam_bank_migration="$ROOT_DIR/$LEARNING_TASK_EXAM_BANK_MIGRATION"
mysql "$DB" < "$learning_task_exam_bank_migration"
mysql "$DB" < "$learning_task_exam_bank_migration"
learning_question_exam_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_learning_question' AND column_name IN ('exam_format','exam_options_json','exam_answers_json','exam_explanation','exam_enabled','review_status','review_note','reviewed_by','reviewed_time');")"
test "$learning_question_exam_columns" = 'exam_format,exam_options_json,exam_answers_json,exam_explanation,exam_enabled,review_status,review_note,reviewed_by,reviewed_time'
learning_question_exam_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_learning_question' AND index_name='idx_aihr_learning_question_exam_pick';")"
test "$learning_question_exam_index" = '1|tenant_id,position_code,review_status,exam_enabled,enabled,id|0'
learning_draw_table="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB' AND table_name='aihr_exam_question_draw';")"
test "$learning_draw_table" = '1'
learning_task_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_onboard_task' AND column_name IN ('material_type','material_attachment_id','material_title','material_version','progress_percent','completion_rule','exam_id','recurrence_type','recurrence_series_key','dispatch_request_key');")"
test "$learning_task_columns" = '10'
learning_task_dispatch_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_onboard_task' AND index_name='uk_aihr_onboard_task_dispatch';")"
test "$learning_task_dispatch_index" = '0|tenant_id,ext_party_id,dispatch_request_key|0'

mysql "$DB" <<'SQL'
CREATE TABLE aihr_practice_session (
  id bigint NOT NULL AUTO_INCREMENT,
  session_id varchar(100) NOT NULL,
  total_score int,
  ai_comment varchar(1000),
  PRIMARY KEY (id)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_practice_session_evidence_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_practice_session_evidence_mysql8.sql"
practice_session_evidence_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_practice_session' AND column_name IN ('annotations_json','review_advice','incentive_point','satisfaction_score','satisfaction_comment');")"
test "$practice_session_evidence_columns" = '5'

# The growth curriculum migration must be able to upgrade the compact legacy
# practice tables used by older deployments, and be safe when applied again.
mysql "$DB" <<'SQL'
ALTER TABLE aihr_practice_session
  ADD COLUMN tenant_id varchar(20) DEFAULT '000000',
  ADD COLUMN mode varchar(20) DEFAULT 'mobile',
  ADD COLUMN scenario_name varchar(100) DEFAULT NULL;
CREATE TABLE aihr_practice_scenario (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  scenario_code varchar(80) NOT NULL,
  position varchar(80) DEFAULT '生活顾问',
  difficulty tinyint DEFAULT 1,
  goal varchar(1000) DEFAULT NULL,
  enabled tinyint DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_aihr_practice_scenario (tenant_id, scenario_code)
) ENGINE=InnoDB;
INSERT INTO aihr_practice_scenario (tenant_id, scenario_code, position, difficulty, goal, enabled) VALUES
  ('000000', 'legacy-growth-enabled', '生活顾问', 2, 'legacy goal', 1),
  ('000000', 'legacy-growth-disabled', '保安', 3, 'legacy goal', 0);
CREATE TABLE aihr_practice_assignment (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) DEFAULT '000000',
  ext_party_id varchar(100) NOT NULL,
  request_key varchar(100) DEFAULT NULL,
  source varchar(30) NOT NULL DEFAULT 'manual',
  content_hash char(64) DEFAULT NULL,
  create_time datetime DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
SQL
practice_growth_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260724_practice_growth_curriculum_mysql8.sql"
test -f "$practice_growth_migration"
mysql "$DB" < "$practice_growth_migration"
# A high-risk item may already have completed its first review when a release
# re-runs this idempotent migration. Its audit trail must survive for the
# second reviewer instead of being reset to an unreviewed item.
mysql "$DB" <<'SQL'
UPDATE aihr_practice_scenario
SET review_status = '待业务审核', risk_level = '高风险',
    reviewer = '首审甲', reviewer_user_id = 101, reviewed_time = '2026-07-24 10:00:00'
WHERE scenario_code = 'legacy-growth-enabled';
SQL
mysql "$DB" < "$practice_growth_migration"
practice_growth_scenario_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY column_name) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_practice_scenario' AND column_name IN ('growth_level','competency_code','collaboration_positions','review_status','risk_level','curriculum_version','reviewer','reviewer_user_id','reviewed_time','second_reviewer','second_reviewer_user_id','second_reviewed_time');")"
test "$practice_growth_scenario_columns" = 'collaboration_positions,competency_code,curriculum_version,growth_level,review_status,reviewed_time,reviewer,reviewer_user_id,risk_level,second_reviewed_time,second_reviewer,second_reviewer_user_id'
practice_growth_session_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY column_name) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_practice_session' AND column_name IN ('position_snapshot','project_type_snapshot','growth_level','competency_code','collaboration_positions','curriculum_version','growth_confirmation_level','growth_confirmed_by','growth_confirmed_time');")"
test "$practice_growth_session_columns" = 'collaboration_positions,competency_code,curriculum_version,growth_confirmation_level,growth_confirmed_by,growth_confirmed_time,growth_level,position_snapshot,project_type_snapshot'
practice_growth_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_scenario' AND index_name='idx_aihr_practice_scenario_curriculum';")"
test "$practice_growth_index" = 'tenant_id,position,growth_level,competency_code,review_status,enabled'
practice_assignment_request_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_assignment' AND index_name='uk_aihr_assignment_request';")"
test "$practice_assignment_request_index" = '0|tenant_id,request_key,ext_party_id|0'
practice_assignment_recent_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_assignment' AND index_name='idx_aihr_assignment_recent_content';")"
test "$practice_assignment_recent_index" = '1|tenant_id,ext_party_id,source,content_hash,create_time|0'
mysql "$DB" -e "ALTER TABLE aihr_practice_assignment DROP INDEX uk_aihr_assignment_request, ADD INDEX uk_aihr_assignment_request (tenant_id, request_key(20), ext_party_id), DROP INDEX idx_aihr_assignment_recent_content, ADD KEY idx_aihr_assignment_recent_content (tenant_id, ext_party_id, source, content_hash);"
mysql "$DB" < "$practice_growth_migration"
practice_assignment_repaired_request_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_assignment' AND index_name='uk_aihr_assignment_request';")"
test "$practice_assignment_repaired_request_index" = '0|tenant_id,request_key,ext_party_id|0'
practice_assignment_repaired_recent_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_assignment' AND index_name='idx_aihr_assignment_recent_content';")"
test "$practice_assignment_repaired_recent_index" = '1|tenant_id,ext_party_id,source,content_hash,create_time|0'
practice_growth_statuses="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(CONCAT(scenario_code,':',review_status) ORDER BY scenario_code) FROM aihr_practice_scenario WHERE scenario_code LIKE 'legacy-growth-%';")"
test "$practice_growth_statuses" = 'legacy-growth-disabled:待业务审核,legacy-growth-enabled:待业务审核'
practice_growth_first_review="$(mysql "$DB" -N -B -e "SELECT CONCAT(COALESCE(reviewer,''),':',COALESCE(reviewer_user_id,'')) FROM aihr_practice_scenario WHERE scenario_code='legacy-growth-enabled';")"
test "$practice_growth_first_review" = '首审甲:101'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_practice_evidence_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_practice_evidence_mysql8.sql"
evidence_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_practice_audio','aihr_practice_audio_upload','aihr_practice_calibration');")"
test "$evidence_tables" = 'aihr_practice_audio,aihr_practice_audio_upload,aihr_practice_calibration'

practice_help_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260723_practice_m2_help_event_mysql8.sql"
test -f "$practice_help_migration"
mysql "$DB" < "$practice_help_migration"
mysql "$DB" < "$practice_help_migration"
practice_help_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_practice_help_event';")"
test "$practice_help_columns" = 'id,tenant_id,session_id,scenario_id,ext_party_id,round_index,create_time'
practice_help_tenant_collation="$(mysql "$DB" -N -B -e "SELECT CONCAT(character_set_name,'|',collation_name) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_practice_help_event' AND column_name='tenant_id';")"
test "$practice_help_tenant_collation" = 'utf8mb4|utf8mb4_general_ci'
practice_help_session_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_practice_help_event' AND index_name='idx_aihr_practice_help_session';")"
test "$practice_help_session_index" = 'tenant_id,session_id,create_time'

mysql "$DB" <<'SQL'
CREATE TABLE aihr_sop_answer_review (
  id bigint NOT NULL AUTO_INCREMENT,
  snippet_count int DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
SQL

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_sop_requester_identity_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_sop_requester_identity_mysql8.sql"
sop_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_sop_answer_review' AND column_name IN ('source','prompt_version','requester_ext_party_id');")"
test "$sop_columns" = 'source,prompt_version,requester_ext_party_id'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_org_hire_date_mysql8.sql"
mysql "$DB" <<'SQL'
CREATE TABLE aihr_org_snapshot (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) NOT NULL,
  project_code varchar(50) NOT NULL,
  ext_party_id varchar(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_aihr_org_party (tenant_id, ext_party_id)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_org_hire_date_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260714_org_hire_date_mysql8.sql"
org_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_org_snapshot' AND column_name='hire_date';")"
test "$org_columns" = 'hire_date'
org_multi_project_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260721_org_multi_project_membership_mysql8.sql"
mysql "$DB" < "$org_multi_project_migration"
mysql "$DB" < "$org_multi_project_migration"
org_party_unique="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_org_snapshot' AND index_name='uk_aihr_org_party';")"
test "$org_party_unique" = 'tenant_id,project_code,ext_party_id'

tenant_org_governance_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260723_tenant_org_governance_mysql8.sql"
test -f "$tenant_org_governance_migration"
mysql "$DB" < "$tenant_org_governance_migration"
mysql "$DB" < "$tenant_org_governance_migration"
tenant_org_governance_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_org_directory','aihr_tenant_org_binding');")"
test "$tenant_org_governance_tables" = 'aihr_org_directory,aihr_tenant_org_binding'
tenant_org_binding_unique="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_tenant_org_binding' AND index_name='uk_aihr_tenant_org_binding_tenant';")"
test "$tenant_org_binding_unique" = 'tenant_id'
tenant_org_binding_tenant_collation="$(mysql "$DB" -N -B -e "SELECT CONCAT(character_set_name,'|',collation_name) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_tenant_org_binding' AND column_name='tenant_id';")"
test "$tenant_org_binding_tenant_collation" = 'utf8mb4|utf8mb4_general_ci'

mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_position_sop_qualification_mysql8.sql"
mysql "$DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_position_sop_qualification_mysql8.sql"
position_sop_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_onboard_task','aihr_position_responsibility','aihr_qualification_gate','aihr_sop_applicability');")"
test "$position_sop_tables" = 'aihr_onboard_task,aihr_position_responsibility,aihr_qualification_gate,aihr_sop_applicability'
position_responsibility_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_position_responsibility' AND column_name IN ('position_code','responsibility_text','sop_id','sop_code');")"
test "$position_responsibility_columns" = '4'
sop_applicability_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_sop_applicability' AND column_name IN ('sop_id','sop_code','project_type','industry','org_scope');")"
test "$sop_applicability_columns" = '5'
onboard_task_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_onboard_task' AND column_name IN ('ext_party_id','position_code','task_type','status','assign_by');")"
test "$onboard_task_columns" = '5'
qualification_gate_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_qualification_gate' AND column_name IN ('ext_party_id','position_code','cert_id','passed','valid_thru');")"
test "$qualification_gate_columns" = '5'

mysql -e "CREATE DATABASE \`$LEGACY_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
mysql "$LEGACY_DB" <<'SQL'
CREATE TABLE aihr_candidate_material (
  id bigint NOT NULL AUTO_INCREMENT,
  status varchar(20) DEFAULT '待审核',
  PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE aihr_interview_result (
  id bigint NOT NULL AUTO_INCREMENT,
  session_id varchar(100) NOT NULL,
  total_score int,
  answers_json text,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
SQL
mysql "$LEGACY_DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_candidate_interview_review_mysql8.sql"
mysql "$LEGACY_DB" < "$ROOT_DIR/backend/script/sql/update/aihr_20260715_candidate_interview_review_mysql8.sql"
candidate_interview_tables="$(mysql "$LEGACY_DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$LEGACY_DB' AND table_name IN ('aihr_candidate_material','aihr_interview_result','aihr_candidate_employee_link');")"
test "$candidate_interview_tables" = 'aihr_candidate_employee_link,aihr_candidate_material,aihr_interview_result'
candidate_interview_columns="$(mysql "$LEGACY_DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$LEGACY_DB' AND ((table_name = 'aihr_candidate_material' AND column_name IN ('reviewer','reviewed_time')) OR (table_name = 'aihr_interview_result' AND column_name IN ('questions_json','reviewed_score','review_status','reviewer','review_note','reviewed_time')));")"
test "$candidate_interview_columns" = '8'

service_memory_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260719_service_memory_mysql8.sql"
test -f "$service_memory_migration"
mysql "$DB" < "$service_memory_migration"
mysql "$DB" < "$service_memory_migration"
service_memory_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_memory_candidate','aihr_assistant_capture','aihr_service_memory','aihr_service_memory_version');")"
test "$service_memory_tables" = 'aihr_assistant_capture,aihr_memory_candidate,aihr_service_memory,aihr_service_memory_version'
assistant_capture_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_assistant_capture' AND column_name IN ('save_scope','suggested_type','delivery_status','confirm_idempotency_key','confirm_request_hash');")"
test "$assistant_capture_columns" = '5'
service_memory_version_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND ((table_name = 'aihr_memory_candidate' AND column_name IN ('version','confirm_idempotency_key','confirm_request_hash','source_conversation_id','source_request_id')) OR (table_name = 'aihr_service_memory' AND column_name IN ('project_code','version')) OR (table_name = 'aihr_service_memory_version' AND column_name IN ('memory_id','version')));")"
test "$service_memory_version_columns" = '9'

work_assistant_results_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260721_work_assistant_results_mysql8.sql"
test -f "$work_assistant_results_migration"
mysql "$DB" < "$work_assistant_results_migration"
mysql "$DB" < "$work_assistant_results_migration"
work_assistant_result_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_assistant_capture_status_log','aihr_daily_work_result','aihr_knowledge_conversation');")"
test "$work_assistant_result_tables" = 'aihr_assistant_capture_status_log,aihr_daily_work_result,aihr_knowledge_conversation'
work_assistant_result_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND ((table_name = 'aihr_knowledge_conversation' AND column_name = 'project_code') OR (table_name = 'aihr_memory_candidate' AND column_name IN ('work_date','source_snapshot_json')) OR (table_name = 'aihr_assistant_capture' AND column_name IN ('work_date','business_status','source_snapshot_json')));")"
test "$work_assistant_result_columns" = '6'
work_assistant_snapshot_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_daily_work_result' AND column_name='content_hash';")"
test "$work_assistant_snapshot_columns" = '1'

broadcast_question_context_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260722_broadcast_question_context_mysql8.sql"
test -f "$broadcast_question_context_migration"
mysql "$DB" < "$broadcast_question_context_migration"
mysql "$DB" < "$broadcast_question_context_migration"
broadcast_question_context_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_conversation' AND column_name='broadcast_message_id';")"
test "$broadcast_question_context_columns" = 'broadcast_message_id'
broadcast_question_context_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_conversation' AND index_name='idx_aihr_knowledge_conversation_broadcast';")"
test "$broadcast_question_context_index" = 'tenant_id,broadcast_message_id'

work_report_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260719_work_report_idempotency_mysql8.sql"
test -f "$work_report_migration"
mysql "$DB" < "$work_report_migration"
mysql "$DB" < "$work_report_migration"
work_report_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_work_report' AND column_name IN ('request_key','request_hash');")"
test "$work_report_columns" = 'request_key,request_hash'
work_report_unique_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_work_report' AND index_name='uk_aihr_work_report_request';")"
test "$work_report_unique_index" = 'tenant_id,submitter_user_id,request_key'

mysql "$DB" -e "ALTER TABLE aihr_work_report DROP INDEX uk_aihr_work_report_request, ADD INDEX uk_aihr_work_report_request (tenant_id, request_key(20), submitter_user_id);"
mysql "$DB" < "$work_report_migration"
work_report_repaired_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_work_report' AND index_name='uk_aihr_work_report_request';")"
test "$work_report_repaired_index" = '0|tenant_id,submitter_user_id,request_key|0'

mysql "$DB" <<'SQL'
ALTER TABLE aihr_work_report
  DROP INDEX uk_aihr_work_report_request,
  ADD INDEX uk_aihr_work_report_request (tenant_id, request_key(20), submitter_user_id);
INSERT INTO aihr_work_report (
  tenant_id, report_type, title, content, status,
  submitter_user_id, request_key, request_hash
) VALUES
  ('000000', 'CASE', 'duplicate migration guard 1', 'content', 'PENDING', 1, 'duplicate-migration-guard', REPEAT('a', 64)),
  ('000000', 'CASE', 'duplicate migration guard 2', 'content', 'PENDING', 1, 'duplicate-migration-guard', REPEAT('b', 64));
SQL
if mysql "$DB" < "$work_report_migration" >/dev/null 2>&1; then
  echo "FAIL: work-report migration must reject duplicate idempotency keys" >&2
  exit 1
fi
work_report_failed_repair_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_work_report' AND index_name='uk_aihr_work_report_request';")"
test "$work_report_failed_repair_index" = '1|tenant_id,request_key,submitter_user_id|1'

mysql "$DB" <<'SQL'
CREATE TABLE aihr_knowledge_info (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE aihr_knowledge_attach (
  id bigint NOT NULL AUTO_INCREMENT,
  tenant_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  knowledge_id bigint NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL
knowledge_category_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260722_knowledge_category_mysql8.sql"
test -f "$knowledge_category_migration"
mysql "$DB" < "$knowledge_category_migration"
mysql "$DB" < "$knowledge_category_migration"
knowledge_category_table="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB' AND table_name='aihr_knowledge_category';")"
test "$knowledge_category_table" = '1'
knowledge_category_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_category' AND column_name IN ('tenant_id','knowledge_id','code','name','status','sort_order');")"
test "$knowledge_category_columns" = 'tenant_id,knowledge_id,code,name,status,sort_order'
knowledge_attach_category_column="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_attach' AND column_name='category_id';")"
test "$knowledge_attach_category_column" = '1'
knowledge_attach_category_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_attach' AND index_name='idx_aihr_knowledge_attach_category';")"
test "$knowledge_attach_category_index" = 'tenant_id,knowledge_id,category_id'
knowledge_category_code_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_category' AND index_name='uk_aihr_knowledge_category_code';")"
test "$knowledge_category_code_index" = '0|tenant_id,knowledge_id,code|0'
knowledge_category_collation="$(mysql "$DB" -N -B -e "SELECT table_collation FROM information_schema.tables WHERE table_schema='$DB' AND table_name='aihr_knowledge_category';")"
test "$knowledge_category_collation" = 'utf8mb4_general_ci'

formal_source_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260730_formal_knowledge_source_governance_mysql8.sql"
test -f "$formal_source_migration"
mysql "$DB" < "$formal_source_migration"
mysql "$DB" < "$formal_source_migration"
formal_source_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_knowledge_source_governance' AND column_name IN ('tenant_id','source_id','attachment_id','source_doc_id','authority_type','lifecycle_status','source_version','effective_date','expires_date','applicability_scope','content_sha256','approval_basis','approved_by','approved_time');")"
test "$formal_source_columns" = 'tenant_id,source_id,attachment_id,source_doc_id,authority_type,lifecycle_status,source_version,effective_date,expires_date,applicability_scope,content_sha256,approval_basis,approved_by,approved_time'
formal_source_attachment_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_knowledge_source_governance' AND index_name='uk_aihr_knowledge_source_attachment';")"
test "$formal_source_attachment_unique" = '0|tenant_id,attachment_id'
formal_source_collation="$(mysql "$DB" -N -B -e "SELECT table_collation FROM information_schema.tables WHERE table_schema='$DB' AND table_name='aihr_knowledge_source_governance';")"
test "$formal_source_collation" = 'utf8mb4_general_ci'

broadcast_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260722_broadcast_mysql8.sql"
test -f "$broadcast_migration"
mysql "$DB" < "$broadcast_migration"
mysql "$DB" < "$broadcast_migration"
broadcast_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_broadcast_message','aihr_broadcast_read','aihr_broadcast_version');")"
test "$broadcast_tables" = 'aihr_broadcast_message,aihr_broadcast_read,aihr_broadcast_version'
broadcast_publish_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND column_name IN ('publish_request_key','publish_request_hash','withdraw_reason');")"
test "$broadcast_publish_columns" = 'publish_request_key,publish_request_hash,withdraw_reason'
broadcast_publish_unique_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND index_name='uk_aihr_broadcast_message_publish_request';")"
test "$broadcast_publish_unique_index" = '0|tenant_id,published_by,publish_request_key|0'
broadcast_read_unique_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_read' AND index_name='uk_aihr_broadcast_read';")"
test "$broadcast_read_unique_index" = '0|tenant_id,message_id,user_id|0'
broadcast_version_unique_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_version' AND index_name='uk_aihr_broadcast_version';")"
test "$broadcast_version_unique_index" = '0|tenant_id,message_id,version|0'

mysql "$DB" <<'SQL'
INSERT INTO aihr_broadcast_message (
  tenant_id, title, content, status, published_by, published_time, create_time, update_time
) VALUES ('000000', 'legacy M0 targeting default', 'legacy content', 'PUBLISHED', 1, NOW(), NOW(), NOW());
SQL

broadcast_targeting_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260723_broadcast_targeting_mysql8.sql"
test -f "$broadcast_targeting_migration"
mysql "$DB" < "$broadcast_targeting_migration"
mysql "$DB" < "$broadcast_targeting_migration"
broadcast_targeting_column_contract="$(mysql "$DB" -N -B -e "SELECT CONCAT(column_name,'|',column_type,'|',is_nullable,'|',COALESCE(column_default,'<NULL>')) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND column_name IN ('required_read','target_payload_hash') ORDER BY FIELD(column_name,'required_read','target_payload_hash');")"
test "$broadcast_targeting_column_contract" = $'required_read|tinyint(1)|NO|0\ntarget_payload_hash|char(64)|YES|<NULL>'
broadcast_targeting_legacy_defaults="$(mysql "$DB" -N -B -e "SELECT CONCAT(required_read,'|',COALESCE(target_payload_hash,'NULL')) FROM aihr_broadcast_message WHERE title='legacy M0 targeting default';")"
test "$broadcast_targeting_legacy_defaults" = '0|NULL'
broadcast_targeting_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_broadcast_target_rule','aihr_broadcast_target_recipient');")"
test "$broadcast_targeting_tables" = 'aihr_broadcast_target_recipient,aihr_broadcast_target_rule'
broadcast_target_table_contract="$(mysql "$DB" -N -B -e "SELECT CONCAT(table_name,'.',column_name,'|',column_type,'|',is_nullable) FROM information_schema.columns WHERE table_schema='$DB' AND table_name IN ('aihr_broadcast_target_rule','aihr_broadcast_target_recipient') ORDER BY FIELD(table_name,'aihr_broadcast_target_rule','aihr_broadcast_target_recipient'), ordinal_position;")"
test "$broadcast_target_table_contract" = $'aihr_broadcast_target_rule.id|bigint|NO\naihr_broadcast_target_rule.tenant_id|varchar(20)|NO\naihr_broadcast_target_rule.message_id|bigint|NO\naihr_broadcast_target_rule.target_type|varchar(20)|NO\naihr_broadcast_target_rule.target_value|varchar(100)|NO\naihr_broadcast_target_rule.create_time|datetime|NO\naihr_broadcast_target_recipient.id|bigint|NO\naihr_broadcast_target_recipient.tenant_id|varchar(20)|NO\naihr_broadcast_target_recipient.message_id|bigint|NO\naihr_broadcast_target_recipient.recipient_key|varchar(180)|NO\naihr_broadcast_target_recipient.subject_ref|varchar(100)|NO\naihr_broadcast_target_recipient.user_id|bigint|YES\naihr_broadcast_target_recipient.target_status|varchar(20)|NO\naihr_broadcast_target_recipient.target_reason|varchar(100)|NO\naihr_broadcast_target_recipient.create_time|datetime|NO\naihr_broadcast_target_recipient.update_time|datetime|NO'
broadcast_target_rule_unique_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_target_rule' AND index_name='uk_aihr_broadcast_target_rule';")"
test "$broadcast_target_rule_unique_index" = '0|tenant_id,message_id,target_type,target_value|0'
broadcast_target_recipient_unique_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_target_recipient' AND index_name='uk_aihr_broadcast_target_recipient';")"
test "$broadcast_target_recipient_unique_index" = '0|tenant_id,message_id,recipient_key|0'

direct_feedback_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260724_direct_feedback_mysql8.sql"
test -f "$direct_feedback_migration"
mysql "$DB" <<'SQL'
CREATE TABLE sys_role (
  role_id bigint NOT NULL,
  tenant_id varchar(20) NOT NULL,
  role_name varchar(30) NOT NULL,
  role_key varchar(100) NOT NULL,
  role_sort int NOT NULL,
  data_scope char(1) NOT NULL,
  menu_check_strictly tinyint NOT NULL,
  dept_check_strictly tinyint NOT NULL,
  status char(1) NOT NULL,
  del_flag char(1) NOT NULL,
  create_by bigint DEFAULT NULL,
  create_time datetime DEFAULT NULL,
  remark varchar(500) DEFAULT NULL,
  PRIMARY KEY (role_id)
) ENGINE=InnoDB;
SQL
mysql "$DB" < "$direct_feedback_migration"
mysql "$DB" < "$direct_feedback_migration"
direct_feedback_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_direct_feedback' AND column_name IN ('tenant_id','channel_code','sender_user_id','anonymous_flag','content','status','submit_request_key','submit_request_hash','reply_content','replied_by','replied_time');")"
test "$direct_feedback_columns" = '11'
direct_feedback_submit_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_direct_feedback' AND index_name='uk_aihr_direct_feedback_submit';")"
test "$direct_feedback_submit_index" = '0|tenant_id,sender_user_id,submit_request_key|0'
direct_feedback_roles="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM sys_role WHERE tenant_id='000000' AND role_key IN ('direct_president','direct_finance','direct_hr','direct_audit','direct_operations') AND status='0' AND del_flag='0';")"
test "$direct_feedback_roles" = '5'

broadcast_attachment_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260724_broadcast_attachment_mysql8.sql"
multi_role_insight_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260725_broadcast_multi_role_insight_mysql8.sql"
broadcast_delivery_controls_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260802_broadcast_delivery_controls_mysql8.sql"
test -f "$broadcast_attachment_migration"
test -f "$multi_role_insight_migration"
test -f "$broadcast_delivery_controls_migration"
mysql "$DB" < "$broadcast_attachment_migration"
mysql "$DB" < "$broadcast_attachment_migration"
mysql "$DB" < "$multi_role_insight_migration"
mysql "$DB" < "$multi_role_insight_migration"
mysql "$DB" < "$broadcast_delivery_controls_migration"
mysql "$DB" < "$broadcast_delivery_controls_migration"
broadcast_attachment_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment' AND column_name IN ('tenant_id','message_id','oss_id','file_name','file_size','content_type','status','extracted_text','summary','error_message','uploaded_by');")"
test "$broadcast_attachment_columns" = '11'
broadcast_insight_columns="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment' AND column_name IN ('insight_status','insights_json','insight_version');")"
test "$broadcast_insight_columns" = '3'
broadcast_insight_queue_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment' AND index_name='idx_aihr_broadcast_attachment_insight_queue';")"
test "$broadcast_insight_queue_index" = 'insight_status,status,update_time'
broadcast_attachment_message_column="$(mysql "$DB" -N -B -e "SELECT CONCAT(column_type,'|',is_nullable) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND column_name='attachment_id';")"
test "$broadcast_attachment_message_column" = 'bigint|YES'
broadcast_attachment_message_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND index_name='idx_aihr_broadcast_message_attachment';")"
test "$broadcast_attachment_message_index" = 'tenant_id,attachment_id'
broadcast_attachment_bind_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment' AND index_name='uk_aihr_broadcast_attachment_message';")"
test "$broadcast_attachment_bind_index" = '0|tenant_id,message_id|0'
broadcast_delivery_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(CONCAT(column_name,'|',column_type,'|',is_nullable,'|',COALESCE(column_default,'<NULL>')) ORDER BY ordinal_position SEPARATOR ';') FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND column_name IN ('visibility_mode','allow_download');")"
test "$broadcast_delivery_columns" = 'visibility_mode|varchar(20)|NO|ALL;allow_download|tinyint(1)|NO|1'
broadcast_delivery_tables="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(table_name ORDER BY table_name) FROM information_schema.tables WHERE table_schema='$DB' AND table_name IN ('aihr_broadcast_attachment_access_audit','aihr_broadcast_attachment_topic_tag');")"
test "$broadcast_delivery_tables" = 'aihr_broadcast_attachment_access_audit,aihr_broadcast_attachment_topic_tag'
broadcast_topic_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment_topic_tag' AND index_name='uk_aihr_broadcast_attachment_topic_tag';")"
test "$broadcast_topic_unique" = '0|tenant_id,attachment_id,tag_name'
broadcast_access_user_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_attachment_access_audit' AND index_name='idx_aihr_broadcast_attachment_access_user';")"
test "$broadcast_access_user_index" = 'tenant_id,user_id,access_time'

mysql "$DB" -e "ALTER TABLE aihr_broadcast_message DROP INDEX uk_aihr_broadcast_message_publish_request, DROP COLUMN publish_request_hash, DROP COLUMN publish_request_key, DROP COLUMN withdraw_reason;"
mysql "$DB" <<'SQL'
INSERT INTO aihr_broadcast_message (
  tenant_id, title, content, status, published_by, published_time, create_time, update_time
) VALUES ('000000', 'legacy broadcast', 'legacy content', 'PUBLISHED', 1, NOW(), NOW(), NOW());
SQL
mysql "$DB" < "$broadcast_migration"
broadcast_legacy_snapshot_count="$(mysql "$DB" -N -B -e "SELECT COUNT(*) FROM aihr_broadcast_version v JOIN aihr_broadcast_message m ON m.tenant_id=v.tenant_id AND m.id=v.message_id WHERE m.title='legacy broadcast' AND v.\`version\`=1;")"
test "$broadcast_legacy_snapshot_count" = '1'
broadcast_legacy_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND column_name IN ('publish_request_key','publish_request_hash','withdraw_reason');")"
test "$broadcast_legacy_columns" = 'publish_request_key,publish_request_hash,withdraw_reason'

mysql "$DB" -e "ALTER TABLE aihr_broadcast_message DROP INDEX uk_aihr_broadcast_message_publish_request, ADD INDEX uk_aihr_broadcast_message_publish_request (tenant_id, publish_request_key(20), published_by);"
mysql "$DB" < "$broadcast_migration"
broadcast_repaired_publish_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND index_name='uk_aihr_broadcast_message_publish_request';")"
test "$broadcast_repaired_publish_index" = '0|tenant_id,published_by,publish_request_key|0'

mysql "$DB" <<'SQL'
ALTER TABLE aihr_broadcast_message
  DROP INDEX uk_aihr_broadcast_message_publish_request,
  ADD INDEX uk_aihr_broadcast_message_publish_request (tenant_id, publish_request_key(20), published_by);
INSERT INTO aihr_broadcast_message (
  tenant_id, title, content, status, published_by, published_time,
  publish_request_key, publish_request_hash, create_time, update_time
) VALUES
  ('000000', 'duplicate publish guard 1', 'content', 'PUBLISHED', 1, NOW(),
   'duplicate-publish-migration-guard', REPEAT('a', 64), NOW(), NOW()),
  ('000000', 'duplicate publish guard 2', 'content', 'PUBLISHED', 1, NOW(),
   'duplicate-publish-migration-guard', REPEAT('b', 64), NOW(), NOW());
SQL
if mysql "$DB" < "$broadcast_migration" >/dev/null 2>&1; then
  echo "FAIL: broadcast migration must reject duplicate publish idempotency keys" >&2
  exit 1
fi
broadcast_failed_publish_index="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index),'|',SUM(sub_part IS NOT NULL)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_broadcast_message' AND index_name='uk_aihr_broadcast_message_publish_request';")"
test "$broadcast_failed_publish_index" = '1|tenant_id,publish_request_key,published_by|1'

agent_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260724_agent_orchestration_mysql8.sql"
test -f "$agent_migration"
mysql "$DB" < "$agent_migration"
mysql "$DB" < "$agent_migration"
agent_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_agent_run';")"
test "$agent_columns" = 'id,run_id,tenant_id,client_key,user_id,conversation_id,context_version,intent,tool,status,source_type,duration_ms,error_code,result_ref,create_time'
agent_run_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_agent_run' AND index_name='uk_aihr_agent_run';")"
test "$agent_run_unique" = '0|run_id'
agent_user_index="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY seq_in_index) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_agent_run' AND index_name='idx_aihr_agent_run_user';")"
test "$agent_user_index" = 'tenant_id,user_id,create_time'

rag_candidate_trace_migration="$ROOT_DIR/backend/script/sql/update/aihr_20260815_rag_candidate_trace_mysql8.sql"
test -f "$rag_candidate_trace_migration"
mysql "$DB" < "$rag_candidate_trace_migration"
mysql "$DB" < "$rag_candidate_trace_migration"
rag_candidate_trace_columns="$(mysql "$DB" -N -B -e "SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema='$DB' AND table_name='aihr_query_candidate_trace';")"
test "$rag_candidate_trace_columns" = 'id,tenant_id,request_id,fragment_id,candidate_rank,channel,channel_rank,raw_score,fusion_score,rerank_score,rerank_model,rerank_applied,source_authority,source_kind,query_variant,selected_for_evidence,decision_reason,create_time'
rag_candidate_trace_unique="$(mysql "$DB" -N -B -e "SELECT CONCAT(MIN(non_unique),'|',GROUP_CONCAT(column_name ORDER BY seq_in_index)) FROM information_schema.statistics WHERE table_schema='$DB' AND table_name='aihr_query_candidate_trace' AND index_name='uk_aihr_query_candidate_channel';")"
test "$rag_candidate_trace_unique" = '0|tenant_id,request_id,fragment_id,channel,query_variant'

echo "PASS: AIHR legacy schema migrations are idempotent, including Agent run audit, practice growth curriculum snapshots, knowledge-space categories, practice evidence, knowledge feedback, candidate interview review, position/SOP qualification contracts, assistant capture, work-report idempotency, direct feedback, broadcast publish/withdraw audit with targeting, delivery controls, topic evidence and file access audit, v1 snapshots, and trusted broadcast question context"