media-scrapper/README.md

# Plex Media Library Statistics Generator

A production-quality Python 3 command-line tool for scanning Plex-style media libraries and generating CSV statistics about storage size versus total video duration.

## Purpose

This tool analyzes media libraries (TV shows, anime, and movies) without requiring Plex itself or the Plex API. It operates directly on the filesystem and generates CSV reports showing:

- Storage size (in bytes and GB)
- Total video duration (in seconds and hours)
- Storage efficiency (GB per hour of video)

The key innovation is **incremental scanning with SQLite caching**: after the first full scan, subsequent scans are fast because unchanged files are never re-probed with FFmpeg.

## Features

- ✅ Supports TV shows, anime (treated as TV), and movies
- ✅ Automatic library type detection (override with `--type`)
- ✅ Persistent SQLite cache with change detection
- ✅ Fast incremental scans using file size + mtime signatures
- ✅ Comprehensive error handling (one bad file doesn't break the scan)
- ✅ Progress output during scanning and probing
- ✅ CSV output sorted alphabetically by series/movie
- ✅ Support for multiple libraries in a single database
- ✅ Per-video metadata stored for future feature expansion

## Requirements

### System

- Linux (though Windows/macOS paths would work with minor adjustments)
- Python 3.10+
- FFmpeg with `ffprobe` installed

### Python

Only Python standard library is used:
- `argparse` (CLI argument parsing)
- `pathlib` (filesystem operations)
- `sqlite3` (caching)
- `subprocess` (invoking ffprobe)
- `csv` (output generation)

## Installation

### Ubuntu / Debian

```bash
# Install FFmpeg
sudo apt update
sudo apt install ffmpeg

# Clone or download the tool
cd /path/to/media-scrapper

# Make the script executable (optional)
chmod +x plex_stats.py

# Test that ffprobe works
ffprobe -version
```

### CentOS / RHEL

```bash
sudo yum install ffmpeg
```

### macOS (using Homebrew)

```bash
brew install ffmpeg
```

## Quick Start

### Scan a TV library

```bash
python3 plex_stats.py /plex/tv --type tv --csv-prefix tv
```

Output:
- `tv_seasons.csv` — one row per season
- `tv_series.csv` — one row per complete series
- `plex_stats.db` — SQLite cache

### Scan an anime library

```bash
python3 plex_stats.py /plex/anime --type tv --csv-prefix anime
```

TV and anime use the same directory structure and CSV format.

### Scan a movies library

```bash
python3 plex_stats.py /plex/movies --type movies --csv-prefix movies
```

Output:
- `movies_movies.csv` — one row per movie folder

### Re-scan (fast)

Run the same command again. Files that haven't changed will be cached and won't be re-probed:

```bash
python3 plex_stats.py /plex/tv --type tv --csv-prefix tv
```

Expected output:
```
Scanning /plex/tv...

Files discovered: 4,812
Cached/unchanged: 4,790
New:              17
Changed:           5
Removed:           3

Probing 22 files...
  [1/22] Breaking Bad...
  [2/22] Game of Thrones...
  ...

Scan complete.

TV shows:       163
Seasons:        691
Video files:    4,809
Total size:     6.82 TB
Total runtime:  3,921.4 hours

Unchanged:      4,790
New:            14
Changed:        5
Removed:        3
Errors:         0

CSV files written:
  tv_seasons.csv
  tv_series.csv

Database:
  plex_stats.db
```

## CLI Options

```
usage: plex_stats.py [-h] [--type {tv,movies}] [--db DB] 
                     [--output-dir OUTPUT_DIR] [--csv-prefix CSV_PREFIX] 
                     [--force] [--verbose]
                     library_root

positional arguments:
  library_root          Path to media library root (e.g. /plex/tv)

optional arguments:
  -h, --help            show help message
  --type {tv,movies}    Library type. If omitted, inferred from directory name.
  --db DB               Path to SQLite database (default: plex_stats.db)
  --output-dir OUTPUT   Directory for CSV files (default: current directory)
  --csv-prefix PREFIX   Prefix for CSV filenames (default: derived from root name)
  --force               Force ffprobe scan of all files (ignore cache)
  --verbose             Print verbose output
```

## Usage Examples

### Basic single-library scan

```bash
python3 plex_stats.py /plex/tv
```

Infers type as "tv", creates `plex_stats.db` in current directory, outputs `tv_seasons.csv` and `tv_series.csv`.

### All three libraries with explicit prefixes

```bash
python3 plex_stats.py /plex/tv --type tv --csv-prefix tv
python3 plex_stats.py /plex/anime --type tv --csv-prefix anime
python3 plex_stats.py /plex/movies --type movies --csv-prefix movies
```

All use the same `plex_stats.db`, creating six CSV files:
- `tv_seasons.csv`, `tv_series.csv`
- `anime_seasons.csv`, `anime_series.csv`
- `movies_movies.csv`

### Centralized database and output directory

```bash
python3 plex_stats.py /plex/tv \
    --type tv \
    --db /var/lib/plex-stats/plex_stats.db \
    --output-dir /home/user/plex-reports \
    --csv-prefix tv
```

### Force complete re-scan (e.g., after fixing bad ffprobe issues)

```bash
python3 plex_stats.py /plex/tv --force
```

Every file is re-probed. Useful if you suspect cache corruption or want to update all durations.

### Verbose output

```bash
python3 plex_stats.py /plex/tv --verbose
```

## Directory Structure

### TV / Anime

```
/plex/tv/
├── Breaking Bad/                          <- Series name (first directory level)
│   ├── Season 01/                         <- Season folder (second level)
│   │   ├── Breaking Bad - S01E01.mkv
│   │   ├── Breaking Bad - S01E02.mkv
│   │   └── ...
│   ├── Season 02/
│   │   └── ...
│   └── Specials/                          <- Any season name is recognized
├── Game of Thrones/
│   ├── Season 01/
│   │   └── ...
│   └── Season 02/
│       └── ...
```

**Season folder names are flexible:**
- `Season 01`, `Season 1`
- `Season 00` (for pilots/specials)
- `Specials`, `Special`
- `S01`, `S1`

The actual folder name found on disk is preserved in the CSV output.

### Movies

```
/plex/movies/
├── Dune (2021)/                           <- Movie name (first directory level)
│   └── Dune (2021).mkv
├── Interstellar (2014)/
│   ├── Interstellar (2014).mkv
│   └── Interstellar (2014) - Extras.mkv   <- Multiple files per movie are aggregated
```

## Recognized Video Extensions

The tool recognizes these file extensions (case-insensitive):

- `.mkv`, `.mp4`, `.m4v`, `.avi`, `.mov`
- `.ts`, `.m2ts`, `.webm`
- `.mpg`, `.mpeg`, `.wmv`

Other files (subtitles, `.srt`, `.sub`, `.idx`, `.nfo`, images, metadata) are ignored.

## CSV Output Format

### TV Seasons (`*_seasons.csv`)

One row per season of a TV series:

```csv
Library,Series,Season,File_Count,Size_Bytes,Size_GB,Duration_Seconds,Duration_Hours,GB_Per_Hour
tv,Breaking Bad,Season 01,7,13786845020,12.84,20952,5.82,2.21
tv,Breaking Bad,Season 02,13,25887942819,24.11,38628,10.73,2.25
tv,Breaking Bad,Season 03,13,26943589405,25.10,38628,10.73,2.34
tv,Game of Thrones,Season 01,10,47129348901,43.91,41932,11.65,3.77
tv,Game of Thrones,Season 02,10,45219374812,42.13,41932,11.65,3.61
```

- **Library**: Always "tv" for TV/anime
- **Series**: Show name
- **Season**: Season folder name as found on disk
- **File_Count**: Number of video files in this season
- **Size_Bytes**: Total bytes (numeric, for calculation)
- **Size_GB**: Decimal gigabytes (1 GB = 1,000,000,000 bytes)
- **Duration_Seconds**: Total duration in seconds
- **Duration_Hours**: Total duration in hours
- **GB_Per_Hour**: Storage efficiency (size / duration)

### TV Series (`*_series.csv`)

One row per complete series, summing all seasons:

```csv
Library,Series,Season_Count,File_Count,Size_Bytes,Size_GB,Duration_Seconds,Duration_Hours,GB_Per_Hour
tv,Breaking Bad,5,62,128608176245,119.88,191500,53.19,2.25
tv,Game of Thrones,8,80,434328947123,404.56,334656,92.96,4.35
```

- **Season_Count**: Number of seasons in the series
- Other fields aggregate across all seasons

### Movies (`*_movies.csv`)

One row per movie:

```csv
Library,Movie,File_Count,Size_Bytes,Size_GB,Duration_Seconds,Duration_Hours,GB_Per_Hour
movies,Dune (2021),1,4829348901,4.50,10680,2.97,1.51
movies,Interstellar (2014),2,6234919234,5.81,14400,4.00,1.45
```

- **Movie**: Movie folder name
- **File_Count**: Number of video files under the movie folder (includes extras)
- Other fields aggregate all files

### CSV Import to Google Sheets

All numeric columns remain numeric (not formatted as strings), so they import cleanly into Google Sheets:

1. Open Google Sheets
2. Click "File" > "Import" > "Upload"
3. Select the CSV file
4. Choose "Replace spreadsheet"
5. Numeric columns are automatically detected

## SQLite Cache Database

### Schema

The tool automatically creates and maintains a SQLite database with this schema:

```sql
CREATE TABLE media_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT UNIQUE NOT NULL,
    library_root TEXT NOT NULL,
    library_type TEXT NOT NULL,
    show_movie TEXT NOT NULL,
    season TEXT,
    size_bytes INTEGER NOT NULL,
    mtime_ns INTEGER NOT NULL,
    duration_seconds REAL,
    scan_timestamp REAL NOT NULL,
    error_message TEXT
);

CREATE INDEX idx_library_root ON media_files(library_root);
CREATE INDEX idx_path ON media_files(path);
CREATE INDEX idx_show_movie ON media_files(show_movie);
CREATE INDEX idx_season ON media_files(season);
```

### Per-Video Storage

Every video file is stored individually, not just aggregated season/movie totals. This enables future features like:

- Per-episode statistics
- Codec and resolution analysis
- Average file size per series
- Duplicate detection
- Video quality metrics

## Change Detection & Incremental Scanning

### How It Works

1. **File discovery**: Walk the filesystem and list all video files.
2. **Stat check**: For each file, read `stat()` to get size and modification time.
3. **Cache lookup**: Check SQLite for this file's cached entry.
4. **Decision logic**:
   - If cached entry exists **AND** size and mtime match → **Reuse cache** (no ffprobe)
   - If cached entry exists **BUT** size or mtime differs → **Re-probe** (file changed)
   - If no cached entry → **Probe** (new file)
   - If cached entry is in SQLite **BUT** file no longer exists → **Delete from cache**

### Why Size + mtime?

- **Size**: Detects if video content changed
- **mtime (modification time)**: Detects if metadata or the file was touched
- **Together**: Extremely fast check without hashing or reading file content
- **Safe**: Works reliably for normal media workflows (copying files, minor edits)

### Limitations

If you manually edit file bytes without updating mtime (unusual), the cache will not detect it. This is acceptable for media libraries.

## Handling Unchanged, New, and Changed Files

### Unchanged
```
ffprobe not called → instant retrieval from cache
```

### New
```
File doesn't exist in cache → ffprobe called → database entry created
```

### Changed
```
File exists but size_bytes or mtime_ns differs → ffprobe called → database entry updated
```

### Removed
```
Cached file path no longer exists → removed from database
Note: Only scanned library root is checked. 
     Other libraries' files are not affected.
```

## Forced Rescans

Use `--force` to ignore the cache and re-probe every file:

```bash
python3 plex_stats.py /plex/tv --force
```

This is useful if:
- You suspect cache corruption
- FFmpeg was updated and you want fresh probes
- You want to update all durations to the latest ffprobe version

Forced rescans still respect the database schema and update all cached entries.

## Error Handling

### FFprobe Failures

If ffprobe fails on a specific file:
- A warning is printed to console
- The error is recorded in the database (`error_message` column)
- The scan continues with other files
- The file is eligible for retry on future scans

### What's Not an Error

- Permission denied reading a file → warning, continue
- Corrupted video file → ffprobe fails, recorded, continue
- Network timeouts on NFS → handled, continue

### Error Summary

At scan completion, total error count is displayed:

```
Errors: 5
```

To see details, query the database:

```bash
sqlite3 plex_stats.db "SELECT path, error_message FROM media_files WHERE error_message IS NOT NULL;"
```

## Database Management

### View cached files for a library

```bash
sqlite3 plex_stats.db "SELECT COUNT(*) FROM media_files WHERE library_root = '/plex/tv';"
```

### Export all files for a series

```bash
sqlite3 plex_stats.db "SELECT path, size_bytes, duration_seconds FROM media_files WHERE show_movie = 'Breaking Bad';"
```

### Clear all cache for one library

```bash
sqlite3 plex_stats.db "DELETE FROM media_files WHERE library_root = '/plex/tv';"
sqlite3 plex_stats.db "VACUUM;"  # Reclaim disk space
```

### Clear entire database

```bash
rm plex_stats.db
```

The tool will recreate it on the next run.

## Performance Tips

1. **First scan is slow**: Thousands of ffprobe calls can take 10–30 minutes for large libraries.
2. **Subsequent scans are fast**: Usually under a minute if only a few files changed.
3. **Use `--force` sparingly**: Only when you have a specific reason.
4. **Database is small**: Even for 10,000+ files, SQLite database is typically < 1 MB.
5. **CSV generation is instant**: All heavy lifting is in the scanning phase.

### Estimated Timeline

- 1,000 files: 3–10 minutes (first scan), 10–30 seconds (subsequent)
- 5,000 files: 15–50 minutes (first scan), 30–60 seconds (subsequent)
- 10,000 files: 30–90 minutes (first scan), 1–2 minutes (subsequent)

Varies based on filesystem speed, network latency (NFS/SMB), and average file size.

## Multiple Libraries in One Database

All three examples below use the same `plex_stats.db`:

```bash
python3 plex_stats.py /plex/tv --type tv --csv-prefix tv
python3 plex_stats.py /plex/anime --type tv --csv-prefix anime
python3 plex_stats.py /plex/movies --type movies --csv-prefix movies
```

**Database records are scoped by `library_root`**, so:
- Scanning `/plex/tv` only touches records where `library_root = '/plex/tv'`
- Deletion detection only removes files that belong to that library
- Each library can be scanned independently

This allows a single CSV output directory with files from multiple sources:
```
tv_seasons.csv       (from /plex/tv)
tv_series.csv        (from /plex/tv)
anime_seasons.csv    (from /plex/anime)
anime_series.csv     (from /plex/anime)
movies_movies.csv    (from /plex/movies)
```

## Troubleshooting

### ffprobe not found

```
Error: ffprobe was not found.

Install FFmpeg, for example on Ubuntu/Debian:

  sudo apt install ffmpeg
```

**Solution**: Install FFmpeg as shown in the error message.

### Permission denied scanning directory

```
Warning: Could not access /plex/tv: Permission denied
```

**Solution**: Run with appropriate permissions:
```bash
sudo python3 plex_stats.py /plex/tv
```

### Database locked

```
sqlite3.OperationalError: database is locked
```

**Solution**: Ensure only one instance of the tool is running. If multiple processes try to write simultaneously, wait for the first to finish.

### CSV files not created

Check that the output directory exists and is writable:
```bash
python3 plex_stats.py /plex/tv --output-dir /tmp
```

### Strange results in CSV

1. Verify the directory structure matches TV or movie format
2. Check for videos in unexpected locations
3. Re-scan with `--force` to regenerate all probes

## Extending the Tool

The database stores per-video metadata, so future enhancements are straightforward:

### Adding codec analysis
```python
# Expand ffprobe query to include codec
ffprobe ... -show_entries stream=codec_name
# Add columns: video_codec, audio_codec
# Aggregate in CSV generation
```

### Adding resolution tracking
```python
# Query video stream height
ffprobe ... -show_entries stream=height
# Add columns: resolution, resolution_count
```

### Per-episode breakdown
```python
# Parse episode numbers from filenames
# Create *_episodes.csv with per-episode stats
```

All of this is possible without redesigning the cache because per-video data is preserved.

## License

MIT License. Use freely.

## Support

For issues or feature requests, consult the source code and modify as needed. The tool is designed to be maintainable and extensible.

---

**Example complete workflow:**

```bash
#!/bin/bash

# Install FFmpeg (first time only)
sudo apt install ffmpeg

# Create output directory
mkdir -p ~/plex-reports

# Scan TV library
python3 plex_stats.py /plex/tv \
    --type tv \
    --db ~/plex-stats.db \
    --output-dir ~/plex-reports \
    --csv-prefix tv

# Scan anime library
python3 plex_stats.py /plex/anime \
    --type tv \
    --db ~/plex-stats.db \
    --output-dir ~/plex-reports \
    --csv-prefix anime

# Scan movies library
python3 plex_stats.py /plex/movies \
    --type movies \
    --db ~/plex-stats.db \
    --output-dir ~/plex-reports \
    --csv-prefix movies

# View results
ls -lh ~/plex-reports/*.csv

# Import into Google Sheets:
# - Open Google Sheets
# - File > Import
# - Upload ~/plex-reports/tv_seasons.csv
```