getConnection();
if (!$db) {
die('Database connection failed');
}
echo "
Fix Missing Tables
🔧 Fixing Missing IoT Tables
";
// Create iot_logs table
echo "Creating iot_logs table...
";
try {
$db->exec("
CREATE TABLE IF NOT EXISTS `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 "
✅ iot_logs table created successfully!
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
echo "
Creating iot_sensors_data table...
";
try {
$db->exec("
CREATE TABLE IF NOT EXISTS `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 "
✅ iot_sensors_data table created successfully!
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
echo "
Creating iot_schedules table...
";
try {
$db->exec("
CREATE TABLE IF NOT EXISTS `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 "
✅ iot_schedules table created successfully!
";
} catch (Exception $e) {
echo "
❌ Error: " . htmlspecialchars($e->getMessage()) . "
";
}
echo "
📋 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}'");
if ($check->rowCount() > 0) {
$count = $db->query("SELECT COUNT(*) FROM {$table}")->fetchColumn();
echo "
✅ Table '{$table}' exists ({$count} rows)
";
} else {
echo "
❌ Table '{$table}' still missing!
";
$all_good = false;
}
}
echo "
❌ Fatal Error: " . htmlspecialchars($e->getMessage()) . "
";
}
?>