# Production Database Changes

All SQL that must be run against the production database, in chronological order.
Every statement is **idempotent** — safe to re-run, will no-op if already applied.

**Policy:** No new data tables. Only VIEWs and indexes are permitted.

---

## [2026-03-24] — Calls Feature (Issue #9)

### New index on `call_details`

The `/reports/calls/summary`, `/reports/calls/stats`, and `/reports/calls/:empCode` endpoints
all filter by `DATE(call_started_on) BETWEEN ? AND ?` and optionally by `emp_code`.
Without this index MySQL performs a full table scan on every call report request.

```sql
CREATE INDEX IF NOT EXISTS idx_cd_started_emp
  ON call_details (call_started_on, emp_code);
```

---

## [2026-03-24] — Idle/Sleep Analysis (Issue #17)

### New index on `system_sleep_details`

The `/reports/idle/patterns` endpoint queries `system_sleep_details` filtered by date
and grouped by hour of day. Without this index MySQL performs a full table scan on
every request on a 1,293-employee org.

```sql
CREATE INDEX IF NOT EXISTS idx_ssd_triggered_emp
  ON system_sleep_details (triggered_on, emp_code);
```

### Update `v_daily_attendance` view

Two fixes:
1. **`idle_percentage` formula corrected** — old formula `idle / (active + idle + sleep)`
   included sleep in the denominator, which diluted idle % when systems slept.
   New formula: `idle / (active + idle)` — idle as a fraction of awake (screen-on) time only.
2. **New columns added** — `awake_hours` and `sleep_percentage` to distinguish
   active/idle behaviour from system sleep.

```sql
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;
```

---

## [2026-03-23] — Absence Detection (Issue #6 / PR #19)

### 6 base indexes

```sql
-- 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
--    (WHERE total_active > 0 AND DATE(work_logged_on) < ?)
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 GROUP BY, no LIMIT)
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 (getEmployeeApps)
CREATE INDEX IF NOT EXISTS idx_rad_emp_started
  ON running_application_details_archive (emp_code, started_on);
```

### 6 base views

```sql
-- View 1: Daily attendance summary per employee
-- NOTE: Formula updated 2026-03-24 — see entry above for current definition.
-- Run the 2026-03-24 version; this entry is for historical reference only.

-- View 2: 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';

-- View 3: 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);

-- View 4: 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);

-- View 5: 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);

-- View 6: 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);
```

---

## Complete Index Reference

| # | Index | Table | Columns | Added |
|---|---|---|---|---|
| 1 | `idx_ewt_emp_date` | `employee_working_time` | `(emp_code, work_logged_on)` | 2026-03-23 |
| 2 | `idx_ewt_emp_active_date` | `employee_working_time` | `(emp_code, total_active, work_logged_on)` | 2026-03-23 |
| 3 | `idx_ewt_date_active` | `employee_working_time` | `(work_logged_on, total_active)` | 2026-03-23 |
| 4 | `idx_ed_usertype_dept` | `EmployeeDetails` | `(UserType, Department)` | 2026-03-23 |
| 5 | `idx_rad_started_emp_app` | `running_application_details_archive` | `(started_on, emp_code, application_name)` | 2026-03-23 |
| 6 | `idx_rad_emp_started` | `running_application_details_archive` | `(emp_code, started_on)` | 2026-03-23 |
| 7 | `idx_ssd_triggered_emp` | `system_sleep_details` | `(triggered_on, emp_code)` | 2026-03-24 |
| 8 | `idx_cd_started_emp` | `call_details` | `(call_started_on, emp_code)` | 2026-03-24 |

## Complete View Reference

| View | Source Tables | Added | Last Updated |
|---|---|---|---|
| `v_daily_attendance` | `employee_working_time`, `EmployeeDetails` | 2026-03-23 | 2026-03-24 (formula fix) |
| `v_active_employees` | `EmployeeDetails` | 2026-03-23 | — |
| `v_first_last_login` | `system_login_details` | 2026-03-23 | — |
| `v_app_usage_daily` | `running_application_details_archive`, `EmployeeDetails` | 2026-03-23 | — |
| `v_call_summary` | `call_details`, `EmployeeDetails` | 2026-03-23 | — |
| `v_idle_hourly_pattern` | `system_sleep_details`, `EmployeeDetails` | 2026-03-23 | — |

---

## Deployment Instructions

1. Apply entries **oldest first** (bottom to top in this file).
2. To find what's new since your last deployment, compare the date of your last run
   against the entry dates above.
3. All statements are idempotent — re-running a section already applied is safe.
4. After applying, record the date and your name in a comment on the section.
5. The authoritative view definitions are in `database/views/001_create_views.sql`.
   This file is the changelog; that file is the source of truth.