update_score.php

<?php
    header('Content-Type: application/json');

    $servername = "localhost";
    $DBUsername = "root";
    $DBPassword = "root";
    $dbname = "new_unity";

    // Get JSON data from request body
    $json = file_get_contents('php://input');
    $data = json_decode($json, true);

    // Check if JSON was decoded successfully
    if ($data === null) {
        die(json_encode(array("success" => false, "error" => 10, "message" => "Invalid JSON data")));
    }

    // Extract score data
    $username = $data["username"];
    $completionTime = $data["completionTime"];
    $difficulty = $data["difficulty"];

    // Connect to database
    $conn = new mysqli($servername, $DBUsername, $DBPassword, $dbname);

    if($conn->connect_error) {
        die(json_encode(array("success" => false, "error" => 1, "message" => "Connection to database failed")));
    }

    try {
        // First, get the user ID from the username
        $userIdStmt = $conn->prepare("SELECT user_id FROM users WHERE username = ?");
        if (!$userIdStmt) {
            die(json_encode(array("success" => false, "error" => 2, "message" => "Failed to prepare user lookup statement: " . $conn->error)));
        }
        
        $userIdStmt->bind_param("s", $username);
        
        if (!$userIdStmt->execute()) {
            die(json_encode(array("success" => false, "error" => 3, "message" => "User ID lookup failed: " . $userIdStmt->error)));
        }
        
        $userResult = $userIdStmt->get_result();
        
        if ($userResult->num_rows == 0) {
            $userIdStmt->close();
            die(json_encode(array("success" => false, "error" => 4, "message" => "User not found")));
        }
        
        $userRow = $userResult->fetch_assoc();
        $userId = $userRow["user_id"];
        
        $userIdStmt->close();
        
        // Check if the user already has a score for this difficulty
        $checkScoreStmt = $conn->prepare("SELECT id, completion_time FROM scores WHERE user_id = ? AND difficulty = ? ORDER BY completion_time ASC LIMIT 1");
        if (!$checkScoreStmt) {
            die(json_encode(array("success" => false, "error" => 5, "message" => "Failed to prepare score check statement: " . $conn->error)));
        }
        
        $checkScoreStmt->bind_param("is", $userId, $difficulty);
        
        if (!$checkScoreStmt->execute()) {
            die(json_encode(array("success" => false, "error" => 6, "message" => "Score check failed: " . $checkScoreStmt->error)));
        }
        
        $scoreResult = $checkScoreStmt->get_result();
        $isNewBest = false;
        $existingScoreId = null;
        $existingTime = null;
        
        if ($scoreResult->num_rows > 0) {
            // User already has a score for this difficulty
            $scoreRow = $scoreResult->fetch_assoc();
            $existingScoreId = $scoreRow["id"];
            $existingTime = $scoreRow["completion_time"];
            
            // Check if new time is better (lower) than existing time
            if ($completionTime < $existingTime) {
                $isNewBest = true;
            } else {
                // Current score is not better, return early with message
                $checkScoreStmt->close();
                echo json_encode(array(
                    "success" => true, 
                    "message" => "Score not saved. Your previous best time of " . number_format($existingTime, 3) . " seconds is better.",
                    "data" => array(
                        "username" => $username,
                        "newTime" => $completionTime,
                        "bestTime" => $existingTime,
                        "difficulty" => $difficulty,
                        "improved" => false
                    )
                ));
                $conn->close();
                exit;
            }
        } else {
            // No existing score, this is the first one
            $isNewBest = true;
        }
        
        $checkScoreStmt->close();
        
        if ($isNewBest) {
            if ($existingScoreId !== null) {
                // Update existing record with new best time
                $updateScoreStmt = $conn->prepare("UPDATE scores SET completion_time = ?, timestamp = NOW() WHERE id = ?");
                if (!$updateScoreStmt) {
                    die(json_encode(array("success" => false, "error" => 7, "message" => "Failed to prepare score update statement: " . $conn->error)));
                }
                
                $updateScoreStmt->bind_param("di", $completionTime, $existingScoreId);
                
                if (!$updateScoreStmt->execute()) {
                    die(json_encode(array("success" => false, "error" => 8, "message" => "Score update failed: " . $updateScoreStmt->error)));
                }
                
                $updateScoreStmt->close();
                
                echo json_encode(array(
                    "success" => true, 
                    "message" => "New best time saved! You improved by " . number_format($existingTime - $completionTime, 3) . " seconds.",
                    "data" => array(
                        "username" => $username,
                        "completionTime" => $completionTime,
                        "previousBest" => $existingTime,
                        "difficulty" => $difficulty,
                        "improved" => true,
                        "improvement" => $existingTime - $completionTime
                    )
                ));
            } else {
                // Insert new score
                $insertScoreStmt = $conn->prepare("INSERT INTO scores (user_id, completion_time, difficulty, timestamp) VALUES (?, ?, ?, NOW())");
                if (!$insertScoreStmt) {
                    die(json_encode(array("success" => false, "error" => 9, "message" => "Failed to prepare score insert statement: " . $conn->error)));
                }
                
                $insertScoreStmt->bind_param("ids", $userId, $completionTime, $difficulty);
                
                if (!$insertScoreStmt->execute()) {
                    die(json_encode(array("success" => false, "error" => 10, "message" => "Score insertion failed: " . $insertScoreStmt->error)));
                }
                
                $insertScoreStmt->close();
                
                echo json_encode(array(
                    "success" => true, 
                    "message" => "First completion time saved!",
                    "data" => array(
                        "username" => $username,
                        "completionTime" => $completionTime,
                        "difficulty" => $difficulty,
                        "improved" => true,
                        "firstTime" => true
                    )
                ));
            }
        }
        
    } catch(Exception $e) {
        die(json_encode(array("success" => false, "error" => 30, "message" => "Database error: " . $e->getMessage())));
    }
    
    $conn->close();
?>