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

// Check if user is Super Admin
if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true || $_SESSION["role"] !== 0) {
    header("location: login.php");
    exit;
}

$user_id = $_SESSION["id"];
$error_message = "";
$success_message = "";

// Display session messages
if (isset($_SESSION['success_message'])) {
    $success_message = $_SESSION['success_message'];
    unset($_SESSION['success_message']);
}

if (isset($_SESSION['error_message'])) {
    $error_message = $_SESSION['error_message'];
    unset($_SESSION['error_message']);
}

// Fetch schools for backup selection
$schools_list = [];
if (isset($conn) && $conn instanceof mysqli) {
    $sql = "SELECT school_id, school_name FROM schools ORDER BY school_name";
    if ($result = $conn->query($sql)) {
        while ($row = $result->fetch_assoc()) {
            $schools_list[] = $row;
        }
        $result->free();
    }
}

// Get all database tables
$all_tables = [];
if (isset($conn) && $conn instanceof mysqli) {
    $result = $conn->query("SHOW TABLES");
    while ($row = $result->fetch_row()) {
        $all_tables[] = $row[0];
    }
}

// Function to create SQL backup
function createSQLBackup($conn, $tables, $backup_path, $school_id = null) {
    $sql_content = "-- Divine Reports Database Backup\n";
    $sql_content .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
    $sql_content .= "SET FOREIGN_KEY_CHECKS=0;\n\n";
    
    foreach ($tables as $table) {
        // Get table structure
        $result = $conn->query("SHOW CREATE TABLE `$table`");
        if ($result) {
            $row = $result->fetch_row();
            $sql_content .= "-- Table: $table\n";
            $sql_content .= "DROP TABLE IF EXISTS `$table`;\n";
            // Clean up the CREATE TABLE statement - remove trailing semicolon and add back
            $create_table = rtrim($row[1], ';');
            $sql_content .= $create_table . ";\n\n";
            
            // Get table data
            $where_clause = "";
            if ($school_id && in_array('school_id', getTableColumns($conn, $table))) {
                $where_clause = " WHERE school_id = $school_id";
            }
            
            $data_result = $conn->query("SELECT * FROM `$table`" . $where_clause);
            if ($data_result && $data_result->num_rows > 0) {
                $sql_content .= "-- Data for table: $table\n";
                
                while ($data_row = $data_result->fetch_assoc()) {
                    $columns = array_keys($data_row);
                    $values = array_values($data_row);
                    
                    // Escape values properly
                    $escaped_values = array_map(function($value) use ($conn) {
                        if ($value === null) {
                            return 'NULL';
                        }
                        if (is_numeric($value) && !empty($value)) {
                            return $value; // Don't quote numeric values
                        }
                        return "'" . $conn->real_escape_string($value) . "'";
                    }, $values);
                    
                    // Ensure we have valid columns and values
                    if (!empty($columns) && count($columns) === count($escaped_values)) {
                        $sql_content .= "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $escaped_values) . ");\n";
                    }
                }
                $sql_content .= "\n";
                $data_result->free();
            }
        }
    }
    
    $sql_content .= "SET FOREIGN_KEY_CHECKS=1;\n";
    
    // Write to file
    return file_put_contents($backup_path, $sql_content);
}

// Helper function to get table columns
function getTableColumns($conn, $table) {
    $columns = [];
    $result = $conn->query("SHOW COLUMNS FROM `$table`");
    while ($row = $result->fetch_assoc()) {
        $columns[] = $row['Field'];
    }
    return $columns;
}

// Handle backup request
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['create_backup'])) {
    $backup_type = $_POST['backup_type'];
    $school_id = ($backup_type === 'school_specific') ? intval($_POST['school_id']) : null;
    
    // Get selected tables
    $selected_tables = [];
    if ($backup_type === 'custom' && isset($_POST['tables'])) {
        $selected_tables = $_POST['tables'];
    } elseif ($backup_type === 'all_tables') {
        $selected_tables = $all_tables;
    } else {
        // Default tables for full or school-specific backup
        $selected_tables = $all_tables;
    }
    
    // Validate school selection
    if ($backup_type === 'school_specific' && !$school_id) {
        $error_message = "Please select a school for school-specific backup.";
    } else {
        // Create backup directory if not exists
        $backup_dir = 'backups/';
        if (!is_dir($backup_dir)) {
            mkdir($backup_dir, 0755, true);
        }
        
        // Generate backup filename
        $timestamp = date('Y-m-d_H-i-s');
        if ($backup_type === 'all_tables') {
            $backup_filename = "full_database_backup_{$timestamp}.sql";
        } elseif ($backup_type === 'school_specific') {
            $backup_filename = "school_{$school_id}_backup_{$timestamp}.sql";
        } else {
            $backup_filename = "custom_backup_{$timestamp}.sql";
        }
        $backup_path = $backup_dir . $backup_filename;
        
        if (isset($conn) && $conn instanceof mysqli) {
            // Log backup start
            $status = 'in_progress';
            $sql = "INSERT INTO backup_logs (backup_type, school_id, backup_path, status, created_by) 
                    VALUES (?, ?, ?, ?, ?)";
            if ($stmt = $conn->prepare($sql)) {
                $stmt->bind_param("sissi", $backup_type, $school_id, $backup_path, $status, $user_id);
                $stmt->execute();
                $backup_id = $conn->insert_id;
                $stmt->close();
                
                try {
                    // Create backup using PHP
                    $result = createSQLBackup($conn, $selected_tables, $backup_path, $school_id);
                    
                    if ($result !== false && file_exists($backup_path)) {
                        $filesize = filesize($backup_path);
                        
                        // Update backup log as completed
                        $status = 'completed';
                        $sql = "UPDATE backup_logs SET status = ?, backup_size = ?, completed_at = NOW() WHERE backup_id = ?";
                        if ($stmt = $conn->prepare($sql)) {
                            $stmt->bind_param("sii", $status, $filesize, $backup_id);
                            $stmt->execute();
                            $stmt->close();
                        }
                        
                        $size_mb = number_format($filesize / 1024 / 1024, 2);
                        $success_message = "Backup created successfully! File: {$backup_filename} (Size: {$size_mb} MB)";
                    } else {
                        throw new Exception("Failed to create backup file.");
                    }
                } catch (Exception $e) {
                    // Update backup log as failed
                    $status = 'failed';
                    $error_msg = $e->getMessage();
                    $sql = "UPDATE backup_logs SET status = ?, error_message = ? WHERE backup_id = ?";
                    if ($stmt = $conn->prepare($sql)) {
                        $stmt->bind_param("ssi", $status, $error_msg, $backup_id);
                        $stmt->execute();
                        $stmt->close();
                    }
                    
                    $error_message = "Backup failed: " . $error_msg;
                }
            }
        }
    }
}

// Handle backup download
if (isset($_GET['download']) && isset($_GET['backup_id'])) {
    $backup_id = intval($_GET['backup_id']);
    
    if (isset($conn) && $conn instanceof mysqli) {
        $sql = "SELECT backup_path FROM backup_logs WHERE backup_id = ? AND status = 'completed'";
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("i", $backup_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows === 1) {
                $backup = $result->fetch_assoc();
                $file_path = $backup['backup_path'];
                
                if (file_exists($file_path)) {
                    header('Content-Type: application/sql');
                    header('Content-Disposition: attachment; filename="' . basename($file_path) . '"');
                    header('Content-Length: ' . filesize($file_path));
                    readfile($file_path);
                    exit;
                }
            }
            $stmt->close();
        }
    }
}

// Handle backup restore
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['restore_backup'])) {
    $backup_id = intval($_POST['backup_id']);
    
    if (isset($conn) && $conn instanceof mysqli) {
        $sql = "SELECT backup_path FROM backup_logs WHERE backup_id = ? AND status = 'completed'";
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("i", $backup_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows === 1) {
                $backup = $result->fetch_assoc();
                $file_path = $backup['backup_path'];
                
                if (file_exists($file_path)) {
                    try {
                        // Read the SQL file
                        $sql_content = file_get_contents($file_path);
                        
                        $conn->begin_transaction();
                        
                        // Disable foreign key checks to avoid constraint issues during restore
                        $conn->query("SET FOREIGN_KEY_CHECKS=0");
                        
                        // Use multi_query to execute all statements in one go
                        if ($conn->multi_query($sql_content)) {
                            $executed = 0;
                            $errors = [];
                            
                            // Process all result sets
                            do {
                                // If there are any results, free them
                                if ($result = $conn->store_result()) {
                                    $result->free();
                                }
                                
                                // Check for errors after each query
                                if ($conn->error) {
                                    $errors[] = $conn->error;
                                }
                                
                                $executed++;
                            } while ($conn->next_result());
                            
                            // Re-enable foreign key checks
                            $conn->query("SET FOREIGN_KEY_CHECKS=1");
                            
                            // Check if there were any errors during execution
                            if (!empty($errors)) {
                                throw new Exception("SQL execution errors: " . implode(", ", $errors));
                            }
                            
                            $conn->commit();
                            
                            // Log the restore
                            $log_sql = "INSERT INTO backup_logs (backup_type, backup_path, status, created_by) 
                                       VALUES ('restore', ?, 'completed', ?)";
                            if ($log_stmt = $conn->prepare($log_sql)) {
                                $restore_note = "Restored from backup_id: {$backup_id}";
                                $log_stmt->bind_param("si", $restore_note, $user_id);
                                $log_stmt->execute();
                                $log_stmt->close();
                            }
                            
                            $_SESSION['success_message'] = "Database restored successfully! Executed {$executed} queries.";
                            header("Location: " . $_SERVER['PHP_SELF']);
                            exit;
                        } else {
                            throw new Exception("Failed to execute SQL file: " . $conn->error);
                        }
                    } catch (Exception $e) {
                        // Re-enable foreign key checks on error
                        $conn->query("SET FOREIGN_KEY_CHECKS=1");
                        $conn->rollback();
                        $_SESSION['error_message'] = "Restore failed: " . $e->getMessage();
                        header("Location: " . $_SERVER['PHP_SELF']);
                        exit;
                    }
                } else {
                    $_SESSION['error_message'] = "Backup file not found.";
                    header("Location: " . $_SERVER['PHP_SELF']);
                    exit;
                }
            }
            $stmt->close();
        }
    }
}

// Handle backup deletion
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['delete_backup'])) {
    $backup_id = intval($_POST['backup_id']);
    
    if (isset($conn) && $conn instanceof mysqli) {
        $sql = "SELECT backup_path FROM backup_logs WHERE backup_id = ?";
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("i", $backup_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows === 1) {
                $backup = $result->fetch_assoc();
                $file_path = $backup['backup_path'];
                
                // Delete the file if it exists
                if (file_exists($file_path)) {
                    unlink($file_path);
                }
                
                // Delete the database record
                $delete_sql = "DELETE FROM backup_logs WHERE backup_id = ?";
                if ($delete_stmt = $conn->prepare($delete_sql)) {
                    $delete_stmt->bind_param("i", $backup_id);
                    $delete_stmt->execute();
                    $delete_stmt->close();
                }
                
                $_SESSION['success_message'] = "Backup deleted successfully!";
                header("Location: " . $_SERVER['PHP_SELF']);
                exit;
            }
            $stmt->close();
        }
    }
}

// Fetch backup history with accurate file sizes
$backups_list = [];
if (isset($conn) && $conn instanceof mysqli) {
    $sql = "SELECT b.*, s.school_name FROM backup_logs b 
            LEFT JOIN schools s ON b.school_id = s.school_id 
            ORDER BY b.created_at DESC LIMIT 50";
    if ($result = $conn->query($sql)) {
        while ($row = $result->fetch_assoc()) {
            // Get actual file size if file exists
            if ($row['status'] === 'completed' && file_exists($row['backup_path'])) {
                $row['actual_size'] = filesize($row['backup_path']);
            } else {
                $row['actual_size'] = $row['backup_size'];
            }
            $backups_list[] = $row;
        }
        $result->free();
    }
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Backup Management - Super Admin</title>
    <link rel="stylesheet" href="super_admin_layout.css">
    <link rel="icon" href="images/logo.png" type="image/png">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <style>
        .backups-container {
            max-width: 1200px;
            margin: 0 auto;
        }

        .page-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 30px;
        }

        .page-header h2 { 
            color: #4f46e5;
            margin: 0;
        }

        .backup-form-card {
            background: white;
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 6px 20px rgba(0, 0, 0, 0.1);
            margin-bottom: 30px;
        }

        .backup-form-card h3 {
            color: #333;
            margin-bottom: 20px;
        }

        .backup-options {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(220px, 1fr));
            gap: 15px;
            margin: 20px 0;
        }

        .backup-option {
            border: 2px solid #e5e7eb;
            border-radius: 8px;
            padding: 20px;
            text-align: center;
            cursor: pointer;
            transition: all 0.3s;
            position: relative;
        }

        .backup-option:hover {
            border-color: #4f46e5;
            background-color: #f0f0ff;
            transform: translateY(-2px);
        }

        .backup-option input[type="radio"] {
            position: absolute;
            opacity: 0;
        }

        .backup-option input[type="radio"]:checked + label {
            color: #4f46e5;
        }

        .backup-option input[type="radio"]:checked ~ .option-content {
            border-color: #4f46e5;
        }

        .backup-option.selected {
            border-color: #4f46e5;
            background-color: #ede9fe;
        }

        .backup-option i {
            font-size: 2.5em;
            margin-bottom: 10px;
            color: #4f46e5;
        }

        .backup-option h4 {
            margin: 10px 0 5px 0;
            color: #333;
        }

        .backup-option p {
            margin: 0;
            font-size: 0.85em;
            color: #666;
        }

        .form-group {
            margin: 20px 0;
        }

        .form-group label {
            display: block;
            margin-bottom: 8px;
            font-weight: 600;
            color: #555;
        }

        .form-group select {
            width: 100%;
            padding: 12px;
            border: 1px solid #ddd;
            border-radius: 6px;
            font-size: 14px;
        }

        .tables-grid {
            display: grid;
            grid-template-columns: repeat(auto-fill, minmax(200px, 1fr));
            gap: 10px;
            margin-top: 15px;
            max-height: 300px;
            overflow-y: auto;
            padding: 15px;
            border: 1px solid #e5e7eb;
            border-radius: 6px;
        }

        .table-checkbox {
            display: flex;
            align-items: center;
            padding: 8px;
            border-radius: 4px;
            transition: background 0.2s;
        }

        .table-checkbox:hover {
            background: #f3f4f6;
        }

        .table-checkbox input[type="checkbox"] {
            margin-right: 8px;
        }

        .table-checkbox label {
            margin: 0;
            cursor: pointer;
            font-weight: normal;
        }

        .btn-backup {
            background: linear-gradient(135deg, #4f46e5 0%, #7c3aed 100%);
            color: white;
            padding: 14px 40px;
            border: none;
            border-radius: 8px;
            font-size: 16px;
            font-weight: 600;
            cursor: pointer;
            width: 100%;
            transition: all 0.3s;
        }

        .btn-backup:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(79, 70, 229, 0.3);
        }

        .btn-select-all {
            background: #6b7280;
            color: white;
            padding: 8px 16px;
            border: none;
            border-radius: 6px;
            font-size: 14px;
            cursor: pointer;
            margin-bottom: 10px;
        }

        .backups-table-card {
            background: white;
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 6px 20px rgba(0, 0, 0, 0.1);
        }

        .backups-table-card h3 {
            color: #333;
            margin-bottom: 20px;
        }

        .backups-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }

        .backups-table th,
        .backups-table td {
            border: 1px solid #eee;
            padding: 12px;
            text-align: left;
        }

        .backups-table th {
            background-color: #f8f9fa;
            font-weight: 600;
            color: #555;
        }

        .backups-table tbody tr:hover {
            background-color: #f9fafb;
        }

        .status-badge {
            padding: 4px 12px;
            border-radius: 12px;
            font-size: 0.85em;
            font-weight: 600;
        }

        .status-completed { background-color: #d1fae5; color: #065f46; }
        .status-failed { background-color: #fee2e2; color: #991b1b; }
        .status-in_progress { background-color: #dbeafe; color: #1e40af; }

        .btn {
            padding: 6px 12px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-size: 0.9em;
            transition: all 0.2s;
            text-decoration: none;
            display: inline-block;
        }

        .btn-primary {
            background-color: #4f46e5;
            color: white;
        }

        .btn-primary:hover {
            background-color: #4338ca;
        }

        .btn-danger {
            background-color: #ef4444;
            color: white;
        }

        .btn-danger:hover {
            background-color: #dc2626;
        }

        .btn-success {
            background-color: #10b981;
            color: white;
        }

        .btn-success:hover {
            background-color: #059669;
        }

        /* Confirmation Modal */
        .modal {
            display: none;
            position: fixed;
            z-index: 9999;
            left: 0;
            top: 0;
            width: 100%;
            height: 100%;
            background-color: rgba(0, 0, 0, 0.5);
            animation: fadeIn 0.3s;
        }

        @keyframes fadeIn {
            from { opacity: 0; }
            to { opacity: 1; }
        }

        .modal-content {
            background-color: white;
            margin: 15% auto;
            padding: 30px;
            border-radius: 12px;
            width: 90%;
            max-width: 500px;
            box-shadow: 0 10px 40px rgba(0, 0, 0, 0.3);
            animation: slideDown 0.3s;
        }

        @keyframes slideDown {
            from {
                transform: translateY(-50px);
                opacity: 0;
            }
            to {
                transform: translateY(0);
                opacity: 1;
            }
        }

        .modal-header {
            display: flex;
            align-items: center;
            margin-bottom: 20px;
        }

        .modal-header i {
            font-size: 2.5em;
            margin-right: 15px;
        }

        .modal-header.warning i {
            color: #f59e0b;
        }

        .modal-header.danger i {
            color: #ef4444;
        }

        .modal-header h3 {
            margin: 0;
            color: #333;
        }

        .modal-body {
            margin-bottom: 25px;
            color: #666;
            line-height: 1.6;
        }

        .modal-footer {
            display: flex;
            gap: 10px;
            justify-content: flex-end;
        }

        .btn-modal {
            padding: 10px 24px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-size: 15px;
            font-weight: 600;
            transition: all 0.2s;
        }

        .btn-cancel {
            background-color: #e5e7eb;
            color: #333;
        }

        .btn-cancel:hover {
            background-color: #d1d5db;
        }

        .btn-confirm-restore {
            background-color: #10b981;
            color: white;
        }

        .btn-confirm-restore:hover {
            background-color: #059669;
        }

        .btn-confirm-delete {
            background-color: #ef4444;
            color: white;
        }

        .btn-confirm-delete:hover {
            background-color: #dc2626;
        }

        .alert {
            padding: 15px 20px;
            border-radius: 8px;
            margin-bottom: 20px;
            transition: opacity 0.5s ease-out, transform 0.5s ease-out;
            display: flex;
            align-items: center;
            gap: 12px;
            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
        }

        .alert i {
            font-size: 1.3em;
        }

        .alert-success { 
            background-color: #d1fae5; 
            color: #065f46; 
            border-left: 4px solid #10b981;
        }
        
        .alert-error { 
            background-color: #fee2e2; 
            color: #991b1b; 
            border-left: 4px solid #ef4444;
        }

        .alert.fade-out { 
            opacity: 0; 
            transform: translateY(-10px); 
        }

        .warning-box {
            background-color: #fef3c7;
            border-left: 4px solid #f59e0b;
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 4px;
        }

        .file-size {
            font-weight: 600;
            color: #4f46e5;
        }

        .backup-stats {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin-bottom: 30px;
        }

        .stat-box {
            background: linear-gradient(135deg, #4f46e5 0%, #7c3aed 100%);
            color: white;
            padding: 20px;
            border-radius: 10px;
            text-align: center;
        }

        .stat-box h4 {
            margin: 0 0 10px 0;
            font-size: 2em;
        }

        .stat-box p {
            margin: 0;
            opacity: 0.9;
        }
    </style>
</head>
<body>
    <div class="dashboard-container" id="dashboardContainer">
        <?php include 'includes/super_admin_sidebar.php'; ?>
        
        <div class="main-content">
            <?php include 'includes/super_admin_header.php'; ?>
            
            <div class="dashboard-content">
                <div class="backups-container">
                    <h2><i class="fas fa-database"></i> Backup Management</h2>

                    <?php if ($error_message): ?>
                        <div class="alert alert-error">
                            <i class="fas fa-exclamation-circle"></i> <?php echo htmlspecialchars($error_message); ?>
                        </div>
                    <?php endif; ?>

                    <?php if ($success_message): ?>
                        <div class="alert alert-success">
                            <i class="fas fa-check-circle"></i> <?php echo htmlspecialchars($success_message); ?>
                        </div>
                    <?php endif; ?>

                    <!-- Backup Statistics -->
                    <div class="backup-stats">
                        <div class="stat-box">
                            <h4><?php echo count($backups_list); ?></h4>
                            <p>Total Backups</p>
                        </div>
                        <div class="stat-box" style="background: linear-gradient(135deg, #10b981 0%, #059669 100%);">
                            <h4><?php echo count(array_filter($backups_list, fn($b) => $b['status'] === 'completed')); ?></h4>
                            <p>Successful Backups</p>
                        </div>
                        <div class="stat-box" style="background: linear-gradient(135deg, #f59e0b 0%, #d97706 100%);">
                            <h4><?php echo count($all_tables); ?></h4>
                            <p>Database Tables</p>
                        </div>
                    </div>

                    <div class="warning-box">
                        <i class="fas fa-exclamation-triangle"></i> <strong>Important:</strong> 
                        Backups are critical for data recovery. Store backups in a secure location and test restoration regularly.
                    </div>

                    <!-- Backup Creation Form -->
                    <div class="backup-form-card">
                        <h3><i class="fas fa-plus-circle"></i> Create New Backup</h3>
                        <form method="POST" action="" id="backupForm" onsubmit="return validateBackupForm()">
                            <div class="backup-options">
                                <div class="backup-option" onclick="selectBackupType('all_tables')">
                                    <input type="radio" name="backup_type" value="all_tables" id="all_tables" checked>
                                    <label for="all_tables">
                                        <i class="fas fa-server"></i>
                                        <h4>Full Database</h4>
                                        <p>Backup all <?php echo count($all_tables); ?> tables</p>
                                    </label>
                                </div>
                                <div class="backup-option" onclick="selectBackupType('school_specific')">
                                    <input type="radio" name="backup_type" value="school_specific" id="school_specific">
                                    <label for="school_specific">
                                        <i class="fas fa-school"></i>
                                        <h4>School Specific</h4>
                                        <p>Backup specific school data</p>
                                    </label>
                                </div>
                                <div class="backup-option" onclick="selectBackupType('custom')">
                                    <input type="radio" name="backup_type" value="custom" id="custom">
                                    <label for="custom">
                                        <i class="fas fa-sliders-h"></i>
                                        <h4>Custom Tables</h4>
                                        <p>Select specific tables</p>
                                    </label>
                                </div>
                            </div>

                            <div class="form-group" id="schoolSelection" style="display: none;">
                                <label><i class="fas fa-school"></i> Select School</label>
                                <select name="school_id">
                                    <option value="">-- Select School --</option>
                                    <?php foreach ($schools_list as $school): ?>
                                        <option value="<?php echo $school['school_id']; ?>">
                                            <?php echo htmlspecialchars($school['school_name']); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>

                            <div class="form-group" id="tablesSelection" style="display: none;">
                                <label><i class="fas fa-table"></i> Select Tables to Backup</label>
                                <button type="button" class="btn-select-all" onclick="toggleAllTables()">Select All / Deselect All</button>
                                <div class="tables-grid">
                                    <?php foreach ($all_tables as $table): ?>
                                        <div class="table-checkbox">
                                            <input type="checkbox" name="tables[]" value="<?php echo htmlspecialchars($table); ?>" id="table_<?php echo htmlspecialchars($table); ?>">
                                            <label for="table_<?php echo htmlspecialchars($table); ?>"><?php echo htmlspecialchars($table); ?></label>
                                        </div>
                                    <?php endforeach; ?>
                                </div>
                            </div>

                            <button type="submit" name="create_backup" class="btn-backup">
                                <i class="fas fa-save"></i> Create Backup Now
                            </button>
                        </form>
                    </div>

                    <!-- Backup History -->
                    <div class="backups-table-card">
                        <h3><i class="fas fa-history"></i> Backup History</h3>
                        <div style="overflow-x: auto;">
                            <table class="backups-table">
                                <thead>
                                    <tr>
                                        <th>Date/Time</th>
                                        <th>Type</th>
                                        <th>School</th>
                                        <th>File Size</th>
                                        <th>Status</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (empty($backups_list)): ?>
                                        <tr>
                                            <td colspan="6" style="text-align: center; padding: 30px; color: #999;">
                                                <i class="fas fa-folder-open" style="font-size: 2em; display: block; margin-bottom: 10px;"></i>
                                                No backups created yet.
                                            </td>
                                        </tr>
                                    <?php else: ?>
                                        <?php foreach ($backups_list as $backup): ?>
                                            <tr>
                                                <td><?php echo date('M d, Y H:i:s', strtotime($backup['created_at'])); ?></td>
                                                <td>
                                                    <i class="fas fa-<?php echo ($backup['backup_type'] === 'all_tables') ? 'server' : (($backup['backup_type'] === 'school_specific') ? 'school' : 'sliders-h'); ?>"></i>
                                                    <?php echo ucwords(str_replace('_', ' ', $backup['backup_type'])); ?>
                                                </td>
                                                <td><?php echo htmlspecialchars($backup['school_name'] ?? 'All Schools'); ?></td>
                                                <td>
                                                    <?php if ($backup['actual_size']): ?>
                                                        <span class="file-size">
                                                            <?php 
                                                            $size = $backup['actual_size'];
                                                            if ($size >= 1048576) {
                                                                echo number_format($size / 1048576, 2) . ' MB';
                                                            } elseif ($size >= 1024) {
                                                                echo number_format($size / 1024, 2) . ' KB';
                                                            } else {
                                                                echo $size . ' B';
                                                            }
                                                            ?>
                                                        </span>
                                                    <?php else: ?>
                                                        <span style="color: #999;">N/A</span>
                                                    <?php endif; ?>
                                                </td>
                                                <td>
                                                    <span class="status-badge status-<?php echo $backup['status']; ?>">
                                                        <?php echo ucfirst(str_replace('_', ' ', $backup['status'])); ?>
                                                    </span>
                                                </td>
                                                <td>
                                                    <?php if ($backup['status'] === 'completed'): ?>
                                                        <div style="display: flex; gap: 8px; flex-wrap: wrap;">
                                                            <a href="?download=1&backup_id=<?php echo $backup['backup_id']; ?>" class="btn btn-primary" title="Download">
                                                                <i class="fas fa-download"></i> Download
                                                            </a>
                                                            <button onclick="confirmRestore(<?php echo $backup['backup_id']; ?>)" class="btn btn-success" title="Restore">
                                                                <i class="fas fa-undo"></i> Restore
                                                            </button>
                                                            <button onclick="confirmDelete(<?php echo $backup['backup_id']; ?>)" class="btn btn-danger" title="Delete">
                                                                <i class="fas fa-trash"></i> Delete
                                                            </button>
                                                        </div>
                                                    <?php else: ?>
                                                        <button onclick="confirmDelete(<?php echo $backup['backup_id']; ?>)" class="btn btn-danger" title="Delete">
                                                            <i class="fas fa-trash"></i> Delete
                                                        </button>
                                                    <?php endif; ?>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        
        <?php include 'includes/super_admin_mobile_nav.php'; ?>
    </div>

    <!-- Restore Confirmation Modal -->
    <div id="restoreModal" class="modal">
        <div class="modal-content">
            <div class="modal-header warning">
                <i class="fas fa-exclamation-triangle"></i>
                <h3>Confirm Restore</h3>
            </div>
            <div class="modal-body">
                <p><strong>Warning:</strong> Restoring a backup will replace all current data with the backup data. This action cannot be undone.</p>
                <p>Are you sure you want to restore this backup?</p>
            </div>
            <div class="modal-footer">
                <button onclick="closeModal('restoreModal')" class="btn-modal btn-cancel">
                    <i class="fas fa-times"></i> Cancel
                </button>
                <form method="POST" style="display: inline;" id="restoreForm">
                    <input type="hidden" name="backup_id" id="restore_backup_id">
                    <button type="submit" name="restore_backup" class="btn-modal btn-confirm-restore">
                        <i class="fas fa-undo"></i> Yes, Restore
                    </button>
                </form>
            </div>
        </div>
    </div>

    <!-- Delete Confirmation Modal -->
    <div id="deleteModal" class="modal">
        <div class="modal-content">
            <div class="modal-header danger">
                <i class="fas fa-trash-alt"></i>
                <h3>Confirm Delete</h3>
            </div>
            <div class="modal-body">
                <p><strong>Warning:</strong> This will permanently delete the backup file and its record from the database.</p>
                <p>Are you sure you want to delete this backup?</p>
            </div>
            <div class="modal-footer">
                <button onclick="closeModal('deleteModal')" class="btn-modal btn-cancel">
                    <i class="fas fa-times"></i> Cancel
                </button>
                <form method="POST" style="display: inline;" id="deleteForm">
                    <input type="hidden" name="backup_id" id="delete_backup_id">
                    <button type="submit" name="delete_backup" class="btn-modal btn-confirm-delete">
                        <i class="fas fa-trash"></i> Yes, Delete
                    </button>
                </form>
            </div>
        </div>
    </div>

    <script>
        // Auto-hide alerts after 5 seconds
        document.addEventListener('DOMContentLoaded', function() {
            const alerts = document.querySelectorAll('.alert');
            alerts.forEach(function(alert) {
                setTimeout(function() {
                    alert.classList.add('fade-out');
                    setTimeout(function() {
                        alert.remove();
                    }, 500);
                }, 5000);
            });
        });

        function selectBackupType(type) {
            // Remove selected class from all options
            document.querySelectorAll('.backup-option').forEach(opt => {
                opt.classList.remove('selected');
            });
            
            // Add selected class to clicked option
            const selectedOption = document.querySelector(`.backup-option input[value="${type}"]`);
            if (selectedOption && selectedOption.closest('.backup-option')) {
                selectedOption.closest('.backup-option').classList.add('selected');
            }
            
            // Check the radio button
            document.getElementById(type).checked = true;
            
            const schoolSelection = document.getElementById('schoolSelection');
            const tablesSelection = document.getElementById('tablesSelection');
            
            schoolSelection.style.display = 'none';
            tablesSelection.style.display = 'none';
            
            if (type === 'school_specific') {
                schoolSelection.style.display = 'block';
            } else if (type === 'custom') {
                tablesSelection.style.display = 'block';
            }
        }

        function toggleAllTables() {
            const checkboxes = document.querySelectorAll('input[name="tables[]"]');
            const allChecked = Array.from(checkboxes).every(cb => cb.checked);
            
            checkboxes.forEach(cb => {
                cb.checked = !allChecked;
            });
        }

        function validateBackupForm() {
            const backupType = document.querySelector('input[name="backup_type"]:checked').value;
            
            if (backupType === 'school_specific') {
                const schoolSelect = document.querySelector('select[name="school_id"]');
                if (!schoolSelect.value) {
                    showMessage('Please select a school for school-specific backup.', 'error');
                    return false;
                }
            } else if (backupType === 'custom') {
                const checkedTables = document.querySelectorAll('input[name="tables[]"]:checked');
                if (checkedTables.length === 0) {
                    showMessage('Please select at least one table for custom backup.', 'error');
                    return false;
                }
            }
            return true;
        }

        function showMessage(message, type) {
            const alertDiv = document.createElement('div');
            alertDiv.className = `alert alert-${type}`;
            alertDiv.innerHTML = `<i class="fas fa-${type === 'success' ? 'check-circle' : 'exclamation-circle'}"></i> ${message}`;
            
            const container = document.querySelector('.backups-container');
            const firstChild = container.firstElementChild;
            container.insertBefore(alertDiv, firstChild.nextSibling);
            
            setTimeout(function() {
                alertDiv.classList.add('fade-out');
                setTimeout(function() {
                    alertDiv.remove();
                }, 500);
            }, 5000);
        }
        
        // Sidebar toggle
        const menuToggle = document.getElementById('menuToggle');
        const sidebar = document.getElementById('sidebar');
        const dashboardContainer = document.getElementById('dashboardContainer');
        
        if (menuToggle) {
            menuToggle.addEventListener('click', function() {
                sidebar.classList.toggle('collapsed');
                dashboardContainer.classList.toggle('sidebar-collapsed');
            });
        }

        // Set initial selected state
        document.addEventListener('DOMContentLoaded', function() {
            selectBackupType('all_tables');
        });

        // Modal functions
        function confirmRestore(backupId) {
            document.getElementById('restore_backup_id').value = backupId;
            document.getElementById('restoreModal').style.display = 'block';
        }

        function confirmDelete(backupId) {
            document.getElementById('delete_backup_id').value = backupId;
            document.getElementById('deleteModal').style.display = 'block';
        }

        function closeModal(modalId) {
            document.getElementById(modalId).style.display = 'none';
        }

        // Close modal when clicking outside
        window.onclick = function(event) {
            if (event.target.classList.contains('modal')) {
                event.target.style.display = 'none';
            }
        }
    </script>
</body>
</html>
<?php
if (isset($conn) && $conn instanceof mysqli && $conn->ping()) {
    $conn->close();
}
?>