Crafting Directory — DB2 Item Level Sync + Reagent UI Redesign

Notion Task: Crafting Directory — Reagent UI Redesign + DB2 Item Level Sync
Story Points: 8
Epic: Guild Crafting

Overview

Two-part task:

• Part A: Backend sync — pull crafting quality item level breakpoints and reagent-item mappings from Wago DB2 CSVs
• Part B: Frontend UI restructure — merge all reagents into one unified icon-card section (depends on Part A for icon data)

---

Part A — DB2 Item Level Modifier Sync (Backend)

Problem

Crafting quality tiers (Rank 1-5 item levels) are managed via a manual admin table (CraftingQualityTier in Prisma). This data exists authoritatively in Blizzard's DB2 game client tables. Additionally, the ModifiedCraftingReagentItem table provides item-to-slot-type mappings needed by Part B for icon resolution.

DB2 Table Chain

All available as CSVs from https://wago.tools/db2/{TableName}/csv.

The item level breakpoints for crafting qualities are encoded across 5 linked tables:

1. ModifiedCraftingReagentItem (69 rows)

Column	Description
ID	Primary key
Description_lang	Human-readable name (e.g. "Spark of Midnight")
ModifiedCraftingCategoryID	FK → ModifiedCraftingCategory (groups like "Optional Reagents")
ItemBonusTreeID	FK → ItemBonusTree — the key link to item levels
Flags	Bitflags
ItemContextOffset	Context offset value

This table also provides the item-to-slot-type mapping needed for Part B icon resolution.

2. ItemBonusTree (2,820 rows)

Column	Description
ID	Primary key
InventoryTypeMask	Bitmask for applicable inventory slots

Links to ItemBonusTreeNode via ParentItemBonusTreeID.

3. ItemBonusTreeNode (8,708 rows)

Column	Description
ID	Primary key
ItemContext	Context identifier
ChildItemBonusTreeID	Recursive tree link
ChildItemBonusListID	FK → ItemBonusList
ChildItemLevelSelectorID	FK → ItemLevelSelector — this is the goal
ItemBonusListGroupID	FK → ItemBonusListGroup
ParentItemBonusTreeID	FK → ItemBonusTree (parent)

4. ItemLevelSelector (1,148 rows)

Column	Description
ID	Primary key
MinItemLevel	Base item level for this selector tier
ItemLevelSelectorQualitySetID	FK → quality offset table

5. ItemLevelSelectorQualitySet (20 rows)

Column	Description
ID	Primary key
IlvlRare	Item level offset for rare-quality crafts
IlvlEpic	Item level offset for epic-quality crafts

6. ItemLevelSelectorQuality (32 rows)

Column	Description
ID	Primary key
Quality	Craft quality rank (1-5)
QualityItemBonusListID	FK → ItemBonusList (the actual bonus IDs)
ParentILSQualitySetID	FK → ItemLevelSelectorQualitySet

How to derive item levels per quality rank

ModifiedCraftingReagentItem.ItemBonusTreeID
  → ItemBonusTreeNode (where ParentItemBonusTreeID = that ID)
    → ChildItemLevelSelectorID
      → ItemLevelSelector.MinItemLevel (base ilvl)
      → ItemLevelSelector.ItemLevelSelectorQualitySetID
        → ItemLevelSelectorQualitySet.IlvlRare / IlvlEpic
        → ItemLevelSelectorQuality (Quality 1-5 → bonus list IDs)

Implementation Plan

1. New GDL migration (019_crafting_item_level_selectors.sql)

CREATE TABLE IF NOT EXISTS wow_modified_crafting_reagent_item (
    id INT PRIMARY KEY,
    description TEXT NOT NULL DEFAULT '',
    modified_crafting_category_id INT NOT NULL,
    item_bonus_tree_id INT NOT NULL,
    flags INT NOT NULL DEFAULT 0,
    item_context_offset INT NOT NULL DEFAULT 0,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wow_item_bonus_tree_node (
    id INT PRIMARY KEY,
    item_context SMALLINT NOT NULL DEFAULT 0,
    child_item_bonus_tree_id INT NOT NULL DEFAULT 0,
    child_item_bonus_list_id INT NOT NULL DEFAULT 0,
    child_item_level_selector_id INT NOT NULL DEFAULT 0,
    item_bonus_list_group_id INT NOT NULL DEFAULT 0,
    parent_item_bonus_tree_id INT NOT NULL DEFAULT 0,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wow_item_level_selector (
    id INT PRIMARY KEY,
    min_item_level SMALLINT NOT NULL,
    quality_set_id INT NOT NULL,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wow_item_level_selector_quality_set (
    id INT PRIMARY KEY,
    ilvl_rare SMALLINT NOT NULL DEFAULT 0,
    ilvl_epic SMALLINT NOT NULL DEFAULT 0,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wow_item_level_selector_quality (
    id INT PRIMARY KEY,
    quality SMALLINT NOT NULL,
    quality_item_bonus_list_id INT NOT NULL,
    parent_quality_set_id INT NOT NULL,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bonus_tree_node_parent
    ON wow_item_bonus_tree_node(parent_item_bonus_tree_id);
CREATE INDEX IF NOT EXISTS idx_bonus_tree_node_ilvl_selector
    ON wow_item_bonus_tree_node(child_item_level_selector_id) WHERE child_item_level_selector_id > 0;
CREATE INDEX IF NOT EXISTS idx_ilvl_selector_quality_set
    ON wow_item_level_selector(quality_set_id);
CREATE INDEX IF NOT EXISTS idx_ilvl_selector_quality_parent
    ON wow_item_level_selector_quality(parent_quality_set_id);

2. New sync function (crafting_item_levels.rs)

• Download 5 CSVs: ModifiedCraftingReagentItem, ItemBonusTreeNode, ItemLevelSelector, ItemLevelSelectorQualitySet, ItemLevelSelectorQuality
• Parse and bulk upsert into the new tables
• Follow the pattern in crafting_slots.rs (fetch_csv, parse_csv_line helpers)
• Register as wow.crafting_item_levels data category
• Wire into adapter.rs, job.rs, sync_service.rs

3. Query function for recipe item level tiers

Given a recipe's modified_crafting_slots:

SELECT
    ils.min_item_level,
    ilsqs.ilvl_rare,
    ilsqs.ilvl_epic,
    ilsq.quality
FROM wow_modified_crafting_reagent_item mcri
JOIN wow_item_bonus_tree_node ibtn
    ON ibtn.parent_item_bonus_tree_id = mcri.item_bonus_tree_id
    AND ibtn.child_item_level_selector_id > 0
JOIN wow_item_level_selector ils
    ON ils.id = ibtn.child_item_level_selector_id
JOIN wow_item_level_selector_quality_set ilsqs
    ON ilsqs.id = ils.quality_set_id
JOIN wow_item_level_selector_quality ilsq
    ON ilsq.parent_quality_set_id = ilsqs.id
WHERE mcri.id = $1  -- slot type / reagent item ID
ORDER BY ilsq.quality;

4. Update recipe detail API

• Include quality tier item levels derived from DB2 data in RecipeDetailDto
• Fall back to admin CraftingQualityTier table if DB2 data not synced
• Structure: qualityTiers: [{ rank: 1, itemLevel: 623 }, { rank: 2, itemLevel: 626 }, ...]

5. Admin sync button

• Add "Crafting Item Levels" button to admin panel
• RTK Query mutations, WebSocket hook, progress component, i18n keys (7 locales)
• Same pattern as the Crafting Slots button

6. Deprecation path for manual CraftingQualityTier

• Once DB2 sync is reliable, the admin table becomes fallback/override only
• Keep the admin UI but mark as "override" — DB2 values take precedence unless manually overridden
• This avoids needing to manually update numbers each season

---

Part B — Reagent UI Redesign (Frontend)

Depends on Part A — needs ModifiedCraftingReagentItem data for icon resolution.

Problem

The current recipe detail view splits reagents into three separate visual groups:

1. Provided Reagents — shown as small Chip components (e.g. "Alchemy Combined Reagent x4")
2. Required Reagents — shown as cards with icons, name, and quantity (e.g. Mote of Primal Energy x6)
3. Finishing Reagents — shown as Chips

This split reflects the crafting order interface (crafter vs requester-provided), but this page is a directory/lookup view, not the actual crafting UI. Users just want to see "what does this recipe need?" All material reagents should be unified.

Reference Screenshot

Haranir Preserving Agents: "Provided Reagents" as green/yellow Chips vs "Required Reagents" as icon cards. These should be merged.

Required Changes

1. Merge all material reagents into a single "Reagents" section

• Combine detail.craftingOrderSlots where reagentType === REAGENT_TYPE_PROVIDED (type 1) with detail.reagents into one flat list.
• Remove the separate "Provided Reagents" and "Required Reagents" headings.
• Use a single heading: "Reagents" (t('crafting.reagentsTitle')).

2. Unify the visual layout — all reagents as icon cards

• Currently provided reagents render as HeroUI Chip components (no icons, just text).
• Required reagents render as cards with icon, name, and quantity.
• ALL material reagents should use the card layout: icon on left, name in middle, quantity on right.
• The card layout is already implemented for required reagents (lines ~756-790 in CraftingDirectoryPage.tsx). Extend this pattern to provided slots.

3. Resolve icon data for provided reagent slots

CraftingOrderSlot currently has:

interface CraftingOrderSlot {
  slotTypeId: number;
  slotTypeName: string;
  reagentType: number;
  reagentCount: number;
  reCraftCount: number;
  displayOrder: number;
}

It does not have an item icon URL. The slot type names (e.g. "Alchemy Combined Reagent") correspond to item names in wow_items.

Backend change needed in profession_recipe_queries.rs (GDL):

• When building crafting_order_slots, join against wow_items to resolve an icon_url for each slot.
• Use the ModifiedCraftingReagentItem table from Part A to map slot types to item IDs.

API/Frontend changes:

• Update CraftingOrderSlotDto in game-data.service.ts to include iconUrl: string | null.
• Update frontend CraftingOrderSlot interface in gameDataApi.ts to include iconUrl.

4. Keep finishing reagents / modifiers as a separate subsection

Finishing reagents (concentration, etc.) and modifier slots (Spark of Midnight, etc.) are NOT recipe ingredients — they're optional enhancements the crafter applies.

• Keep these in a secondary subsection below the unified reagents, labeled "Crafting Modifiers" or "Optional Enhancements".
• These can remain as Chips or become small cards — implementer's choice.
• Modifier types: REAGENT_TYPE_FINISHING (2), modifiers (0), REAGENT_TYPE_SOCKET (3).

5. Wrap provided reagent cards with ItemPopover (tooltips)

• Required reagents already have ItemPopover tooltips.
• Provided reagents should also get tooltips if item data is available.
• Requires icon resolution from step 3 (need the item ID to fetch preview_item data).

Files to modify

File	Change
CraftingDirectoryPage.tsx	Merge reagent sections, unify card layout
profession_recipe_queries.rs	Add icon URL + item ID to crafting_order_slots query
game-data.service.ts	Update DTO mapping for new fields
gameDataApi.ts	Update CraftingOrderSlot interface

---

Acceptance Criteria

Part A

• New GDL migration creates 5 tables for the bonus tree chain
• Sync function downloads and parses 5 Wago DB2 CSVs
• Admin panel has "Crafting Item Levels" sync button with progress
• Recipe detail API returns quality-tier item levels from DB2 data
• Fallback to admin CraftingQualityTier table if DB2 data unavailable

Part B

• All material reagents (required + provided) appear in a single "Reagents" section
• All reagents use the same card layout: icon, name, quantity
• Provided reagent slots show item icons (resolved from wow_items)
• Finishing reagents / modifiers appear in a separate "Crafting Modifiers" subsection
• ItemPopover tooltips work on provided reagent cards

---

Estimated Breakdown

Sub-task	Points
Part A: GDL migration + sync function	3
Part A: Admin button plumbing	1
Part A: GDL query enrichment	2
Part B: Frontend UI restructure	2