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

MODE="${1:---dry-run}"
case "$MODE" in --dry-run|--apply) ;; *) echo "usage: $0 [--dry-run|--apply]" >&2; exit 2;; esac

SRC_CONFIG=/workspace/wwwroot/tnxlahtks/project/application/config/prod/tnxlahtks/database.php
TGT_CONFIG=/workspace/wwwroot/tnxlgstyj/project/application/config/prod/tnxlgstyj/database.php
BACKUP_ROOT=/workspace/backup/gstyj-selection-migration
TABLES=(tn_talent_area tn_talent_nation tn_talent_item tn_talent_unit tn_talent_norm tn_talent_cate tn_talent_cate_norm tn_talent_score tn_talent_score_detail tn_talent_staff tn_talent_test tn_talent_test_detail)

for f in "$SRC_CONFIG" "$TGT_CONFIG"; do
  test -r "$f" || { echo "missing config: $f" >&2; exit 1; }
done

eval "$(php -r '
$s=include $argv[1]; $t=include $argv[2];
foreach (["hostname"=>"DB_HOST","username"=>"DB_USER","password"=>"DB_PASS"] as $k=>$v) {
  echo $v,"=",escapeshellarg($s[$k]),";";
}
echo "SRC_DB=",escapeshellarg($s["database"]),";";
echo "TGT_DB=",escapeshellarg($t["database"]),";";
if ($s["hostname"]!==$t["hostname"] || $s["username"]!==$t["username"] || $s["password"]!==$t["password"]) exit(3);
' "$SRC_CONFIG" "$TGT_CONFIG")" || { echo "source and target are not cross-database compatible" >&2; exit 1; }
export MYSQL_PWD="$DB_PASS"
trap 'unset DB_HOST DB_USER DB_PASS SRC_DB TGT_DB MYSQL_PWD' EXIT

mysql_cmd=(mysql --batch --raw -h "$DB_HOST" -u "$DB_USER" -D "$TGT_DB")
q() { "${mysql_cmd[@]}" -N -e "$1"; }

echo "mode=$MODE"
echo "schema-signatures:"
q "SET SESSION group_concat_max_len=1000000;
SELECT table_schema,table_name,SHA2(GROUP_CONCAT(CONCAT_WS('|',ordinal_position,column_name,column_type,is_nullable,IFNULL(column_default,'NULL')) ORDER BY ordinal_position SEPARATOR ';'),256)
FROM information_schema.columns WHERE table_schema IN ('$SRC_DB','$TGT_DB') AND table_name LIKE 'tn_talent_%'
GROUP BY table_schema,table_name ORDER BY table_name,table_schema;"
echo "row-counts:"
for t in "${TABLES[@]}"; do q "SELECT '$t',(SELECT COUNT(*) FROM \`$SRC_DB\`.\`$t\`),(SELECT COUNT(*) FROM \`$TGT_DB\`.\`$t\`);"; done

existing_total=$(q "SELECT SUM(c) FROM (
SELECT COUNT(*) c FROM \`$TGT_DB\`.tn_talent_unit UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_norm
UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_cate UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_cate_norm
UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_score UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_score_detail
UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_staff UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_test
UNION ALL SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_test_detail) x;")
marker_staff=$(q "SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_staff WHERE name REGEXP '^人员 (10|[1-9])$' AND del_flag=0;")
marker_tests=$(q "SELECT COUNT(*) FROM \`$TGT_DB\`.tn_talent_test WHERE title REGEXP '^2026年选材测评-人员 (10|[1-9])$';")
if [[ "$existing_total" != 0 ]]; then
  if [[ "$marker_staff" == 10 && "$marker_tests" == 10 ]]; then
    echo "already applied; running verification only"
    MODE=--verify
  else
    echo "ABORT: target selection business tables are non-empty and do not match the complete migration marker" >&2
    exit 4
  fi
fi

if [[ "$MODE" == --dry-run ]]; then
  echo "dry-run complete: no writes performed"
  exit 0
fi

if [[ "$MODE" != --verify ]]; then
stamp=$(date +%Y%m%dT%H%M%S)
backup_dir="$BACKUP_ROOT/$stamp"
mkdir -p "$backup_dir"
mysqldump --single-transaction --skip-lock-tables --routines=false --triggers=false \
  -h "$DB_HOST" -u "$DB_USER" "$TGT_DB" "${TABLES[@]}" | gzip -c > "$backup_dir/target-before.sql.gz"
test -s "$backup_dir/target-before.sql.gz"
echo "backup=$backup_dir/target-before.sql.gz"

sql_file=$(mktemp)
trap 'rm -f "$sql_file"; unset DB_HOST DB_USER DB_PASS SRC_DB TGT_DB MYSQL_PWD' EXIT
sed -e "s/__SRC_DB__/$SRC_DB/g" -e "s/__TGT_DB__/$TGT_DB/g" > "$sql_file" <<'SQL'
SET NAMES utf8mb4;
SET SESSION group_concat_max_len=1000000;
START TRANSACTION;

CREATE TEMPORARY TABLE map_unit (src_uuid CHAR(36) PRIMARY KEY, tgt_uuid CHAR(36) NOT NULL UNIQUE);
CREATE TEMPORARY TABLE map_norm (src_uuid CHAR(36) PRIMARY KEY, tgt_uuid CHAR(36) NOT NULL UNIQUE);
CREATE TEMPORARY TABLE map_cate (src_uuid CHAR(36) PRIMARY KEY, tgt_uuid CHAR(36) NOT NULL UNIQUE);
CREATE TEMPORARY TABLE map_score (src_uuid CHAR(36) PRIMARY KEY, tgt_uuid CHAR(36) NOT NULL UNIQUE);
INSERT INTO map_unit SELECT uuid,UUID() FROM `__SRC_DB__`.tn_talent_unit;
INSERT INTO map_norm SELECT uuid,UUID() FROM `__SRC_DB__`.tn_talent_norm;
INSERT INTO map_cate SELECT uuid,UUID() FROM `__SRC_DB__`.tn_talent_cate;
INSERT INTO map_score SELECT uuid,UUID() FROM `__SRC_DB__`.tn_talent_score;

INSERT INTO `__TGT_DB__`.tn_talent_area SELECT * FROM `__SRC_DB__`.tn_talent_area;
INSERT INTO `__TGT_DB__`.tn_talent_nation (id,name) SELECT id,name FROM `__SRC_DB__`.tn_talent_nation;
INSERT INTO `__TGT_DB__`.tn_talent_item (id,name,pid,status) SELECT id,name,pid,status FROM `__SRC_DB__`.tn_talent_item;
INSERT INTO `__TGT_DB__`.tn_talent_unit (id,uuid,name,order_no,status,del_flag,use_num,demo,create_time,create_by)
SELECT s.id,m.tgt_uuid,s.name,s.order_no,s.status,s.del_flag,s.use_num,s.demo,s.create_time,s.create_by FROM `__SRC_DB__`.tn_talent_unit s JOIN map_unit m ON m.src_uuid=s.uuid;
INSERT INTO `__TGT_DB__`.tn_talent_norm (id,uuid,name,pinyin,unit_uuid,is_derived,edit_flag,value_type,del_flag,use_num,order_no,create_time,create_by)
SELECT s.id,m.tgt_uuid,s.name,s.pinyin,mu.tgt_uuid,s.is_derived,s.edit_flag,s.value_type,s.del_flag,s.use_num,s.order_no,s.create_time,s.create_by
FROM `__SRC_DB__`.tn_talent_norm s JOIN map_norm m ON m.src_uuid=s.uuid LEFT JOIN map_unit mu ON mu.src_uuid=s.unit_uuid;
INSERT INTO `__TGT_DB__`.tn_talent_cate (id,uuid,name,norm_quantity,del_flag,demo,use_num,create_time,create_by)
SELECT s.id,m.tgt_uuid,s.name,s.norm_quantity,s.del_flag,s.demo,s.use_num,s.create_time,s.create_by FROM `__SRC_DB__`.tn_talent_cate s JOIN map_cate m ON m.src_uuid=s.uuid;
INSERT INTO `__TGT_DB__`.tn_talent_cate_norm (id,cate_uuid,del_flag,norm_uuid,use_num,create_time,create_by)
SELECT s.id,mc.tgt_uuid,s.del_flag,mn.tgt_uuid,s.use_num,s.create_time,s.create_by FROM `__SRC_DB__`.tn_talent_cate_norm s JOIN map_cate mc ON mc.src_uuid=s.cate_uuid JOIN map_norm mn ON mn.src_uuid=s.norm_uuid;
INSERT INTO `__TGT_DB__`.tn_talent_score (id,uuid,name,cate_uuid,sex,age,pitem_id,item_id,del_flag,use_num,order_no,demo,create_time,create_by)
SELECT s.id,m.tgt_uuid,s.name,mc.tgt_uuid,s.sex,s.age,s.pitem_id,s.item_id,s.del_flag,s.use_num,s.order_no,s.demo,s.create_time,s.create_by
FROM `__SRC_DB__`.tn_talent_score s JOIN map_score m ON m.src_uuid=s.uuid LEFT JOIN map_cate mc ON mc.src_uuid=s.cate_uuid;
INSERT INTO `__TGT_DB__`.tn_talent_score_detail (id,score_uuid,norm_uuid,num,upper_limit,lower_limit,title,level,score)
SELECT s.id,ms.tgt_uuid,mn.tgt_uuid,s.num,s.upper_limit,s.lower_limit,s.title,s.level,s.score FROM `__SRC_DB__`.tn_talent_score_detail s JOIN map_score ms ON ms.src_uuid=s.score_uuid JOIN map_norm mn ON mn.src_uuid=s.norm_uuid;

SET @template_test=(SELECT t.uuid FROM `__SRC_DB__`.tn_talent_test t ORDER BY (SELECT COUNT(*) FROM `__SRC_DB__`.tn_talent_test_detail d WHERE d.test_uuid=t.uuid) DESC,t.test_date DESC LIMIT 1);
SET @template_cate=(SELECT cate_uuid FROM `__SRC_DB__`.tn_talent_test WHERE uuid=@template_test);
SET @template_pitem=(SELECT pitem_id FROM `__SRC_DB__`.tn_talent_test WHERE uuid=@template_test);
SET @template_item=(SELECT item_id FROM `__SRC_DB__`.tn_talent_test WHERE uuid=@template_test);
SET @template_staff=(SELECT staff_uuid FROM `__SRC_DB__`.tn_talent_test_detail WHERE test_uuid=@template_test ORDER BY id LIMIT 1);
SET @target_cate=(SELECT tgt_uuid FROM map_cate WHERE src_uuid=@template_cate);
SET @target_norms=(SELECT GROUP_CONCAT(DISTINCT mn.tgt_uuid ORDER BY d.id) FROM `__SRC_DB__`.tn_talent_test_detail d JOIN map_norm mn ON mn.src_uuid=d.norm_uuid WHERE d.test_uuid=@template_test);
SET @norm_qty=(SELECT COUNT(DISTINCT norm_uuid) FROM `__SRC_DB__`.tn_talent_test_detail WHERE test_uuid=@template_test);
SET @nation=(SELECT MIN(id) FROM `__TGT_DB__`.tn_talent_nation);
SET @province=(SELECT MIN(id) FROM `__TGT_DB__`.tn_talent_area WHERE pid=0);
SET @city=(SELECT MIN(id) FROM `__TGT_DB__`.tn_talent_area WHERE pid=@province);
SET @county=(SELECT MIN(id) FROM `__TGT_DB__`.tn_talent_area WHERE pid=@city);
CREATE TEMPORARY TABLE seq10 (n INT PRIMARY KEY);
INSERT INTO seq10 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
CREATE TEMPORARY TABLE generated_staff (n INT PRIMARY KEY,staff_uuid CHAR(36) UNIQUE,test_uuid CHAR(36) UNIQUE,test_date DATE);
INSERT INTO generated_staff SELECT n,UUID(),UUID(),DATE_ADD('2026-05-03',INTERVAL (n-1)*13 DAY) FROM seq10;
INSERT INTO `__TGT_DB__`.tn_talent_staff (uuid,name,sex,birthday,age,nation_id,pitem_id,item_id,province_id,city_id,county_id,train_year,test_date,status,del_flag,create_time,create_by)
SELECT g.staff_uuid,CONCAT('人员 ',g.n),IF(MOD(g.n,2)=1,1,2),DATE_SUB(g.test_date,INTERVAL (12+MOD(g.n,5)) YEAR),12+MOD(g.n,5),@nation,@template_pitem,@template_item,@province,@city,@county,1+MOD(g.n,5),g.test_date,1,0,CONCAT(g.test_date,' 09:00:00'),'migration'
FROM generated_staff g;
INSERT INTO `__TGT_DB__`.tn_talent_test (uuid,title,test_date,sex,staff_uuid_str,norm_uuid_str,staff_quantity,norm_quantity,pitem_id,item_id,cate_uuid,coach,demo,summary,create_time,create_by)
SELECT g.test_uuid,CONCAT('2026年选材测评-人员 ',g.n),g.test_date,IF(MOD(g.n,2)=1,1,2),g.staff_uuid,@target_norms,1,@norm_qty,@template_pitem,@template_item,@target_cate,'选材组','基于安体科所选材数据结构生成',CONCAT('人员 ',g.n,' 完成标准选材指标测评，数据用于列表、详情、图表及报告验证。'),CONCAT(g.test_date,' 10:00:00'),'migration'
FROM generated_staff g;
INSERT INTO `__TGT_DB__`.tn_talent_test_detail (test_uuid,staff_uuid,norm_uuid,value,is_derived)
SELECT g.test_uuid,g.staff_uuid,mn.tgt_uuid,d.value,d.is_derived FROM generated_staff g JOIN `__SRC_DB__`.tn_talent_test_detail d ON d.test_uuid=@template_test AND d.staff_uuid=@template_staff JOIN map_norm mn ON mn.src_uuid=d.norm_uuid;

COMMIT;
SELECT 'MAP_UNIT' mapping_type,src_uuid,tgt_uuid FROM map_unit;
SELECT 'MAP_NORM' mapping_type,src_uuid,tgt_uuid FROM map_norm;
SELECT 'MAP_CATE' mapping_type,src_uuid,tgt_uuid FROM map_cate;
SELECT 'MAP_SCORE' mapping_type,src_uuid,tgt_uuid FROM map_score;
SELECT 'GENERATED_STAFF' mapping_type,CONCAT('人员 ',n) source_key,staff_uuid target_uuid FROM generated_staff;
SELECT 'GENERATED_TEST' mapping_type,staff_uuid source_key,test_uuid target_uuid FROM generated_staff;
SQL

"${mysql_cmd[@]}" < "$sql_file" | tee "$backup_dir/uuid-mapping.tsv" >/dev/null
echo "mapping=$backup_dir/uuid-mapping.tsv"
fi

echo "verification:"
q "SELECT 'staff',COUNT(*),MIN(test_date),MAX(test_date) FROM \`$TGT_DB\`.tn_talent_staff WHERE name REGEXP '^人员 (10|[1-9])$' AND del_flag=0;
SELECT 'tests',COUNT(*),MIN(test_date),MAX(test_date),SUM(staff_quantity),SUM(norm_quantity) FROM \`$TGT_DB\`.tn_talent_test WHERE title REGEXP '^2026年选材测评-人员 (10|[1-9])$';
SELECT 'details',COUNT(*) FROM \`$TGT_DB\`.tn_talent_test_detail d JOIN \`$TGT_DB\`.tn_talent_test t ON t.uuid=d.test_uuid WHERE t.title REGEXP '^2026年选材测评-人员 (10|[1-9])$';
SELECT 'orphan_cate_norm_cate',COUNT(*) FROM \`$TGT_DB\`.tn_talent_cate_norm x LEFT JOIN \`$TGT_DB\`.tn_talent_cate c ON c.uuid=x.cate_uuid WHERE c.uuid IS NULL;
SELECT 'orphan_cate_norm_norm',COUNT(*) FROM \`$TGT_DB\`.tn_talent_cate_norm x LEFT JOIN \`$TGT_DB\`.tn_talent_norm n ON n.uuid=x.norm_uuid WHERE n.uuid IS NULL;
SELECT 'orphan_score_detail_score',COUNT(*) FROM \`$TGT_DB\`.tn_talent_score_detail x LEFT JOIN \`$TGT_DB\`.tn_talent_score s ON s.uuid=x.score_uuid WHERE s.uuid IS NULL;
SELECT 'orphan_score_detail_norm',COUNT(*) FROM \`$TGT_DB\`.tn_talent_score_detail x LEFT JOIN \`$TGT_DB\`.tn_talent_norm n ON n.uuid=x.norm_uuid WHERE n.uuid IS NULL;
SELECT 'orphan_test_detail_test',COUNT(*) FROM \`$TGT_DB\`.tn_talent_test_detail x LEFT JOIN \`$TGT_DB\`.tn_talent_test t ON t.uuid=x.test_uuid WHERE t.uuid IS NULL;
SELECT 'orphan_test_detail_staff',COUNT(*) FROM \`$TGT_DB\`.tn_talent_test_detail x LEFT JOIN \`$TGT_DB\`.tn_talent_staff s ON s.uuid=x.staff_uuid WHERE s.uuid IS NULL;
SELECT 'orphan_test_detail_norm',COUNT(*) FROM \`$TGT_DB\`.tn_talent_test_detail x LEFT JOIN \`$TGT_DB\`.tn_talent_norm n ON n.uuid=x.norm_uuid WHERE n.uuid IS NULL;"
echo "apply complete"