# Database Column Updates - March 27, 2026

## Summary
Updated all PHP files to use the correct database column names from the webhost database schema.

## Files Updated

### 1. **dashboard/controllers/DashboardController.php**
- Line 70: `created_at` → `logged_at` (getTotalActivities method)
- Line 115: `a.created_at DESC` → `a.logged_at DESC` (getRecentActivities method)

### 2. **dashboard/controllers/AgentViewController.php**
- Lines 32-46: Removed invalid `a.created_at` column from agents GROUP BY clause
- Line 75: `created_at` → `logged_at` (getAgentActivityTimeline method)
- Line 78: `ORDER BY created_at DESC` → `ORDER BY logged_at DESC`

### 3. **dashboard/controllers/ActivityViewController.php**
- Line 32: `a.created_at` → `a.logged_at` (getActivities SELECT)
- Line 49: `DATE(a.created_at)` → `DATE(a.logged_at)` (date filter)
- Line 53: `DATE(a.created_at)` → `DATE(a.logged_at)` (date filter)
- Line 68: `ORDER BY a.created_at DESC` → `ORDER BY a.logged_at DESC`
- Line 97: `created_at` → `logged_at` (getActivityStats)
- Line 116: `DATE(created_at)` → `DATE(logged_at)` (getActivityTimeline)
- Line 120: `created_at` → `logged_at` (getActivityTimeline)
- Line 120: `GROUP BY DATE(created_at)` → `GROUP BY DATE(logged_at)`

### 4. **dashboard/controllers/CommandViewController.php**
- Line 31: `c.created_at` → `c.issued_at` (getCommands SELECT)
- Line 54: Updated count_sql to use `issued_at` instead of `created_at`
- Line 59: `ORDER BY c.created_at DESC` → `ORDER BY c.issued_at DESC`
- Line 109: `ORDER BY created_at ASC` → `ORDER BY issued_at ASC` (getPendingCommandsForAgent)
- Line 150: `c.created_at` → `c.issued_at` (getCommandsByStatus)
- Line 156: `ORDER BY c.created_at DESC` → `ORDER BY c.issued_at DESC`
- Line 177: `TIMESTAMPDIFF(SECOND, created_at, executed_at)` → `TIMESTAMPDIFF(SECOND, issued_at, executed_at)`
- Line 179: `created_at` → `issued_at` (getCommandExecutionStats)
- Line 200: `created_at` → `issued_at` (getAgentCommandHistory)
- Line 205: `ORDER BY created_at DESC` → `ORDER BY issued_at DESC`
- Line 240: `created_at` → `issued_at` (sendCommand INSERT)

## Column Mapping Reference

| Table | Old Column | New Column | Status |
|-------|-----------|-----------|--------|
| pc_activities | created_at | logged_at | ✅ UPDATED |
| agents | updated_at | last_seen | ✅ UPDATED |
| agents | created_at | (removed - not in schema) | ✅ UPDATED |
| remote_commands | created_at | issued_at | ✅ UPDATED |
| running_processes | created_at | snapshot_at | ✅ (not used in dashboard) |
| browser_history | created_at | visit_time | ✅ (not used in dashboard) |
| bandwidth_control | created_at | issued_at | ✅ (not used in dashboard) |
| network_status | created_at | checked_at | ✅ (not used in dashboard) |

## API Controllers Status
- **api/controllers/CommandController.php**: Already uses `issued_at` ✅
- **api/controllers/AgentController.php**: Already uses `last_seen` ✅
- **api/controllers/ActivityController.php**: Doesn't query by timestamp, no changes needed ✅

## Files Not Modified (No Changes Needed)
- debug.php - Already updated in previous fix
- BandwidthViewController.php - Doesn't use timestamp columns
- DashboardIntegration.php - Doesn't use timestamp columns

## Testing Steps
1. ✅ Verify all PHP files have no syntax errors
2. ✅ Confirm grep_search finds no remaining `created_at` or `updated_at` references in dashboard
3. Deploy to webhost
4. Test dashboard pages to confirm data loads correctly:
   - Agents page
   - Activities page
   - Commands page
   - Dashboard stats

## Deployment Notes
All changes are backward compatible with the webhost database schema. No database migrations required.