getConnection();
echo "Create Pins Tables
🔧 Creating Pins Tables
";
// Create device_pins
try {
$db->exec("CREATE TABLE IF NOT EXISTS `device_pins` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`device_id` VARCHAR(50) NOT NULL,
`pin_number` TINYINT NOT NULL,
`pin_gpio` TINYINT NOT NULL,
`pin_type` ENUM('relay', 'sensor', 'led', 'button', 'pwm') DEFAULT 'relay',
`pin_name` VARCHAR(100) NOT NULL,
`pin_location` VARCHAR(100),
`pin_icon` VARCHAR(50) DEFAULT 'fa-plug',
`is_active` BOOLEAN DEFAULT TRUE,
`current_state` BOOLEAN DEFAULT FALSE,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `unique_device_pin` (`device_id`, `pin_gpio`),
INDEX `idx_device_pins` (`device_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
echo "✅ Table device_pins created!
";
} catch (Exception $e) {
echo "❌ device_pins: " . htmlspecialchars($e->getMessage()) . "
";
}
// Create pin_commands
try {
$db->exec("CREATE TABLE IF NOT EXISTS `pin_commands` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`device_id` VARCHAR(50) NOT NULL,
`pin_gpio` TINYINT NOT NULL,
`action` ENUM('on', 'off', 'toggle', 'pulse') NOT NULL,
`duration` INT DEFAULT NULL,
`status` ENUM('pending', 'sent', 'executed', 'failed') DEFAULT 'pending',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`executed_at` DATETIME,
INDEX `idx_pin_commands` (`device_id`, `pin_gpio`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
echo "✅ Table pin_commands created!
";
} catch (Exception $e) {
echo "❌ pin_commands: " . htmlspecialchars($e->getMessage()) . "
";
}
// Insert sample data
try {
$samples = [
['ESP01', 1, 16, 'relay', 'إضاءة غرفة النوم', 'غرفة النوم', 'fa-lightbulb'],
['ESP01', 2, 5, 'relay', 'إضاءة الصالة', 'الصالة', 'fa-lightbulb'],
['ESP01', 3, 4, 'relay', 'إضاءة المطبخ', 'المطبخ', 'fa-lightbulb'],
['ESP01', 4, 0, 'relay', 'مروحة غرفة النوم', 'غرفة النوم', 'fa-fan'],
['ESP01', 5, 2, 'led', 'LED الحالة', 'اللوحة', 'fa-circle'],
['ESP01', 6, 14, 'relay', 'إضاءة المدخل', 'المدخل', 'fa-door-open'],
['ESP01', 7, 12, 'sensor', 'حساس الحرارة', 'الصالة', 'fa-thermometer-half'],
['ESP01', 8, 13, 'sensor', 'حساس الحركة', 'المدخل', 'fa-walking'],
['ESP01', 9, 15, 'relay', 'إضاءة خارجية', 'الحديقة', 'fa-tree']
];
$stmt = $db->prepare("INSERT IGNORE INTO device_pins (device_id, pin_number, pin_gpio, pin_type, pin_name, pin_location, pin_icon) VALUES (?, ?, ?, ?, ?, ?, ?)");
$inserted = 0;
foreach ($samples as $sample) {
if ($stmt->execute($sample)) {
$inserted++;
}
}
echo "✅ Inserted {$inserted} sample pins!
";
} catch (Exception $e) {
echo "❌ Sample data: " . htmlspecialchars($e->getMessage()) . "
";
}
// Verify
$check1 = $db->query("SELECT COUNT(*) FROM device_pins")->fetchColumn();
$check2 = $db->query("SELECT COUNT(*) FROM pin_commands")->fetchColumn();
echo "
🎉 All Done!
✅ device_pins: {$check1} rows
✅ pin_commands: {$check2} rows
🎛️ إدارة Pins
";
echo "";
?>