Функции, Процедури и Тригери

Во рамки на системот BookNest се имплементирани функции, процедури и тригери кои овозможуваат автоматизација на клучните процеси во библиотеката. Функциите се користат за проверки и пресметки, процедурите за извршување на деловни процеси, а тригерите за автоматско реагирање при промени во податоците.

ФУНКЦИИ

• has_active_membership: Проверува дали даден член има активна и важечка членарина.

  CREATE OR REPLACE FUNCTION has_active_membership(p_user_id INT)
  RETURNS BOOLEAN AS $$
  BEGIN
      RETURN EXISTS (
          SELECT 1
          FROM membership
          WHERE member_user_id = p_user_id
            AND status = 'active'
            AND end_date >= CURRENT_DATE
      );
  END;
  $$ LANGUAGE plpgsql;
  

• get_available_copies: Го враќа бројот на достапни копии за одредена книга.

  CREATE OR REPLACE FUNCTION get_available_copies(p_barcode VARCHAR)
  RETURNS INT AS $$
  DECLARE
      v_count INT;
  BEGIN
      SELECT COUNT(*)
      INTO v_count
      FROM book_copy
      WHERE barcode = p_barcode
        AND status = 'available';
  
      RETURN v_count;
  END;
  $$ LANGUAGE plpgsql;
  
  

• calculate_fine: Ја пресметува казната за задоцнето враќање на книга според бројот на денови доцнење.

  CREATE OR REPLACE FUNCTION calculate_fine(p_loan_id INTEGER)
  RETURNS INTEGER AS $$
  DECLARE
      v_due_date DATE;
      v_return_date DATE;
      v_days_late INT;
      v_fine INT;
  BEGIN
      SELECT due_date, return_date
      INTO v_due_date, v_return_date
      FROM loan_history
      WHERE loan_id = p_loan_id;
  
      IF v_return_date IS NULL THEN
          RETURN 0;
      END IF;
  
      v_days_late := v_return_date - v_due_date;
  
      IF v_days_late <= 0 THEN
          RETURN 0;
      END IF;
  
      v_fine := v_days_late * 20;
  
      RETURN v_fine;
  END;
  $$ LANGUAGE plpgsql;
  

ПРОЦЕДУРИ

• borrow_book: Овозможува позајмување книга со проверки за членство, библиотекар и достапна копија.

  CREATE OR REPLACE PROCEDURE borrow_book(
      p_user_id INT,
      p_barcode VARCHAR,
      p_librarian_id INT
  ) AS $$
  DECLARE
      v_copy_id INT;
  BEGIN
      IF NOT EXISTS (
          SELECT 1
          FROM member
          WHERE user_id = p_user_id
      ) THEN
          RAISE EXCEPTION 'User is not a member.';
      END IF;
  
      IF NOT EXISTS (
          SELECT 1
          FROM librarian
          WHERE user_id = p_librarian_id
      ) THEN
          RAISE EXCEPTION 'User is not a librarian.';
      END IF;
  
      IF NOT has_active_membership(p_user_id) THEN
          RAISE EXCEPTION 'Member does not have an active membership.';
      END IF;
  
      SELECT copy_id
      INTO v_copy_id
      FROM book_copy
      WHERE barcode = p_barcode
        AND status = 'available'
      LIMIT 1
      FOR UPDATE;
  
      IF v_copy_id IS NULL THEN
          RAISE EXCEPTION 'No available copies for this book.';
      END IF;
  
      INSERT INTO loan_history (
          member_user_id,
          copy_id,
          librarian_user_id,
          borrow_date,
          due_date,
          status
      )
      VALUES (
          p_user_id,
          v_copy_id,
          p_librarian_id,
          CURRENT_DATE,
          CURRENT_DATE + INTERVAL '14 days',
          'borrowed'
      );
  
      UPDATE book_copy
      SET status = 'borrowed'
      WHERE copy_id = v_copy_id;
  
      RAISE NOTICE 'Book borrowed successfully. Copy ID: %', v_copy_id;
  END;
  $$ LANGUAGE plpgsql;
  

• return_book: Евидентира враќање на книга и ја прави копијата повторно достапна.

  CREATE OR REPLACE PROCEDURE return_book(
      IN p_copy_id INTEGER,
      IN p_is_damaged BOOLEAN
  )
  LANGUAGE plpgsql
  AS $$
  DECLARE
      v_loan_id INTEGER;
  BEGIN
      UPDATE loan_history
      SET return_date = CURRENT_DATE,
          status = 'returned'
      WHERE copy_id = p_copy_id
        AND status = 'borrowed'
      RETURNING loan_id INTO v_loan_id;
  
      IF v_loan_id IS NULL THEN
          RAISE NOTICE 'No active borrowed loan found for copy %.', p_copy_id;
          RETURN;
      END IF;
  
      IF p_is_damaged = TRUE THEN
          UPDATE book_copy
          SET status = 'damaged'
          WHERE copy_id = p_copy_id;
      ELSE
          UPDATE book_copy
          SET status = 'available'
          WHERE copy_id = p_copy_id;
      END IF;
  
      RAISE NOTICE 'Book copy % returned successfully.', p_copy_id;
  END;
  $$;
  

• reserve_event: Креира резервација за настан и го намалува бројот на слободни места.

  CREATE OR REPLACE PROCEDURE reserve_event(
      p_event_id INT,
      p_member_id INT,
      p_seats INT
  )
  LANGUAGE plpgsql
  AS $$
  DECLARE
      v_available_seats INT;
  BEGIN
      SELECT available_seats
      INTO v_available_seats
      FROM event
      WHERE event_id = p_event_id;
  
      IF NOT FOUND THEN
          RAISE EXCEPTION 'Event with ID % does not exist.', p_event_id;
      END IF;
  
      IF v_available_seats < p_seats THEN
          RAISE EXCEPTION 'Not enough available seats.';
      END IF;
  
      INSERT INTO event_reservation(
          event_id,
          member_user_id,
          seats_reserved,
          status
      )
      VALUES(
          p_event_id,
          p_member_id,
          p_seats,
          'reserved'
      );
  
      UPDATE event
      SET available_seats = available_seats - p_seats
      WHERE event_id = p_event_id;
  END;
  $$;
  

• cancel_event_reservation: Откажува резервација и ги враќа резервираните места.

  CREATE OR REPLACE PROCEDURE cancel_event_reservation(
      p_event_reservation_id INT
  )
  LANGUAGE plpgsql
  AS $$
  DECLARE
      v_event_id INT;
      v_seats INT;
      v_current_status event_reservation_status;
  BEGIN
      SELECT event_id, seats_reserved, status
      INTO v_event_id, v_seats, v_current_status
      FROM event_reservation
      WHERE event_reservation_id = p_event_reservation_id;
  
      IF NOT FOUND THEN
          RAISE EXCEPTION 'Reservation with ID % does not exist!', p_event_reservation_id;
      END IF;
  
      IF v_current_status = 'cancelled' THEN
          RAISE EXCEPTION 'Reservation is already cancelled!';
      END IF;
  
      UPDATE event_reservation
      SET status = 'cancelled'
      WHERE event_reservation_id = p_event_reservation_id;
  
      UPDATE event
      SET available_seats = available_seats + v_seats
      WHERE event_id = v_event_id;
  END;
  $$;
  

• create_event: Креира нов настан по проверка на библиотекарот, времето и бројот на места.

  CREATE OR REPLACE PROCEDURE create_event(
      p_title VARCHAR,
      p_description TEXT,
      p_event_date DATE,
      p_start_time TIME,
      p_end_time TIME,
      p_location VARCHAR,
      p_max_seats INT,
      p_created_by INT
  )
  LANGUAGE plpgsql
  AS $$
  BEGIN
  
      IF NOT EXISTS (
          SELECT 1
          FROM librarian
          WHERE user_id = p_created_by
      ) THEN
          RAISE EXCEPTION 'Librarian with ID % does not exist!', p_created_by;
      END IF;
  
  
      IF p_end_time <= p_start_time THEN
          RAISE EXCEPTION 'End time must be greater than start time!';
      END IF;
  
  
      IF p_max_seats <= 0 THEN
          RAISE EXCEPTION 'Max seats must be greater than 0!';
      END IF;
  
  
      INSERT INTO event (
          title,
          description,
          event_date,
          start_time,
          end_time,
          location,
          max_seats,
          available_seats,
          created_by
      )
      VALUES (
          p_title,
          p_description,
          p_event_date,
          p_start_time,
          p_end_time,
          p_location,
          p_max_seats,
          p_max_seats,
          p_created_by
      );
  
  END;
  $$;
  

ТРИГЕРИ

• trigger_update_copy_status: Автоматски го ажурира статусот на копијата при позајмување или враќање.

  CREATE OR REPLACE FUNCTION fn_update_copy_status()
  RETURNS TRIGGER AS $$
  BEGIN
      IF (TG_OP = 'INSERT') THEN
          UPDATE book_copy
          SET status = 'borrowed'
          WHERE copy_id = NEW.copy_id;
  
      ELSIF (NEW.status = 'returned') THEN
          UPDATE book_copy
          SET status = 'available'
          WHERE copy_id = NEW.copy_id;
      END IF;
  
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

  CREATE TRIGGER trigger_update_copy_status
  AFTER INSERT OR UPDATE ON loan_history
  FOR EACH ROW
  EXECUTE FUNCTION fn_update_copy_status();
  

• trigger_create_fine: Автоматски креира казна кога книгата е вратена по рокот.

  CREATE OR REPLACE FUNCTION trigger_create_fine_function()
  RETURNS TRIGGER AS $$
  DECLARE
      v_amount INT;
  BEGIN
      IF NEW.return_date IS NOT NULL
         AND NEW.return_date > NEW.due_date THEN
  
          v_amount := calculate_fine(NEW.loan_id);
  
          IF v_amount > 0 THEN
              INSERT INTO fines(
                  loan_id,
                  amount,
                  payment_due_date,
                  status
              )
              VALUES(
                  NEW.loan_id,
                  v_amount,
                  CURRENT_DATE + INTERVAL '7 days',
                  'unpaid'
              );
          END IF;
      END IF;
  
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

  CREATE TRIGGER trigger_create_fine
  AFTER UPDATE OF return_date ON loan_history
  FOR EACH ROW
  EXECUTE FUNCTION trigger_create_fine_function();
  

• trigger_notification_on_fine: Испраќа нотификација до членот при креирање казна.

  CREATE OR REPLACE FUNCTION trigger_notification_on_fine_function()
  RETURNS TRIGGER AS $$
  DECLARE
      v_member_id INT;
      v_notification_id INT;
  BEGIN
      SELECT member_user_id
      INTO v_member_id
      FROM loan_history
      WHERE loan_id = NEW.loan_id;
  
      INSERT INTO notification(
          member_user_id,
          notification_type,
          title,
          message,
          status
      )
      VALUES(
          v_member_id,
          'fine',
          'New Fine Created',
          'You have received a fine for returning a book late.',
          'pending'
      )
      RETURNING notification_id INTO v_notification_id;
  
      INSERT INTO fines_notification(
          fine_id,
          notification_id
      )
      VALUES(
          NEW.fine_id,
          v_notification_id
      );
  
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  

CREATE TRIGGER trigger_notification_on_fine
AFTER INSERT ON fines
FOR EACH ROW
EXECUTE FUNCTION trigger_notification_on_fine_function();

• trigger_activate_reservation: Активира резервација и испраќа известување кога книгата станува достапна.

  CREATE OR REPLACE FUNCTION trigger_activate_reservation_function()
  RETURNS TRIGGER AS $$
  DECLARE
      v_barcode VARCHAR(13);
      v_reservation_id INT;
      v_member_id INT;
  BEGIN
      SELECT barcode
      INTO v_barcode
      FROM book_copy
      WHERE copy_id = NEW.copy_id;
  
      SELECT reservation_id, member_user_id
      INTO v_reservation_id, v_member_id
      FROM reservation
      WHERE barcode = v_barcode
        AND status = 'pending'
      ORDER BY reservation_timestamp
      LIMIT 1;
  
      IF v_reservation_id IS NOT NULL THEN
  
          UPDATE reservation
          SET status = 'active',
              notified_at = CURRENT_TIMESTAMP
          WHERE reservation_id = v_reservation_id;
  
          INSERT INTO notification(
              member_user_id,
              notification_type,
              title,
              message,
              status
          )
          VALUES(
              v_member_id,
              'reservation',
              'Reserved Book Available',
              'The book you reserved is now available.',
              'pending'
          );
  
      END IF;
  
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  

CREATE TRIGGER trigger_activate_reservation
AFTER UPDATE OF status ON book_copy
FOR EACH ROW
WHEN (NEW.status = 'available')
EXECUTE FUNCTION trigger_activate_reservation_function();