-- إنشاء جدول الأجهزة وتوثيق الفحوصات (لصفحة تكامل الأجهزة)
CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    vendor VARCHAR(150) NULL,
    model VARCHAR(150) NULL,
    serial_number VARCHAR(100) NULL,
    connection_type VARCHAR(50) NULL,
    location VARCHAR(150) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS device_tests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    test_id INT NOT NULL,
    device_test_code VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (test_id) REFERENCES tests(id),
    UNIQUE KEY uk_device_test_code (device_id, device_test_code)
);
