<?php

include "../root.class.php";

$db = new db_safeguard();

function interpret_sql_log(string $sql, int $userId, string $timestamp): string
{
    $call = new call_functions();

    // Normalize spaces
    $sql = trim(preg_replace('/\s+/', ' ', $sql));

    // Parse UPDATE
    if (preg_match('/UPDATE\s+`?(\w+)`?\s+SET\s+(.*?)\s+WHERE\s+(.*)/i', $sql, $matches)) {
        $table = $matches[1];
        $setPart = $matches[2];
        $wherePart = $matches[3];

        // Parse key-value pairs from SET
        $changes = [];
        foreach (explode(',', $setPart) as $pair) {
            [$column, $value] = explode('=', $pair, 2);
            $changes[] = trim($column) . " changed to " . trim($value);
        }

        $changeString = implode('; ', $changes);
        return "User " . $call->get_username($userId) . " updated the `{$table}` table: {$changeString} where {$wherePart} on {$timestamp}.";
    }

    // Parse INSERT
    if (preg_match('/INSERT\s+INTO\s+`?(\w+)`?\s*\((.*?)\)\s*VALUES\s*\((.*?)\)/i', $sql, $matches)) {
        $table = $matches[1];
        $columns = array_map('trim', explode(',', $matches[2]));
        $values = array_map('trim', explode(',', $matches[3]));

        $pairs = [];
        foreach ($columns as $i => $col) {
            $pairs[] = "{$col} = {$values[$i]}";
        }

        $pairString = implode('; ', $pairs);
        return "User " . $call->get_username($userId) . " inserted into `{$table}`: {$pairString} on {$timestamp}.";
    }

    // Parse DELETE
    if (preg_match('/DELETE\s+FROM\s+`?(\w+)`?\s+WHERE\s+(.*)/i', $sql, $matches)) {
        $table = $matches[1];
        $where = $matches[2];

        return "User " . $call->get_username($userId) . " deleted from `{$table}` where {$where} on {$timestamp}.";
    }

    return "User " . $call->get_username($userId) . " ran an unrecognized query on {$timestamp}.";
}

$res = $db->query("logs", "SELECT * FROM `logs` ORDER BY record_id DESC");

echo '

<style>
    body {
        background-color: #f4f4f9;
        font-family: Arial, sans-serif;
    }
    table {
        width: 80%;
        margin: 20px auto;
        border-collapse: collapse;
    }
    th, td {
        padding: 10px;
        text-align: left;
        border-bottom: 1px solid #ddd;
    }
    th {
        background-color: #4CAF50;
        color: white;
    }
    tr:hover {
        background-color: #f1f1f1;
    }
</style>
<style>body {background-color: white;}</style>
<table border="1" cellpadding="5" cellspacing="0">';
echo '<tr><th>Query Interpretation</th></tr>';

while ($row = $res->fetch_assoc()) {
    echo '<tr>';
    echo '<td>' . htmlspecialchars(interpret_sql_log($row['query'], $row['user_id'], $row['date_time'])) . '</td>';
    echo '</tr>';
}

echo '</table>';

