<?php 
// Konfigurasi database
$host = "46.247.109.23";
$username = "admiN";
$password = "Jerman123";
$database = "murid_sekolah";

// Koneksi ke database
$koneksi = mysqli_connect($host, $username, $password, $database);

// Cek koneksi
if (!$koneksi) {
    die("Koneksi gagal: " . mysqli_connect_error());
}

// Set charset untuk menghindari masalah encoding
mysqli_set_charset($koneksi, "utf8");

try {
    // Ambil daftar semua tabel
    $tables = array();
    $result = mysqli_query($koneksi, "SHOW TABLES");
    
    if (!$result) {
        throw new Exception("Error getting tables: " . mysqli_error($koneksi));
    }

    while ($row = mysqli_fetch_row($result)) {
        $tables[] = $row[0];
    }

    // Mulai membuat script SQL
    $sqlScript = "-- Database Backup untuk " . $database . "\n";
    $sqlScript .= "-- Dibuat pada: " . date('Y-m-d H:i:s') . "\n";
    $sqlScript .= "-- Generator: PHP MySQL Backup Script\n\n";
    $sqlScript .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
    $sqlScript .= "SET AUTOCOMMIT = 0;\n";
    $sqlScript .= "START TRANSACTION;\n";
    $sqlScript .= "SET time_zone = \"+00:00\";\n\n";

    // Loop untuk setiap tabel
    foreach ($tables as $table) {
        $sqlScript .= "-- --------------------------------------------------------\n";
        $sqlScript .= "-- Structure for table `$table`\n";
        $sqlScript .= "-- --------------------------------------------------------\n\n";
        
        // Drop table jika ada
        $sqlScript .= "DROP TABLE IF EXISTS `$table`;\n";
        
        // Ambil struktur tabel
        $result = mysqli_query($koneksi, "SHOW CREATE TABLE `$table`");
        if (!$result) {
            throw new Exception("Error getting table structure for $table: " . mysqli_error($koneksi));
        }
        
        $row = mysqli_fetch_row($result);
        $sqlScript .= $row[1] . ";\n\n";
        
        // Ambil data dari tabel
        $result = mysqli_query($koneksi, "SELECT * FROM `$table`");
        if (!$result) {
            throw new Exception("Error getting data from $table: " . mysqli_error($koneksi));
        }
        
        $numFields = mysqli_num_fields($result);
        $numRows = mysqli_num_rows($result);
        
        if ($numRows > 0) {
            $sqlScript .= "-- Dumping data for table `$table`\n";
            $sqlScript .= "-- Records: $numRows\n";
            $sqlScript .= "-- --------------------------------------------------------\n\n";
            
            // Ambil nama kolom untuk INSERT statement yang lebih aman
            $fields = array();
            $fieldInfo = mysqli_fetch_fields($result);
            foreach ($fieldInfo as $field) {
                $fields[] = "`" . $field->name . "`";
            }
            
            $insertHeader = "INSERT INTO `$table` (" . implode(", ", $fields) . ") VALUES\n";
            $sqlScript .= $insertHeader;
            
            $rowCount = 0;
            while ($row = mysqli_fetch_row($result)) {
                if ($rowCount > 0) {
                    $sqlScript .= ",\n";
                }
                
                $sqlScript .= "(";
                for ($j = 0; $j < $numFields; $j++) {
                    if ($j > 0) {
                        $sqlScript .= ", ";
                    }
                    
                    if ($row[$j] === null) {
                        $sqlScript .= "NULL";
                    } else {
                        // Escape string untuk mencegah SQL injection dan error
                        $escapedValue = mysqli_real_escape_string($koneksi, $row[$j]);
                        $sqlScript .= "'" . $escapedValue . "'";
                    }
                }
                $sqlScript .= ")";
                $rowCount++;
            }
            $sqlScript .= ";\n\n";
        } else {
            $sqlScript .= "-- No data found for table `$table`\n\n";
        }
    }

    $sqlScript .= "COMMIT;\n";
    $sqlScript .= "-- End of backup\n";

    // Simpan ke file dan download
    if (!empty($sqlScript)) {
        $backup_file_name = $database . '_backup_' . date('Y-m-d_H-i-s') . '.sql';
        
        // Buat file backup
        $fileHandler = fopen($backup_file_name, 'w+');
        if (!$fileHandler) {
            throw new Exception("Tidak dapat membuat file backup");
        }
        
        $number_of_lines = fwrite($fileHandler, $sqlScript);
        fclose($fileHandler);
        
        if ($number_of_lines === false) {
            throw new Exception("Gagal menulis ke file backup");
        }
        
        // Set headers untuk download
        if (file_exists($backup_file_name)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/sql');
            header('Content-Disposition: attachment; filename="' . basename($backup_file_name) . '"');
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
            header('Pragma: public');
            header('Content-Length: ' . filesize($backup_file_name));
            
            // Bersihkan output buffer
            if (ob_get_level()) {
                ob_end_clean();
            }
            
            // Baca dan output file
            readfile($backup_file_name);
            
            // Hapus file temporary setelah download
            unlink($backup_file_name);
            
            echo "<!-- Backup berhasil dibuat: $backup_file_name -->";
        } else {
            throw new Exception("File backup tidak ditemukan setelah dibuat");
        }
    } else {
        throw new Exception("Tidak ada data untuk di-backup");
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
    
    // Hapus file jika ada error
    if (isset($backup_file_name) && file_exists($backup_file_name)) {
        unlink($backup_file_name);
    }
} finally {
    // Tutup koneksi database
    if ($koneksi) {
        mysqli_close($koneksi);
    }
}
?>