# computrace-reporting

React + Node.js dashboard for CompuTrace employee monitoring and reporting.

---

## Tech Stack

- **Client:** React 19, TypeScript, Vite, Tailwind CSS
- **Server:** Node.js, Express, TypeScript
- **Database:** MySQL (existing CompuTrace DB — no new data tables; VIEWs and indexes only)
- **Auth:** Azure AD (OAuth2) + JWT
- **Cache:** Redis (ioredis) with in-memory fallback

---

## Dependencies

### Server — Runtime

| Package | Version | Purpose |
|---|---|---|
| `express` | ^4.21.2 | HTTP server framework |
| `mysql2` | ^3.12.0 | MySQL database driver |
| `ioredis` | ^5.10.1 | Redis client (response caching) |
| `jsonwebtoken` | ^9.0.2 | JWT auth tokens |
| `@azure/msal-node` | ^2.16.0 | Azure AD OAuth2 login |
| `dotenv` | ^16.4.7 | Environment variable loading |
| `cors` | ^2.8.5 | CORS middleware |
| `helmet` | ^8.0.0 | HTTP security headers |
| `express-rate-limit` | ^7.5.0 | Rate limiting |
| `json2csv` | ^6.0.0-alpha.2 | CSV export |
| `pdfkit` | ^0.15.1 | PDF generation |

### Server — Dev

| Package | Version | Purpose |
|---|---|---|
| `typescript` | ^5.7.3 | TypeScript compiler |
| `ts-node-dev` | ^2.0.0 | Dev server with hot reload |
| `jest` | ^30.3.0 | Test runner |
| `ts-jest` | ^29.4.6 | TypeScript support for Jest |
| `@types/node` | ^22.12.0 | Node type definitions |
| `@types/express` | ^5.0.0 | Express type definitions |
| `@types/jsonwebtoken` | ^9.0.7 | JWT type definitions |
| `@types/cors` | ^2.8.17 | CORS type definitions |
| `@types/pdfkit` | ^0.17.5 | PDFKit type definitions |

### Client — Runtime

| Package | Version | Purpose |
|---|---|---|
| `react` + `react-dom` | ^19.2.4 | UI framework |
| `react-router-dom` | ^7.13.1 | Client-side routing |
| `axios` | ^1.13.6 | HTTP client |
| `recharts` | ^3.8.0 | Charts and graphs |
| `@tanstack/react-table` | ^8.21.3 | Data tables |
| `tailwindcss` | ^4.2.2 | Utility CSS framework |
| `@tailwindcss/vite` | ^4.2.2 | Tailwind Vite plugin |

### Client — Dev

| Package | Version | Purpose |
|---|---|---|
| `vite` | ^8.0.1 | Build tool / dev server |
| `vitest` | ^4.1.0 | Unit testing |
| `typescript` | ~5.9.3 | TypeScript compiler |
| `@vitejs/plugin-react` | ^6.0.1 | React fast refresh |
| `@testing-library/react` | ^16.3.2 | Component testing |
| `@testing-library/jest-dom` | ^6.9.1 | DOM assertion matchers |
| `@testing-library/user-event` | ^14.6.1 | User interaction simulation |
| `eslint` + plugins | ^9.39.4 | Linting |
| `jsdom` | ^29.0.1 | DOM environment for Vitest |

---

## Redis

Redis is used as a **response cache** to avoid repeated heavy DB queries. If Redis is unavailable, the server automatically falls back to an in-memory `Map`-based cache — no crash, no hanging requests.

### Configuration

Set `REDIS_URL` in `server/.env` (defaults to `redis://127.0.0.1:6379`):

```env
REDIS_URL=redis://127.0.0.1:6379
```

### Behaviour

| Setting | Value |
|---|---|
| Connection | Lazy — first command triggers it |
| Connect timeout | 3 seconds |
| Offline queue | Disabled — fails fast to in-memory fallback |
| Retry | Capped backoff: 200ms → 3s, stops after 10 attempts |
| Invalidation | Uses `SCAN` (non-blocking), never `KEYS` |
| Fallback | In-memory `MemCache`, max 1000 entries, sweeps every 5 min |

### Cache TTL

| Data | TTL |
|---|---|
| Attendance/absence (includes today) | 2 minutes |
| Attendance/absence (fully historical) | 10 minutes |

### Which endpoints are cached

| Controller | Cached endpoints |
|---|---|
| `attendance.controller.ts` | Attendance dashboard, absence detection |
| `apps.controller.ts` | App usage summary |

### Production setup (Ubuntu/Debian)

```bash
sudo apt install redis-server
sudo systemctl enable redis
sudo systemctl start redis
redis-cli ping   # should return PONG
```

> If Redis is not available, the app still works — it falls back to the in-memory cache automatically.

---

## Local Development

### Prerequisites

- Node.js 18+
- MySQL access to `computraceqa` DB
- Redis running locally (`redis://127.0.0.1:6379`)

### Setup

```bash
# 1. Clone the repo
git clone <repo-url>
cd computrace-reporting

# 2. Configure server environment
cp server/.env.example server/.env
# Fill in DB credentials, Azure AD app details, JWT secret, and Redis URL

# 3. Install dependencies
cd server && npm install
cd ../client && npm install

# 4. Start both servers (from repo root)
cd server && npm run dev      # API on :3333
cd ../client && npm run dev   # UI on :5173
```

---

## Docker (Local)

A full Docker setup is included — two application containers (client + server) and a managed Redis container.

### Prerequisites

1. **Docker Desktop** — [download here](https://www.docker.com/products/docker-desktop/)
   - Enable the **WSL 2 backend** during install
   - After install, launch Docker Desktop and wait for the whale icon in the system tray to show **"Docker Desktop is running"**
   - Verify: `docker --version` and `docker compose version`

2. **MySQL running on your host machine**
   - The app connects to the existing `computraceqa` database on your local MySQL instance
   - Docker uses `host.docker.internal` to reach it — no extra config needed
   - Ensure MySQL is running on port `3306` before starting containers

3. **`server/.env` configured**
   - Copy and fill in credentials:
     ```bash
     cp server/.env.example server/.env
     ```
   - Required values:
     ```env
     DB_HOST=localhost        # Docker overrides this automatically
     DB_PORT=3306
     DB_NAME=computraceqa
     DB_USER=<your_db_user>
     DB_PASSWORD=<your_db_password>
     JWT_SECRET=<secure_random_string_min_32_chars>
     ```

### How Docker overrides `.env`

`docker-compose.yml` overrides three variables at runtime so you never need to edit `.env` for Docker:

| Variable | `.env` value | Docker override | Reason |
|---|---|---|---|
| `DB_HOST` | `localhost` | `host.docker.internal` | Containers can't use `localhost` to reach host MySQL |
| `REDIS_URL` | `redis://127.0.0.1:6379` | `redis://redis:6379` | Redis runs as a named container in the compose network |
| `CLIENT_URL` | `http://localhost:5173` | `http://localhost` | Frontend is served on port 80 in Docker, not Vite's 5173 |

### Start

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

Open `http://localhost` in your browser.

| Service | URL |
|---|---|
| Frontend | http://localhost |
| API | http://localhost:3333/api |

### Useful commands

```bash
# Run in background
docker compose up --build -d

# View logs
docker compose logs -f server
docker compose logs -f client

# Stop all services
docker compose down

# Stop and remove volumes (clears Redis cache)
docker compose down -v
```

---

## Running Tests

```bash
# Server (Jest — 15 tests across 3 suites)
cd server
npm test

# Client (Vitest — 16 tests, 1 suite)
cd client
npm test
```

---

## Production Deployment (Go-Live)

Follow these steps **in order** when deploying to production.

### Step 1 — Run Unit Tests

All tests must pass before proceeding.

```bash
cd server && npm test
cd ../client && npm test
```

### Step 2 — Ensure Redis is Running

```bash
sudo systemctl status redis
# If not running:
sudo systemctl start redis
redis-cli ping   # should return PONG
```

### Step 3 — Apply Database Changes

> All statements are idempotent (`IF NOT EXISTS` / `CREATE OR REPLACE`). Safe to re-run.

Connect to the production MySQL database:

```bash
mysql -h <DB_HOST> -u <DB_USER> -p <DB_NAME>
```

Paste and run the following SQL:

```sql
-- =============================================
-- INDEXES (8 total)
-- =============================================

-- 1. Per-employee date range scans (every attendance/absence/idle query)
CREATE INDEX IF NOT EXISTS idx_ewt_emp_date
  ON employee_working_time (emp_code, work_logged_on);

-- 2. Eliminates full scans in last_active_date LEFT JOIN subquery
CREATE INDEX IF NOT EXISTS idx_ewt_emp_active_date
  ON employee_working_time (emp_code, total_active, work_logged_on);

-- 3. Date-range BETWEEN scans + HAVING SUM(total_active) = 0
--    Also covers /idle/stats and /idle/export full-workforce aggregations
CREATE INDEX IF NOT EXISTS idx_ewt_date_active
  ON employee_working_time (work_logged_on, total_active);

-- 4. Every EmployeeDetails WHERE UserType = 'Member' AND Department = ? filter
CREATE INDEX IF NOT EXISTS idx_ed_usertype_dept
  ON EmployeeDetails (UserType, Department);

-- 5. App usage date range scan + GROUP BY application_name
CREATE INDEX IF NOT EXISTS idx_rad_started_emp_app
  ON running_application_details_archive (started_on, emp_code, application_name);

-- 6. Per-employee app usage drill-down
CREATE INDEX IF NOT EXISTS idx_rad_emp_started
  ON running_application_details_archive (emp_code, started_on);

-- 7. Sleep details — date + employee filter (/reports/idle/patterns)
CREATE INDEX IF NOT EXISTS idx_ssd_triggered_emp
  ON system_sleep_details (triggered_on, emp_code);

-- 8. Call details — date range scan + per-employee drill-down
CREATE INDEX IF NOT EXISTS idx_cd_started_emp
  ON call_details (call_started_on, emp_code);


-- =============================================
-- VIEWS (6 total)
-- =============================================

-- Active employees base list (for absence detection)
CREATE OR REPLACE VIEW v_active_employees AS
SELECT EmployeeID AS emp_code, DisplayName, Department, Manager, JobTitle, OfficeLocation, Email
FROM EmployeeDetails
WHERE UserType = 'Member';

-- First login / last logout per employee per day
CREATE OR REPLACE VIEW v_first_last_login AS
SELECT
  sld.emp_code,
  DATE(sld.login_time) AS report_date,
  MIN(sld.login_time) AS first_login,
  MAX(sld.logout_time) AS last_logout,
  COUNT(*) AS event_count
FROM system_login_details sld
GROUP BY sld.emp_code, DATE(sld.login_time);

-- App usage per employee per day
CREATE OR REPLACE VIEW v_app_usage_daily AS
SELECT
  rad.emp_code,
  ed.DisplayName AS employee_name,
  ed.Department,
  rad.application_name,
  DATE(rad.started_on) AS report_date,
  SUM(rad.application_count) AS total_sessions,
  SUM(rad.browsing_time) AS total_browsing_secs,
  ROUND(SUM(rad.browsing_time) / 3600, 2) AS browsing_hours
FROM running_application_details_archive rad
JOIN EmployeeDetails ed ON rad.emp_code = ed.EmployeeID
GROUP BY rad.emp_code, ed.DisplayName, ed.Department, rad.application_name, DATE(rad.started_on);

-- Call summary per employee per day
CREATE OR REPLACE VIEW v_call_summary AS
SELECT
  cd.emp_code,
  ed.DisplayName AS employee_name,
  ed.Department,
  DATE(cd.call_started_on) AS report_date,
  COUNT(*) AS total_calls,
  SUM(TIMESTAMPDIFF(SECOND, cd.call_started_on, cd.call_ended_on)) AS total_call_secs
FROM call_details cd
JOIN EmployeeDetails ed ON cd.emp_code = ed.EmployeeID
GROUP BY cd.emp_code, ed.DisplayName, ed.Department, DATE(cd.call_started_on);

-- Sleep events by hour of day (idle patterns)
CREATE OR REPLACE VIEW v_idle_hourly_pattern AS
SELECT
  ssd.emp_code,
  ed.Department,
  DATE(ssd.triggered_on) AS report_date,
  HOUR(ssd.triggered_on) AS hour_of_day,
  COUNT(*) AS sleep_event_count
FROM system_sleep_details ssd
JOIN EmployeeDetails ed ON ssd.emp_code = ed.EmployeeID
GROUP BY ssd.emp_code, ed.Department, DATE(ssd.triggered_on), HOUR(ssd.triggered_on);

-- Daily attendance summary per employee
-- idle_percentage = idle / (active + idle) — fraction of awake time, not total time
CREATE OR REPLACE VIEW v_daily_attendance AS
SELECT
  ewt.emp_code,
  ed.DisplayName AS employee_name,
  ed.Department,
  ed.Manager AS manager_email,
  ed.JobTitle,
  ed.OfficeLocation,
  DATE(ewt.work_logged_on) AS report_date,
  SUM(ewt.total_active) AS total_active_secs,
  SUM(ewt.total_idle) AS total_idle_secs,
  SUM(ewt.total_sleep) AS total_sleep_secs,
  ROUND(SUM(ewt.total_active) / 3600, 2) AS active_hours,
  ROUND(SUM(ewt.total_idle) / 3600, 2) AS idle_hours,
  ROUND(SUM(ewt.total_sleep) / 3600, 2) AS sleep_hours,
  ROUND((SUM(ewt.total_active) + SUM(ewt.total_idle)) / 3600, 2) AS awake_hours,
  ROUND(
    SUM(ewt.total_idle) * 100.0
    / NULLIF(SUM(ewt.total_active) + SUM(ewt.total_idle), 0),
    1
  ) AS idle_percentage,
  ROUND(
    SUM(ewt.total_sleep) * 100.0
    / NULLIF(SUM(ewt.total_active) + SUM(ewt.total_idle) + SUM(ewt.total_sleep), 0),
    1
  ) AS sleep_percentage,
  CASE
    WHEN SUM(ewt.total_active) = 0 AND SUM(ewt.total_idle) = 0 THEN 'zero_activity'
    WHEN SUM(ewt.total_active) = 0 THEN 'zero_active'
    ELSE 'present'
  END AS attendance_status
FROM employee_working_time ewt
JOIN EmployeeDetails ed ON ewt.emp_code = ed.EmployeeID
GROUP BY ewt.emp_code, DATE(ewt.work_logged_on), ed.DisplayName, ed.Department,
         ed.Manager, ed.JobTitle, ed.OfficeLocation;
```

> **Note:** Indexes on `running_application_details_archive` (indexes 5 & 6) may take a minute or two to build on large tables. InnoDB will not block reads while they build.

### Step 4 — Deploy Server

```bash
git pull origin main
cd server
npm install
pm2 restart computrace-server    # adjust pm2 app name if different
```

### Step 5 — Build and Deploy Client

```bash
cd client
npm install
npm run build
# Copy dist/ to your web root, or let CI/CD handle it
```

### Step 6 — Smoke Test

After deploy, verify:

- [ ] Absence page loads — KPI cards show data, filters work, CSV export works
- [ ] Idle Analysis — 3 tabs render, stats and patterns load
- [ ] Calls page — redesigned screen renders, date filter works
- [ ] Employees list and Employee Detail pages load
- [ ] Attendance Dashboard — preset filters and dept dropdown work
- [ ] Leadership Dashboard — KPI cards and filters scope correctly
- [ ] Location page — 6 KPI cards render, pie charts load, comparison table shows Match/Mismatch/Unknown tabs with correct VPN badges

---

## Database Policy

**No new data tables.** Only VIEWs and indexes are permitted in the production database.
All production DB changes are logged in [`database/PRODUCTION_CHANGES.md`](database/PRODUCTION_CHANGES.md).

---

## Project Structure

```
computrace-reporting/
├── client/                  # React + Vite frontend
│   └── src/
│       ├── pages/           # Route-level page components
│       ├── components/      # Shared UI components
│       ├── services/        # API client (api.ts)
│       ├── types/           # Shared TypeScript types
│       └── utils/           # Helper functions
├── server/                  # Express + TypeScript API
│   └── src/
│       ├── controllers/     # Route handlers
│       ├── routes/          # Express router definitions
│       ├── config/          # DB, Redis, constants, thresholds
│       └── utils/           # Shared server utilities
└── database/
    └── PRODUCTION_CHANGES.md  # Changelog of all DB changes
```