<?php
/**
 * Migration Script: اصلاح owner_didar_id های موجود
 * 
 * این اسکریپت owner_didar_id های موجود را با didar_user_id در users table match می‌کند
 */

$dbFile = __DIR__ . '/crm_database (3).db';

if (!file_exists($dbFile)) {
    // Try default database
    $dbFile = __DIR__ . '/crm_database.db';
    if (!file_exists($dbFile)) {
        die("دیتابیس یافت نشد.\n");
    }
}

try {
    $pdo = new PDO('sqlite:' . $dbFile);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "=== اصلاح owner_didar_id های موجود ===\n\n";
    
    // 1. بررسی persons بدون owner_didar_id
    echo "1. بررسی persons بدون owner_didar_id...\n";
    $personsWithoutOwner = $pdo->query("
        SELECT COUNT(*) FROM persons 
        WHERE owner_didar_id IS NULL OR owner_didar_id = ''
    ")->fetchColumn();
    echo "   تعداد: $personsWithoutOwner\n\n";
    
    // 2. بررسی deals با owner_didar_id نامعتبر
    echo "2. بررسی deals با owner_didar_id نامعتبر...\n";
    $dealsWithInvalidOwner = $pdo->query("
        SELECT COUNT(*) FROM deals d
        WHERE d.owner_didar_id IS NOT NULL 
        AND d.owner_didar_id != ''
        AND NOT EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = d.owner_didar_id)
    ")->fetchColumn();
    echo "   تعداد: $dealsWithInvalidOwner\n\n";
    
    // 3. اصلاح deals با owner_didar_id نامعتبر
    if ($dealsWithInvalidOwner > 0) {
        echo "3. اصلاح deals با owner_didar_id نامعتبر...\n";
        $invalidDeals = $pdo->query("
            SELECT d.didar_deal_id, d.owner_didar_id, d.raw_json
            FROM deals d
            WHERE d.owner_didar_id IS NOT NULL 
            AND d.owner_didar_id != ''
            AND NOT EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = d.owner_didar_id)
            LIMIT 100
        ")->fetchAll(PDO::FETCH_ASSOC);
        
        $fixedDeals = 0;
        $failedDeals = 0;
        
        foreach ($invalidDeals as $deal) {
            $oldOwnerId = $deal['owner_didar_id'];
            $newOwnerId = null;
            
            // Try to extract from raw_json
            if ($deal['raw_json']) {
                $json = json_decode($deal['raw_json'], true);
                if ($json) {
                    // Try OwnerId field first
                    if (isset($json['OwnerId']) && !empty($json['OwnerId'])) {
                        $potentialOwnerId = $json['OwnerId'];
                        // Check if this exists in users
                        $check = $pdo->prepare("SELECT didar_user_id FROM users WHERE didar_user_id = ? LIMIT 1");
                        $check->execute([$potentialOwnerId]);
                        if ($check->fetch()) {
                            $newOwnerId = $potentialOwnerId;
                        }
                    }
                    
                    // If not found, try Owner object
                    if (!$newOwnerId && isset($json['Owner']) && is_array($json['Owner'])) {
                        $potentialOwnerId = $json['Owner']['UserId'] ?? $json['Owner']['Id'] ?? '';
                        if ($potentialOwnerId) {
                            $check = $pdo->prepare("SELECT didar_user_id FROM users WHERE didar_user_id = ? LIMIT 1");
                            $check->execute([$potentialOwnerId]);
                            if ($check->fetch()) {
                                $newOwnerId = $potentialOwnerId;
                            }
                        }
                    }
                }
            }
            
            try {
                if ($newOwnerId) {
                    $pdo->prepare("UPDATE deals SET owner_didar_id = ? WHERE didar_deal_id = ?")
                        ->execute([$newOwnerId, $deal['didar_deal_id']]);
                    $fixedDeals++;
                    echo "   ✅ Deal {$deal['didar_deal_id']}: از '{$oldOwnerId}' به '{$newOwnerId}' تغییر یافت\n";
                } else {
                    // Set to NULL if we can't find a match
                    $pdo->prepare("UPDATE deals SET owner_didar_id = NULL WHERE didar_deal_id = ?")
                        ->execute([$deal['didar_deal_id']]);
                    $fixedDeals++;
                    echo "   ⚠️  Deal {$deal['didar_deal_id']}: owner_didar_id به NULL تغییر یافت (match یافت نشد)\n";
                }
            } catch (Exception $e) {
                $failedDeals++;
                echo "   ❌ Deal {$deal['didar_deal_id']}: خطا - " . $e->getMessage() . "\n";
            }
        }
        
        echo "\n   خلاصه: $fixedDeals اصلاح شد، $failedDeals خطا\n\n";
    }
    
    // 4. اصلاح persons بدون owner_didar_id (از raw_json)
    if ($personsWithoutOwner > 0) {
        echo "4. اصلاح persons بدون owner_didar_id (از raw_json)...\n";
        $personsWithoutOwnerList = $pdo->query("
            SELECT didar_contact_id, raw_json
            FROM persons 
            WHERE owner_didar_id IS NULL OR owner_didar_id = ''
            LIMIT 100
        ")->fetchAll(PDO::FETCH_ASSOC);
        
        $fixedPersons = 0;
        $failedPersons = 0;
        
        foreach ($personsWithoutOwnerList as $person) {
            $newOwnerId = null;
            
            // Try to extract from raw_json
            if ($person['raw_json']) {
                $json = json_decode($person['raw_json'], true);
                if ($json) {
                    // Try OwnerId field first
                    if (isset($json['OwnerId']) && !empty($json['OwnerId'])) {
                        $potentialOwnerId = $json['OwnerId'];
                        // Check if this exists in users
                        $check = $pdo->prepare("SELECT didar_user_id FROM users WHERE didar_user_id = ? LIMIT 1");
                        $check->execute([$potentialOwnerId]);
                        if ($check->fetch()) {
                            $newOwnerId = $potentialOwnerId;
                        }
                    }
                    
                    // If not found, try Owner object
                    if (!$newOwnerId && isset($json['Owner']) && is_array($json['Owner'])) {
                        $potentialOwnerId = $json['Owner']['UserId'] ?? $json['Owner']['Id'] ?? '';
                        if ($potentialOwnerId) {
                            $check = $pdo->prepare("SELECT didar_user_id FROM users WHERE didar_user_id = ? LIMIT 1");
                            $check->execute([$potentialOwnerId]);
                            if ($check->fetch()) {
                                $newOwnerId = $potentialOwnerId;
                            }
                        }
                    }
                }
            }
            
            try {
                if ($newOwnerId) {
                    $pdo->prepare("UPDATE persons SET owner_didar_id = ? WHERE didar_contact_id = ?")
                        ->execute([$newOwnerId, $person['didar_contact_id']]);
                    $fixedPersons++;
                    echo "   ✅ Person {$person['didar_contact_id']}: owner_didar_id = '{$newOwnerId}'\n";
                }
            } catch (Exception $e) {
                $failedPersons++;
                echo "   ❌ Person {$person['didar_contact_id']}: خطا - " . $e->getMessage() . "\n";
            }
        }
        
        echo "\n   خلاصه: $fixedPersons اصلاح شد، $failedPersons خطا\n\n";
    }
    
    echo "=== پایان ===\n";
    echo "\n⚠️  توجه: این اسکریپت فقط 100 مورد اول را پردازش می‌کند.\n";
    echo "اگر موارد بیشتری دارید، اسکریپت را دوباره اجرا کنید.\n";
    echo "\n✅ بعد از اجرای این اسکریپت، یک sync کامل انجام دهید.\n";
    
} catch (Exception $e) {
    echo "❌ خطا: " . $e->getMessage() . "\n";
    exit(1);
}

