<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "=== IoT Estimation System Setup ===\n\n";

require_once __DIR__ . '/../config/database.php';

$database = new Database();
$conn = $database->getConnection();

if (!$conn) {
    die("❌ Database connection failed!\n");
}

echo "✅ Database connected\n\n";

// تنفيذ الاستعلامات مباشرة
$queries = [
    // جدول المشاريع
    "CREATE TABLE IF NOT EXISTS iot_projects (
        id INT AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(100) NOT NULL,
        customer_phone VARCHAR(20) NOT NULL,
        customer_email VARCHAR(100),
        project_type ENUM('house', 'apartment', 'villa', 'office', 'other') DEFAULT 'house',
        status ENUM('new', 'in_progress', 'completed', 'cancelled') DEFAULT 'new',
        total_cost DECIMAL(10, 2) DEFAULT 0.00,
        discount_percentage DECIMAL(5, 2) DEFAULT 0.00,
        discount_amount DECIMAL(10, 2) DEFAULT 0.00,
        final_cost DECIMAL(10, 2) DEFAULT 0.00,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        created_by INT,
        INDEX idx_customer_phone (customer_phone),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    // جدول الغرف
    "CREATE TABLE IF NOT EXISTS iot_project_rooms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        project_id INT NOT NULL,
        room_name VARCHAR(100) NOT NULL,
        room_type ENUM('bedroom', 'living_room', 'kitchen', 'bathroom', 'office', 'garage', 'garden', 'other') DEFAULT 'bedroom',
        area_sqm DECIMAL(6, 2),
        floor_number INT DEFAULT 1,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (project_id) REFERENCES iot_projects(id) ON DELETE CASCADE,
        INDEX idx_project_id (project_id),
        INDEX idx_room_type (room_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    // جدول الأجهزة
    "CREATE TABLE IF NOT EXISTS iot_project_devices (
        id INT AUTO_INCREMENT PRIMARY KEY,
        room_id INT NOT NULL,
        device_type VARCHAR(50) NOT NULL,
        device_name VARCHAR(100) NOT NULL,
        device_model VARCHAR(100),
        quantity INT DEFAULT 1,
        unit_price DECIMAL(10, 2) NOT NULL,
        installation_cost DECIMAL(10, 2) DEFAULT 0.00,
        total_price DECIMAL(10, 2) NOT NULL,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (room_id) REFERENCES iot_project_rooms(id) ON DELETE CASCADE,
        INDEX idx_room_id (room_id),
        INDEX idx_device_type (device_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    // جدول قوالب الأجهزة
    "CREATE TABLE IF NOT EXISTS iot_device_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        device_name VARCHAR(100) NOT NULL,
        device_type VARCHAR(50) NOT NULL,
        category ENUM('lighting', 'control', 'security', 'entertainment', 'climate', 'sensors', 'other') DEFAULT 'other',
        default_price DECIMAL(10, 2) NOT NULL,
        installation_cost DECIMAL(10, 2) DEFAULT 0.00,
        description TEXT,
        image_url VARCHAR(255),
        specifications JSON,
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_category (category),
        INDEX idx_device_type (device_type),
        INDEX idx_is_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    
    // جدول قوالب الغرف
    "CREATE TABLE IF NOT EXISTS iot_room_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        room_name VARCHAR(100) NOT NULL,
        room_type ENUM('bedroom', 'living_room', 'kitchen', 'bathroom', 'office', 'garage', 'garden', 'other') DEFAULT 'bedroom',
        default_devices JSON,
        description TEXT,
        estimated_cost DECIMAL(10, 2),
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_room_type (room_type),
        INDEX idx_is_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
];

echo "Creating tables...\n";
foreach ($queries as $i => $query) {
    try {
        $conn->exec($query);
        echo "✅ Table " . ($i + 1) . " created\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'already exists') !== false) {
            echo "ℹ️  Table " . ($i + 1) . " already exists\n";
        } else {
            echo "❌ Error creating table " . ($i + 1) . ": " . $e->getMessage() . "\n";
        }
    }
}

echo "\nInserting sample data...\n";

// إدراج بيانات تجريبية لقوالب الأجهزة
$device_templates = [
    ['مصباح ذكي LED', 'smart_bulb', 'lighting', 150.00, 20.00, 'مصباح LED ذكي قابل للتحكم عبر التطبيق'],
    ['شريط LED ذكي', 'led_strip', 'lighting', 200.00, 50.00, 'شريط إضاءة LED ملون قابل للتحكم'],
    ['مفتاح إضاءة ذكي', 'smart_switch', 'lighting', 180.00, 30.00, 'مفتاح ذكي للتحكم بالإضاءة'],
    ['مساعد صوتي', 'voice_assistant', 'control', 500.00, 0.00, 'مساعد صوتي ذكي (Alexa/Google Home)'],
    ['لوحة تحكم مركزية', 'control_panel', 'control', 800.00, 100.00, 'لوحة تحكم مركزية للمنزل الذكي'],
    ['كاميرا مراقبة', 'security_camera', 'security', 600.00, 80.00, 'كاميرا مراقبة ذكية بدقة عالية'],
    ['قفل ذكي', 'smart_lock', 'security', 800.00, 100.00, 'قفل باب ذكي بصمة وكود'],
    ['جرس باب ذكي', 'smart_doorbell', 'security', 450.00, 50.00, 'جرس باب بكاميرا وإشعارات'],
    ['حساس حركة', 'motion_sensor', 'sensors', 120.00, 20.00, 'حساس كشف الحركة'],
    ['ثيرموستات ذكي', 'smart_thermostat', 'climate', 700.00, 100.00, 'منظم حرارة ذكي'],
    ['تلفاز ذكي', 'smart_tv', 'entertainment', 3000.00, 100.00, 'تلفاز ذكي 55 بوصة'],
    ['سماعات ذكية', 'smart_speaker', 'entertainment', 350.00, 0.00, 'سماعات صوتية ذكية']
];

$insert_query = "INSERT IGNORE INTO iot_device_templates (device_name, device_type, category, default_price, installation_cost, description) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($insert_query);

$inserted = 0;
foreach ($device_templates as $template) {
    try {
        $stmt->execute($template);
        if ($stmt->rowCount() > 0) {
            $inserted++;
        }
    } catch (PDOException $e) {
        // تجاهل الأخطاء
    }
}

echo "✅ Inserted $inserted device templates\n";

// إدراج قوالب الغرف
$room_templates = [
    ['غرفة نوم رئيسية ذكية', 'bedroom', 'غرفة نوم مجهزة بالكامل بأجهزة ذكية', 3500.00],
    ['صالة معيشة ذكية', 'living_room', 'صالة معيشة مع نظام ترفيه وإضاءة ذكية', 8000.00],
    ['مطبخ ذكي', 'kitchen', 'مطبخ مجهز بأجهزة ذكية', 4500.00],
    ['مكتب منزلي ذكي', 'office', 'مكتب منزلي بنظام تحكم ذكي', 3000.00]
];

$room_insert_query = "INSERT IGNORE INTO iot_room_templates (room_name, room_type, description, estimated_cost) VALUES (?, ?, ?, ?)";
$room_stmt = $conn->prepare($room_insert_query);

$room_inserted = 0;
foreach ($room_templates as $template) {
    try {
        $room_stmt->execute($template);
        if ($room_stmt->rowCount() > 0) {
            $room_inserted++;
        }
    } catch (PDOException $e) {
        // تجاهل الأخطاء
    }
}

echo "✅ Inserted $room_inserted room templates\n";

// التحقق من الجداول
echo "\nVerifying tables:\n";
$tables = ['iot_projects', 'iot_project_rooms', 'iot_project_devices', 'iot_device_templates', 'iot_room_templates'];

foreach ($tables as $table) {
    $stmt = $conn->query("SHOW TABLES LIKE '{$table}'");
    if ($stmt->rowCount() > 0) {
        $countStmt = $conn->query("SELECT COUNT(*) as count FROM {$table}");
        $count = $countStmt->fetch(PDO::FETCH_ASSOC)['count'];
        echo "✅ {$table} ({$count} records)\n";
    } else {
        echo "❌ {$table} NOT FOUND\n";
    }
}

echo "\n🎉 Setup completed successfully!\n";
echo "\nAccess the system at: http://localhost/backend/admin/iot-estimation/index.php\n";
?>