<?php
$root = __DIR__;
$cfg = json_decode(file_get_contents($root . '/core/config.json'), true);
$pdo = new PDO('mysql:host=' . $cfg['db_host'] . ';dbname=' . $cfg['db_name'] . ';charset=utf8mb4', $cfg['db_user'], $cfg['db_pass']);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$used = [
    'all_pass_pages', 'charts_pages', 'cms_pages', 'cms_settings', 'daily_results', 'dpboss_weekly_data',
    'free_game', 'games', 'media', 'menu_items', 'pages', 'plugins', 'predefined_panels', 'results',
    'settings', 'themes', 'users', 'weekly_chart_guesses'
];
$current = [];
foreach ($pdo->query('SHOW TABLES') as $row) {
    $current[] = (string) reset($row);
}
sort($current);
$unused = array_values(array_diff($current, $used));
$backupDir = $root . '/backups';
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0775, true);
}
$timestamp = date('Ymd-His');
$backupPath = $backupDir . '/unused-tables-backup-' . $timestamp . '.json';
$report = [
    'generated_at' => date('c'),
    'database' => $cfg['db_name'],
    'tables' => []
];
foreach ($unused as $table) {
    $createStmt = $pdo->query('SHOW CREATE TABLE `' . str_replace('`', '``', $table) . '`')->fetch(PDO::FETCH_ASSOC);
    $rows = $pdo->query('SELECT * FROM `' . str_replace('`', '``', $table) . '`')->fetchAll(PDO::FETCH_ASSOC);
    $report['tables'][$table] = [
        'row_count' => count($rows),
        'create_sql' => $createStmt['Create Table'] ?? '',
        'rows' => $rows,
    ];
}
file_put_contents($backupPath, json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), LOCK_EX);
foreach ($unused as $table) {
    $pdo->exec('DROP TABLE IF EXISTS `' . str_replace('`', '``', $table) . '`');
    echo 'Dropped: ' . $table . PHP_EOL;
}
echo 'Backup: ' . $backupPath . PHP_EOL;
