USE `nephrolink`;

CREATE TABLE IF NOT EXISTS patient_referrals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  doctor_id INT DEFAULT NULL,
  institution_id INT DEFAULT NULL,
  referral_reason VARCHAR(255) DEFAULT NULL,
  referral_date DATE DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY (institution_id) REFERENCES institutions(id) ON DELETE SET NULL ON UPDATE CASCADE,
  INDEX idx_ref_patient (patient_id),
  INDEX idx_ref_doctor (doctor_id),
  INDEX idx_ref_institution (institution_id)
) ENGINE=InnoDB;