-- Migration script to add reason_detail to attendances

DELIMITER $$
CREATE PROCEDURE AddReasonDetailToAttendancesIfNotExist()
BEGIN
    IF NOT EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'attendances'
        AND COLUMN_NAME = 'reason_detail'
        AND TABLE_SCHEMA = DATABASE()
    ) THEN
        ALTER TABLE `attendances` ADD COLUMN `reason_detail` TEXT NULL AFTER `reason`;
    END IF;
END $$
DELIMITER ;

CALL AddReasonDetailToAttendancesIfNotExist();
DROP PROCEDURE AddReasonDetailToAttendancesIfNotExist;