# Room Reservation System

A full-stack database-oriented Room Reservation System developed for the Databases course project.

The application allows users to search available rooms, create reservation requests, add optional equipment, approve or reject pending reservations, and view analytical reports generated from live PostgreSQL data.

---

## Project Overview

The system manages shared rooms, equipment, users, reservations, and approval decisions.

A reservation can include:

- a room only,
- equipment only,
- both a room and equipment.

The main goal of the project is to demonstrate correct relational database design, SQL implementation, database constraints, transactions, advanced SQL reports, and a working application connected to the faculty PostgreSQL database.

---

## Technologies Used

### Backend

- Java 21
- Spring Boot
- Spring JDBC / JdbcTemplate
- HikariCP connection pooling
- PostgreSQL
- BCrypt password hashing
- Maven
- Docker

### Frontend

- React
- Vite
- JavaScript
- CSS
- Lucide React icons
- Framer Motion
- React Hot Toast
- Docker / Nginx

### Database

- PostgreSQL
- Faculty database accessed through SSH tunnel
- Schema: `project`

---

## Main Features

### User Access

- User registration
- User sign in with username or email
- Password hashing using BCrypt
- Role-based navigation:
    - `regular`
    - `approver`
    - `admin`

### Dashboard

- Live system overview
- Room count
- Equipment count
- User count
- Pending reservation count
- Approved reservation count
- Current database connection status

### Search Rooms

- Search available rooms by:
    - reservation date
    - start time
    - end time
    - minimum capacity
    - room type
    - required equipment

### Create Reservation

- Create a pending reservation request
- Select an available room
- Create equipment-only reservations
- Add optional equipment with quantity
- Validate empty reservations
- Store reservation data in the PostgreSQL database

### Approvals

- View pending reservations
- Approve or reject requests
- Uses PostgreSQL stored function logic
- Updates reservation status and approval records

### Reports

- Advanced SQL reports based on live project data
- Room utilization report
- Equipment demand and stock risk report
- Grouping, aggregation, ranking, and analytical SQL logic

### Admin Panel

- Read-only overview for administrator users
- Users overview
- Equipment overview
- Pending reservations overview

---

## Database Connection

The application connects to the faculty PostgreSQL database through an SSH tunnel.

Before starting the backend, the tunnel must be running and forwarding the database to local port `9999`.

Expected local tunnel port:

```text
localhost:9999
```

Backend local database URL:

```text
jdbc:postgresql://localhost:9999/db_202526z_va_prj_room_reservation
```

Docker database URL:

```text
jdbc:postgresql://host.docker.internal:9999/db_202526z_va_prj_room_reservation
```

---

## Environment Variables

Create a local `.env` file in the root folder.

```env
SPRING_DATASOURCE_URL=jdbc:postgresql://host.docker.internal:9999/db_202526z_va_prj_room_reservation
SPRING_DATASOURCE_USERNAME=db_202526z_va_prj_room_reservation_owner
SPRING_DATASOURCE_PASSWORD=YOUR_DATABASE_PASSWORD_HERE
```

---

## Required Database Objects

The application expects the following database objects to exist in schema `project`:

### Main Tables

```text
project.buildings
project.rooms
project.equipment
project.room_equipment
project.users
project.reservations
project.reservation_equipment
project.approvals
project.user_credentials
```

### Views

```text
project.v_pending_reservation_details
project.v_quarterly_room_utilization
```

### Stored Function

```text
project.fn_approve_or_reject_reservation
```

### Domain

```text
project.approval_decision_domain
```

---

## Authentication Table

The UI authentication flow uses an additional table for password hashes.

The SQL script is located in:

```text
backend/src/main/resources/authentication-table.sql
```

SQL:

```sql
CREATE TABLE IF NOT EXISTS project.user_credentials (
    user_id INTEGER NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_user_credentials PRIMARY KEY (user_id),

    CONSTRAINT fk_user_credentials_users FOREIGN KEY (user_id)
        REFERENCES project.users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
```

This table stores hashed passwords only. Plain text passwords are not stored.

---

## Run the Application Locally

### 1. Start the SSH Tunnel

Start the faculty tunnel script and keep the tunnel window open.

The tunnel should forward the database to:

```text
localhost:9999
```

---

### 2. Start Backend Locally

From the root folder:

```bash
cd backend
mvn spring-boot:run
```

Backend URL:

```text
http://localhost:8080
```

Health endpoint:

```text
http://localhost:8080/api/health
```

Expected response example:

```json
{
  "status": "UP",
  "database": "PostgreSQL",
  "schema": "project",
  "roomCount": 5
}
```

---

### 3. Start Frontend Locally

Open a new terminal:

```bash
cd frontend
npm install
npm run dev
```

Frontend development URL:

```text
http://localhost:5173
```

---

## Run with Docker Compose

Make sure the SSH tunnel is running first.

Then run:

```bash
docker compose up -d --build
```

Frontend Docker URL:

```text
http://localhost:3000
```

Backend Docker URL:

```text
http://localhost:8080
```

Health endpoint:

```text
http://localhost:8080/api/health
```

Stop containers:

```bash
docker compose down
```

---

## Backend API Endpoints

### Health and Dashboard

```text
GET /api/health
GET /api/dashboard
```

### Authentication

```text
POST /api/auth/register
POST /api/auth/login
POST /api/auth/activate
```

### Users

```text
GET /api/users
GET /api/approvers
```

### Rooms

```text
GET /api/rooms
POST /api/rooms/search
```

### Equipment

```text
GET /api/equipment
```

### Reservations

```text
POST /api/reservations
GET /api/reservations/{id}
GET /api/reservations/pending
```

### Approvals

```text
POST /api/approvals
```

### Reports

```text
GET /api/reports/room-utilization
GET /api/reports/equipment-demand
```

---

## Example API Requests

### Register User

```json
POST /api/auth/register

{
  "fullName": "Nikola Test",
  "username": "nikolatest",
  "email": "nikola.test@example.com",
  "password": "Password123!"
}
```

### Login User

```json
POST /api/auth/login

{
  "identifier": "nikolatest",
  "password": "Password123!"
}
```

### Search Available Rooms

```json
POST /api/rooms/search

{
  "reservationDate": "2026-07-20",
  "startTime": "09:00",
  "endTime": "10:00",
  "minimumCapacity": 1,
  "roomType": "",
  "requiredEquipmentName": ""
}
```

### Create Reservation

```json
POST /api/reservations

{
  "userId": 1,
  "reservationDate": "2026-07-20",
  "startTime": "09:00",
  "endTime": "10:00",
  "roomId": 1,
  "equipment": [
    {
      "equipmentId": 1,
      "requestedQuantity": 1
    }
  ]
}
```

### Approve Reservation

```json
POST /api/approvals

{
  "reservationId": 1,
  "approverId": 2,
  "decision": "approved",
  "note": "Approved through React UI."
}
```

---

## Testing Through DBeaver

After using the UI, the changes can be verified directly in DBeaver.

### Check Registered Users

```sql
SELECT *
FROM project.users
ORDER BY user_id DESC;
```

### Check User Credentials

```sql
SELECT *
FROM project.user_credentials
ORDER BY user_id DESC;
```

### Check Created Reservations

```sql
SELECT *
FROM project.reservations
ORDER BY reservation_id DESC;
```

### Check Reservation Equipment

```sql
SELECT *
FROM project.reservation_equipment
ORDER BY reservation_id DESC;
```

### Check Approvals

```sql
SELECT *
FROM project.approvals
ORDER BY approval_id DESC;
```

### Check Pending Reservations View

```sql
SELECT *
FROM project.v_pending_reservation_details;
```

### Check Room Utilization View

```sql
SELECT *
FROM project.v_quarterly_room_utilization;
```

---

## Role-Based Testing

The application supports role-based navigation.

To test different roles, update the role of a test user in DBeaver.

### Set User as Regular

```sql
UPDATE project.users
SET role = 'regular'
WHERE username = 'nikolatest';
```

### Set User as Approver

```sql
UPDATE project.users
SET role = 'approver'
WHERE username = 'nikolatest';
```

### Set User as Admin

```sql
UPDATE project.users
SET role = 'admin'
WHERE username = 'nikolatest';
```

After changing the role, log out and log in again.

---

## Build Checks

### Backend Build

```bash
cd backend
mvn clean package -DskipTests
```

### Frontend Build

```bash
cd frontend
npm install
npm run build
```

### Docker Build

```bash
docker compose down
docker compose up -d --build
```

---

## Author

- Nikola Sarafimov