# Translation Batch Workflow

This workflow is the production-shaped path for expanding menu translations beyond `cn`, `jp`, and `kr`.

It is designed around the production translation tables that the runtime already reads:

- `translationsMenuCategories`
- `translationsMenuSubCategories`
- `translationsMenuItems`
- `translationsMenuOptions`
- `translationsMenuOptionSets`
- `translationsMenuItemModifications`
- `translationsVendorData`
- `translationsRequestableItems`

## Goals

- Work vendor by vendor.
- Work one target language at a time.
- Work one entity type at a time.
- Export reviewable JSON instead of writing direct AI output into production.
- Import only reviewed JSON, with dry-run as the default.

## JSON Schema

Each exported file uses this structure:

```json
{
  "metadata": {
    "schemaVersion": "translation-batch-v1",
    "database": "prod",
    "generatedAt": "2026-03-22T10:15:00Z",
    "vendor": {
      "id": "sogo",
      "name": "SOGO",
      "supportedLanguages": ["bg", "cn", "cz", "de"],
      "targetLanguageEnabled": true
    },
    "entityType": "item",
    "targetLanguage": {
      "code": "de",
      "name": "German"
    },
    "exportMode": "todo",
    "chunk": {
      "index": 1,
      "size": 100,
      "totalChunks": 3,
      "totalEntries": 248
    },
    "referenceLanguages": ["cn", "jp", "kr"]
  },
  "entries": [
    {
      "entryId": "item:itemId=344",
      "recordKey": {
        "itemId": 344
      },
      "source": {
        "name": "Marinated Beef",
        "description": "Sweet soy marinade"
      },
      "context": {
        "categoryName": "Grill",
        "subCategoryName": "Beef"
      },
      "referenceTranslations": {
        "cn": {
          "name": "腌制牛肉",
          "description": "甜酱油腌料"
        },
        "jp": {
          "name": "味付け牛肉",
          "description": "甘い醤油ベースの味付け"
        },
        "kr": {
          "name": "양념 소고기",
          "description": "달콤한 간장 양념"
        }
      },
      "existingTargetTranslation": null,
      "translation": {
        "name": null,
        "description": null
      },
      "reviewerNotes": null
    }
  ]
}
```

## Export Rules

- `mode=todo` exports only rows that still need work.
- A row is considered TODO when:
  - the target-language row does not exist, or
  - a required translated field is empty, or
  - the stored tracked source text no longer matches current English source text.
- Tracking is only as precise as the production schema allows.
  - `translationsMenuItems` tracks both `sourceText` and `sourceDescription`.
  - `translationsMenuOptions` only tracks `sourceText`, so stale option descriptions cannot be auto-detected once translated.
  - `translationsVendorData` and `translationsRequestableItems` have no stored source snapshot columns, so only missing fields can be auto-detected there.

## Export Command

Run from [coconut-ai-api](coconut-ai-api):

```powershell
php scripts/translation-batch-export.php --vendor=sogo --entity=item --language=de --chunk=1 --chunk-size=100 --mode=todo --db=prod
```

Useful entity values:

- `category`
- `subCategory`
- `item`
- `optionSet`
- `option`
- `modification`
- `vendorData`
- `requestableItem`

The script writes JSON to `scripts/translation-batches/` unless `--output=...` is provided.

## Review Prompt

Use this exact prompt shape when sending a batch to a translation model or translator:

```text
Translate the JSON batch below into TARGET_LANGUAGE.

Rules:
1. Do not change metadata.
2. Do not change entryId or recordKey.
3. Only fill values under each entry.translation object.
4. Preserve menu meaning and restaurant tone.
5. Keep brand names, dish names, and proper nouns unchanged when they should stay branded.
6. Use referenceTranslations only as context, not as text to copy blindly.
7. If source.description is null, keep translation.description as null.
8. Return valid JSON only.

Batch JSON:
<paste exported file contents here>
```

## Review Checklist

- Check dish names that should remain branded or transliterated.
- Check food vocabulary consistency across the whole vendor.
- Check descriptions for over-translation or invented ingredients.
- Check option labels for terse UI-safe wording.
- Check hashtags and home-button labels for length.
- Leave `reviewerNotes` if a record needs later manual follow-up.

## Import Preview

Dry-run is the default:

```powershell
php scripts/translation-batch-import.php --input=scripts/translation-batches/sogo-item-de-chunk01.json --db=prod
```

## Import Execute

Execution requires `--execute=1`:

```powershell
php scripts/translation-batch-import.php --input=scripts/translation-batches/sogo-item-de-chunk01.json --db=prod --execute=1
```

## Import Behavior

- Inserts missing translation rows.
- Updates existing rows only when translated values or tracked source snapshots changed.
- Uses `timestampTranslationBySystem` for production tables that carry a system timestamp column.
- Leaves `timestampTranslationByVendorHuman` as `NULL` for these batch imports.

## Recommended Rollout Order

For each vendor:

1. `vendorData`
2. `category`
3. `subCategory`
4. `item`
5. `optionSet`
6. `option`
7. `modification`
8. `requestableItem`

Then repeat for the next target language.

## Per-Language Work Files

For active translation work, prefer one clean file per target language instead of a single massive JSON file.

Step 1: extract the whole pending backlog for one vendor into separate per-language files.

```powershell
php scripts/translation-workfile-export.php --vendor=sogo --languages=all --entities=all --db=prod
```

Default output directory:

- `scripts/translation-workfiles/sogo/`

Example files:

- `scripts/translation-workfiles/sogo/sogo-translation-workfile-de.json`
- `scripts/translation-workfiles/sogo/sogo-translation-workfile-fr.json`
- `scripts/translation-workfiles/sogo/sogo-translation-workfile-jp.json`

Each file is designed for translation work in chat:

- it contains only one target language
- each batch keeps the same import-safe schema
- only the `translation` objects should be filled in

If you explicitly want the old single-file export, use:

```powershell
php scripts/translation-workfile-export.php --vendor=sogo --languages=all --entities=all --db=prod --single-file=1
```

Step 2: once the per-language files have been translated, compile them together into one SQL file.

```powershell
php scripts/translation-workfile-to-sql.php --input-dir=scripts/translation-workfiles/sogo --db=prod
```

If the target database already contains translation rows that need correction, force update statements instead of environment-dependent insert/update detection:

```powershell
php scripts/translation-workfile-to-sql.php --input-dir=scripts/translation-workfiles/sogo --db=prod --sql-mode=update
```

Default outputs:

- `scripts/sogo-translations.sql`
- `scripts/sogo-translations-sql-report.json`

This matches the intended workflow:

- extract one file per target language
- translate each language file independently
- generate one SQL file for phpMyAdmin or direct execution
- use `--sql-mode=update` when patching an environment that already has translation rows and you want deterministic `UPDATE` statements