Lost & Found
Portal

-- Trigger 1: Mark items Matched/Claimed when a claim is submitted
CREATE TRIGGER trg_claim_pending
AFTER INSERT ON CLAIM
BEGIN
    UPDATE LOST_ITEM  SET Status = 'Matched'
        WHERE Lost_ID  = NEW.Lost_ID;
    UPDATE FOUND_ITEM SET Status = 'Claimed'
        WHERE Found_ID = NEW.Found_ID;
END;

-- Trigger 2: Mark both items Returned when claim is Approved
CREATE TRIGGER trg_claim_approved
AFTER UPDATE OF Status ON CLAIM
WHEN NEW.Status = 'Approved'
BEGIN
    UPDATE LOST_ITEM  SET Status = 'Returned'
        WHERE Lost_ID  = NEW.Lost_ID;
    UPDATE FOUND_ITEM SET Status = 'Returned'
        WHERE Found_ID = NEW.Found_ID;
END;

-- Find potential matches for a lost item (same category)
SELECT
    f.Found_ID,
    f.Item_Name,
    f.Description,
    f.Date_Found,
    f.Location,
    c.Category_Name,
    u.Name AS Found_By
FROM FOUND_ITEM f
LEFT JOIN CATEGORY c ON f.Category_ID = c.Category_ID
JOIN      USER u      ON f.User_ID     = u.User_ID
WHERE
    f.Category_ID = (
        SELECT Category_ID FROM LOST_ITEM
        WHERE Lost_ID = :lost_id
    )
    AND f.Status = 'Unclaimed'
ORDER BY f.Date_Found DESC;

-- Stored procedure: Add a new lost item (PL/SQL style)
CREATE PROCEDURE Add_Lost_Item (
    p_item_name   TEXT,
    p_description TEXT,
    p_date_lost   TEXT,
    p_location    TEXT,
    p_user_id     INTEGER,
    p_category_id INTEGER
)
BEGIN
    -- Validate user exists
    IF NOT EXISTS (
        SELECT 1 FROM USER WHERE User_ID = p_user_id
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Invalid user ID';
    END IF;

    INSERT INTO LOST_ITEM
        (Item_Name, Description, Date_Lost, Location, User_ID, Category_ID)
    VALUES
        (p_item_name, p_description, p_date_lost, p_location,
         p_user_id, p_category_id);
END;