-- SaleOs Commission Module schema sync (idempotent, phpMyAdmin-ready)
-- Context:
-- - You already ran:
--   1) 2026_05_03_fix_commission_rules_salesperson_fk.sql
--   2) 2026_05_03_fix_commission_rules_kpi_source.sql
-- - You did NOT run migrate_commission_kpi_decoupled.php
--
-- This script aligns existing tables to the decoupled KPI-driven structure
-- WITHOUT dropping tables.

START TRANSACTION;

-- ---------------------------------------------------------------------------
-- 1) commission_rules: add missing columns used by current backend logic
-- ---------------------------------------------------------------------------

-- title (legacy-safe optional display label)
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'title'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN title VARCHAR(255) NULL AFTER id",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- kpi_source (already handled by your prior migration, kept idempotent)
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'kpi_source'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN kpi_source VARCHAR(120) NOT NULL DEFAULT 'tiered_cashflow' AFTER title",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

-- thresholds
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'kpi_min_threshold'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN kpi_min_threshold DECIMAL(15,4) NOT NULL DEFAULT 0 AFTER requested_services",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'kpi_max_threshold'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN kpi_max_threshold DECIMAL(15,4) NOT NULL DEFAULT 100 AFTER kpi_min_threshold",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- amounts
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'base_amount'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN base_amount DECIMAL(18,2) NOT NULL DEFAULT 0 AFTER kpi_max_threshold",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'reward_percentage'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN reward_percentage DECIMAL(8,4) NOT NULL DEFAULT 0 AFTER base_amount",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- status
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'status'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN status ENUM('active','inactive') NOT NULL DEFAULT 'active' AFTER reward_percentage",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- salesperson_user_id (nullable for general rules)
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'salesperson_user_id'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN salesperson_user_id INT NULL AFTER kpi_source",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE commission_rules
SET salesperson_user_id = NULL
WHERE salesperson_user_id = 0;

ALTER TABLE commission_rules
    MODIFY COLUMN salesperson_user_id INT NULL;

-- timestamps
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'created_at'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND COLUMN_NAME = 'updated_at'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rules ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- fallback backfill for kpi_source from kpi_key
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 := 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 FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- JSON defaults backfill
UPDATE commission_rules
SET target_users = JSON_ARRAY('ALL')
WHERE target_users IS NULL;

UPDATE commission_rules
SET requested_services = JSON_ARRAY('ALL')
WHERE requested_services IS NULL;

-- helpful indexes
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 := 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 FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_idx := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rules' AND INDEX_NAME = 'idx_commission_rules_salesperson'
);
SET @sql := IF(@has_idx = 0,
    "CREATE INDEX idx_commission_rules_salesperson ON commission_rules (salesperson_user_id)",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- FK to users (only if missing)
SET @has_fk := (
    SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'commission_rules'
      AND CONSTRAINT_NAME = 'commission_rules_ibfk_1'
);
SET @sql := IF(@has_fk = 0,
    "ALTER TABLE commission_rules
     ADD CONSTRAINT commission_rules_ibfk_1
     FOREIGN KEY (salesperson_user_id) REFERENCES users(id)
     ON DELETE CASCADE ON UPDATE CASCADE",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- 2) user_commissions_ledger: add missing columns used by current code
-- ---------------------------------------------------------------------------

SET @has_tbl := (
    SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger'
);
SET @sql := IF(@has_tbl = 0,
    "CREATE TABLE user_commissions_ledger (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        rule_id INT NOT NULL,
        kpi_score_recorded DECIMAL(15,4) NOT NULL DEFAULT 0,
        calculated_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
        calculation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_date (user_id, calculation_date),
        INDEX idx_rule (rule_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger' AND COLUMN_NAME = 'kpi_score_recorded'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE user_commissions_ledger ADD COLUMN kpi_score_recorded DECIMAL(15,4) NOT NULL DEFAULT 0 AFTER rule_id",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger' AND COLUMN_NAME = 'calculated_amount'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE user_commissions_ledger ADD COLUMN calculated_amount DECIMAL(18,2) NOT NULL DEFAULT 0 AFTER kpi_score_recorded",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger' AND COLUMN_NAME = 'calculation_date'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE user_commissions_ledger ADD COLUMN calculation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER calculated_amount",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- indexes
SET @has_idx := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger' AND INDEX_NAME = 'idx_user_date'
);
SET @sql := IF(@has_idx = 0,
    "CREATE INDEX idx_user_date ON user_commissions_ledger (user_id, calculation_date)",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_idx := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_commissions_ledger' AND INDEX_NAME = 'idx_rule'
);
SET @sql := IF(@has_idx = 0,
    "CREATE INDEX idx_rule ON user_commissions_ledger (rule_id)",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- FK user_commissions_ledger.user_id -> users.id
SET @has_fk := (
    SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'user_commissions_ledger'
      AND CONSTRAINT_NAME = 'fk_ledger_user'
);
SET @sql := IF(@has_fk = 0,
    "ALTER TABLE user_commissions_ledger
     ADD CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id)
     ON DELETE CASCADE ON UPDATE CASCADE",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- FK user_commissions_ledger.rule_id -> commission_rules.id
SET @has_fk := (
    SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'user_commissions_ledger'
      AND CONSTRAINT_NAME = 'fk_ledger_rule'
);
SET @sql := IF(@has_fk = 0,
    "ALTER TABLE user_commissions_ledger
     ADD CONSTRAINT fk_ledger_rule FOREIGN KEY (rule_id) REFERENCES commission_rules(id)
     ON DELETE CASCADE ON UPDATE CASCADE",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- 3) commission_rule_tiers: ensure table exists for tiered/kpi band rows
-- ---------------------------------------------------------------------------

SET @has_tbl := (
    SELECT COUNT(*) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rule_tiers'
);
SET @sql := IF(@has_tbl = 0,
    "CREATE TABLE commission_rule_tiers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        rule_id INT NOT NULL,
        min_realization_percent DECIMAL(10,4) NULL,
        max_realization_percent DECIMAL(10,4) NULL,
        min_amount DECIMAL(18,2) NULL,
        max_amount DECIMAL(18,2) NULL,
        allocated_commission_percent DECIMAL(10,4) NULL,
        commission_percent DECIMAL(10,4) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_rule (rule_id),
        CONSTRAINT fk_commission_rule_tiers_rule FOREIGN KEY (rule_id) REFERENCES commission_rules(id) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- add missing columns if table already existed
SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rule_tiers' AND COLUMN_NAME = 'min_amount'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rule_tiers ADD COLUMN min_amount DECIMAL(18,2) NULL AFTER max_realization_percent",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rule_tiers' AND COLUMN_NAME = 'max_amount'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rule_tiers ADD COLUMN max_amount DECIMAL(18,2) NULL AFTER min_amount",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rule_tiers' AND COLUMN_NAME = 'allocated_commission_percent'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rule_tiers ADD COLUMN allocated_commission_percent DECIMAL(10,4) NULL AFTER max_amount",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'commission_rule_tiers' AND COLUMN_NAME = 'commission_percent'
);
SET @sql := IF(@has_col = 0,
    "ALTER TABLE commission_rule_tiers ADD COLUMN commission_percent DECIMAL(10,4) NULL AFTER allocated_commission_percent",
    "SELECT 1"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;

-- Optional quick verification
SELECT 'commission_rules' AS table_name;
SHOW COLUMNS FROM commission_rules;
SELECT 'user_commissions_ledger' AS table_name;
SHOW COLUMNS FROM user_commissions_ledger;
SELECT 'commission_rule_tiers' AS table_name;
SHOW COLUMNS FROM commission_rule_tiers;
