<?php
/**
 * Valu-Basic SQLite Initialisierung v1.0
 * Erstellt alle Tabellen und den ersten Admin-User
 * Nach der Ausführung sofort löschen!
 */

require_once __DIR__ . '/config.php';

$sqlitePath = defined('SQLITE_PATH') ? SQLITE_PATH : __DIR__ . '/data/valu_basic.sqlite';
$dataDir = dirname($sqlitePath);

$error = '';
$success = false;
$tables_created = [];

// Schritt 1: data/ Verzeichnis erstellen
if (!is_dir($dataDir)) {
    mkdir($dataDir, 0755, true);
}

// Schritt 2: Admin-User Formular verarbeiten
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username  = trim($_POST['username'] ?? '');
    $password  = $_POST['password'] ?? '';   // KEIN trim() bei Passwörtern!
    $password2 = $_POST['password2'] ?? '';  // KEIN trim() bei Passwörtern!

    if (empty($username) || empty($password)) {
        $error = 'Alle Felder ausfüllen!';
    } elseif ($password !== $password2) {
        $error = 'Passwörter stimmen nicht überein!';
    } elseif (strlen($password) < 8) {
        $error = 'Passwort muss mindestens 8 Zeichen haben!';
    } else {
        try {
            $pdo = new PDO('sqlite:' . $sqlitePath, null, null, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            ]);
            $pdo->exec('PRAGMA journal_mode=WAL');
            $pdo->exec('PRAGMA foreign_keys=ON');

            // Alle Tabellen anlegen
            $statements = [
                // Orte
                "CREATE TABLE IF NOT EXISTS `orte` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `name` TEXT NOT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                // Kategorien
                "CREATE TABLE IF NOT EXISTS `kategorien` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `name` TEXT NOT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                // Standort-Räume
                // v4.1 Standort-Hierarchie: Raum → Standort → Position
                "CREATE TABLE IF NOT EXISTS `raeume` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `user_id` INTEGER DEFAULT NULL,
                    `name` TEXT NOT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                "CREATE TABLE IF NOT EXISTS `standorte` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `raum_id` INTEGER DEFAULT NULL,
                    `name` TEXT NOT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                "CREATE TABLE IF NOT EXISTS `positionen` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `standort_id` INTEGER DEFAULT NULL,
                    `name` TEXT NOT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                // Benutzer
                "CREATE TABLE IF NOT EXISTS `users` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `username` TEXT NOT NULL UNIQUE,
                    `password` TEXT DEFAULT NULL,
                    `email` TEXT DEFAULT NULL,
                    `role` TEXT DEFAULT 'read',
                    `theme` TEXT DEFAULT 'cloud',
                    `standard_ersteller` TEXT DEFAULT NULL,
                    `dokumente_aktiv` INTEGER DEFAULT 1,
                    `erstellt_am` TEXT DEFAULT (datetime('now')),
                    `letzter_login` TEXT DEFAULT NULL,
                    `spalten_auswahl` TEXT DEFAULT NULL,
                    `avatar` TEXT DEFAULT NULL,
                    `sieht_alle` INTEGER NOT NULL DEFAULT 0,
                    `last_seen_version` TEXT DEFAULT NULL
                )",
                // Wertsachen
                "CREATE TABLE IF NOT EXISTS `wertsachen` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `name` TEXT NOT NULL,
                    `ort_id` INTEGER DEFAULT NULL,
                    `kategorie_id` INTEGER DEFAULT NULL,
                    `kaufdatum` TEXT DEFAULT NULL,
                    `preis` REAL DEFAULT 0.00,
                    `aktueller_wert` REAL DEFAULT NULL,
                    `aktueller_wert_datum` TEXT DEFAULT NULL,
                    `notizen` TEXT DEFAULT NULL,
                    `bild` TEXT DEFAULT NULL,
                    `erstellt_von` TEXT DEFAULT NULL,
                    `gelistet_am` TEXT DEFAULT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now')),
                    `geaendert_am` TEXT DEFAULT (datetime('now')),
                    `hidden` INTEGER DEFAULT 0,
                    `custom1_wert` TEXT DEFAULT NULL,
                    `custom1_typ` TEXT DEFAULT 'text',
                    `custom2_wert` TEXT DEFAULT NULL,
                    `custom2_typ` TEXT DEFAULT 'text',
                    `barcode` TEXT DEFAULT NULL,
                    `versicherung_id` INTEGER DEFAULT NULL,
                    `oeffentlich` INTEGER NOT NULL DEFAULT 0,
                    `sort_order` INTEGER DEFAULT 0,
                    `standort_position_id` INTEGER DEFAULT NULL,
                    `raum_id` INTEGER DEFAULT NULL,
                    `standort_id` INTEGER DEFAULT NULL,
                    `position_id` INTEGER DEFAULT NULL,
                    `public_token` TEXT DEFAULT NULL,
                    `schadenfall` INTEGER DEFAULT 0
                )",
                // Bilder
                "CREATE TABLE IF NOT EXISTS `item_images` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `item_id` INTEGER NOT NULL,
                    `filename` TEXT NOT NULL,
                    `sort_order` INTEGER DEFAULT 0,
                    `is_primary` INTEGER DEFAULT 0,
                    `created_at` TEXT DEFAULT (datetime('now'))
                )",
                // Dokumente
                "CREATE TABLE IF NOT EXISTS `dokumente` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `wertsache_id` INTEGER NOT NULL,
                    `dateiname` TEXT NOT NULL,
                    `original_name` TEXT NOT NULL,
                    `dateityp` TEXT NOT NULL,
                    `dateigröße` INTEGER NOT NULL,
                    `hochgeladen_am` TEXT DEFAULT (datetime('now')),
                    `hochgeladen_von` TEXT DEFAULT NULL
                )",
                // Werthistorie
                "CREATE TABLE IF NOT EXISTS `wert_historie` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `wertsache_id` INTEGER NOT NULL,
                    `wert` REAL NOT NULL,
                    `datum` TEXT NOT NULL,
                    `notiz` TEXT DEFAULT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
                // App-Einstellungen
                "CREATE TABLE IF NOT EXISTS `app_settings` (
                    `setting_key` TEXT NOT NULL PRIMARY KEY,
                    `setting_value` TEXT DEFAULT NULL,
                    `updated_at` TEXT DEFAULT (datetime('now'))
                )",
                // Aktivitäts-Log
                "CREATE TABLE IF NOT EXISTS `activity_log` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `user_id` INTEGER DEFAULT NULL,
                    `aktion` TEXT NOT NULL,
                    `tabelle` TEXT NOT NULL,
                    `datensatz_id` INTEGER NOT NULL,
                    `bezeichnung` TEXT DEFAULT NULL,
                    `alt_wert` TEXT DEFAULT NULL,
                    `neu_wert` TEXT DEFAULT NULL,
                    `zeitstempel` TEXT DEFAULT (datetime('now')),
                    `ip_adresse` TEXT DEFAULT NULL
                )",
                // Login-Versuche
                "CREATE TABLE IF NOT EXISTS `login_attempts` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `username` TEXT NOT NULL,
                    `ip_address` TEXT NOT NULL,
                    `success` INTEGER NOT NULL DEFAULT 0,
                    `attempted_at` TEXT DEFAULT (datetime('now')),
                    `user_agent` TEXT DEFAULT NULL
                )",
                // Rate Limiting
                "CREATE TABLE IF NOT EXISTS `rate_limit` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `ip_address` TEXT NOT NULL,
                    `action` TEXT NOT NULL,
                    `attempts` INTEGER DEFAULT 1,
                    `first_attempt` TEXT NOT NULL,
                    `last_attempt` TEXT NOT NULL,
                    `locked_until` TEXT DEFAULT NULL
                )",
                // Security Log
                "CREATE TABLE IF NOT EXISTS `security_log` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `event_type` TEXT NOT NULL,
                    `user_id` INTEGER DEFAULT NULL,
                    `description` TEXT DEFAULT NULL,
                    `ip_address` TEXT DEFAULT NULL,
                    `created_at` TEXT DEFAULT (datetime('now'))
                )",
                // User-Aktivität
                "CREATE TABLE IF NOT EXISTS `user_activity` (
                    `user_id` INTEGER NOT NULL PRIMARY KEY,
                    `username` TEXT NOT NULL,
                    `role` TEXT NOT NULL DEFAULT 'read',
                    `last_seen` TEXT NOT NULL,
                    `current_page` TEXT DEFAULT NULL,
                    `ip_address` TEXT DEFAULT NULL
                )",
                // Berechtigungen
                "CREATE TABLE IF NOT EXISTS `permissions` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `action_key` TEXT NOT NULL UNIQUE,
                    `section` TEXT NOT NULL,
                    `icon` TEXT NOT NULL DEFAULT '',
                    `label` TEXT NOT NULL,
                    `sort_order` INTEGER NOT NULL DEFAULT 0,
                    `admin_can` INTEGER NOT NULL DEFAULT 1,
                    `edit_can` INTEGER NOT NULL DEFAULT 0,
                    `read_can` INTEGER NOT NULL DEFAULT 0,
                    `locked_for` TEXT DEFAULT NULL,
                    `created_at` TEXT DEFAULT (datetime('now'))
                )",
                // Versicherungen
                "CREATE TABLE IF NOT EXISTS `versicherungen` (
                    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                    `name` TEXT NOT NULL,
                    `anbieter` TEXT DEFAULT NULL,
                    `vertragsnummer` TEXT DEFAULT NULL,
                    `praemie` REAL DEFAULT NULL,
                    `laufzeit_bis` TEXT DEFAULT NULL,
                    `notiz` TEXT DEFAULT NULL,
                    `erstellt_am` TEXT DEFAULT (datetime('now'))
                )",
            ];

            foreach ($statements as $sql) {
                $pdo->exec($sql);
                preg_match('/TABLE IF NOT EXISTS `(\w+)`/', $sql, $m);
                if ($m[1]) $tables_created[] = $m[1];
            }

            // Standard-Einstellungen
            $settings = [
                ['instance_name', defined('APP_NAME') ? APP_NAME : 'Valu-Basic'],
                ['public_link_enabled', '0'],
                ['public_token', ''],
                ['backup_enabled', '0'],
                ['only_own_items', '0'],
                ['app_version', '2.0'],
                ['licence_valid', '0'],
                ['licence_tier', 'private'],
                ['licence_expires', ''],
                ['licence_days_left', '0'],
                ['licence_last_check', ''],
                ['sa_backup_php_allowed', '1'],
                ['sa_backup_download_allowed', '1'],
                ['sa_backup_images_allowed', '1'],
                ['sa_restore_allowed', '1'],
                ['public_show_price', '0'],
            ];
            $ins = $pdo->prepare("INSERT OR IGNORE INTO app_settings (setting_key, setting_value) VALUES (?, ?)");
            foreach ($settings as $s) { $ins->execute($s); }

            // Admin-User anlegen
            $hash = password_hash($password, PASSWORD_ARGON2ID);
            $pdo->prepare("INSERT OR REPLACE INTO users (username, password, role, theme, sieht_alle) VALUES (?, ?, 'admin', 'cloud', 1)")
                ->execute([$username, $hash]);

            // Standard-Orte
            $defaultOrte = ['Wohnzimmer', 'Schlafzimmer', 'Küche', 'Keller', 'Garage'];
            $insOrt = $pdo->prepare("INSERT INTO orte (name) VALUES (?)");
            foreach ($defaultOrte as $ort) { $insOrt->execute([$ort]); }

            // Standard-Kategorien
            $defaultKategorien = ['Elektronik', 'Möbel', 'Schmuck', 'Kleidung', 'Fahrrad', 'Musikinstrument', 'Sonstiges'];
            $insKat = $pdo->prepare("INSERT INTO kategorien (name) VALUES (?)");
            foreach ($defaultKategorien as $kat) { $insKat->execute([$kat]); }

            // Ort- und Kategorie-IDs abrufen
            $ortWohnzimmer = $pdo->query("SELECT id FROM orte WHERE name = 'Wohnzimmer'")->fetchColumn();
            $ortGarage     = $pdo->query("SELECT id FROM orte WHERE name = 'Garage'")->fetchColumn();
            $katElektronik = $pdo->query("SELECT id FROM kategorien WHERE name = 'Elektronik'")->fetchColumn();
            $katFahrrad    = $pdo->query("SELECT id FROM kategorien WHERE name = 'Fahrrad'")->fetchColumn();
            $katMusik      = $pdo->query("SELECT id FROM kategorien WHERE name = 'Musikinstrument'")->fetchColumn();

            // Demo-Gegenstände einfügen
            $demoItems = [
                [
                    'name'         => 'Laptop',
                    'notizen'      => 'Beispiel-Gegenstand. Bitte anpassen oder löschen.',
                    'preis'        => 1299.00,
                    'kaufdatum'    => '2024-03-15',
                    'ort_id'       => $ortWohnzimmer,
                    'kategorie_id' => $katElektronik,
                    'bild'         => 'demo_laptop.svg',
                    'erstellt_von' => $username,
                ],
                [
                    'name'         => 'City-Bike 28"',
                    'notizen'      => 'Beispiel-Gegenstand. Bitte anpassen oder löschen.',
                    'preis'        => 649.00,
                    'kaufdatum'    => '2023-05-20',
                    'ort_id'       => $ortGarage,
                    'kategorie_id' => $katFahrrad,
                    'bild'         => 'demo_fahrrad.svg',
                    'erstellt_von' => $username,
                ],
                [
                    'name'         => 'Westerngitarre',
                    'notizen'      => 'Beispiel-Gegenstand. Bitte anpassen oder löschen.',
                    'preis'        => 449.00,
                    'kaufdatum'    => '2022-12-01',
                    'ort_id'       => $ortWohnzimmer,
                    'kategorie_id' => $katMusik,
                    'bild'         => 'demo_gitarre.svg',
                    'erstellt_von' => $username,
                ],
            ];

            $insItem = $pdo->prepare("
                INSERT INTO wertsachen (name, notizen, preis, kaufdatum, ort_id, kategorie_id, bild, erstellt_von, erstellt_am)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
            ");
            foreach ($demoItems as $item) {
                $insItem->execute([
                    $item['name'], $item['notizen'], $item['preis'], $item['kaufdatum'],
                    $item['ort_id'], $item['kategorie_id'], $item['bild'], $item['erstellt_von'],
                ]);
            }

            // .htaccess zum Schutz der data/-Datei erstellen
            $htaccess = $dataDir . '/.htaccess';
            if (!file_exists($htaccess)) {
                file_put_contents($htaccess, "Deny from all\n");
            }

            $success = true;

        } catch (Exception $e) {
            $error = 'Fehler: ' . $e->getMessage();
        }
    }
}
?>
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Valu-Basic Setup</title>
<style>
* { box-sizing: border-box; margin: 0; padding: 0; }
body { font-family: Arial, sans-serif; background: #f0f4f8; padding: 40px 20px; }
.box { max-width: 480px; margin: 0 auto; background: white; padding: 32px; border-radius: 12px; box-shadow: 0 2px 12px rgba(0,0,0,0.1); }
h2 { color: #27ae60; margin-bottom: 6px; font-size: 22px; }
.subtitle { color: #7f8c8d; font-size: 13px; margin-bottom: 24px; }
label { display: block; font-size: 13px; font-weight: 600; color: #555; margin-bottom: 5px; }
input { width: 100%; padding: 10px 12px; border: 1px solid #ddd; border-radius: 6px; font-size: 14px; margin-bottom: 14px; }
button { width: 100%; padding: 12px; background: #27ae60; color: white; border: none; border-radius: 6px; font-size: 15px; font-weight: 600; cursor: pointer; }
button:hover { background: #219a52; }
.error { background: #f8d7da; color: #721c24; padding: 12px; border-radius: 6px; margin-bottom: 16px; font-size: 13px; }
.success-box { text-align: center; }
.success-box h3 { color: #27ae60; font-size: 20px; margin-bottom: 12px; }
.success-box p { color: #555; font-size: 14px; margin-bottom: 16px; }
.btn-login { display: inline-block; padding: 12px 32px; background: #27ae60; color: white; border-radius: 6px; text-decoration: none; font-weight: 600; font-size: 15px; }
.table-list { font-size: 12px; color: #888; margin: 12px 0; columns: 2; }
.warn { background: #fff3cd; border: 1px solid #ffc107; padding: 12px; border-radius: 6px; margin-top: 20px; font-size: 12px; color: #856404; }
</style>
</head>
<body>
<div class="box">
    <h2>🟢 Valu-Basic Setup</h2>
    <p class="subtitle">Datenbank initialisieren und Admin-Konto anlegen</p>

    <?php if ($success): ?>
    <div class="success-box">
        <h3>✅ Installation erfolgreich!</h3>
        <p>Datenbank angelegt mit <?php echo count($tables_created); ?> Tabellen.<br>
        Admin-Konto ist bereit. Orte, Kategorien und 3 Demo-Gegenstände wurden angelegt.</p>
        <div class="table-list">
            <?php foreach ($tables_created as $t): ?><?php echo $t; ?><br><?php endforeach; ?>
        </div>
        <a href="login.php" class="btn-login">→ Jetzt einloggen</a>

        <!-- Optionale Registrierung -->
        <div id="reg-box" style="margin-top:28px; padding:20px; background:#f0fdf4; border:1px solid #86efac; border-radius:10px; text-align:left;">
            <p style="font-weight:600; color:#166534; margin-bottom:6px;">📬 Bleiben Sie informiert</p>
            <p style="font-size:13px; color:#555; margin-bottom:14px;">
                Tragen Sie Ihre E-Mail ein um über Updates und neue Versionen informiert zu werden.<br>
                <em style="color:#888;">Freiwillig · Kein Spam · Jederzeit abbestellbar</em>
            </p>
            <div style="display:flex; gap:8px; flex-wrap:wrap;">
                <input type="email" id="reg-email" placeholder="ihre@email.de"
                       style="flex:1; min-width:200px; padding:10px 12px; border:1px solid #d1d5db; border-radius:6px; font-size:14px;">
                <button type="button" onclick="registerEmail()"
                        style="padding:10px 20px; background:#27ae60; color:white; border:none; border-radius:6px; cursor:pointer; font-size:14px; font-weight:600;">
                    Eintragen
                </button>
                <button type="button" onclick="document.getElementById('reg-box').style.display='none'"
                        style="padding:10px 16px; background:transparent; border:1px solid #d1d5db; border-radius:6px; cursor:pointer; font-size:13px; color:#666;">
                    Nein danke
                </button>
            </div>
            <div id="reg-result" style="display:none; margin-top:10px; font-size:13px;"></div>
        </div>

        <script>
        function registerEmail() {
            const email = document.getElementById('reg-email').value.trim();
            const result = document.getElementById('reg-result');
            if (!email || !email.includes('@')) {
                result.style.display = 'block';
                result.style.color = '#dc2626';
                result.textContent = '❌ Bitte eine gültige E-Mail-Adresse eingeben.';
                return;
            }
            result.style.display = 'block';
            result.style.color = '#888';
            result.textContent = '⏳ Wird eingetragen…';
            fetch('https://valu-basic.palindrom.de/register_api.php', {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ email: email, version: '2.0', source: 'init_sqlite' })
            })
            .then(r => r.json())
            .then(data => {
                if (data.success) {
                    result.style.color = '#166534';
                    result.textContent = '✅ Vielen Dank! Sie werden über Updates informiert.';
                    document.getElementById('reg-email').disabled = true;
                    document.querySelector('#reg-box button:first-of-type').disabled = true;
                } else {
                    result.style.color = '#dc2626';
                    result.textContent = '❌ ' + (data.message || 'Fehler beim Eintragen.');
                }
            })
            .catch(() => {
                result.style.color = '#dc2626';
                result.textContent = '❌ Verbindung fehlgeschlagen. Bitte später versuchen.';
            });
        }
        </script>
    </div>
    <?php else: ?>

    <?php if ($error): ?>
        <div class="error">❌ <?php echo htmlspecialchars($error); ?></div>
    <?php endif; ?>

    <form method="POST">
        <label>Admin-Benutzername</label>
        <input type="text" name="username" value="<?php echo htmlspecialchars($_POST['username'] ?? 'admin'); ?>" required autofocus>

        <label>Passwort (min. 8 Zeichen)</label>
        <input type="password" name="password" required>

        <label>Passwort bestätigen</label>
        <input type="password" name="password2" required>

        <button type="submit">🚀 Installieren</button>
    </form>
    <?php endif; ?>

    <div class="warn">⚠️ <strong>Diese Datei nach dem Setup sofort löschen!</strong> (init_sqlite.php)</div>
</div>
</body>
</html>
