Βελτίωση καταγραφής ενεργειών

[Sugarv]

Να γίνει βελτίωση καταγραφής ενεργειών.

Προτεινόμενη δομή πίνακα:
CREATE TABLE IF NOT EXISTS employee_log (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
emp_id INT NOT NULL COMMENT 'affected employee id',
user_id INT NOT NULL COMMENT 'who made the change',
table_name VARCHAR(64) NOT NULL,
action ENUM('add','edit','delete') NOT NULL,
ip VARCHAR(45) NOT NULL,
query TEXT NOT NULL,
affected_fields JSON NULL,
old_values JSON NULL,
new_values JSON NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_emp (emp_id),
KEY idx_user (user_id),
KEY idx_table (table_name),
KEY idx_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[Sugarv]

Function:

function employeeAuditLog(
    mysqli $db,
    int $empId,
    int $userId,
    string $table,
    string $action, // add | edit | delete
    string $sql,
    ?array $oldValues = null,
    ?array $newValues = null
) {
    $affectedFields = null;

    if ($oldValues && $newValues) {
        $affectedFields = array_keys(array_diff_assoc($newValues, $oldValues));
    }

    $stmt = $db->prepare("
        INSERT INTO employee_log
        (emp_id, user_id, table_name, action, ip, `query`,
         affected_fields, old_values, new_values)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");

    $ip = $_SERVER['REMOTE_ADDR'] ?? 'CLI';

    $stmt->bind_param(
        "iisssssss",
        $empId,
        $userId,
        $table,
        $action,
        $ip,
        $sql,
        $affectedFields ? json_encode($affectedFields, JSON_UNESCAPED_UNICODE) : null,
        $oldValues ? json_encode($oldValues, JSON_UNESCAPED_UNICODE) : null,
        $newValues ? json_encode($newValues, JSON_UNESCAPED_UNICODE) : null
    );

    $stmt->execute();
    $stmt->close();
}