-- Step 29: Add translations for requestable items
-- translationsRequestableItems is keyed by (requestableItemId, languageCode) — no vendorId since requestableItems are global.

CREATE TABLE `translationsRequestableItems` (
  `requestableItemId` int(10) UNSIGNED NOT NULL,
  `languageCode` varchar(3) NOT NULL,
  `name` varchar(100) NOT NULL,
  UNIQUE KEY `uq_item_lang` (`requestableItemId`, `languageCode`),
  CONSTRAINT `fk_translationsRequestableItems_itemId` FOREIGN KEY (`requestableItemId`) REFERENCES `requestableItems` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Seed translations for all items in cn, jp, kr
-- Uses a JOIN on name so the insert is safe regardless of auto-increment ID values.
INSERT INTO `translationsRequestableItems` (`requestableItemId`, `languageCode`, `name`)
SELECT ri.`id`, t.`lc`, t.`n`
FROM `requestableItems` ri
INNER JOIN (
  SELECT 'Beer refill'           AS item, 'cn' AS lc, '啤酒续杯'             AS n UNION ALL
  SELECT 'Beer refill',                   'jp',        'おかわりビール'             UNION ALL
  SELECT 'Beer refill',                   'kr',        '맥주 리필'                  UNION ALL
  SELECT 'Chili sauce',                   'cn',        '辣椒酱'                     UNION ALL
  SELECT 'Chili sauce',                   'jp',        'チリソース'                 UNION ALL
  SELECT 'Chili sauce',                   'kr',        '칠리 소스'                  UNION ALL
  SELECT 'Chopsticks',                    'cn',        '筷子'                       UNION ALL
  SELECT 'Chopsticks',                    'jp',        'お箸'                       UNION ALL
  SELECT 'Chopsticks',                    'kr',        '젓가락'                     UNION ALL
  SELECT 'Clean table',                   'cn',        '擦桌子'                     UNION ALL
  SELECT 'Clean table',                   'jp',        'テーブルを拭いてください'   UNION ALL
  SELECT 'Clean table',                   'kr',        '테이블 닦아주세요'          UNION ALL
  SELECT 'Clean under the table',         'cn',        '清理桌下'                   UNION ALL
  SELECT 'Clean under the table',         'jp',        '椅子の下を掃除してください' UNION ALL
  SELECT 'Clean under the table',         'kr',        '테이블 아래 청소해주세요'   UNION ALL
  SELECT 'Extra bowl',                    'cn',        '多一个碗'                   UNION ALL
  SELECT 'Extra bowl',                    'jp',        'お椀を追加'                 UNION ALL
  SELECT 'Extra bowl',                    'kr',        '그릇 추가'                  UNION ALL
  SELECT 'Extra cup',                     'cn',        '多一个杯子'                 UNION ALL
  SELECT 'Extra cup',                     'jp',        'カップを追加'               UNION ALL
  SELECT 'Extra cup',                     'kr',        '컵 추가'                    UNION ALL
  SELECT 'Extra glass',                   'cn',        '多一个玻璃杯'               UNION ALL
  SELECT 'Extra glass',                   'jp',        'グラスを追加'               UNION ALL
  SELECT 'Extra glass',                   'kr',        '유리잔 추가'                UNION ALL
  SELECT 'Extra plate',                   'cn',        '多一个盘子'                 UNION ALL
  SELECT 'Extra plate',                   'jp',        'お皿を追加'                 UNION ALL
  SELECT 'Extra plate',                   'kr',        '접시 추가'                  UNION ALL
  SELECT 'Extra small bowl',              'cn',        '多一个小碗'                 UNION ALL
  SELECT 'Extra small bowl',              'jp',        '小鉢を追加'                 UNION ALL
  SELECT 'Extra small bowl',              'kr',        '작은 그릇 추가'             UNION ALL
  SELECT 'Extra small plate',             'cn',        '多一个小盘子'               UNION ALL
  SELECT 'Extra small plate',             'jp',        '小皿を追加'                 UNION ALL
  SELECT 'Extra small plate',             'kr',        '작은 접시 추가'             UNION ALL
  SELECT 'Fork',                          'cn',        '叉子'                       UNION ALL
  SELECT 'Fork',                          'jp',        'フォーク'                   UNION ALL
  SELECT 'Fork',                          'kr',        '포크'                       UNION ALL
  SELECT 'High chair',                    'cn',        '婴儿椅'                     UNION ALL
  SELECT 'High chair',                    'jp',        'ハイチェア'                 UNION ALL
  SELECT 'High chair',                    'kr',        '유아용 의자'                UNION ALL
  SELECT 'Ice',                           'cn',        '冰'                         UNION ALL
  SELECT 'Ice',                           'jp',        '氷'                         UNION ALL
  SELECT 'Ice',                           'kr',        '얼음'                       UNION ALL
  SELECT 'Ketchup',                       'cn',        '番茄酱'                     UNION ALL
  SELECT 'Ketchup',                       'jp',        'ケチャップ'                 UNION ALL
  SELECT 'Ketchup',                       'kr',        '케첩'                       UNION ALL
  SELECT 'Knife',                         'cn',        '刀'                         UNION ALL
  SELECT 'Knife',                         'jp',        'ナイフ'                     UNION ALL
  SELECT 'Knife',                         'kr',        '나이프'                     UNION ALL
  SELECT 'Napkin',                        'cn',        '餐纸'                       UNION ALL
  SELECT 'Napkin',                        'jp',        'ナプキン'                   UNION ALL
  SELECT 'Napkin',                        'kr',        '냅킨'                       UNION ALL
  SELECT 'Pepper',                        'cn',        '胡椒粉'                     UNION ALL
  SELECT 'Pepper',                        'jp',        'コショウ'                   UNION ALL
  SELECT 'Pepper',                        'kr',        '후추'                       UNION ALL
  SELECT 'Salt',                          'cn',        '盐'                         UNION ALL
  SELECT 'Salt',                          'jp',        '塩'                         UNION ALL
  SELECT 'Salt',                          'kr',        '소금'                       UNION ALL
  SELECT 'Soy sauce',                     'cn',        '酱油'                       UNION ALL
  SELECT 'Soy sauce',                     'jp',        '醤油'                       UNION ALL
  SELECT 'Soy sauce',                     'kr',        '간장'                       UNION ALL
  SELECT 'Spoon',                         'cn',        '勺子'                       UNION ALL
  SELECT 'Spoon',                         'jp',        'スプーン'                   UNION ALL
  SELECT 'Spoon',                         'kr',        '숟가락'                     UNION ALL
  SELECT 'Straw',                         'cn',        '吸管'                       UNION ALL
  SELECT 'Straw',                         'jp',        'ストロー'                   UNION ALL
  SELECT 'Straw',                         'kr',        '빨대'                       UNION ALL
  SELECT 'Sugar',                         'cn',        '糖'                         UNION ALL
  SELECT 'Sugar',                         'jp',        '砂糖'                       UNION ALL
  SELECT 'Sugar',                         'kr',        '설탕'                       UNION ALL
  SELECT 'Sugar syrup',                   'cn',        '糖浆'                       UNION ALL
  SELECT 'Sugar syrup',                   'jp',        'シュガーシロップ'           UNION ALL
  SELECT 'Sugar syrup',                   'kr',        '설탕 시럽'                  UNION ALL
  SELECT 'Takeaway box',                  'cn',        '打包盒'                     UNION ALL
  SELECT 'Takeaway box',                  'jp',        'テイクアウトボックス'       UNION ALL
  SELECT 'Takeaway box',                  'kr',        '포장 박스'                  UNION ALL
  SELECT 'Teaspoon',                      'cn',        '茶匙'                       UNION ALL
  SELECT 'Teaspoon',                      'jp',        'ティースプーン'             UNION ALL
  SELECT 'Teaspoon',                      'kr',        '티스푼'                     UNION ALL
  SELECT 'Toothpicks',                    'cn',        '牙签'                       UNION ALL
  SELECT 'Toothpicks',                    'jp',        'つまようじ'                 UNION ALL
  SELECT 'Toothpicks',                    'kr',        '이쑤시개'                   UNION ALL
  SELECT 'Water for dog',                 'cn',        '狗狗用水'                   UNION ALL
  SELECT 'Water for dog',                 'jp',        'ペット用のお水'             UNION ALL
  SELECT 'Water for dog',                 'kr',        '강아지 물'                  UNION ALL
  SELECT 'Wet wipes',                     'cn',        '湿纸巾'                     UNION ALL
  SELECT 'Wet wipes',                     'jp',        'おしぼり'                   UNION ALL
  SELECT 'Wet wipes',                     'kr',        '물티슈'
) t ON t.item = ri.`name`;
