<?php
session_start();
if (!isset($_SESSION['admin_id'])) {
    header('Location: login.php');
    exit;
}
require_once __DIR__ . '/../../config/db.php';

$database = new Database();
$db = $database->getConnection();

$message = '';
$action = $_GET['action'] ?? '';

if ($action === 'test') {
    if ($db) {
        $message = 'Database connection successful!';
    } else {
        $message = 'Database connection failed.';
    }
}

if ($action === 'backup') {
    $tables = [];
    $result = $db->query("SHOW TABLES");
    while ($row = $result->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
    }

    $output = "-- Myriadonica Database Backup\n";
    $output .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
    $output .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

    foreach ($tables as $table) {
        // Create table
        $stmt = $db->query("SHOW CREATE TABLE `$table`");
        $row = $stmt->fetch(PDO::FETCH_NUM);
        $output .= "\n\n" . $row[1] . ";\n\n";

        // Table data
        $stmt = $db->query("SELECT * FROM `$table`");
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $output .= "INSERT INTO `$table` VALUES(";
            $vals = [];
            foreach ($row as $val) {
                if (is_null($val)) {
                    $vals[] = "NULL";
                } else {
                    $vals[] = $db->quote($val);
                }
            }
            $output .= implode(",", $vals) . ");\n";
        }
    }

    $output .= "\n\nSET FOREIGN_KEY_CHECKS=1;\n";

    $filename = "backup_" . date('Y-m-d_H-i-s') . ".sql";
    header('Content-Type: application/sql');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    echo $output;
    exit;
}

if ($action === 'restore' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['backup_file']) && $_FILES['backup_file']['error'] === UPLOAD_ERR_OK) {
        $sql = file_get_contents($_FILES['backup_file']['tmp_name']);
        try {
            $db->exec("SET FOREIGN_KEY_CHECKS=0; " . $sql . " SET FOREIGN_KEY_CHECKS=1;");
            $message = 'База данных успешно восстановлена из бэкапа.';
        } catch (PDOException $e) {
            $message = 'Ошибка восстановления: ' . $e->getMessage();
        }
    } else {
        $message = 'Файл бэкапа не выбран.';
    }
}

if ($action === 'import_stats' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['stats_file']) && $_FILES['stats_file']['error'] === UPLOAD_ERR_OK) {
        $sqlContent = file_get_contents($_FILES['stats_file']['tmp_name']);
        
        // Remove comments
        $sqlContent = preg_replace('/--.*$/m', '', $sqlContent);
        $sqlContent = preg_replace('/\/\*.*?\*\//s', '', $sqlContent);
        
        // Basic sanitization and adaptation for new schema
        $replacements = [
            '/`online_visitors`/' => '`visitors`',
            '/\bonline_visitors\b/i' => 'visitors',
            '/`visit_time`/' => '`timestamp`',
            '/\bvisit_time\b/i' => 'timestamp',
            '/`visit_id`/' => '`id`',
            '/\bvisit_id\b/i' => 'id',
        ];
        $sqlContent = preg_replace(array_keys($replacements), array_values($replacements), $sqlContent);
        
        // Split into individual queries more robustly
        // This is still not a full parser but better than a simple explode
        $sqlContent = str_replace("\r\n", "\n", $sqlContent);
        $queries = preg_split("/;(?=(?:[^'\"`]*['\"`][^'\"`]*['\"`])*[^'\"`]*$)/", $sqlContent);
        
        $successCount = 0;
        $errorCount = 0;
        $skipCount = 0;
        $insertCount = 0;
        $lastError = '';
        $debugInfo = [];
        $totalFound = 0;
        
        foreach ($queries as $query) {
            $query = trim($query);
            if (empty($query)) continue;
            $totalFound++;
            
            // Identify query type
            $isInsert = preg_match('/^\s*INSERT\s+INTO\s+/i', $query);
            $isDestructive = preg_match('/^\s*(DROP|CREATE|TRUNCATE|DELETE|REPLACE|USE|DATABASE)\s+/i', $query);
            
            // Specifically check for visitors/online_visitors to see if our rename worked
            $targetsVisitors = stripos($query, 'visitors') !== false;
            
            // Skip destructive table statements to protect existing schema
            // EXCEPT if it's an INSERT/REPLACE into our target table
            if ($isDestructive && !$targetsVisitors) {
                $skipCount++;
                continue;
            }
            
            // If it's a DROP/CREATE for visitors, we skip to be safe
            if (preg_match('/^\s*(DROP|CREATE)\s+TABLE\s+/i', $query) && $targetsVisitors) {
                $skipCount++;
                continue;
            }
            
            try {
                $db->exec($query);
                $successCount++;
                if ($isInsert) $insertCount++;
                if (count($debugInfo) < 5) $debugInfo[] = "OK (" . ($isInsert ? 'INS' : 'OTH') . "): " . substr($query, 0, 80) . "...";
            } catch (PDOException $e) {
                $errorCount++;
                $lastError = $e->getMessage();
                if (count($debugInfo) < 5) $debugInfo[] = "ERR: " . substr($query, 0, 80) . "... -> " . $lastError;
            }
        }
        
        $message = "Результат импорта:<br>";
        $message .= "- Всего найдено команд: <b>$totalFound</b><br>";
        $message .= "- Выполнено успешно: <b>$successCount</b> (в т.ч. <b>$insertCount</b> вставок)<br>";
        $message .= "- Пропущено из соображений безопасности: <b>$skipCount</b><br>";
        $message .= "- Ошибок: <b>$errorCount</b><br>";
        
        if ($errorCount > 0) {
            $message .= "<br>Последняя ошибка: <span style='color:var(--error-color)'>" . htmlspecialchars($lastError) . "</span>";
        }
        
        if (!empty($debugInfo)) {
            $message .= "<br><br>Журнал (первые 5):<br><small style='display:block; background:rgba(0,0,0,0.2); padding:5px; border-radius:4px;'>" . implode("<br>", array_map('htmlspecialchars', $debugInfo)) . "</small>";
        }
    } else {
        $message = 'Файл не выбран или произошла ошибка при загрузке.';
    }
}
?>
<!DOCTYPE html>
<html lang="ru">
<head>
    <meta charset="UTF-8">
    <title>Database Tools - Admin</title>
    <link rel="stylesheet" href="css/admin.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
</head>
<body>
    <div class="sidebar">
        <div class="sidebar-header">MYRIADONICA</div>
        <a href="index.php" class="nav-item"><i class="fas fa-home"></i> Панель</a>
        <a href="schedule.php" class="nav-item"><i class="fas fa-calendar-alt"></i> Расписание</a>
        <a href="cdn.php" class="nav-item"><i class="fas fa-server"></i> CDN Серверы</a>
        <a href="info_blocks.php" class="nav-item"><i class="fas fa-info-circle"></i> Инфо-блоки</a>
        <a href="settings.php" class="nav-item"><i class="fas fa-cog"></i> Настройки</a>
        <a href="telegram.php" class="nav-item"><i class="fab fa-telegram"></i> Телеграм</a>
        <a href="database.php" class="nav-item active"><i class="fas fa-database"></i> База данных</a>
        <a href="logout.php" class="nav-item" style="margin-top: auto;"><i class="fas fa-sign-out-alt"></i> Выход</a>
    </div>

    <div class="main-content">
        <div class="header">
            <h1>Инструменты базы данных</h1>
        </div>

        <?php if ($message): ?>
            <div class="card" style="color: var(--secondary-color);"><?php echo $message; ?></div>
        <?php endif; ?>

        <div class="card">
            <h3>Действия</h3>
            <div style="display: flex; gap: 10px; flex-wrap: wrap;">
                <a href="database.php?action=test" class="btn btn-secondary"><i class="fas fa-plug"></i> Проверить соединение</a>
                <a href="database.php?action=backup" class="btn btn-primary"><i class="fas fa-download"></i> Скачать резервную копию (SQL)</a>
            </div>
        </div>

        <div class="card">
            <h3>Восстановление из бэкапа</h3>
            <form action="database.php?action=restore" method="POST" enctype="multipart/form-data">
                <div class="form-group">
                    <label>Выберите файл бэкапа (.sql)</label>
                    <input type="file" name="backup_file" accept=".sql" required>
                    <p style="font-size: 0.8em; color: var(--error-color); margin-top: 5px;">
                        <i class="fas fa-exclamation-triangle"></i> Внимание: Восстановление перезапишет существующие данные!
                    </p>
                </div>
                <button type="submit" class="btn btn-primary" style="background-color: var(--accent-color);" onclick="return confirm('Вы уверены? Текущие данные будут заменены данными из файла.')">
                    <i class="fas fa-upload"></i> Восстановить базу
                </button>
            </form>
        </div>

        <div class="card">
            <h3>Импорт статистики</h3>
            <form action="database.php?action=import_stats" method="POST" enctype="multipart/form-data">
                <div class="form-group">
                    <label>Загрузить online_visitors.sql</label>
                    <input type="file" name="stats_file">
                </div>
                <button type="submit" class="btn btn-primary">Импортировать</button>
            </form>
        </div>
    </div>
</body>
</html>