<?php
/**
 * One-Time Data Cleanup Script
 * Fixes double-encoded HTML entities (&amp;, &#039;, &quot;, etc.)
 * that were incorrectly stored in the DB by the old sanitize() function.
 *
 * Run ONCE by visiting: http://localhost/travel agnecy 3/fix_encoding.php
 * Then DELETE this file for security.
 */
require_once 'config/db.php';

echo "<h2 style='font-family:sans-serif;'>🔧 Data Encoding Cleanup</h2>";
echo "<p style='font-family:sans-serif;color:#666;'>Fixing HTML entities stored in database columns...</p>";
echo "<hr>";

$total_fixed = 0;

/**
 * Decode HTML entities in a column and update the DB rows.
 */
function fix_column($conn, $table, $column, &$total) {
    $res = $conn->query("SELECT id, `$column` FROM `$table`");
    if (!$res) {
        echo "<p style='color:red;font-family:sans-serif;'>✗ Could not read $table.$column: " . $conn->error . "</p>";
        return;
    }
    $fixed = 0;
    while ($row = $res->fetch_assoc()) {
        $original = $row[$column];
        // Decode HTML entities (may be double-encoded)
        $decoded = html_entity_decode(html_entity_decode($original, ENT_QUOTES | ENT_HTML5, 'UTF-8'), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        // Only update if something changed
        if ($decoded !== $original) {
            $safe = $conn->real_escape_string($decoded);
            $conn->query("UPDATE `$table` SET `$column` = '$safe' WHERE id = {$row['id']}");
            $fixed++;
        }
    }
    if ($fixed > 0) {
        echo "<p style='color:green;font-family:sans-serif;'>✓ Fixed <b>$fixed</b> rows in <b>$table.$column</b></p>";
    } else {
        echo "<p style='color:#aaa;font-family:sans-serif;'>ℹ No encoding issues found in <b>$table.$column</b></p>";
    }
    $total += $fixed;
}

// Fix packages table
fix_column($conn, 'packages', 'title',         $total_fixed);
fix_column($conn, 'packages', 'tour_name',     $total_fixed);
fix_column($conn, 'packages', 'duration',      $total_fixed);
fix_column($conn, 'packages', 'travel_period', $total_fixed);
fix_column($conn, 'packages', 'landmarks',     $total_fixed);
fix_column($conn, 'packages', 'includes',      $total_fixed);

echo "<hr>";

// Fix destinations table
fix_column($conn, 'destinations', 'name',        $total_fixed);
fix_column($conn, 'destinations', 'description', $total_fixed);

echo "<hr>";

// Fix local_tours table
fix_column($conn, 'local_tours', 'title',       $total_fixed);
fix_column($conn, 'local_tours', 'location',    $total_fixed);
fix_column($conn, 'local_tours', 'description', $total_fixed);

echo "<hr>";

// Fix gallery table
fix_column($conn, 'gallery', 'title', $total_fixed);

echo "<hr>";

echo "<h3 style='font-family:sans-serif;color:" . ($total_fixed > 0 ? 'green' : '#333') . ";'>
    " . ($total_fixed > 0 ? "✅ Done! Fixed $total_fixed rows total." : "✅ Database was already clean. No changes needed.") . "
</h3>";
echo "<p style='font-family:sans-serif;color:red;font-weight:bold;'>⚠ IMPORTANT: Delete this file after running it!</p>";
echo "<p style='font-family:sans-serif;'><a href='index.php'>← Go to Homepage</a> | <a href='admin/index.php'>Go to Admin →</a></p>";
?>
