-- Add missing columns to patients table
ALTER TABLE patients
  ADD COLUMN IF NOT EXISTS blood_group VARCHAR(5) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS city VARCHAR(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS state VARCHAR(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS allergies TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS chronic_diseases TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS current_medications TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS medical_history TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS family_history TEXT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS smoking VARCHAR(20) DEFAULT 'no',
  ADD COLUMN IF NOT EXISTS alcohol VARCHAR(20) DEFAULT 'no',
  ADD COLUMN IF NOT EXISTS height INT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS weight INT DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS profile_photo VARCHAR(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS emergency_contact_relation VARCHAR(50) DEFAULT NULL;

-- Ensure medical_records table has all required columns
CREATE TABLE IF NOT EXISTS medical_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  record_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  doctor_name VARCHAR(150),
  record_date DATE,
  file_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_patient (patient_id),
  INDEX idx_type (record_type)
);

-- Ensure prescriptions table exists
CREATE TABLE IF NOT EXISTS prescriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  doctor_id INT,
  appointment_id INT,
  medicines TEXT,
  instructions TEXT,
  diagnosis TEXT,
  follow_up_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_patient (patient_id)
);

-- Ensure notifications table has is_read column
ALTER TABLE notifications ADD COLUMN IF NOT EXISTS is_read TINYINT(1) DEFAULT 0;
