page tab in moderation portal saved for persistence after reload

migrations/007_comment_moderation.sql

@@ -0,0 +1,14 @@
+-- =====================================================================
+-- Migration 007: Adds Status Column to Comments for Moderation
+-- =====================================================================
+
+-- Adds Status Column with Default 'pending'
+ALTER TABLE comments
+    ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending'
+    CHECK (status IN ('pending', 'approved', 'rejected'));
+
+-- Index for fast Status Filtering
+CREATE INDEX idx_comments_status ON comments(status);
+
+-- Sets all existing Comments to 'approved' (retroactive)
+UPDATE comments SET status = 'approved';

migrations/008_comment_count_trigger.sql

@@ -0,0 +1,65 @@
+-- =====================================================================
+-- Migration 008: Adds comment_count Column with automatic Trigger
+-- Mirrors Pattern from likes_count and dislikes_count.
+-- =====================================================================
+
+
+-- ---------------------------------------------------------------------
+-- Block 1: Adds comment_count Column to Contributions
+-- ---------------------------------------------------------------------
+ALTER TABLE contributions
+    ADD COLUMN comment_count INTEGER NOT NULL DEFAULT 0;
+
+
+-- ---------------------------------------------------------------------
+-- Block 2: Backfills existing Comment Counts
+-- ---------------------------------------------------------------------
+UPDATE contributions c
+SET comment_count = (
+    SELECT COUNT(*)
+    FROM comments cm
+    WHERE cm.contribution_id = c.contribution_id
+      AND cm.status = 'approved'
+);
+
+
+-- ---------------------------------------------------------------------
+-- Block 3: Trigger Function to update comment_count
+-- Fires on Status Change on comments. Only counts approved Comments
+-- ---------------------------------------------------------------------
+CREATE OR REPLACE FUNCTION update_comment_count()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
+        UPDATE contributions
+        SET comment_count = (
+            SELECT COUNT(*) FROM comments
+            WHERE contribution_id = NEW.contribution_id
+              AND status = 'approved'
+        )
+        WHERE contribution_id = NEW.contribution_id;
+    END IF;
+
+    IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.contribution_id != NEW.contribution_id) THEN
+        UPDATE contributions
+        SET comment_count = (
+            SELECT COUNT(*) FROM comments
+            WHERE contribution_id = OLD.contribution_id
+              AND status = 'approved'
+        )
+        WHERE contribution_id = OLD.contribution_id;
+    END IF;
+
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- ---------------------------------------------------------------------
+-- Block 4: Attaches Trigger to comments Table
+-- ---------------------------------------------------------------------
+CREATE TRIGGER trigger_update_comment_count
+    AFTER INSERT OR DELETE OR UPDATE OF status
+    ON comments
+    FOR EACH ROW
+    EXECUTE FUNCTION update_comment_count();

public/admin.php

@@ -57,6 +57,7 @@ $stmt = $pdo->prepare("SELECT * FROM municipalities WHERE slug = :slug");
 $stmt->execute([':slug' => getenv('MUNICIPALITY_SLUG')]);
 $municipality = $stmt->fetch();
 
+
 // Loads News for Moderation
 $stmt = $pdo->prepare("
     SELECT news_id, title, content, author_name, published_at, created_at
@@ -67,6 +68,20 @@ $stmt = $pdo->prepare("
 $stmt->execute([':mid' => $municipality['municipality_id']]);
 $news_items = $stmt->fetchAll();
 
+
+// Loads Comments with Contribution for Moderation
+$stmt = $pdo->prepare("
+    SELECT contribution_id, title, category, description, author_name,
+           geom_type, status, likes_count, dislikes_count, comment_count,
+           photo_path, created_at, updated_at
+    FROM contributions
+    WHERE municipality_id = :mid
+    ORDER BY created_at DESC
+");
+$stmt->execute([':mid' => $municipality['municipality_id']]);
+$all_comments = $stmt->fetchAll();
+
+
 // Shows Login Page if not authenticated
 if ($page === 'login' || !is_admin()) {
     show_login_page($municipality, $login_error ?? null);
@@ -84,7 +99,7 @@ $categories = get_categories();
 
 // Loads all Contributions for Municipality
 $stmt = $pdo->prepare("
-    SELECT contribution_id, title, category, description, author_name,
+    SELECT contribution_id, title, category, description, author_name, photo_path,
            geom_type, status, likes_count, dislikes_count, created_at, updated_at
     FROM contributions
     WHERE municipality_id = :mid
@@ -159,6 +174,9 @@ $counts['total'] = count($all_contributions);
             <button class="page-tab active" onclick="showPageTab('contributions')">
                 <i class="fa-solid fa-list-check"></i> Beiträge
             </button>
+            <button class="page-tab" onclick="showPageTab('comments')">
+                <i class="fa-solid fa-comments"></i> Kommentare
+            </button>
             <button class="page-tab" onclick="showPageTab('news')">
                 <i class="fa-solid fa-newspaper"></i> Neuigkeiten
             </button>
@@ -257,9 +275,28 @@ $counts['total'] = count($all_contributions);
                             <!-- Expanded Detail -->
                             <div class="contribution-row-detail">
                                 <div class="detail-layout">
-                                    <!-- Map Preview -->
+                                    <!-- Map and Photo Slider -->
-                                    <div class="detail-map" id="map-<?= $item['contribution_id'] ?>"
+                                    <div class="detail-slider" id="slider-<?= $item['contribution_id'] ?>">
-                                         data-contribution-id="<?= $item['contribution_id'] ?>">
+                                        <!-- Slide 1: Map -->
+                                        <div class="detail-slide active" data-slide="map">
+                                            <div class="detail-map" id="map-<?= $item['contribution_id'] ?>"
+                                                 data-contribution-id="<?= $item['contribution_id'] ?>">
+                                            </div>
+                                        </div>
+                                        <?php if (!empty($item['photo_path'])): ?>
+                                            <!-- Slide 2: Photo -->
+                                            <div class="detail-slide" data-slide="photo" style="display:none;">
+                                                <img src="<?= htmlspecialchars($item['photo_path']) ?>" alt="Foto"
+                                                     class="detail-slide-photo" onclick="window.open('<?= htmlspecialchars($item['photo_path']) ?>', '_blank')">
+                                            </div>
+                                            <!-- Slider Arrows -->
+                                            <button class="slider-arrow slider-arrow-left" onclick="slideDetail(<?= $item['contribution_id'] ?>, -1)">
+                                                <i class="fa-solid fa-chevron-left"></i>
+                                            </button>
+                                            <button class="slider-arrow slider-arrow-right" onclick="slideDetail(<?= $item['contribution_id'] ?>, 1)">
+                                                <i class="fa-solid fa-chevron-right"></i>
+                                            </button>
+                                        <?php endif; ?>
                                     </div>
 
                                     <!-- Content -->
@@ -277,6 +314,10 @@ $counts['total'] = count($all_contributions);
                                                 <i class="fa-solid fa-thumbs-up"></i> <?= $item['likes_count'] ?>
                                                 &middot;
                                                 <i class="fa-solid fa-thumbs-down"></i> <?= $item['dislikes_count'] ?>
+                                                &middot;
+                                                <i class="fa-solid fa-comment"></i> <?= $item['comment_count'] ?? 0 ?>
+
+                                                
                                             </span>
                                         </div>
                                     </div>
@@ -322,6 +363,73 @@ $counts['total'] = count($all_contributions);
         </div>
 
 
+        <!-- ========================================================= -->
+        <!-- Comments Moderation Tab                                   -->
+        <!-- ========================================================= -->
+        <div id="tab-comments" class="page-tab-content" style="display:none;">
+
+            <!-- Sort Controls -->
+            <div class="sort-controls">
+                <span><?= count($all_comments) ?> Kommentare</span>
+                <select onchange="sortCommentRows(this.value)">
+                    <option value="date-desc">Neueste zuerst</option>
+                    <option value="date-asc">Älteste zuerst</option>
+                    <option value="contribution">Nach Beitrag</option>
+                </select>
+            </div>
+
+            <!-- Comments List -->
+            <div id="comments-mod-container">
+                <?php if (empty($all_comments)): ?>
+                    <div class="empty-state">
+                        <i class="fa-solid fa-comments" style="font-size:2rem;margin-bottom:8px;display:block;"></i>
+                        Noch keine Kommentare vorhanden.
+                    </div>
+                <?php else: ?>
+                    <?php foreach ($all_comments as $comment): ?>
+                        <div class="contribution-row comment-mod-row"
+                             data-date="<?= $comment['created_at'] ?>"
+                             data-contribution="<?= htmlspecialchars($comment['contribution_title']) ?>">
+                            <div class="contribution-row-header" onclick="toggleRow(this.parentElement)">
+                                <div class="contribution-row-summary">
+                                    <span class="title" style="flex:1;"><?= htmlspecialchars(mb_strimwidth($comment['content'], 0, 80, '...')) ?></span>
+                                    <span style="font-size:0.75rem;color:#999;">
+                                        <?= htmlspecialchars($comment['author_name']) ?>
+                                        · <?= date('d.m.Y H:i', strtotime($comment['created_at'])) ?>
+                                    </span>
+                                </div>
+                                <i class="fa-solid fa-chevron-down collapse-icon"></i>
+                            </div>
+                            <div class="contribution-row-detail">
+                                <div style="padding:12px 0;">
+                                    <!-- Reference to Contribution -->
+                                    <div style="font-size:0.8rem;color:var(--color-text-secondary);margin-bottom:8px;padding:8px 12px;background:#f8f9fa;border-radius:6px;border-left:3px solid var(--color-primary);">
+                                        <i class="fa-solid fa-reply"></i> Beitrag: <strong><?= htmlspecialchars($comment['contribution_title']) ?></strong>
+                                    </div>
+                                    <!-- Comment Content -->
+                                    <div style="font-size:0.9rem;line-height:1.6;color:var(--color-text);margin-bottom:8px;">
+                                        <?= nl2br(htmlspecialchars($comment['content'])) ?>
+                                    </div>
+                                    <!-- Meta -->
+                                    <div class="detail-meta">
+                                        <span><i class="fa-solid fa-user"></i> <?= htmlspecialchars($comment['author_name']) ?></span>
+                                        <span><i class="fa-solid fa-calendar"></i> <?= date('d.m.Y, H:i', strtotime($comment['created_at'])) ?> Uhr</span>
+                                    </div>
+                                </div>
+                                <!-- Action Buttons -->
+                                <div class="action-buttons">
+                                    <button class="btn btn-delete" onclick="deleteModComment(<?= $comment['comment_id'] ?>)">
+                                        <i class="fa-solid fa-trash"></i> Kommentar löschen
+                                    </button>
+                                </div>
+                            </div>
+                        </div>
+                    <?php endforeach; ?>
+                <?php endif; ?>
+            </div>
+        </div>
+        
+
         <!-- ========================================================= -->
         <!-- News Article Tab                                      -->
         <!-- ========================================================= -->
@@ -405,13 +513,25 @@ $counts['total'] = count($all_contributions);
         // Current Status Filter
         let currentFilter = 'all';
 
+        // Restores active Tab after Page Reload
+        const savedTab = sessionStorage.getItem('admin_active_tab');
+        if (savedTab) {
+            // Delays to ensure DOM is ready
+            setTimeout(function () {
+                const tabBtn = document.querySelector('.page-tab[onclick*="' + savedTab + '"]');
+                if (tabBtn) tabBtn.click();
+            }, 100);
+        }
+
 
         // =============================================================
         // Page Tab Navigation
         // =============================================================
 
         function showPageTab(tabName) {
-            // Hides all Tab Contents
+            // Saves active Tab for Persistence after Reload
+            sessionStorage.setItem('admin_active_tab', tabName);
+
             document.querySelectorAll('.page-tab-content').forEach(function (el) {
                 el.style.display = 'none';
             });
@@ -452,6 +572,39 @@ $counts['total'] = count($all_contributions);
         }
 
 
+        // =============================================================
+        // Detail Slider for Maps and Photos
+        // =============================================================
+
+        function slideDetail(contributionId, direction) {
+            const slider = document.getElementById('slider-' + contributionId);
+            if (!slider) return;
+
+            const slides = slider.querySelectorAll('.detail-slide');
+            let activeIndex = -1;
+
+            // Finds currently active Slide
+            slides.forEach(function (slide, i) {
+                if (slide.style.display !== 'none') activeIndex = i;
+            });
+
+            // Calculates next Slide Index (wraps around)
+            const nextIndex = (activeIndex + direction + slides.length) % slides.length;
+
+            // Switches Slides
+            slides.forEach(function (slide) { slide.style.display = 'none'; });
+            slides[nextIndex].style.display = 'block';
+
+            // Loads Map if switching to Map Slide and not yet loaded
+            if (slides[nextIndex].dataset.slide === 'map') {
+                const mapDiv = slides[nextIndex].querySelector('.detail-map');
+                if (mapDiv && !mapDiv.dataset.loaded) {
+                    loadMapPreview(mapDiv);
+                }
+            }
+        }
+
+
         // =============================================================
         // Map Preview (Leaflet Mini Map per Contribution)
         // =============================================================
@@ -621,7 +774,7 @@ $counts['total'] = count($all_contributions);
 
 
         // =============================================================
-        // Edit Contribution (Title and Description)
+        // Edit Contribution
         // =============================================================
 
         function editContribution(contributionId, currentTitle, currentDescription) {
@@ -847,6 +1000,60 @@ $counts['total'] = count($all_contributions);
             });
         }
 
+
+        // =============================================================
+        // Sort Comments
+        // =============================================================
+
+        function sortCommentRows(sortBy) {
+            const container = document.getElementById('comments-mod-container');
+            const rows = Array.from(container.querySelectorAll('.comment-mod-row'));
+
+            rows.sort(function (a, b) {
+                if (sortBy === 'date-desc') {
+                    return new Date(b.dataset.date) - new Date(a.dataset.date);
+                } else if (sortBy === 'date-asc') {
+                    return new Date(a.dataset.date) - new Date(b.dataset.date);
+                } else if (sortBy === 'contribution') {
+                    return a.dataset.contribution.localeCompare(b.dataset.contribution);
+                }
+                return 0;
+            });
+
+            rows.forEach(function (row) { container.appendChild(row); });
+        }
+
+
+        // =============================================================
+        // Delete Comments
+        // =============================================================
+
+        function deleteModComment(commentId) {
+            Swal.fire({
+                title: 'Kommentar löschen?',
+                text: 'Diese Aktion kann nicht rückgängig gemacht werden.',
+                icon: 'warning',
+                showCancelButton: true,
+                confirmButtonText: 'Löschen',
+                cancelButtonText: 'Abbrechen',
+                confirmButtonColor: '#c62828'
+            }).then(function (result) {
+                if (!result.isConfirmed) return;
+
+                apiCall({
+                    action: 'delete_comment',
+                    comment_id: commentId
+                }).then(function (response) {
+                    if (response.error) {
+                        Swal.fire('Fehler', response.error, 'error');
+                        return;
+                    }
+                    Swal.fire('Gelöscht!', 'Kommentar wurde entfernt.', 'success')
+                        .then(function () { location.reload(); });
+                });
+            });
+        }
+
     </script>
 
 </body>

public/api/contributions.php

@@ -83,8 +83,8 @@ function handle_read($input) {
     $municipality_id = $input['municipality_id'];
 
     // Builds SQL Query with Placeholders for prepared Statement
-    $sql = "SELECT *, ST_AsGeoJSON(geom) AS geojson 
+    $sql = "SELECT *, ST_AsGeoJSON(geom) AS geojson
-            FROM contributions 
+            FROM contributions
             WHERE municipality_id = :mid";
     $params = [':mid' => $municipality_id];
 

public/js/app.js

@@ -17,7 +17,8 @@
 const API_URL = 'api/contributions.php';
 
 // Username set via Login Modal stored in sessionStorage
-let currentUser = sessionStorage.getItem('webgis_user') || '';
+let currentUser = sessionStorage.getItem('webgis_user') || 
+    decodeURIComponent(document.cookie.replace(/(?:(?:^|.*;\s*)webgis_user\s*=\s*([^;]*).*$)|^.*$/, '$1')) || '';
 
 // Browser Identification Number for anonymous User Identification stored as Cookie
 let browserId = getBrowserId();
@@ -409,11 +410,6 @@ function buildPopupHtml(feature) {
     if (props.photo_path) {
         html += '<div class="popup-photo-container" id="photo-container-' + props.contribution_id + '" style="display:none;">' +
                     '<img src="' + escapeHtml(props.photo_path) + '" alt="Foto" class="popup-photo-img" onclick="window.open(\'' + escapeHtml(props.photo_path) + '\', \'_blank\')">' +
-                '</div>' +
-                '<div class="popup-photo-toggle">' +
-                    '<button class="popup-photo-btn" onclick="togglePhoto(' + props.contribution_id + ')">' +
-                        '<i class="fa-solid fa-camera"></i> <span id="photo-label-' + props.contribution_id + '">Foto anzeigen</span>' +
-                    '</button>' +
                 '</div>';
     }
 
@@ -423,15 +419,23 @@ function buildPopupHtml(feature) {
                 ' &middot; <i class="fa-solid fa-calendar"></i> ' + dateStr +
             '</div>';
 
-    // Vote Buttons
+    // Vote Buttons and Photo Toggle
     html += '<div class="popup-detail-votes">' +
                 '<button class="popup-vote-btn' + (userVotes[props.contribution_id] === 'like' ? ' liked' : '') + '" id="vote-like-' + props.contribution_id + '" onclick="voteContribution(' + props.contribution_id + ', \'like\')" title="Gefällt mir">' +
                     '<i class="fa-solid fa-thumbs-up"></i> <span id="likes-' + props.contribution_id + '">' + props.likes_count + '</span>' +
                 '</button>' +
                 '<button class="popup-vote-btn' + (userVotes[props.contribution_id] === 'dislike' ? ' disliked' : '') + '" id="vote-dislike-' + props.contribution_id + '" onclick="voteContribution(' + props.contribution_id + ', \'dislike\')" title="Gefällt mir nicht">' +
                     '<i class="fa-solid fa-thumbs-down"></i> <span id="dislikes-' + props.contribution_id + '">' + props.dislikes_count + '</span>' +
-                '</button>' +
+                '</button>';
-            '</div>';
+
+    // Photo Toggle Button
+    if (props.photo_path) {
+        html += '<button class="popup-vote-btn" onclick="togglePhoto(' + props.contribution_id + ')" title="Foto">' +
+                    '<i class="fa-solid fa-camera"></i> <span id="photo-label-' + props.contribution_id + '">Foto anzeigen</span>' +
+                '</button>';
+    }
+
+    html += '</div>';
 
     // Edit and Delete Buttons for Author or Admin
     if (props.browser_id === browserId || (typeof IS_ADMIN !== 'undefined' && IS_ADMIN)) {
@@ -952,6 +956,7 @@ function submitLogin() {
     }
     currentUser = name;
     sessionStorage.setItem('webgis_user', currentUser);
+    document.cookie = 'webgis_user=' + encodeURIComponent(name) + ';path=/;max-age=31536000;SameSite=Lax';
     document.getElementById('login-modal').style.display = 'none';
 
     // Open Create Modal if Geometry is pending 

public/styles.css

@@ -634,10 +634,6 @@ select.form-input { cursor: pointer; }
    ----------------------------------------------------------------- */
 
 /* Photo Toggle Button */
-.popup-photo-toggle {
-    margin: var(--space-sm) 0;
-}
-
 .popup-photo-btn {
     display: inline-flex;
     align-items: center;
@@ -1036,6 +1032,60 @@ select.form-input { cursor: pointer; }
 .back-link a { color: var(--color-text-secondary); }
 
 
+/* -----------------------------------------------------------------
+   5.8 Detail Slider (Map/Photo in Admin)
+   ----------------------------------------------------------------- */
+.detail-slider {
+    width: 220px;
+    height: 170px;
+    flex-shrink: 0;
+    position: relative;
+    border-radius: 6px;
+    overflow: hidden;
+    border: 1px solid var(--color-border);
+    background: #f0f0f0;
+}
+
+.detail-slide { width: 100%; height: 100%; }
+
+.detail-slide-photo {
+    width: 100%;
+    height: 100%;
+    object-fit: cover;
+    cursor: pointer;
+}
+
+.detail-slider .detail-map {
+    width: 100%;
+    height: 100%;
+    border: none;
+    border-radius: 0;
+}
+
+.slider-arrow {
+    position: absolute;
+    top: 50%;
+    transform: translateY(-50%);
+    background: rgba(0, 0, 0, 0.5);
+    color: white;
+    border: none;
+    width: 28px;
+    height: 28px;
+    border-radius: 50%;
+    cursor: pointer;
+    display: flex;
+    align-items: center;
+    justify-content: center;
+    font-size: 0.7rem;
+    z-index: 1000;
+    transition: background var(--transition-fast);
+}
+
+.slider-arrow:hover { background: rgba(0, 0, 0, 0.7); }
+.slider-arrow-left { left: 4px; }
+.slider-arrow-right { right: 4px; }
+
+
 /* =================================================================
    SECTION 6: Responsive Overrides
    ================================================================= */
@@ -1075,6 +1125,8 @@ select.form-input { cursor: pointer; }
     .action-buttons .btn { justify-content: center; }
     .filter-tabs { overflow-x: auto; }
     .page-tabs { overflow-x: auto; }
+    .detail-slider { width: 100%; height: 200px; }
+
 
     /* Legal */
     .page-content-box { padding: 20px; }