-- Create External Laboratories Table (Independent from Institutions)
CREATE TABLE IF NOT EXISTS external_laboratories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL COMMENT 'Laboratuvar Adı (Lims, Labcore vb.)',
  api_url VARCHAR(500) NOT NULL COMMENT 'Web servis API adresi',
  api_key VARCHAR(255) NOT NULL COMMENT 'API Key / Kurum ID',
  api_password VARCHAR(255) NOT NULL COMMENT 'Web servis şifresi (şifrelenmiş)',
  description TEXT COMMENT 'Laboratuvar açıklaması',
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_status (status),
  UNIQUE KEY unique_api_url (api_url)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Dış laboratuvar hizmetleri';

-- Create Institution-External Lab Junction Table (Many-to-Many)
CREATE TABLE IF NOT EXISTS institution_external_labs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  institution_id INT NOT NULL,
  external_lab_id INT NOT NULL,
  is_default BOOLEAN DEFAULT FALSE COMMENT 'Bu kurum için varsayılan laboratuvar',
  custom_api_key VARCHAR(255) COMMENT 'Kuruma özel API Key (isteğe bağlı)',
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  CONSTRAINT fk_institution_lab_institution 
    FOREIGN KEY (institution_id) 
    REFERENCES institutions(id) 
    ON DELETE CASCADE,
  
  CONSTRAINT fk_institution_lab_external 
    FOREIGN KEY (external_lab_id) 
    REFERENCES external_laboratories(id) 
    ON DELETE CASCADE,
  
  INDEX idx_institution_id (institution_id),
  INDEX idx_external_lab_id (external_lab_id),
  UNIQUE KEY unique_institution_lab (institution_id, external_lab_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Kurumlar ve dış laboratuvarlar arasındaki ilişki';
