<?php
require_once __DIR__ . '/../includes/init.php';

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

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

foreach ($tables as $table) {
    // Drop table
    $sqlScript .= "DROP TABLE IF EXISTS `$table`;\n";

    // Create Table
    $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
    $row = $stmt->fetch(PDO::FETCH_NUM);
    $sqlScript .= $row[1] . ";\n\n";

    // Data
    $stmt = $pdo->query("SELECT * FROM `$table`");
    $columnCount = $stmt->columnCount();
    
    if ($stmt->rowCount() > 0) {
        $sqlScript .= "INSERT INTO `$table` VALUES\n";
        $rows = [];
        while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
            $line = "(";
            for ($j = 0; $j < $columnCount; $j++) {
                if (!isset($row[$j])) {
                    $line .= "NULL";
                } else {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace("\n", "\\n", $row[$j]);
                    $line .= '"' . $row[$j] . '"';
                }
                if ($j < ($columnCount - 1)) {
                    $line .= ",";
                }
            }
            $line .= ")";
            $rows[] = $line;
        }
        $sqlScript .= implode(",\n", $rows) . ";\n\n";
    }
}
$sqlScript .= "SET FOREIGN_KEY_CHECKS=1;\n";

// Save to root database.sql
file_put_contents(__DIR__ . '/../database.sql', $sqlScript);

echo "database.sql has been generated successfully in the root directory.";
?>
