Tul xxx Tul

Viewing: database_api.php

<?php
/**
 * ════════════════════════════════════════════════════════════════════
 *  Database Backup / Restore  —  API Endpoint
 * ════════════════════════════════════════════════════════════════════
 */

// Suppress all output — ensure pure JSON
error_reporting(E_ALL);
ini_set('display_errors', '0');
ini_set('log_errors', '1');

// Buffer any stray output from includes
ob_start();

// Aumentar límites para procesos largos de BD
ini_set('max_execution_time', '600');
ini_set('memory_limit', '512M');
set_time_limit(600);

include '../components/connect.php';

session_start();

$admin_id = $_SESSION['admin_id'] ?? null;
if (!$admin_id) {
    ob_end_clean();
    header('Content-Type: application/json; charset=utf-8');
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'No autorizado']);
    exit();
}

// ── Ensure roles system is loaded ──
require_once __DIR__ . '/../components/admin_roles.php';
ensureAdminRolesSchema($conn);
$adminRole = getRoleBySession($conn);
$_SESSION['admin_role'] = $adminRole;

if (!adminCanAccess($adminRole, 'database_management')) {
    ob_end_clean();
    header('Content-Type: application/json; charset=utf-8');
    http_response_code(403);
    echo json_encode(['success' => false, 'message' => 'Sin permisos para gestionar la base de datos']);
    exit();
}

// Discard any output from includes
ob_end_clean();

date_default_timezone_set('America/Bogota');
header('Content-Type: application/json; charset=utf-8');

// ── Backup directory ──
$backupDir = realpath(__DIR__ . '/../') . DIRECTORY_SEPARATOR . 'backups';
if (!is_dir($backupDir)) {
    mkdir($backupDir, 0755, true);
}

// Protect backup directory with .htaccess
$htaccess = $backupDir . DIRECTORY_SEPARATOR . '.htaccess';
if (!file_exists($htaccess)) {
    file_put_contents($htaccess, "Order Deny,Allow\nDeny from all\n");
}

// ── Database credentials ──
function getDbCredentials()
{
    // Usar automáticamente las credenciales de connect.php
    global $dbConfig;
    return [
        'host' => $dbConfig['host'] ?? 'localhost',
        'name' => $dbConfig['name'] ?? 'comidarapidafran',
        'user' => $dbConfig['user'] ?? 'root',
        'pass' => $dbConfig['pass'] ?? ''
    ];
}

// ── History log (JSON file) ──
$historyFile = $backupDir . DIRECTORY_SEPARATOR . '.backup_history.json';

function loadHistory(string $file): array
{
    if (!file_exists($file)) {
        return [];
    }
    $data = json_decode(file_get_contents($file), true);
    return is_array($data) ? $data : [];
}

function saveHistory(string $file, array $data): void
{
    file_put_contents($file, json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));
}

/**
 * Ejecuta un script SQL dividiéndolo correctamente por sentencias,
 * manejando el comando DELIMITER (común en triggers/procedimientos).
 */
function executeSqlScript(PDO $pdo, string $sql): void
{
    // Limpiar comentarios simples y líneas vacías para procesar mejor
    $lines = explode("\n", $sql);
    $query = '';
    $delimiter = ';';

    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || strpos($line, '--') === 0 || strpos($line, '#') === 0) {
            continue;
        }

        // Detectar cambio de DELIMITER (ej: DELIMITER $$ o DELIMITER //)
        if (stripos($line, 'DELIMITER') === 0) {
            $parts = preg_split('/\s+/', $line);
            $delimiter = $parts[1] ?? ';';
            continue;
        }

        $query .= $line . "\n";

        // Si la línea termina con el delimitador actual, ejecutar la consulta
        if (substr(trim($line), -strlen($delimiter)) === $delimiter) {
            // Quitar el delimitador del final para la ejecución PDO
            $execQuery = substr(trim($query), 0, -strlen($delimiter));
            
            if (trim($execQuery) !== '') {
                $pdo->exec($execQuery);
            }
            $query = '';
        }
    }
}

function addHistoryEntry(string $file, string $type, string $filename, string $status, string $notes = '', ?float $sizeMb = null): void
{
    $history = loadHistory($file);
    $entry = [
        'id'        => uniqid('db_', true),
        'type'      => $type, // 'backup' or 'restore'
        'filename'  => $filename,
        'status'    => $status, // 'success' or 'error'
        'notes'     => $notes,
        'size_mb'   => $sizeMb,
        'admin_id'  => $_SESSION['admin_id'] ?? null,
        'timestamp' => date('Y-m-d H:i:s'),
    ];
    array_unshift($history, $entry);
    // Keep last 500 entries
    $history = array_slice($history, 0, 500);
    saveHistory($file, $history);
}

// ════════════════════════════════════════
//  ACTION DISPATCHER
// ════════════════════════════════════════
$action = $_POST['action'] ?? $_GET['action'] ?? '';

try {
    switch ($action) {

        // ── CREATE BACKUP ──────────────────────────
        case 'backup':
            $creds = getDbCredentials();
            $timestamp = date('Y-m-d_H-i-s');
            $filename = 'backup_' . $creds['name'] . '_' . $timestamp . '.sql';
            $filepath = $backupDir . DIRECTORY_SEPARATOR . $filename;

            try {
                $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $creds['host'], $creds['name']);
                $pdo = new PDO($dsn, $creds['user'], $creds['pass'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

                $handle = @fopen($filepath, 'w+');
                if (!$handle) throw new Exception("No se puede crear el archivo de respaldo.");

                fwrite($handle, "-- Respaldo generado con PHP Nativo\n");
                fwrite($handle, "-- Fecha: " . date('Y-m-d H:i:s') . "\n");
                fwrite($handle, "SET FOREIGN_KEY_CHECKS=0;\n");
                fwrite($handle, "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n");
                fwrite($handle, "SET time_zone = \"+00:00\";\n\n");

                $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);

                foreach ($tables as $table) {
                    fwrite($handle, "DROP TABLE IF EXISTS `$table`;\n");
                    $createTableInfo = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_ASSOC);
                    $createSql = $createTableInfo['Create Table'] ?? $createTableInfo['Create View'] ?? '';
                    fwrite($handle, $createSql . ";\n\n");

                    // No volcar datos si es una vista
                    if (isset($createTableInfo['Create View'])) continue;

                    $rows = $pdo->query("SELECT * FROM `$table`");
                    $rowCount = 0;
                    while ($row = $rows->fetch(PDO::FETCH_NUM)) {
                        if ($rowCount % 100 == 0) {
                            if ($rowCount > 0) fwrite($handle, ";\n");
                            fwrite($handle, "INSERT INTO `$table` VALUES ");
                        } else {
                            fwrite($handle, ",");
                        }
                        
                        $values = [];
                        foreach ($row as $val) {
                            if ($val === null) $values[] = "NULL";
                            else $values[] = $pdo->quote($val);
                        }
                        fwrite($handle, "(" . implode(",", $values) . ")");
                        $rowCount++;
                    }
                    if ($rowCount > 0) fwrite($handle, ";\n");
                    fwrite($handle, "\n");
                }
                
                // Exportar triggers
                $triggers = $pdo->query("SHOW TRIGGERS")->fetchAll(PDO::FETCH_ASSOC);
                foreach ($triggers as $t) {
                    fwrite($handle, "DROP TRIGGER IF EXISTS `{$t['Trigger']}`;\n");
                    fwrite($handle, "DELIMITER $$\n");
                    fwrite($handle, "CREATE TRIGGER `{$t['Trigger']}` {$t['Timing']} {$t['Event']} ON `{$t['Table']}` FOR EACH ROW {$t['Statement']}$$\n");
                    fwrite($handle, "DELIMITER ;\n");
                }

                fwrite($handle, "SET FOREIGN_KEY_CHECKS=1;\n");
                fclose($handle);

                $sizeMb = round(filesize($filepath) / (1024 * 1024), 2);
                addHistoryEntry($historyFile, 'backup', $filename, 'success', 'Respaldo creado correctamente (PHP Nativo)', $sizeMb);

                echo json_encode([
                    'success'  => true,
                    'message'  => 'Respaldo creado exitosamente',
                    'filename' => $filename,
                    'size_mb'  => $sizeMb,
                    'date'     => date('d/m/Y H:i:s'),
                ]);
            } catch (Throwable $e) {
                if (file_exists($filepath)) @unlink($filepath);
                $errorMsg = $e->getMessage();
                addHistoryEntry($historyFile, 'backup', $filename, 'error', $errorMsg);
                echo json_encode(['success' => false, 'message' => 'Error al crear respaldo: ' . $errorMsg]);
            }
            break;

        // ── RESTORE DATABASE ───────────────────────
        case 'restore':
            $creds = getDbCredentials();
            $source = $_POST['source'] ?? 'upload'; // 'upload' or 'file'

            if ($source === 'upload') {
                if (!isset($_FILES['sql_file']) || $_FILES['sql_file']['error'] !== UPLOAD_ERR_OK) {
                    echo json_encode(['success' => false, 'message' => 'No se recibió el archivo SQL o excedió el límite de tamaño.']);
                    exit();
                }

                $uploadedFile = $_FILES['sql_file'];
                $ext = strtolower(pathinfo($uploadedFile['name'], PATHINFO_EXTENSION));
                if ($ext !== 'sql') {
                    echo json_encode(['success' => false, 'message' => 'Solo se permiten archivos .sql']);
                    exit();
                }

                $tempPath = $backupDir . DIRECTORY_SEPARATOR . 'temp_restore_' . uniqid() . '.sql';
                if (!move_uploaded_file($uploadedFile['tmp_name'], $tempPath)) {
                    echo json_encode(['success' => false, 'message' => 'Error al guardar el archivo temporal en el servidor. Verifica los permisos de la carpeta backups.']);
                    exit();
                }
                $restoreFile = $tempPath;
                $restoreFilename = $uploadedFile['name'];
            } else {
                $filename = basename($_POST['filename'] ?? '');
                $restoreFile = $backupDir . DIRECTORY_SEPARATOR . $filename;
                $restoreFilename = $filename;

                if (!file_exists($restoreFile)) {
                    echo json_encode(['success' => false, 'message' => 'Archivo de respaldo no encontrado.']);
                    exit();
                }
            }

            // Validate the file contains SQL
            $handle = @fopen($restoreFile, 'r');
            if (!$handle) {
                $err = error_get_last();
                if ($source === 'upload' && isset($tempPath) && file_exists($tempPath)) @unlink($tempPath);
                echo json_encode([
                    'success' => false, 
                    'message' => 'No se pudo leer el archivo SQL. Detalles: ' . ($err['message'] ?? 'Error desconocido')
                ]);
                exit();
            }
            $firstLine = trim((string) fgets($handle));
            fclose($handle);
            
            if (stripos($firstLine, '--') !== 0 && stripos($firstLine, '/*') !== 0 && stripos($firstLine, 'CREATE') !== 0 && stripos($firstLine, 'DROP') !== 0 && stripos($firstLine, 'SET') !== 0 && stripos($firstLine, 'INSERT') !== 0) {
                if ($source === 'upload' && isset($tempPath) && file_exists($tempPath)) @unlink($tempPath);
                echo json_encode(['success' => false, 'message' => 'El archivo no parece ser un dump SQL válido.']);
                exit();
            }

            try {
                $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $creds['host'], $creds['name']);
                $pdo = new PDO($dsn, $creds['user'], $creds['pass'], [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_EMULATE_PREPARES => true
                ]);

                $sqlContent = file_get_contents($restoreFile);
                if ($sqlContent === false) throw new Exception("No se pudo leer el archivo SQL en memoria.");

                // Eliminar DEFINERs que suelen romper las vistas/triggers en hostings compartidos
                $sqlContent = preg_replace('/DEFINER\s*=\s*(?:`[^`]+`|\'[^\']+\'|[^\s]+)@(?:`[^`]+`|\'[^\']+\'|[^\s]+)/i', '', $sqlContent);

                $pdo->exec('SET FOREIGN_KEY_CHECKS = 0;');

                // Forzar limpieza de base de datos actual para evitar errores si el dump no incluye DROP TABLE IF EXISTS
                $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
                foreach ($tables as $table) {
                    $pdo->exec('DROP TABLE IF EXISTS `' . $table . '`');
                }

                // Ejecutar todo el archivo SQL usando el helper que maneja DELIMITER
                executeSqlScript($pdo, $sqlContent);

                $pdo->exec('SET FOREIGN_KEY_CHECKS = 1;');

                // Si fue una subida, guardamos el archivo permanentemente en la carpeta de respaldos
                // para que aparezca en el historial de archivos disponibles.
                if ($source === 'upload' && isset($tempPath) && file_exists($tempPath)) {
                    $finalPath = $backupDir . DIRECTORY_SEPARATOR . 'uploaded_' . date('Y-m-d_H-i-s') . '_' . basename($restoreFilename);
                    @rename($tempPath, $finalPath);
                }

                addHistoryEntry($historyFile, 'restore', $restoreFilename, 'success', 'Base de datos restaurada correctamente (PHP Nativo)');
                echo json_encode([
                    'success' => true,
                    'message' => 'Base de datos restaurada exitosamente desde: ' . $restoreFilename,
                ]);

            } catch (Throwable $e) {
                if ($source === 'upload' && isset($tempPath) && file_exists($tempPath)) {
                    @unlink($tempPath);
                }
                $errorMsg = $e->getMessage();
                addHistoryEntry($historyFile, 'restore', $restoreFilename, 'error', $errorMsg);
                echo json_encode(['success' => false, 'message' => 'Error al restaurar (PDO): ' . $errorMsg]);
                exit();
            }
            break;

        // ── LIST BACKUPS ───────────────────────────
        case 'list':
            $type = $_GET['type'] ?? 'all'; // 'backup', 'restore', 'all'
            $history = loadHistory($historyFile);

            if ($type !== 'all') {
                $history = array_values(array_filter($history, function ($e) use ($type) {
                    return ($e['type'] ?? '') === $type;
                }));
            }

            // Also check for actual backup files
            $files = glob($backupDir . DIRECTORY_SEPARATOR . 'backup_*.sql') ?: [];
            $existingFiles = array_map('basename', $files);

            echo json_encode([
                'success'  => true,
                'history'  => $history,
                'files'    => $existingFiles,
            ]);
            break;

        // ── DOWNLOAD BACKUP ────────────────────────
        case 'download':
            $filename = basename($_GET['file'] ?? '');
            $filepath = $backupDir . DIRECTORY_SEPARATOR . $filename;

            if ($filename === '' || !file_exists($filepath)) {
                http_response_code(404);
                echo json_encode(['success' => false, 'message' => 'Archivo no encontrado']);
                exit();
            }

            header('Content-Type: application/sql');
            header('Content-Disposition: attachment; filename="' . $filename . '"');
            header('Content-Length: ' . filesize($filepath));
            header('Cache-Control: no-cache, no-store, must-revalidate');
            readfile($filepath);
            exit();

        // ── DELETE SINGLE BACKUP ───────────────────
        case 'delete':
            $filename = basename($_POST['filename'] ?? '');
            $filepath = $backupDir . DIRECTORY_SEPARATOR . $filename;

            if ($filename !== '' && file_exists($filepath)) {
                unlink($filepath);
            }

            // Also remove from history
            $history = loadHistory($historyFile);
            $history = array_values(array_filter($history, function ($e) use ($filename) {
                return ($e['filename'] ?? '') !== $filename;
            }));
            saveHistory($historyFile, $history);

            echo json_encode(['success' => true, 'message' => 'Respaldo eliminado']);
            break;

        // ── CLEAR HISTORY ──────────────────────────
        case 'clear_history':
            $type = $_POST['type'] ?? 'all'; // 'backup', 'restore', 'all'
            $deleteFiles = ($_POST['delete_files'] ?? '0') === '1';

            $history = loadHistory($historyFile);

            if ($type === 'all') {
                if ($deleteFiles) {
                    $files = glob($backupDir . DIRECTORY_SEPARATOR . 'backup_*.sql') ?: [];
                    foreach ($files as $f) {
                        unlink($f);
                    }
                }
                $history = [];
            } else {
                if ($deleteFiles && $type === 'backup') {
                    foreach ($history as $entry) {
                        if (($entry['type'] ?? '') === 'backup') {
                            $fpath = $backupDir . DIRECTORY_SEPARATOR . ($entry['filename'] ?? '');
                            if (file_exists($fpath)) {
                                unlink($fpath);
                            }
                        }
                    }
                }
                $history = array_values(array_filter($history, function ($e) use ($type) {
                    return ($e['type'] ?? '') !== $type;
                }));
            }

            saveHistory($historyFile, $history);

            echo json_encode(['success' => true, 'message' => 'Historial limpiado correctamente']);
            break;

        default:
            echo json_encode(['success' => false, 'message' => 'Acción no válida']);
            break;
    }
} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Error interno: ' . $e->getMessage()]);
}
User / IP	: 216.73.216.183
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64