-- External laboratory services (tests)
CREATE TABLE IF NOT EXISTS external_lab_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    external_lab_id INT NOT NULL,
    service_code VARCHAR(100) NOT NULL,
    service_name VARCHAR(255) NOT NULL,
    sut_code VARCHAR(100) DEFAULT NULL,
    sample_type_id VARCHAR(50) DEFAULT NULL,
    sample_type_name VARCHAR(100) DEFAULT NULL,
    container_id VARCHAR(50) DEFAULT NULL,
    container_name VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_lab_service (external_lab_id, service_code),
    FOREIGN KEY (external_lab_id) REFERENCES external_laboratories(id) ON DELETE CASCADE,
    INDEX idx_service_code (service_code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
