<?php
require_once '../includes/auth.php';
require_once '../includes/db.php';

if (!hasRole('superadmin')) {
    header("HTTP/1.1 403 Forbidden");
    echo "Unauthorized access.";
    exit;
}

// Generate the filename
$filename = 'backup_' . date('Y_m_d_H_i_s') . '.sql';

// Set headers for download
header('Content-Type: application/sql');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');

// Open output stream
$output = fopen('php://output', 'w');

fwrite($output, "-- Apparel Management System Database Backup\n");
fwrite($output, "-- Generated: " . date('Y-m-d H:i:s') . "\n\n");

// Get all tables
$tables = fetchAll("SHOW TABLES");

if ($tables) {
    // The key of the first column will be the "Tables_in_dbname"
    $firstRow = $tables[0];
    $tableKey = array_keys($firstRow)[0];
    
    foreach ($tables as $row) {
        $table = $row[$tableKey];
        
        fwrite($output, "-- --------------------------------------------------------\n");
        fwrite($output, "-- Table structure for table `$table`\n");
        fwrite($output, "-- --------------------------------------------------------\n\n");
        
        fwrite($output, "DROP TABLE IF EXISTS `$table`;\n");
        
        // Get Create Table statement
        $createTableQuery = fetchAll("SHOW CREATE TABLE `$table`");
        if ($createTableQuery) {
            $createRow = $createTableQuery[0];
            $createSql = $createRow['Create Table'];
            fwrite($output, $createSql . ";\n\n");
        }
        
        // Get all data from table
        $data = fetchAll("SELECT * FROM `$table`");
        if (!empty($data)) {
            fwrite($output, "-- Dumping data for table `$table`\n\n");
            
            // Build insert statements in batches to avoid huge strings
            foreach ($data as $dataRow) {
                $keys = array_keys($dataRow);
                $values = array_values($dataRow);
                
                $escapedValues = array_map(function($value) {
                    if ($value === null) return 'NULL';
                    
                    // Basic escape for SQL string
                    $value = str_replace(array("\\", "\0", "\n", "\r", "'", "\"", "\x1a"), array("\\\\", "\\0", "\\n", "\\r", "\\'", "\\\"", "\\Z"), $value);
                    return "'" . $value . "'";
                }, $values);
                
                $insertQuery = "INSERT INTO `$table` (`" . implode("`, `", $keys) . "`) VALUES (" . implode(", ", $escapedValues) . ");\n";
                fwrite($output, $insertQuery);
            }
            fwrite($output, "\n");
        }
    }
}

fwrite($output, "-- Backup completed\n");
fclose($output);
exit;
