--
-- Table with rooms allowed for booking
-- 
CREATE TABLE mod_roomreservation_roomswhitelist (
		rrr_name			 TEXT			 UNIQUE REFERENCES rooms(name)
                             ON DELETE CASCADE
                             ON UPDATE CASCADE
);

--
-- Table with bookings
--
CREATE TABLE mod_roomreservation_bookings (
    rrb_uid        SERIAL    NOT NULL PRIMARY KEY, -- Unique ID 
    rrb_room       TEXT      NOT NULL              -- Name of the room
                             REFERENCES rooms(name)
                             ON DELETE CASCADE
                             ON UPDATE CASCADE,
    rrb_date       DATE      NOT NULL,             -- Date of the booking
    rrb_tsfirst    SMALLINT  NOT NULL,             -- Number of the first timeslice
    rrb_tslast     SMALLINT  NOT NULL              -- Number of the last timeslice
                             CHECK(rrb_tsfirst <= rrb_tslast),
    rrb_act        TEXT      NOT NULL              -- Owner of the booking
                             REFERENCES users(Act)
                             ON DELETE CASCADE
                             ON UPDATE CASCADE,
    rrb_reason     TEXT      NOT NULL,             -- Reason
    rrb_interval   SMALLINT  NOT NULL              -- Interval in weeks for recurring bookings
                             DEFAULT 0
);

--
-- Permissions
--
GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webusr;
GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webadm;
GRANT SELECT ON mod_roomreservation_roomswhitelist TO webusr;
GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist TO webadm;
