# Requirements: VCL Multi-Vertical Upload

## Context

The compliance team generates CCP (Customer Compliance Program) metric data from CyberMetrics on a 24-hour cycle. The data is exported as separate xlsx files per organizational vertical (e.g., NTS_AEO, SDIT_CISO, TSI). They need a way to upload these files into the VCL reporting page to generate executive-level compliance reports for senior leadership across all organizations — with the ability to drill down by vertical and by metric.

This is a POC that may later be replaced by direct API integration with CyberMetrics. It will be deployed as a separate flow from the existing single-file AEO compliance upload, and may run on its own instance to isolate compliance team experimentation from dev/production data.

## Verticals (from filename convention)

| Vertical Code | Organization |
|---|---|
| AllOthers | Catch-all for unclassified |
| NTS_AEO | NTS AEO (contains sub-teams: STEAM, ACCESS-ENG, ACCESS-OPS) |
| NTS_AVVOC | NTS AVVOC |
| NTS_CPE | NTS CPE |
| NTS_NEO | NTS NEO |
| NTS_WTS | NTS WTS |
| PRDCT_VSO | Product VSO |
| SBNOE | SBNOE |
| SDIT_CISO | SDIT CISO |
| SDIT_CSD | SDIT CSD |
| SDIT_EDIS | SDIT EDIS |
| SDIT_IT | SDIT IT |
| SR | SR |
| TSI | TSI |

## User Stories

### US-1: Multi-file upload

As a compliance analyst, I want to upload multiple vertical xlsx files at once so that I can ingest a full reporting cycle without uploading one file at a time.

**Acceptance Criteria:**
- 1.1: User can select or drag-drop 1–14 xlsx files simultaneously
- 1.2: System extracts the vertical code from each filename using the pattern `<VERTICAL>_YYYY_MM_DD.xlsx`
- 1.3: System extracts the report date from each filename
- 1.4: If a filename does not match the expected pattern, the user is prompted to manually assign a vertical and date
- 1.5: A preview table shows: filename, detected vertical, report date, item count, diff (new/recurring/resolved) per file
- 1.6: User can remove individual files from the batch before committing
- 1.7: User confirms the batch and all files are committed
- 1.8: Upload supports daily frequency (not just weekly)

### US-2: Vertical-scoped resolution

As a compliance analyst, I want uploading a vertical's file to only affect that vertical's data so that partial uploads don't incorrectly resolve devices from other verticals.

**Acceptance Criteria:**
- 2.1: Committing a file for vertical X only resolves active items where `vertical = X`
- 2.2: Items belonging to other verticals remain unchanged
- 2.3: Re-uploading the same vertical on the same day replaces the previous state (idempotent)
- 2.4: The system tracks which upload introduced/resolved each item per vertical

### US-3: Cross-vertical VCL report

As a senior leader, I want to see an aggregated compliance report across all verticals so that I can assess organizational posture at a glance.

**Acceptance Criteria:**
- 3.1: Stats bar shows aggregated totals: Total Devices, Compliant, Non-Compliant, Current %, Target %
- 3.2: Vertical breakdown table shows one row per vertical with: compliance %, non-compliant count, total devices
- 3.3: Donut chart shows Blocked vs In-Progress across all verticals
- 3.4: Trend chart shows monthly compliance % over time (aggregated)
- 3.5: Data refreshes immediately after a new upload is committed

### US-4: Vertical drill-down

As a compliance analyst, I want to click into a vertical to see its per-metric breakdown so that I can identify which metrics are driving non-compliance.

**Acceptance Criteria:**
- 4.1: Clicking a vertical row navigates to a detail view for that vertical
- 4.2: Detail view shows per-metric compliance: metric ID, description, compliant count, non-compliant count, compliance %, target %
- 4.3: Metrics are grouped by category (Logging & Monitoring, Vulnerability Management, etc.)
- 4.4: Each metric row is clickable to see the device list for that metric

### US-5: Metric drill-down

As a compliance analyst, I want to click a metric within a vertical to see the non-compliant devices so that I can identify specific remediation targets.

**Acceptance Criteria:**
- 5.1: Clicking a metric shows the list of non-compliant devices for that metric in that vertical
- 5.2: Device list shows: hostname, IP address, device type, team (sub-team), seen count, first seen, last seen
- 5.3: Devices can have resolution dates set (for burndown forecasting)
- 5.4: Devices can have remediation plans documented

### US-6: Burndown forecast

As a senior leader, I want to see a projected burndown timeline so that I can assess whether verticals are on track to meet compliance targets.

**Acceptance Criteria:**
- 6.1: Each vertical in the breakdown table shows forecast burndown columns (monthly projections)
- 6.2: Burndown is computed from resolution_date values on non-compliant devices
- 6.3: Devices without a resolution_date count as "blockers" (no committed timeline)
- 6.4: The trend chart includes a forecast line (linear regression on 3+ months of data)
- 6.5: Per-vertical drill-down shows that vertical's burndown separately

### US-7: Separation from existing AEO upload

As a system administrator, I want the multi-vertical upload to be a separate flow from the existing AEO compliance upload so that the two don't interfere with each other.

**Acceptance Criteria:**
- 7.1: Multi-vertical upload has its own UI entry point (separate from the existing "Upload" button on the AEO Compliance page)
- 7.2: Multi-vertical data is stored with a `vertical` field that distinguishes it from existing AEO-only data
- 7.3: Existing AEO compliance page continues to work unchanged
- 7.4: The VCL report page can show either multi-vertical data or fall back to existing AEO-only data if no multi-vertical uploads exist
- 7.5: The system can be deployed as a standalone instance without affecting other deployments

### US-8: Summary sheet ingestion

As a compliance analyst, I want the system to parse the Summary sheet from each vertical file so that metric-level health data (compliance %, targets) is captured per vertical.

**Acceptance Criteria:**
- 8.1: Summary sheet data is stored per vertical per upload
- 8.2: Overall scores (customer_network, vertical) are captured
- 8.3: Per-metric entries include: metric_id, non_compliant, compliant, total, compliance_pct, target, status, description
- 8.4: Summary data feeds the metric drill-down view (compliance % and targets come from here)