water/server/scripts/cleanup_unused_water_tables.php

<?php
/** 限定清理4张已确认废弃表。默认只检查；必须提供完整备份目录。 */
declare(strict_types=1);
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;

const TARGETS = ['sa_device_operation_profile','sa_article_banner','sa_article','sa_article_category'];
function fingerprint(array $rows): string {
    $encoded = [];
    foreach ($rows as $row) {
        ksort($row);
        foreach ($row as &$value) if ($value !== null) $value = (string)$value;
        unset($value);
        $encoded[] = json_encode($row,JSON_UNESCAPED_UNICODE|JSON_THROW_ON_ERROR);
    }
    sort($encoded,SORT_STRING);
    return hash('sha256',implode("\n",$encoded));
}
function backupDdl(array $snapshot,string $table,string $target,bool $temporary = false): string {
    $schema = $snapshot['tables'][$table]['schema'] ?? '';
    $prefix = 'CREATE TABLE `'.$table.'`';
    if (!str_starts_with($schema,$prefix)) throw new RuntimeException('Unexpected backup schema for '.$table);
    return 'CREATE '.($temporary ? 'TEMPORARY ' : '').'TABLE `'.$target.'`'.substr($schema,strlen($prefix));
}
function insertRows(string $table,array $rows): void {
    foreach (array_chunk($rows,100) as $chunk) Db::table($table)->insertAll($chunk);
}

$directory = $argv[1] ?? '';
$file = $directory.'/database.json.gz';
$manifestFile = $directory.'/database-manifest.json';
if (!is_file($file) || !is_file($manifestFile)) throw new RuntimeException('Verified full backup directory required');
if (Db::query('SELECT DATABASE() AS name')[0]['name'] !== 'water') throw new RuntimeException('Refusing to run outside water database');
$manifest = json_decode(file_get_contents($manifestFile),true,512,JSON_THROW_ON_ERROR);
if (!hash_equals($manifest['sha256'],hash_file('sha256',$file))) throw new RuntimeException('Backup checksum mismatch');
$snapshot = json_decode(gzdecode(file_get_contents($file)),true,512,JSON_THROW_ON_ERROR);
foreach (TARGETS as $table) {
    if (!isset($snapshot['tables'][$table]) || count($snapshot['tables'][$table]['rows']) !== ($manifest['tables'][$table] ?? -1)) throw new RuntimeException('Missing or inconsistent backup for '.$table);
}
$present = array_column(Db::query('SELECT TABLE_NAME AS name FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()'),'name');
$restore = in_array('--restore',$argv,true);
$apply = in_array('--apply',$argv,true);
if ($restore) {
    // 不覆盖任何同名表；恢复需显式 --restore --apply。
    foreach (TARGETS as $table) if (in_array($table,$present,true)) throw new RuntimeException('Restore refuses existing table: '.$table);
    foreach (TARGETS as $table) {
        if ($apply) {
            Db::execute(backupDdl($snapshot,$table,$table));
            insertRows($table,$snapshot['tables'][$table]['rows']);
            if (fingerprint(Db::table($table)->select()->toArray()) !== fingerprint($snapshot['tables'][$table]['rows'])) throw new RuntimeException('Restore verification failed: '.$table);
        }
        echo ($apply ? 'Restored ' : 'Restore ready ').$table.' rows='.count($snapshot['tables'][$table]['rows'])."\n";
    }
    exit(0);
}

// 删除前每次复核已知运行路径中的静态引用，防止盘点后新增依赖。
$pattern = '/(?<![a-zA-Z0-9_])(?:sa_)?(?:article|article_banner|article_category|device_operation_profile)(?![a-zA-Z0-9_])/i';
foreach (['app','config','plugin/saiadmin/app','plugin/saiadmin/config','plugin/saipackage/app','plugin/saipackage/config'] as $relative) {
    $path = dirname(__DIR__).'/'.$relative;
    if (!is_dir($path)) continue;
    foreach (new RecursiveIteratorIterator(new RecursiveDirectoryIterator($path,FilesystemIterator::SKIP_DOTS)) as $source) {
        if ($source->getExtension()==='php' && preg_match($pattern,file_get_contents($source->getPathname()))) throw new RuntimeException('Runtime reference found: '.$source->getPathname());
    }
}
$dependencies = array_merge(
    Db::query('SELECT REFERENCED_TABLE_NAME AS definition FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA=DATABASE()'),
    Db::query('SELECT VIEW_DEFINITION AS definition FROM information_schema.VIEWS WHERE TABLE_SCHEMA=DATABASE()'),
    Db::query("SELECT CONCAT(EVENT_OBJECT_TABLE,' ',ACTION_STATEMENT) AS definition FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA=DATABASE()"),
    Db::query('SELECT ROUTINE_DEFINITION AS definition FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA=DATABASE()'),
    Db::query('SELECT EVENT_DEFINITION AS definition FROM information_schema.EVENTS WHERE EVENT_SCHEMA=DATABASE()')
);
foreach ($dependencies as $dependency) {
    if ($dependency['definition'] === null) throw new RuntimeException('Cannot inspect a database object definition');
    if (preg_match($pattern,$dependency['definition'])) throw new RuntimeException('Database dependency found; cleanup stopped');
}
$beforeRemaining = [];
foreach ($present as $table) {
    if (!in_array($table,TARGETS,true)) $beforeRemaining[$table] = (int)Db::table($table)->count();
}
foreach (TARGETS as $table) {
    if (!in_array($table,$present,true)) throw new RuntimeException('Target missing before cleanup: '.$table);
    $saved = $snapshot['tables'][$table];
    if (Db::query('SHOW CREATE TABLE `'.$table.'`')[0]['Create Table'] !== $saved['schema'] || fingerprint(Db::table($table)->select()->toArray()) !== fingerprint($saved['rows'])) {
        throw new RuntimeException('Target changed after backup: '.$table);
    }
    // 临时表仅在本连接内存在，用实际建表+回填+比对验证备份可恢复。
    $temporary = '__water_restore_check_'.bin2hex(random_bytes(5));
    Db::execute(backupDdl($snapshot,$table,$temporary,true));
    try {
        insertRows($temporary,$saved['rows']);
        if (fingerprint(Db::table($temporary)->select()->toArray()) !== fingerprint($saved['rows'])) throw new RuntimeException('Backup restore rehearsal failed: '.$table);
    } finally { Db::execute('DROP TEMPORARY TABLE `'.$temporary.'`'); }
    echo 'Verified backup and restore: '.$table.' rows='.count($saved['rows'])."\n";
}
if (!$apply) {
    echo "CHECK ONLY: no persistent tables changed. Add --apply to remove the four reviewed tables.\n";
    exit(0);
}
// 固定白名单，绝不根据动态“无引用”结果扩大删除范围。
Db::execute('DROP TABLE `sa_device_operation_profile`, `sa_article_banner`, `sa_article`, `sa_article_category`');
$after = array_column(Db::query('SELECT TABLE_NAME AS name FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()'),'name');
foreach (TARGETS as $table) if (in_array($table,$after,true)) throw new RuntimeException('Target remains after cleanup: '.$table);
$changedCounts = [];
foreach ($beforeRemaining as $table=>$count) {
    if (!in_array($table,$after,true)) throw new RuntimeException('Unexpected missing table: '.$table);
    $now = (int)Db::table($table)->count();
    if ($now !== $count) $changedCounts[$table] = ['before'=>$count,'after'=>$now];
}
$result = ['database'=>'water','removed'=>TARGETS,'before_tables'=>count($present),'after_tables'=>count($after),
    'backup_sha256'=>$manifest['sha256'],'remaining_row_count_changes'=>$changedCounts,'finished_at'=>date(DATE_ATOM)];
file_put_contents($directory.'/cleanup-result.json',json_encode($result,JSON_PRETTY_PRINT|JSON_THROW_ON_ERROR),LOCK_EX);
echo json_encode($result,JSON_PRETTY_PRINT|JSON_THROW_ON_ERROR)."\n";