IN p_order_id BIGINT,
    IN p_customer_user_id BIGINT,
    IN p_pickup_lat DECIMAL(10, 8),
    IN p_pickup_lng DECIMAL(11, 8),
    OUT p_assigned_driver_id BIGINT

proc_label: BEGIN
    DECLARE v_driver_id BIGINT DEFAULT NULL;
    DECLARE v_order_cod DECIMAL(15, 2) DEFAULT 0.00;
    DECLARE v_order_vol DECIMAL(4, 1) DEFAULT 1.0;
    DECLARE v_auto_count INT DEFAULT 0;
    DECLARE v_new_active_cnt INT DEFAULT 0;
    DECLARE v_driver_deposit DECIMAL(15, 2) DEFAULT 0.00;
    DECLARE v_err_no INT DEFAULT 0;
    DECLARE v_err_msg TEXT DEFAULT '';

    -- 1. XỬ LÝ KHI CÓ LỖI XẢY RA: ROLLBACK và đưa đơn về 'pending'
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_err_no = MYSQL_ERRNO, v_err_msg = MESSAGE_TEXT;
        ROLLBACK;
        SET p_assigned_driver_id = NULL;
        UPDATE orders 
        SET status = 'pending', updated_at = NOW() 
        WHERE id = p_order_id AND status = 'finding_driver';
        COMMIT;
    END;

    SET p_assigned_driver_id = NULL;

    -- 2. ĐIỀU KIỆN LỌC ĐẦU VÀO (Khóa dòng đơn hàng để chống race condition)
    START TRANSACTION;

    IF NOT EXISTS (
        SELECT 1 FROM orders 
        WHERE id = p_order_id 
          AND status IN ('finding_driver', 'pending') 
          AND driver_id IS NULL
        FOR UPDATE
    ) OR EXISTS (
        SELECT 1 FROM order_dispatch_logs 
        WHERE order_id = p_order_id 
          AND status = 'sent' 
          AND dispatched_at >= NOW() - INTERVAL 35 SECOND
    ) THEN
        COMMIT;
        LEAVE proc_label;
    END IF;

    -- 3. LẤY THÔNG TIN ĐƠN HÀNG
    SELECT
        COALESCE(total_cod_amount, 0),
        CASE UPPER(TRIM(COALESCE(package_size, 'S')))
            WHEN 'XS'  THEN 0.5
            WHEN 'S'   THEN 1.0
            WHEN 'M'   THEN 2.0
            WHEN 'L'   THEN 3.0
            WHEN 'XL'  THEN 4.0
            WHEN '2XL' THEN 5.0
            ELSE 1.0
        END
    INTO v_order_cod, v_order_vol
    FROM orders
    WHERE id = p_order_id
    LIMIT 1;

    -- 4. TÌM TÀI XẾ PHÙ HỢP
    SELECT d.id INTO v_driver_id
    FROM drivers d
    LEFT JOIN (
        SELECT
            driver_id,
            COUNT(*) AS active_cnt,
            COALESCE(SUM(total_cod_amount), 0) AS current_cod_sum,
            COALESCE(SUM(
                CASE UPPER(TRIM(COALESCE(package_size, 'S')))
                    WHEN 'XS'  THEN 0.5
                    WHEN 'S'   THEN 1.0
                    WHEN 'M'   THEN 2.0
                    WHEN 'L'   THEN 3.0
                    WHEN 'XL'  THEN 4.0
                    WHEN '2XL' THEN 5.0
                    ELSE 1.0
                END
            ), 0) AS current_vol_sum
        FROM orders
        WHERE driver_id IS NOT NULL
          AND status IN ('picking_up', 'picked_up', 'delivering')
        GROUP BY driver_id
    ) active_info ON active_info.driver_id = d.id
    WHERE d.status IN ('online', 'busy')
      AND d.is_auto_accept = TRUE
      AND NOT (d.user_id <=> p_customer_user_id) -- Bỏ chọn tài xế trùng với khách hàng
      AND d.current_lat IS NOT NULL AND d.current_lng IS NOT NULL
      AND p_pickup_lat IS NOT NULL AND p_pickup_lng IS NOT NULL

      -- ĐIỀU KIỆN 1: TÀI XẾ TẮT TẢI HÀNG THÊM
      AND NOT (COALESCE(active_info.active_cnt, 0) >= 1 AND COALESCE(d.allow_extra_capacity, 1) = 0)

      -- ĐIỀU KIỆN 2: HẠN MỨC ĐƠN GHÉP THEO KÝ QUỸ
      AND (
          COALESCE(active_info.active_cnt, 0) = 0
          OR (
              COALESCE(active_info.active_cnt, 0) < IF(COALESCE(d.deposit_amount, 0) >= 1000000.00, 3, IF(COALESCE(d.deposit_amount, 0) >= 500000.00, 2, 1))
              AND (
                  d.is_auto_batch_enabled = 1
                  OR EXISTS (
                      SELECT 1 FROM orders o_batch
                      WHERE o_batch.driver_id = d.id
                        AND o_batch.status IN ('picking_up', 'picked_up', 'delivering')
                        AND o_batch.is_auto_batch_enabled = 1
                  )
              )
          )
      )

      -- ĐIỀU KIỆN 3: SỨC CHỨA CỒNG KỀNH
      AND (COALESCE(active_info.current_vol_sum, 0) + v_order_vol) <= IF(COALESCE(d.allow_extra_capacity, 1) = 1, 7.0, 6.0)

      -- ĐIỀU KIỆN 4: HẠN MỨC COD
      AND (COALESCE(active_info.current_cod_sum, 0) + v_order_cod) <= COALESCE(d.max_cod_amount, 2000000.00)

      -- ĐIỀU KIỆN 5: BÁN KÍNH LẤY HÀNG (<= d.max_distance_km)
      AND (6371 * ACOS(
          LEAST(1.0, GREATEST(-1.0,
              COS(RADIANS(p_pickup_lat)) * COS(RADIANS(d.current_lat)) *
              COS(RADIANS(d.current_lng) - RADIANS(p_pickup_lng)) +
              SIN(RADIANS(p_pickup_lat)) * SIN(RADIANS(d.current_lat))
          ))
      )) <= COALESCE(d.max_distance_km, 3.0)

      -- ĐIỀU KIỆN 6: KIỂM TRA CÙNG TUYẾN (Không có đơn active nào bị ngược hướng)
      AND (
          COALESCE(active_info.active_cnt, 0) = 0
          OR NOT EXISTS (
              SELECT 1
              FROM orders o_active
              JOIN order_stops s_p1 ON s_p1.order_id = o_active.id AND s_p1.stop_type = 'pickup'
              JOIN order_stops s_d1 ON s_d1.order_id = o_active.id AND s_d1.stop_type = 'dropoff'
              LEFT JOIN order_stops s_d2 ON s_d2.order_id = p_order_id AND s_d2.stop_type = 'dropoff'
              WHERE o_active.driver_id = d.id
                AND o_active.status IN ('picking_up', 'picked_up', 'delivering')
                AND s_d2.id IS NOT NULL
                AND (
                    -- A. Khoảng cách 2 điểm dropoff > 3.0km
                    (6371 * ACOS(
                        LEAST(1.0, GREATEST(-1.0,
                            COS(RADIANS(s_d1.lat)) * COS(RADIANS(s_d2.lat)) *
                            COS(RADIANS(s_d2.lng) - RADIANS(s_d1.lng)) +
                            SIN(RADIANS(s_d1.lat)) * SIN(RADIANS(s_d2.lat))
                        ))
                    )) > 3.0
                    OR
                    -- B. Tính góc vector < 0.707 (góc lệch > 45 độ)
                    COALESCE(
                        ((s_d1.lat - s_p1.lat) * (s_d2.lat - p_pickup_lat) +
                         (s_d1.lng - s_p1.lng) * (s_d2.lng - p_pickup_lng))
                        /
                        NULLIF(
                            (SQRT(POW(s_d1.lat - s_p1.lat, 2) + POW(s_d1.lng - s_p1.lng, 2)) *
                             SQRT(POW(s_d2.lat - p_pickup_lat, 2) + POW(s_d2.lng - p_pickup_lng, 2))),
                            0
                        ),
                        1.0
                    ) < 0.707
                )
          )
      )

      -- ĐIỀU KIỆN 7: Dùng NOT EXISTS chống NULL khi kiểm tra danh sách đã từ chối/timeout
      AND NOT EXISTS (
          SELECT 1 FROM order_dispatch_logs l
          WHERE l.order_id = p_order_id
            AND l.driver_id = d.id
            AND l.status IN ('rejected', 'timeout')
      )
    ORDER BY
        COALESCE(active_info.active_cnt, 0) ASC,
        (6371 * ACOS(
            LEAST(1.0, GREATEST(-1.0,
                COS(RADIANS(p_pickup_lat)) * COS(RADIANS(d.current_lat)) *
                COS(RADIANS(d.current_lng) - RADIANS(p_pickup_lng)) +
                SIN(RADIANS(p_pickup_lat)) * SIN(RADIANS(d.current_lat))
            ))
        )) ASC,
        COALESCE(d.rating_average, 5.0) DESC
    LIMIT 1;

    -- 5. XỬ LÝ KẾT QUẢ GÁN ĐƠN
    IF v_driver_id IS NOT NULL THEN
        -- Lấy tiền ký quỹ của tài xế
        SELECT COALESCE(deposit_amount, 0) INTO v_driver_deposit
        FROM drivers WHERE id = v_driver_id;

        -- Đếm số đơn hoàn thành trong ngày
        SELECT COALESCE(
            (SELECT completed_orders_count FROM driver_daily_earnings WHERE driver_id = v_driver_id AND stat_date = CURDATE() LIMIT 1),
            (SELECT COUNT(*) FROM orders WHERE driver_id = v_driver_id AND status = 'completed' AND DATE(created_at) = CURDATE()),
            0
        ) INTO v_auto_count;

        IF v_auto_count < 3 THEN
            -- Gán trực tiếp & nhận đơn tự động
            UPDATE drivers SET status = 'busy' WHERE id = v_driver_id;

            UPDATE orders
            SET driver_id = v_driver_id,
                status = 'picking_up',
                assignment_type = 'auto',
                updated_at = NOW()
            WHERE id = p_order_id;

            INSERT INTO order_dispatch_logs (order_id, driver_id, status, dispatched_at, responded_at)
            VALUES (p_order_id, v_driver_id, 'accepted', NOW(), NOW())
            ON DUPLICATE KEY UPDATE status = 'accepted', responded_at = NOW();

            INSERT INTO order_status_history (order_id, status, note)
            VALUES (p_order_id, 'picking_up', 'Hệ thống tự động ghép đơn cho tài xế');

            -- Đếm số đơn active và kiểm tra điều kiện tắt batching
            SELECT COUNT(*) INTO v_new_active_cnt
            FROM orders
            WHERE driver_id = v_driver_id
              AND status IN ('picking_up', 'picked_up', 'delivering');

            IF v_new_active_cnt >= IF(v_driver_deposit >= 1000000.00, 3, IF(v_driver_deposit >= 500000.00, 2, 1)) THEN
                UPDATE drivers SET is_auto_batch_enabled = 0 WHERE id = v_driver_id;
                UPDATE orders SET is_auto_batch_enabled = 0 WHERE driver_id = v_driver_id AND status IN ('picking_up', 'picked_up', 'delivering');
            END IF;

            SET p_assigned_driver_id = v_driver_id;
        ELSE
            -- Bắn đơn dạng offer ('sent'), giữ status orders = 'finding_driver', chưa gán driver_id
            INSERT INTO order_dispatch_logs (order_id, driver_id, status, dispatched_at)
            VALUES (p_order_id, v_driver_id, 'sent', NOW())
            ON DUPLICATE KEY UPDATE status = 'sent', dispatched_at = NOW();

            SET p_assigned_driver_id = NULL;
        END IF;
    ELSE
        -- KHÔNG TÌM THẤY TÀI XẾ -> ĐỔI TRẠNG THÁI VỀ 'pending'
        UPDATE orders
        SET status = 'pending',
            updated_at = NOW()
        WHERE id = p_order_id AND status = 'finding_driver';

        SET p_assigned_driver_id = NULL;
    END IF;

    COMMIT;
END