<?php
/**
 * Database Dump Script
 * Usage: php dump_db.php
 */

require_once __DIR__ . '/config/config.php';

$host = DB_HOST;
$user = DB_USER;
$pass = DB_PASS;
$name = DB_NAME;

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

$tables = [];
$query = $pdo->query("SHOW TABLES");
while ($row = $query->fetch(PDO::FETCH_NUM)) {
    $tables[] = $row[0];
}

$sqlScript = "";
$sqlScript .= "-- Database Dump: $name\n";
$sqlScript .= "-- Date: " . date('Y-m-d H:i:s') . "\n\n";
$sqlScript .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

foreach ($tables as $table) {
    // Structure
    $row = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_NUM);
    $sqlScript .= "\n\n" . $row[1] . ";\n\n";

    // Data
    $query = $pdo->query("SELECT * FROM `$table`");
    $rowCount = $query->rowCount();
    
    if ($rowCount > 0) {
        $sqlScript .= "INSERT INTO `$table` VALUES ";
        $rows = $query->fetchAll(PDO::FETCH_NUM);
        $count = 0;
        foreach ($rows as $row) {
            $count++;
            $sqlScript .= "(";
            $values = [];
            foreach ($row as $value) {
                if ($value === null) {
                    $values[] = "NULL";
                } else {
                    $value = addslashes($value);
                    $value = str_replace("\n", "\\n", $value);
                    $values[] = "'$value'";
                }
            }
            $sqlScript .= implode(',', $values);
            $sqlScript .= ")";
            if ($count < $rowCount) {
                $sqlScript .= ",";
            }
        }
        $sqlScript .= ";\n";
    }
}

$sqlScript .= "\n\nSET FOREIGN_KEY_CHECKS=1;\n";

$backupFile = __DIR__ . '/deploy_backup.sql';
file_put_contents($backupFile, $sqlScript);

echo "Backup created successfully: $backupFile";
