# Handoff: Phase 2 DB Fixes — Photo Linkage + Container Prefix

**Date:** 2026-05-16
**From:** Auditor session
**To:** Next executor
**Priority:** LOW — no data loss, cosmetic/linkage issues only

---

## 2 Issues Found in tmed2_install.db

### 1. Photos orphaned — all 431 have machine_id=NULL

All 431 packing photos were bulk-extracted from xlsx and uploaded to MinIO bucket `tmed2-photos`, but none are linked to a machine record.

**Current state:**
```
machine_photos.machine_id = NULL (all 431 rows)
machine_photos.minio_key = 'packing/DKTIND-2604-01_image1.png' (sequential, no machine info)
```

**Root cause:** The xlsx embeds images in a flat `xl/media/` folder with generic names (image1, image2...). The executor uploaded them without parsing which image belongs to which machine.

**Fix approach:** The "Machine PHOTO" sheets in each xlsx have a grid layout where each cell contains a bilingual caption (e.g., "WINDING MACHINE", "Bobbin alignment(보빈정렬)") and the image is anchored to that cell. The linkage is:

1. Parse each xlsx "Machine PHOTO" sheet for image anchors (openpyxl `ws._images` gives row/col position per image)
2. Read the caption text from the cell near each image anchor
3. Match caption to machine record by english_name or korean_name
4. UPDATE machine_photos SET machine_id = matched_id WHERE minio_key = corresponding_key

**Files:**
- DB: `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/tmed2_install.db`
- Photos in MinIO: bucket `tmed2-photos`, key prefix `packing/`
- Source xlsx: `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/package_lists/*.xlsx`
- Machine PHOTO sheet exists in: DKTIND-2604-01 ("Machine Photo"), all 4 HWIA files ("Machine PHOTO")
- DKTIND-2604-02 has NO Machine Photo sheet

### 2. Container prefix doubled — DK-DK-1 instead of DK-1

`supplier_prefix` column contains "DK" or "HWIA", but `container_num` already includes the prefix ("DK-1", "HWIA-5"). Displaying both produces "DK-DK-1".

**Fix:** Either:
- (a) Strip prefix from `container_num` so it stores just "1", "5", etc. — then combine as `{supplier_prefix}-{container_num}` at query time
- (b) Drop `supplier_prefix` column entirely and derive from `container_num` prefix

Option (a) is cleaner. One UPDATE statement:
```sql
UPDATE containers SET container_num = REPLACE(container_num, supplier_prefix || '-', '');
```

---

## DB location
`/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/tmed2_install.db`

## MinIO
- Host: 192.168.100.251:9000 (direct SDK, not MCP — Docker mount limitation)
- Bucket: `tmed2-photos`
- 431 objects, key pattern: `packing/{SOURCE_FILE}_image{N}.{png|jpeg}`