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

# Credential-free, idempotent migration for the GSTYJ demo business dataset.
# Run from /workspace/wwwroot/tnxlgstyj/project on the application server.
# Usage: bash gstyj_demo_business_migration.sh dry-run|apply|verify|rollback

MODE="${1:-dry-run}"
CONFIG_PATH="application/config/prod/tnxlgstyj/database.php"
MARKER="DEMO_BIZ_20260826"
CREATOR="演示数据迁移"
TEAM_UUID="AC7167F2-C9D7-83AB-CFB3-16F6D86B28A5"
ADMIN_UUID="AFB65B8D-E006-63CC-C111-2744E7066795"

case "$MODE" in
  dry-run|apply|verify|rollback) ;;
  *) echo "Usage: $0 dry-run|apply|verify|rollback" >&2; exit 2 ;;
esac

[[ -r "$CONFIG_PATH" ]] || { echo "Database config is not readable: $CONFIG_PATH" >&2; exit 3; }

# Read credentials only into process memory. They are never printed or stored in this script.
eval "$(php -r '$c=include $argv[1]; foreach(["hostname"=>"MIG_DB_HOST","username"=>"MIG_DB_USER","password"=>"MIG_DB_PASS","database"=>"MIG_DB_NAME"] as $k=>$v){echo "export $v=".escapeshellarg($c[$k]).";";}' "$CONFIG_PATH")"

mysql_cmd=(mysql --batch --raw --skip-column-names -h"$MIG_DB_HOST" -u"$MIG_DB_USER" -p"$MIG_DB_PASS" "$MIG_DB_NAME")
mysql_table=(mysql --table -h"$MIG_DB_HOST" -u"$MIG_DB_USER" -p"$MIG_DB_PASS" "$MIG_DB_NAME")

read -r -d '' STAFF_UUIDS <<'EOF' || true
943FCF85-8B13-1E3A-443C-D56FFD63AD1D
E62C7DD2-6260-20E3-9918-EE199B64DE5F
E0C06C1A-C814-B1A9-7A25-EF86219D6660
C08A1F0F-0A6B-88AC-572B-DCD0A6B1FBCC
C74460CC-DE4A-BACE-EAC8-8CCB081CB568
8A1B3CCB-E791-4518-BA81-E79B3D3E58C5
DE0A7135-83EE-437B-55D1-BBC19CBB3563
26758C6A-B4CE-1AE0-EE11-416C60A608E0
8D9C4537-9148-7FDE-D999-81A3210DC49F
5D5CC0D9-A1B7-9436-893A-E900B62F6339
EOF
mapfile -t STAFF < <(printf '%s\n' "$STAFF_UUIDS")
STAFF_CSV=$(IFS=,; echo "${STAFF[*]}")
STAFF_SQL=$(printf "'%s'," "${STAFF[@]}")
STAFF_SQL="${STAFF_SQL%,}"

# Existing target setting UUIDs. These are references only; the script never changes settings.
NORM_SPRINT="66CF5CE3-3030-F8E3-5937-5B25D453248B"
NORM_CMJ="7B46D0A2-4D4F-E33C-0D3D-22C78A0F68E7"
NORM_SQUAT="24A0D735-C80A-1771-369D-9F632D4AB7F3"
NORM_VO2="53ACA78F-9720-5260-8EE9-3ED9EE53707C"
CATE_SPEED="EE81D79A-3D60-D03A-D4E7-31A7A1AC1C9E"
CATE_JUMP="A82743D6-FF1A-ACBE-BC84-6760D20A2F5B"
CATE_STRENGTH="B6312B79-3BF5-5964-F148-0350FB281CF6"
CATE_ENDURANCE="F446AB16-5508-4DE7-07E8-414A9EC5E3D9"
INJURY_FATIGUE="CD245EB5-AC0B-E096-DFEC-5228199F93D1"
INJURY_ACUTE="A267FC54-A3FA-8F8E-A890-BFE1DB596A45"
INJURY_WAIST="5E428C58-D09B-4D1F-DFE5-DC2B7391DE80"
INJURY_SHOULDER="B7968407-0BE1-697E-0DEE-BD4C5B34D5F9"
INJURY_KNEE="A10B43D9-DB30-7AAB-8559-D6651E6AEB75"
INJURY_ANKLE="53B0B817-3634-485A-40F0-35E7A2029F90"
INJURY_NORMAL="CC4EFE2C-71D1-F030-419D-648A385D458B"
INJURY_SEVERE="9FFB0D6D-762A-0CAC-E0E1-0FE1038CF5E3"
ACTION_BIKE="46C9B3A5-B658-A733-DBF8-2AD49BEB331E"
ACTION_INTERVAL_RUN="BD8A9E0B-071F-5A4F-9F21-8FFA98802300"
ACTION_CORE="AD5C9927-BB1A-223D-5934-FEB095057FAA"

preflight_sql=$(cat <<SQL
SELECT 'team' item, COUNT(*) actual, 1 expected FROM tn_department WHERE uuid='$TEAM_UUID' AND name='演示运动队' AND del_flag=0
UNION ALL SELECT 'athletes', COUNT(*), 10 FROM tn_staff s JOIN tn_staff_department sd ON sd.staff_uuid=s.uuid WHERE sd.department_uuid='$TEAM_UUID' AND s.uuid IN ($STAFF_SQL) AND s.del_flag=0 AND s.is_athlete=1
UNION ALL SELECT 'norms', COUNT(*), 4 FROM tn_norm WHERE uuid IN ('$NORM_SPRINT','$NORM_CMJ','$NORM_SQUAT','$NORM_VO2') AND status=1 AND del_flag=0
UNION ALL SELECT 'injury_settings', COUNT(*), 8 FROM tn_injury_set WHERE uuid IN ('$INJURY_FATIGUE','$INJURY_ACUTE','$INJURY_WAIST','$INJURY_SHOULDER','$INJURY_KNEE','$INJURY_ANKLE','$INJURY_NORMAL','$INJURY_SEVERE') AND status=1 AND del_flag=0
UNION ALL SELECT 'training_actions', COUNT(*), 3 FROM tn_action WHERE uuid IN ('$ACTION_BIKE','$ACTION_INTERVAL_RUN','$ACTION_CORE') AND status=1 AND del_flag=0
UNION ALL SELECT 'injury_staff_fk', COUNT(*), 1 FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tn_injury' AND COLUMN_NAME='staff_uuid' AND REFERENCED_TABLE_NAME='tn_staff' AND REFERENCED_COLUMN_NAME='uuid';
SQL
)

echo "Preflight mapping checks"
"${mysql_table[@]}" -e "$preflight_sql" 2>/dev/null
bad=$("${mysql_cmd[@]}" -e "SELECT (SELECT COUNT(*) FROM tn_department WHERE uuid='$TEAM_UUID' AND name='演示运动队' AND del_flag=0)<>1 OR (SELECT COUNT(*) FROM tn_staff s JOIN tn_staff_department sd ON sd.staff_uuid=s.uuid WHERE sd.department_uuid='$TEAM_UUID' AND s.uuid IN ($STAFF_SQL) AND s.del_flag=0 AND s.is_athlete=1)<>10 OR (SELECT COUNT(*) FROM tn_norm WHERE uuid IN ('$NORM_SPRINT','$NORM_CMJ','$NORM_SQUAT','$NORM_VO2') AND status=1 AND del_flag=0)<>4 OR (SELECT COUNT(*) FROM tn_injury_set WHERE uuid IN ('$INJURY_FATIGUE','$INJURY_ACUTE','$INJURY_WAIST','$INJURY_SHOULDER','$INJURY_KNEE','$INJURY_ANKLE','$INJURY_NORMAL','$INJURY_SEVERE') AND status=1 AND del_flag=0)<>8 OR (SELECT COUNT(*) FROM tn_action WHERE uuid IN ('$ACTION_BIKE','$ACTION_INTERVAL_RUN','$ACTION_CORE') AND status=1 AND del_flag=0)<>3 OR (SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tn_injury' AND COLUMN_NAME='staff_uuid' AND REFERENCED_TABLE_NAME='tn_staff' AND REFERENCED_COLUMN_NAME='uuid')<>1" 2>/dev/null)
[[ "$bad" == "0" ]] || { echo "Preflight failed; no writes performed." >&2; exit 4; }

show_plan() {
  cat <<'EOF'
Planned additive rows (2026-05-01 through 2026-08-31):
  status_record: 1,800 (10 athletes × 18 dates × 10 indicators)
  pain_appraise: 60 (10 athletes × 6 dates)
  test_result: 4; test_value/result links: 160; manage_staff: 40
  legacy test/staff_test compatibility: 4 / 160
  current weekly training chain: 187 summaries; 51 team days + 510 athlete days,
    with matching detail/action rows; default week 2026-08-24 has Mon/Wed/Fri data
  legacy training_plan/detail/outline compatibility: 8 / 8 / 16
  injury: 14; staff.disease_times is recomputed for the 10 mapped athletes
  talent/selection rows: 0 (owned by the separate selection-migration task)
  setting or equipment changes: 0 (manual tests reuse existing norm/category UUIDs)
EOF
}

verify() {
  "${mysql_table[@]}" 2>/dev/null <<SQL
SELECT 'status_record' module,COUNT(*) rows_count,COUNT(DISTINCT staff_uuid) athletes,MIN(date) min_date,MAX(date) max_date FROM tn_status_record WHERE create_by='$CREATOR'
UNION ALL SELECT 'pain_appraise',COUNT(*),COUNT(DISTINCT staff_uuid),MIN(date),MAX(date) FROM tn_pain_appraise WHERE create_by='$CREATOR'
UNION ALL SELECT 'test_result',COUNT(*),0,MIN(test_date),MAX(test_date) FROM tn_test_result WHERE title LIKE '[$MARKER]%'
UNION ALL SELECT 'test_value',COUNT(*),COUNT(DISTINCT staff_uuid),MIN(test_date),MAX(test_date) FROM tn_test_value WHERE create_by='$CREATOR' AND is_del=0
UNION ALL SELECT 'legacy_test',COUNT(*),0,MIN(test_date),MAX(test_date) FROM tn_test WHERE title LIKE '[$MARKER]%'
UNION ALL SELECT 'current_team_train',COUNT(*),0,MIN(train_date),MAX(train_date) FROM tn_staff_train WHERE create_by='$CREATOR' AND type=1
UNION ALL SELECT 'current_athlete_train',COUNT(*),COUNT(DISTINCT organize_uuid),MIN(train_date),MAX(train_date) FROM tn_staff_train WHERE create_by='$CREATOR' AND type=2
UNION ALL SELECT 'training_plan',COUNT(*),0,MIN(date),MAX(date) FROM tn_training_plan WHERE user_name='[$MARKER]' AND is_del=0
UNION ALL SELECT 'injury',COUNT(*),COUNT(DISTINCT staff_uuid),MIN(start_date),MAX(start_date) FROM tn_injury WHERE demo='$MARKER';

SELECT 'orphan_test_result_value' check_name,COUNT(*) orphan_count FROM tn_test_result_value rv LEFT JOIN tn_test_result r ON r.id=rv.result_id LEFT JOIN tn_test_value v ON v.id=rv.value_id WHERE rv.create_by='$CREATOR' AND (r.id IS NULL OR v.id IS NULL)
UNION ALL SELECT 'orphan_manage_staff',COUNT(*) FROM tn_manage_staff m LEFT JOIN tn_test_result r ON r.id=m.outer_uuid WHERE m.type=10 AND r.title LIKE '[$MARKER]%' AND r.id IS NULL
UNION ALL SELECT 'orphan_legacy_staff_test',COUNT(*) FROM tn_staff_test st LEFT JOIN tn_test t ON t.uuid=st.test_uuid LEFT JOIN tn_staff s ON s.uuid=st.staff_uuid LEFT JOIN tn_norm n ON n.uuid=st.norm_uuid WHERE st.create_by='$CREATOR' AND (t.uuid IS NULL OR s.uuid IS NULL OR n.uuid IS NULL)
UNION ALL SELECT 'orphan_current_train_detail',COUNT(*) FROM tn_staff_train_detail d LEFT JOIN tn_staff_train st ON st.organize_uuid=d.organize_uuid AND st.type=d.type AND st.train_date=d.train_date WHERE d.create_by='$CREATOR' AND st.uuid IS NULL
UNION ALL SELECT 'orphan_current_train_action',COUNT(*) FROM tn_staff_train_detail_ext e LEFT JOIN tn_staff_train_detail d ON d.uuid=e.staff_train_detail_uuid LEFT JOIN tn_action a ON a.uuid=e.action_uuid WHERE e.create_by='$CREATOR' AND (d.uuid IS NULL OR a.uuid IS NULL)
UNION ALL SELECT 'orphan_training_detail',COUNT(*) FROM tn_training_plan_detail d LEFT JOIN tn_training_plan p ON p.id=d.training_plan_id WHERE d.coach_name='演示教练' AND p.id IS NULL
UNION ALL SELECT 'orphan_training_outline',COUNT(*) FROM tn_training_plan_outline o LEFT JOIN tn_training_plan p ON p.id=o.training_plan_id WHERE o.require LIKE '%演示%' AND p.id IS NULL
UNION ALL SELECT 'orphan_injury_staff_or_setting',COUNT(*) FROM tn_injury i LEFT JOIN tn_staff s ON s.uuid=i.staff_uuid LEFT JOIN tn_injury_set p ON p.uuid=i.injury_position_uuid LEFT JOIN tn_injury_set c ON c.uuid=i.injury_cate_uuid LEFT JOIN tn_injury_set d ON d.uuid=i.injury_degree_uuid WHERE i.demo='$MARKER' AND (s.uuid IS NULL OR p.uuid IS NULL OR c.uuid IS NULL OR d.uuid IS NULL);

SELECT DATE_FORMAT(date,'%Y-%m') month,COUNT(*) status_rows,COUNT(DISTINCT staff_uuid) athletes FROM tn_status_record WHERE create_by='$CREATOR' GROUP BY DATE_FORMAT(date,'%Y-%m') ORDER BY month;
SELECT DATE_FORMAT(test_date,'%Y-%m') month,COUNT(*) values_count,COUNT(DISTINCT staff_uuid) athletes,COUNT(DISTINCT norm_uuid) norms FROM tn_test_value WHERE create_by='$CREATOR' AND is_del=0 GROUP BY DATE_FORMAT(test_date,'%Y-%m') ORDER BY month;
SELECT DATE_FORMAT(train_date,'%Y-%m') month,COUNT(*) athlete_day_rows,COUNT(DISTINCT organize_uuid) athletes,ROUND(AVG(real_timelen*rpe),1) avg_load FROM tn_staff_train WHERE create_by='$CREATOR' AND type=2 GROUP BY DATE_FORMAT(train_date,'%Y-%m') ORDER BY month;
SELECT DATE_FORMAT(start_date,'%Y-%m') month,COUNT(*) injuries,COUNT(DISTINCT staff_uuid) athletes FROM tn_injury WHERE demo='$MARKER' GROUP BY DATE_FORMAT(start_date,'%Y-%m') ORDER BY month;
SQL
}

if [[ "$MODE" == "dry-run" ]]; then
  show_plan
  echo "Existing in-scope data (read-only)"
  "${mysql_table[@]}" 2>/dev/null <<SQL
SELECT 'status_existing' item,COUNT(*) n,MIN(date) min_date,MAX(date) max_date FROM tn_status_record WHERE staff_uuid IN ($STAFF_SQL)
UNION ALL SELECT 'test_result_existing',COUNT(*),MIN(test_date),MAX(test_date) FROM tn_test_result WHERE department_uuid='$TEAM_UUID'
UNION ALL SELECT 'training_plan_existing',COUNT(*),NULL,NULL FROM tn_training_plan WHERE staff_uuids REGEXP '(${STAFF_CSV//,/|})'
UNION ALL SELECT 'injury_existing',COUNT(*),MIN(start_date),MAX(start_date) FROM tn_injury WHERE staff_uuid IN ($STAFF_SQL);
SQL
  exit 0
fi

if [[ "$MODE" == "verify" ]]; then
  verify
  exit 0
fi

if [[ "$MODE" == "rollback" ]]; then
  "${mysql_cmd[@]}" 2>/dev/null <<SQL
START TRANSACTION;
DELETE rv FROM tn_test_result_value rv JOIN tn_test_result r ON r.id=rv.result_id WHERE r.title LIKE '[$MARKER]%';
DELETE m FROM tn_manage_staff m JOIN tn_test_result r ON r.id=m.outer_uuid WHERE m.type=10 AND r.title LIKE '[$MARKER]%';
DELETE FROM tn_test_value WHERE create_by='$CREATOR';
DELETE FROM tn_test_result WHERE title LIKE '[$MARKER]%';
DELETE st FROM tn_staff_test st JOIN tn_test t ON t.uuid=st.test_uuid WHERE t.title LIKE '[$MARKER]%';
DELETE FROM tn_test WHERE title LIKE '[$MARKER]%';
DELETE e FROM tn_staff_train_detail_ext e JOIN tn_staff_train_detail d ON d.uuid=e.staff_train_detail_uuid WHERE d.create_by='$CREATOR';
DELETE FROM tn_staff_train_detail WHERE create_by='$CREATOR';
DELETE FROM tn_staff_train WHERE create_by='$CREATOR';
DELETE FROM tn_train WHERE create_by='$CREATOR';
DELETE d FROM tn_training_plan_detail d JOIN tn_training_plan p ON p.id=d.training_plan_id WHERE p.user_name='[$MARKER]';
DELETE o FROM tn_training_plan_outline o JOIN tn_training_plan p ON p.id=o.training_plan_id WHERE p.user_name='[$MARKER]';
DELETE FROM tn_training_plan WHERE user_name='[$MARKER]';
DELETE FROM tn_injury WHERE demo='$MARKER';
DELETE FROM tn_pain_appraise WHERE create_by='$CREATOR';
DELETE FROM tn_status_record WHERE create_by='$CREATOR';
UPDATE tn_staff s SET disease_times=(SELECT COUNT(*) FROM tn_injury i WHERE i.staff_uuid=s.uuid) WHERE s.uuid IN ($STAFF_SQL);
COMMIT;
SQL
  verify
  exit 0
fi

show_plan
tmp_sql=$(mktemp /tmp/gstyj-demo-business.XXXXXX.sql)
trap 'rm -f "$tmp_sql"' EXIT

cat >"$tmp_sql" <<SQL
SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_staff LIKE tn_staff;
INSERT IGNORE INTO migbak_demo_business_20260826_staff SELECT * FROM tn_staff WHERE uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_status_record LIKE tn_status_record;
INSERT IGNORE INTO migbak_demo_business_20260826_status_record SELECT * FROM tn_status_record WHERE staff_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_pain_appraise LIKE tn_pain_appraise;
INSERT IGNORE INTO migbak_demo_business_20260826_pain_appraise SELECT * FROM tn_pain_appraise WHERE staff_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_test LIKE tn_test;
INSERT IGNORE INTO migbak_demo_business_20260826_test SELECT * FROM tn_test WHERE department_uuid='$TEAM_UUID';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_staff_test LIKE tn_staff_test;
INSERT IGNORE INTO migbak_demo_business_20260826_staff_test SELECT st.* FROM tn_staff_test st JOIN tn_test t ON t.uuid=st.test_uuid WHERE t.department_uuid='$TEAM_UUID';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_test_result LIKE tn_test_result;
INSERT IGNORE INTO migbak_demo_business_20260826_test_result SELECT * FROM tn_test_result WHERE department_uuid='$TEAM_UUID';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_test_value LIKE tn_test_value;
INSERT IGNORE INTO migbak_demo_business_20260826_test_value SELECT * FROM tn_test_value WHERE staff_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_test_result_value LIKE tn_test_result_value;
INSERT IGNORE INTO migbak_demo_business_20260826_test_result_value SELECT rv.* FROM tn_test_result_value rv JOIN tn_test_result r ON r.id=rv.result_id WHERE r.department_uuid='$TEAM_UUID';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_manage_staff LIKE tn_manage_staff;
INSERT IGNORE INTO migbak_demo_business_20260826_manage_staff SELECT * FROM tn_manage_staff WHERE department_uuid='$TEAM_UUID' AND staff_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_training_plan LIKE tn_training_plan;
INSERT IGNORE INTO migbak_demo_business_20260826_training_plan SELECT * FROM tn_training_plan WHERE staff_uuids REGEXP '(${STAFF_CSV//,/|})';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_training_plan_detail LIKE tn_training_plan_detail;
INSERT IGNORE INTO migbak_demo_business_20260826_training_plan_detail SELECT d.* FROM tn_training_plan_detail d JOIN tn_training_plan p ON p.id=d.training_plan_id WHERE p.staff_uuids REGEXP '(${STAFF_CSV//,/|})';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_training_plan_outline LIKE tn_training_plan_outline;
INSERT IGNORE INTO migbak_demo_business_20260826_training_plan_outline SELECT o.* FROM tn_training_plan_outline o JOIN tn_training_plan p ON p.id=o.training_plan_id WHERE p.staff_uuids REGEXP '(${STAFF_CSV//,/|})';
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_train LIKE tn_train;
INSERT IGNORE INTO migbak_demo_business_20260826_train SELECT * FROM tn_train WHERE organize_uuid='$TEAM_UUID' OR organize_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_staff_train LIKE tn_staff_train;
INSERT IGNORE INTO migbak_demo_business_20260826_staff_train SELECT * FROM tn_staff_train WHERE organize_uuid='$TEAM_UUID' OR organize_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_staff_train_detail LIKE tn_staff_train_detail;
INSERT IGNORE INTO migbak_demo_business_20260826_staff_train_detail SELECT * FROM tn_staff_train_detail WHERE organize_uuid='$TEAM_UUID' OR organize_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_staff_train_detail_ext LIKE tn_staff_train_detail_ext;
INSERT IGNORE INTO migbak_demo_business_20260826_staff_train_detail_ext SELECT e.* FROM tn_staff_train_detail_ext e JOIN tn_staff_train_detail d ON d.uuid=e.staff_train_detail_uuid WHERE d.organize_uuid='$TEAM_UUID' OR d.organize_uuid IN ($STAFF_SQL);
CREATE TABLE IF NOT EXISTS migbak_demo_business_20260826_injury LIKE tn_injury;
INSERT IGNORE INTO migbak_demo_business_20260826_injury SELECT * FROM tn_injury WHERE staff_uuid IN ($STAFF_SQL);

SQL

dates=(2026-05-04 2026-05-11 2026-05-18 2026-05-25 2026-06-01 2026-06-08 2026-06-15 2026-06-22 2026-06-29 2026-07-06 2026-07-13 2026-07-20 2026-07-27 2026-08-03 2026-08-10 2026-08-17 2026-08-24 2026-08-31)
for si in "${!STAFF[@]}"; do
  su="${STAFF[$si]}"
  for di in "${!dates[@]}"; do
    dt="${dates[$di]}"
    pulse=$((54 + (si % 5) + (di % 4)))
    sleep=$(awk -v s="$si" -v d="$di" 'BEGIN{printf "%.1f",7.1+((s+d)%5)*0.2}')
    jump=$(awk -v s="$si" -v d="$di" 'BEGIN{printf "%.1f",38+s*0.7+d*0.18}')
    cmj=$(awk -v s="$si" -v d="$di" 'BEGIN{printf "%.1f",40+s*0.7+d*0.2}')
    grip=$(awk -v s="$si" -v d="$di" 'BEGIN{printf "%.1f",38+s*1.2+d*0.15}')
    cadence=$((46 + si + (di % 6)))
    sleepq=$((7 + ((si+di)%3)))
    appetite=$((7 + ((si+di+1)%3)))
    self=$((7 + ((si+di+2)%3)))
    sore=$((2 + ((si+di)%4)))
    for pair in "1:$pulse" "3:$sleep" "4:$jump" "5:$cmj" "6:$grip" "7:$cadence" "8:$sleepq" "9:$appetite" "10:$self" "11:$sore"; do
      typ="${pair%%:*}"; val="${pair#*:}"
      printf "INSERT IGNORE INTO tn_status_record(staff_uuid,type,value,date,create_time,create_by) VALUES('%s',%s,'%s','%s','2026-08-26 14:30:00','%s');\n" "$su" "$typ" "$val" "$dt" "$CREATOR" >>"$tmp_sql"
    done
  done
done

pain_dates=(2026-05-11 2026-06-08 2026-06-29 2026-07-20 2026-08-10 2026-08-24)
for si in "${!STAFF[@]}"; do
  for di in "${!pain_dates[@]}"; do
    pos=$((1 + ((si+di)%8))); val=$((1 + ((si+di)%4)))
    printf "INSERT INTO tn_pain_appraise(staff_uuid,position,value,date,create_time,create_by) SELECT '%s',%s,%s,'%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_pain_appraise WHERE staff_uuid='%s' AND position=%s AND date='%s' AND create_by='%s');\n" "${STAFF[$si]}" "$pos" "$val" "${pain_dates[$di]}" "$CREATOR" "${STAFF[$si]}" "$pos" "${pain_dates[$di]}" "$CREATOR" >>"$tmp_sql"
  done
done

test_dates=(2026-05-15 2026-06-18 2026-07-16 2026-08-13)
test_uuids=(D2605150-0000-4000-8000-000000000001 D2606180-0000-4000-8000-000000000002 D2607160-0000-4000-8000-000000000003 D2608130-0000-4000-8000-000000000004)
norms=("$NORM_SPRINT" "$NORM_CMJ" "$NORM_SQUAT" "$NORM_VO2")
cates=("$CATE_SPEED" "$CATE_JUMP" "$CATE_STRENGTH" "$CATE_ENDURANCE")
norm_struct='[{"name":"速度","norm":["10米冲刺"]},{"name":"爆发力","norm":["CMJ纵跳"]},{"name":"力量","norm":["深蹲"]},{"name":"耐力","norm":["最大摄氧量"]}]'
for ti in "${!test_dates[@]}"; do
  dt="${test_dates[$ti]}"; tu="${test_uuids[$ti]}"; title="[$MARKER] 演示运动队第$((ti+1))阶段综合测试"
  printf "INSERT INTO tn_test(uuid,title,test_date,department_uuid,staff_uuid_str,staff_quantity,norm_quantity,annex_quantity,coach,address_uuid,type,demo,summary,cate_norm,create_time,create_by,sync_id,sync_type) SELECT '%s','%s','%s','%s','%s',10,4,0,'演示教练','',7,'阶段测试演示数据','四项指标完成率100%%','', '2026-08-26 14:30:00','%s',NULL,NULL FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_test WHERE uuid='%s');\n" "$tu" "$title" "$dt" "$TEAM_UUID" "$STAFF_CSV" "$CREATOR" "$tu" >>"$tmp_sql"
  printf "INSERT INTO tn_test_result(type,title,department_uuid,staff_uuid_str,norm_uuid_str,test_date,norm_struct,test_goal,address_uuid,evaluate,department_center_name,person_num_yingce,person_num_shice,person_num_qingjia,person_num_quance,create_time,create_by) SELECT 1,'%s','%s','%s','%s,%s,%s,%s','%s','%s','阶段性评估速度、爆发力、力量与耐力','', '整体呈稳定提升趋势','演示运动队','10','10','0','10','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_test_result WHERE title='%s' AND test_date='%s' AND department_uuid='%s');\n" "$title" "$TEAM_UUID" "$STAFF_CSV" "$NORM_SPRINT" "$NORM_CMJ" "$NORM_SQUAT" "$NORM_VO2" "$dt" "$norm_struct" "$CREATOR" "$title" "$dt" "$TEAM_UUID" >>"$tmp_sql"
  for si in "${!STAFF[@]}"; do
    su="${STAFF[$si]}"
    sprint=$(awk -v s="$si" -v t="$ti" 'BEGIN{printf "%.2f",1.92+s*0.025-t*0.035}')
    cmj=$(awk -v s="$si" -v t="$ti" 'BEGIN{printf "%.1f",39+s*0.8+t*1.1}')
    squat=$(awk -v s="$si" -v t="$ti" 'BEGIN{printf "%.1f",85+s*3.8+t*4.5}')
    vo2=$(awk -v s="$si" -v t="$ti" 'BEGIN{printf "%.1f",47+s*0.65+t*1.0}')
    vals=("$sprint" "$cmj" "$squat" "$vo2")
    for ni in 0 1 2 3; do
      printf "INSERT INTO tn_staff_test(test_uuid,staff_uuid,cate_uuid,norm_uuid,norm_value,json_value,value_type,create_time,create_by) SELECT '%s','%s','%s','%s','%s',NULL,0,'2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_test WHERE test_uuid='%s' AND staff_uuid='%s' AND norm_uuid='%s');\n" "$tu" "$su" "${cates[$ni]}" "${norms[$ni]}" "${vals[$ni]}" "$CREATOR" "$tu" "$su" "${norms[$ni]}" >>"$tmp_sql"
      printf "INSERT INTO tn_test_value(staff_uuid,norm_uuid,norm_value,test_date,note,is_del,status,create_time,create_by) SELECT '%s','%s','%s','%s','%s',0,1,'2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_test_value WHERE staff_uuid='%s' AND norm_uuid='%s' AND test_date='%s' AND is_del=0);\n" "$su" "${norms[$ni]}" "${vals[$ni]}" "$dt" "$MARKER" "$CREATOR" "$su" "${norms[$ni]}" "$dt" >>"$tmp_sql"
      printf "INSERT INTO tn_test_result_value(result_id,value_id,is_del,create_time,create_by) SELECT r.id,v.id,0,'2026-08-26 14:30:00','%s' FROM tn_test_result r JOIN tn_test_value v ON v.staff_uuid='%s' AND v.norm_uuid='%s' AND v.test_date='%s' AND v.is_del=0 WHERE r.title='%s' AND r.test_date='%s' AND NOT EXISTS(SELECT 1 FROM tn_test_result_value x WHERE x.result_id=r.id AND x.value_id=v.id AND x.is_del=0);\n" "$CREATOR" "$su" "${norms[$ni]}" "$dt" "$title" "$dt" >>"$tmp_sql"
    done
    printf "INSERT INTO tn_manage_staff(outer_uuid,type,department_uuid,staff_uuid) SELECT r.id,10,'%s','%s' FROM tn_test_result r WHERE r.title='%s' AND r.test_date='%s' AND NOT EXISTS(SELECT 1 FROM tn_manage_staff m WHERE m.outer_uuid=r.id AND m.type=10 AND m.staff_uuid='%s');\n" "$TEAM_UUID" "$su" "$title" "$dt" "$su" >>"$tmp_sql"
  done
done

# Current training UI chain (/trainingPlan/weekly/teamPage): one team summary and
# ten athlete summaries per week, plus Mon/Wed/Fri team and individual sessions.
week_starts=(2026-05-04 2026-05-11 2026-05-18 2026-05-25 2026-06-01 2026-06-08 2026-06-15 2026-06-22 2026-06-29 2026-07-06 2026-07-13 2026-07-20 2026-07-27 2026-08-03 2026-08-10 2026-08-17 2026-08-24)
for wi in "${!week_starts[@]}"; do
  ws="${week_starts[$wi]}"
  we=$(date -d "$ws +6 days" +%F)
  team_summary_uuid=$(printf "D26W%04d-0000-4000-8000-%012d" "$wi" "$((wi+1))")
  printf "INSERT INTO tn_train(uuid,organize_uuid,staff_uuids,type,summary,annex_quantity,start_date,end_date,create_time,create_by) SELECT '%s','%s','%s',1,'第%d周：体能基础、专项能力与恢复统筹安排',0,'%s','%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_train WHERE organize_uuid='%s' AND type=1 AND start_date='%s' AND end_date='%s');\n" "$team_summary_uuid" "$TEAM_UUID" "$STAFF_CSV" "$((wi+1))" "$ws" "$we" "$CREATOR" "$TEAM_UUID" "$ws" "$we" >>"$tmp_sql"
  for si in "${!STAFF[@]}"; do
    psuuid=$(printf "D26P%02d%02d-0000-4000-8000-%012d" "$wi" "$si" "$((wi*10+si+1))")
    printf "INSERT INTO tn_train(uuid,organize_uuid,staff_uuids,type,summary,annex_quantity,start_date,end_date,create_time,create_by) SELECT '%s','%s','%s',2,'第%d周个人训练：执行团队计划并按RPE调整',0,'%s','%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_train WHERE organize_uuid='%s' AND type=2 AND start_date='%s' AND end_date='%s');\n" "$psuuid" "${STAFF[$si]}" "${STAFF[$si]}" "$((wi+1))" "$ws" "$we" "$CREATOR" "${STAFF[$si]}" "$ws" "$we" >>"$tmp_sql"
  done
  for offset in 0 2 4; do
    dt=$(date -d "$ws +$offset days" +%F)
    if (( offset == 0 )); then target="基础力量与动作质量"; action="$ACTION_CORE"; template="力量基础课"; start="09:00"; end="10:30"; mins=90
    elif (( offset == 2 )); then target="专项耐力与配速"; action="$ACTION_INTERVAL_RUN"; template="专项耐力课"; start="15:00"; end="16:40"; mins=100
    else target="有氧恢复与灵活性"; action="$ACTION_BIKE"; template="恢复训练课"; start="09:30"; end="10:40"; mins=70; fi
    team_train_uuid=$(printf "D26T%02d%d0-0000-4000-8000-%012d" "$wi" "$offset" "$((wi*10+offset+1))")
    team_detail_uuid=$(printf "D26D%02d%d0-0000-4000-8000-%012d" "$wi" "$offset" "$((wi*10+offset+1))")
    team_ext_uuid=$(printf "D26E%02d%d0-0000-4000-8000-%012d" "$wi" "$offset" "$((wi*10+offset+1))")
    rpe=$((6 + ((wi+offset)%3)))
    real=$((mins - 5 + ((wi+offset)%11)))
    printf "INSERT INTO tn_staff_train(uuid,organize_uuid,staff_uuids,type,target,train_date,plan_timelen,real_timelen,plan_rpe,rpe,create_time,create_by) SELECT '%s','%s','%s',1,'%s','%s','%s','%s','%s','%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train WHERE organize_uuid='%s' AND type=1 AND train_date='%s');\n" "$team_train_uuid" "$TEAM_UUID" "$STAFF_CSV" "$target" "$dt" "$mins" "$real" "$rpe" "$rpe" "$CREATOR" "$TEAM_UUID" "$dt" >>"$tmp_sql"
    printf "INSERT INTO tn_staff_train_detail(uuid,organize_uuid,staff_uuids,type,train_date,template_uuid,template_name,group_no,start_time,end_time,timelen,create_time,create_by) SELECT '%s','%s','%s',1,'%s',NULL,'%s',1,'%s','%s',%s,'2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train_detail WHERE uuid='%s');\n" "$team_detail_uuid" "$TEAM_UUID" "$STAFF_CSV" "$dt" "$template" "$start" "$end" "$mins" "$CREATOR" "$team_detail_uuid" >>"$tmp_sql"
    printf "INSERT INTO tn_staff_train_detail_ext(uuid,staff_train_detail_uuid,template_uuid,template_name,group_no,action_uuid,gnum,tnum,timelen,ggap,loads,create_time,create_by) SELECT '%s','%s',NULL,'%s',1,'%s','4','8','','90','RPE%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train_detail_ext WHERE uuid='%s');\n" "$team_ext_uuid" "$team_detail_uuid" "$template" "$action" "$rpe" "$CREATOR" "$team_ext_uuid" >>"$tmp_sql"
    for si in "${!STAFF[@]}"; do
      su="${STAFF[$si]}"
      ptruuid=$(printf "D26I%02d%d%02d-0000-4000-8000-%010d" "$wi" "$offset" "$si" "$((wi*100+offset*10+si+1))")
      pduuid=$(printf "D26J%02d%d%02d-0000-4000-8000-%010d" "$wi" "$offset" "$si" "$((wi*100+offset*10+si+1))")
      peuuid=$(printf "D26K%02d%d%02d-0000-4000-8000-%010d" "$wi" "$offset" "$si" "$((wi*100+offset*10+si+1))")
      prpe=$((6 + ((wi+offset+si)%3)))
      preal=$((mins - 8 + ((wi+si)%13)))
      printf "INSERT INTO tn_staff_train(uuid,organize_uuid,staff_uuids,type,target,train_date,plan_timelen,real_timelen,plan_rpe,rpe,create_time,create_by) SELECT '%s','%s','%s',2,'%s','%s','%s','%s','%s','%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train WHERE organize_uuid='%s' AND type=2 AND train_date='%s');\n" "$ptruuid" "$su" "$su" "$target" "$dt" "$mins" "$preal" "$prpe" "$prpe" "$CREATOR" "$su" "$dt" >>"$tmp_sql"
      printf "INSERT INTO tn_staff_train_detail(uuid,organize_uuid,staff_uuids,type,train_date,template_uuid,template_name,group_no,start_time,end_time,timelen,create_time,create_by) SELECT '%s','%s','%s',2,'%s',NULL,'%s',1,'%s','%s',%s,'2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train_detail WHERE uuid='%s');\n" "$pduuid" "$su" "$su" "$dt" "$template" "$start" "$end" "$mins" "$CREATOR" "$pduuid" >>"$tmp_sql"
      printf "INSERT INTO tn_staff_train_detail_ext(uuid,staff_train_detail_uuid,template_uuid,template_name,group_no,action_uuid,gnum,tnum,timelen,ggap,loads,create_time,create_by) SELECT '%s','%s',NULL,'%s',1,'%s','4','8','','90','RPE%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_staff_train_detail_ext WHERE uuid='%s');\n" "$peuuid" "$pduuid" "$template" "$action" "$prpe" "$CREATOR" "$peuuid" >>"$tmp_sql"
    done
  done
done

plan_dates=(2026-05-04 2026-05-18 2026-06-01 2026-06-15 2026-07-06 2026-07-20 2026-08-03 2026-08-17)
targets=(基础耐力 最大力量 速度能力 爆发力 基础耐力 最大力量 速度能力 赛前综合)
for pi in "${!plan_dates[@]}"; do
  dt="${plan_dates[$pi]}"; target="${targets[$pi]}"
  printf "INSERT INTO tn_training_plan(user_uuid,user_name,date,department,team,staff_uuids,staff_names,coach_name,is_del,create_time,update_time) SELECT '%s','[%s]','%s','演示运动队','演示运动队','%s','运动员 1,运动员 2,运动员 3,运动员 4,运动员 5,运动员 6,运动员 7,运动员 8,运动员 9,运动员 10','演示教练',0,'2026-08-26 14:30:00','2026-08-26 14:30:00' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_training_plan WHERE user_name='[%s]' AND date='%s' AND is_del=0);\n" "$ADMIN_UUID" "$MARKER" "$dt" "$STAFF_CSV" "$MARKER" "$dt" >>"$tmp_sql"
  printf "INSERT INTO tn_training_plan_outline(training_plan_id,exercise_category,program_exercise,\`require\`,tempo,load_rs,load_rs1,load_rs2,load_rs3,load_rs4,load_rs5,load_rs6) SELECT p.id,'主训练','深蹲 / 跑台 / 核心稳定','演示计划：动作规范、负荷渐进','3-1-X-1','LOAD:','4组','8次','75%%','90秒','','' FROM tn_training_plan p WHERE p.user_name='[%s]' AND p.date='%s' AND NOT EXISTS(SELECT 1 FROM tn_training_plan_outline o WHERE o.training_plan_id=p.id AND o.load_rs='LOAD:');\n" "$MARKER" "$dt" >>"$tmp_sql"
  printf "INSERT INTO tn_training_plan_outline(training_plan_id,exercise_category,program_exercise,\`require\`,tempo,load_rs,load_rs1,load_rs2,load_rs3,load_rs4,load_rs5,load_rs6) SELECT p.id,'辅助训练','拉伸 / 放松','演示计划：控制疲劳','2-1-2-1','R&S:','3组','12次','轻','60秒','','' FROM tn_training_plan p WHERE p.user_name='[%s]' AND p.date='%s' AND NOT EXISTS(SELECT 1 FROM tn_training_plan_outline o WHERE o.training_plan_id=p.id AND o.load_rs='R&S:');\n" "$MARKER" "$dt" >>"$tmp_sql"
  staff_json='[["运动员 1","4×8","75%","RPE7","完成"],["运动员 2","4×8","72%","RPE7","完成"],["运动员 3","4×8","78%","RPE8","完成"],["运动员 4","4×8","70%","RPE7","完成"],["运动员 5","4×8","76%","RPE8","完成"],["运动员 6","4×8","71%","RPE7","完成"],["运动员 7","4×8","80%","RPE8","完成"],["运动员 8","4×8","69%","RPE6","完成"],["运动员 9","4×8","77%","RPE8","完成"],["运动员 10","4×8","73%","RPE7","完成"]]'
  printf "INSERT INTO tn_training_plan_detail(training_plan_id,date,department_name,department_uuid,coach_name,team_uuid,team_name,import_staff_name,main_target,other_target,train_time,exercise_category,program_exercise,program_exercise_json,lull_time,\`require\`,staff_uuids,staff_names,staff_json,other_target1,other_target2) SELECT p.id,'%s','演示运动队','%s','演示教练','%s','演示运动队','运动员 1,运动员 2,运动员 3,运动员 4,运动员 5,运动员 6,运动员 7,运动员 8,运动员 9,运动员 10','%s','灵活性',120,'[\"主训练\"]','[\"深蹲\",\"间歇跑\",\"核心稳定\"]','[\"动作名称\",\"深蹲\",\"间歇跑\",\"核心稳定\"]','[\"间歇\",\"90秒\",\"120秒\",\"60秒\"]','[\"要求\",\"动作规范\",\"配速稳定\",\"核心收紧\"]','%s','运动员 1,运动员 2,运动员 3,运动员 4,运动员 5,运动员 6,运动员 7,运动员 8,运动员 9,运动员 10','%s','力量耐力','恢复能力' FROM tn_training_plan p WHERE p.user_name='[%s]' AND p.date='%s' AND NOT EXISTS(SELECT 1 FROM tn_training_plan_detail d WHERE d.training_plan_id=p.id AND d.date='%s');\n" "$dt" "$TEAM_UUID" "$TEAM_UUID" "$target" "$STAFF_CSV" "$staff_json" "$MARKER" "$dt" "$dt" >>"$tmp_sql"
done

injury_dates=(2026-05-06 2026-05-13 2026-05-27 2026-06-03 2026-06-17 2026-06-24 2026-07-01 2026-07-15 2026-07-22 2026-08-05 2026-08-12 2026-08-19 2026-08-24 2026-08-29)
positions=("$INJURY_ANKLE" "$INJURY_KNEE" "$INJURY_WAIST" "$INJURY_SHOULDER")
for ii in "${!injury_dates[@]}"; do
  dt="${injury_dates[$ii]}"; su="${STAFF[$((ii%10))]}"; pos="${positions[$((ii%4))]}"
  if (( ii % 3 == 0 )); then cate="$INJURY_ACUTE"; else cate="$INJURY_FATIGUE"; fi
  if (( ii == 7 || ii == 11 )); then degree="$INJURY_SEVERE"; days=14; else degree="$INJURY_NORMAL"; days=$((3+ii%6)); fi
  recovery=$(date -d "$dt +$days days" +%F 2>/dev/null || python3 -c "from datetime import datetime,timedelta; print((datetime.strptime('$dt','%Y-%m-%d')+timedelta(days=$days)).date())")
  [[ "$recovery" > "2026-08-31" ]] && recovery="NULL" || recovery="'$recovery'"
  uuid=$(printf "D26%04d0-0000-4000-8000-%012d" "$ii" "$((ii+1))")
  printf "INSERT INTO tn_injury(uuid,staff_uuid,start_date,injury_position_uuid,injury_cate_uuid,injury_degree_uuid,treat_measure,recovery_date,influence_train_days,influence_match,influence_match_days,influence_match_name,annex_quantity,demo,create_time,create_by) SELECT '%s','%s','%s','%s','%s','%s','冰敷、理疗、渐进恢复训练',%s,%s,%s,%s,'%s',0,'%s','2026-08-26 14:30:00','%s' FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM tn_injury WHERE uuid='%s');\n" "$uuid" "$su" "$dt" "$pos" "$cate" "$degree" "$recovery" "$days" "$((ii%4==0?1:0))" "$((ii%4==0?days:0))" "$([[ $((ii%4)) == 0 ]] && echo '演示邀请赛' || echo '')" "$MARKER" "$CREATOR" "$uuid" >>"$tmp_sql"
done

cat >>"$tmp_sql" <<SQL
UPDATE tn_staff s SET disease_times=(SELECT COUNT(*) FROM tn_injury i WHERE i.staff_uuid=s.uuid) WHERE s.uuid IN ($STAFF_SQL);
COMMIT;
SQL

"${mysql_cmd[@]}" <"$tmp_sql" 2>/dev/null
verify