-- This query should fetch the whole tours that need to be matched with an agent
-- We should take the closest agent to the tour's apartment
-- We should check that this agent doesn't have exists tour_request for this tour
-- We should check that this agent doesn't have another tour for this time (agent can't be at 2 places at the same time)

SELECT
    tours.id as tour_id,
    users.id as matched_user_id,
    ((ACOS(SIN(apartments.latitude * PI() / 180) * SIN(users.latitude * PI() / 180) + COS(apartments.latitude * PI() / 180) * COS(
        users.latitude * PI() / 180) * COS((apartments.longitude - users.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.853159616) as distance
FROM
    tours
INNER JOIN
    apartments
        ON
            tours.apartment_id = apartments.id
INNER JOIN
    users
LEFT OUTER JOIN
    tour_requests
        ON
            tour_requests.tour_id = tours.id AND
            tour_requests.user_id = users.id

LEFT OUTER JOIN
    tours as user_tours
        ON
            user_tours.user_id = users.id AND
            user_tours.tour_at = tours.tour_at
WHERE
    tours.status = 'pending' AND
    tours.user_id is NULL AND
    tours.tour_at < DATE_ADD(NOW(), INTERVAL 90 MINUTE) AND
    tours.tour_at > DATE_ADD(NOW(), INTERVAL 35 MINUTE) AND
    tour_requests.id is NULL AND
    user_tours.id is NULL
ORDER BY distance
LIMIT 1