water/server/scripts/migrate_device_operation_sources.php

<?php

/**
 * php scripts/migrate_device_operation_sources.php --apply --legacy-schema=api
 * 不带 --apply 只检查。旧库可选，只回填唯一 SN、类型和 legacy_id 一致的设备成本。
 * 运行时的报表不依赖旧库；代理、商户比例始终读取新系统已有档案。
 */
require dirname(__DIR__) . '/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);
\Webman\ThinkOrm\ThinkOrm::start(null);

use support\think\Db;

$options = getopt('', ['apply', 'legacy-schema:']);
$apply = array_key_exists('apply', $options);
$schema = $options['legacy-schema'] ?? '';
if ($schema !== '' && !preg_match('/^[a-zA-Z0-9_]+$/D', $schema)) {
    throw new InvalidArgumentException('Invalid legacy schema name');
}
$migrations = [
    '20260908_use_device_operation_sources.sql',
    '20260908_add_device_product_dictionary.sql',
];
foreach ($migrations as $migration) {
    if ($apply) {
        $sql = file_get_contents(dirname(__DIR__) . '/plugin/saiadmin/db/' . $migration);
        foreach (explode(';', $sql) as $statement) {
            if (trim($statement) !== '') {
                Db::execute($statement);
            }
        }
    }
    echo ($apply ? 'Applied: ' : 'Planned: ') . $migration . "\n";
}
if ($schema !== '') {
    // 不用机器码、名称或顺序猜对应关系；有重复 SN 时不导入。
    $legacy = "(SELECT goods_sn,goods_type,MIN(id) AS id,MIN(cost_price) AS cost_price
        FROM `$schema`.xs_market_goods WHERE is_delete=0
        GROUP BY goods_sn,goods_type HAVING COUNT(*)=1 AND MIN(cost_price)>=0) legacy";
    $join = "FROM sa_device d JOIN $legacy
        ON legacy.goods_sn=d.goods_sn AND legacy.goods_type=d.goods_type
        WHERE d.delete_time IS NULL AND (d.legacy_id IS NULL OR d.legacy_id=legacy.id)";
    $hasCost = Db::query("SELECT COUNT(*) AS n FROM information_schema.columns
        WHERE table_schema=DATABASE() AND table_name='sa_device' AND column_name='cost_price'")[0]['n'];
    $missingCost = $hasCost ? ' AND d.cost_price IS NULL' : '';
    $matches = (int) Db::query("SELECT COUNT(*) AS n $join $missingCost")[0]['n'];
    echo "Matching devices without cost: $matches\n";
    if ($apply && $matches > 0) {
        $updated = Db::execute("UPDATE sa_device d JOIN $legacy
            ON legacy.goods_sn=d.goods_sn AND legacy.goods_type=d.goods_type
            SET d.cost_price=legacy.cost_price
            WHERE d.delete_time IS NULL AND d.cost_price IS NULL
            AND (d.legacy_id IS NULL OR d.legacy_id=legacy.id)");
        echo "Imported device costs: $updated\n";
    }
}
if ($apply) {
    \plugin\saiadmin\app\cache\DictCache::clear();
    \plugin\saiadmin\app\cache\UserAuthCache::clear();
    \plugin\saiadmin\app\cache\UserMenuCache::clearMenuCache();
}