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

// ========== CONFIG ==========
$dbHost = 'localhost';
$dbName = 'porihoqu_ticket';
$dbUser = 'porihoqu_ticket';
$dbPass = ']78P*%Dt5^FVRf2B';

$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($mysqli->connect_error) {
    http_response_code(500);
    echo json_encode(['error' => 'DB connection failed: ' . $mysqli->connect_error]);
    exit;
}

// Read input
$input = json_decode(file_get_contents('php://input'), true);
if (!isset($input['users']) || !is_array($input['users'])) {
    http_response_code(400);
    echo json_encode(['error' => 'Invalid input: "users" array expected']);
    exit;
}

$users = $input['users'];
if (empty($users)) {
    echo json_encode(['message' => 'No users to process', 'processed' => 0]);
    exit;
}

// Prepare statements (maps scraper fields to table columns)
$checkUserStmt = $mysqli->prepare("SELECT recharge_id, username, name, address, phone, expire_at FROM users WHERE user_id = ?");
$checkUsernameStmt = $mysqli->prepare("SELECT recharge_id, username, name, address, phone, expire_at FROM users WHERE username = ?");
$updateUserStmt = $mysqli->prepare("UPDATE users SET recharge_id = ?, username = ?, name = ?, address = ?, phone = ?, expire_at = ? WHERE user_id = ?");
$updateUsernameStmt = $mysqli->prepare("UPDATE users SET recharge_id = ?, username = ?, name = ?, address = ?, phone = ?, expire_at = ? WHERE username = ?");
$insertStmt = $mysqli->prepare("INSERT INTO users (recharge_id, user_id, username, name, address, phone, expire_at) VALUES (?, ?, ?, ?, ?, ?, ?)");

if (!$checkUserStmt || !$checkUsernameStmt || !$updateUserStmt || !$updateUsernameStmt || !$insertStmt) {
    http_response_code(500);
    echo json_encode(['error' => 'Prepare statements failed: ' . $mysqli->error]);
    exit;
}

$processed = 0;
$inserted = 0;
$updated = 0;
$skipped = 0;
$errors = [];

foreach ($users as $user) {
    // Map from scraper's field names
    $rechargeId = $user['recharge_id'] ?? '';
    $userId     = $user['user_id'] ?? '';
    $username   = $user['username'] ?? '';
    $name       = $user['name'] ?? '';
    $address    = $user['address'] ?? '';
    $phone      = $user['phone'] ?? '';
    $expireAt   = $user['expire_at'] ?? '';

    if (empty($userId) && empty($username)) {
        $errors[] = "Skipped record without user_id or username: " . json_encode($user);
        continue;
    }

    $existing = false;
    $dbRechargeId = $dbUsername = $dbName = $dbAddress = $dbPhone = $dbExpireAt = null;
    $checkByUserId = !empty($userId);

    if ($checkByUserId) {
        $checkUserStmt->bind_param('s', $userId);
        if ($checkUserStmt->execute()) {
            $checkUserStmt->store_result();
            if ($checkUserStmt->num_rows > 0) {
                $checkUserStmt->bind_result($dbRechargeId, $dbUsername, $dbName, $dbAddress, $dbPhone, $dbExpireAt);
                $checkUserStmt->fetch();
                $existing = true;
            }
            $checkUserStmt->free_result();
        } else {
            $errors[] = "Check query failed for user_id {$userId}: " . $checkUserStmt->error;
            continue;
        }
    } else {
        $checkUsernameStmt->bind_param('s', $username);
        if ($checkUsernameStmt->execute()) {
            $checkUsernameStmt->store_result();
            if ($checkUsernameStmt->num_rows > 0) {
                $checkUsernameStmt->bind_result($dbRechargeId, $dbUsername, $dbName, $dbAddress, $dbPhone, $dbExpireAt);
                $checkUsernameStmt->fetch();
                $existing = true;
            }
            $checkUsernameStmt->free_result();
        } else {
            $errors[] = "Check query failed for username {$username}: " . $checkUsernameStmt->error;
            continue;
        }
    }

    if ($existing) {
        // User exists, check for changes
        $dbRechargeId = $dbRechargeId ?? '';
        $dbUsername   = $dbUsername ?? '';
        $dbName       = $dbName ?? '';
        $dbAddress    = $dbAddress ?? '';
        $dbPhone      = $dbPhone ?? '';
        $dbExpireAt   = $dbExpireAt ?? '';

        $hasChanges = (
            $dbRechargeId !== $rechargeId ||
            $dbUsername !== $username ||
            $dbName !== $name ||
            $dbAddress !== $address ||
            $dbPhone !== $phone ||
            $dbExpireAt !== $expireAt
        );

        if ($hasChanges) {
            // Update
            if ($checkByUserId) {
                $updateUserStmt->bind_param('sssssss', $rechargeId, $username, $name, $address, $phone, $expireAt, $userId);
                if ($updateUserStmt->execute()) {
                    $processed++;
                    $updated++;
                } else {
                    $errors[] = "Update failed for user_id {$userId}: " . $updateUserStmt->error;
                }
            } else {
                $updateUsernameStmt->bind_param('sssssss', $rechargeId, $username, $name, $address, $phone, $expireAt, $username);
                if ($updateUsernameStmt->execute()) {
                    $processed++;
                    $updated++;
                } else {
                    $errors[] = "Update failed for username {$username}: " . $updateUsernameStmt->error;
                }
            }
        } else {
            // No changes, skip updating
            $skipped++;
        }
    } else {
        // User does not exist, insert
        $insertStmt->bind_param('sssssss', $rechargeId, $userId, $username, $name, $address, $phone, $expireAt);
        if ($insertStmt->execute()) {
            $processed++;
            $inserted++;
        } else {
            $errors[] = "Insert failed for user_id {$userId}: " . $insertStmt->error;
        }
    }
}

$checkUserStmt->close();
$checkUsernameStmt->close();
$updateUserStmt->close();
$updateUsernameStmt->close();
$insertStmt->close();
$mysqli->close();

echo json_encode([
    'message' => 'Bulk upsert completed',
    'received' => count($users),
    'processed' => $processed,
    'inserted' => $inserted,
    'updated' => $updated,
    'skipped' => $skipped,
    'errors' => $errors
]);