-- Migration 73: Add is_completed and completed_at to customer_notes table

SET @col_is_completed = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'customer_notes'
      AND COLUMN_NAME = 'is_completed'
);

SET @stmt1 = IF(@col_is_completed = 0,
    'ALTER TABLE `customer_notes` 
        ADD COLUMN `is_completed` TINYINT(1) NOT NULL DEFAULT 0 AFTER `reminder_at`,
        ADD KEY `idx_customer_notes_is_completed` (`is_completed`)',
    'SELECT 1'
);

PREPARE alter_stmt1 FROM @stmt1;
EXECUTE alter_stmt1;
DEALLOCATE PREPARE alter_stmt1;

SET @col_completed_at = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'customer_notes'
      AND COLUMN_NAME = 'completed_at'
);

SET @stmt2 = IF(@col_completed_at = 0,
    'ALTER TABLE `customer_notes` 
        ADD COLUMN `completed_at` DATETIME NULL AFTER `is_completed`',
    'SELECT 1'
);

PREPARE alter_stmt2 FROM @stmt2;
EXECUTE alter_stmt2;
DEALLOCATE PREPARE alter_stmt2;
