<?php
// fix_db.php
$dbFile = __DIR__ . '/database.sqlite';
if (!file_exists($dbFile)) {
    die('database.sqlite not found');
}
$pdo = new PDO('sqlite:' . $dbFile);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Sort accounts so the oldest (smallest ID) is the survivor
$stmt = $pdo->query("SELECT id, data FROM accounts ORDER BY id ASC");
$accounts = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $data = json_decode($row['data'], true);
    $accounts[$row['id']] = $data;
}

$essentialCodes = ['1', '2', '3', '4', '5'];
$survivors = [];
$toDelete = [];

foreach ($accounts as $id => $data) {
    $code = isset($data['code']) ? (string)$data['code'] : '';
    if (in_array($code, $essentialCodes, true)) {
        if (!isset($survivors[$code])) {
            $survivors[$code] = $id;
        } else {
            $toDelete[] = $id;
        }
    }
}

// Now fix all orphaned children
$updatedCount = 0;
foreach ($accounts as $id => $data) {
    $pid = isset($data['parent_id']) ? $data['parent_id'] : null;
    if (in_array($pid, $toDelete)) {
        // This child belongs to a deleted duplicate!
        // Find which code the duplicate was
        $duplicateCode = $accounts[$pid]['code'];
        $survivorId = $survivors[$duplicateCode];
        $data['parent_id'] = $survivorId;
        $stmtUpdate = $pdo->prepare("UPDATE accounts SET data = ? WHERE id = ?");
        $stmtUpdate->execute([json_encode($data, JSON_UNESCAPED_UNICODE), $id]);
        $updatedCount++;
    }
}

// Now delete the duplicates
foreach ($toDelete as $delId) {
    $pdo->exec("DELETE FROM accounts WHERE id = " . (int)$delId);
}

echo "Fixed DB successfully! Deleted " . count($toDelete) . " duplicates. Re-parented " . $updatedCount . " children.";
