<?php
header('Content-Type: application/json');
define('SECURE_ACCESS', true);
require_once __DIR__ . '/../../includes/config.php';
require_once __DIR__ . '/../../includes/db.php';
require_once __DIR__ . '/../../includes/auth.php';

if (!isLoggedIn() || getCurrentUser()['role'] !== 'admin') {
    http_response_code(403);
    echo json_encode(['success' => false, 'message' => 'Unauthorized.']);
    exit();
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    echo json_encode(['success' => false, 'message' => 'Invalid request method.']);
    exit();
}

if (!verifyCSRFToken($_POST['csrf_token'] ?? '')) {
    echo json_encode(['success' => false, 'message' => 'Security check failed (CSRF).']);
    exit();
}

if (!isset($_FILES['backup_file']) || $_FILES['backup_file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['success' => false, 'message' => 'No SQL backup file uploaded or upload error occurred.']);
    exit();
}

$tmp_name = $_FILES['backup_file']['tmp_name'];
$file_name = $_FILES['backup_file']['name'];
$ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

if ($ext !== 'sql') {
    echo json_encode(['success' => false, 'message' => 'Only standard SQL files (.sql) are allowed.']);
    exit();
}

// Max file size: 50MB
$max_size = 50 * 1024 * 1024;
if ($_FILES['backup_file']['size'] > $max_size) {
    echo json_encode(['success' => false, 'message' => 'Backup file is too large. Maximum allowed size is 50MB.']);
    exit();
}

try {
    $sql_content = file_get_contents($tmp_name);
    if (empty($sql_content)) {
        throw new Exception("The uploaded backup file is empty.");
    }

    // Security: Validate that the SQL file is a genuine EventPortal backup
    if (strpos($sql_content, '-- EventPortal PostgreSQL') === false) {
        throw new Exception("Invalid backup file. Only backups generated by this system are accepted.");
    }

    // Security: Whitelist allowed SQL statement types to prevent arbitrary code execution
    $dangerous_patterns = [
        '/\bCREATE\s+FUNCTION\b/i',
        '/\bCREATE\s+PROCEDURE\b/i',
        '/\bCREATE\s+TRIGGER\b/i',
        '/\bCREATE\s+EXTENSION\b/i',
        '/\bCOPY\s+.*\bFROM\s+PROGRAM\b/i',
        '/\bEXECUTE\b/i',
        '/\bDO\s+\$\$/i',
        '/\bCREATE\s+ROLE\b/i',
        '/\bALTER\s+ROLE\b/i',
        '/\bCREATE\s+USER\b/i',
        '/\bGRANT\b/i',
        '/\bREVOKE\b/i',
        '/\bLOAD\b/i',
    ];

    foreach ($dangerous_patterns as $pattern) {
        if (preg_match($pattern, $sql_content)) {
            throw new Exception("The backup file contains disallowed SQL statements and cannot be restored for security reasons.");
        }
    }

    // Split into individual statements and execute them one by one within a transaction
    $pdo->beginTransaction();
    
    // Parse statements while respecting single quotes to prevent breaking on internal semicolons
    $statements = [];
    $current = '';
    $in_string = false;
    $len = strlen($sql_content);
    for ($i = 0; $i < $len; $i++) {
        $char = $sql_content[$i];
        if ($char === "'") {
            if ($in_string && $i + 1 < $len && $sql_content[$i+1] === "'") {
                $current .= "''";
                $i++;
                continue;
            }
            $in_string = !$in_string;
        }
        
        if ($char === ';' && !$in_string) {
            $statements[] = $current;
            $current = '';
        } else {
            $current .= $char;
        }
    }
    if (trim($current) !== '') {
        $statements[] = $current;
    }
    
    $executed_count = 0;
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        // Skip empty statements and comment-only lines
        if (empty($statement) || preg_match('/^--/', $statement) || preg_match('/^\s*SET\s/i', $statement)) {
            continue;
        }
        
        // Only allow: CREATE TABLE, DROP TABLE, INSERT INTO, ALTER TABLE, CREATE INDEX, CREATE SEQUENCE
        if (!preg_match('/^\s*(CREATE\s+TABLE|DROP\s+TABLE|INSERT\s+INTO|ALTER\s+TABLE|CREATE\s+INDEX|CREATE\s+SEQUENCE|SELECT\s+setval)/i', $statement)) {
            // Skip unknown statement types for safety
            continue;
        }
        
        $pdo->exec($statement);
        $executed_count++;
    }

    $pdo->commit();

    echo json_encode(['success' => true, 'message' => "Database successfully restored from backup file. ($executed_count statements executed)"]);
} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    error_log("DB Restore Error: " . $e->getMessage());
    echo json_encode(['success' => false, 'message' => 'Restore failed: ' . $e->getMessage()]);
}
