-- External lab service mappings to local tests
CREATE TABLE IF NOT EXISTS external_lab_service_mappings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    external_lab_service_id INT NOT NULL,
    local_test_code VARCHAR(100) NOT NULL,
    local_test_name VARCHAR(255) DEFAULT NULL,
    auto_import 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_mapping (external_lab_service_id),
    FOREIGN KEY (external_lab_service_id) REFERENCES external_lab_services(id) ON DELETE CASCADE,
    INDEX idx_local_test (local_test_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
