RelationalDesign: data_load.sql

SET search_path TO project;

TRUNCATE TABLE
    approvals,
    reservation_equipment,
    reservations,
    room_equipment,
    equipment,
    rooms,
    users,
    buildings
RESTART IDENTITY CASCADE;

INSERT INTO buildings (building_id, name, address) VALUES
(1, 'Main Faculty Building', 'Rugjer Boshkovikj 16, Skopje'),
(2, 'Laboratory Building', 'Rugjer Boshkovikj 16A, Skopje'),
(3, 'Administration Building', 'Rugjer Boshkovikj 18, Skopje');

INSERT INTO rooms (room_id, building_id, room_code, capacity, type) VALUES
(1, 1, 'B1-101', 60, 'classroom'),
(2, 1, 'B1-204', 35, 'meeting_room'),
(3, 2, 'LAB-1', 25, 'lab'),
(4, 2, 'LAB-2', 20, 'lab'),
(5, 3, 'ADM-12', 10, 'office');

INSERT INTO equipment (equipment_id, name, stock_quantity) VALUES
(1, 'Projector', 3),
(2, 'Whiteboard', 0),
(3, 'Microphone', 5),
(4, 'Computer Workstation', 0),
(5, 'Video Conference Camera', 2),
(6, 'HDMI Cable', 12),
(7, 'Extension Cable', 8);

INSERT INTO room_equipment (room_id, equipment_id, quantity) VALUES
(1, 1, 1),
(1, 2, 1),
(2, 2, 1),
(2, 3, 2),
(2, 5, 1),
(3, 1, 1),
(3, 2, 1),
(3, 4, 15),
(4, 2, 1),
(4, 4, 12),
(5, 2, 1);

INSERT INTO users (user_id, username, email, full_name, role) VALUES
(1, 'nikola', 'nikola.sarafimov@example.com', 'Nikola Sarafimov', 'regular'),
(2, 'admin', 'admin.room@example.com', 'System Administrator', 'admin'),
(3, 'approver1', 'approver1.room@example.com', 'Reservation Approver One', 'approver'),
(4, 'professor1', 'professor1@example.com', 'Professor User', 'regular'),
(5, 'assistant1', 'assistant1@example.com', 'Teaching Assistant User', 'regular');

INSERT INTO reservations (
    reservation_id,
    room_id,
    user_id,
    reservation_date,
    start_time,
    end_time,
    status
) VALUES
(1, 1, 1, DATE '2026-02-10', TIME '10:00', TIME '12:00', 'pending'),
(2, 2, 4, DATE '2026-02-10', TIME '13:00', TIME '14:30', 'approved'),
(3, 3, 5, DATE '2026-02-11', TIME '09:00', TIME '11:00', 'approved'),
(4, 4, 1, DATE '2026-02-12', TIME '12:00', TIME '13:00', 'cancelled'),
(5, NULL, 4, DATE '2026-02-13', TIME '15:00', TIME '16:00', 'rejected'),
(6, 1, 5, DATE '2026-02-14', TIME '08:00', TIME '10:00', 'approved'),
(7, NULL, 1, DATE '2026-02-14', TIME '11:00', TIME '12:00', 'pending');

INSERT INTO reservation_equipment (
    reservation_id,
    equipment_id,
    requested_quantity
) VALUES
(1, 6, 2),
(1, 7, 1),
(2, 5, 1),
(3, 3, 1),
(5, 1, 1),
(5, 6, 2),
(6, 3, 2),
(7, 6, 3),
(7, 7, 2);

INSERT INTO approvals (
    approval_id,
    reservation_id,
    approver_id,
    decision,
    decision_time,
    note
) VALUES
(1, 2, 3, 'approved', TIMESTAMP '2026-02-09 15:30:00', 'Approved for department meeting.'),
(2, 3, 3, 'approved', TIMESTAMP '2026-02-10 12:15:00', 'Approved for laboratory exercise.'),
(3, 5, 2, 'rejected', TIMESTAMP '2026-02-12 10:00:00', 'Rejected because the requested portable projector is not available.'),
(4, 6, 2, 'approved', TIMESTAMP '2026-02-13 09:45:00', 'Approved for morning lecture with additional microphones.');