<?php
/**
 * Migration Script for Virtual Stages
 * تبدیل virtual stages قدیمی به جدید
 */

$dbFiles = [
    __DIR__ . '/crm_database.db',
    __DIR__ . '/crm_database (2).db',
    __DIR__ . '/crm_database (1).db'
];

$DB_FILE = null;
foreach ($dbFiles as $file) {
    if (file_exists($file)) {
        $DB_FILE = $file;
        break;
    }
}

if (!$DB_FILE) {
    die("دیتابیس یافت نشد.\n");
}

try {
    $pdo = new PDO('sqlite:' . $DB_FILE);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "=== Migration Virtual Stages ===\n\n";
    
    // 1. Backup old virtual_stages
    echo "1. Backup virtual_stages قدیمی...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS virtual_stages_backup AS SELECT * FROM virtual_stages");
    echo "   ✅ Backup ایجاد شد\n\n";
    
    // 2. Delete old virtual_stages
    echo "2. حذف virtual_stages قدیمی...\n";
    $deleted = $pdo->exec("DELETE FROM virtual_stages");
    echo "   ✅ $deleted رکورد حذف شد\n\n";
    
    // 3. Update schema (drop and recreate table)
    echo "3. به‌روزرسانی schema...\n";
    $pdo->exec("DROP TABLE IF EXISTS virtual_stages");
    $pdo->exec("CREATE TABLE virtual_stages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        contact_didar_id TEXT NOT NULL,
        stage_name TEXT CHECK(stage_name IN (
            'inactive_failed', 'inactive_success', 'new', 'contact_no_answer', 
            'contact_followup', 'deal_registered', 'payment', 
            'payment_followup', 'course_delivered', 'support'
        )),
        entered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_activity_id TEXT,
        notes TEXT,
        UNIQUE(contact_didar_id, stage_name)
    )");
    echo "   ✅ Schema به‌روزرسانی شد\n\n";
    
    // 4. Recalculate virtual stages for all contacts
    echo "4. محاسبه مجدد virtual stages برای همه contacts...\n";
    
    // Load calculateVirtualStage function
    require_once __DIR__ . '/index.php';
    
    $contacts = $pdo->query("SELECT DISTINCT didar_contact_id FROM persons WHERE didar_contact_id IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN);
    $total = count($contacts);
    $processed = 0;
    
    foreach ($contacts as $contactId) {
        try {
            updateVirtualStage($contactId);
            $processed++;
            if ($processed % 100 == 0) {
                echo "   در حال پردازش: $processed از $total\n";
            }
        } catch (Exception $e) {
            echo "   ⚠️  خطا برای contact $contactId: " . $e->getMessage() . "\n";
        }
    }
    
    echo "   ✅ $processed از $total contact پردازش شد\n\n";
    
    echo "=== Migration تکمیل شد ===\n";
    echo "توجه: virtual_stages قدیمی در جدول virtual_stages_backup ذخیره شده است.\n";
    
} catch (PDOException $e) {
    die("خطا: " . $e->getMessage() . "\n");
}
?>

