CREATE TABLE IF NOT EXISTS dislab_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  national_id VARCHAR(11),
  dosya_no VARCHAR(50),
  referans_id BIGINT,
  gelis_tarihi DATETIME,
  gelis_tipi VARCHAR(50),
  status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, sent, error',
  error_message TEXT,
  response_xml LONGTEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  INDEX(status),
  INDEX(referans_id)
);

CREATE TABLE IF NOT EXISTS dislab_records_tests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  record_id INT NOT NULL,
  test_code VARCHAR(50) NOT NULL,
  test_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (record_id) REFERENCES dislab_records(id) ON DELETE CASCADE
);
