<?php
// =============================================================================
// 1. DATABASE CONFIGURATION & CONNECTION
// =============================================================================

$host = '127.0.0.1';
$db   = 'my_database';
$user = 'root';
$pass = 'password123';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch results as associative array
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Use real prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "<h2>Database connection established successfully!</h2>";
} catch (\PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

echo "<hr>";

// =============================================================================
// 2. CREATE (Insert a new user)
// =============================================================================

$firstName = 'John';
$lastName = 'Doe';
$email = '[email protected]';

// Prepare query to prevent SQL Injection
$sqlInsert = "INSERT INTO users (first_name, last_name, email) VALUES (:first_name, :last_name, :email)";
$stmtInsert = $pdo->prepare($sqlInsert);

// Execute with data
$stmtInsert->execute([
    'first_name' => $firstName,
    'last_name'  => $lastName,
    'email'      => $email
]);

// Get the ID of the newly inserted row
$newId = $pdo->lastInsertId();
echo "<strong>[CREATE]</strong> User successfully added with ID: " . $newId . "<br>";

// =============================================================================
// 3. UPDATE (Modify the newly created user)
// =============================================================================

$newEmail = '[email protected]';

$sqlUpdate = "UPDATE users SET email = :email WHERE id = :id";
$stmtUpdate = $pdo->prepare($sqlUpdate);
$stmtUpdate->execute([
    'email' => $newEmail,
    'id'    => $newId
]);

echo "<strong>[UPDATE]</strong> Email for user ID {$newId} has been updated.<br>";
echo "<hr>";

// =============================================================================
// 4. READ (Fetch all users into an HTML table)
// =============================================================================

// Use query() since no user input/variables are used in this SQL string
$stmtSelect = $pdo->query("SELECT id, first_name, last_name, email FROM users");
$users = $stmtSelect->fetchAll();

echo "<h3>[READ] List of users in the database:</h3>";

if (!empty($users)) {
    echo '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
    echo '<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>E-mail</th></tr>';
    
    foreach ($users as $row) {
        echo '<tr>';
        // htmlspecialchars() prevents XSS attacks
        echo '<td>' . htmlspecialchars($row['id']) . '</td>';
        echo '<td>' . htmlspecialchars($row['first_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['last_name']) . '</td>';
        echo '<td>' . htmlspecialchars($row['email']) . '</td>';
        echo '</tr>';
    }
    
    echo '</table>';
} else {
    echo "No users found in the database.<br>";
}

echo "<hr>";

// =============================================================================
// 5. DELETE (Remove the test user to keep the DB clean)
// =============================================================================

$sqlDelete = "DELETE FROM users WHERE id = ?";
$stmtDelete = $pdo->prepare($sqlDelete);
$stmtDelete->execute([$newId]);

echo "<strong>[DELETE]</strong> Test user with ID {$newId} has been deleted to keep the database clean.";