START TRANSACTION;

SET @has_valid_from := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'valid_from'
);
SET @sql := IF(
    @has_valid_from = 0,
    "ALTER TABLE commission_rules ADD COLUMN valid_from DATE NOT NULL DEFAULT (CURRENT_DATE)",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_valid_to := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'valid_to'
);
SET @sql := IF(
    @has_valid_to = 0,
    "ALTER TABLE commission_rules ADD COLUMN valid_to DATE NULL",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_rule_type := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'rule_type'
);
SET @sql := IF(
    @has_rule_type = 0,
    "ALTER TABLE commission_rules ADD COLUMN rule_type ENUM('KPI_NUMERICAL','TIERED_CASHFLOW') NOT NULL DEFAULT 'TIERED_CASHFLOW'",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;
