hm_ai_qc_report_tool/README.md

# Unified HM QC Platform

**Version:** 2.5.0
**Status:** Production (Deployed)
**Deployed at:** https://ai-sandbox.oliver.solutions/hm-ai-qc-report

A comprehensive quality control platform for H&M marketing assets with AI-powered validation, video matching, and consolidated reporting.

---

## Overview

The platform integrates seven tools into a single web application:

1. **Reporting** - Consolidated QC reports from Box.com with search history
2. **HM QC** - AI-powered image quality control (text legibility, language, quality, pricing)
3. **Video QC** - AI-powered video quality control (direct video analysis via Gemini)
4. **Video Master Adot** - Campaign-based master-to-adaptation video matching via Box
5. **Printer Check** - CSV-to-PDF cross-referencing for print order validation
6. **Campaigns** - Campaign presentation and media plan management for QC reference
7. **Usage Dashboard** - API usage tracking, token counts, and cost estimates

### Key Features

- Unified tabbed interface with H&M branding
- Local username/password authentication
- Multi-provider AI: OpenAI GPT-4o and Google Gemini 2.5 Flash
- Google Gemini direct video analysis (no frame extraction needed)
- Campaign presentation upload for guideline-based QC validation
- **Pricing references library** — standalone uploadable docs (PDF or multi-sheet Excel Mastersheet), per-run selectable, independent of any campaign
- **Deterministic price matching** — Excel `MPC Prices` sheet parsed without LLM into structured per-locale lookup (format + actual prices)
- **Video Master 3-pass matching** (v2.5.0) — same-duration masters tried first, then strictly longer masters, with Gemini AI Vision as a same-duration/different-resolution fallback for crops; version-aware (only the highest `V<n>` per file group is matched)
- **Batch processing for both HM QC and Video QC** — multi-file upload, sequential processing, batch results page, collapsible batch groupings on index
- Batch naming by job number (entered at upload time)
- Consolidated report generation: select multiple reports and download a combined HTML
- Asset thumbnails in report listings and embedded in HTML reports (base64)
- Sequential batch processing with memory-safe garbage collection
- Real-time progress tracking (SSE + polling)
- Docker deployment with Apache reverse proxy
- Usage tracking with estimated costs per API call

---

## Deployment

### Docker (Production)

```bash
# Clone from Bitbucket
git clone git@bitbucket.org:zlalani/hm_ai_qc_report_tool.git /opt/hm-qc-app
cd /opt/hm-qc-app

# Configure environment
cp .env.example .env
# Edit .env with production values (see .env.example)
# Generate password: python3 deploy/generate_password.py

# Build and start
docker compose build
docker compose up -d

# Create database tables
docker exec hm-qc-app python3 -c "from app import app; from core.models.database import db; app.app_context().push(); db.create_all(); print('Tables created')"
```

The app runs on `127.0.0.1:5050` inside Docker. Configure Apache or Nginx as reverse proxy — see `deploy/` for config snippets.

### Common Commands

```bash
docker compose logs -f              # Tail logs
docker compose restart              # Quick restart
docker compose down && docker compose up -d --build  # Rebuild after code changes
git pull && docker compose down && docker compose up -d --build  # Deploy update
```

---

## Modules

### 1. Reporting

Consolidated QC report search from Box.com and local database.

**Features:**
- Job number search (single or comma-separated for multi-job)
- Async search with real-time progress bar
- Box reports saved locally for instant re-viewing (no re-fetch)
- Previous Box Reports section with View/Delete
- Dashboard with designer-friendly error display
- Export: HTML and CSV (full or errors-only)

**Workflow:** Search job number -> Progress bar -> Dashboard with aggregated results

### 2. HM QC

AI-powered image quality control for marketing assets.

**Profile:** H&M Image Check (3 checks)
- **Filename Parse** (30%) - Flexibly extracts country code, language, dimensions, campaign number from multiple H&M naming conventions (Display, DOOH, OOH, SOME STATIC, Social, POS)
- **Image Quality** (40%) - AI visual assessment with strict text legibility rules; validates against campaign presentation guidelines when available
- **Price/Currency** (30%) - Detects prices via LLM vision, then **deterministic match** against the attached Pricing Reference: currency symbol/format validated via `_format` lookup, actual prices validated via `_prices` lookup (exact numeric compare with 0.005 tolerance). Falls back to LLM-based campaign-sheet comparison only when the reference has no structured prices (e.g. legacy PDF references).

**AI Quality Check evaluates:**
- Text & title legibility (CRITICAL - illegible text = automatic fail)
- Language word validation (avoids false positives like "Rock" = German for skirt)
- Campaign guideline compliance (typography, layout, copy, logo placement)
- Image quality, color, composition
- Logo and branding clarity

**Features:**
- Single and batch file upload (up to 100 files)
- Batch report grouping: reports grouped by upload batch with collapsible sections, batch stats, and "Download All" ZIP
- Batch naming: batches display their job number (from configure step)
- Delete batch: removes all reports, files, and thumbnails in a batch
- Consolidated report: select multiple reports and download a single combined HTML with summary table + embedded individual reports
- Asset thumbnails in report listings and embedded in HTML reports (base64, self-contained)
- Sequential batch processing with gc.collect() between files for stable memory usage
- LLM provider choice: OpenAI GPT-4o or Google Gemini 2.5 Flash
- **Two independent dropdowns at configure time**: Campaign Presentation (for visual guideline checks) and Pricing Reference (for currency/price validation) — pick either, both, or neither
- Previous QC Reports with View/Download/Delete on all pages (index, upload, results)
- HTML report generation with per-check scoring
- Usage tracking (tokens + estimated cost)

**Workflow:** Upload -> Configure (provider + campaign + pricing ref + job number) -> Execute -> Results

### 3. Video QC

AI-powered video quality control with direct video analysis and batch processing.

**Checks:**
- **Visual Quality** (weight 50) - Language consistency + text legibility throughout the video
- **Censorship** (weight 50) - Body coverage compliance (only for `_CEN` market files, skipped otherwise)
- **Price/Currency** (weight 30, new in v2.4.0) - Detects prices across video frames via LLM, deterministic-validates currency + actual price against the attached Pricing Reference. Skipped if no reference attached, locale not parseable from filename, GEN/CEN markets, or no price visible.

Overall score is the weighted mean of non-skipped checks (so skipping any one check falls through cleanly).

**How it works (Google Gemini — default):**
1. Uploads the video file directly to Google Gemini via `genai.upload_file()`
2. Gemini processes the full video with temporal context (motion, transitions, audio)
3. AI analyzes language consistency, text legibility, branding, and prices in a single pass per check
4. Language check includes false-positive prevention (e.g., "Rock" = skirt in German)

**How it works (OpenAI — fallback):**
1. Extracts 1 frame per second from the video
2. Stitches frames into a labeled grid image
3. Sends grid to GPT-4o for analysis (1 API call per check)

**Features:**
- Default: Google Gemini direct video analysis (no frame extraction)
- Fallback: OpenAI GPT-4o frame grid method
- CEN market auto-detection from filename
- **Multi-file batch processing** (new in v2.4.0): upload up to 50 videos, sequential processing with `gc.collect()` between files, batch results page with summary + per-file list + ZIP download
- Previous Video QC Reports grouped by batch on the index page (collapsible sections, same pattern as HM QC)
- Two independent dropdowns at configure: Campaign Presentation and Pricing Reference
- Usage tracking

**Workflow:** Upload video(s) -> Configure (provider + campaign + pricing ref) -> Execute -> Results
- Single file → `/video-qc/results/<session_id>` (single report)
- Multiple files → `/video-qc/results/batch/<session_id>` (batch summary + per-file list)

### 4. Video Master Adot

Campaign-based master-to-adaptation video matching using Box.com integration.

**How it works:**
1. User enters campaign name
2. System searches Box for campaign folder, finds Global Masters and Regional Masters
3. Preview shows: master count, countries, adaptation count
4. Phase 1: Downloads each master temporarily, fingerprints it (~50KB), deletes video
5. Phase 2: Downloads each adaptation temporarily, matches against fingerprints, deletes video
6. Results: per-master adaptation mapping, unmatched items, match rate

**Matching Engine (4-tier cascade):**
- Stage 0: Metadata filtering (80-95% reduction)
- Tier 1: Perceptual hash matching
- Tier 2: AKAZE feature verification
- Tier 3: AI Vision fallback (smart triggering)

**Storage:** Only fingerprints (~50KB/master) stored permanently. Videos deleted after processing.

**Box Folder Structure:**
```
CAMPAIGNS/{campaign_name}/
├── Global Masters/          (various casing)
│   ├── DOOH/
│   ├── DS/
│   ├── OLV/
│   └── ... (video files with MASTER in name)
└── Regional Masters/        (various casing)
    ├── DE/ (country code folders)
    ├── FR/
    └── ...
```

### 5. Printer Check

CSV-to-PDF cross-referencing for print order validation. Ported from the CrossMatch desktop application.

**What it does:**
1. User uploads a CSV order sheet and a ZIP file containing the PDF folder structure
2. Filters CSV rows by selected geographic region and country groups
3. Scans the PDF folder structure (multi-region or country-level layouts)
4. Matches CSV filenames against actual PDF files
5. Reports: matched, missing, and extra files with structural warnings

**Features:**
- Auto-detects CSV delimiter (tab or comma)
- Region and country group selection (EEU, CEU, etc.)
- Campaign detection and filtering from filenames
- Language column normalization (GEN files, KZ/MK locale handling)
- Folder structure validation: misplaced GEN files, duplicate GEN, wrong country folders, files at wrong level
- Results filtering by status (All, Matched, Missing, Extra)
- XLSX export of filtered data
- GEN asset priority: special handling for `Root/GEN` folder validation

**Folder Layouts Supported:**
- **Multi-Region:** `Root/EEU/PL/`, `Root/CEU/DE/`, `Root/GEN/`
- **Country-Level:** `Root/PL/`, `Root/DE/`, `Root/GEN/`

**Workflow:** Select region -> Upload CSV + PDF ZIP -> Process -> View results -> Export XLSX

### 6. Campaigns

Reference data layer consumed by both Image QC and Video QC. Holds two independent things:

#### Campaign Presentations (tied to a campaign_id)
- **PDF** — creative guidelines with typography specs, layout rules, copy text, ratio-specific mockups. Parsed via LlamaParse (text + page images).
- **Excel** — media plan / spec sheet parsed via openpyxl into structured text.
- Multiple documents per campaign are supported and loaded together at QC time.
- Linked to a specific campaign ID (e.g. `1022B`, `1013A`) typed at upload time.

#### Pricing References (standalone library, new in v2.4.0)
Independent uploadable documents — NOT tied to a campaign_id. Users pick one at QC configure time alongside (or instead of) a campaign presentation.

- **Excel Mastersheet** — parsed **deterministically with openpyxl, no LLM**. Looks for:
  - `MPC Prices` sheet → flat list of `{product_id, language, country, price, currency, product_name}` entries (the authoritative source).
  - Regional sheets (AME, CEU, EEU, NEU, SEU, FRN, SHE, GCN, EAS, IN, BR…) → formatted prices per locale column, used to derive currency `symbol`, `position`, `decimal_separator`, `thousands_separator`.
  - Sheets matching `OLD` or `COPY` are skipped. Rows marked `PRICE NOT PRESENT IN REPORT` are skipped.
- **PDF** — falls back to LlamaParse + LLM extraction for currency format metadata only (no `_prices` produced).

Stored shape in `PricingReference.parsed_data_json`:
```json
{
  "_format": {"en-US": {"currency_code":"USD","symbol":"$","position":"before",...}, ...},
  "_prices": [{"product_id":"1334912002","language":"en-US","price":"49.99","currency":"USD",...}, ...]
}
```

#### Workflow
1. Upload campaign presentation PDF for a campaign (e.g., `1013A`).
2. Upload the mastersheet as a Pricing Reference (give it a name, e.g. "1013A Mastersheet").
3. On HM QC or Video QC configure, two independent dropdowns appear: Campaign Presentation and Pricing Reference. Pick either/both/neither.

#### Features
- Multiple documents per campaign (guidelines + media plan)
- Pricing references library — upload multiple, name them, delete independently
- Auto-polling: status badges update in-place when parsing completes
- View parsed content and page images (campaign presentations)
- API endpoints:
  - `/campaigns/api/list` — campaign presentations for dropdown
  - `/campaigns/api/<campaign_id>` — specific campaign with parsed content
  - `/campaigns/api/pricing/list` — pricing references for dropdown
  - `/campaigns/api/pricing/status/<id>` — parse status for polling

#### Backwards compatibility
If `storage/reference/global_pricing.json` exists on first startup after upgrading from ≤v2.3.x and no `PricingReference` rows are present, it is auto-imported as a **"Default (legacy global)"** row so existing installs keep a valid reference attached. Users just pick it from the dropdown.

### 7. Usage Dashboard

API usage tracking across all tools.

**Displays:**
- Summary cards: total API calls, tokens used, estimated cost (USD)
- Breakdowns: by provider, model, tool, user
- Recent API calls table with full details
- Time filters: All Time, 30 Days, 7 Days, Today

**Cost estimates** based on per-model token pricing (GPT-4o, Gemini 2.5 Flash, etc.)

---

## Configuration

### Environment Variables (.env)

```bash
# Authentication
AUTH_USERS=admin:pbkdf2:sha256:600000$$salt$$hash

# Session
SESSION_COOKIE_PATH=/hm-ai-qc-report

# Box
BOX_CONFIG_PATH=config/box_config.json
BOX_REPORT_FOLDER_ID=133295752718
BOX_CAMPAIGNS_FOLDER_ID=156182880490

# Flask
SECRET_KEY=<generate-random-key>
FLASK_ENV=production

# Database (use absolute path for Docker)
DATABASE_URI=sqlite:////app/database/qc_platform.db

# LLM Providers
OPENAI_API_KEY=<your-key>
GOOGLE_API_KEY=<your-key>
```

Note: `$$` in AUTH_USERS hash is required for Docker Compose (escapes `$`).

---

## Architecture

### Tech Stack

- **Backend:** Flask 3.0, SQLAlchemy, Gunicorn (gthread workers)
- **Frontend:** Bootstrap 5, Vanilla JS, Server-Sent Events
- **AI:** OpenAI GPT-4o, Google Gemini 2.5 Flash (via `google-generativeai`)
- **Video:** FFmpeg, OpenCV (AKAZE), Chromaprint
- **Storage:** Box.com (JWT auth), SQLite
- **Deployment:** Docker, Apache reverse proxy

### Directory Structure

```
hm_ai_qc_report_tool/
├── app.py                    # Application factory
├── config.py                 # Configuration
├── Dockerfile                # Docker image
├── docker-compose.yml        # Docker services
├── deploy/                   # Deployment scripts & configs
│
├── core/                     # Shared infrastructure
│   ├── auth/                 # Session-based authentication
│   ├── models/               # Database models (QCReport, UsageLog, CampaignPresentation, PricingReference)
│   ├── services/             # LLM config, Box client
│   └── utils/                # Progress tracker, report parser
│
├── modules/
│   ├── hm_qc/               # HM QC (checks, executor, batch executor, profiles)
│   ├── video_qc/            # Video QC (executor, batch executor, price check)
│   ├── video_master/         # Video Master (matching engine, campaign matcher)
│   ├── printer_check/        # Printer Check (CSV parser, folder scanner, matcher)
│   ├── campaigns/            # Campaign presentations + pricing references library
│   ├── reporting/            # Reporting (aggregator, Box search, cache)
│   └── usage/                # Usage dashboard
│
├── templates/                # Shared templates (base.html, login.html)
├── static/                   # CSS, JavaScript
├── database/                 # SQLite database
└── storage/
    ├── reports/              # QC report HTML files
    ├── campaigns/            # Campaign presentation PDFs + page images
    ├── pricing_references/   # Pricing reference files (per-row dir)
    ├── thumbnails/           # Asset thumbnails
    └── reference/            # Legacy global_pricing.json (auto-imported on upgrade)
```

---

## Security

- Local username/password auth with PBKDF2/scrypt hashing
- Session-based with `before_request` login enforcement
- No hardcoded API keys (all from environment)
- Docker container binds to 127.0.0.1 only (not exposed to internet)
- HTTPS via Apache with wildcard SSL certificate
- httpOnly, Secure, SameSite=Lax cookies

---

## License

Proprietary - H&M Hennes & Mauritz AB