<?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("فشل الاتصال بقاعدة البيانات");
    }
    
    // إحصائيات الجودة
    $statsQuery = "SELECT 
                    COUNT(*) as total,
                    SUM(CASE WHEN quality >= 90 THEN 1 ELSE 0 END) as excellent,
                    SUM(CASE WHEN quality BETWEEN 75 AND 89 THEN 1 ELSE 0 END) as good,
                    SUM(CASE WHEN quality BETWEEN 60 AND 74 THEN 1 ELSE 0 END) as needs,
                    SUM(CASE WHEN quality < 60 THEN 1 ELSE 0 END) as rejected,
                    AVG(quality) as overall
                   FROM (
                       SELECT 
                           ir.id,
                           (SUM(CASE WHEN cp.status = 'pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) as quality
                       FROM inspection_requests ir
                       JOIN inspection_checkpoints cp ON ir.id = cp.request_id
                       WHERE ir.status = 'completed'
                       GROUP BY ir.id
                   ) as inspection_quality";
    $statsStmt = $db->query($statsQuery);
    $stats = $statsStmt->fetch(PDO::FETCH_ASSOC);
    
    // بيانات الرسوم البيانية
    $charts = [];
    
    // توزيع الجودة
    $qualityLabels = ['ممتاز', 'جيد', 'بحاجة تحسين', 'مرفوض'];
    $qualityData = [
        (int)$stats['excellent'],
        (int)$stats['good'],
        (int)$stats['needs'],
        (int)$stats['rejected']
    ];
    
    $charts['quality'] = [
        'labels' => $qualityLabels,
        'data' => $qualityData
    ];
    
    // اتجاه الجودة الشهري
    $trendQuery = "SELECT 
                    DATE_FORMAT(ir.created_at, '%Y-%m') as month,
                    AVG(cp_quality.quality) as avg_quality
                   FROM inspection_requests ir
                   JOIN (
                       SELECT 
                           request_id,
                           (SUM(CASE WHEN status = 'pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) as quality
                       FROM inspection_checkpoints
                       GROUP BY request_id
                   ) cp_quality ON ir.id = cp_quality.request_id
                   WHERE ir.created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
                   GROUP BY DATE_FORMAT(ir.created_at, '%Y-%m')
                   ORDER BY month ASC";
    $trendStmt = $db->query($trendQuery);
    $trend = $trendStmt->fetchAll(PDO::FETCH_ASSOC);
    
    $months = [];
    $values = [];
    foreach ($trend as $row) {
        $months[] = $row['month'];
        $values[] = round((float)$row['avg_quality'], 1);
    }
    
    $charts['trend'] = [
        'labels' => $months,
        'data' => $values
    ];
    
    // آخر عمليات تدقيق
    $auditsQuery = "SELECT 
                    ir.id,
                    ir.request_number,
                    ir.created_at as date,
                    CONCAT(u_insp.first_name, ' ', u_insp.last_name) as inspector_name,
                    CONCAT(cc.brand, ' ', cc.model, ' ', cc.year) as car_info,
                    cp_quality.quality as score,
                    CONCAT(u_sup.first_name, ' ', u_sup.last_name) as reviewer,
                    CASE 
                        WHEN cp_quality.quality >= 90 THEN 'ممتاز'
                        WHEN cp_quality.quality >= 75 THEN 'جيد'
                        WHEN cp_quality.quality >= 60 THEN 'بحاجة تحسين'
                        ELSE 'مرفوض'
                    END as level,
                    CASE 
                        WHEN cp_quality.quality < 60 THEN 'نتائج الفحص غير مطابقة للمواصفات'
                        WHEN cp_quality.quality < 75 THEN 'بعض نقاط التفتيش بحاجة تحسين'
                        ELSE NULL
                    END as issues
                   FROM inspection_requests ir
                   JOIN inspectors i ON ir.inspector_id = i.id
                   JOIN users u_insp ON i.user_id = u_insp.id
                   JOIN client_cars cc ON ir.car_id = cc.id
                   LEFT JOIN users u_sup ON ir.approved_by = u_sup.id
                   JOIN (
                       SELECT 
                           request_id,
                           (SUM(CASE WHEN status = 'pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) as quality
                       FROM inspection_checkpoints
                       GROUP BY request_id
                   ) cp_quality ON ir.id = cp_quality.request_id
                   WHERE ir.status = 'completed'
                   ORDER BY ir.created_at DESC
                   LIMIT 10";
    $auditsStmt = $db->query($auditsQuery);
    $audits = $auditsStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // توصيات تحسين الجودة
    $tips = [
        "مراجعة نقاط التفتيش الأكثر شيوعاً في الفحوصات المرفوضة",
        "عقد جلسات تدريبية للفاحصين ذوي التقييم المنخفض",
        "تحديث قائمة نقاط التفتيش بناءً على الملاحظات المتكررة",
        "تحسين جودة الصور المرفقة بنتائج الفحص",
        "توحيد معايير التقييم بين جميع الفاحصين",
        "تطوير نظام للتغذية الراجعة للفاحصين",
        "إضافة نقاط تفتيش جديدة للفحوصات المعقدة",
        "مراجعة الفحوصات ذات الجودة المنخفضة بشكل دوري"
    ];
    
    echo json_encode([
        'success' => true,
        'data' => [
            'stats' => [
                'overall' => round((float)$stats['overall'], 1),
                'excellent' => (int)$stats['excellent'],
                'good' => (int)$stats['good'],
                'needs' => (int)$stats['needs'],
                'rejected' => (int)$stats['rejected']
            ],
            'charts' => $charts,
            'audits' => $audits,
            'tips' => $tips
        ]
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>