<?php
/**
 * Debug Script: بررسی مشکل owner_didar_id و agent users
 * 
 * این اسکریپت مشکلات مربوط به owner_didar_id را بررسی می‌کند
 */

$dbFile = __DIR__ . '/crm_database.db';

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

try {
    $pdo = new PDO('sqlite:' . $dbFile);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "=== DEBUG: بررسی Owner Mapping ===\n\n";
    
    // 1. بررسی کاربران
    echo "1. بررسی کاربران:\n";
    echo str_repeat("-", 80) . "\n";
    $users = $pdo->query("SELECT id, didar_user_id, display_name, role, email FROM users WHERE is_active = 1 ORDER BY role DESC, display_name")
        ->fetchAll(PDO::FETCH_ASSOC);
    
    echo sprintf("%-5s | %-40s | %-15s | %-30s\n", "ID", "didar_user_id", "Role", "Display Name");
    echo str_repeat("-", 80) . "\n";
    foreach ($users as $user) {
        echo sprintf("%-5s | %-40s | %-15s | %-30s\n", 
            $user['id'], 
            $user['didar_user_id'] ?? 'NULL', 
            $user['role'],
            $user['display_name'] ?? ''
        );
    }
    echo "\n";
    
    // 2. بررسی persons با owner_didar_id
    echo "2. بررسی persons با owner_didar_id:\n";
    echo str_repeat("-", 80) . "\n";
    
    // تعداد کل persons
    $totalPersons = $pdo->query("SELECT COUNT(*) FROM persons")->fetchColumn();
    echo "کل persons: $totalPersons\n";
    
    // تعداد persons با owner_didar_id
    $personsWithOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NOT NULL AND owner_didar_id != ''")->fetchColumn();
    echo "persons با owner_didar_id: $personsWithOwner\n";
    
    // تعداد persons بدون owner_didar_id
    $personsWithoutOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NULL OR owner_didar_id = ''")->fetchColumn();
    echo "persons بدون owner_didar_id: $personsWithoutOwner\n";
    
    // تعداد persons که owner_didar_id آنها با users match می‌شود
    $matchedPersons = $pdo->query("
        SELECT COUNT(*) FROM persons p
        WHERE p.owner_didar_id IS NOT NULL 
        AND p.owner_didar_id != ''
        AND EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = p.owner_didar_id)
    ")->fetchColumn();
    echo "persons با owner_didar_id معتبر (match با users): $matchedPersons\n";
    
    // تعداد persons که owner_didar_id آنها با users match نمی‌شود
    $unmatchedPersons = $pdo->query("
        SELECT COUNT(*) FROM persons p
        WHERE p.owner_didar_id IS NOT NULL 
        AND p.owner_didar_id != ''
        AND NOT EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = p.owner_didar_id)
    ")->fetchColumn();
    echo "persons با owner_didar_id نامعتبر (عدم match با users): $unmatchedPersons\n";
    
    echo "\n";
    
    // 3. بررسی deals با owner_didar_id
    echo "3. بررسی deals با owner_didar_id:\n";
    echo str_repeat("-", 80) . "\n";
    
    // تعداد کل deals
    $totalDeals = $pdo->query("SELECT COUNT(*) FROM deals")->fetchColumn();
    echo "کل deals: $totalDeals\n";
    
    // تعداد deals با owner_didar_id
    $dealsWithOwner = $pdo->query("SELECT COUNT(*) FROM deals WHERE owner_didar_id IS NOT NULL AND owner_didar_id != ''")->fetchColumn();
    echo "deals با owner_didar_id: $dealsWithOwner\n";
    
    // تعداد deals بدون owner_didar_id
    $dealsWithoutOwner = $pdo->query("SELECT COUNT(*) FROM deals WHERE owner_didar_id IS NULL OR owner_didar_id = ''")->fetchColumn();
    echo "deals بدون owner_didar_id: $dealsWithoutOwner\n";
    
    // تعداد deals که owner_didar_id آنها با users match می‌شود
    $matchedDeals = $pdo->query("
        SELECT COUNT(*) FROM deals d
        WHERE d.owner_didar_id IS NOT NULL 
        AND d.owner_didar_id != ''
        AND EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = d.owner_didar_id)
    ")->fetchColumn();
    echo "deals با owner_didar_id معتبر (match با users): $matchedDeals\n";
    
    // تعداد deals که owner_didar_id آنها با users match نمی‌شود
    $unmatchedDeals = $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 "deals با owner_didar_id نامعتبر (عدم match با users): $unmatchedDeals\n";
    
    echo "\n";
    
    // 4. بررسی برای هر agent user
    echo "4. بررسی برای هر agent user:\n";
    echo str_repeat("-", 80) . "\n";
    
    $agentUsers = $pdo->query("SELECT id, didar_user_id, display_name FROM users WHERE role = 'agent' AND is_active = 1")
        ->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($agentUsers as $agent) {
        $didarId = $agent['didar_user_id'];
        $agentName = $agent['display_name'];
        
        // تعداد persons برای این agent
        $agentPersons = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE owner_didar_id = ?");
        $agentPersons->execute([$didarId]);
        $personsCount = $agentPersons->fetchColumn();
        
        // تعداد deals برای این agent
        $agentDeals = $pdo->prepare("SELECT COUNT(*) FROM deals WHERE owner_didar_id = ?");
        $agentDeals->execute([$didarId]);
        $dealsCount = $agentDeals->fetchColumn();
        
        echo "Agent: $agentName (didar_user_id: $didarId)\n";
        echo "  - Persons: $personsCount\n";
        echo "  - Deals: $dealsCount\n";
        echo "\n";
    }
    
    // 5. نمونه‌ای از persons بدون owner
    echo "5. نمونه‌ای از persons بدون owner_didar_id (10 مورد اول):\n";
    echo str_repeat("-", 80) . "\n";
    $samplePersons = $pdo->query("
        SELECT didar_contact_id, first_name, last_name, mobile_phone, owner_didar_id, created_at
        FROM persons 
        WHERE owner_didar_id IS NULL OR owner_didar_id = ''
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($samplePersons) > 0) {
        echo sprintf("%-40s | %-20s | %-15s | %-40s\n", "didar_contact_id", "Name", "Mobile", "owner_didar_id");
        echo str_repeat("-", 80) . "\n";
        foreach ($samplePersons as $p) {
            $name = ($p['first_name'] ?? '') . ' ' . ($p['last_name'] ?? '');
            echo sprintf("%-40s | %-20s | %-15s | %-40s\n", 
                $p['didar_contact_id'],
                $name,
                $p['mobile_phone'] ?? '',
                $p['owner_didar_id'] ?? 'NULL'
            );
        }
    } else {
        echo "هیچ person بدون owner_didar_id یافت نشد.\n";
    }
    
    echo "\n";
    
    // 6. نمونه‌ای از owner_didar_id های نامعتبر
    echo "6. نمونه‌ای از owner_didar_id های نامعتبر (10 مورد اول):\n";
    echo str_repeat("-", 80) . "\n";
    $invalidOwners = $pdo->query("
        SELECT DISTINCT p.owner_didar_id, COUNT(*) as count
        FROM persons p
        WHERE p.owner_didar_id IS NOT NULL 
        AND p.owner_didar_id != ''
        AND NOT EXISTS (SELECT 1 FROM users u WHERE u.didar_user_id = p.owner_didar_id)
        GROUP BY p.owner_didar_id
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($invalidOwners) > 0) {
        echo sprintf("%-40s | %-10s\n", "owner_didar_id", "Count");
        echo str_repeat("-", 80) . "\n";
        foreach ($invalidOwners as $inv) {
            echo sprintf("%-40s | %-10s\n", $inv['owner_didar_id'], $inv['count']);
        }
    } else {
        echo "هیچ owner_didar_id نامعتبر یافت نشد.\n";
    }
    
    echo "\n";
    echo "=== پایان گزارش ===\n";
    echo "\n";
    echo "⚠️  اگر owner_didar_id های نامعتبر وجود دارد، باید sync مجدد انجام شود.\n";
    echo "⚠️  اگر persons/deals بدون owner_didar_id وجود دارد، باید sync مجدد انجام شود.\n";
    
} catch (Exception $e) {
    echo "❌ خطا: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
    exit(1);
}

