--function to check seat availability for a train I
 DELIMITER//
CREATE FUNCTION check_seat_availability(p_train_id INT) RETURNS VARCHAR(255)
BEGIN
DECLARE train capacity INT;
DECLARE current reserved seats INT;
--total seats and current_reserved_seats for the corresponding train
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
    RETURN "Seats available for reservation.";

ELSE 
    RETURN 'No available seats."; 
    END IF;
 END;
//DELIMITER;
SELECT check seat_availability(1) AS seat_availability,