delete-dynamic-record.php

<?php
include 'security-check.php';
include 'db_connect.php';

if (!isset($_SESSION['staff_user'])) {
    header("Location: login.php");
    exit();
}

$table_id = intval($_GET['table_id'] ?? 0);
$record_id = intval($_GET['record_id'] ?? 0);

if ($table_id == 0 || $record_id == 0) {
    die("Invalid parameters!");
}

// Get table metadata
$table_query = "SELECT * FROM dynamic_tables WHERE id = $table_id";
$table_result = mysqli_query($conn, $table_query);

if (!$table_result || mysqli_num_rows($table_result) == 0) {
    die("Table not found!");
}

$table_meta = mysqli_fetch_assoc($table_result);
$table_name = 'dt_' . $table_meta['table_name'];

// FIX: Capture the record BEFORE deleting so audit log has old_data
$old_row = null;
$fetch_res = mysqli_query($conn, "SELECT * FROM `{$table_name}` WHERE id = $record_id LIMIT 1");
if ($fetch_res) {
    $old_row = mysqli_fetch_assoc($fetch_res);
}
$old_data_json = $old_row ? json_encode($old_row, JSON_UNESCAPED_UNICODE) : null;

// Delete the record
$delete_sql = "DELETE FROM `{$table_name}` WHERE id = $record_id";

if (mysqli_query($conn, $delete_sql)) {
    // FIX: Log with old_data captured above; use prepared statement
    $log_user = $_SESSION['staff_user'];
    $log_ip   = $_SERVER['REMOTE_ADDR'];
    $log_tbl  = $table_meta['table_name'];
    $log_op   = 'DELETE';
    $log_stmt = mysqli_prepare($conn, "INSERT INTO dynamic_table_audit_log (table_name, operation, record_id, old_data, performed_by, ip_address) VALUES (?, ?, ?, ?, ?, ?)");
    mysqli_stmt_bind_param($log_stmt, 'ssisss', $log_tbl, $log_op, $record_id, $old_data_json, $log_user, $log_ip);
    mysqli_stmt_execute($log_stmt);
    mysqli_stmt_close($log_stmt);
    
    // Redirect back with success message
    header("Location: view-dynamic-table.php?table_id=$table_id&deleted=1");
} else {
    // Redirect back with error
    header("Location: view-dynamic-table.php?table_id=$table_id&error=delete_failed");
}
exit();
?>