SELECT total seats, COUNT(*) INTO train_capacity, current_reserved_seats 
    FROM trains
INNER JOIN reservations ON trains.train_id =reservations.train_id 
    WHERE trains.train_id=p_train_id
    GROUP BY trains .train_id;
--Check
IF current reserved_seats < train_capacity THEN

INSERT INTO reservations (train_id, passenger id, reservation_date)
    VALUES (p_train id, p_passenger_id, p_reservation_date);
UPDATE trains
SET total seats train capacity current_reserved_seats
    WHERE train id =p_train_id;
ELSE 
    SELECT 'No available seats. AS results 
    END IF;
    END;
//
SELECT 'Reservation successful. AS results
 DELIMETER;
CALL make reservation (1, 3,' 2023-11-23 12:00:00');