<?php
/**
 * Valu-Basic v2.0 — Migrations-Cleanup
 * Bereinigt Daten nach dem Upgrade auf v2.0
 * Zugriff: cleanup_v2.php?token=valu_cleanup_2026
 * Nach Verwendung sofort löschen!
 */

$token = $_GET['token'] ?? '';
if ($token !== 'valu_cleanup_2026') {
    http_response_code(403);
    die('Kein Zugriff.');
}

require_once __DIR__ . '/config.php';

$sqlitePath = defined('SQLITE_PATH') ? SQLITE_PATH : __DIR__ . '/data/valu_basic.sqlite';
$pdo = new PDO('sqlite:' . $sqlitePath, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('PRAGMA foreign_keys=OFF');

$results = [];

// 1. Doppelte Kategorien bereinigen
$dupes = $pdo->query("SELECT name, COUNT(*) as cnt FROM kategorien GROUP BY name HAVING cnt > 1")->fetchAll();
foreach ($dupes as $dup) {
    $name = $pdo->quote($dup['name']);
    $ids = $pdo->query("SELECT id FROM kategorien WHERE name = {$name} ORDER BY id ASC")->fetchAll(PDO::FETCH_COLUMN);
    $keep = $ids[0];
    foreach (array_slice($ids, 1) as $del) {
        $pdo->exec("UPDATE wertsachen SET kategorie_id = $keep WHERE kategorie_id = $del");
        $pdo->exec("DELETE FROM kategorien WHERE id = $del");
    }
    $results[] = ['ok' => true, 'msg' => "Kategorie '{$dup['name']}': " . (count($ids)-1) . " Duplikat(e) entfernt"];
}
if (empty($dupes)) $results[] = ['ok' => null, 'msg' => 'Keine doppelten Kategorien gefunden'];

// 2. Alte Orte in neue Raeume migrieren
$orte = $pdo->query("SELECT * FROM orte ORDER BY id ASC")->fetchAll();
$ort_to_raum = [];
foreach ($orte as $ort) {
    $name = $pdo->quote($ort['name']);
    $existing = $pdo->query("SELECT id FROM raeume WHERE name = {$name} LIMIT 1")->fetch();
    if ($existing) {
        $ort_to_raum[$ort['id']] = $existing['id'];
    } else {
        $pdo->prepare("INSERT INTO raeume (name) VALUES (?)")->execute([$ort['name']]);
        $raum_id = $pdo->lastInsertId();
        $ort_to_raum[$ort['id']] = $raum_id;
        $results[] = ['ok' => true, 'msg' => "Ort '{$ort['name']}' als Raum migriert (ID $raum_id)"];
    }
}

// Gegenstaende: ort_id auf raum_id setzen
$migrated = 0;
foreach ($ort_to_raum as $ort_id => $raum_id) {
    $affected = $pdo->exec("UPDATE wertsachen SET raum_id = $raum_id WHERE ort_id = $ort_id AND (raum_id IS NULL OR raum_id = 0)");
    $migrated += $affected;
}
if ($migrated > 0) $results[] = ['ok' => true, 'msg' => "$migrated Gegenstaende: ort_id auf raum_id migriert"];

// 3. erstellt_von befuellen
$admin = $pdo->query("SELECT username FROM users WHERE role = 'admin' LIMIT 1")->fetchColumn();
if ($admin) {
    $adminQ = $pdo->quote($admin);
    $affected = $pdo->exec("UPDATE wertsachen SET erstellt_von = {$adminQ} WHERE (erstellt_von IS NULL OR erstellt_von = '')");
    $results[] = ['ok' => true, 'msg' => "$affected Gegenstaende: erstellt_von auf '$admin' gesetzt"];
}

// 4. only_own_items deaktivieren
$pdo->exec("UPDATE app_settings SET setting_value = '0' WHERE setting_key = 'only_own_items'");
$results[] = ['ok' => true, 'msg' => "only_own_items auf 0 gesetzt"];

// 5. Doppelte Orte bereinigen
$dupes_orte = $pdo->query("SELECT name, COUNT(*) as cnt FROM orte GROUP BY name HAVING cnt > 1")->fetchAll();
foreach ($dupes_orte as $dup) {
    $name = $pdo->quote($dup['name']);
    $ids = $pdo->query("SELECT id FROM orte WHERE name = {$name} ORDER BY id ASC")->fetchAll(PDO::FETCH_COLUMN);
    $keep = $ids[0];
    foreach (array_slice($ids, 1) as $del) {
        $pdo->exec("UPDATE wertsachen SET ort_id = $keep WHERE ort_id = $del");
        $pdo->exec("DELETE FROM orte WHERE id = $del");
    }
    $results[] = ['ok' => true, 'msg' => "Ort '{$dup['name']}': " . (count($ids)-1) . " Duplikat(e) entfernt"];
}

// 6. Zaehlung
$kat  = $pdo->query("SELECT COUNT(*) FROM kategorien")->fetchColumn();
$raum = $pdo->query("SELECT COUNT(*) FROM raeume")->fetchColumn();
$wert = $pdo->query("SELECT COUNT(*) FROM wertsachen")->fetchColumn();
$results[] = ['ok' => null, 'msg' => "Ergebnis: $wert Gegenstaende, $raum Raeume, $kat Kategorien"];

$pdo->exec('PRAGMA foreign_keys=ON');

$ok = count(array_filter($results, fn($r) => $r['ok'] === true));
$info = count(array_filter($results, fn($r) => $r['ok'] === null));
?>
<!DOCTYPE html><html lang="de"><head><meta charset="utf-8"><title>Cleanup v2.0</title>
<style>body{font-family:monospace;background:#111;color:#eee;padding:2rem;max-width:700px;margin:0 auto}
h1{color:#f0a500}.ok{color:#4caf50}.info{color:#888}
.sum{background:#1e2030;padding:1rem;border-radius:8px;margin:1rem 0}</style></head>
<body>
<h1>🧹 Valu-Basic v2.0 Cleanup</h1>
<div class="sum"><span class="ok">✅ <?= $ok ?> Bereinigungen</span> &nbsp;|&nbsp; <span class="info">ℹ️ <?= $info ?> Info</span></div>
<ul>
<?php foreach ($results as $r): ?>
<li class="<?= $r['ok'] ? 'ok' : 'info' ?>"><?= $r['ok'] ? '✅' : 'ℹ️' ?> <?= htmlspecialchars($r['msg']) ?></li>
<?php endforeach; ?>
</ul>
<hr style="border-color:#333;margin-top:2rem">
<p style="color:#f44336">⚠️ Diese Datei sofort loeschen!</p>
</body></html>
