<?php
require_once __DIR__ . '/../../vendor/autoload.php';

use App\Database\Connection;

try {
    $db = Connection::getInstance();
    $pdo = $db->getPdo();

    $pdo->beginTransaction();

    $pdo->exec("CREATE TABLE IF NOT EXISTS commission_rules_backup AS SELECT * FROM commission_rules");
    if ($pdo->query("SHOW TABLES LIKE 'deal_commissions'")->rowCount() > 0) {
        $pdo->exec("CREATE TABLE IF NOT EXISTS deal_commissions_backup AS SELECT * FROM deal_commissions");
    }

    $pdo->exec("DROP TABLE IF EXISTS commission_rules");
    $pdo->exec("
        CREATE TABLE commission_rules (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            kpi_source VARCHAR(100) NOT NULL,
            target_users JSON NOT NULL,
            kpi_min_threshold DECIMAL(15,4) NOT NULL DEFAULT 0,
            kpi_max_threshold DECIMAL(15,4) NOT NULL DEFAULT 0,
            base_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
            reward_percentage DECIMAL(8,4) NOT NULL DEFAULT 0,
            status ENUM('active','inactive') NOT NULL DEFAULT 'active',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_kpi_source_status (kpi_source, status),
            INDEX idx_thresholds (kpi_min_threshold, kpi_max_threshold)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $pdo->exec("DROP TABLE IF EXISTS user_commissions_ledger");
    $pdo->exec("
        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 DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_date (user_id, calculation_date),
            INDEX idx_rule (rule_id),
            CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT fk_ledger_rule FOREIGN KEY (rule_id) REFERENCES commission_rules(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $pdo->commit();
    echo "✅ Migration completed successfully.\n";
} catch (\Throwable $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "❌ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}
