-- ============================================
-- MIGRATION SCRIPT: Update Patients Table Structure
-- Run this to fix schema mismatch issues
-- ============================================

-- Backup existing data first (optional but recommended)
-- CREATE TABLE patients_backup AS SELECT * FROM patients;

-- Step 1: Add new columns if they don't exist
ALTER TABLE patients 
ADD COLUMN IF NOT EXISTS phone VARCHAR(20) AFTER gender,
ADD COLUMN IF NOT EXISTS emergency_contact VARCHAR(255) AFTER marital_status,
ADD COLUMN IF NOT EXISTS emergency_phone VARCHAR(20) AFTER emergency_contact,
ADD COLUMN IF NOT EXISTS medical_history TEXT AFTER emergency_phone,
ADD COLUMN IF NOT EXISTS surgical_history TEXT AFTER medical_history,
ADD COLUMN IF NOT EXISTS current_medications TEXT AFTER allergies,
ADD COLUMN IF NOT EXISTS diet ENUM('vegetarian', 'non-vegetarian', 'vegan', 'mixed') AFTER current_medications,
ADD COLUMN IF NOT EXISTS exercise ENUM('none', 'light', 'moderate', 'active') AFTER diet,
ADD COLUMN IF NOT EXISTS sleep_pattern ENUM('poor', 'average', 'good', 'excellent') AFTER exercise,
ADD COLUMN IF NOT EXISTS addictions TEXT AFTER sleep_pattern;

-- Step 2: Copy data from old column to new column if old column exists
UPDATE patients 
SET phone = contact_number 
WHERE phone IS NULL AND contact_number IS NOT NULL;

-- Step 3: Add indexes for new columns
ALTER TABLE patients 
ADD INDEX IF NOT EXISTS idx_phone (phone),
ADD INDEX IF NOT EXISTS idx_email (email);

-- Step 4: Drop old column if it exists (optional - keep for safety)
-- ALTER TABLE patients DROP COLUMN IF EXISTS contact_number;
-- ALTER TABLE patients DROP COLUMN IF EXISTS chronic_conditions;

-- Step 5: Verify the changes
DESCRIBE patients;

-- Optional: Show column count
SELECT COUNT(*) as column_count 
FROM information_schema.columns 
WHERE table_schema = DATABASE() 
AND table_name = 'patients';

SELECT 'Migration completed successfully!' as status;
