-- Migration script to add reason_detail to absenceclarificationchilds

CREATE TABLE IF NOT EXISTS `absenceclarificationchilds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Use a safe procedure approach to add column if not exists
DELIMITER $$
CREATE PROCEDURE AddReasonDetailIfNotExist()
BEGIN
    IF NOT EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'absenceclarificationchilds'
        AND COLUMN_NAME = 'reason_detail'
        AND TABLE_SCHEMA = DATABASE()
    ) THEN
        ALTER TABLE `absenceclarificationchilds` ADD COLUMN `reason_detail` TEXT NULL AFTER `reason`;
    END IF;
END $$
DELIMITER ;

CALL AddReasonDetailIfNotExist();

DROP PROCEDURE AddReasonDetailIfNotExist;