<?php
/**
 * GSTYJ 状态监控 -> 机能状态演示数据纠错。
 *
 * 目标：
 * 1. 精确删除 DEMO_BIZ_20260826 批次中误用运动能力指标的 4 个阶段；
 * 2. 对照 DPS “阶段生化测试”的 6 个真实指标、单位和男女阈值；
 * 3. 为演示运动队运动员 1~10 生成 2026-05~08 的 4 阶段合理数据；
 * 4. 同时维护 GSTYJ 当前读取链和历史兼容链。
 *
 * 安全约束：
 * - 不包含凭据，数据库连接仅从服务器应用配置读取；
 * - 默认 dry-run；apply 前自动生成服务器非 Web SQL 全表备份和库内精确关系闭包备份；
 * - simulate 会完整写入后强制回滚；
 * - 任一指标语义、关系闭包、外部引用或数量不符合预期即停止；
 * - 不修改设置、人员、组织、设备、训练、伤病、选材或代码。
 *
 * 用法（在目标服务器执行）：
 *   php functional-status-demo-correction.php dry-run
 *   php functional-status-demo-correction.php simulate
 *   php functional-status-demo-correction.php apply
 *   php functional-status-demo-correction.php verify
 *   php functional-status-demo-correction.php rollback
 */

declare(strict_types=1);

const SOURCE_DB_CONFIG = '/workspace/wwwroot/tnxl/project/application/config/prod/tnxl/database.php';
const TARGET_DB_CONFIG = '/workspace/wwwroot/tnxlgstyj/project/application/config/prod/tnxlgstyj/database.php';
const TARGET_APP_CONFIG = '/workspace/wwwroot/tnxlgstyj/project/application/config.php';
const BACKUP_DIR = '/root/gstyj-maintenance/20260826-functional-status-correction';
const BACKUP_PREFIX = 'migbak_func_status_fix_20260826_';
const OLD_MARKER = '[DEMO_BIZ_20260826]';
const OLD_CREATOR = '演示数据迁移';
const NEW_MARKER = '[DEMO_FUNC_STATUS_20260826]';
const NEW_DEMO = 'DEMO_FUNC_STATUS_20260826';
const NEW_CREATOR = '演示机能状态修正';
const TEAM_NAME = '演示运动队';
const CATEGORY_NAME = '阶段生化测试';
const FIXED_CREATE_TIME = '2026-08-26 18:00:00';

$mode = $argv[1] ?? 'dry-run';
if (!in_array($mode, ['dry-run', 'simulate', 'apply', 'verify', 'rollback'], true)) {
    fwrite(STDERR, "Usage: php functional-status-demo-correction.php dry-run|simulate|apply|verify|rollback\n");
    exit(2);
}

function fail(string $message, int $code = 1): void
{
    fwrite(STDERR, "STOP: {$message}\n");
    exit($code);
}

function assertTrue(bool $condition, string $message): void
{
    if (!$condition) {
        throw new RuntimeException($message);
    }
}

function dbConfig(string $path): array
{
    if (!is_readable($path)) {
        fail("Database config is not readable: {$path}", 3);
    }
    $config = include $path;
    foreach (['hostname', 'username', 'password', 'database'] as $key) {
        if (!array_key_exists($key, $config)) {
            fail("Database config is missing key: {$key}", 3);
        }
    }
    return $config;
}

function pdoFromConfig(array $config): PDO
{
    $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $config['hostname'], $config['database']);
    return new PDO($dsn, (string)$config['username'], (string)$config['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
}

function fetchAll(PDO $pdo, string $sql, array $params = []): array
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function fetchOne(PDO $pdo, string $sql, array $params = []): ?array
{
    $rows = fetchAll($pdo, $sql, $params);
    return $rows[0] ?? null;
}

function scalar(PDO $pdo, string $sql, array $params = [])
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchColumn();
}

function placeholders(int $count): string
{
    assertTrue($count > 0, 'Cannot build an empty IN clause.');
    return implode(',', array_fill(0, $count, '?'));
}

function normalizedFloat($value): float
{
    return round((float)$value, 4);
}

function expectedMetricSignatures(): array
{
    return [
        '红细胞' => ['unit' => '× 10^12/L', 'male' => [4.0, 5.5], 'female' => [3.5, 5.0]],
        '肌酸激酶' => ['unit' => 'U/I', 'male' => [100.0, 300.0], 'female' => [100.0, 200.0]],
        '皮质醇' => ['unit' => 'ug/dl', 'male' => [6.0, 26.0], 'female' => [6.0, 26.0]],
        '血睾酮' => ['unit' => 'ng/dl', 'male' => [270.0, 1000.0], 'female' => [10.0, 100.0]],
        '血红蛋白' => ['unit' => 'g/L', 'male' => [120.0, 160.0], 'female' => [110.0, 150.0]],
        '血尿素' => ['unit' => 'mmol/L', 'male' => [3.2, 8.0], 'female' => [3.2, 8.0]],
    ];
}

function discover(PDO $source, PDO $target): array
{
    $teams = fetchAll($target, 'SELECT uuid,name FROM tn_department WHERE name=? AND del_flag=0', [TEAM_NAME]);
    assertTrue(count($teams) === 1, '目标系统“演示运动队”必须唯一且有效。');
    $teamUuid = $teams[0]['uuid'];

    $athletes = fetchAll(
        $target,
        "SELECT s.uuid,s.name,s.sex
         FROM tn_staff s
         JOIN tn_staff_department sd ON sd.staff_uuid=s.uuid
         WHERE sd.department_uuid=? AND s.del_flag=0 AND s.is_athlete=1
           AND s.name REGEXP '^运动员( )?[0-9]+$'
         ORDER BY CAST(REPLACE(REPLACE(s.name,'运动员',''),' ','') AS UNSIGNED)",
        [$teamUuid]
    );
    assertTrue(count($athletes) === 10, '目标运动员 1~10 必须完整存在。');
    $expectedNames = [];
    for ($i = 1; $i <= 10; $i++) {
        $expectedNames[] = '运动员 ' . $i;
    }
    assertTrue(array_column($athletes, 'name') === $expectedNames, '运动员名称或排序不符合 1~10。');
    $sexCounts = array_count_values(array_map('intval', array_column($athletes, 'sex')));
    assertTrue(($sexCounts[1] ?? 0) === 5 && ($sexCounts[2] ?? 0) === 5, '运动员必须男女各 5 人。');

    $sourceRows = fetchAll(
        $source,
        "SELECT c.uuid cate_uuid,c.name,n.uuid,n.cn_name,u.cn_name unit_name,
                n.value_type,n.float_digit,n.male_symbol,n.male_lower_limit,n.male_upper_limit,
                n.female_symbol,n.female_lower_limit,n.female_upper_limit,n.edit_flag,n.pinyin,n.en_name
         FROM tn_cate c
         JOIN tn_norm_cate nc ON nc.cate_uuid=c.uuid
         JOIN tn_norm n ON n.uuid=nc.norm_uuid
         LEFT JOIN tn_unit u ON u.uuid=n.unit_uuid
         WHERE c.type=7 AND c.name=? AND c.status=1 AND c.del_flag=0
           AND n.status=1 AND n.del_flag=0
         ORDER BY n.cn_name",
        [CATEGORY_NAME]
    );
    assertMetricSemantics($sourceRows, 'DPS');

    $targetRows = fetchAll(
        $target,
        "SELECT c.uuid cate_uuid,c.name,c.builtin,c.edit_flag cate_edit_flag,
                n.uuid,n.cn_name,u.cn_name unit_name,n.unit_uuid,n.value_type,n.float_digit,
                n.male_symbol,n.male_lower_limit,n.male_upper_limit,
                n.female_symbol,n.female_lower_limit,n.female_upper_limit,
                n.edit_flag,n.pinyin,n.en_name,n.device_model_id
         FROM tn_cate c
         JOIN tn_norm_cate nc ON nc.cate_uuid=c.uuid
         JOIN tn_norm n ON n.uuid=nc.norm_uuid
         LEFT JOIN tn_unit u ON u.uuid=n.unit_uuid
         WHERE c.type=7 AND c.name=? AND c.status=1 AND c.del_flag=0
           AND n.status=1 AND n.del_flag=0
         ORDER BY n.cn_name",
        [CATEGORY_NAME]
    );
    assertMetricSemantics($targetRows, 'GSTYJ');
    assertTrue(count(array_unique(array_column($targetRows, 'cate_uuid'))) === 1, '目标“阶段生化测试”分类必须唯一。');
    foreach ($targetRows as $row) {
        assertTrue($row['device_model_id'] === null || $row['device_model_id'] === '', '手工阶段生化指标不得误用设备专属指标。');
    }

    $sourceByName = [];
    foreach ($sourceRows as $row) {
        $sourceByName[$row['cn_name']] = $row;
    }
    $targetByName = [];
    foreach ($targetRows as $row) {
        $targetByName[$row['cn_name']] = $row;
    }
    foreach (array_keys(expectedMetricSignatures()) as $name) {
        foreach (['unit_name', 'male_lower_limit', 'male_upper_limit', 'female_lower_limit', 'female_upper_limit'] as $field) {
            $left = $sourceByName[$name][$field];
            $right = $targetByName[$name][$field];
            if ($field !== 'unit_name') {
                $left = normalizedFloat($left);
                $right = normalizedFloat($right);
            }
            assertTrue($left === $right, "{$name} 的 {$field} 在源、目标不一致。");
        }
    }

    return [
        'team_uuid' => $teamUuid,
        'athletes' => $athletes,
        'category_uuid' => $targetRows[0]['cate_uuid'],
        'metrics' => $targetByName,
    ];
}

function assertMetricSemantics(array $rows, string $system): void
{
    $expected = expectedMetricSignatures();
    assertTrue(count($rows) === 6, "{$system} 阶段生化测试必须恰好包含 6 个指标。");
    $names = array_column($rows, 'cn_name');
    sort($names);
    $expectedNames = array_keys($expected);
    sort($expectedNames);
    assertTrue($names === $expectedNames, "{$system} 阶段生化指标名称集不符合预期。");
    foreach ($rows as $row) {
        $signature = $expected[$row['cn_name']];
        assertTrue($row['unit_name'] === $signature['unit'], "{$system} {$row['cn_name']} 单位不一致。");
        assertTrue(normalizedFloat($row['male_lower_limit']) === $signature['male'][0], "{$system} {$row['cn_name']} 男性下限不一致。");
        assertTrue(normalizedFloat($row['male_upper_limit']) === $signature['male'][1], "{$system} {$row['cn_name']} 男性上限不一致。");
        assertTrue(normalizedFloat($row['female_lower_limit']) === $signature['female'][0], "{$system} {$row['cn_name']} 女性下限不一致。");
        assertTrue(normalizedFloat($row['female_upper_limit']) === $signature['female'][1], "{$system} {$row['cn_name']} 女性上限不一致。");
    }
}

function wrongClosure(PDO $pdo, array $context): array
{
    $teamUuid = $context['team_uuid'];
    $current = fetchAll(
        $pdo,
        'SELECT id,title,test_date FROM tn_test_result WHERE title LIKE ? AND create_by=? AND department_uuid=? AND type=1 ORDER BY test_date,id',
        [OLD_MARKER . '%', OLD_CREATOR, $teamUuid]
    );
    $legacy = fetchAll(
        $pdo,
        'SELECT uuid,title,test_date FROM tn_test WHERE title LIKE ? AND create_by=? AND department_uuid=? AND type=7 ORDER BY test_date,id',
        [OLD_MARKER . '%', OLD_CREATOR, $teamUuid]
    );
    $currentIds = array_map('intval', array_column($current, 'id'));
    $legacyUuids = array_column($legacy, 'uuid');
    if (count($currentIds) !== 4 || count($legacyUuids) !== 4) {
        return ['current' => $current, 'legacy' => $legacy, 'counts' => [], 'safe' => false];
    }

    $currentIn = placeholders(count($currentIds));
    $legacyIn = placeholders(count($legacyUuids));
    $valueIds = array_map('intval', array_column(fetchAll(
        $pdo,
        "SELECT DISTINCT value_id FROM tn_test_result_value WHERE result_id IN ({$currentIn})",
        $currentIds
    ), 'value_id'));
    assertTrue(count($valueIds) === 160, '错误当前链应精确包含 160 个 value_id。');
    $valueIn = placeholders(count($valueIds));
    $sharedRefs = (int)scalar(
        $pdo,
        "SELECT COUNT(*) FROM tn_test_result_value WHERE value_id IN ({$valueIn}) AND result_id NOT IN ({$currentIn})",
        array_merge($valueIds, $currentIds)
    );
    assertTrue($sharedRefs === 0, '错误 tn_test_value 被其他业务结果引用，禁止删除。');

    $wrongMetricNames = fetchAll(
        $pdo,
        "SELECT DISTINCT n.cn_name
         FROM tn_test_value v JOIN tn_norm n ON n.uuid=v.norm_uuid
         WHERE v.id IN ({$valueIn}) ORDER BY n.cn_name",
        $valueIds
    );
    $names = array_column($wrongMetricNames, 'cn_name');
    sort($names);
    $expectedWrong = ['10米冲刺', 'CMJ纵跳', '深蹲', '最大摄氧量'];
    sort($expectedWrong);
    assertTrue($names === $expectedWrong, '待删数据不再是已取证的 4 个错误指标，禁止继续。');

    $counts = [
        'current_results' => count($currentIds),
        'current_links' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_test_result_value WHERE result_id IN ({$currentIn})", $currentIds),
        'current_values' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_test_value WHERE id IN ({$valueIn})", $valueIds),
        'current_permissions' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff WHERE type=10 AND outer_uuid IN ({$currentIn})", $currentIds),
        'legacy_tests' => count($legacyUuids),
        'legacy_values' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_staff_test WHERE test_uuid IN ({$legacyIn})", $legacyUuids),
        'legacy_permissions' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff WHERE type=1 AND outer_uuid IN ({$legacyIn})", $legacyUuids),
        'legacy_physical_refs' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_staff_physical_test WHERE test_uuid IN ({$legacyIn})", $legacyUuids),
        'shared_value_refs' => $sharedRefs,
    ];
    assertTrue($counts['current_links'] === 160, '错误当前关联行必须为 160。');
    assertTrue($counts['current_values'] === 160, '错误当前值行必须为 160。');
    assertTrue($counts['current_permissions'] === 40, '错误当前权限行必须为 40。');
    assertTrue($counts['legacy_values'] === 160, '错误历史兼容值必须为 160。');
    assertTrue($counts['legacy_permissions'] === 0, '旧批次意外出现历史权限关联，需要单独审核。');
    assertTrue($counts['legacy_physical_refs'] === 0, '旧批次被体能测试引用，禁止删除。');
    assertTrue((int)scalar($pdo, 'SELECT COUNT(*) FROM tn_test_value WHERE create_by=?', [OLD_CREATOR]) === 160, '旧创建人标记还包含其他 test_value，禁止扩大删除。');
    assertTrue((int)scalar($pdo, 'SELECT COUNT(*) FROM tn_staff_test WHERE create_by=?', [OLD_CREATOR]) === 160, '旧创建人标记还包含其他 staff_test，禁止扩大删除。');

    return [
        'current' => $current,
        'legacy' => $legacy,
        'current_ids' => $currentIds,
        'legacy_uuids' => $legacyUuids,
        'value_ids' => $valueIds,
        'counts' => $counts,
        'safe' => true,
    ];
}

function newCounts(PDO $pdo, array $context): array
{
    $teamUuid = $context['team_uuid'];
    $currentIds = array_map('intval', array_column(fetchAll(
        $pdo,
        'SELECT id FROM tn_test_result WHERE title LIKE ? AND create_by=? AND department_uuid=? AND type=1 ORDER BY id',
        [NEW_MARKER . '%', NEW_CREATOR, $teamUuid]
    ), 'id'));
    $legacyUuids = array_column(fetchAll(
        $pdo,
        'SELECT uuid FROM tn_test WHERE title LIKE ? AND create_by=? AND department_uuid=? AND type=7 ORDER BY id',
        [NEW_MARKER . '%', NEW_CREATOR, $teamUuid]
    ), 'uuid');
    $counts = [
        'current_results' => count($currentIds),
        'current_links' => 0,
        'current_values' => 0,
        'current_permissions' => 0,
        'legacy_tests' => count($legacyUuids),
        'legacy_values' => 0,
        'legacy_permissions' => 0,
    ];
    if ($currentIds) {
        $in = placeholders(count($currentIds));
        $counts['current_links'] = (int)scalar($pdo, "SELECT COUNT(*) FROM tn_test_result_value WHERE result_id IN ({$in}) AND is_del=0", $currentIds);
        $counts['current_values'] = (int)scalar($pdo, "SELECT COUNT(DISTINCT value_id) FROM tn_test_result_value WHERE result_id IN ({$in}) AND is_del=0", $currentIds);
        $counts['current_permissions'] = (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff WHERE type=10 AND outer_uuid IN ({$in})", $currentIds);
    }
    if ($legacyUuids) {
        $in = placeholders(count($legacyUuids));
        $counts['legacy_values'] = (int)scalar($pdo, "SELECT COUNT(*) FROM tn_staff_test WHERE test_uuid IN ({$in})", $legacyUuids);
        $counts['legacy_permissions'] = (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff WHERE type=1 AND outer_uuid IN ({$in})", $legacyUuids);
    }
    return $counts;
}

function printPlan(array $context, array $wrong, array $new): void
{
    echo "DPS reference semantics: 1 category / 6 metrics\n";
    echo "Target mapping: 1 team / 10 athletes (5 male, 5 female) / 6 semantic metrics\n";
    if ($wrong['safe']) {
        echo "Wrong batch deletion closure:\n";
        foreach ($wrong['counts'] as $key => $value) {
            echo "  {$key}: {$value}\n";
        }
    } else {
        echo "Wrong batch is not in the expected pre-apply state.\n";
    }
    echo "Existing corrected batch:\n";
    foreach ($new as $key => $value) {
        echo "  {$key}: {$value}\n";
    }
    echo "Planned insertion:\n";
    echo "  current chain: 4 results + 240 values + 240 links + 40 permissions\n";
    echo "  legacy chain: 4 tests + 240 values + 40 permissions\n";
    echo "  dates: 2026-05-15 / 2026-06-18 / 2026-07-16 / 2026-08-13\n";
    echo "  settings / people / organization / equipment / other business writes: 0\n";
    echo "  Redis: disabled in target runtime; active cache rows to delete: 0\n";
}

function redisIsDisabled(): bool
{
    if (!defined('APP_PATH')) {
        define('APP_PATH', '/workspace/wwwroot/tnxlgstyj/project/application/');
        define('TP_ENV', 'prod');
        define('BUSINESS', 'tnxlgstyj');
        define('THINK_PATH', '/workspace/wwwroot/tnxlgstyj/project/thinkphp/');
        define('EXT', '.php');
        define('DS', DIRECTORY_SEPARATOR);
        define('LOG_PATH', '/tmp/');
        define('CACHE_PATH', '/tmp/');
    }
    $config = include TARGET_APP_CONFIG;
    return empty($config['redisOn']);
}

function createFullBackup(array $targetConfig, array $manifest): string
{
    if (!is_dir(BACKUP_DIR) && !mkdir(BACKUP_DIR, 0700, true) && !is_dir(BACKUP_DIR)) {
        throw new RuntimeException('Cannot create backup directory.');
    }
    chmod(BACKUP_DIR, 0700);
    $sqlPath = BACKUP_DIR . '/before-full-tables.sql';
    if (!file_exists($sqlPath)) {
        throw new RuntimeException(
            'External backup is missing. Run functional-status-demo-correction.sh apply; '
            . 'the production PHP runtime disables proc_open.'
        );
    }
    assertTrue(filesize($sqlPath) > 0, 'Backup SQL is empty.');
    $manifestPath = BACKUP_DIR . '/manifest.json';
    file_put_contents($manifestPath, json_encode($manifest, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n");
    chmod($manifestPath, 0600);
    return $sqlPath;
}

function createScopedBackups(PDO $pdo, array $wrong): void
{
    $tables = [
        'test_result' => 'tn_test_result',
        'test_value' => 'tn_test_value',
        'test_result_value' => 'tn_test_result_value',
        'manage_staff' => 'tn_manage_staff',
        'test' => 'tn_test',
        'staff_test' => 'tn_staff_test',
        'staff_physical_test' => 'tn_staff_physical_test',
    ];
    foreach ($tables as $suffix => $sourceTable) {
        $backupTable = BACKUP_PREFIX . $suffix;
        $pdo->exec("CREATE TABLE IF NOT EXISTS {$backupTable} LIKE {$sourceTable}");
    }
    $expectedExisting = (int)scalar(
        $pdo,
        "SELECT SUM(n) FROM (" . implode(' UNION ALL ', array_map(function ($suffix) {
            return 'SELECT COUNT(*) n FROM ' . BACKUP_PREFIX . $suffix;
        }, array_keys($tables))) . ') x'
    );
    if ($expectedExisting > 0) {
        assertScopedBackupCounts($pdo);
        return;
    }

    $currentIn = placeholders(count($wrong['current_ids']));
    $legacyIn = placeholders(count($wrong['legacy_uuids']));
    $valueIn = placeholders(count($wrong['value_ids']));
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "test_result SELECT * FROM tn_test_result WHERE id IN ({$currentIn})");
    $stmt->execute($wrong['current_ids']);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "test_value SELECT * FROM tn_test_value WHERE id IN ({$valueIn})");
    $stmt->execute($wrong['value_ids']);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "test_result_value SELECT * FROM tn_test_result_value WHERE result_id IN ({$currentIn})");
    $stmt->execute($wrong['current_ids']);
    $params = array_merge($wrong['current_ids'], $wrong['legacy_uuids']);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "manage_staff SELECT * FROM tn_manage_staff WHERE (type=10 AND outer_uuid IN ({$currentIn})) OR (type=1 AND outer_uuid IN ({$legacyIn}))");
    $stmt->execute($params);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "test SELECT * FROM tn_test WHERE uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "staff_test SELECT * FROM tn_staff_test WHERE test_uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
    $stmt = $pdo->prepare('INSERT INTO ' . BACKUP_PREFIX . "staff_physical_test SELECT * FROM tn_staff_physical_test WHERE test_uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
    assertScopedBackupCounts($pdo);
}

function assertScopedBackupCounts(PDO $pdo): void
{
    $expected = [
        'test_result' => 4,
        'test_value' => 160,
        'test_result_value' => 160,
        'manage_staff' => 40,
        'test' => 4,
        'staff_test' => 160,
        'staff_physical_test' => 0,
    ];
    foreach ($expected as $suffix => $count) {
        $actual = (int)scalar($pdo, 'SELECT COUNT(*) FROM ' . BACKUP_PREFIX . $suffix);
        assertTrue($actual === $count, "Scoped backup {$suffix} count mismatch: {$actual} != {$count}");
    }
}

function stageValue(string $metric, int $sex, int $athleteIndex, int $stageIndex): string
{
    $j = ($athleteIndex - 1) % 5;
    switch ($metric) {
        case '红细胞':
            $base = $sex === 1 ? 4.62 + $j * 0.09 : 4.08 + $j * 0.08;
            $delta = [0.00, -0.07, -0.14, 0.05][$stageIndex];
            return number_format($base + $delta, 2, '.', '');
        case '肌酸激酶':
            $base = $sex === 1 ? 158 + $j * 14 : 122 + $j * 9;
            $delta = [0, 38, 92, -12][$stageIndex];
            return number_format($base + $delta, 0, '.', '');
        case '皮质醇':
            $base = 15.8 + $j * 0.9;
            $delta = [0.0, 2.6, 6.2, -0.8][$stageIndex];
            return number_format($base + $delta, 1, '.', '');
        case '血睾酮':
            if ($sex === 1) {
                $base = 650 - $j * 24;
                $delta = [0, -55, -145, 35][$stageIndex];
                return number_format($base + $delta, 0, '.', '');
            }
            $base = 62 - $j * 2.5;
            $delta = [0.0, -5.0, -13.0, 4.0][$stageIndex];
            return number_format($base + $delta, 1, '.', '');
        case '血红蛋白':
            $base = $sex === 1 ? 149 - $j * 1.6 : 134 - $j * 1.4;
            $delta = [0.0, -2.0, -5.0, 1.0][$stageIndex];
            return number_format($base + $delta, 1, '.', '');
        case '血尿素':
            $base = 5.0 + ($j % 3) * 0.3;
            $delta = [0.1, 0.8, 1.8, -0.2][$stageIndex];
            return number_format($base + $delta, 1, '.', '');
    }
    throw new RuntimeException("Unknown metric: {$metric}");
}

function categoryJson(array $context): string
{
    $norms = [];
    foreach (array_keys(expectedMetricSignatures()) as $name) {
        $metric = $context['metrics'][$name];
        $norms[] = [
            'uuid' => $metric['uuid'],
            'en_name' => $metric['en_name'],
            'unit_uuid' => $metric['unit_uuid'],
            'unit_name' => $metric['unit_name'],
            'value_type' => (int)$metric['value_type'],
            'male_symbol' => (int)$metric['male_symbol'],
            'male_lower_limit' => (float)$metric['male_lower_limit'],
            'male_upper_limit' => (float)$metric['male_upper_limit'],
            'female_symbol' => (int)$metric['female_symbol'],
            'female_lower_limit' => (float)$metric['female_lower_limit'],
            'female_upper_limit' => (float)$metric['female_upper_limit'],
            'order_no' => 1,
            'pinyin' => $metric['pinyin'],
            'edit_flag' => (int)$metric['edit_flag'],
            'name' => $metric['cn_name'],
            'showCheckbox' => true,
        ];
    }
    return json_encode([[
        'uuid' => $context['category_uuid'],
        'name' => CATEGORY_NAME,
        'builtin' => 0,
        'edit_flag' => 1,
        'norms' => $norms,
        'showCheckbox' => true,
    ]], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function deleteWrong(PDO $pdo, array $wrong): void
{
    $currentIn = placeholders(count($wrong['current_ids']));
    $legacyIn = placeholders(count($wrong['legacy_uuids']));
    $valueIn = placeholders(count($wrong['value_ids']));
    $stmt = $pdo->prepare("DELETE FROM tn_manage_staff WHERE (type=10 AND outer_uuid IN ({$currentIn})) OR (type=1 AND outer_uuid IN ({$legacyIn}))");
    $stmt->execute(array_merge($wrong['current_ids'], $wrong['legacy_uuids']));
    $stmt = $pdo->prepare("DELETE FROM tn_test_result_value WHERE result_id IN ({$currentIn})");
    $stmt->execute($wrong['current_ids']);
    $stmt = $pdo->prepare("DELETE FROM tn_test_value WHERE id IN ({$valueIn})");
    $stmt->execute($wrong['value_ids']);
    $stmt = $pdo->prepare("DELETE FROM tn_test_result WHERE id IN ({$currentIn})");
    $stmt->execute($wrong['current_ids']);
    $stmt = $pdo->prepare("DELETE FROM tn_staff_physical_test WHERE test_uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
    $stmt = $pdo->prepare("DELETE FROM tn_staff_test WHERE test_uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
    $stmt = $pdo->prepare("DELETE FROM tn_test WHERE uuid IN ({$legacyIn})");
    $stmt->execute($wrong['legacy_uuids']);
}

function insertCorrect(PDO $pdo, array $context): void
{
    $stages = [
        ['date' => '2026-05-15', 'title' => '第1阶段基线生化机能测试', 'legacy_uuid' => 'F2605150-0001-4000-8000-000000000001'],
        ['date' => '2026-06-18', 'title' => '第2阶段负荷适应生化机能测试', 'legacy_uuid' => 'F2606180-0002-4000-8000-000000000002'],
        ['date' => '2026-07-16', 'title' => '第3阶段强化训练生化机能测试', 'legacy_uuid' => 'F2607160-0003-4000-8000-000000000003'],
        ['date' => '2026-08-13', 'title' => '第4阶段恢复评估生化机能测试', 'legacy_uuid' => 'F2608130-0004-4000-8000-000000000004'],
    ];
    $metricNames = array_keys(expectedMetricSignatures());
    $normUuids = [];
    $normStruct = [];
    foreach ($metricNames as $name) {
        $normUuids[] = $context['metrics'][$name]['uuid'];
        $normStruct[] = [$context['category_uuid'], $context['metrics'][$name]['uuid']];
    }
    $staffUuids = array_column($context['athletes'], 'uuid');
    $staffCsv = implode(',', $staffUuids);
    $normCsv = implode(',', $normUuids);
    $normStructJson = json_encode($normStruct, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    $cateNormJson = categoryJson($context);

    $insertResult = $pdo->prepare(
        'INSERT INTO tn_test_result
         (type,title,department_uuid,staff_uuid_str,norm_uuid_str,test_date,norm_struct,test_goal,address_uuid,evaluate,create_time,create_by)
         VALUES (1,?,?,?,?,?,?,?,?,?,?,?)'
    );
    $insertValue = $pdo->prepare(
        'INSERT INTO tn_test_value
         (staff_uuid,norm_uuid,norm_value,test_date,note,is_del,status,create_time,create_by)
         VALUES (?,?,?,?,?,0,0,?,?)'
    );
    $insertLink = $pdo->prepare(
        'INSERT INTO tn_test_result_value (result_id,value_id,is_del,create_time,create_by) VALUES (?,?,0,?,?)'
    );
    $insertPermission = $pdo->prepare(
        'INSERT INTO tn_manage_staff (outer_uuid,type,department_uuid,staff_uuid) VALUES (?,?,?,?)'
    );
    $insertLegacy = $pdo->prepare(
        '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)
         VALUES (?,?,?,?,?,10,6,0,?,?,7,?,?,?,?,?,NULL,NULL)'
    );
    $insertStaffTest = $pdo->prepare(
        'INSERT INTO tn_staff_test
         (test_uuid,staff_uuid,cate_uuid,norm_uuid,norm_value,json_value,value_type,create_time,create_by)
         VALUES (?,?,?,?,?,NULL,?,?,?)'
    );

    foreach ($stages as $stageIndex => $stage) {
        $title = NEW_MARKER . ' 演示运动队' . $stage['title'];
        $goal = '跟踪阶段训练中造血、肌损伤、内分泌与代谢恢复状态';
        $evaluate = ['基线整体平稳。', '负荷增加后部分疲劳指标轻度上升。', '强化期肌酸激酶和皮质醇升高，睾酮下降，建议加强恢复。', '恢复期主要指标回落，整体状态改善。'][$stageIndex];
        $insertResult->execute([
            $title,
            $context['team_uuid'],
            $staffCsv,
            $normCsv,
            $stage['date'],
            $normStructJson,
            $goal,
            '',
            '<p>' . $evaluate . '</p>',
            FIXED_CREATE_TIME,
            NEW_CREATOR,
        ]);
        $resultId = (int)$pdo->lastInsertId();

        foreach ($context['athletes'] as $athleteIndexZero => $athlete) {
            $athleteIndex = $athleteIndexZero + 1;
            $insertPermission->execute([$resultId, 10, $context['team_uuid'], $athlete['uuid']]);
            foreach ($metricNames as $name) {
                $metric = $context['metrics'][$name];
                $value = stageValue($name, (int)$athlete['sex'], $athleteIndex, $stageIndex);
                $insertValue->execute([
                    $athlete['uuid'],
                    $metric['uuid'],
                    $value,
                    $stage['date'],
                    NEW_DEMO,
                    FIXED_CREATE_TIME,
                    NEW_CREATOR,
                ]);
                $valueId = (int)$pdo->lastInsertId();
                $insertLink->execute([$resultId, $valueId, FIXED_CREATE_TIME, NEW_CREATOR]);
            }
        }

        $insertLegacy->execute([
            $stage['legacy_uuid'],
            $title,
            $stage['date'],
            $context['team_uuid'],
            $staffCsv,
            '',
            '',
            NEW_DEMO,
            $evaluate,
            $cateNormJson,
            FIXED_CREATE_TIME,
            NEW_CREATOR,
        ]);
        foreach ($context['athletes'] as $athleteIndexZero => $athlete) {
            $athleteIndex = $athleteIndexZero + 1;
            $insertPermission->execute([$stage['legacy_uuid'], 1, $context['team_uuid'], $athlete['uuid']]);
            foreach ($metricNames as $name) {
                $metric = $context['metrics'][$name];
                $value = stageValue($name, (int)$athlete['sex'], $athleteIndex, $stageIndex);
                $insertStaffTest->execute([
                    $stage['legacy_uuid'],
                    $athlete['uuid'],
                    $context['category_uuid'],
                    $metric['uuid'],
                    $value,
                    (int)$metric['value_type'],
                    FIXED_CREATE_TIME,
                    NEW_CREATOR,
                ]);
            }
        }
    }
}

function verifyCorrect(PDO $pdo, array $context): array
{
    $counts = newCounts($pdo, $context);
    $expected = [
        'current_results' => 4,
        'current_links' => 240,
        'current_values' => 240,
        'current_permissions' => 40,
        'legacy_tests' => 4,
        'legacy_values' => 240,
        'legacy_permissions' => 40,
    ];
    assertTrue($counts === $expected, '纠正批次计数不符合预期：' . json_encode($counts, JSON_UNESCAPED_UNICODE));

    $row = fetchOne(
        $pdo,
        "SELECT COUNT(*) rows_count,COUNT(DISTINCT v.staff_uuid) athletes,
                COUNT(DISTINCT v.norm_uuid) metrics,MIN(v.test_date) min_date,MAX(v.test_date) max_date,
                SUM(v.norm_value='' OR v.norm_value IS NULL) empty_values
         FROM tn_test_value v
         WHERE v.create_by=? AND v.is_del=0",
        [NEW_CREATOR]
    );
    assertTrue((int)$row['rows_count'] === 240, '当前链数据值不是 240。');
    assertTrue((int)$row['athletes'] === 10, '当前链未覆盖 10 名运动员。');
    assertTrue((int)$row['metrics'] === 6, '当前链未覆盖 6 个指标。');
    assertTrue($row['min_date'] === '2026-05-15' && $row['max_date'] === '2026-08-13', '当前链日期范围错误。');
    assertTrue((int)$row['empty_values'] === 0, '当前链存在空值。');

    $metricRows = fetchAll(
        $pdo,
        "SELECT n.cn_name,u.cn_name unit_name,COUNT(*) rows_count,
                MIN(CAST(v.norm_value AS DECIMAL(18,4))) min_value,
                MAX(CAST(v.norm_value AS DECIMAL(18,4))) max_value
         FROM tn_test_value v
         JOIN tn_norm n ON n.uuid=v.norm_uuid
         LEFT JOIN tn_unit u ON u.uuid=n.unit_uuid
         WHERE v.create_by=? AND v.is_del=0
         GROUP BY n.uuid,n.cn_name,u.cn_name ORDER BY n.cn_name",
        [NEW_CREATOR]
    );
    assertMetricSemantics(array_map(function ($row) use ($context) {
        return array_merge($context['metrics'][$row['cn_name']], $row);
    }, $metricRows), 'GSTYJ 纠正批次');
    foreach ($metricRows as $metricRow) {
        assertTrue((int)$metricRow['rows_count'] === 40, "{$metricRow['cn_name']} 应有 40 个值。");
    }

    $currentIds = array_map('intval', array_column(fetchAll($pdo, 'SELECT id FROM tn_test_result WHERE title LIKE ? AND create_by=?', [NEW_MARKER . '%', NEW_CREATOR]), 'id'));
    $legacyUuids = array_column(fetchAll($pdo, 'SELECT uuid FROM tn_test WHERE title LIKE ? AND create_by=?', [NEW_MARKER . '%', NEW_CREATOR]), 'uuid');
    $currentIn = placeholders(count($currentIds));
    $legacyIn = placeholders(count($legacyUuids));
    $orphans = [
        'current_result_value' => (int)scalar($pdo, "SELECT 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.result_id IN ({$currentIn}) AND (r.id IS NULL OR v.id IS NULL)", $currentIds),
        'current_permission' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff m LEFT JOIN tn_test_result r ON CAST(r.id AS CHAR)=m.outer_uuid LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE m.type=10 AND m.outer_uuid IN ({$currentIn}) AND (r.id IS NULL OR s.uuid IS NULL)", $currentIds),
        'legacy_value' => (int)scalar($pdo, "SELECT 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 LEFT JOIN tn_cate c ON c.uuid=st.cate_uuid WHERE st.test_uuid IN ({$legacyIn}) AND (t.uuid IS NULL OR s.uuid IS NULL OR n.uuid IS NULL OR c.uuid IS NULL)", $legacyUuids),
        'legacy_permission' => (int)scalar($pdo, "SELECT COUNT(*) FROM tn_manage_staff m LEFT JOIN tn_test t ON t.uuid=m.outer_uuid LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE m.type=1 AND m.outer_uuid IN ({$legacyIn}) AND (t.uuid IS NULL OR s.uuid IS NULL)", $legacyUuids),
    ];
    foreach ($orphans as $name => $count) {
        assertTrue($count === 0, "{$name} orphan count is {$count}");
    }

    $oldRemaining = [
        'current_results' => (int)scalar($pdo, 'SELECT COUNT(*) FROM tn_test_result WHERE title LIKE ? AND create_by=?', [OLD_MARKER . '%', OLD_CREATOR]),
        'current_values' => (int)scalar($pdo, 'SELECT COUNT(*) FROM tn_test_value WHERE create_by=?', [OLD_CREATOR]),
        'legacy_tests' => (int)scalar($pdo, 'SELECT COUNT(*) FROM tn_test WHERE title LIKE ? AND create_by=?', [OLD_MARKER . '%', OLD_CREATOR]),
        'legacy_values' => (int)scalar($pdo, 'SELECT COUNT(*) FROM tn_staff_test WHERE create_by=?', [OLD_CREATOR]),
    ];
    foreach ($oldRemaining as $name => $count) {
        assertTrue($count === 0, "Old wrong data remains in {$name}: {$count}");
    }

    return ['counts' => $counts, 'orphans' => $orphans, 'metrics' => $metricRows, 'old_remaining' => $oldRemaining];
}

function deleteCorrect(PDO $pdo, array $context): void
{
    $currentIds = array_map('intval', array_column(fetchAll($pdo, 'SELECT id FROM tn_test_result WHERE title LIKE ? AND create_by=? AND department_uuid=?', [NEW_MARKER . '%', NEW_CREATOR, $context['team_uuid']]), 'id'));
    $legacyUuids = array_column(fetchAll($pdo, 'SELECT uuid FROM tn_test WHERE title LIKE ? AND create_by=? AND department_uuid=?', [NEW_MARKER . '%', NEW_CREATOR, $context['team_uuid']]), 'uuid');
    if ($currentIds) {
        $in = placeholders(count($currentIds));
        $valueIds = array_map('intval', array_column(fetchAll($pdo, "SELECT DISTINCT value_id FROM tn_test_result_value WHERE result_id IN ({$in})", $currentIds), 'value_id'));
        $stmt = $pdo->prepare("DELETE FROM tn_manage_staff WHERE type=10 AND outer_uuid IN ({$in})");
        $stmt->execute($currentIds);
        $stmt = $pdo->prepare("DELETE FROM tn_test_result_value WHERE result_id IN ({$in})");
        $stmt->execute($currentIds);
        if ($valueIds) {
            $vin = placeholders(count($valueIds));
            $outside = (int)scalar($pdo, "SELECT COUNT(*) FROM tn_test_result_value WHERE value_id IN ({$vin})", $valueIds);
            assertTrue($outside === 0, '纠正值仍被其他结果引用，禁止回滚。');
            $stmt = $pdo->prepare("DELETE FROM tn_test_value WHERE id IN ({$vin})");
            $stmt->execute($valueIds);
        }
        $stmt = $pdo->prepare("DELETE FROM tn_test_result WHERE id IN ({$in})");
        $stmt->execute($currentIds);
    }
    if ($legacyUuids) {
        $in = placeholders(count($legacyUuids));
        $stmt = $pdo->prepare("DELETE FROM tn_manage_staff WHERE type=1 AND outer_uuid IN ({$in})");
        $stmt->execute($legacyUuids);
        $stmt = $pdo->prepare("DELETE FROM tn_staff_physical_test WHERE test_uuid IN ({$in})");
        $stmt->execute($legacyUuids);
        $stmt = $pdo->prepare("DELETE FROM tn_staff_test WHERE test_uuid IN ({$in})");
        $stmt->execute($legacyUuids);
        $stmt = $pdo->prepare("DELETE FROM tn_test WHERE uuid IN ({$in})");
        $stmt->execute($legacyUuids);
    }
}

function restoreScopedBackup(PDO $pdo): void
{
    assertScopedBackupCounts($pdo);
    foreach (['test_result', 'test_value', 'test_result_value', 'manage_staff', 'test', 'staff_test', 'staff_physical_test'] as $suffix) {
        $pdo->exec('INSERT INTO tn_' . $suffix . ' SELECT * FROM ' . BACKUP_PREFIX . $suffix);
    }
}

try {
    $sourceConfig = dbConfig(SOURCE_DB_CONFIG);
    $targetConfig = dbConfig(TARGET_DB_CONFIG);
    $source = pdoFromConfig($sourceConfig);
    $target = pdoFromConfig($targetConfig);
    assertTrue(redisIsDisabled(), '目标 Redis 开关已改为开启，需先单独审核精确缓存键。');
    $context = discover($source, $target);
    $wrong = wrongClosure($target, $context);
    $new = newCounts($target, $context);

    if ($mode === 'dry-run') {
        printPlan($context, $wrong, $new);
        assertTrue($wrong['safe'], '错误批次无法被预期关系闭包精确识别。');
        assertTrue(array_sum($new) === 0, '纠正批次已存在部分数据，禁止重复执行。');
        echo "DRY-RUN OK: no writes performed.\n";
        exit(0);
    }

    if ($mode === 'verify') {
        $verification = verifyCorrect($target, $context);
        echo json_encode($verification, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n";
        echo "VERIFY OK\n";
        exit(0);
    }

    if ($mode === 'rollback') {
        assertTrue(array_sum($new) === 808, '纠正批次数量不完整，禁止盲目回滚。');
        $target->beginTransaction();
        try {
            deleteCorrect($target, $context);
            restoreScopedBackup($target);
            $target->commit();
        } catch (Throwable $e) {
            $target->rollBack();
            throw $e;
        }
        $restored = wrongClosure($target, $context);
        assertTrue($restored['safe'], '回滚后旧批次未完整恢复。');
        assertTrue(array_sum(newCounts($target, $context)) === 0, '回滚后纠正批次仍有残留。');
        echo "ROLLBACK OK\n";
        exit(0);
    }

    printPlan($context, $wrong, $new);
    assertTrue($wrong['safe'], '错误批次无法被预期关系闭包精确识别。');
    assertTrue(array_sum($new) === 0, '纠正批次已存在部分数据，禁止重复执行。');

    if ($mode === 'apply') {
        $sqlPath = createFullBackup($targetConfig, [
            'task' => 'functional-status-demo-correction',
            'created_at' => date('c'),
            'old_closure_counts' => $wrong['counts'],
            'planned_current' => ['results' => 4, 'values' => 240, 'links' => 240, 'permissions' => 40],
            'planned_legacy' => ['tests' => 4, 'values' => 240, 'permissions' => 40],
            'credentials_stored' => false,
        ]);
        createScopedBackups($target, $wrong);
        echo "Backup ready: {$sqlPath}\n";
    }

    $target->beginTransaction();
    try {
        deleteWrong($target, $wrong);
        insertCorrect($target, $context);
        $verification = verifyCorrect($target, $context);
        if ($mode === 'simulate') {
            $target->rollBack();
            $restored = wrongClosure($target, $context);
            assertTrue($restored['safe'], '模拟回滚后旧批次未完整恢复。');
            assertTrue(array_sum(newCounts($target, $context)) === 0, '模拟回滚后纠正批次有残留。');
            echo "SIMULATE OK: full write verified and rolled back.\n";
            exit(0);
        }
        $target->commit();
        echo json_encode($verification, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n";
        echo "APPLY OK\n";
    } catch (Throwable $e) {
        if ($target->inTransaction()) {
            $target->rollBack();
        }
        throw $e;
    }
} catch (Throwable $e) {
    fail($e->getMessage(), 10);
}