# Weekly Vulnerability Report Upload Feature

## Overview

A new feature has been added to the CVE Dashboard that allows users to upload their weekly vulnerability reports in Excel format (.xlsx) and automatically process them to split multiple CVE IDs into separate rows for easier filtering and analysis.

## What Was Implemented

### Backend Changes

1. **Database Migration** (`backend/migrations/add_weekly_reports_table.js`)
   - Created `weekly_reports` table to store report metadata
   - Tracks upload date, file paths, row counts, and which report is current
   - Indexed for fast queries

2. **Excel Processor** (`backend/helpers/excelProcessor.js`)
   - Executes Python script via Node.js child_process
   - Parses row counts from Python output
   - Handles errors, timeouts (30 seconds), and validation

3. **API Routes** (`backend/routes/weeklyReports.js`)
   - `POST /api/weekly-reports/upload` - Upload and process Excel file
   - `GET /api/weekly-reports` - List all reports
   - `GET /api/weekly-reports/:id/download/:type` - Download original or processed file
   - `DELETE /api/weekly-reports/:id` - Delete report (admin only)

4. **Python Script** (`backend/scripts/split_cve_report.py`)
   - Moved from ~/Documents to backend/scripts
   - Splits comma-separated CVE IDs into separate rows
   - Duplicates device/IP data for each CVE

### Frontend Changes

1. **Weekly Report Modal** (`frontend/src/components/WeeklyReportModal.js`)
   - Phase-based UI: idle → uploading → processing → success
   - File upload with .xlsx validation
   - Display existing reports with current report indicator (★)
   - Download buttons for both original and processed files

2. **App.js Integration**
   - Added "Weekly Report" button next to NVD Sync button
   - State management for modal visibility
   - Modal rendering

## How to Use

### Starting the Application

1. **Backend:**
   ```bash
   cd /home/admin/cve-dashboard/backend
   node server.js
   ```

2. **Frontend:**
   ```bash
   cd /home/admin/cve-dashboard/frontend
   npm start
   ```

### Using the Feature

1. **Access the Feature**
   - Login as an editor or admin user
   - Look for the "Weekly Report" button in the top header (next to "NVD Sync")

2. **Upload a Report**
   - Click the "Weekly Report" button
   - Click "Choose File" and select your .xlsx file
   - Click "Upload & Process"
   - Wait for processing to complete (usually 5-10 seconds)

3. **Download Processed Report**
   - After upload succeeds, you'll see row counts (e.g., "45 → 67 rows")
   - Click "Download Processed" to get the split version
   - The current week's report is marked with a ★ star icon

4. **Access Previous Reports**
   - All previous reports are listed below the upload section
   - Click the download icons to get original or processed versions
   - Reports are labeled as "This week's report", "Last week's report", or by date

### What the Processing Does

**Before Processing:**
| HOSTNAME | IP | CVE ID |
|----------|------------|---------------------------|
| server01 | 10.0.0.1 | CVE-2024-1234, CVE-2024-5678 |

**After Processing:**
| HOSTNAME | IP | CVE ID |
|----------|------------|---------------------------|
| server01 | 10.0.0.1 | CVE-2024-1234 |
| server01 | 10.0.0.1 | CVE-2024-5678 |

Each CVE now has its own row, making it easy to:
- Sort by CVE ID
- Filter for specific CVEs
- Research CVEs one by one per device

## File Locations

### New Files Created

```
backend/
  scripts/
    split_cve_report.py          # Python script for CVE splitting
    requirements.txt             # Python dependencies
  routes/
    weeklyReports.js             # API endpoints
  helpers/
    excelProcessor.js            # Python integration
  migrations/
    add_weekly_reports_table.js  # Database migration
  uploads/
    weekly_reports/              # Uploaded and processed files

frontend/
  src/
    components/
      WeeklyReportModal.js       # Upload modal UI
```

### Modified Files

```
backend/
  server.js                      # Added route mounting

frontend/
  src/
    App.js                       # Added button and modal
```

## Security & Permissions

- **Upload**: Requires editor or admin role
- **Download**: Any authenticated user
- **Delete**: Admin only
- **File Validation**: Only .xlsx files accepted, 10MB limit
- **Audit Logging**: All uploads, downloads, and deletions are logged

## Troubleshooting

### Backend Issues

**Python not found:**
```bash
# Install Python 3
sudo apt-get install python3
```

**Missing dependencies:**
```bash
# Install pandas and openpyxl
pip3 install pandas openpyxl
```

**Port already in use:**
```bash
# Find and kill process using port 3001
lsof -i :3001
kill -9 <PID>
```

### Frontend Issues

**Button not visible:**
- Make sure you're logged in as editor or admin
- Viewer role cannot upload reports

**Upload fails:**
- Check file is .xlsx format (not .xls or .csv)
- Ensure file has "Vulnerabilities" sheet with "CVE ID" column
- Check file size is under 10MB

**Processing timeout:**
- Large files (10,000+ rows) may timeout
- Try reducing file size or increase timeout in `excelProcessor.js`

## Testing Checklist

- [x] Backend starts without errors
- [x] Frontend compiles successfully
- [x] Database migration completed
- [x] Python dependencies installed
- [ ] Upload .xlsx file (manual test in browser)
- [ ] Verify processed file has split CVEs (manual test)
- [ ] Download original and processed files (manual test)
- [ ] Verify current report marked with star (manual test)
- [ ] Test as viewer - button should be hidden (manual test)

## Future Enhancements

Possible improvements:
- Progress bar during Python processing
- Email notifications when processing completes
- Scheduled automatic uploads
- Report comparison (diff between weeks)
- Export to other formats (CSV, JSON)
- Bulk delete old reports
- Report validation before upload

## Support

For issues or questions:
1. Check the troubleshooting section above
2. Review audit logs for error details
3. Check browser console for frontend errors
4. Review backend server logs for API errors