coa-server-manager/docs/PORTABLE_IMPORT.md

# Offline import into a stopped realm (Phase 3)

Code: `crates/coa-core/src/portable/realm/{sqlenc,plan,import}.rs`, journal in `portable/store/journal.rs`.

This is the **offline** importer: it writes a canonical snapshot into a **stopped** realm's characters database. It proves
the model end to end and serves realms that are stopped. It is **not** the production path for a running public host:
that is the in-core `PortableImportService` ([`PORTABLE_IMPORT_SERVICE.md`](PORTABLE_IMPORT_SERVICE.md)), which is designed but
not implemented.

## Flow

```text
preflight (read-only snapshot)  ->  journal "prepared"  ->  ONE realm transaction  ->  mappings + journal "committed"
                                                              (marker inside it)         (ONE local transaction)
```

1. **Preflight** (`preflight`): a read-only consistent snapshot of the realm. Refuses (`ImportRefused`, nothing written,
   no journal entry) when the character belongs to another ruleset (CoA and Wildcard never mix), an id is of a namespace
   the importer does not apply, the realm is running (a session of the game server's database user exists), characters are
   marked online, the account does not exist / is a bot or Manager account / is full, the realm has no start position for the
   race and class, or `item_template` / `creature_template` do not know an entry the character carries (the realm would
   delete such items on load).
2. **Journal**: `begin_import` writes an `import_journal` row `prepared` (nonce, the plan: portable ids, entries,
   identities) *before* anything happens in the realm.
3. **Realm transaction**: one `mysql` session, one `START TRANSACTION ... COMMIT`, generated by `plan.rs`.
4. **Local commit**: `finish_import` records the realm binding, the item mappings and the journal state in **one** local
   transaction. Manager mappings exist only after the realm has committed.

No driver is added; everything goes through the existing `Db` abstraction.

## The typed encoder (`sqlenc.rs`)

Untrusted strings and blobs (names, item texts, pet names, settings, macro blobs) can only be `Val::Text` / `Val::Bytes` and
are rendered **only** as hex literals (`_utf8mb4 0x...` / `0x...`). Nothing is ever escaped or interpolated: a quote,
backslash, NUL, `;` or `--` in any input cannot reach the SQL text. Numbers come from typed integers; the only free-form SQL
is `Val::Expr(&'static str)`, i.e. literals in this crate's source. Tests prove this by building the script for a hostile
character and a quiet one and checking that the scaffolds are identical once hex literals are removed, and on a real MySQL by
importing hostile text and reading it back byte for byte.

## What the transaction does

* takes the named lock `coa_portable_import`; asserts the realm is stopped (no online character, no session of the
  game server's database user), the account exists and is not internal, and has room;
* allocates `@char`, `@item_base`, `@pet_base` **inside the transaction from the realm's own tables**: above the highest
  character guid, above every item guid that *anything* still names (inventory, mail, auction, guild bank, gifts, refund and
  trade data, bank, loot, ...), above every pet number;
* remaps every reference through them: item *i* of the plan is `@item_base + i` in the item row, the inventory row and as a
  bag's contents' `bag`; pet *i* is `@pet_base + i`; no guid of the source realm or of the snapshot is ever written
  (crafter guids are zeroed);
* reads the **start position from the realm's own `playercreateinfo`** (map, zone, position, homebind);
* checks the name inside the transaction (case/accent-insensitively against characters and `reserved_name`); a taken or
  reserved name becomes `<name><HEX(guid)>` with `AT_LOGIN_RENAME`, as the core's `.pdump load` does;
* deletes **leftovers of a deleted character that had this guid** from a curated list of per-character tables (the realm's
  own delete does not clean module tables);
* inserts the character and, for the approved v1 scope, its inventory/items/gifts, spells, skills, glyphs, reputation, quests,
  action bars, pets (+ spells, declined names), carried settings and macros;
* **asserts its own result** (`DO IF(count = expected, 1, <error>)` for every table, every inventory row resolves to an item of
  this character, every bag to an inventory row of this character); a failed assertion is an SQL error, so there is no
  `COMMIT` and everything rolls back;
* writes the **marker** `character_settings (@char, 'coa.portable.import', '<nonce words> <revision> ')` in the same
  transaction, then commits and reports `@char`, the bases and the final name.

A fresh arrival is not online, has the intro marked as seen, no first-login flag, full health, mana and energy (the realm
clamps to the maximum).

### What is not applied

* **Stock talents (`character_talent`) are never written.** Found with a real worldserver: a row naming a spell that is not a
  talent of the realm's DBC makes `Player::_LoadTalents` `ASSERT` and **kills the whole worldserver**; nothing in the
  database can tell whether a spell id is a talent. CoA keeps talents as spells and builds in `character_settings`; real CoA
  characters have no `character_talent` rows. The rows stay in the snapshot and are reported as not applied.
* Settings outside the carry list (policy v2) and all extensions stay in the snapshot only.
* Equipment sets, per-character wardrobe, personal bank, collections: deferred by decision.

## Crash recovery (the gap between the two commits)

The realm and the local store cannot share a transaction, so the **marker** (inside the realm transaction) decides what
happened:

| Crash point | What exists | Recovery (`recover_imports`) |
|---|---|---|
| after `begin_import`, before the realm commit | journal `prepared`, no marker | marker absent -> `aborted` (after a grace period and after taking the import lock, so an orphaned client process that is still finishing the import is waited for) |
| after the realm commit, before `finish_import` | journal `prepared`, marker present | marker present -> item/pet bases and the guid are read from the realm, counts and contiguity are checked against the journal's plan, then `finish_import` records the mappings -> `committed` |
| realm does not match the plan | marker present, rows missing | `needs_attention`; **no mapping is recorded on a guess** |
| a lost answer (error after commit) | same as above | `import_character` itself resolves through the marker before reporting |

`finish_import` is idempotent. The marker stays in the realm after a successful import; the export policy drops
`coa.portable.*` so it is never carried.

## Verified against a real server

On two disposable MySQL servers (source A with synthetic characters, destination B with the real CoA world data) and a
**real `worldserver.exe`** started on B: the six characters were imported while the realm was stopped; the server then started
(`Loaded Character Infos For 39 Characters`), four of them were logged in through the real `HandlePlayerLoginFromDB` /
`Player::LoadFromDB` (`botcmd spawnbot`), saved, and read back.

Findings, all expected consequences of the realm's own rules, none of them import bugs:

* the two characters imported under a temporary name (`<name><HEX>`, rename-at-login) cannot log in without a client: the
  realm demands the rename first;
* on first login the realm **adds** defaults (class spells and skills, taxi mask, default reputations, `core.ascension_*`
  settings), **resets** daily honor and an unknown chosen title, **removes** what its DBC does not know (action buttons and
  spells of the synthetic fixture), and **mails** items it cannot place (31 of 60 synthetic items, whose entries do not fit the
  slots they were put in);
* the import marker survives the realm's save.

These are exactly the changes Phase 4's reconciliation has to treat as the realm's own, not as the player's.

A real client login (character select, entering the world) was **not** performed: no game client is available to the
automated tests.

## Tests

* `realm/import_tests.rs` (no database): one transaction, hex-only text, remapping, counts and assertions, settings policy,
  marker, guards, name logic, orphan cleanup list, refusals, the report parser.
* `store` tests: journal begin/finish/abort/flag, idempotence, rollback of a failing local step.
* `realm/live_import.rs` (`#[ignore]`, two real MySQL servers): a trip through a second realm for six characters with
  read-back equality; GUID remapping and orphan cleanup; failure injected in the middle (all tables' row counts unchanged,
  nothing mapped, retry works); every refusal; crash after the realm commit / before it / lock held / realm tampered;
  hostile text; the realm's own start data. Steps `server_check_*` drive the real worldserver.