<?php
/**
 * آنالیز کامل دیتابیس برای بررسی مشکل owner_didar_id
 */

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

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

try {
    $pdo = new PDO('sqlite:' . $dbFile);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "=== آنالیز کامل دیتابیس ===\n\n";
    
    // 1. بررسی کاربران
    echo "1. لیست کاربران:\n";
    echo str_repeat("=", 100) . "\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);
    
    printf("%-5s | %-40s | %-15s | %-30s | %-30s\n", "ID", "didar_user_id", "Role", "Display Name", "Email");
    echo str_repeat("-", 100) . "\n";
    foreach ($users as $user) {
        printf("%-5s | %-40s | %-15s | %-30s | %-30s\n", 
            $user['id'], 
            $user['didar_user_id'] ?? 'NULL', 
            $user['role'],
            $user['display_name'] ?? '',
            $user['email'] ?? ''
        );
    }
    echo "\n";
    
    // 2. بررسی persons
    echo "2. آمار persons:\n";
    echo str_repeat("=", 100) . "\n";
    $totalPersons = $pdo->query("SELECT COUNT(*) FROM persons")->fetchColumn();
    $personsWithOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NOT NULL AND owner_didar_id != ''")->fetchColumn();
    $personsWithoutOwner = $totalPersons - $personsWithOwner;
    
    echo "کل persons: $totalPersons\n";
    echo "با owner_didar_id: $personsWithOwner\n";
    echo "بدون owner_didar_id: $personsWithoutOwner\n\n";
    
    // 3. بررسی persons جدید (آخرین 10 تا)
    echo "3. آخرین 10 person ایجاد شده:\n";
    echo str_repeat("=", 100) . "\n";
    $recentPersons = $pdo->query("
        SELECT didar_contact_id, first_name, last_name, owner_didar_id, created_at, contact_type
        FROM persons 
        ORDER BY created_at DESC 
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    printf("%-40s | %-25s | %-40s | %-20s | %-15s\n", "didar_contact_id", "Name", "owner_didar_id", "created_at", "contact_type");
    echo str_repeat("-", 100) . "\n";
    foreach ($recentPersons as $p) {
        $name = trim(($p['first_name'] ?? '') . ' ' . ($p['last_name'] ?? ''));
        printf("%-40s | %-25s | %-40s | %-20s | %-15s\n", 
            $p['didar_contact_id'],
            $name ?: '-',
            $p['owner_didar_id'] ?? 'NULL',
            $p['created_at'] ?? '-',
            $p['contact_type'] ?? '-'
        );
    }
    echo "\n";
    
    // 4. بررسی match شدن owner_didar_id با users
    echo "4. بررسی match شدن owner_didar_id در persons با users:\n";
    echo str_repeat("=", 100) . "\n";
    
    $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();
    
    $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): $matchedPersons\n";
    echo "persons با owner_didar_id نامعتبر (no match): $unmatchedPersons\n\n";
    
    // 5. نمونه‌ای از owner_didar_id های نامعتبر در persons
    if ($unmatchedPersons > 0) {
        echo "5. نمونه‌ای از owner_didar_id های نامعتبر در persons (10 مورد اول):\n";
        echo str_repeat("=", 100) . "\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);
        
        printf("%-40s | %-10s\n", "owner_didar_id", "Count");
        echo str_repeat("-", 100) . "\n";
        foreach ($invalidOwners as $inv) {
            printf("%-40s | %-10s\n", $inv['owner_didar_id'], $inv['count']);
        }
        echo "\n";
    }
    
    // 6. بررسی برای هر agent user
    echo "6. بررسی برای هر agent user:\n";
    echo str_repeat("=", 100) . "\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\n";
        echo "  didar_user_id: $didarId\n";
        echo "  Persons: $personsCount\n";
        echo "  Deals: $dealsCount\n";
        
        // اگر 0 است، بررسی کنیم چرا
        if ($personsCount == 0) {
            // بررسی آیا persons با owner_didar_id خالی وجود دارد
            $emptyOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NULL OR owner_didar_id = ''")->fetchColumn();
            echo "  ⚠️  هیچ person برای این agent یافت نشد (کل persons بدون owner: $emptyOwner)\n";
        }
        echo "\n";
    }
    
    // 7. بررسی raw_json یک person جدید برای دیدن ساختار Owner
    echo "7. بررسی ساختار Owner در raw_json (آخرین person):\n";
    echo str_repeat("=", 100) . "\n";
    $latestPerson = $pdo->query("SELECT didar_contact_id, first_name, last_name, owner_didar_id, raw_json FROM persons ORDER BY created_at DESC LIMIT 1")
        ->fetch(PDO::FETCH_ASSOC);
    
    if ($latestPerson && $latestPerson['raw_json']) {
        $json = json_decode($latestPerson['raw_json'], true);
        if ($json) {
            echo "Person: " . ($latestPerson['first_name'] ?? '') . " " . ($latestPerson['last_name'] ?? '') . "\n";
            echo "owner_didar_id در DB: " . ($latestPerson['owner_didar_id'] ?? 'NULL') . "\n";
            echo "\nساختار Owner در raw_json:\n";
            if (isset($json['Owner'])) {
                echo "  Owner object: " . json_encode($json['Owner'], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT) . "\n";
                if (is_array($json['Owner'])) {
                    echo "  Owner.UserId: " . ($json['Owner']['UserId'] ?? 'NOT SET') . "\n";
                    echo "  Owner.Id: " . ($json['Owner']['Id'] ?? 'NOT SET') . "\n";
                }
            } else {
                echo "  Owner object: NOT SET\n";
            }
            
            if (isset($json['OwnerId'])) {
                echo "  OwnerId field: " . $json['OwnerId'] . "\n";
            } else {
                echo "  OwnerId field: NOT SET\n";
            }
        }
    }
    
    echo "\n=== پایان گزارش ===\n";
    
} catch (Exception $e) {
    echo "❌ خطا: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
    exit(1);
}

