<?php
/** 只读表清理盘点：仅输出结构统计、代码引用路径及外键，不输出业务数据或凭据。 */
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;
$root = dirname(__DIR__);
$sources = [];
foreach (['app','config','plugin/saiadmin/app','plugin/saiadmin/config','plugin/saipackage/app','plugin/saipackage/config'] as $directory) {
    if (!is_dir($root.'/'.$directory)) continue;
    foreach (new RecursiveIteratorIterator(new RecursiveDirectoryIterator($root.'/'.$directory,FilesystemIterator::SKIP_DOTS)) as $file) {
        if ($file->getExtension() === 'php') $sources[str_replace('\\','/',substr($file->getPathname(),strlen($root)+1))] = file_get_contents($file->getPathname());
    }
}
$tables = Db::query('SELECT TABLE_NAME AS name,TABLE_ROWS AS estimated_rows,DATA_LENGTH+INDEX_LENGTH AS bytes FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() ORDER BY TABLE_NAME');
foreach ($tables as &$table) {
    $full = $table['name'];
    $short = preg_replace('/^sa_/','',$full);
    $pattern = '/(?<![a-zA-Z0-9_])(?:'.preg_quote($full,'/').'|'.preg_quote($short,'/').')(?![a-zA-Z0-9_])/i';
    $table['reference_files'] = [];
    foreach ($sources as $file=>$content) if (preg_match($pattern,$content)) $table['reference_files'][] = $file;
    $table['water_dynamic_tables'] = str_starts_with($full,'sa_water_');
    if (!$table['reference_files'] && !$table['water_dynamic_tables']) $table['exact_rows'] = Db::table($full)->count();
}
unset($table);
echo json_encode(['database'=>Db::query('SELECT DATABASE() AS name')[0]['name'],
    'note'=>'静态字面引用仅供筛选线索，无引用不等于无使用；需检查动态ORM、外部项目及历史数据。',
    'tables'=>$tables,
    'foreign_keys'=>Db::query('SELECT TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL'),
],JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE).PHP_EOL;