-- John Wilkinson (MS50)
-- Databases (COMS 30103) - Dr. J.Gallagher
-- Video Shop - Assignment 2
------------------------------------------------------------------------
-- Question 2
------------------------------------------------------------------------


CREATE OR REPLACE PROCEDURE returnVideo
	(video_title IN Video.Vtitle%type, 
     	member_id IN Member.MemberId%type)
     	
AS

-- variables

found_video number;
found_member number;
video_on_loan number;
correct_member number; 
err_message varchar(50);

-- exceptions

invalid_title exception;
invalid_id exception;
not_loaned exception;
wrong_member exception;

BEGIN

   -- check whether video exists
   select count(*) into found_video from Video where Video.Vtitle = video_title;
       IF found_video = 0 THEN raise invalid_title;
       END IF;      
   
   -- check whether id exists
   select count(*) into found_member from Member where Member.MemberId = member_id;
       IF found_member = 0 THEN raise invalid_id;
       END IF;
   
   select count(*) into video_on_loan from Rental where VideoTitle = video_title AND
       Returned = 'n';
   -- check video out in Rental file
       IF video_on_loan = 0 THEN raise not_loaned;
       END IF;
   
   -- checks rental table for correct member returning an outstanding video
   select count(*) into correct_member from Rental where VideoTitle = video_title AND
       Returned = 'n' AND MemberId = member_id;         
       IF correct_member = 0 THEN raise wrong_member;
       ELSE
         -- update details
         update Video set InStock = 'y' where Vtitle = video_title;
         update Member set MemberCount = MemberCount - 1 where MemberId = member_id;
         update nakeesan.Member set MemberCount = MemberCount - 1 where MemberId = member_id;
         update Rental set Returned = 'y' where VideoTitle = video_title AND MemberId = member_id;
       END IF;
              
   COMMIT;

EXCEPTION
 WHEN invalid_title THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The requested video: '|| video_title || ' is not held in the collection.');
        COMMIT;
 WHEN invalid_id THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values(member_id || ' is not a regestered member.');
        COMMIT;
 WHEN not_loaned THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The video: ' || video_title || ' is not on loan.');
        COMMIT;
 WHEN wrong_member THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The member: ' || member_id || ' is not registered as having rented: '
                       || video_title);
        COMMIT;
 WHEN OTHERS THEN
        ROLLBACK;
        delete from message_s;
        err_message := SUBSTR(SQLERRM,1,50); -- report Oracle error message
        insert into message_s 
                values(err_message);
        COMMIT;    
END;          
/