USE `nephrolink`;

CREATE TABLE IF NOT EXISTS patient_relatives (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  relation VARCHAR(100) NOT NULL, -- e.g., Spouse, Parent, Child
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_rel_patient (patient_id)
) ENGINE=InnoDB;