Tolan/项目文档/chatbot_mysql5_建表.sql

SET NAMES utf8mb4;

-- =========================================================
-- 1) 系统商品：家具/服装/背景幕布(shop_items)
-- =========================================================
DROP TABLE IF EXISTS `shop_items`;
CREATE TABLE `shop_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `item_code` VARCHAR(64) NOT NULL COMMENT '商品编码(业务唯一)',
  `name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  `name_zh` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品中文名称',
  `name_en` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品英文名称',
  `item_type` ENUM('furniture','clothing','backdrop') NOT NULL COMMENT '商品类型',
  `placement_type` ENUM('wall','floor','backdrop','clothing') NOT NULL COMMENT '摆放/使用类型',
  `image_name` VARCHAR(255) NOT NULL COMMENT '列表/卡片展示用图片名称(OSS)',
  `render_image_name` VARCHAR(255) NULL DEFAULT NULL COMMENT '场景网格/幕布渲染用图片(OSS)；为空则同 image_name',

  `size_rows` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用行数(家具用)',
  `size_cols` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '占用列数(家具用)',

  `price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '能量石价格(保留2位小数)',
  `price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '水晶石价格(保留2位小数)',

  `default_z_index` INT NOT NULL DEFAULT 0 COMMENT '默认渲染层级',
  `scene_anchor_type` ENUM('none','seat','window') NOT NULL DEFAULT 'none' COMMENT '场景锚点类型(none=非锚点,seat=凳子/沙发,window=窗户)',
  `has_special_scene_trigger` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否有特殊触发场景(1=有,0=无)',

  `stock_mode` ENUM('unlimited') NOT NULL DEFAULT 'unlimited' COMMENT '库存模式(当前仅无限)',
  `repeat_purchase_mode` ENUM('single','repeatable') NOT NULL DEFAULT 'single' COMMENT '是否可重复购买',
  `max_per_user` INT UNSIGNED DEFAULT NULL COMMENT '每用户最多可购数量(NULL=不限制)',

  `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否上架',
  `sort_order` INT NOT NULL DEFAULT 0 COMMENT '前端展示排序',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统商品表';
CREATE INDEX `idx_shop_items_scene_anchor_type` ON `shop_items` (`scene_anchor_type`);

-- =========================================================
-- 2) 用户资产/背包表(user_assets)
-- =========================================================
DROP TABLE IF EXISTS `user_assets`;
CREATE TABLE `user_assets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `item_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',

  `owned_qty` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '拥有数量',
  `used_qty` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已使用数量(摆放/穿戴等)',
  `last_acquired_at` DATETIME DEFAULT NULL COMMENT '最近获取时间',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户资产表';

-- =========================================================
-- 3) 购物车(购物车主表+明细表)
--    购物车=待购买列表；支持部分结算。
-- =========================================================
DROP TABLE IF EXISTS `shopping_cart_items`;
DROP TABLE IF EXISTS `shopping_carts`;

CREATE TABLE `shopping_carts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车主表';

CREATE TABLE `shopping_cart_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cart_id` BIGINT UNSIGNED NOT NULL COMMENT '购物车ID',
  `item_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',

  `quantity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购物车数量(支持部分结算后递减)',

  `unit_price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '加购时能量石单价快照(保留2位小数)',
  `unit_price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '加购时水晶石单价快照(保留2位小数)',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车明细表';

-- =========================================================
-- 4) 购买订单(A)：purchase_orders + purchase_order_items
--    一次checkout -> 一笔订单
-- =========================================================
DROP TABLE IF EXISTS `purchase_order_items`;
DROP TABLE IF EXISTS `purchase_orders`;

CREATE TABLE `purchase_orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `cart_id` BIGINT UNSIGNED NOT NULL COMMENT '结算来源购物车ID(业务字段)',

  `status` ENUM('created','completed','failed','cancelled') NOT NULL DEFAULT 'created' COMMENT '订单状态',
  `total_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单总数量(便于展示)',
  `total_cost_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '能量石总花费(保留2位小数)',
  `total_cost_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '水晶石总花费(保留2位小数)',

  `completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购买订单主表';

CREATE TABLE `purchase_order_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单ID(业务字段)',
  `item_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',

  `quantity` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量',

  `unit_price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '单价快照-能量石(保留2位小数)',
  `unit_price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '单价快照-水晶石(保留2位小数)',

  `cost_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '花费快照-能量石(保留2位小数)',
  `cost_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '花费快照-水晶石(保留2位小数)',

  `item_name_snapshot` VARCHAR(100) NOT NULL COMMENT '商品名快照',
  `image_name_snapshot` VARCHAR(255) NOT NULL COMMENT '图片名称快照',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购买订单明细表';

-- =========================================================
-- 5) 购买流水/审计(asset_purchase_logs)
-- =========================================================
DROP TABLE IF EXISTS `asset_purchase_logs`;
CREATE TABLE `asset_purchase_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `item_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID',

  `quantity` INT UNSIGNED NOT NULL COMMENT '购买数量',
  `unit_price_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '单价快照-能量石(保留2位小数)',
  `unit_price_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '单价快照-水晶石(保留2位小数)',

  `cost_energy` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '总花费-能量石(保留2位小数)',
  `cost_crystal` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '总花费-水晶石(保留2位小数)',

  `item_name_snapshot` VARCHAR(100) NOT NULL COMMENT '商品名快照',
  `image_name_snapshot` VARCHAR(255) NOT NULL COMMENT '图片名称快照',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户购买流水表';

-- =========================================================
-- 6) /chatbot当前背景幕布(user_chatbot_backdrops)
-- =========================================================
DROP TABLE IF EXISTS `user_chatbot_backdrops`;
CREATE TABLE `user_chatbot_backdrops` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `backdrop_item_id` BIGINT UNSIGNED NOT NULL COMMENT '幕布商品ID(来源shop_items)',

  `z_index` INT NOT NULL DEFAULT 0 COMMENT '渲染层级',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户当前/chatbot背景幕布表';

-- =========================================================
-- 7) /chatbot布局主表(每用户仅保留最新；在业务层覆盖实现)
-- =========================================================
DROP TABLE IF EXISTS `chatbot_layout_items`;
DROP TABLE IF EXISTS `chatbot_layouts`;

CREATE TABLE `chatbot_layouts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',

  `grid_rows` TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '固定：5行',
  `grid_cols` TINYINT UNSIGNED NOT NULL DEFAULT 4 COMMENT '固定：4列',
  `cell_width` SMALLINT UNSIGNED NOT NULL DEFAULT 96 COMMENT '固定：96',
  `cell_height` SMALLINT UNSIGNED NOT NULL DEFAULT 96 COMMENT '固定：96',

  `design_width` SMALLINT UNSIGNED NOT NULL DEFAULT 393 COMMENT '设计宽(393)',
  `design_height` SMALLINT UNSIGNED NOT NULL DEFAULT 852 COMMENT '设计高(852)',
  `decor_area_width` SMALLINT UNSIGNED NOT NULL DEFAULT 393 COMMENT '装饰区宽(393)',
  `decor_area_height` SMALLINT UNSIGNED NOT NULL DEFAULT 482 COMMENT '装饰区高(482)',

  `version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '布局版本(可选)',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='/chatbot布局主表(仅最新)';

-- =========================================================
-- 8) /chatbot布局明细(墙/地家具位置 + z_index)
-- =========================================================
CREATE TABLE `chatbot_layout_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `layout_id` BIGINT UNSIGNED NOT NULL COMMENT '/chatbot布局ID(业务字段)',

  `user_asset_id` BIGINT UNSIGNED NOT NULL COMMENT '用户资产ID(业务字段)',
  `item_id` BIGINT UNSIGNED NOT NULL COMMENT '商品ID(冗余便于查询)',

  `placement_type` ENUM('wall','floor') NOT NULL COMMENT '区域类型：墙/地',

  `start_row` TINYINT UNSIGNED NOT NULL COMMENT '起始行(从1开始)',
  `start_col` TINYINT UNSIGNED NOT NULL COMMENT '起始列(从1开始)',
  `size_rows` TINYINT UNSIGNED NOT NULL COMMENT '占用行数',
  `size_cols` TINYINT UNSIGNED NOT NULL COMMENT '占用列数',

  `z_index` INT NOT NULL DEFAULT 0 COMMENT '渲染层级(用于排序)',
  `image_name_snapshot` VARCHAR(255) NOT NULL COMMENT '图片名称快照',

  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='/chatbot布局明细表';