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

$db = new Database();

$startDate = date('Y-m-d', strtotime('monday this week'));
$endDate = date('Y-m-d');

echo "Bu hafta tarih aralığı: {$startDate} - {$endDate}\n";
echo "================================================\n\n";

echo "Bu hafta outgoing_calls tablosundaki tüm kayıtlar:\n";
echo "==================================================\n";
$outgoingRecords = $db->fetchAll("
    SELECT oc.*, u.full_name, u.extension 
    FROM outgoing_calls oc 
    JOIN users u ON oc.user_id = u.id 
    WHERE oc.call_date BETWEEN ? AND ? 
    ORDER BY oc.user_id, oc.call_date
", [$startDate, $endDate]);

foreach($outgoingRecords as $record) {
    echo "User ID: {$record['user_id']}, Name: {$record['full_name']}, Extension: {$record['extension']}, Date: {$record['call_date']}, Calls: {$record['call_count']}\n";
}

echo "\nKullanıcı bazında bu hafta toplam:\n";
echo "==================================\n";
$userTotals = $db->fetchAll("
    SELECT 
        oc.user_id,
        u.full_name,
        u.extension,
        COUNT(oc.id) as record_count,
        SUM(oc.call_count) as total_calls
    FROM outgoing_calls oc 
    JOIN users u ON oc.user_id = u.id 
    WHERE oc.call_date BETWEEN ? AND ? 
    GROUP BY oc.user_id, u.full_name, u.extension
    ORDER BY oc.user_id
", [$startDate, $endDate]);

foreach($userTotals as $total) {
    echo "User ID: {$total['user_id']}, Name: {$total['full_name']}, Extension: {$total['extension']}, Records: {$total['record_count']}, Total Calls: {$total['total_calls']}\n";
}

echo "\nArife SARI'nin outgoing_calls kayıtları (tüm zamanlar):\n";
echo "======================================================\n";
$arifeRecords = $db->fetchAll("
    SELECT oc.*, u.full_name 
    FROM outgoing_calls oc 
    JOIN users u ON oc.user_id = u.id 
    WHERE u.full_name LIKE '%Arife%' 
    ORDER BY oc.call_date DESC 
    LIMIT 10
");

if(empty($arifeRecords)) {
    echo "Arife SARI'nin outgoing_calls tablosunda hiç kaydı yok!\n";
} else {
    foreach($arifeRecords as $record) {
        echo "Date: {$record['call_date']}, Calls: {$record['call_count']}, Answered: {$record['answered_count']}\n";
    }
}

echo "\nSeda Çınar'ın outgoing_calls kayıtları:\n";
echo "=======================================\n";
$sedaRecords = $db->fetchAll("
    SELECT oc.*, u.full_name 
    FROM outgoing_calls oc 
    JOIN users u ON oc.user_id = u.id 
    WHERE u.full_name LIKE '%Seda%' 
    ORDER BY oc.call_date DESC 
    LIMIT 10
");

foreach($sedaRecords as $record) {
    echo "User ID: {$record['user_id']}, Date: {$record['call_date']}, Calls: {$record['call_count']}, Answered: {$record['answered_count']}\n";
}

echo "\nSeda Çınar için GROUP BY sorgusu sonucu:\n";
echo "========================================\n";
$sedaGrouped = $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
    FROM users u
    INNER JOIN outgoing_calls oc ON u.id = oc.user_id
    WHERE u.full_name LIKE '%Seda%'
        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]);

foreach($sedaGrouped as $group) {
    echo "ID: {$group['id']}, Name: {$group['full_name']}, Extension: {$group['extension']}, Total Calls: {$group['total_calls']}, Records: {$group['record_count']}\n";
}

// Performans skoru hesaplama problemi kontrol et
echo "\nPerformans skoru hesaplama debug:\n";
echo "=================================\n";
$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,
        SUM(TIME_TO_SEC(oc.total_duration)) as total_talk_seconds,
        SEC_TO_TIME(FLOOR(AVG(TIME_TO_SEC(oc.avg_duration)))) as avg_call_duration,
        ROUND(SUM(oc.call_count) / COUNT(oc.id), 0) as daily_avg_calls,
        MAX(oc.call_date) as last_record_date,
        MIN(oc.call_date) as first_record_date
    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]);

foreach($userStats as $stat) {
    echo "ID: {$stat['id']}, Name: {$stat['full_name']}, Extension: {$stat['extension']}, Total Calls: {$stat['total_calls']}, Records: {$stat['record_count']}\n";
}
?>
