<?php
// Veritabanı kurulum scripti
header('Content-Type: text/html; charset=utf-8');

echo "<h1>DMAppsESP Veritabanı Kurulum</h1>";

// Database configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'dmapesp');

try {
    // Ana veritabanı bağlantısı (veritabanı seçmeden)
    $con = new mysqli(DB_HOST, DB_USER, DB_PASS);
    
    if ($con->connect_error) {
        throw new Exception("Bağlantı hatası: " . $con->connect_error);
    }
    
    echo "<p style='color: green;'>✅ MySQL bağlantısı başarılı</p>";
    
    // Veritabanını oluştur
    $sql = "CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
    if ($con->query($sql)) {
        echo "<p style='color: green;'>✅ Veritabanı oluşturuldu: " . DB_NAME . "</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ Veritabanı zaten mevcut</p>";
    }
    
    // Veritabanını seç
    $con->select_db(DB_NAME);
    
    // Tabloları oluştur
    $tables = [
        // Kullanıcılar tablosu
        "CREATE TABLE IF NOT EXISTS `users` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `username` varchar(50) NOT NULL UNIQUE,
            `email` varchar(100) NOT NULL UNIQUE,
            `password_hash` varchar(255) NOT NULL,
            `role` enum('user','admin') DEFAULT 'user',
            `first_name` varchar(50) DEFAULT NULL,
            `last_name` varchar(50) DEFAULT NULL,
            `phone` varchar(20) DEFAULT NULL,
            `avatar_url` varchar(255) DEFAULT NULL,
            `bio` text DEFAULT NULL,
            `preferences` json DEFAULT NULL,
            `two_factor_enabled` tinyint(1) DEFAULT 0,
            `session_timeout` int(11) DEFAULT 1800,
            `login_attempts` int(11) DEFAULT 0,
            `failed_logins` int(11) DEFAULT 0,
            `password_changed` datetime DEFAULT NULL,
            `last_login` datetime DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_username` (`username`),
            KEY `idx_email` (`email`),
            KEY `idx_role` (`role`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Cihazlar tablosu
        "CREATE TABLE IF NOT EXISTS `devices` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `device_id` varchar(50) NOT NULL UNIQUE,
            `user_id` int(11) NOT NULL,
            `name` varchar(100) NOT NULL,
            `description` text DEFAULT NULL,
            `esp_model` enum('ESP32','ESP32-S2','ESP32-S3','ESP32-C3') DEFAULT 'ESP32',
            `firmware_version` varchar(20) DEFAULT NULL,
            `mac_address` varchar(17) DEFAULT NULL,
            `ip_address` varchar(15) DEFAULT NULL,
            `wifi_ssid` varchar(50) DEFAULT NULL,
            `wifi_strength` int(11) DEFAULT NULL,
            `status` enum('online','offline','error') DEFAULT 'offline',
            `last_seen` datetime DEFAULT NULL,
            `last_heartbeat` datetime DEFAULT NULL,
            `uptime` int(11) DEFAULT 0,
            `free_heap` int(11) DEFAULT 0,
            `cpu_temp` decimal(5,2) DEFAULT NULL,
            `config` json DEFAULT NULL,
            `location` json DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uk_device_id` (`device_id`),
            KEY `idx_user_id` (`user_id`),
            KEY `idx_status` (`status`),
            KEY `idx_last_seen` (`last_seen`),
            FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Sensörler tablosu
        "CREATE TABLE IF NOT EXISTS `sensors` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `sensor_code` varchar(50) NOT NULL UNIQUE,
            `name` varchar(100) NOT NULL,
            `unit` varchar(20) NOT NULL,
            `type` enum('digital','analog','i2c','spi','uart','onewire','system') NOT NULL,
            `min_value` decimal(10,4) DEFAULT NULL,
            `max_value` decimal(10,4) DEFAULT NULL,
            `precision_value` int(11) DEFAULT 2,
            `description` text DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uk_sensor_code` (`sensor_code`),
            KEY `idx_type` (`type`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Sensör atamaları tablosu
        "CREATE TABLE IF NOT EXISTS `sensor_assignments` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `device_id` int(11) NOT NULL,
            `sensor_id` int(11) NOT NULL,
            `sensor_code` varchar(50) NOT NULL,
            `pin` int(11) DEFAULT NULL,
            `enabled` tinyint(1) DEFAULT 1,
            `config` json DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_device_id` (`device_id`),
            KEY `idx_sensor_id` (`sensor_id`),
            KEY `idx_sensor_code` (`sensor_code`),
            FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE,
            FOREIGN KEY (`sensor_id`) REFERENCES `sensors`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Sensör verileri tablosu
        "CREATE TABLE IF NOT EXISTS `sensor_data` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `device_id` int(11) NOT NULL,
            `sensor_code` varchar(50) NOT NULL,
            `value` decimal(15,4) NOT NULL,
            `unit` varchar(20) NOT NULL,
            `quality` enum('good','fair','poor') DEFAULT 'good',
            `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_device_id` (`device_id`),
            KEY `idx_sensor_code` (`sensor_code`),
            KEY `idx_timestamp` (`timestamp`),
            KEY `idx_device_sensor` (`device_id`,`sensor_code`,`timestamp`),
            FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Cihaz logları tablosu
        "CREATE TABLE IF NOT EXISTS `device_logs` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `device_id` int(11) DEFAULT NULL,
            `user_id` int(11) DEFAULT NULL,
            `level` enum('ERROR','WARNING','INFO','DEBUG') NOT NULL,
            `message` text NOT NULL,
            `context` json DEFAULT NULL,
            `ip_address` varchar(15) DEFAULT NULL,
            `user_agent` text DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_device_id` (`device_id`),
            KEY `idx_user_id` (`user_id`),
            KEY `idx_level` (`level`),
            KEY `idx_created_at` (`created_at`),
            FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE SET NULL,
            FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Kullanıcı aktivitesi tablosu
        "CREATE TABLE IF NOT EXISTS `user_activity` (
            `id` bigint(20) NOT NULL AUTO_INCREMENT,
            `user_id` int(11) NOT NULL,
            `action` varchar(50) NOT NULL,
            `description` text DEFAULT NULL,
            `ip_address` varchar(15) DEFAULT NULL,
            `user_agent` text DEFAULT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_user_id` (`user_id`),
            KEY `idx_action` (`action`),
            KEY `idx_created_at` (`created_at`),
            FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        
        // Firmware versiyonları tablosu
        "CREATE TABLE IF NOT EXISTS `firmware_versions` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `device_type` enum('ESP32','ESP32-S2','ESP32-S3','ESP32-C3') NOT NULL,
            `version` varchar(20) NOT NULL,
            `file_path` varchar(255) NOT NULL,
            `file_size` int(11) DEFAULT NULL,
            `checksum` varchar(64) DEFAULT NULL,
            `description` text DEFAULT NULL,
            `is_stable` tinyint(1) DEFAULT 1,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            KEY `idx_device_type` (`device_type`),
            KEY `idx_version` (`version`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    ];
    
    // Tabloları oluştur
    foreach ($tables as $sql) {
        if ($con->query($sql)) {
            echo "<p style='color: green;'>✅ Tablo oluşturuldu</p>";
        } else {
            echo "<p style='color: red;'>❌ Tablo hatası: " . $con->error . "</p>";
        }
    }
    
    // Varsayılan admin kullanıcısı oluştur
    $admin_username = 'admin';
    $admin_email = 'admin@dmapesp.com';
    $admin_password = password_hash('admin123', PASSWORD_DEFAULT);
    
    $sql = "INSERT IGNORE INTO `users` (`username`, `email`, `password_hash`, `role`, `first_name`, `last_name`) 
            VALUES (?, ?, ?, 'admin', 'Admin', 'User')";
    
    $stmt = $con->prepare($sql);
    $stmt->bind_param("sss", $admin_username, $admin_email, $admin_password);
    
    if ($stmt->execute()) {
        echo "<p style='color: green;'>✅ Admin kullanıcısı oluşturuldu</p>";
        echo "<p style='color: blue;'>📝 Kullanıcı adı: admin</p>";
        echo "<p style='color: blue;'>📝 Şifre: admin123</p>";
    } else {
        echo "<p style='color: orange;'>⚠️ Admin kullanıcısı zaten mevcut</p>";
    }
    
    // Varsayılan sensörleri ekle
    $default_sensors = [
        ['DHT22_TEMP', 'DHT22 Sıcaklık', '°C', 'digital'],
        ['DHT22_HUM', 'DHT22 Nem', '%', 'digital'],
        ['BMP280_TEMP', 'BMP280 Sıcaklık', '°C', 'i2c'],
        ['BMP280_PRESS', 'BMP280 Basınç', 'hPa', 'i2c'],
        ['LDR_LIGHT', 'LDR Işık', 'adc', 'analog'],
        ['PIR_MOTION', 'PIR Hareket', 'bool', 'digital'],
        ['ULTRASONIC_DIST', 'HC-SR04 Mesafe', 'cm', 'digital'],
        ['RELAY_1', 'Röle 1', 'bool', 'digital'],
        ['BUTTON_1', 'Buton 1', 'bool', 'digital']
    ];
    
    foreach ($default_sensors as $sensor) {
        $sql = "INSERT IGNORE INTO `sensors` (`sensor_code`, `name`, `unit`, `type`) VALUES (?, ?, ?, ?)";
        $stmt = $con->prepare($sql);
        $stmt->bind_param("ssss", $sensor[0], $sensor[1], $sensor[2], $sensor[3]);
        $stmt->execute();
    }
    
    echo "<p style='color: green;'>✅ Varsayılan sensörler eklendi</p>";
    
    $con->close();
    
    echo "<h2 style='color: green;'>✅ Veritabanı kurulumu tamamlandı!</h2>";
    echo "<p><a href='index.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>Ana Sayfaya Git</a></p>";
    
} catch (Exception $e) {
    echo "<h2 style='color: red;'>❌ Hata oluştu!</h2>";
    echo "<p style='color: red;'>" . $e->getMessage() . "</p>";
    
    // XAMPP kontrolü
    if (strpos($e->getMessage(), 'Access denied') !== false) {
        echo "<h3>Çözüm Önerileri:</h3>";
        echo "<ol>";
        echo "<li>XAMPP kontrol panelini açın</li>";
        echo "<li>MySQL servisinin çalıştığından emin olun</li>";
        echo "<li>MySQL şifresini kontrol edin (varsayılan: boş)</li>";
        echo "<li>config/database.php dosyasını kontrol edin</li>";
        echo "</ol>";
    }
}
?>
