-- ============================================
-- AUTO TESTER - الإجراءات المخزنة (Stored Procedures)
-- ============================================

USE auto_tester_egypt;

DELIMITER $$

-- ============================================
-- إجراء: الحصول على طلبات الفاحص
-- ============================================
CREATE PROCEDURE GetInspectorRequests(
    IN p_inspector_id INT UNSIGNED,
    IN p_status VARCHAR(50),
    IN p_date_from DATE,
    IN p_date_to DATE
)
BEGIN
    SELECT 
        ir.*,
        c.full_name AS client_name,
        c.phone AS client_phone,
        cr.brand,
        cr.model,
        cr.year,
        cr.plate_number,
        it.name_ar AS inspection_name
    FROM inspection_requests ir
    JOIN clients cl ON ir.client_id = cl.id
    JOIN users c ON cl.user_id = c.id
    JOIN client_cars cr ON ir.car_id = cr.id
    JOIN inspection_types it ON ir.inspection_type_id = it.id
    WHERE ir.inspector_id = p_inspector_id
        AND (p_status IS NULL OR ir.status = p_status)
        AND (p_date_from IS NULL OR DATE(ir.scheduled_date) >= p_date_from)
        AND (p_date_to IS NULL OR DATE(ir.scheduled_date) <= p_date_to)
    ORDER BY ir.scheduled_date DESC, ir.scheduled_time DESC;
END$$

-- ============================================
-- إجراء: توزيع طلب على فاحص
-- ============================================
CREATE PROCEDURE AssignRequestToInspector(
    IN p_request_id INT UNSIGNED,
    IN p_inspector_id INT UNSIGNED,
    IN p_assigned_by INT UNSIGNED
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- تحديث الطلب
    UPDATE inspection_requests 
    SET inspector_id = p_inspector_id,
        assigned_by = p_assigned_by,
        assigned_at = NOW(),
        status = 'assigned',
        updated_at = NOW()
    WHERE id = p_request_id;
    
    -- تحديث عدد طلبات الفاحص
    UPDATE inspectors 
    SET current_requests = current_requests + 1 
    WHERE id = p_inspector_id;
    
    -- تسجيل في التايم لاين
    INSERT INTO request_timeline (request_id, status, notes, created_by)
    VALUES (p_request_id, 'assigned', 'تم توزيع الطلب على فاحص', p_assigned_by);
    
    COMMIT;
END$$

-- ============================================
-- إجراء: إضافة نتيجة فحص
-- ============================================
CREATE PROCEDURE AddInspectionResult(
    IN p_request_id INT UNSIGNED,
    IN p_checkpoint_id INT UNSIGNED,
    IN p_result ENUM('pass','fail','warning','not_applicable'),
    IN p_notes TEXT,
    IN p_measured_value VARCHAR(100),
    IN p_inspector_id INT UNSIGNED
)
BEGIN
    DECLARE v_checkpoint_name VARCHAR(255);
    
    -- الحصول على اسم نقطة الفحص
    SELECT checkpoint_name_ar INTO v_checkpoint_name 
    FROM inspection_checkpoints 
    WHERE id = p_checkpoint_id;
    
    -- إضافة النتيجة
    INSERT INTO inspection_results (
        request_id, checkpoint_id, result, notes, 
        measured_value, created_by
    ) VALUES (
        p_request_id, p_checkpoint_id, p_result, p_notes,
        p_measured_value, p_inspector_id
    );
    
    -- تسجيل في التايم لاين
    INSERT INTO request_timeline (request_id, status, notes, created_by)
    VALUES (
        p_request_id, 
        'in_progress', 
        CONCAT('تم إضافة نتيجة: ', v_checkpoint_name, ' - ', p_result),
        p_inspector_id
    );
END$$

-- ============================================
-- إجراء: إكمال الفحص وإنشاء التقرير
-- ============================================
CREATE PROCEDURE CompleteInspection(
    IN p_request_id INT UNSIGNED,
    IN p_inspector_id INT UNSIGNED,
    IN p_report_number VARCHAR(50)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- تحديث الطلب
    UPDATE inspection_requests 
    SET status = 'completed',
        inspection_completed_at = NOW(),
        report_generated_at = NOW(),
        report_number = p_report_number,
        completed_at = NOW(),
        updated_at = NOW()
    WHERE id = p_request_id AND inspector_id = p_inspector_id;
    
    -- تحديث إحصائيات الفاحص
    UPDATE inspectors 
    SET completed_inspections = completed_inspections + 1,
        current_requests = current_requests - 1
    WHERE id = p_inspector_id;
    
    -- تحديث إحصائيات العميل
    UPDATE clients c
    JOIN inspection_requests ir ON c.id = ir.client_id
    SET c.total_requests = c.total_requests + 1,
        c.total_spent = c.total_spent + ir.total_amount
    WHERE ir.id = p_request_id;
    
    -- تسجيل في التايم لاين
    INSERT INTO request_timeline (request_id, status, notes, created_by)
    VALUES (p_request_id, 'completed', 'تم اكتمال الفحص بنجاح', p_inspector_id);
    
    COMMIT;
END$$

-- ============================================
-- إجراء: إلغاء طلب
-- ============================================
CREATE PROCEDURE CancelRequest(
    IN p_request_id INT UNSIGNED,
    IN p_reason TEXT,
    IN p_cancelled_by INT UNSIGNED
)
BEGIN
    DECLARE v_inspector_id INT UNSIGNED;
    DECLARE v_status VARCHAR(50);
    
    -- الحصول على الفاحص الحالي
    SELECT inspector_id, status INTO v_inspector_id, v_status
    FROM inspection_requests 
    WHERE id = p_request_id;
    
    START TRANSACTION;
    
    -- تحديث الطلب
    UPDATE inspection_requests 
    SET status = 'cancelled',
        cancellation_reason = p_reason,
        cancelled_by = p_cancelled_by,
        cancelled_at = NOW(),
        updated_at = NOW()
    WHERE id = p_request_id;
    
    -- إذا كان هناك فاحص معين، تقليل عدد طلباته
    IF v_inspector_id IS NOT NULL AND v_status = 'assigned' THEN
        UPDATE inspectors 
        SET current_requests = current_requests - 1 
        WHERE id = v_inspector_id;
    END IF;
    
    -- تسجيل في التايم لاين
    INSERT INTO request_timeline (request_id, status, notes, created_by)
    VALUES (p_request_id, 'cancelled', CONCAT('تم الإلغاء: ', p_reason), p_cancelled_by);
    
    COMMIT;
END$$

-- ============================================
-- إجراء: الحصول على تقرير أداء الفاحصين
-- ============================================
CREATE PROCEDURE GetInspectorsPerformanceReport(
    IN p_date_from DATE,
    IN p_date_to DATE
)
BEGIN
    SELECT 
        u.full_name,
        i.specialization,
        i.city,
        COUNT(DISTINCT ir.id) AS total_requests,
        SUM(CASE WHEN ir.status = 'completed' THEN 1 ELSE 0 END) AS completed_requests,
        ROUND(AVG(ir.client_rating), 2) AS avg_rating,
        SUM(CASE WHEN DATE(ir.created_at) BETWEEN p_date_from AND p_date_to THEN 1 ELSE 0 END) AS period_requests
    FROM inspectors i
    JOIN users u ON i.user_id = u.id
    LEFT JOIN inspection_requests ir ON i.id = ir.inspector_id
        AND (p_date_from IS NULL OR DATE(ir.created_at) >= p_date_from)
        AND (p_date_to IS NULL OR DATE(ir.created_at) <= p_date_to)
    GROUP BY i.id
    ORDER BY completed_requests DESC;
END$$

-- ============================================
-- إجراء: الحصول على تقرير مالي
-- ============================================
CREATE PROCEDURE GetFinancialReport(
    IN p_date_from DATE,
    IN p_date_to DATE
)
BEGIN
    SELECT 
        DATE(created_at) AS date,
        COUNT(*) AS total_requests,
        SUM(total_amount) AS total_revenue,
        SUM(deposit_amount) AS total_deposits,
        AVG(total_amount) AS average_order_value,
        SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END) AS paid_revenue,
        SUM(CASE WHEN payment_status = 'pending' THEN total_amount ELSE 0 END) AS pending_revenue
    FROM inspection_requests
    WHERE DATE(created_at) BETWEEN p_date_from AND p_date_to
    GROUP BY DATE(created_at)
    ORDER BY date DESC;
END$$

-- ============================================
-- إجراء: البحث عن الفاحص المتاح
-- ============================================
CREATE PROCEDURE FindAvailableInspector(
    IN p_city VARCHAR(50),
    IN p_specialization VARCHAR(255),
    IN p_date DATE,
    IN p_time TIME
)
BEGIN
    SELECT 
        i.*,
        u.full_name,
        u.phone,
        (i.max_daily_requests - i.current_requests) AS available_slots
    FROM inspectors i
    JOIN users u ON i.user_id = u.id
    WHERE i.city = p_city
        AND i.available = TRUE
        AND i.current_requests < i.max_daily_requests
        AND (p_specialization IS NULL OR i.specialization LIKE CONCAT('%', p_specialization, '%'))
        AND NOT EXISTS (
            SELECT 1 FROM inspector_leaves 
            WHERE inspector_id = i.id AND leave_date = p_date
        )
    ORDER BY i.current_requests ASC, i.rating DESC
    LIMIT 5;
END$$

DELIMITER ;