-- Ensures commission_rules has a canonical KPI selector column and required indexes.
-- Safe to run multiple times.

START TRANSACTION;

-- 1) Add kpi_source if missing.
SET @has_kpi_source := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND COLUMN_NAME = 'kpi_source'
);
SET @sql_add_kpi_source := IF(
    @has_kpi_source = 0,
    'ALTER TABLE commission_rules ADD COLUMN kpi_source VARCHAR(120) NULL AFTER id',
    'SELECT 1'
);
PREPARE stmt_add_kpi_source FROM @sql_add_kpi_source;
EXECUTE stmt_add_kpi_source;
DEALLOCATE PREPARE stmt_add_kpi_source;

-- 2) Backfill kpi_source from kpi_key when available.
SET @has_kpi_key := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND COLUMN_NAME = 'kpi_key'
);
SET @sql_backfill := IF(
    @has_kpi_key > 0,
    "UPDATE commission_rules
     SET kpi_source = COALESCE(NULLIF(kpi_source, ''), NULLIF(kpi_key, ''), 'tiered_cashflow')
     WHERE kpi_source IS NULL OR kpi_source = ''",
    "UPDATE commission_rules
     SET kpi_source = COALESCE(NULLIF(kpi_source, ''), 'tiered_cashflow')
     WHERE kpi_source IS NULL OR kpi_source = ''"
);
PREPARE stmt_backfill FROM @sql_backfill;
EXECUTE stmt_backfill;
DEALLOCATE PREPARE stmt_backfill;

-- 3) Enforce NOT NULL default for stable inserts.
ALTER TABLE commission_rules
    MODIFY COLUMN kpi_source VARCHAR(120) NOT NULL DEFAULT 'tiered_cashflow';

-- 4) Add supporting index if missing.
SET @has_idx := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND INDEX_NAME = 'idx_commission_rules_kpi_source_status'
);
SET @has_status_col := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND COLUMN_NAME = 'status'
);
SET @sql_add_idx := IF(
    @has_idx = 0 AND @has_status_col > 0,
    'CREATE INDEX idx_commission_rules_kpi_source_status ON commission_rules (kpi_source, status)',
    'SELECT 1'
);
PREPARE stmt_add_idx FROM @sql_add_idx;
EXECUTE stmt_add_idx;
DEALLOCATE PREPARE stmt_add_idx;

COMMIT;
