Functions of common actions

Renting a book
Checks if user is eligible to rent a copy

CREATE OR REPLACE FUNCTION fn_rent_copy(
    p_user_id BIGINT,
    p_copy_id BIGINT,
    p_employee_id BIGINT
)
RETURNS BIGINT LANGUAGE plpgsql AS $$
DECLARE
    v_membership_id BIGINT;
    v_max_days INT;
    v_due TIMESTAMP;
    v_rental_id BIGINT;
BEGIN
    SELECT m.id, mp.max_rent_period_days
    INTO v_membership_id, v_max_days
    FROM Membership m
    JOIN MembershipPlan mp ON mp.id = m.membership_plan_id
    WHERE m.user_id = p_user_id
      AND m.cancelled_at IS NULL
      AND m.expires_at > NOW()
    LIMIT 1;

    IF v_membership_id IS NULL THEN
        RAISE EXCEPTION 'No active membership';
    END IF;

    IF NOT fn_can_rent(p_user_id) THEN
        RAISE EXCEPTION 'Max concurrent rentals reached';
    END IF;


    IF EXISTS (
        SELECT 1 FROM Rental
        WHERE resource_copy_id = p_copy_id
          AND returned_at IS NULL
    ) THEN
        RAISE EXCEPTION 'Copy already rented';
    END IF;

    v_due := NOW() + (v_max_days || ' days')::INTERVAL;

    INSERT INTO Rental(resource_copy_id, rented_at, due_at, membership_id, employee_id)
    VALUES (p_copy_id, NOW(), v_due, v_membership_id, p_employee_id)
    RETURNING id INTO v_rental_id;

    RETURN v_rental_id;
END;
$$;

Helper function to check if a user can rent

CREATE OR REPLACE FUNCTION fn_can_rent(p_user_id BIGINT)
RETURNS BOOLEAN LANGUAGE plpgsql AS $$
DECLARE
    v_max INT;
    v_current INT;
BEGIN
    SELECT mp.max_concurrent_rentals
    INTO v_max
    FROM Membership m
    JOIN MembershipPlan mp ON mp.id = m.membership_plan_id
    WHERE m.user_id = p_user_id
      AND m.cancelled_at IS NULL
      AND m.expires_at > NOW()
    LIMIT 1;

    IF v_max IS NULL THEN
        RETURN FALSE;
    END IF;

    SELECT COUNT(*)
    INTO v_current
    FROM Rental r
    JOIN Membership m ON m.id = r.membership_id
    WHERE m.user_id = p_user_id
      AND r.returned_at IS NULL;

    RETURN v_current < v_max;
END;
$$;

Returning copies Calculates penalty upon book return if applicable

CREATE OR REPLACE FUNCTION fn_return_copy(p_rental_id BIGINT)
RETURNS NUMERIC LANGUAGE plpgsql AS $$
DECLARE
    v_due TIMESTAMP;
    v_penalty NUMERIC := 0;
    v_days_late INT;
BEGIN
    SELECT due_at INTO v_due
    FROM Rental
    WHERE id = p_rental_id;

    IF v_due IS NULL THEN
        RAISE EXCEPTION 'Rental not found';
    END IF;

    IF NOW() > v_due THEN
        v_days_late := EXTRACT(DAY FROM NOW() - v_due);
        v_penalty := v_days_late * 10; -- configurable
    END IF;

    UPDATE Rental
    SET returned_at = NOW(),
        penalty = v_penalty
    WHERE id = p_rental_id;

    RETURN v_penalty;
END;
$$;

Registering to an event

CREATE OR REPLACE FUNCTION fn_register_to_event(
    p_user_id BIGINT,
    p_event_id BIGINT
)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_capacity INT;
    v_count INT;
BEGIN
    SELECT capacity INTO v_capacity
    FROM Event
    WHERE id = p_event_id;

    SELECT COUNT(*) INTO v_count
    FROM EventRegistration
    WHERE event_id = p_event_id;

    IF v_count >= v_capacity THEN
        RAISE EXCEPTION 'Event is full';
    END IF;

    INSERT INTO EventRegistration(user_id, event_id, registered_at)
    VALUES (p_user_id, p_event_id, NOW());
END;
$$;

Adding an edition to the wishlist

CREATE OR REPLACE FUNCTION fn_add_to_wishlist(
    p_user_id BIGINT,
    p_isbn VARCHAR
)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_wishlist_id BIGINT;
BEGIN
    SELECT id INTO v_wishlist_id
    FROM Wishlist
    WHERE user_id = p_user_id;

    IF v_wishlist_id IS NULL THEN
        INSERT INTO Wishlist(user_id)
        VALUES (p_user_id)
        RETURNING id INTO v_wishlist_id;
    END IF;

    INSERT INTO Wishlist_entry(edition_isbn, wishlist_id)
    VALUES (p_isbn, v_wishlist_id)
    ON CONFLICT DO NOTHING;
END;
$$;

Active membership check

CREATE OR REPLACE FUNCTION fn_has_active_membership(
    p_user_id BIGINT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM Membership
        WHERE user_id = p_user_id
          AND cancelled_at IS NULL
          AND expires_at > NOW()
    );
END;
$$;

Create membership

CREATE OR REPLACE FUNCTION fn_create_membership(
    p_user_id BIGINT,
    p_membership_plan_id BIGINT
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_duration_days INT;
    v_membership_id BIGINT;
    v_started_at TIMESTAMP := NOW();
    v_expires_at TIMESTAMP;
BEGIN
    IF fn_has_active_membership(p_user_id) THEN
        RAISE EXCEPTION 'User already has an active membership';
    END IF;

    SELECT duration_days
    INTO v_duration_days
    FROM MembershipPlan
    WHERE id = p_membership_plan_id;

    IF v_duration_days IS NULL THEN
        RAISE EXCEPTION 'Membership plan not found';
    END IF;

    v_expires_at := v_started_at +
                     (v_duration_days || ' days')::INTERVAL;

    INSERT INTO Membership(
        user_id,
        membership_plan_id,
        started_at,
        expires_at,
        created_at
    )
    VALUES (
        p_user_id,
        p_membership_plan_id,
        v_started_at,
        v_expires_at,
        NOW()
    )
    RETURNING id INTO v_membership_id;

    RETURN v_membership_id;
END;
$$;

Renew membership

CREATE OR REPLACE FUNCTION fn_renew_membership(
    p_user_id BIGINT,
    p_new_plan_id BIGINT
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_membership_id BIGINT;
    v_duration_days INT;
    v_start_date TIMESTAMP;
    v_expires_at TIMESTAMP;
    v_new_membership_id BIGINT;
BEGIN
    SELECT id, expires_at
    INTO v_current_membership_id, v_start_date
    FROM Membership
    WHERE user_id = p_user_id
      AND cancelled_at IS NULL
    ORDER BY expires_at DESC
    LIMIT 1;

    SELECT duration_days
    INTO v_duration_days
    FROM MembershipPlan
    WHERE id = p_new_plan_id;

    IF v_duration_days IS NULL THEN
        RAISE EXCEPTION 'Membership plan not found';
    END IF;

    IF v_start_date IS NULL OR v_start_date < NOW() THEN
        v_start_date := NOW();
    END IF;

    v_expires_at := v_start_date +
                    (v_duration_days || ' days')::INTERVAL;

    IF v_current_membership_id IS NOT NULL THEN
        UPDATE Membership
        SET cancelled_at = NOW()
        WHERE id = v_current_membership_id;
    END IF;

    INSERT INTO Membership(
        user_id,
        membership_plan_id,
        started_at,
        expires_at,
        created_at
    )
    VALUES (
        p_user_id,
        p_new_plan_id,
        NOW(),
        v_expires_at,
        NOW()
    )
    RETURNING id INTO v_new_membership_id;

    RETURN v_new_membership_id;
END;
$$;

Cancel membership

CREATE OR REPLACE FUNCTION fn_cancel_membership(
    p_membership_id BIGINT
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM Membership
        WHERE id = p_membership_id
    ) THEN
        RAISE EXCEPTION 'Membership not found';
    END IF;

    UPDATE Membership
    SET cancelled_at = NOW()
    WHERE id = p_membership_id
      AND cancelled_at IS NULL;
END;
$$;

Get average book rating

CREATE OR REPLACE FUNCTION fn_avg_rating(p_isbn VARCHAR)
RETURNS NUMERIC LANGUAGE plpgsql AS $$
BEGIN
    RETURN (
        SELECT ROUND(AVG(rating), 2)
        FROM Review
        WHERE resource_edition_isbn = p_isbn
    );
END;
$$;

Leave review

CREATE OR REPLACE FUNCTION fn_leave_review(
    p_user_id BIGINT,
    p_edition_isbn VARCHAR(20),
    p_rating INT,
    p_review_text TEXT
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_review_id BIGINT;
BEGIN
    IF p_rating NOT BETWEEN 1 AND 5 THEN
        RAISE EXCEPTION 'Rating must be between 1 and 5';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM Rental r
        JOIN Membership m
            ON m.id = r.membership_id
        JOIN ResourceCopy rc
            ON rc.id = r.resource_copy_id
        WHERE m.user_id = p_user_id
          AND rc.edition_isbn = p_edition_isbn
          AND r.returned_at IS NOT NULL
    ) THEN
        RAISE EXCEPTION
            'User has not completed a rental of this edition';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM Review
        WHERE user_id = p_user_id
          AND resource_edition_isbn = p_edition_isbn
    ) THEN
        RAISE EXCEPTION
            'User has already reviewed this edition';
    END IF;

    INSERT INTO Review (
        user_id,
        resource_edition_isbn,
        rating,
        review_text,
        created_at,
        updated_at
    )
    VALUES (
        p_user_id,
        p_edition_isbn,
        p_rating,
        p_review_text,
        NOW(),
        NOW()
    )
    RETURNING id INTO v_review_id;

    RETURN v_review_id;
END;
$$;

Triggers

Automatic penalty calculation on rental update

CREATE OR REPLACE FUNCTION trg_rental_return()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_days_late INT;
BEGIN
    IF OLD.returned_at IS NULL AND NEW.returned_at IS NOT NULL THEN
        IF NEW.due_at IS NOT NULL AND NEW.returned_at > NEW.due_at THEN
            v_days_late := EXTRACT(DAY FROM NEW.returned_at - NEW.due_at);
            NEW.penalty := COALESCE(NEW.penalty, 0) + (v_days_late * 10);
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_rental_return_update
BEFORE UPDATE OF returned_at ON Rental
FOR EACH ROW
EXECUTE FUNCTION trg_rental_return();

User soft deletion

CREATE OR REPLACE FUNCTION trg_soft_delete_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.deleted THEN
        RETURN NULL;
    END IF;

    UPDATE "User"
    SET
        first_name = '[deleted_user]',
        last_name  = '[deleted_user]',
        email      = '[deleted_user@example.com]',
        password   = '[deleted]',
        deleted    = TRUE,
        deleted_at = NOW(),
        updated_at = NOW()
    WHERE id = OLD.id;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_soft_delete_user
BEFORE DELETE ON "User"
FOR EACH ROW
EXECUTE FUNCTION trg_soft_delete_user();