-- 珠宝鉴定小程序数据库建表SQL
-- 数据库名: jewelry_app


-- ----------------------------
-- 用户表
-- ----------------------------
CREATE TABLE `users` (
  `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `openid` VARCHAR(128) NOT NULL UNIQUE COMMENT '微信openid',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `nickname` VARCHAR(64) DEFAULT '' COMMENT '昵称',
  `avatar` VARCHAR(512) DEFAULT '' COMMENT '头像URL',
  `gender` TINYINT DEFAULT 0 COMMENT '性别: 0-未知, 1-男, 2-女',
  `birthday` DATE DEFAULT NULL COMMENT '生日',
  `bio` VARCHAR(256) DEFAULT '' COMMENT '个人简介',
  `points` INT NOT NULL DEFAULT 0 COMMENT '当前积分',
  `total_points_earned` INT NOT NULL DEFAULT 0 COMMENT '累计获得积分',
  `level` INT NOT NULL DEFAULT 1 COMMENT '等级(1-10)',
  `ident_count` INT NOT NULL DEFAULT 0 COMMENT '鉴定次数',
  `viewed_count` INT NOT NULL DEFAULT 0 COMMENT '被他人查看次数',
  `register_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `last_login_time` DATETIME DEFAULT NULL COMMENT '最后登录时间',
  `inviter_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '邀请人ID',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_openid` (`openid`),
  KEY `idx_phone` (`phone`),
  KEY `idx_inviter` (`inviter_id`),
  CONSTRAINT `fk_inviter` FOREIGN KEY (`inviter_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ----------------------------
-- 鉴定记录表
-- ----------------------------
CREATE TABLE `identifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '鉴定记录ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `images` JSON NOT NULL COMMENT '图片JSON数组',
  `description` TEXT COMMENT '用户描述',
  `ai_result` JSON DEFAULT NULL COMMENT 'AI鉴定结果JSON',
  `status` ENUM('public', 'hidden') NOT NULL DEFAULT 'public' COMMENT '状态: public-公开, hidden-隐藏',
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '查看次数',
  `share_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分享次数',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='鉴定记录表';

-- ----------------------------
-- 追问表
-- ----------------------------
CREATE TABLE `followups` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '追问ID',
  `identification_id` BIGINT UNSIGNED NOT NULL COMMENT '鉴定记录ID',
  `user_question` TEXT NOT NULL COMMENT '用户问题',
  `ai_answer` TEXT COMMENT 'AI回答',
  `round` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '追问轮次(1-5)',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_identification_id` (`identification_id`),
  CONSTRAINT `fk_ident` FOREIGN KEY (`identification_id`) REFERENCES `identifications` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='追问表';

-- ----------------------------
-- 积分明细表
-- ----------------------------
CREATE TABLE `point_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '积分记录ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `amount` INT NOT NULL COMMENT '积分变动数量(正数增加,负数减少)',
  `type` VARCHAR(32) NOT NULL COMMENT '类型: recharge-充值, pay_for_ident-鉴定扣费, view_others-查看他人, view_income-查看收入, platform_fee-平台抽成, share_invite-分享邀请, withdraw-提现, checkin-签到, invite_reward-邀请奖励',
  `related_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '关联ID(如鉴定记录ID,订单ID等)',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_point_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分明细表';

-- ----------------------------
-- 充值订单表
-- ----------------------------
CREATE TABLE `recharge_orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '充值金额(元)',
  `points` INT NOT NULL COMMENT '获得积分',
  `status` ENUM('pending', 'success', 'failed') NOT NULL DEFAULT 'pending' COMMENT '状态',
  `trade_no` VARCHAR(64) NOT NULL COMMENT '交易流水号',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trade_no` (`trade_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_recharge_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值订单表';

-- ----------------------------
-- 提现申请表
-- ----------------------------
CREATE TABLE `withdraw_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '提现ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `points` INT NOT NULL COMMENT '提现积分',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '提现金额(元)',
  `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending' COMMENT '状态',
  `handled_at` DATETIME DEFAULT NULL COMMENT '处理时间',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_withdraw_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现申请表';

-- ----------------------------
-- 提示词模板表
-- ----------------------------
CREATE TABLE `prompt_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `name` VARCHAR(128) NOT NULL COMMENT '模板名称',
  `content` TEXT NOT NULL COMMENT '模板内容',
  `version` VARCHAR(32) NOT NULL COMMENT '版本号',
  `is_active` TINYINT NOT NULL DEFAULT 0 COMMENT '是否启用: 0-否, 1-是',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提示词模板表';

-- ----------------------------
-- 失败案例表
-- ----------------------------
CREATE TABLE `failed_cases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '案例ID',
  `identification_id` BIGINT UNSIGNED NOT NULL COMMENT '鉴定记录ID',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `images` JSON NOT NULL COMMENT '图片JSON',
  `user_description` TEXT COMMENT '用户描述',
  `ai_output` JSON COMMENT 'AI输出',
  `expected_output` JSON COMMENT '期望输出',
  `reason` VARCHAR(512) DEFAULT '' COMMENT '失败原因',
  `status` ENUM('pending', 'fixed', 'ignored') NOT NULL DEFAULT 'pending' COMMENT '状态',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_identification_id` (`identification_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_failed_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='失败案例表';

-- ----------------------------
-- AB测试表
-- ----------------------------
CREATE TABLE `prompt_ab_tests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '测试ID',
  `test_name` VARCHAR(128) NOT NULL COMMENT '测试名称',
  `prompt_version_a` BIGINT UNSIGNED NOT NULL COMMENT '版本A模板ID',
  `prompt_version_b` BIGINT UNSIGNED NOT NULL COMMENT '版本B模板ID',
  `winner` BIGINT UNSIGNED DEFAULT NULL COMMENT '获胜版本ID',
  `start_time` DATETIME NOT NULL COMMENT '开始时间',
  `end_time` DATETIME DEFAULT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`),
  KEY `idx_winner` (`winner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AB测试表';

