🔧 Complete IoT System Fix
";
// Step 1: Fix iot_devices table
echo "
1️⃣ Fixing iot_devices Table
";
$devices_columns = $db->query("SHOW COLUMNS FROM iot_devices")->fetchAll(PDO::FETCH_ASSOC);
$devices_cols = array_column($devices_columns, 'Field');
$devices_updates = [
'is_active' => "ALTER TABLE iot_devices ADD COLUMN is_active BOOLEAN DEFAULT TRUE AFTER is_online",
'ip_address' => "ALTER TABLE iot_devices ADD COLUMN ip_address VARCHAR(45) AFTER last_seen",
'firmware_version' => "ALTER TABLE iot_devices ADD COLUMN firmware_version VARCHAR(20) AFTER ip_address"
];
foreach ($devices_updates as $col => $sql) {
if (!in_array($col, $devices_cols)) {
try {
$db->exec($sql);
echo "
✅ Added column: {$col}
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
} else {
echo "
ℹ️ Column {$col} exists
";
}
}
echo "
";
// Step 2: Fix iot_commands table
echo "
2️⃣ Fixing iot_commands Table
";
$commands_columns = $db->query("SHOW COLUMNS FROM iot_commands")->fetchAll(PDO::FETCH_ASSOC);
$commands_cols = array_column($commands_columns, 'Field');
$commands_updates = [
'priority' => "ALTER TABLE iot_commands ADD COLUMN priority TINYINT DEFAULT 5 AFTER payload",
'sent_at' => "ALTER TABLE iot_commands ADD COLUMN sent_at DATETIME AFTER status",
'created_by' => "ALTER TABLE iot_commands ADD COLUMN created_by VARCHAR(50) AFTER executed_at"
];
foreach ($commands_updates as $col => $sql) {
if (!in_array($col, $commands_cols)) {
try {
$db->exec($sql);
echo "
✅ Added column: {$col}
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
} else {
echo "
ℹ️ Column {$col} exists
";
}
}
// Update status enum if needed
try {
$db->exec("ALTER TABLE iot_commands MODIFY COLUMN status ENUM('pending', 'sent', 'executed', 'failed', 'cancelled') DEFAULT 'pending'");
echo "
✅ Updated status enum
";
} catch (Exception $e) {
echo "
ℹ️ Status enum already updated
";
}
echo "
";
// Step 3: Create missing tables
echo "
3️⃣ Creating Missing Tables
";
// iot_logs
$check = $db->query("SHOW TABLES LIKE 'iot_logs'")->rowCount();
if ($check == 0) {
try {
$db->exec("
CREATE TABLE `iot_logs` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`device_id` VARCHAR(50) NOT NULL,
`log_type` ENUM('info', 'warning', 'error', 'command', 'status') DEFAULT 'info',
`message` TEXT NOT NULL,
`data` JSON,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_device_log` (`device_id`, `log_type`),
INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
echo "
✅ Created iot_logs table
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
} else {
echo "
ℹ️ iot_logs table exists
";
}
// iot_sensors_data
$check = $db->query("SHOW TABLES LIKE 'iot_sensors_data'")->rowCount();
if ($check == 0) {
try {
$db->exec("
CREATE TABLE `iot_sensors_data` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`device_id` VARCHAR(50) NOT NULL,
`sensor_type` VARCHAR(50) NOT NULL,
`value` DECIMAL(10, 2),
`unit` VARCHAR(20),
`raw_data` JSON,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_device_sensor` (`device_id`, `sensor_type`),
INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
echo "
✅ Created iot_sensors_data table
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
} else {
echo "
ℹ️ iot_sensors_data table exists
";
}
// iot_schedules
$check = $db->query("SHOW TABLES LIKE 'iot_schedules'")->rowCount();
if ($check == 0) {
try {
$db->exec("
CREATE TABLE `iot_schedules` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`device_id` VARCHAR(50) NOT NULL,
`schedule_name` VARCHAR(100) NOT NULL,
`action` VARCHAR(50) NOT NULL,
`payload` JSON,
`cron_expression` VARCHAR(100),
`is_active` BOOLEAN DEFAULT TRUE,
`last_run` DATETIME,
`next_run` DATETIME,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_device_schedule` (`device_id`, `is_active`),
INDEX `idx_next_run` (`next_run`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");
echo "
✅ Created iot_schedules table
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
} else {
echo "
ℹ️ iot_schedules table exists
";
}
echo "
";
// Step 4: Final Verification
echo "
4️⃣ Final Verification
";
$tables = ['iot_devices', 'iot_commands', 'iot_logs', 'iot_sensors_data', 'iot_schedules'];
$all_good = true;
foreach ($tables as $table) {
$check = $db->query("SHOW TABLES LIKE '{$table}'")->rowCount();
if ($check > 0) {
$count = $db->query("SELECT COUNT(*) FROM {$table}")->fetchColumn();
$cols = $db->query("SHOW COLUMNS FROM {$table}")->fetchAll(PDO::FETCH_ASSOC);
echo "
✅ Table '{$table}' OK ({$count} rows, " . count($cols) . " columns)
";
} else {
echo "
❌ Table '{$table}' missing!
";
$all_good = false;
}
}
echo "
";
// Success Message
if ($all_good) {
echo "
";
} else {
echo "
⚠️ بعض المشاكل لا تزال موجودة
يرجى مراجعة الأخطاء أعلاه
";
}
echo "
";
} catch (Exception $e) {
echo "❌ Fatal Error: " . htmlspecialchars($e->getMessage()) . "
";
echo "