<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

try {
    $db = new PDO('sqlite:database.db');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    http_response_code(500);
    exit("DB connection failed: " . $e->getMessage());
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST' || !isset($_POST['data'])) {
    http_response_code(400);
    exit("No data received");
}

$data = json_decode($_POST['data'], true);

if (json_last_error() !== JSON_ERROR_NONE) {
    http_response_code(400);
    exit("Invalid JSON");
}

try {
    $db->beginTransaction();

    foreach ($data as $row) {

        $user_id = $row['user_id'];
        $name = $row['name'];

        // CHECK IF USER EXISTS
        $check = $db->prepare("SELECT user_id FROM result WHERE user_id = ?");
        $check->execute([$user_id]);
        $exists = $check->fetch();

        if (!$exists) {
            // INSERT EMPTY ROW FIRST (IMPORTANT)
            $insert = $db->prepare("
                INSERT INTO result (user_id, name, \"1\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"8\")
                VALUES (?, ?, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
            ");
            $insert->execute([$user_id, $name]);
        }

        foreach ($row as $column => $value) {

            if ($column === 'user_id' || $column === 'name') {
                continue;
            }

            if ($value === null || $value === '') {
                continue; // VERY IMPORTANT FIX
            }

            if (!in_array($column, ["1", "2", "3", "4", "5", "6", "7", "8"])) {
                continue;
            }

            $update = $db->prepare("
        UPDATE result
        SET \"$column\" = :value
        WHERE user_id = :user_id
    ");

            $update->execute([
                ':value' => $value,
                ':user_id' => $user_id
            ]);
        }
    }

    $db->commit();

    echo json_encode(
        "Scores Uploaded Successfully"
    );
} catch (Exception $e) {
    $db->rollBack();
    http_response_code(500);

    echo json_encode([
        "status" => "error",
        "message" => $e->getMessage()
    ]);
}
