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


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

-- variables

in_stock Video.InStock%type;
limit Video.Vlimits%type;
member_age date;
member_dob date;
member_count Member.MemberCount%type;
shop_name Shop.ShopName%type;
rental_num number;
found_video number;
found_member number;
err_message varchar(50);

-- exceptions

invalid_title exception;
invalid_id exception;
not_in_stock exception;
under_age exception;
loan_limit 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;
   
   -- check video in stock
   select InStock into in_stock from Video where Vtitle = video_title;        
       IF in_stock != 'y' THEN raise not_in_stock; 
       END IF;     
  
   -- If the video is an 18 check member age
   select Vlimits into limit from Video where Vtitle = video_title;
       IF limit = '18' THEN
          select MemberDOB into member_dob from Member where MemberId = member_id; 
       END IF;  
       
   -- check member age is OK if video is an '18'
   IF limit = '18' AND (member_dob > (sysdate - (18*365))) THEN raise under_age;
   END IF;

   -- create a Rental tuple
   select count(*) into rental_num from Rental; 
   select ShopName into shop_name from Shop;
   insert into Rental values (rental_num, video_title, member_id, SYSDATE+ 14, shop_name, 'n');  

   -- update other relations
   update Video set InStock = 'n' where Vtitle = video_title;

   -- check MemberCount and update at all locations
   select MemberCount into member_count from Member where MemberId = member_id;
   IF member_count = 5 THEN raise loan_limit; 
   END IF;
   update Member set MemberCount = MemberCount + 1 where MemberId = member_id;
   update nakeesan.Member set MemberCount = MemberCount + 1 where MemberId = member_id;

   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_in_stock THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The requested video: ' || video_title || 
                ' is on loan.');
        COMMIT;
 WHEN under_age THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The requested video: ' || video_title || 
                ' is 18 certificate - the member is under-age.');
        COMMIT;
 WHEN loan_limit THEN
        ROLLBACK;
        delete from message_s;
        insert into message_s 
                values('The member: ' || member_id || 
                ' is already at the lending limit (5).');
        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;          
/