<?php
require_once 'config.php';
require_once 'includes/Database.php';

$db = new Database();

echo "outgoing_calls.php'deki personel dropdown için kullanılan query:\n";
echo "======================================================\n";
$users = $db->fetchAll("SELECT id, username, full_name, extension FROM users WHERE is_active = 1 ORDER BY full_name");
foreach($users as $user) {
    echo "ID: {$user['id']}, Username: {$user['username']}, Full Name: {$user['full_name']}, Extension: {$user['extension']}\n";
}

echo "\nPersonel bazında detaylı istatistikler için kullanılan query:\n";
echo "============================================================\n";
$startDate = date('Y-m-d', strtotime('monday this week'));
$endDate = date('Y-m-d');

$userStats = $db->fetchAll("
    SELECT
        u.id,
        u.username,
        u.full_name,
        u.extension,
        COUNT(oc.id) as record_count,
        SUM(oc.call_count) as total_calls,
        SUM(oc.answered_count) as total_answered,
        SUM(oc.unanswered_count) as total_unanswered,
        ROUND(AVG(oc.answer_percentage), 2) as avg_answer_rate,
        SEC_TO_TIME(SUM(TIME_TO_SEC(oc.total_duration))) as total_talk_time
    FROM users u
    INNER JOIN outgoing_calls oc ON u.id = oc.user_id
    WHERE u.is_active = 1
        AND oc.call_date BETWEEN ? AND ?
    GROUP BY u.id, u.username, u.full_name, u.extension
    ORDER BY SUM(oc.call_count) DESC
", [$startDate, $endDate]);

echo "Bu hafta için personel istatistikleri ({$startDate} - {$endDate}):\n";
foreach($userStats as $stat) {
    echo "ID: {$stat['id']}, Name: {$stat['full_name']}, Extension: {$stat['extension']}, Total Calls: {$stat['total_calls']}\n";
}

echo "\nTüm kullanıcılar (is_active kontrolü olmadan):\n";
echo "==============================================\n";
$allUsers = $db->fetchAll("SELECT id, username, full_name, extension, is_active FROM users ORDER BY full_name");
foreach($allUsers as $user) {
    echo "ID: {$user['id']}, Username: {$user['username']}, Full Name: {$user['full_name']}, Extension: {$user['extension']}, Active: " . ($user['is_active'] ? 'Evet' : 'Hayır') . "\n";
}

echo "\nÇift kayıt kontrolü:\n";
echo "====================\n";
$duplicateCheck = $db->fetchAll("
    SELECT full_name, COUNT(*) as count 
    FROM users 
    GROUP BY full_name 
    HAVING COUNT(*) > 1
");
if(empty($duplicateCheck)) {
    echo "Çift kayıt bulunamadı.\n";
} else {
    foreach($duplicateCheck as $dup) {
        echo "Çift kayıt: {$dup['full_name']} - {$dup['count']} adet\n";
    }
}

echo "\nExtension çift kayıt kontrolü:\n";
echo "==============================\n";
$extDuplicateCheck = $db->fetchAll("
    SELECT extension, COUNT(*) as count 
    FROM users 
    WHERE extension IS NOT NULL AND extension != ''
    GROUP BY extension 
    HAVING COUNT(*) > 1
");
if(empty($extDuplicateCheck)) {
    echo "Extension çift kayıt bulunamadı.\n";
} else {
    foreach($extDuplicateCheck as $dup) {
        echo "Çift extension: {$dup['extension']} - {$dup['count']} adet\n";
    }
}
?>
