Implemenation.sql

USE libraryloansystem;

-- ================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 01 :::: Trigger to check availability before inserting into Borrowedby table 
-- ================================================================================================================

DELIMITER //
DROP TRIGGER IF EXISTS checkBookAvailability //
CREATE TRIGGER checkBookAvailability
BEFORE INSERT ON Borrowedby
FOR EACH ROW
BEGIN
  DECLARE available_books INT;
  -- Count the number of available books in the specified branch
  SELECT COUNT(*) INTO available_books
  FROM Holding
  WHERE BookID = NEW.BookID AND BranchID = NEW.BranchID AND InStock > OnLoan;
  
  -- If no available books, stop the insertion
  IF available_books = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'No available copies of the book in the specified branch';
  END IF;
END;
//
DELIMITER ;

-- Testing this trigger <<< checkBookAvailability >>>
INSERT INTO Borrowedby (BranchID,BookID,MemberID,DateBorrowed,DateReturned,ReturnDueDate)
VALUES ('1', '1','2','2023-10-28',NULL,'2023-11-04');


-- ================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 02 ::: Trigger to check member status before inserting into Borrowedby table
-- ================================================================================================================

DELIMITER //
DROP TRIGGER IF EXISTS checkMemberStatus //
CREATE TRIGGER checkMemberStatus
BEFORE INSERT ON Borrowedby
FOR EACH ROW
BEGIN
  DECLARE member_status VARCHAR(9);
  -- Get the member status for the given MemberID
  SELECT MemberStatus INTO member_status
  FROM Member
  WHERE MemberID = NEW.MemberID;
  
  -- If member status is not 'REGULAR', prevent the insertion
  IF member_status <> 'REGULAR' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Only REGULAR members can borrow books. Your account is suspended, and you cannot borrow books.';
  END IF;
END;
//
DELIMITER ;

-- ======================================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 03 ::: Trigger to enforce a rule of Each member can borrow one copy of the same book on the same day
-- ======================================================================================================================================
DELIMITER //
DROP TRIGGER IF EXISTS CheckBorrowing //
CREATE TRIGGER CheckBorrowing
BEFORE INSERT ON Borrowedby
FOR EACH ROW
BEGIN
    DECLARE existingBorrowCount INT;

    -- Check if the member has already borrowed the same book on the same day
    SELECT COUNT(*) INTO existingBorrowCount
    FROM Borrowedby
    WHERE BranchID = NEW.BranchID
    AND BookID = NEW.BookID
    AND MemberID = NEW.MemberID
    AND DateBorrowed = NEW.DateBorrowed;

    IF existingBorrowCount > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Oops! The member has already borrowed this book on the same day. Please choose another book or try again tomorrow.';
    END IF;
END//
DELIMITER ;

-- =======================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 04 ::: Trigger to do a restriction like a member can borrow up to 5 items for 3 weeks
-- =======================================================================================================================
DELIMITER //
DROP TRIGGER IF EXISTS CheckBorrowingHistory // 
CREATE TRIGGER CheckBorrowingHistory
BEFORE INSERT ON Borrowedby
FOR EACH ROW
BEGIN
    DECLARE borrowedCount INT;
    
    -- Count the number of books borrowed by the member in the last 3 weeks
    SELECT COUNT(*) INTO borrowedCount
    FROM Borrowedby
    WHERE MemberID = NEW.MemberID
    AND DateBorrowed >= DATE_SUB(CURDATE(), INTERVAL 3 WEEK);
    
    -- Check if the member has borrowed more than 5 books in the last 3 weeks
    IF borrowedCount >= 5 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Member has already borrowed 5 or more books in the last 3 weeks.';
    END IF;
END//

DELIMITER ;

-- ===========================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 05  ::: Implementing a trigger to do this validation - 
--                                         The return due date of the borrowed book cannot be past the membership expiry date
-- ===========================================================================================================================

DELIMITER //
DROP TRIGGER IF EXISTS CheckReturnDueDate // 
CREATE TRIGGER CheckReturnDueDate
BEFORE INSERT ON Borrowedby
FOR EACH ROW
BEGIN
    DECLARE memberExpirationDate DATE;
    
    -- Get the member's expiration date
    SELECT MemberExpDate INTO memberExpirationDate
    FROM Member
    WHERE MemberID = NEW.MemberID;
    
    -- Check if the return due date is past the membership expiration date
    IF NEW.ReturnDueDate > memberExpirationDate THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Return due date cannot be past the membership expiration date.';
    END IF;
END//
DELIMITER ;


-- ==================================================================================================================================================
-- IMPLEMENTATION OF BUSINESS RULE 06  ::: Implementing a Trigger to a suspended membership of member who has an outstanding fee* it has reached $30.
-- ===================================================================================================================================================
DELIMITER //
DROP TRIGGER IF EXISTS CheckOutstandingFees // 
CREATE TRIGGER CheckOutstandingFees BEFORE UPDATE ON Member
FOR EACH ROW
BEGIN
	DECLARE numberOfSuspensionPerDay INT;
	
	SELECT COUNT(*) INTO numberOfSuspensionPerDay FROM PreviousSuspensionLog WHERE MemberID = NEW.MemberID AND SuspendedDate = CURDATE();
	
    -- If outstanding fees reach or exceed $30 and the member is not already suspended
    IF NEW.FineFees >= 30 AND NEW.MemberStatus != 'SUSPENDED' THEN
        SET NEW.MemberStatus = 'SUSPENDED';
        
        -- Insert a record into PreviousSuspensionLog table with MemberID and current date
        IF numberOfSuspensionPerDay = 0 THEN
			INSERT INTO PreviousSuspensionLog (`MemberID`, `SuspendedDate`) VALUES (NEW.MemberID, CURDATE());
        ELSE
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'This Member has been suspended perviously for this day. So, it should be a accidental enter';
        END IF;
    END IF;
END;
//
DELIMITER ;

-- IMPLEMENTATION OF BUSINESS RULE 07  <<< This business rule is implemented in Task 1 - Question 1
-- IMPLEMENTATION OF BUSINESS RULE 08  <<< This business rule is implemented in Task 2 - Question 1

-- ==========================================================================================================================================================
-- ==========================================================================================================================================================

-- *****************************************************
-- 							TASK 01
-- *****************************************************
-- Question 1
ALTER TABLE Member
ADD COLUMN FineFees DECIMAL(8, 2) DEFAULT 0;

DELIMITER //
DROP TRIGGER IF EXISTS AllocateFineFee //
CREATE TRIGGER AllocateFineFee
BEFORE UPDATE ON Borrowedby
FOR EACH ROW
BEGIN
    DECLARE fine_fee DECIMAL(8, 2);
    DECLARE total_overdue_days INT;
    DECLARE total_fine DECIMAL(8, 2);

     -- Declare variables for Member details
    DECLARE member_id INT;
    DECLARE current_fine DECIMAL(8, 2);

    -- Create a cursor to retrieve MemberID and current FineFee from Member table
    DECLARE member_cursor CURSOR FOR
        SELECT MemberID, FineFees
        FROM Member
        WHERE MemberID = NEW.MemberID;

    -- Open the cursor and fetch Member details
    OPEN member_cursor;
    FETCH member_cursor INTO member_id, current_fine;
    CLOSE member_cursor;
    
    -- Calculate overdue days
    SET total_overdue_days = DATEDIFF(NEW.DateReturned, NEW.ReturnDueDate);

    -- Calculate fine fee (assuming $2 per day for each overdue item)
    SET fine_fee = 2 * total_overdue_days;
	
	-- Calculate total fine (considering existing fine and new fine)
	SET total_fine = fine_fee + current_fine; -- Add the current FineFee to the new fine
   
    -- Update FineFee column in the Member table
    UPDATE Member
    SET FineFees = total_fine
    WHERE MemberID = NEW.MemberID;
END;
//
DELIMITER ;

-- *****************************************************
-- 							TASK 02
-- *****************************************************
-- Question 1
DELIMITER //
DROP TRIGGER IF EXISTS resetMemberStatus //
CREATE TRIGGER resetMemberStatus
BEFORE UPDATE ON Member
FOR EACH ROW 
BEGIN
    DECLARE TotalOverdue INT;
    DECLARE ExistingFine DECIMAL(10, 2);
    
    -- Error handling variables declared here
    DECLARE errorOccurred INT DEFAULT 0;
    
    BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	SET errorOccurred = 1;

    -- Calculating the total overdue books
    SELECT COUNT(*) INTO TotalOverdue
        FROM Borrowedby
        WHERE MemberID = NEW.MemberID 
        AND DateReturned IS NULL 
        AND ReturnDueDate < CURDATE();
	END;
    
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            SET errorOccurred = 1;
            
        -- Retrieving the Fine
        SELECT FineFees INTO ExistingFine FROM Member WHERE MemberID = NEW.MemberID;
    END;

    -- Handle trigger logic with error handling
    IF errorOccurred = 0 AND ExistingFine = 0 AND TotalOverdue = 0 THEN
        SET NEW.MemberStatus = 'REGULAR';
    END IF;
END;
//
DELIMITER ;

-- Test Planning and Execution
-- Adding a New Member
INSERT INTO Member (MemberID,MemberStatus,MemberName,MemberAddress,MemberSuburb,MemberState,MemberExpDate,MemberPhone) 
VALUES ('7','REGULAR','Gishor','Bridge Road','Ryde','NSW','2023-10-01','0467751020');

-- Inserting Member Borrowing Book Details
INSERT INTO Borrowedby (BranchID, BookID, MemberID, DateBorrowed, ReturnDueDate)
VALUES ('3', '5', '7', '2023-10-16', '2023-10-23');

UPDATE Borrowedby SET DateReturned = '2023-10-29' WHERE BookIssueID = 24;  -- Book Return Date Updated for BookIssueID: 24

INSERT INTO Borrowedby (BranchID, BookID, MemberID, DateBorrowed, ReturnDueDate)
VALUES ('1', '2', '7', '2023-10-08', '2023-10-13');

UPDATE Borrowedby SET DateReturned = '2023-10-29' WHERE BookIssueID = 25;  -- Book Return Date Updated for BookIssueID: 25

UPDATE Member SET FineFees = 0.00 WHERE MemberID = 7;

SELECT * FROM Member;



-- *****************************************************
-- 							TASK 03
-- *****************************************************

CREATE TABLE PreviousSuspensionLog(
	SuspensionLogID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	MemberID INT NOT NULL,
    SuspendedDate DATE, 
	PRIMARY KEY (SuspensionLogID),
	CONSTRAINT member_reviousSuspensionLog FOREIGN KEY (MemberID) REFERENCES Member (MemberID)
);

SELECT * FROM PreviousSuspensionLog

-- Trigger in Business Rule 06 was updated

DELIMITER //
DROP PROCEDURE IF EXISTS EndSuspendedMemberships //
CREATE PROCEDURE EndSuspendedMemberships()
BEGIN
    DECLARE memberId INT;
    DECLARE suspensionCount INT;
    DECLARE memberNotFound BOOLEAN DEFAULT FALSE;

    -- Declare handler for not found exception
    DECLARE NOT_FOUND CONDITION FOR SQLSTATE '45000';

    -- Cursor to select members with suspended membership
    DECLARE memberCursor CURSOR FOR
        SELECT MemberID
        FROM Member
        WHERE MemberStatus = 'SUSPENDED';

    -- Error handler for not found exception
    DECLARE CONTINUE HANDLER FOR NOT_FOUND
        SET memberNotFound = TRUE;

    -- Open the cursor and process members
    OPEN memberCursor;
    read_loop: LOOP
        FETCH memberCursor INTO memberId;
        IF memberNotFound THEN
            LEAVE read_loop;
        END IF;

        -- Check if the member has been suspended twice in the past three years
        SET suspensionCount = (
            SELECT COUNT(*)
            FROM PreviousSuspensionLog
            WHERE MemberID = memberId
            AND SuspendedDate >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
        );

        -- If suspended twice in the past three years, terminate membership
        IF suspensionCount >= 2 THEN
            BEGIN
                -- Error handling block
                DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                    SET memberNotFound = TRUE;
                
                -- Update MemberStatus to 'TERMINATED'
                UPDATE Member
                SET MemberStatus = 'TERMINATED'
                WHERE MemberID = memberId;
                
                -- Reset memberNotFound flag
                SET memberNotFound = FALSE;
            END;
        END IF;
    END LOOP;

    -- Close the cursor
    CLOSE memberCursor;

    -- Error handling
    IF memberNotFound THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error occurred during the procedure execution. While Trying to find the specific member.';
    END IF;
END//
DELIMITER ;