<?php
$hostname = "localhost";
$database = "doctor_appointment_system";
$username = "root";
$password = "";
$port = 3306;

try {
    $mysqli = new mysqli($hostname, $username, $password, $database, $port);
    if ($mysqli->connect_error) { die("Connection failed: " . $mysqli->connect_error); }
    echo "=== Database Connection Successful ===" . PHP_EOL;
    echo "Database: $database" . PHP_EOL . PHP_EOL;
    $checkQuery = "SELECT COUNT(*) as count FROM users WHERE formatted_user_id LIKE 'PAT%'";
    $result = $mysqli->query($checkQuery);
    $row = $result->fetch_assoc();
    $countBefore = $row["count"];
    echo "Records with 'PAT' prefix BEFORE update: " . $countBefore . PHP_EOL;
    if ($countBefore > 0) {
        echo PHP_EOL . "Sample records before update:" . PHP_EOL;
        $sampleQuery = "SELECT id, formatted_user_id FROM users WHERE formatted_user_id LIKE 'PAT%' LIMIT 5";
        $result = $mysqli->query($sampleQuery);
        while ($row = $result->fetch_assoc()) {
            echo "  - " . $row["formatted_user_id"] . PHP_EOL;
        }
    }
    echo PHP_EOL . "=== Executing UPDATE Query ===" . PHP_EOL;
    $updateQuery = "UPDATE users SET formatted_user_id = REPLACE(formatted_user_id, 'PAT', 'PT') WHERE formatted_user_id LIKE 'PAT%'";
    if ($mysqli->query($updateQuery)) {
        $affectedRows = $mysqli->affected_rows;
        echo "UPDATE executed successfully!" . PHP_EOL;
        echo "Rows affected: $affectedRows" . PHP_EOL;
        $checkAfterQuery = "SELECT COUNT(*) as count FROM users WHERE formatted_user_id LIKE 'PAT%'";
        $result = $mysqli->query($checkAfterQuery);
        $row = $result->fetch_assoc();
        $countAfter = $row["count"];
        echo PHP_EOL . "Records with 'PAT' prefix AFTER update: " . $countAfter . PHP_EOL;
        echo PHP_EOL . "Sample records after update:" . PHP_EOL;
        $sampleAfterQuery = "SELECT id, formatted_user_id FROM users WHERE formatted_user_id LIKE 'PT%' LIMIT 5";
        $result = $mysqli->query($sampleAfterQuery);
        while ($row = $result->fetch_assoc()) {
            echo "  - " . $row["formatted_user_id"] . PHP_EOL;
        }
        echo PHP_EOL . "=== UPDATE COMPLETED SUCCESSFULLY ===" . PHP_EOL;
    } else {
        echo "Error: " . $mysqli->error . PHP_EOL;
    }
    $mysqli->close();
} catch (Exception $e) {
    echo "Exception: " . $e->getMessage() . PHP_EOL;
}
?>