# Database and UI Updates - 2025-12-28

## Summary

Fixed the status filter issue and added container format and encoder columns to the dashboard table.

## Changes Made

### 1. Fixed Status Filter (dashboard.py:717)

**Issue**: Status filter dropdown wasn't working for "Discovered" state - API was rejecting it as invalid.

**Fix**: Added 'discovered' to the valid_states list in the `/api/files` endpoint.

```python
# Before
valid_states = ['pending', 'processing', 'completed', 'failed', 'skipped', None]

# After
valid_states = ['discovered', 'pending', 'processing', 'completed', 'failed', 'skipped', None]
```

**Testing**: Select "Discovered" in the status filter dropdown - should now properly filter files.

---

### 2. Added Container Format Column to Database

**Files Modified**:
- `dashboard.py` (lines 161, 210)
- `reencode.py` (lines 374, 388, 400, 414, 417, 934, 951, 966)

**Database Schema Changes**:
```sql
ALTER TABLE files ADD COLUMN container_format TEXT
```

**Scanner Updates**:
- Extracts container format from FFprobe output during library scan
- Format name extracted from `format.format_name` (e.g., "matroska", "mov,mp4,m4a,3gp,3g2,mj2")
- Takes first format if multiple listed

**Migration**: Automatic - runs on next dashboard or scanner startup

---

### 3. Added Dashboard Table Columns

**dashboard.html Changes**:

**Table Headers** (lines 667-675):
- Added "Container" column (shows file container format like MKV, MP4)
- Added "Encoder" column (shows encoder used for completed files)
- Moved existing columns to accommodate

**Table Column Order**:
1. Checkbox
2. File
3. State
4. Resolution (now shows actual resolution like "1920x1080")
5. **Container** (NEW - shows MKV, MP4, AVI, etc.)
6. **Encoder** (NEW - shows encoder used like "hevc_qsv", "h264_nvenc")
7. Original Size
8. Encoded Size
9. Savings
10. Status

**Data Display** (lines 1518-1546):
- Resolution: Shows `widthxheight` (e.g., "1920x1080") or "-"
- Container: Shows uppercase format name (e.g., "MATROSKA", "MP4") or "-"
- Encoder: Shows encoder_used from database (e.g., "hevc_qsv") or "-"

**Colspan Updates**: Changed from 8 to 10 to match new column count

---

### 4. Database Update Script

**File**: `update-database.py`

**Purpose**: Populate container_format for existing database records

**Usage**:
```bash
# Auto-detect database location
python update-database.py

# Specify database path
python update-database.py path/to/state.db
```

**What It Does**:
1. Finds all files with NULL or empty container_format
2. Uses ffprobe to extract container format
3. Updates database with format information
4. Shows progress for each file
5. Commits every 10 files for safety

**Requirements**: ffprobe must be installed and in PATH

**Example Output**:
```
Opening database: data/state.db
Found 42 files to update
[1/42] Updated: movie1.mkv -> matroska
[2/42] Updated: movie2.mp4 -> mov,mp4,m4a,3gp,3g2,mj2
...
Update complete!
  Updated: 40
  Failed: 2
  Total: 42
```

---

## How Container Format is Populated

### For New Scans (Automatic)
When you run "Scan Library", the scanner now:
1. Runs FFprobe on each file
2. Extracts `format.format_name` from JSON output
3. Takes first format if comma-separated list
4. Stores in database during `add_file()`

**Example**:
- MKV files: `format_name = "matroska,webm"` → stored as "matroska"
- MP4 files: `format_name = "mov,mp4,m4a,3gp,3g2,mj2"` → stored as "mov"

### For Existing Records (Manual)
Run the update script to populate container format for files already in database:
```bash
python update-database.py
```

---

## Encoder Column

The "Encoder" column shows which encoder was used for completed encodings:

**Data Source**: `files.encoder_used` column (already existed)

**Display**:
- Completed files: Shows encoder name (e.g., "hevc_qsv", "h264_nvenc")
- Other states: Shows "-"

**Updated By**: The encoding process already sets this when completing a file

**Common Values**:
- `hevc_qsv` - Intel QSV H.265
- `av1_qsv` - Intel QSV AV1
- `h264_nvenc` - NVIDIA NVENC H.264
- `hevc_nvenc` - NVIDIA NVENC H.265
- `libx265` - CPU H.265
- `libx264` - CPU H.264

---

## Testing Checklist

### Status Filter
- [ ] Select "All States" - shows all files
- [ ] Select "Discovered" - shows only discovered files
- [ ] Select "Pending" - shows only pending files
- [ ] Select "Completed" - shows only completed files
- [ ] Combine with attribute filter (e.g., Discovered + 4K)

### Dashboard Table
- [ ] Table has 10 columns (was 8)
- [ ] Resolution column shows actual resolution or "-"
- [ ] Container column shows format name or "-"
- [ ] Encoder column shows encoder for completed files or "-"
- [ ] All columns align properly

### New Scans
- [ ] Run "Scan Library"
- [ ] Check database - new files should have container_format populated
- [ ] Dashboard should show container formats immediately

### Database Update Script
- [ ] Run `python update-database.py`
- [ ] Verify container_format populated for existing files
- [ ] Check dashboard - existing files should now show containers

---

## Migration Notes

**Backward Compatible**: Yes
- New columns have NULL default
- Existing code works without changes
- Database auto-migrates on startup

**Data Loss**: None
- Existing data preserved
- Only adds new columns

**Rollback**: Safe
- Can remove columns with ALTER TABLE DROP COLUMN (SQLite 3.35+)
- Or restore from backup

---

## Files Changed

1. **dashboard.py**
   - Line 161: Added container_format to schema
   - Line 210: Added container_format migration
   - Line 717: Fixed valid_states to include 'discovered'

2. **reencode.py**
   - Line 374: Added container_format migration
   - Line 388: Added container_format parameter to add_file()
   - Lines 400, 414, 417: Updated SQL to include container_format
   - Lines 934, 951: Extract and pass container_format during scan
   - Line 966: Pass container_format to add_file()

3. **templates/dashboard.html**
   - Lines 670-671: Added Container and Encoder column headers
   - Line 680: Updated colspan from 8 to 10
   - Line 1472: Updated empty state colspan to 10
   - Lines 1518-1525: Added resolution, container, encoder formatting
   - Lines 1544-1546: Added new columns to table row

4. **update-database.py** (NEW)
   - Standalone script to populate container_format for existing records

---

## Next Steps

1. **Restart Flask Application** to load database changes
2. **Test Status Filter** - verify "Discovered" works
3. **Scan Library** (optional) - populates container format for new files
4. **Run Update Script** - `python update-database.py` to update existing files
5. **Verify Dashboard** - check that all columns display correctly