File size: 5,247 Bytes
64075d7 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | -- Make available_seats and total_seats nullable on driver_trips.
-- Group-message trips often lack seat info; store NULL instead of defaulting.
ALTER TABLE public.driver_trips
ALTER COLUMN available_seats DROP NOT NULL,
ALTER COLUMN total_seats DROP NOT NULL;
-- Replace the check constraint so it only enforces when both values are present
ALTER TABLE public.driver_trips
DROP CONSTRAINT IF EXISTS driver_trips_available_not_over_total;
ALTER TABLE public.driver_trips
ADD CONSTRAINT driver_trips_available_not_over_total
CHECK (available_seats IS NULL OR total_seats IS NULL OR available_seats <= total_seats);
-- Drop BOTH overloads of match_active_trips and recreate with NULL-safe seats filter
DROP FUNCTION IF EXISTS public.match_active_trips(
extensions.vector(1024), float, int, text, text, date, text, time, int, text
);
DROP FUNCTION IF EXISTS public.match_active_trips(
extensions.vector(1024), float, int, text, text, text, date, text, time, int, text
);
-- Recreate with filter_driver_name (the version the code actually calls)
CREATE OR REPLACE FUNCTION public.match_active_trips(
query_embedding extensions.vector(1024),
match_threshold float DEFAULT 0.0,
match_count int DEFAULT 10,
filter_departure text DEFAULT NULL,
filter_destination text DEFAULT NULL,
filter_driver_name text DEFAULT NULL,
filter_departure_date date DEFAULT NULL,
filter_departure_time text DEFAULT NULL,
filter_requested_time time DEFAULT NULL,
filter_seats int DEFAULT 1,
filter_vehicle_type text DEFAULT NULL
)
RETURNS TABLE (
trip_id uuid,
departure text,
destination text,
departure_date date,
departure_time text,
available_seats integer,
total_seats integer,
price numeric,
status text,
driver_name text,
driver_phone_number text,
car_type text,
chunk_text text,
similarity float,
time_difference_minutes integer,
registered boolean
)
LANGUAGE sql STABLE
AS $$
WITH ranked AS (
SELECT
driver_trips.id AS trip_id,
driver_trips.departure,
driver_trips.destination,
driver_trips.departure_date,
driver_trips.departure_time,
driver_trips.available_seats,
driver_trips.total_seats,
driver_trips.price,
driver_trips.status,
customers.name AS driver_name,
customers."remoteJid" AS driver_phone_number,
driver_cars.car_type,
driver_trip_embeddings.chunk_text,
COALESCE(customers.registered, false) AS registered,
1 - (driver_trip_embeddings.embedding <=> query_embedding) AS similarity,
driver_trip_embeddings.embedding <=> query_embedding AS vector_distance,
CASE
WHEN filter_requested_time IS NULL THEN NULL
ELSE abs(
extract(epoch FROM (
public.departure_bucket_clock_time(driver_trips.departure_time)
- filter_requested_time
)) / 60
)::integer
END AS time_difference_minutes
FROM public.driver_trip_embeddings
JOIN public.driver_trips ON driver_trips.id = driver_trip_embeddings.trip_id
LEFT JOIN public.drivers ON drivers.id = driver_trips.driver_id
LEFT JOIN public.customers ON customers.id = drivers.customer_id
LEFT JOIN public.driver_cars ON driver_cars.id = driver_trips.car_id
WHERE driver_trips.status = 'active'
AND (driver_trips.available_seats IS NULL OR driver_trips.available_seats >= COALESCE(filter_seats, 1))
AND (filter_departure IS NULL OR driver_trips.departure ILIKE '%' || filter_departure || '%')
AND (filter_destination IS NULL OR driver_trips.destination ILIKE '%' || filter_destination || '%')
AND (filter_driver_name IS NULL OR customers.name ILIKE '%' || filter_driver_name || '%')
AND (filter_departure_date IS NULL OR driver_trips.departure_date = filter_departure_date)
AND (filter_departure_time IS NULL OR driver_trips.departure_time = filter_departure_time)
AND (filter_vehicle_type IS NULL OR driver_cars.car_type ILIKE '%' || filter_vehicle_type || '%')
AND (
driver_trips.departure_date > (NOW() AT TIME ZONE 'Asia/Aden')::date
OR (
driver_trips.departure_date = (NOW() AT TIME ZONE 'Asia/Aden')::date
AND (
(NOW() AT TIME ZONE 'Asia/Aden')::time < TIME '12:00'
OR (
(NOW() AT TIME ZONE 'Asia/Aden')::time < TIME '18:00'
AND driver_trips.departure_time IN ('noon', 'night')
)
OR (
(NOW() AT TIME ZONE 'Asia/Aden')::time >= TIME '18:00'
AND driver_trips.departure_time = 'night'
)
)
)
)
)
SELECT
ranked.trip_id,
ranked.departure,
ranked.destination,
ranked.departure_date,
ranked.departure_time,
ranked.available_seats,
ranked.total_seats,
ranked.price,
ranked.status,
ranked.driver_name,
ranked.driver_phone_number,
ranked.car_type,
ranked.chunk_text,
ranked.similarity,
ranked.time_difference_minutes,
ranked.registered
FROM ranked
WHERE ranked.similarity >= match_threshold
ORDER BY
ranked.registered DESC,
ranked.time_difference_minutes NULLS LAST,
ranked.departure_date,
ranked.vector_distance
LIMIT match_count;
$$;
|