<?php
session_start();
require_once '../config/database.php';

// Check admin authentication
if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {
    header('Location: login.php');
    exit;
}

$database = new Database();
$conn = $database->getConnection();

$message = '';
$error = '';
$progress = [];

// Handle SQL file upload and execution
if (isset($_POST['import_sql']) && isset($_FILES['sql_file'])) {
    try {
        $file = $_FILES['sql_file'];
        
        if ($file['error'] === UPLOAD_ERR_OK) {
            set_time_limit(0); // No time limit for large files
            ini_set('memory_limit', '512M'); // Increase memory limit
            
            $sql = file_get_contents($file['tmp_name']);
            $fileSize = strlen($sql);
            $progress[] = "حجم الملف: " . number_format($fileSize / 1024, 2) . " KB";
            
            // Step 1: Clean and normalize SQL
            $progress[] = "جاري تنظيف وتنسيق SQL...";
            
            // Fix /n to proper newlines
            $sql = str_replace('/n', "\n", $sql);
            $sql = str_replace('\r\n', "\n", $sql);
            $sql = str_replace('\r', "\n", $sql);
            
            // Remove MySQL 8+ specific syntax not compatible with MariaDB 11
            $sql = preg_replace('/INVISIBLE\s+/i', '', $sql);
            $sql = preg_replace('/VISIBLE\s+/i', '', $sql);
            
            // Fix character set issues
            $sql = str_replace('utf8mb4_0900_ai_ci', 'utf8mb4_unicode_ci', $sql);
            $sql = str_replace('utf8mb3', 'utf8', $sql);
            
            // Remove problematic comments
            $sql = preg_replace('/^--.*$/m', '', $sql);
            $sql = preg_replace('/\/\*!40\d{3}.*?\*\//s', '', $sql);
            
            // Step 2: Split into statements
            $progress[] = "جاري تقسيم الاستعلامات...";
            
            // Split by semicolon followed by newline
            $statements = preg_split('/;[\s]*[\r\n]+/', $sql);
            $statements = array_filter(array_map('trim', $statements), function($stmt) {
                return !empty($stmt) && strlen($stmt) > 5;
            });
            
            $totalStatements = count($statements);
            $progress[] = "عدد الاستعلامات: $totalStatements";
            
            // Step 3: Configure MariaDB for import
            $progress[] = "جاري تهيئة MariaDB...";
            
            $conn->exec('SET FOREIGN_KEY_CHECKS=0');
            $conn->exec('SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"');
            $conn->exec('SET AUTOCOMMIT=0');
            $conn->exec('SET UNIQUE_CHECKS=0');
            $conn->exec('SET time_zone = "+00:00"');
            $conn->exec('START TRANSACTION');
            
            // Step 4: Execute statements
            $progress[] = "جاري تنفيذ الاستعلامات...";
            
            $executed = 0;
            $failed = 0;
            $errors = [];
            
            foreach ($statements as $index => $statement) {
                $statement = trim($statement);
                
                if (empty($statement)) continue;
                
                try {
                    $conn->exec($statement);
                    $executed++;
                    
                    // Progress update every 100 statements
                    if ($executed % 100 === 0) {
                        $progress[] = "تم تنفيذ $executed من $totalStatements...";
                    }
                    
                } catch (PDOException $e) {
                    $failed++;
                    $errorMsg = $e->getMessage();
                    
                    // Log only unique errors
                    $errorKey = substr($errorMsg, 0, 100);
                    if (!isset($errors[$errorKey])) {
                        $errors[$errorKey] = [
                            'message' => $errorMsg,
                            'statement' => substr($statement, 0, 200),
                            'count' => 1
                        ];
                    } else {
                        $errors[$errorKey]['count']++;
                    }
                    
                    // Continue with other statements
                    continue;
                }
            }
            
            // Step 5: Commit and restore settings
            $conn->exec('COMMIT');
            $conn->exec('SET FOREIGN_KEY_CHECKS=1');
            $conn->exec('SET UNIQUE_CHECKS=1');
            $conn->exec('SET AUTOCOMMIT=1');
            
            $progress[] = "✓ اكتمل التنفيذ!";
            $progress[] = "نجح: $executed استعلام";
            
            if ($failed > 0) {
                $progress[] = "فشل: $failed استعلام";
                $progress[] = "الأخطاء الفريدة: " . count($errors);
            }
            
            $message = "تم استيراد الملف بنجاح! ($executed/$totalStatements استعلام)";
            
            // Store errors for display
            if (!empty($errors)) {
                $_SESSION['import_errors'] = $errors;
            }
            
        } else {
            $error = 'خطأ في رفع الملف';
        }
    } catch (Exception $e) {
        $error = 'خطأ في استيراد الملف: ' . $e->getMessage();
        
        // Rollback on error
        try {
            $conn->exec('ROLLBACK');
            $conn->exec('SET FOREIGN_KEY_CHECKS=1');
            $conn->exec('SET UNIQUE_CHECKS=1');
            $conn->exec('SET AUTOCOMMIT=1');
        } catch (Exception $rollbackError) {
            // Ignore rollback errors
        }
    }
}

// Get import errors from session
$importErrors = $_SESSION['import_errors'] ?? [];
unset($_SESSION['import_errors']);
?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>استيراد SQL - لوحة التحكم</title>
    <link href="https://fonts.googleapis.com/css2?family=Tajawal:wght@300;400;500;600;700&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
            font-family: 'Tajawal', sans-serif;
        }
        
        body {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 20px;
        }
        
        .container {
            max-width: 1000px;
            margin: 0 auto;
        }
        
        .card {
            background: white;
            border-radius: 16px;
            padding: 32px;
            margin-bottom: 24px;
            box-shadow: 0 10px 40px rgba(0,0,0,0.2);
        }
        
        h1 {
            color: #2c3e50;
            margin-bottom: 8px;
            font-size: 32px;
            display: flex;
            align-items: center;
            gap: 12px;
        }
        
        .subtitle {
            color: #7f8c8d;
            margin-bottom: 32px;
            font-size: 16px;
        }
        
        .alert {
            padding: 20px;
            border-radius: 12px;
            margin-bottom: 24px;
            animation: slideIn 0.3s ease;
        }
        
        @keyframes slideIn {
            from {
                opacity: 0;
                transform: translateY(-10px);
            }
            to {
                opacity: 1;
                transform: translateY(0);
            }
        }
        
        .alert-success {
            background: #d4edda;
            color: #155724;
            border: 2px solid #c3e6cb;
        }
        
        .alert-error {
            background: #f8d7da;
            color: #721c24;
            border: 2px solid #f5c6cb;
        }
        
        .upload-area {
            border: 3px dashed #667eea;
            border-radius: 16px;
            padding: 60px 40px;
            text-align: center;
            background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
            transition: all 0.3s;
            cursor: pointer;
        }
        
        .upload-area:hover {
            border-color: #764ba2;
            transform: translateY(-2px);
        }
        
        .upload-area.dragover {
            background: #e3f2fd;
            border-color: #2196f3;
        }
        
        .upload-icon {
            font-size: 64px;
            color: #667eea;
            margin-bottom: 20px;
        }
        
        .file-input {
            display: none;
        }
        
        .btn {
            padding: 14px 32px;
            border: none;
            border-radius: 12px;
            cursor: pointer;
            font-size: 16px;
            font-weight: 600;
            transition: all 0.3s;
            display: inline-flex;
            align-items: center;
            gap: 10px;
            text-decoration: none;
        }
        
        .btn-primary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            box-shadow: 0 4px 15px rgba(102, 126, 234, 0.4);
        }
        
        .btn-primary:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.6);
        }
        
        .btn-secondary {
            background: #95a5a6;
            color: white;
        }
        
        .btn-secondary:hover {
            background: #7f8c8d;
        }
        
        .progress-log {
            background: #2c3e50;
            color: #ecf0f1;
            padding: 20px;
            border-radius: 12px;
            font-family: 'Courier New', monospace;
            font-size: 14px;
            max-height: 400px;
            overflow-y: auto;
            margin-top: 20px;
        }
        
        .progress-log div {
            padding: 4px 0;
            border-bottom: 1px solid rgba(255,255,255,0.1);
        }
        
        .progress-log div:last-child {
            border-bottom: none;
        }
        
        .features {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 20px;
            margin: 32px 0;
        }
        
        .feature {
            text-align: center;
            padding: 20px;
            background: #f8f9fa;
            border-radius: 12px;
        }
        
        .feature i {
            font-size: 32px;
            color: #667eea;
            margin-bottom: 12px;
        }
        
        .feature h3 {
            font-size: 16px;
            color: #2c3e50;
            margin-bottom: 8px;
        }
        
        .feature p {
            font-size: 13px;
            color: #7f8c8d;
        }
        
        .error-details {
            background: #fff3cd;
            border: 2px solid #ffc107;
            border-radius: 12px;
            padding: 20px;
            margin-top: 20px;
        }
        
        .error-item {
            background: white;
            padding: 12px;
            border-radius: 8px;
            margin-bottom: 12px;
            border-right: 4px solid #e74c3c;
        }
        
        .error-item:last-child {
            margin-bottom: 0;
        }
        
        .loading {
            display: none;
            text-align: center;
            padding: 40px;
        }
        
        .loading.active {
            display: block;
        }
        
        .spinner {
            border: 4px solid #f3f3f3;
            border-top: 4px solid #667eea;
            border-radius: 50%;
            width: 50px;
            height: 50px;
            animation: spin 1s linear infinite;
            margin: 0 auto 20px;
        }
        
        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="card">
            <h1>
                <i class="fas fa-file-import"></i>
                استيراد ملف SQL
            </h1>
            <p class="subtitle">رفع وتنفيذ ملفات SQL كبيرة مع التوافق التلقائي مع MariaDB 11</p>
            
            <?php if ($message): ?>
                <div class="alert alert-success">
                    <i class="fas fa-check-circle"></i> <?php echo htmlspecialchars($message); ?>
                </div>
            <?php endif; ?>
            
            <?php if ($error): ?>
                <div class="alert alert-error">
                    <i class="fas fa-exclamation-circle"></i> <?php echo htmlspecialchars($error); ?>
                </div>
            <?php endif; ?>
            
            <div class="features">
                <div class="feature">
                    <i class="fas fa-magic"></i>
                    <h3>تنظيف تلقائي</h3>
                    <p>إزالة التعليقات والأكواد غير المتوافقة</p>
                </div>
                <div class="feature">
                    <i class="fas fa-database"></i>
                    <h3>توافق MariaDB</h3>
                    <p>تحويل تلقائي للتوافق مع MariaDB 11</p>
                </div>
                <div class="feature">
                    <i class="fas fa-bolt"></i>
                    <h3>تنفيذ سريع</h3>
                    <p>معالجة الملفات الكبيرة بكفاءة</p>
                </div>
                <div class="feature">
                    <i class="fas fa-shield-alt"></i>
                    <h3>آمن</h3>
                    <p>استخدام Transactions لحماية البيانات</p>
                </div>
            </div>
            
            <form method="POST" enctype="multipart/form-data" id="import-form">
                <div class="upload-area" id="upload-area">
                    <i class="fas fa-cloud-upload-alt upload-icon"></i>
                    <h2 style="color: #2c3e50; margin-bottom: 12px;">اسحب وأفلت ملف SQL هنا</h2>
                    <p style="color: #7f8c8d; margin-bottom: 20px;">أو انقر لاختيار ملف</p>
                    
                    <input type="file" name="sql_file" id="sql_file" class="file-input" accept=".sql" required>
                    <label for="sql_file" class="btn btn-primary">
                        <i class="fas fa-folder-open"></i>
                        اختر ملف SQL
                    </label>
                    
                    <div id="file-name" style="margin-top: 20px; color: #27ae60; font-weight: 600; font-size: 16px;"></div>
                </div>
                
                <input type="hidden" name="import_sql" value="1">
                
                <div style="display: flex; gap: 12px; justify-content: center; margin-top: 24px;">
                    <button type="submit" class="btn btn-primary" id="import-btn" disabled>
                        <i class="fas fa-play"></i>
                        بدء الاستيراد
                    </button>
                    <a href="database-backup.php" class="btn btn-secondary">
                        <i class="fas fa-arrow-right"></i>
                        العودة للنسخ الاحتياطية
                    </a>
                </div>
            </form>
            
            <div class="loading" id="loading">
                <div class="spinner"></div>
                <p style="color: #667eea; font-weight: 600; font-size: 18px;">جاري المعالجة... الرجاء الانتظار</p>
            </div>
            
            <?php if (!empty($progress)): ?>
                <div class="progress-log">
                    <div style="color: #3498db; font-weight: bold; margin-bottom: 12px;">
                        <i class="fas fa-terminal"></i> سجل التنفيذ:
                    </div>
                    <?php foreach ($progress as $log): ?>
                        <div><?php echo htmlspecialchars($log); ?></div>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>
            
            <?php if (!empty($importErrors)): ?>
                <div class="error-details">
                    <h3 style="color: #856404; margin-bottom: 16px;">
                        <i class="fas fa-exclamation-triangle"></i>
                        تفاصيل الأخطاء (<?php echo count($importErrors); ?> خطأ فريد)
                    </h3>
                    <?php foreach (array_slice($importErrors, 0, 10) as $error): ?>
                        <div class="error-item">
                            <strong>الخطأ (<?php echo $error['count']; ?>x):</strong>
                            <div style="color: #e74c3c; margin: 8px 0;"><?php echo htmlspecialchars($error['message']); ?></div>
                            <small style="color: #7f8c8d;">الاستعلام: <?php echo htmlspecialchars($error['statement']); ?>...</small>
                        </div>
                    <?php endforeach; ?>
                    <?php if (count($importErrors) > 10): ?>
                        <p style="text-align: center; color: #856404; margin-top: 12px;">
                            ... و <?php echo count($importErrors) - 10; ?> أخطاء أخرى
                        </p>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>
    </div>
    
    <script>
        const uploadArea = document.getElementById('upload-area');
        const fileInput = document.getElementById('sql_file');
        const fileName = document.getElementById('file-name');
        const importBtn = document.getElementById('import-btn');
        const importForm = document.getElementById('import-form');
        const loading = document.getElementById('loading');
        
        // File selection
        fileInput.addEventListener('change', function(e) {
            const file = e.target.files[0];
            if (file) {
                fileName.innerHTML = `<i class="fas fa-check-circle"></i> ${file.name} (${(file.size / 1024).toFixed(2)} KB)`;
                importBtn.disabled = false;
            }
        });
        
        // Drag and drop
        uploadArea.addEventListener('dragover', function(e) {
            e.preventDefault();
            uploadArea.classList.add('dragover');
        });
        
        uploadArea.addEventListener('dragleave', function(e) {
            uploadArea.classList.remove('dragover');
        });
        
        uploadArea.addEventListener('drop', function(e) {
            e.preventDefault();
            uploadArea.classList.remove('dragover');
            
            const file = e.dataTransfer.files[0];
            if (file && file.name.endsWith('.sql')) {
                fileInput.files = e.dataTransfer.files;
                fileName.innerHTML = `<i class="fas fa-check-circle"></i> ${file.name} (${(file.size / 1024).toFixed(2)} KB)`;
                importBtn.disabled = false;
            }
        });
        
        // Form submission
        importForm.addEventListener('submit', function(e) {
            if (!confirm('هل أنت متأكد من استيراد هذا الملف؟ قد يستغرق بعض الوقت للملفات الكبيرة.')) {
                e.preventDefault();
                return;
            }
            
            loading.classList.add('active');
            importBtn.disabled = true;
        });
    </script>
</body>
</html>