initial upload ... somewhat cleaned up and "less" swearing included

1 files changed, 361 insertions, 0 deletions

diff --git a/admin/tools/edit_database.php b/admin/tools/edit_database.php
new file mode 100644
index 0000000..bc5489a
--- /dev/null
+++ b/admin/tools/edit_database.php
@@ -0,0 +1,361 @@
+<?php
+session_start();
+
+// Config lade
+$config = include(__DIR__ . '/../../config.php');
+
+// WebsiteID us der URL oder session hole 
+$websiteId = isset($_GET['websiteId']) ? (int)$_GET['websiteId'] : (isset($_SESSION['websiteId']) ? (int)$_SESSION['websiteId'] : 1);
+$_SESSION['websiteId'] = $websiteId;
+
+// Website config ider config finde
+$websiteConfig = array_filter($config, function($site) use ($websiteId) {
+    return isset($site['id']) && $site['id'] === $websiteId;
+});
+$websiteConfig = reset($websiteConfig);
+
+// Sqlite datenbank ahfigge
+$db = new SQLite3($websiteConfig['database']);
+
+// Backup erstelle oder ganzi datehbank lôsche *evil laugh* 😈 jk mer propbieret sehr schlecht IDs wieder sekuentiel zmache
+if (isset($_POST['backup']) || isset($_POST['delete_row']) || isset($_POST['reassign_ids'])) {
+    $source = $websiteConfig['database'];
+    $backupDir = __DIR__ . '/../../' . $websiteConfig['backup_folder'];
+    $timestamp = date('Y-m-d-H-i-s');
+    $destination = $backupDir . '/' . $timestamp . '.db';
+
+    // machen das backup directory wenns nid git
+    if (!is_dir($backupDir)) {
+        mkdir($backupDir, 0755, true);
+    }
+
+    // omg file deht ineh kopiere 😱
+    if (copy($source, $destination)) {
+        $backupMessage = "Database backup created successfully: " . htmlspecialchars($destination);
+    } else {
+        $backupMessage = "Failed to create database backup.";
+    }
+}
+
+$selectedTable = $_POST['table'] ?? $_SESSION['selectedTable'] ?? '';
+$columns = [];
+$rows = [];
+$sortOrder = $_GET['sortOrder'] ?? 'ASC';
+
+$tablesResult = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name != 'sqlite_sequence'");
+$tables = [];
+while ($row = $tablesResult->fetchArray(SQLITE3_ASSOC)) {
+    $tables[] = $row['name'];
+}
+
+if ($selectedTable && in_array($selectedTable, $tables)) {
+    $_SESSION['selectedTable'] = $selectedTable;
+    $columnsResult = $db->query("PRAGMA table_info($selectedTable)");
+    while ($row = $columnsResult->fetchArray(SQLITE3_ASSOC)) {
+        $columns[] = $row['name'];
+    }
+    if ($selectedTable == 'content' && in_array('date', $columns)) {
+        $orderBy = "ORDER BY date DESC"; // das hier machen default sorting nach datum und ziiht de neuschti row zerscht will susch isch das unmanagebar
+    } else {
+        $orderBy = in_array('date', $columns) ? "ORDER BY date $sortOrder" : "ORDER BY id ASC";
+    }
+    $rowsResult = $db->query("SELECT * FROM $selectedTable $orderBy");
+    while ($row = $rowsResult->fetchArray(SQLITE3_ASSOC)) {
+        $rows[] = $row;
+    }
+} else {
+    $selectedTable = '';
+    $_SESSION['selectedTable'] = '';
+}
+
+if (isset($_POST['edit_row'])) {
+    header("Location: edit_row.php?table=$selectedTable&id=" . $_POST['id']);
+    exit();
+}
+
+if (isset($_POST['delete_row'])) {
+    $idsToDelete = $_POST['ids'] ?? [];
+    foreach ($idsToDelete as $id) {
+        $stmt = $db->prepare("DELETE FROM $selectedTable WHERE id = :id");
+        $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
+        $stmt->execute();
+    }
+    $message = "Selected rows deleted successfully (most likely).";
+}
+
+if (isset($_POST['insert_row'])) {
+    $values = $_POST['values'];
+    $highestIdResult = $db->querySingle("SELECT MAX(id) as max_id FROM $selectedTable");
+    $highestId = $highestIdResult ? $highestIdResult : 0;
+    $values['id'] = $highestId + 1;
+    if ($selectedTable == 'content') {
+        if (empty($values['date'])) {
+            $values['date'] = date('Y-m-d H:i:s');
+        }
+    }
+    $columnsString = implode(", ", array_keys($values));
+    $placeholders = implode(", ", array_fill(0, count($values), "?"));
+    $stmt = $db->prepare("INSERT INTO $selectedTable ($columnsString) VALUES ($placeholders)");
+    $index = 1;
+    foreach ($values as $value) {
+        $stmt->bindValue($index, $value ?: null, SQLITE3_TEXT);
+        $index++;
+    }
+    $stmt->execute();
+    $message = "Row inserted successfully. (maybe)";
+}
+
+if (isset($_POST['swap_row'])) {
+    $id = $_POST['id'];
+    $targetId = $_POST['target_id'];
+    $db->exec("BEGIN TRANSACTION");
+    $db->exec("UPDATE $selectedTable SET id = -1 WHERE id = $id");
+    $db->exec("UPDATE $selectedTable SET id = $id WHERE id = $targetId");
+    $db->exec("UPDATE $selectedTable SET id = $targetId WHERE id = -1");
+    $db->exec("COMMIT");
+    $message = "Row swapped successfully for sure.";
+}
+
+if (isset($_POST['push_row'])) {
+    $id = $_POST['id'];
+    $targetId = $_POST['target_id'];
+    $tempTable = $selectedTable . '_temp';
+    $db->exec("BEGIN TRANSACTION");
+    $db->exec("CREATE TEMPORARY TABLE $tempTable AS SELECT * FROM $selectedTable");
+    $db->exec("UPDATE $tempTable SET id = -1 WHERE id = $id");
+    if ($id < $targetId) {
+        $db->exec("UPDATE $tempTable SET id = id - 1 WHERE id > $id AND id <= $targetId");
+    } else {
+        $db->exec("UPDATE $tempTable SET id = id + 1 WHERE id < $id AND id >= $targetId");
+    }
+    $db->exec("UPDATE $tempTable SET id = $targetId WHERE id = -1");
+    $db->exec("DELETE FROM $selectedTable");
+    $db->exec("INSERT INTO $selectedTable SELECT * FROM $tempTable");
+    $db->exec("DROP TABLE $tempTable");
+    $db->exec("COMMIT");
+    $message = "Row pushed successfully (perhaps).";
+}
+
+if (isset($_POST['reassign_ids'])) {
+    $tempBackup = $backupDir . '/' . $timestamp . '_temp.db';
+    copy($source, $tempBackup);
+
+    try {
+        $db->exec("BEGIN TRANSACTION");
+        $tempTable = $selectedTable . '_temp';
+        $db->exec("CREATE TEMPORARY TABLE $tempTable AS SELECT * FROM $selectedTable");
+        $db->exec("DELETE FROM $selectedTable");
+
+        if (in_array('date', $columns)) {
+            $orderBy = "ORDER BY date ASC";
+        } else {
+            $orderBy = "ORDER BY id ASC";
+        }
+
+        $rowsResult = $db->query("SELECT * FROM $tempTable $orderBy");
+        $newId = 1;
+        while ($row = $rowsResult->fetchArray(SQLITE3_ASSOC)) {
+            $row['id'] = $newId++;
+            $columnsString = implode(", ", array_keys($row));
+            $placeholders = implode(", ", array_fill(0, count($row), "?"));
+            $stmt = $db->prepare("INSERT INTO $selectedTable ($columnsString) VALUES ($placeholders)");
+            $index = 1;
+            foreach ($row as $value) {
+                $stmt->bindValue($index, $value ?: null, SQLITE3_TEXT);
+                $index++;
+            }
+            $stmt->execute();
+        }
+
+        $db->exec("DROP TABLE $tempTable");
+        $db->exec("COMMIT");
+        $message = "IDs reassigned successfully.";
+    } catch (Exception $e) {
+        copy($tempBackup, $source);
+        $message = "Failed to reassign IDs. Database restored from backup. (probably)";
+    } finally {
+        unlink($tempBackup);
+    }
+}
+?>
+<!DOCTYPE html>
+<html lang="en">
+<head>
+    <meta charset="UTF-8">
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
+    <title>Edit Database</title>
+</head>
+<body>
+    <form method="post" action="../index.php">
+        <button type="submit">Back to Admin Panel</button>
+    </form>
+    <h1>Edit Database</h1>
+    <form method="get" action="edit_database.php">
+        <label for="websiteId">Select Website:</label>
+        <select name="websiteId" id="websiteId" onchange="this.form.submit()">
+            <?php foreach ($config as $site): ?>
+                <?php if (isset($site['id'])): ?>
+                    <option value="<?php echo $site['id']; ?>" <?php echo ($websiteId == $site['id']) ? 'selected' : ''; ?>>
+                        <?php echo htmlspecialchars($site['name']); ?>
+                    </option>
+                <?php endif; ?>
+            <?php endforeach; ?>
+        </select>
+    </form>
+    <form method="post">
+        <button type="submit" name="backup">Backup das Database</button>
+    </form>
+    <?php if (isset($backupMessage)): ?>
+        <p><?php echo $backupMessage; ?></p>
+    <?php endif; ?>
+    <form method="post">
+        <label for="table">Select das Table:</label>
+        <select id="table" name="table" onchange="this.form.submit()">
+            <option value="">-- Select a table --</option>
+            <?php foreach ($tables as $table): ?>
+                <option value="<?php echo htmlspecialchars($table); ?>" <?php echo ($selectedTable == $table) ? 'selected' : ''; ?>><?php echo htmlspecialchars($table); ?></option>
+            <?php endforeach; ?>
+        </select>
+    </form>
+    <form method="post">
+        <button type="submit" name="refresh">Refresh</button>
+    </form>
+    <?php if ($selectedTable): ?>
+        <h2>Editing Table: <?php echo htmlspecialchars($selectedTable); ?></h2>
+        <?php if ($selectedTable == 'content'): ?>
+            <form method="post">
+                <input type="hidden" name="table" value="<?php echo htmlspecialchars($selectedTable); ?>">
+                <table border="1">
+                    <thead>
+                        <tr>
+                            <th>Select</th>
+                            <?php foreach ($columns as $column): ?>
+                                <th>
+                                    <?php echo htmlspecialchars($column); ?>
+                                    <?php if ($column == 'date'): ?>
+                                        <a href="?table=<?php echo htmlspecialchars($selectedTable); ?>&sortOrder=<?php echo $sortOrder == 'ASC' ? 'DESC' : 'ASC'; ?>">Sort by Date</a>
+                                    <?php endif; ?>
+                                </th>
+                            <?php endforeach; ?>
+                            <th>Actions</th>
+                        </tr>
+                    </thead>
+                    <tbody>
+                        <tr>
+                            <td></td>
+                            <?php foreach ($columns as $column): ?>
+                                <td>
+                                    <?php if ($column == 'id'): ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]" disabled>
+                                    <?php elseif ($column == 'date'): ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]" value="<?php echo date('Y-m-d H:i:s'); ?>" disabled>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]">
+                                    <?php endif; ?>
+                                </td>
+                            <?php endforeach; ?>
+                            <td>
+                                <button type="submit" name="insert_row">Add Row</button>
+                            </td>
+                        </tr>
+                        <?php foreach ($rows as $row): ?>
+                            <tr>
+                                <td><input type="checkbox" name="ids[]" value="<?php echo $row['id']; ?>"></td>
+                                <?php foreach ($columns as $column): ?>
+                                    <td><?php echo htmlspecialchars($row[$column] ?? ''); ?></td>
+                                <?php endforeach; ?>
+                                <td>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <button type="submit" name="edit_row">Edit</button>
+                                    </form>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <button type="submit" name="delete_row">Delete</button>
+                                    </form>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <input type="text" name="target_id" placeholder="Target ID">
+                                        <button type="submit" name="swap_row">Swap</button>
+                                        <button type="submit" name="push_row">Push</button>
+                                    </form>
+                                </td>
+                            </tr>
+                        <?php endforeach; ?>
+                    </tbody>
+                </table>
+                <button type="submit" name="delete_row">Maybe Delete Selected Rows</button>
+                <button type="submit" name="reassign_ids">Maybe Reassign IDs</button>
+            </form>
+        <?php else: ?>
+            <form method="post">
+                <input type="hidden" name="table" value="<?php echo htmlspecialchars($selectedTable); ?>">
+                <table border="1">
+                    <thead>
+                        <tr>
+                            <th>Select</th>
+                            <?php foreach ($columns as $column): ?>
+                                <th>
+                                    <?php echo htmlspecialchars($column); ?>
+                                    <?php if ($column == 'date'): ?>
+                                        <a href="?table=<?php echo htmlspecialchars($selectedTable); ?>&sortOrder=<?php echo $sortOrder == 'ASC' ? 'DESC' : 'ASC'; ?>">Sort by Datum</a>
+                                    <?php endif; ?>
+                                </th>
+                            <?php endforeach; ?>
+                            <th>Actions</th>
+                        </tr>
+                    </thead>
+                    <tbody>
+                        <?php foreach ($rows as $row): ?>
+                            <tr>
+                                <td><input type="checkbox" name="ids[]" value="<?php echo $row['id']; ?>"></td>
+                                <?php foreach ($columns as $column): ?>
+                                    <td><?php echo htmlspecialchars($row[$column] ?? ''); ?></td>
+                                <?php endforeach; ?>
+                                <td>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <button type="submit" name="edit_row">Edit</button>
+                                    </form>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <button type="submit" name="delete_row">Bye</button>
+                                    </form>
+                                    <form method="post" style="display:inline;">
+                                        <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
+                                        <input type="text" name="target_id" placeholder="Target ID">
+                                        <button type="submit" name="swap_row">Swap</button>
+                                        <button type="submit" name="push_row">Push</button>
+                                    </form>
+                                </td>
+                            </tr>
+                        <?php endforeach; ?>
+                        <tr>
+                            <td></td>
+                            <?php foreach ($columns as $column): ?>
+                                <td>
+                                    <?php if ($column == 'id'): ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]" disabled>
+                                    <?php elseif ($column == 'date'): ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]" value="<?php echo date('Y-m-d H:i:s'); ?>" disabled>
+                                    <?php elseif ($selectedTable == 'content' && $column == 'custom_html'): ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]" value="0">
+                                    <?php else: ?>
+                                        <input type="text" name="values[<?php echo htmlspecialchars($column); ?>]">
+                                    <?php endif; ?>
+                                </td>
+                            <?php endforeach; ?>
+                            <td>
+                                <button type="submit" name="insert_row">Add Row</button>
+                            </td>
+                        </tr>
+                    </tbody>
+                </table>
+                <button type="submit" name="delete_row">Delete Selected Rows</button>
+                <button type="submit" name="reassign_ids">Reassign IDs</button>
+            </form>
+        <?php endif; ?>
+    <?php else: ?>
+        <p>No das table selected. Please select das table to editieren.</p>
+    <?php endif; ?>
+</body>
+</html>
\ No newline at end of file