trg_HandlePenalties.sql

CREATE TRIGGER trg_HandlePenalties
ON Reservations
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Reserv_ID INT, @Email VARCHAR(128), @Started_At DATETIME, @ReservPeriod INT, @Status INT;
    DECLARE @CurrentPriority INT, @MaxPriority INT, @NewPriority INT;

    SELECT 
        @Reserv_ID = ins.Reserv_ID,
        @Email = ins.Email,
        @Started_At = ins.Started_At,
        @ReservPeriod = ins.ReservPeriod,
        @Status = ins.Status
    FROM Inserted ins;

    -- Check if the status has been changed to "Forgotten".
    IF EXISTS (SELECT 1 FROM States WHERE State_ID = @Status AND Name = 'Forgotten')
    BEGIN
        -- Adding a penalty for Forgotten status
        INSERT INTO Penalties (Reason, Timestamp, Email)
        VALUES ('Reservation Forgotten', GETDATE(), @Email);
    END;

    -- Check if the user did not pick up the resource during the reservation period
    IF EXISTS (
        SELECT 1
        FROM Reservations r
        WHERE r.Reserv_ID = @Reserv_ID
          AND DATEADD(HOUR, r.ReservPeriod, r.Started_At) < GETDATE() -- The reservation period has expired
          AND r.Status NOT IN (SELECT State_ID FROM States WHERE Name IN ('Satisfied', 'Cancelled'))
    )
    BEGIN
        -- Add a penalty for late receipt of a resource
        INSERT INTO Penalties (Reason, Timestamp, Email)
        VALUES ('Resource not picked up in time', GETDATE(), @Email);
    END;

    -- Check if the user did not return the resource on time
    IF EXISTS (
        SELECT 1
        FROM Requisitions req
        WHERE req.Reserv_ID = @Reserv_ID
          AND req.Status NOT IN (SELECT State_ID FROM States WHERE Name = 'Closed')
          AND DATEADD(HOUR, @ReservPeriod, @Started_At) < GETDATE()
    )
    BEGIN
        -- Add a penalty for late return of a resource
        INSERT INTO Penalties (Reason, Timestamp, Email)
        VALUES ('Resource not returned on time', GETDATE(), @Email);
    END;

    -- Downgrade the user's priority if he/she received a penalty
    IF EXISTS (
        SELECT 1
        FROM Penalties p
        WHERE p.Email = @Email
          AND p.Timestamp = GETDATE()
    )
    BEGIN
        -- Get the minimum priority (lowest possible level)
        SELECT @MaxPriority = MIN(Value)
        FROM Priorities;

        -- Get the current priority of the user
        SELECT @CurrentPriority = u.CurrentPriority
        FROM Users u
        WHERE u.Email = @Email;

        -- Lower the priority by 1 level if it is higher than the minimum priority
        IF @CurrentPriority > @MaxPriority
        BEGIN
            SET @NewPriority = @CurrentPriority - 1;

            UPDATE Users
            SET CurrentPriority = @NewPriority
            WHERE Email = @Email;
        END;
    END;
END;