chatbot_shop_items_seed.sql 7.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134
  1. SET NAMES utf8mb4;
  2. -- =========================================================
  3. -- shop_items 数据种子:从《生成商品的初始化的文档提示词.md》同步
  4. -- =========================================================
  5. -- 运行前提:
  6. -- 1) 先执行 `项目文档/chatbot_mysql5_建表.sql`,确保表 `shop_items` 存在
  7. -- 2) 该脚本设计为幂等:
  8. -- - 已存在的 item_code:更新字段
  9. -- - 不存在的 item_code:插入
  10. -- - 本次 seed 不包含的 item_code:is_active 置为 0(下架)
  11. START TRANSACTION;
  12. DROP TEMPORARY TABLE IF EXISTS `tmp_shop_items_seed`;
  13. CREATE TEMPORARY TABLE `tmp_shop_items_seed` (
  14. `item_code` VARCHAR(64) NOT NULL COMMENT '商品编码(业务唯一)',
  15. `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  16. `name_zh` VARCHAR(100) NOT NULL COMMENT '商品中文名称',
  17. `name_en` VARCHAR(100) NOT NULL COMMENT '商品英文名称',
  18. `biz_type` ENUM('seat_sofa','table_cabinet','window','wallpaper','prop') NOT NULL COMMENT '业务分类',
  19. `item_type` ENUM('furniture','clothing','backdrop') NOT NULL COMMENT '商品类型',
  20. `placement_type` ENUM('wall','floor','backdrop','clothing') NOT NULL COMMENT '摆放/使用类型',
  21. `image_name` VARCHAR(255) NOT NULL COMMENT '图片名称',
  22. `size_rows` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用行数(家具用)',
  23. `size_cols` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用列数(家具用)',
  24. `price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '能量石价格(保留2位小数)',
  25. `price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '水晶石价格(保留2位小数)',
  26. `default_z_index` INT NOT NULL DEFAULT 0 COMMENT '默认渲染层级',
  27. `scene_anchor_type` ENUM('none','seat','window') NOT NULL DEFAULT 'none' COMMENT '场景锚点类型',
  28. `has_special_scene_trigger` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否有特殊触发场景(1=有,0=无)',
  29. `sort_order` INT NOT NULL DEFAULT 0 COMMENT '前端展示排序',
  30. PRIMARY KEY (`item_code`)
  31. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  32. INSERT INTO `tmp_shop_items_seed`
  33. (`item_code`,`name`,`name_zh`,`name_en`,`biz_type`,`item_type`,`placement_type`,`image_name`,`size_rows`,`size_cols`,`price_energy`,`price_crystal`,`default_z_index`,`scene_anchor_type`,`has_special_scene_trigger`,`sort_order`)
  34. VALUES
  35. ('stump_stool','小木桩','小木桩','Stump Stool','seat_sofa','furniture','floor','Stump-Stool.png',1,1,0,0.00,10,'seat',0,10),
  36. ('forest_sofa','绿意沙发','绿意沙发','Forest Sofa','seat_sofa','furniture','floor','Forest-Sofa.png',1,1,150,0.00,10,'seat',0,20),
  37. ('vintage_table','复古小桌','复古小桌','Vintage Table','table_cabinet','furniture','floor','Vintage-Table.png',1,2,50,0.00,10,'none',0,30),
  38. ('stump_table','木桩小桌','木桩小桌','Stump Table','table_cabinet','furniture','floor','Stump-Table.png',1,2,150,0.00,10,'none',0,40),
  39. ('mushroom_table','蘑菇长桌','蘑菇长桌','Mushroom Table','table_cabinet','furniture','floor','Mushroom-Table.png',1,2,150,0.00,10,'none',0,50),
  40. ('french_window','落地窗','落地窗','French Window','window','furniture','wall','French-Window.png',2,3,0,0.00,20,'window',0,60),
  41. ('garden_balcony','花园阳台','花园阳台','Garden Balcony','window','furniture','wall','Garden-Balcony.png',2,3,150,0.00,20,'window',0,70),
  42. ('forest_round_window','绿意圆窗','绿意圆窗','Forest Round Window','window','furniture','wall','Forest-Window.png',2,3,150,0.00,20,'window',0,80),
  43. ('terra_cotta_window','红泥窗','红泥窗','Terra Cotta Window','window','furniture','wall','Terra-Cotta-Window.png',2,3,50,0.00,20,'window',0,90),
  44. ('fresh_green_wallpaper','清新绿','清新绿','Fresh Green Wallpaper','wallpaper','backdrop','backdrop','Fresh-Green.png',1,1,0,0.00,0,'none',0,100),
  45. ('midnight_blue_wallpaper','深夜蓝','深夜蓝','Midnight Blue Wallpaper','wallpaper','backdrop','backdrop','Midnight-Blue.png',1,1,50,0.00,0,'none',0,110),
  46. ('springtime_wallpaper','春之意','春之意','Springtime Wallpaper','wallpaper','backdrop','backdrop','Springtime.png',1,1,50,0.00,0,'none',0,120),
  47. ('mocha_wallpaper','摩卡浓情','摩卡浓情','Mocha Wallpaper','wallpaper','backdrop','backdrop','Mocha.png',1,1,50,0.00,0,'none',0,130),
  48. ('coffee_mug','咖啡杯','咖啡杯','Coffee Mug','prop','furniture','floor','Coffee-Mug.png',1,1,0,0.00,10,'none',1,140),
  49. ('snacks','零食','零食','Snacks','prop','furniture','floor','Snacks.png',1,1,0,0.00,10,'none',1,150),
  50. ('books','书','书','Books','prop','furniture','floor','Books.png',1,1,0,0.00,10,'none',1,160),
  51. ('energy_ball','能量球','能量球','Energy Ball','prop','furniture','floor','Energy-Ball.png',1,1,50,0.00,10,'none',0,170),
  52. ('chinese_new_year_bloom','年宵花','年宵花','Chinese New Year Bloom','prop','furniture','floor','Chinese-New-Year-Bloom.png',2,1,50,0.00,10,'none',0,180),
  53. ('photo_frame','普通相框','普通相框','Photo Frame','prop','furniture','wall','Photo-Frame.png',1,1,50,0.00,10,'none',0,190),
  54. ('chocolate_tree','巧克力树','巧克力树','Chocolate Tree','prop','furniture','floor','Chocolate-Tree.png',1,1,50,0.00,10,'none',0,200),
  55. ('lilybell_lamp','铃兰花灯','铃兰花灯','Lilybell Lamp','prop','furniture','floor','Lilybell-Lamp.png',2,1,50,0.00,10,'none',0,210),
  56. ('nut_wind_bell','坚果风铃','坚果风铃','Nut Wind Bell','prop','furniture','wall','Nut-Wind-Bell.png',2,1,50,0.00,10,'none',0,220);
  57. -- 更新已存在商品
  58. UPDATE `shop_items` s
  59. JOIN `tmp_shop_items_seed` t
  60. ON s.`item_code` = t.`item_code`
  61. SET
  62. s.`name` = t.`name`,
  63. s.`name_zh` = t.`name_zh`,
  64. s.`name_en` = t.`name_en`,
  65. s.`biz_type` = t.`biz_type`,
  66. s.`item_type` = t.`item_type`,
  67. s.`placement_type` = t.`placement_type`,
  68. s.`image_name` = t.`image_name`,
  69. s.`size_rows` = t.`size_rows`,
  70. s.`size_cols` = t.`size_cols`,
  71. s.`price_energy` = t.`price_energy`,
  72. s.`price_crystal` = t.`price_crystal`,
  73. s.`default_z_index` = t.`default_z_index`,
  74. s.`scene_anchor_type` = t.`scene_anchor_type`,
  75. s.`has_special_scene_trigger` = t.`has_special_scene_trigger`,
  76. s.`stock_mode` = 'unlimited',
  77. s.`repeat_purchase_mode` = 'single',
  78. s.`max_per_user` = 1,
  79. s.`is_active` = 1,
  80. s.`sort_order` = t.`sort_order`;
  81. -- 插入缺失商品
  82. INSERT INTO `shop_items`
  83. (`item_code`,`name`,`name_zh`,`name_en`,`biz_type`,`item_type`,`placement_type`,`image_name`,`size_rows`,`size_cols`,
  84. `price_energy`,`price_crystal`,`default_z_index`,`scene_anchor_type`,`has_special_scene_trigger`,`stock_mode`,`repeat_purchase_mode`,`max_per_user`,`is_active`,`sort_order`)
  85. SELECT
  86. t.`item_code`,
  87. t.`name`,
  88. t.`name_zh`,
  89. t.`name_en`,
  90. t.`biz_type`,
  91. t.`item_type`,
  92. t.`placement_type`,
  93. t.`image_name`,
  94. t.`size_rows`,
  95. t.`size_cols`,
  96. t.`price_energy`,
  97. t.`price_crystal`,
  98. t.`default_z_index`,
  99. t.`scene_anchor_type`,
  100. t.`has_special_scene_trigger`,
  101. 'unlimited',
  102. 'single',
  103. 1,
  104. 1,
  105. t.`sort_order`
  106. FROM `tmp_shop_items_seed` t
  107. WHERE NOT EXISTS (
  108. SELECT 1
  109. FROM `shop_items` s
  110. WHERE s.`item_code` = t.`item_code`
  111. );
  112. -- 下架非本次 seed 的旧商品
  113. UPDATE `shop_items` s
  114. SET s.`is_active` = 0
  115. WHERE NOT EXISTS (
  116. SELECT 1
  117. FROM `tmp_shop_items_seed` t
  118. WHERE t.`item_code` = s.`item_code`
  119. );
  120. COMMIT;