-- Add GPS fields to vehicle_trips
ALTER TABLE vehicle_trips
    ADD COLUMN IF NOT EXISTS start_latitude DECIMAL(10,6),
    ADD COLUMN IF NOT EXISTS start_longitude DECIMAL(10,6),
    ADD COLUMN IF NOT EXISTS end_latitude DECIMAL(10,6),
    ADD COLUMN IF NOT EXISTS end_longitude DECIMAL(10,6);

-- To convert existing text coordinates stored as 'lat,lon' in start_location
-- and end_location you can run:
-- UPDATE vehicle_trips
--   SET start_latitude = split_part(start_location, ',', 1)::DECIMAL,
--       start_longitude = split_part(start_location, ',', 2)::DECIMAL
-- WHERE start_location ~ '^-?[0-9.]+,-?[0-9.]+$';
-- UPDATE vehicle_trips
--   SET end_latitude = split_part(end_location, ',', 1)::DECIMAL,
--       end_longitude = split_part(end_location, ',', 2)::DECIMAL
-- WHERE end_location ~ '^-?[0-9.]+,-?[0-9.]+$';
