-- Fix commission_rules.product_id FK failures for matrix rules
-- Makes product_id nullable and converts invalid 0 to NULL.

START TRANSACTION;

SET @has_col := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND COLUMN_NAME = 'product_id'
);

SET @sql := IF(
    @has_col > 0,
    'UPDATE commission_rules SET product_id = NULL WHERE product_id = 0',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    @has_col > 0,
    'ALTER TABLE commission_rules MODIFY COLUMN product_id INT NULL',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;
