<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET');

require_once __DIR__ . '/../../database.php';

try {
    $database = new Database();
    $db = $database->getConnection();
    
    if (!$db) {
        throw new Exception("فشل الاتصال بقاعدة البيانات");
    }
    
    $period = $_GET['period'] ?? 'week'; // week, month, quarter, year
    
    // تحديد نطاق التاريخ حسب الفترة
    $dateCondition = "";
    switch ($period) {
        case 'week':
            $dateCondition = "AND ir.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
            break;
        case 'month':
            $dateCondition = "AND ir.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
            break;
        case 'quarter':
            $dateCondition = "AND ir.created_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH)";
            break;
        case 'year':
            $dateCondition = "AND ir.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
            break;
    }
    
    // إحصائيات عامة
    $statsQuery = "SELECT 
                    COUNT(DISTINCT i.id) as total_inspectors,
                    COUNT(ir.id) as total_inspections,
                    AVG(i.rating_avg) as avg_rating,
                    AVG(cp_quality.quality) as avg_quality
                   FROM inspectors i
                   LEFT JOIN inspection_requests ir ON i.id = ir.inspector_id $dateCondition
                   LEFT JOIN (
                       SELECT 
                           ir.id,
                           AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) as quality
                       FROM inspection_requests ir
                       JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                       GROUP BY ir.id
                   ) cp_quality ON ir.id = cp_quality.id";
    $statsStmt = $db->query($statsQuery);
    $stats = $statsStmt->fetch(PDO::FETCH_ASSOC);
    
    // بيانات الرسوم البيانية
    $charts = [];
    
    // توزيع الفحوصات
    $distributionQuery = "SELECT 
                          'ممتاز' as label,
                          COUNT(*) as count
                         FROM (
                             SELECT 
                                 ir.id,
                                 AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) as quality
                             FROM inspection_requests ir
                             JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                             WHERE ir.status = 'completed'
                             GROUP BY ir.id
                             HAVING quality >= 90
                         ) as excellent
                         UNION ALL
                         SELECT 'جيد', COUNT(*)
                         FROM (
                             SELECT ir.id
                             FROM inspection_requests ir
                             JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                             WHERE ir.status = 'completed'
                             GROUP BY ir.id
                             HAVING AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) BETWEEN 75 AND 89
                         ) as good
                         UNION ALL
                         SELECT 'بحاجة تحسين', COUNT(*)
                         FROM (
                             SELECT ir.id
                             FROM inspection_requests ir
                             JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                             WHERE ir.status = 'completed'
                             GROUP BY ir.id
                             HAVING AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) BETWEEN 60 AND 74
                         ) as needs
                         UNION ALL
                         SELECT 'مرفوض', COUNT(*)
                         FROM (
                             SELECT ir.id
                             FROM inspection_requests ir
                             JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                             WHERE ir.status = 'completed'
                             GROUP BY ir.id
                             HAVING AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) < 60
                         ) as rejected";
    $distStmt = $db->query($distributionQuery);
    $distribution = $distStmt->fetchAll(PDO::FETCH_ASSOC);
    
    $charts['distribution'] = [
        'labels' => array_column($distribution, 'label'),
        'data' => array_column($distribution, 'count')
    ];
    
    // أداء الفاحصين
    $performanceQuery = "SELECT 
                         CONCAT(u.first_name, ' ', u.last_name) as name,
                         COUNT(ir.id) as count
                        FROM inspectors i
                        JOIN users u ON i.user_id = u.id
                        LEFT JOIN inspection_requests ir ON i.id = ir.inspector_id $dateCondition
                        GROUP BY i.id
                        ORDER BY count DESC
                        LIMIT 5";
    $perfStmt = $db->query($performanceQuery);
    $performance = $perfStmt->fetchAll(PDO::FETCH_ASSOC);
    
    $charts['performance'] = [
        'labels' => array_column($performance, 'name'),
        'data' => array_column($performance, 'count')
    ];
    
    // ترتيب الفاحصين
    $rankingQuery = "SELECT 
                        CONCAT(u.first_name, ' ', u.last_name) as name,
                        COUNT(ir.id) as total,
                        i.rating_avg as rating,
                        COALESCE(cp_quality.quality, 0) as quality,
                        (COUNT(ir.id) * 0.4 + i.rating_avg * 20 * 0.3 + cp_quality.quality * 0.3) as performance
                     FROM inspectors i
                     JOIN users u ON i.user_id = u.id
                     LEFT JOIN inspection_requests ir ON i.id = ir.inspector_id $dateCondition
                     LEFT JOIN (
                         SELECT 
                             ir.inspector_id,
                             AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) as quality
                         FROM inspection_requests ir
                         JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                         GROUP BY ir.inspector_id
                     ) cp_quality ON i.id = cp_quality.inspector_id
                     GROUP BY i.id
                     ORDER BY performance DESC";
    $rankingStmt = $db->query($rankingQuery);
    $ranking = $rankingStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // تفاصيل الأداء
    $detailsQuery = "SELECT 
                        CONCAT(u.first_name, ' ', u.last_name) as name,
                        ic.name as center,
                        COUNT(ir.id) as total,
                        SUM(CASE WHEN ir.status = 'completed' THEN 1 ELSE 0 END) as completed,
                        i.rating_avg as rating,
                        COALESCE(cp_quality.quality, 0) as quality,
                        AVG(TIMESTAMPDIFF(MINUTE, ir.started_at, ir.completed_at)) as avg_time
                     FROM inspectors i
                     JOIN users u ON i.user_id = u.id
                     LEFT JOIN inspection_centers ic ON i.center_id = ic.id
                     LEFT JOIN inspection_requests ir ON i.id = ir.inspector_id $dateCondition
                     LEFT JOIN (
                         SELECT 
                             ir.inspector_id,
                             AVG(CASE WHEN cp.status = 'pass' THEN 100 ELSE 0 END) as quality
                         FROM inspection_requests ir
                         JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                         GROUP BY ir.inspector_id
                     ) cp_quality ON i.id = cp_quality.inspector_id
                     GROUP BY i.id
                     ORDER BY completed DESC";
    $detailsStmt = $db->query($detailsQuery);
    $details = $detailsStmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo json_encode([
        'success' => true,
        'data' => [
            'stats' => [
                'total_inspectors' => (int)$stats['total_inspectors'],
                'total_inspections' => (int)$stats['total_inspections'],
                'avg_rating' => round((float)$stats['avg_rating'], 1),
                'quality_rate' => round((float)$stats['avg_quality'], 1)
            ],
            'charts' => $charts,
            'ranking' => $ranking,
            'details' => $details
        ]
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>