-- Migration: Add additional fields to donors table
-- Run this after creating the initial schema

USE `blood_bank`;

ALTER TABLE `donors` 
ADD COLUMN `phone_secondary` VARCHAR(20) DEFAULT NULL AFTER `phone`,
ADD COLUMN `age` INT(11) DEFAULT NULL AFTER `phone_secondary`,
ADD COLUMN `gender` ENUM('male', 'female') DEFAULT NULL AFTER `age`,
ADD COLUMN `last_donation_date` DATE DEFAULT NULL AFTER `gender`,
ADD COLUMN `has_chronic_diseases` TINYINT(1) DEFAULT 0 AFTER `last_donation_date`,
ADD COLUMN `chronic_diseases_details` TEXT DEFAULT NULL AFTER `has_chronic_diseases`,
ADD COLUMN `medications` TEXT DEFAULT NULL AFTER `chronic_diseases_details`,
ADD COLUMN `allergies` TEXT DEFAULT NULL AFTER `medications`,
ADD COLUMN `weight` DECIMAL(5,2) DEFAULT NULL COMMENT 'in kg' AFTER `allergies`,
ADD COLUMN `email` VARCHAR(100) DEFAULT NULL AFTER `phone_secondary`,
ADD COLUMN `national_id` VARCHAR(20) DEFAULT NULL AFTER `email`,
ADD COLUMN `address` TEXT DEFAULT NULL AFTER `national_id`,
ADD COLUMN `city` VARCHAR(50) DEFAULT NULL AFTER `address`,
ADD COLUMN `notes_medical` TEXT DEFAULT NULL COMMENT 'Medical notes' AFTER `notes`;

-- Add index for secondary phone
ALTER TABLE `donors` ADD INDEX `phone_secondary` (`phone_secondary`);
ALTER TABLE `donors` ADD INDEX `email` (`email`);
ALTER TABLE `donors` ADD INDEX `national_id` (`national_id`);

