<?php
session_start();

// Database connection
$host = 'localhost';
$dbname = 'jv_overseas';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Authentication check
if (!isset($_SESSION['user_id']) || $_SESSION['user_type'] !== 'admin') {
    header('Location: admin-login.php');
    exit;
}

// Get admin data
$stmt = $pdo->prepare("SELECT a.*, u.email FROM admins a JOIN users u ON a.id = u.id WHERE a.id = ?");
$stmt->execute([$_SESSION['user_id']]);
$admin = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$admin) {
    die("Admin profile not found.");
}

// Create backup directory if it doesn't exist
$backup_dir = 'backups/';
if (!file_exists($backup_dir)) {
    mkdir($backup_dir, 0777, true);
}

// Handle backup creation
$backup_success = '';
$backup_error = '';

if (isset($_POST['create_backup'])) {
    try {
        // Generate filename
        $filename = 'jv_education_backup_' . date('Y-m-d_H-i-s') . '.sql';
        $filepath = $backup_dir . $filename;
        
        // Get all tables
        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        
        $output = "-- JV Overseas Education Database Backup\n";
        $output .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $output .= "-- Host: $host\n";
        $output .= "-- Database: $dbname\n";
        $output .= "--\n\n";
        
        foreach ($tables as $table) {
            // Get table structure
            $table_structure = $pdo->query("SHOW CREATE TABLE `$table`")->fetch();
            $output .= $table_structure['Create Table'] . ";\n\n";
            
            // Get table data
            $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($rows)) {
                $output .= "INSERT INTO `$table` VALUES\n";
                $values = [];
                foreach ($rows as $row) {
                    $row_values = [];
                    foreach ($row as $value) {
                        if ($value === null) {
                            $row_values[] = 'NULL';
                        } else {
                            $row_values[] = "'" . addslashes($value) . "'";
                        }
                    }
                    $values[] = '(' . implode(',', $row_values) . ')';
                }
                $output .= implode(",\n", $values) . ";\n\n";
            }
        }
        
        // Save to file
        file_put_contents($filepath, $output);
        
        // Log the backup activity
        $pdo->prepare("INSERT INTO activities (
            user_id, activity_type, description, created_at
        ) VALUES (?, ?, ?, NOW())")->execute([
            $_SESSION['user_id'],
            "database_backup",
            "Admin {$_SESSION['first_name']} {$_SESSION['last_name']} created a database backup"
        ]);
        
        $backup_success = "Database backup created successfully! File saved as: $filename";
        
    } catch (Exception $e) {
        $backup_error = "Failed to create backup: " . $e->getMessage();
    }
}

// Get existing backups
$backups = [];
if (file_exists($backup_dir)) {
    $files = glob($backup_dir . '*.sql');
    foreach ($files as $file) {
        $backups[] = [
            'filename' => basename($file),
            'size' => filesize($file),
            'modified' => filemtime($file)
        ];
    }
    // Sort by modified date (newest first)
    usort($backups, function($a, $b) {
        return $b['modified'] - $a['modified'];
    });
}

// Handle backup download
if (isset($_GET['download']) && !empty($_GET['download'])) {
    $filename = basename($_GET['download']);
    $filepath = $backup_dir . $filename;
    
    if (file_exists($filepath)) {
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filepath));
        readfile($filepath);
        exit;
    }
}

// Handle backup deletion
if (isset($_GET['delete']) && !empty($_GET['delete'])) {
    $filename = basename($_GET['delete']);
    $filepath = $backup_dir . $filename;
    
    if (file_exists($filepath)) {
        unlink($filepath);
        header('Location: admin-backup.php');
        exit;
    }
}

// Logout
if (isset($_GET['logout'])) {
    session_destroy();
    header('Location: admin-login.php');
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Database Backup - JV Overseas Education</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Jost:wght@500;600;700&family=Open+Sans:wght@400;600&display=swap" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.10.0/css/all.min.css" rel="stylesheet">
    <link href="lib/owlcarousel/assets/owl.carousel.min.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
    <style>
        .stat-card {
            border-left: 5px solid #3fb7fe;
            margin-bottom: 20px;
        }
        .backup-item {
            border: 1px solid #eee;
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 15px;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .backup-info {
            flex-grow: 1;
        }
        .backup-name {
            font-weight: 600;
        }
        .backup-date {
            font-size: 0.9em;
            color: #6c7577;
        }
        .backup-size {
            font-size: 0.9em;
            color: #6c7577;
        }
    </style>
</head>
<body>

<?php include 'admin-navbar.php'; ?>

<div class="container py-5">
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">System</h6>
        <h1 class="display-5">Database Backup & Restore</h1>
    </div>

    <?php if (isset($backup_error)): ?>
        <div class="alert alert-danger"><?= htmlspecialchars($backup_error) ?></div>
    <?php endif; ?>
    
    <?php if (isset($backup_success)): ?>
        <div class="alert alert-success"><?= htmlspecialchars($backup_success) ?></div>
    <?php endif; ?>

    <!-- Create Backup -->
    <div class="bg-white p-4 rounded mb-4">
        <h5 class="mb-3">Create Database Backup</h5>
        <p>Create a complete backup of the JV Overseas Education database. This will include all tables, data, and structure.</p>
        <form method="POST">
            <button type="submit" name="create_backup" class="btn btn-primary">
                <i class="fas fa-download"></i> Create New Backup
            </button>
        </form>
    </div>

    <!-- Backup Statistics -->
    <div class="row mb-4">
        <div class="col-md-3 mb-3">
            <div class="stat-card">
                <div class="card-body">
                    <h6>Total Backups</h6>
                    <h3><?= count($backups) ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-3 mb-3">
            <div class="stat-card" style="border-left-color: #28a745;">
                <div class="card-body">
                    <h6>Latest Backup</h6>
                    <h3><?= !empty($backups) ? date('M j', $backups[0]['modified']) : 'None' ?></h3>
                </div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="bg-light p-4 rounded">
                <h6><i class="fas fa-info-circle"></i> Backup Guidelines</h6>
                <ul class="small">
                    <li>Backups are automatically created in the /backups/ directory</li>
                    <li>Each backup includes the complete database structure and data</li>
                    <li>Backups are named with timestamp for easy identification</li>
                    <li>Regular backups are recommended for data safety</li>
                </ul>
            </div>
        </div>
    </div>

    <!-- Existing Backups -->
    <div class="section-title position-relative mb-4">
        <h6 class="d-inline-block position-relative text-secondary text-uppercase pb-2">Existing Backups</h6>
        <h1 class="display-6">Manage Your Database Backups</h1>
    </div>
    
    <?php if (empty($backups)): ?>
    <div class="bg-white p-4 rounded text-center">
        <i class="fas fa-archive fa-3x text-muted mb-3"></i>
        <p>No backups found. Create your first backup using the button above.</p>
    </div>
    <?php else: ?>
    <div class="bg-white p-4 rounded">
        <?php foreach ($backups as $backup): ?>
        <div class="backup-item">
            <div class="backup-info">
                <div class="backup-name"><?= htmlspecialchars($backup['filename']) ?></div>
                <div class="backup-date">Created: <?= date('M j, Y H:i', $backup['modified']) ?></div>
                <div class="backup-size">Size: <?= number_format($backup['size'] / 1024, 2) ?> KB</div>
            </div>
            <div>
                <a href="admin-backup.php?download=<?= urlencode($backup['filename']) ?>" 
                   class="btn btn-sm btn-primary mr-2">
                    <i class="fas fa-download"></i> Download
                </a>
                <a href="admin-backup.php?delete=<?= urlencode($backup['filename']) ?>" 
                   class="btn btn-sm btn-danger" 
                   onclick="return confirm('Are you sure you want to delete this backup? This action cannot be undone.')">
                    <i class="fas fa-trash"></i> Delete
                </a>
            </div>
        </div>
        <?php endforeach; ?>
    </div>
    <?php endif; ?>
</div>

<?php include 'footer.php'; ?>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
<script>
    document.getElementById('year').textContent = new Date().getFullYear();
</script>
</body>
</html>