# Handoff: Post-Audit Corrections to Cross-Reference Sheet & KakaoTalk Draft

**Date:** 2026-05-16
**From:** Auditor session
**To:** Executor (next session)
**Priority:** Do before Luis sends message to Daniel

---

## 5 Corrections Required

### 1. WINDING MACHINE FALSE POSITIVE — remove from Flags sheet

The Flags sheet has entries claiming Winding Machines #9, #14, #15, #16 (EXCLUDED/5만 미포함) appear in packages. This is WRONG. The script confused **package sequence numbers** (9-16) with **machine numbers**. There are exactly 8 winding machines in packages = exactly 8 INCLUDED in scope. No excluded machines are arriving.

**Action:** Delete all EXCLUDED_IN_PACKAGES rows for Winding Machines from the Flags sheet.

### 2. FLAT RACK COUNT — fix Container Summary sheet

The script flagged 8 flat rack containers (HWIA-37~44) needing crane. Two corrections:

- **Missing 2 flat racks:** DK-1 and DK-2 from DKTIND-2604-02 (Decoating Machines) are also flat racks. They were missed because container numbers collided with standard containers 1-2 from DKTIND-2604-01. Total flat racks = 10, not 8.
- **Flat racks do NOT need crane.** Forklifts can access flat racks from the sides — they're easier to unload than shipping containers. Remove "crane" designation for all flat rack containers and assign normal forklift class by weight.

**Action:** Update Container Summary — mark DK-1/DK-2 (from file 02) as FR type, change ALL FR containers from "crane" to appropriate forklift class based on heaviest item weight.

### 3. KAKAOTALK DRAFT — rewrite message

The previous draft had 3 questions. After audit, only 1 question survives:

**New draft for KakaoTalk to Daniel Lee:**

```
Daniel 안녕하세요,

Package list 6개 파일 vs master schedule cross-reference 완료했습니다.

Summary:
- 173 items, 55 containers, 323.6T gross
- 6 working days to unload (10 containers/day, 2 forklifts)
- 10 flat rack containers (easy unload)

Google Sheet:
https://docs.google.com/spreadsheets/d/1Tvt3fAZdwR7DAhBmnJ3AbzWdUJi2wagcG2P4xLQvgUQ/edit

3 tabs: Cross-Reference, Container Summary, Flags

One question:
컨베어 外 (메인) — master schedule shows 125T but only ~6T matched in these 6 packing lists. Is structural steel shipping separately or in a different shipment?

Unload day assignments are on the Container Summary tab.

감사합니다
```

### 4. VESSEL / SAILING DATES — add to Container Summary sheet

Sailing dates are in each xlsx Invoice sheet at R15/C3. Add a "Vessel" and "Sailing Date" column to Container Summary so Daniel can sequence unloading by arrival order.

| File | Vessel | Sailing Date | Est. Arrival (25-35d transit) |
|---|---|---|---|
| DKTIND-2604-01 | BUENAVENTURA EXPRESS 2614E | Apr 14, 2026 | ~May 14-19 |
| DKTIND-2604-02 | HYUNDAI COURAGE 0123E | Apr 20, 2026 | ~May 20-25 |
| HWIA-EM260408-01 | CMA CGM THAMES 1WU0SE1MA | Apr 14, 2026 | ~May 14-19 |
| HWIA-EM260408-02 | MSC CALIDRIS III QM615A | Apr 16, 2026 | ~May 16-21 |
| HWIA-EM260413-03 | HYUNDAI COURAGE 0123E | Apr 20, 2026 | ~May 20-25 |
| HWIA-EM260413-04 | LIMA EXPRESS 2615E | Apr 21, 2026 | ~May 21-26 |

**Action:** Add Vessel, Sailing Date, and Est. Arrival columns to Container Summary. Assign each container its vessel info based on source file. Re-sort unload days by estimated arrival order (earliest vessel first). DKTIND-01 and HWIA-01 containers (Apr 14 sailing, ~May 19-20 arrival) get Day 1-2 priority.

**Data location in xlsx:** Invoice sheet, R15/C1 = vessel name, R15/C3 = sailing date. All files follow same layout.

### 5. PRODUCTION LINE COLUMN — add to Cross-Reference AND Container Summary

Add a "Line" column to both sheets. All current containers are **Motor Line** (confirmed via Maquinas sheet cross-reference):

| Vendor (source file) | Production Line |
|---|---|
| DKTEC INDUSTRY (DKTIND-*) | Motor Line (Winding) |
| Hyundai WIA (HWIA-*) | Motor Line (Post-treatment) |
| TBD (future shipment) | Case Line — arrives Jun 22-Jul 1 |
| TBD (future shipment) | Assembly Line — arrives Jul 4-8 |

**Action on Cross-Reference sheet:** Add "Line" column = "Motor Line" for all 64 items.

**Action on Container Summary sheet:** Add "Line" column:
- All DK-* containers → Motor Line (Winding)
- All HWIA-* containers → Motor Line (Post-treatment)

This enables filtering/grouping by production line for unload planning and staging.

**Also add to Container Summary:** "Est. Arrival" column using vessel sailing dates + 30-day transit:

| Containers | Source File | Sailing | Est. Arrival |
|---|---|---|---|
| DK-1 through DK-11 | DKTIND-2604-01 | Apr 14 | ~May 14-19 |
| DK-1, DK-2 (FR) | DKTIND-2604-02 | Apr 20 | ~May 20-25 |
| HWIA-1 through HWIA-4 | HWIA-EM260408-01 | Apr 14 | ~May 14-19 |
| HWIA-5 through HWIA-36 | HWIA-EM260408-02 | Apr 16 | ~May 16-21 |
| HWIA-37, 38, 43 | HWIA-EM260413-03 | Apr 20 | ~May 20-25 |
| HWIA-39 through HWIA-44 | HWIA-EM260413-04 | Apr 21 | ~May 21-26 |

Re-sort unload days: earliest arrival first, Motor Line (DK) containers Day 1-2 priority.

---

## Files to modify

| File | Action |
|---|---|
| Google Sheet `1Tvt3fAZdwR7DAhBmnJ3AbzWdUJi2wagcG2P4xLQvgUQ` — Flags tab | Delete EXCLUDED_IN_PACKAGES winding machine rows |
| Google Sheet — Container Summary tab | Fix DK-1/DK-2 type to FR; change all FR forklift class from "crane" to weight-based |
| `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/cross_reference.py` | Fix flat rack detection to check DKTIND-02 file separately; remove crane override for FR |

---

## Phase 2: Installation Tracking DB + MinIO Photos

**When:** After cross-reference ships to Daniel. Before first containers arrive (~May 19).
**Why:** Turn one-time cross-reference into live installation tracker with photo evidence.

### Schema (SQLite — `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/tmed2_install.db`)

```sql
-- All 3 lines from Maquinas sheet (Motor 64 + Case 15 + Assembly 18 = 97 items)
machines (
  id INTEGER PRIMARY KEY,
  line TEXT NOT NULL,           -- 'Motor Line', 'Case Line', 'Assembly Line'
  line_sub TEXT,                -- 'Winding', 'Post-treatment', 'Motor Assy', 'B-Line CNC', etc.
  korean_name TEXT NOT NULL,
  english_name TEXT NOT NULL,
  item_no TEXT,
  weight_net_t REAL,
  weight_gross_t REAL,
  dims_lxwxh TEXT,
  container_id INTEGER REFERENCES containers(id),
  supplier TEXT,
  arrival_group TEXT,           -- 'May 20', 'Jun 15', 'Jun 22', 'Jul 4', etc.
  -- Status tracking
  received BOOLEAN DEFAULT 0,
  installed BOOLEAN DEFAULT 0,
  electrical BOOLEAN DEFAULT 0,
  air BOOLEAN DEFAULT 0,
  commissioned BOOLEAN DEFAULT 0,
  received_date TEXT,
  installed_date TEXT,
  commissioned_date TEXT,
  notes TEXT
);

containers (
  id INTEGER PRIMARY KEY,
  supplier_prefix TEXT NOT NULL, -- 'DK', 'HWIA'
  container_num TEXT NOT NULL,
  container_type TEXT,           -- '40HQ', '40FR', '20FR'
  line TEXT,
  vessel TEXT,
  sailing_date TEXT,
  est_arrival TEXT,
  forklift_class TEXT,
  unload_day INTEGER,
  unloaded BOOLEAN DEFAULT 0,
  unload_date TEXT
);

machine_photos (
  id INTEGER PRIMARY KEY,
  machine_id INTEGER REFERENCES machines(id),
  photo_type TEXT NOT NULL,      -- 'packing_before', 'packing_after', 'site_install', 'commissioned'
  minio_bucket TEXT DEFAULT 'tmed2-photos',
  minio_key TEXT NOT NULL,
  uploaded_at TEXT DEFAULT (datetime('now'))
);
```

### Data load plan

1. **Motor Line (now):** Load from cross_reference.py JSON output — 64 machines + 55 containers. Already structured.
2. **Packing photos:** Extract from xlsx "Machine PHOTO" sheets (DKTIND has 1 sheet, HWIA has 1 per file). Upload to MinIO `tmed2-photos` bucket with key `packing/{machine_id}_{filename}`.
3. **Case + Assembly (later):** Pre-load from Maquinas sheet (15 + 18 items) with arrival_group and empty container refs. Fill when packing lists arrive.

### Photo extraction from xlsx

DKTIND Machine Photo sheet has photos in rows with Korean/English captions. HWIA Machine PHOTO sheets have bilingual captions like "Bobbin alignment(보빈정렬)". Extract embedded images via openpyxl or `zipfile` (xlsx = zip with `xl/media/` folder), link by caption→machine_id.

### Reporting queries

```sql
-- Daily progress for Daniel/Park
SELECT line, count(*) total,
  sum(received) received, sum(installed) installed,
  sum(electrical) electrical, sum(commissioned) commissioned
FROM machines GROUP BY line;

-- Containers not yet unloaded, ordered by arrival
SELECT * FROM containers WHERE unloaded=0 ORDER BY est_arrival;

-- Machines with photos vs without
SELECT m.english_name, count(p.id) photo_count
FROM machines m LEFT JOIN machine_photos p ON m.id=p.machine_id
GROUP BY m.id;
```

### MinIO bucket
- Bucket: `tmed2-photos`
- Key pattern: `{photo_type}/{machine_id}_{timestamp}.jpg`
- MCP already available for upload/download

### Source references
- Maquinas sheet (all 3 lines): `1m8qOZIpG9T86S_pOvwihAMyE1yyJlkGgETgA-YUi3ZA`
- Cross-reference output sheet: `1Tvt3fAZdwR7DAhBmnJ3AbzWdUJi2wagcG2P4xLQvgUQ`
- Cross-reference script JSON: output of `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/cross_reference.py`
- Package list xlsx (packing photos): `/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/package_lists/*.xlsx`

---

## Script location
`/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/cross_reference.py`

## Sheet ID
`1Tvt3fAZdwR7DAhBmnJ3AbzWdUJi2wagcG2P4xLQvgUQ`