kknongchang_2506/app/Module/Shop/Databases/create.sql

-- 创建商店分类表
CREATE TABLE IF NOT EXISTS `kku_shop_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类ID，主键',
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `code` varchar(50) NOT NULL COMMENT '分类编码（唯一）',
  `icon` varchar(255) DEFAULT NULL COMMENT '分类图标',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重',
  `parent_id` int(10) unsigned DEFAULT NULL COMMENT '父分类ID（可为空，用于实现分类层级）',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否激活（0:否, 1:是）',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_categories_code_unique` (`code`),
  KEY `shop_categories_parent_id_index` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商店分类表';

-- 创建商店商品表
CREATE TABLE IF NOT EXISTS `kku_shop_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品ID，主键',
  `name` varchar(100) NOT NULL COMMENT '商品名称',
  `description` text COMMENT '商品描述',
  `category_id` int(10) unsigned NOT NULL COMMENT '分类ID，外键关联kku_shop_categories表',
  `item_id` int(10) unsigned NOT NULL COMMENT '关联的物品ID，外键关联kku_item_items表',
  `item_quantity` int(11) NOT NULL DEFAULT '1' COMMENT '物品数量',
  `price` int(11) NOT NULL COMMENT '价格',
  `currency_id` int(10) unsigned NOT NULL COMMENT '货币类型ID',
  `max_buy` int(11) NOT NULL DEFAULT '0' COMMENT '最大购买数量（0表示无限制）',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否激活（0:否, 1:是）',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重',
  `image` varchar(255) DEFAULT NULL COMMENT '商品图片',
  `start_time` timestamp NULL DEFAULT NULL COMMENT '上架时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '下架时间',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `shop_items_category_id_index` (`category_id`),
  KEY `shop_items_item_id_index` (`item_id`),
  KEY `shop_items_currency_id_index` (`currency_id`),
  KEY `shop_items_is_active_index` (`is_active`),
  KEY `shop_items_sort_order_index` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商店商品表';

-- 创建商店购买记录表
CREATE TABLE IF NOT EXISTS `kku_shop_purchase_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录ID，主键',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `shop_item_id` int(10) unsigned NOT NULL COMMENT '商品ID，外键关联kku_shop_items表',
  `item_id` int(10) unsigned NOT NULL COMMENT '物品ID，外键关联kku_item_items表',
  `quantity` int(11) NOT NULL COMMENT '购买数量',
  `price` int(11) NOT NULL COMMENT '单价',
  `total_price` int(11) NOT NULL COMMENT '总价',
  `currency_id` int(10) unsigned NOT NULL COMMENT '货币类型ID',
  `purchase_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '购买时间',
  `ip_address` varchar(45) DEFAULT NULL COMMENT '购买IP地址',
  `device_info` varchar(255) DEFAULT NULL COMMENT '设备信息',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `shop_purchase_logs_user_id_index` (`user_id`),
  KEY `shop_purchase_logs_shop_item_id_index` (`shop_item_id`),
  KEY `shop_purchase_logs_item_id_index` (`item_id`),
  KEY `shop_purchase_logs_purchase_time_index` (`purchase_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商店购买记录表';

-- 创建商店促销活动表
CREATE TABLE IF NOT EXISTS `kku_shop_promotions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '促销ID，主键',
  `name` varchar(100) NOT NULL COMMENT '促销名称',
  `description` text COMMENT '促销描述',
  `banner` varchar(255) DEFAULT NULL COMMENT '促销横幅图片',
  `discount_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '折扣类型（1:固定折扣, 2:百分比折扣）',
  `discount_value` int(11) NOT NULL COMMENT '折扣值（固定折扣为具体金额，百分比折扣为1-100的整数）',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否激活（0:否, 1:是）',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重',
  `start_time` timestamp NULL DEFAULT NULL COMMENT '开始时间',
  `end_time` timestamp NULL DEFAULT NULL COMMENT '结束时间',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `shop_promotions_is_active_index` (`is_active`),
  KEY `shop_promotions_sort_order_index` (`sort_order`),
  KEY `shop_promotions_start_time_index` (`start_time`),
  KEY `shop_promotions_end_time_index` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商店促销活动表';

-- 创建商店促销商品关联表
CREATE TABLE IF NOT EXISTS `kku_shop_promotion_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '关联ID，主键',
  `promotion_id` int(10) unsigned NOT NULL COMMENT '促销ID，外键关联kku_shop_promotions表',
  `shop_item_id` int(10) unsigned NOT NULL COMMENT '商品ID，外键关联kku_shop_items表',
  `custom_discount_value` int(11) DEFAULT NULL COMMENT '自定义折扣值（可为空，优先于促销活动的折扣值）',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_promotion_items_promotion_id_shop_item_id_unique` (`promotion_id`,`shop_item_id`),
  KEY `shop_promotion_items_promotion_id_index` (`promotion_id`),
  KEY `shop_promotion_items_shop_item_id_index` (`shop_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商店促销商品关联表';