<?php
/**
 * Detailed Database Check
 * بررسی دقیق مشکلات دیتابیس
 */

$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 "=== بررسی دقیق مشکلات ===\n\n";
    
    // 1. بررسی owner_didar_id در persons
    $personsWithOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NOT NULL AND owner_didar_id != ''")->fetchColumn();
    $personsWithoutOwner = $pdo->query("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NULL OR owner_didar_id = ''")->fetchColumn();
    $totalPersons = $pdo->query("SELECT COUNT(*) FROM persons")->fetchColumn();
    
    echo "1. Persons:\n";
    echo "   - کل: $totalPersons\n";
    echo "   - با owner: $personsWithOwner\n";
    echo "   - بدون owner: $personsWithoutOwner\n\n";
    
    // 2. بررسی owner_didar_id در deals
    $dealsWithOwner = $pdo->query("SELECT COUNT(*) FROM deals WHERE owner_didar_id IS NOT NULL AND owner_didar_id != ''")->fetchColumn();
    $dealsWithoutOwner = $pdo->query("SELECT COUNT(*) FROM deals WHERE owner_didar_id IS NULL OR owner_didar_id = ''")->fetchColumn();
    $totalDeals = $pdo->query("SELECT COUNT(*) FROM deals")->fetchColumn();
    
    echo "2. Deals:\n";
    echo "   - کل: $totalDeals\n";
    echo "   - با owner: $dealsWithOwner\n";
    echo "   - بدون owner: $dealsWithoutOwner\n\n";
    
    // 3. بررسی virtual_stages
    $virtualStagesCount = $pdo->query("SELECT COUNT(*) FROM virtual_stages")->fetchColumn();
    echo "3. Virtual Stages: $virtualStagesCount\n\n";
    
    // 4. بررسی owner_didar_id های مختلف در persons
    echo "4. توزیع owner_didar_id در persons (10 مورد اول):\n";
    $stmt = $pdo->query("SELECT owner_didar_id, COUNT(*) as count FROM persons WHERE owner_didar_id IS NOT NULL AND owner_didar_id != '' GROUP BY owner_didar_id ORDER BY count DESC LIMIT 10");
    $owners = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($owners as $owner) {
        echo "   - {$owner['owner_didar_id']}: {$owner['count']} person\n";
    }
    echo "\n";
    
    // 5. بررسی users و داده‌های آنها
    echo "5. Users و داده‌های آنها:\n";
    $users = $pdo->query("SELECT didar_user_id, display_name, email FROM users")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($users as $user) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE owner_didar_id = ?");
        $stmt->execute([$user['didar_user_id']]);
        $personCount = $stmt->fetchColumn();
        
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM deals WHERE owner_didar_id = ?");
        $stmt->execute([$user['didar_user_id']]);
        $dealCount = $stmt->fetchColumn();
        
        echo "   - {$user['display_name']} ({$user['email']}):\n";
        echo "     * Persons: $personCount\n";
        echo "     * Deals: $dealCount\n";
    }
    echo "\n";
    
    // 6. نمونه‌ای از persons بدون owner
    if ($personsWithoutOwner > 0) {
        echo "6. نمونه persons بدون owner (5 مورد اول):\n";
        $stmt = $pdo->query("SELECT didar_contact_id, first_name, last_name, owner_didar_id FROM persons WHERE owner_didar_id IS NULL OR owner_didar_id = '' LIMIT 5");
        $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($samples as $sample) {
            echo "   - {$sample['first_name']} {$sample['last_name']} (ID: {$sample['didar_contact_id']}, Owner: " . ($sample['owner_didar_id'] ?: 'NULL') . ")\n";
        }
        echo "\n";
    }
    
    // 7. بررسی raw_json برای نمونه
    echo "7. بررسی ساختار Owner در raw_json (نمونه):\n";
    $stmt = $pdo->query("SELECT raw_json FROM persons WHERE raw_json IS NOT NULL AND raw_json != '' LIMIT 1");
    $sample = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($sample) {
        $json = json_decode($sample['raw_json'], true);
        if (isset($json['Owner'])) {
            echo "   - Owner structure: " . json_encode($json['Owner'], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . "\n";
        } else {
            echo "   - OwnerId: " . ($json['OwnerId'] ?? 'NOT SET') . "\n";
        }
    }
    echo "\n";
    
} catch (PDOException $e) {
    die("خطا: " . $e->getMessage() . "\n");
}
?>

