-- 评论功能相关表创建脚本
-- 创建时间: 2024-12-19

-- 商品评论表
CREATE TABLE `ai_comments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `product_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
  `order_id` bigint(20) unsigned NOT NULL COMMENT '订单ID',
  `order_item_id` bigint(20) unsigned NOT NULL COMMENT '订单项ID',
  `rating` tinyint(4) NOT NULL DEFAULT '5' COMMENT '评分 1-5星',
  `content` text COMMENT '评论内容',
  `images` text COMMENT '评论图片，JSON格式存储',
  `is_anonymous` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否匿名评论',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态：1-正常，2-隐藏，3-删除',
  `reply_count` int(11) NOT NULL DEFAULT '0' COMMENT '回复数量',
  `like_count` int(11) NOT NULL DEFAULT '0' COMMENT '点赞数量',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_order_item_id` (`order_item_id`),
  KEY `idx_rating` (`rating`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品评论表';

-- 评论回复表
CREATE TABLE `ai_comment_replies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL COMMENT '评论ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '回复用户ID',
  `content` text NOT NULL COMMENT '回复内容',
  `is_admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否管理员回复',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态：1-正常，2-隐藏，3-删除',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_comment_id` (`comment_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论回复表';

-- 评论点赞表
CREATE TABLE `ai_comment_likes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL COMMENT '评论ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_comment_user` (`comment_id`, `user_id`),
  KEY `idx_comment_id` (`comment_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论点赞表';

-- 为商品表添加评论统计字段
ALTER TABLE `ai_products` 
ADD COLUMN `comment_count` int(11) NOT NULL DEFAULT '0' COMMENT '评论数量' AFTER `sales`,
ADD COLUMN `average_rating` decimal(3,2) NOT NULL DEFAULT '0.00' COMMENT '平均评分' AFTER `comment_count`;

-- 为订单项表添加评论状态字段
ALTER TABLE `order_items` 
ADD COLUMN `is_commented` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已评论' AFTER `spec_info`;