# backfill-and-analyze-video-thumbnails

Investigates video products missing thumbnail data in `art_relations.product_video` and generates a Liquibase SQL file to backfill `thumbnail_path` and `thumbnail_at_milliseconds`. Also generates a CSV of products that could not be resolved, with diagnostic information from both the ingest and approval workflows to guide next steps.

## Background

When a video product is ingested, MediaConvert extracts JPG frames stored in S3 at `{env}-orcd-video-assets/thumbnails/original-size/{pipeline_run_id}/`. A user must then select a thumbnail in the UI, which saves `thumbnail_path` and `thumbnail_at_milliseconds` to `product_video`. If this selection never happens (e.g. ingest failed silently), the approval workflow fails and delivery workers cannot find the S2/S5 TIFF images, producing the error:

```
UPC: XXXX Asset_type: video_image_S2 is not found in S3
```

## What the script does

For each UPC in the input file, it queries `art_relations.product_video` and handles these cases:

| Case | Action |
|---|---|
| `thumbnail_path` + `thumbnail_at_milliseconds` + `latest_approval_job_id` all set, S_ images present in `video_asset` | Skip — fully processed |
| `thumbnail_path` + `thumbnail_at_milliseconds` + `latest_approval_job_id` all set, but no S_ images in `video_asset` | Diagnose both ingest and approval jobs; skip with contextual reason |
| `thumbnail_path` + `thumbnail_at_milliseconds` set, no `latest_approval_job_id` | Diagnose ingest job; skip with contextual reason |
| `thumbnail_path` set, `thumbnail_at_milliseconds` null | Derive ms from path; diagnose both jobs. If errors found → CSV only. If clean → ms-only UPDATE in SQL |
| `thumbnail_path` null, `latest_pipeline_run_id` set | Look up 10th JPG frame in S3. If not found → diagnose both jobs; CSV only. If found → diagnose both jobs; if errors → CSV only, if clean → full UPDATE in SQL |
| `thumbnail_path` null, no `latest_pipeline_run_id` | Skip — ingest never completed |

For any case that runs a diagnosis, the script queries the ows-video `jobs`, `job_statuses`, `job_outputs` tables to surface:
- The workflow type and datetime of the ingest run
- Any `error_` output names from child jobs
- Any child jobs with ERROR status
- The overall status of the parent job (to distinguish a completed ingest from a failed one)

## Setup

### Install dependencies

```bash
pip install boto3 pymysql python-dotenv
```

### Configure environment variables

Create a `.env` file in this directory:

```bash
cp .env.shadow .env
```

AWS credentials must also be configured (e.g. via `awsume`).

## Usage

Place a list of UPCs (one per line, no comma) at `inputs/upcs.txt`, then run:

```bash
python backfill_video_thumbnails.py [ticket_id] [sql_username]
# e.g.
python backfill_video_thumbnails.py CDAM-1234 jsmith
```

Both arguments are optional and default to `MAINT` and `<FILL_ME_IN>` respectively.

## Outputs

All outputs are written to a timestamped folder under `outputs/` (e.g. `outputs/20260609T120000Z/`).

### `outputs/<timestamp>/{ticket_id}_BACKFILL_video_thumbnails.sql`

Liquibase-formatted SQL with UPDATE statements for all resolved products. Drop this into `database/ows_video/build/changelog/dml/` and open a PR.

```sql
--liquibase formatted sql

--changeset jsmith:CDAM-1234


UPDATE product_video SET thumbnail_path = '12345/67890.0000010.jpg', thumbnail_at_milliseconds = 10000 WHERE release_id = 99999;
UPDATE product_video SET thumbnail_at_milliseconds = 10000 WHERE release_id = 99998;

--rollback SELECT "No Rollback";
```

### `outputs/<timestamp>/video_errors.csv`

All UPCs that could not be resolved, sorted by `ingest_run_datetime`. Columns:

| Column | Description |
|---|---|
| `upc` | The UPC |
| `reason` | Contextual explanation built from both ingest and approval job diagnoses |
| `ingest_workflow_type` | Workflow type of the ingest run (e.g. `workflow_ingest_from_s3`) |
| `ingest_run_datetime` | Datetime of the ingest job |
| `ingest_errors` | Comma-separated error outputs from ingest child jobs. `error_unknown` entries include the value (e.g. `error_unknown: 'NoneType' object has no attribute 'pop'`) |
| `ingest_errored_jobs` | Comma-separated job types whose latest status was ERROR in the ingest run (e.g. `validate_analysis`) |
| `approval_errors` | Comma-separated error outputs from approval workflow child jobs. `error_unknown` entries include the value |
| `approval_errored_jobs` | Comma-separated job types whose latest status was ERROR in the approval workflow |

The `reason` column segments are joined with ` — ` and describe what was found in each workflow. Each segment follows this pattern:

- `{context} failed in {errored_jobs}: {errors}` — a job errored with a known error output
- `{context} failed: {errored_jobs} errored (no error output recorded)` — job reached ERROR status but left no output
- `{context} produced error outputs but no ERROR-status job found: {errors}` — unusual; error outputs exist but no job has ERROR status
- `{context} completed successfully` — workflow finished cleanly
- `{context} did not complete (status: CANCELLED)` — parent job did not reach COMPLETE

Example reason: `no S_ images in video_asset — ingest completed successfully — approval workflow failed in convert_thumbnails_to_tiffs: error_unknown`

## After running

- **Products with generated SQL** → open a DB PR, then re-trigger the approval workflow for each product (see [Missing S2 Image Notion doc](https://app.notion.com/p/Missing-S2-Image-Video-Asset-re-run-approval-workflow-86dfff580d0447bab3ccf27ec5623cc4))
- **`reason` contains `completed successfully` with no approval errors** → re-trigger the approval workflow; no DB change needed (see [Missing S2 Image Notion doc](https://app.notion.com/p/Missing-S2-Image-Video-Asset-re-run-approval-workflow-86dfff580d0447bab3ccf27ec5623cc4))
- **`ingest_errors` contains a validation error** (e.g. `error_cannot_resize_to_standard_resolution`, `error_dual_mono_audio`) → original video needs to be fixed and re-ingested; tell CX to notify VidOps
- **`ingest_errors` or `approval_errors` contains `error_unknown`** → investigation needed; check Sentry for the original exception