i18n: localize DB-backed content strings (shops, events, gacha, achievements)

[Houmgaor]

Background

#188 landed full server-side multi-language support for content served from JSON files:

• Per-session language preference (users.language, !lang command, Session.Lang()/I18n())
• Localized quest JSON (title, description, 6 other text fields) with per-session compilation and (questID, lang)-keyed cache
• Localized scenario JSON (subheader strings, inline text)
• Per-session routing of all 51 s.server.i18n.* call sites in chat/guild/cafe/timer handlers

Gap

Content stored in the database still has a single-language column:

Table	Text columns	Used by
shops / shop_items	item/shop display names	shop browsing
events / event_schedule	event titles, descriptions	event list
gacha / gacha_items / gacha_entries	gacha banner names, item names, descriptions	gacha UI
campaigns / campaign_rewards	campaign names, descriptions	event tent
achievements	achievement names, descriptions	achievement list
tournaments / tournament_cups / tournament_sub_events	tournament name, cup names/descriptions, event subtype labels	tournament UI
diva_prizes	prize names	Diva Defense rewards

These are served to every client in whatever language the data was seeded in (typically Japanese or English depending on the seed file), regardless of the client's Session.Lang().

Proposal

Two options, pick one per table based on how much text each row has:

Option A — sibling column pattern (small, 1-2 text fields)

ALTER TABLE shops
  ADD COLUMN name_jp TEXT,
  ADD COLUMN name_en TEXT,
  ADD COLUMN name_fr TEXT,
  ADD COLUMN name_es TEXT;

Repo method resolves per requested language with the same fallback chain as LocalizedString.Resolve (requested → jp → en → any non-empty).

Pros: simple, no joins.
Cons: adds 3 columns per text field, doesn't scale past ~4 languages.

Option B — translation table pattern (multiple text fields, dynamic language set)

CREATE TABLE shop_translations (
  shop_id INT NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  lang TEXT NOT NULL,
  name TEXT,
  description TEXT,
  PRIMARY KEY (shop_id, lang)
);

Pros: scales to any number of languages without schema churn, single row change per language.
Cons: one extra query (or join) per fetch; slightly more complex seed files.

Shared helper

Regardless of the column layout, add a generic repo helper:

// resolveLocalized picks the best value for the requested language from a
// map of language → text, using the same fallback chain as
// LocalizedString.Resolve.
func resolveLocalized(values map[string]string, lang string) string { ... }

This should mirror LocalizedString.Resolve exactly so the fallback semantics are identical whether the source is JSON or SQL.

Scope of this issue

Don't try to localize every table at once. Suggest starting with the highest-impact player-facing surfaces:

• Shop names (most visible, most frequently seen)
• Gacha banner + item names (existing bug reports Gacha error #175 mention players not understanding gacha content)
• Event titles (campaign tent is prominently featured)

Then in subsequent PRs:

• Achievement names/descriptions
• Tournament cup/sub-event labels
• Diva prize names
• Campaign reward descriptions

Each table can be a self-contained PR sharing the resolver helper.

Non-goals

• Runtime translation (e.g. calling an LLM to translate at request time). Content translations must be authored once and stored, same as quest/scenario JSON.
• Changing the wire protocol. Clients still receive a single Shift-JIS string; the server just picks the right one per session. Same encoding constraints as Server-side multiple language support #188 apply (ASCII + kana + CJK on the wire).
• World-wide broadcasts (e.g. BroadcastRaviente). These have no session context and stay on the server default.

Related

• Server-side multiple language support #188 — JSON content localization (done)
• i18n: split language strings into per-language files and open JSON discussion #185 — per-language i18n files for static server strings (done)

[Houmgaor]

Needs discussion, but it may be an occasion to move those areas out of the database.

They are all data that almost never changes (or once in a while), so may not worth using postgres features. What server admins need is plug-and-play, so a file-based system can be an alternative (same idea as #160).