-- MySQL 8.0 建表脚本
-- 根据代码中的entity和domain生成

-- 设置字符集和排序规则
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------
-- keXuePay模块表结构
-- ------------------------------

-- 1. 产品表 t_product
CREATE TABLE `t_product` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `title` varchar(255) NOT NULL COMMENT '商品名称',
  `price` int NOT NULL COMMENT '价格（分）',
  `get_beans` int NOT NULL COMMENT '得到的可学豆',
  `add_beans` int NOT NULL COMMENT '加送的可学豆',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='产品表';

-- 2. 订单信息表 t_order_info
CREATE TABLE `t_order_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `title` varchar(255) NOT NULL COMMENT '订单标题',
  `order_no` varchar(64) NOT NULL COMMENT '商户订单编号',
  `user_id` bigint NOT NULL COMMENT '用户id',
  `product_id` bigint NOT NULL COMMENT '支付产品id',
  `total_fee` int NOT NULL COMMENT '订单金额(分)',
  `code_url` varchar(512) DEFAULT NULL COMMENT '订单二维码连接',
  `order_status` varchar(32) NOT NULL COMMENT '订单状态',
  `payment_type` varchar(32) NOT NULL COMMENT '支付方式',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_order_status` (`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单信息表';

-- 3. 支付信息表 t_payment_info
CREATE TABLE `t_payment_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `order_no` varchar(64) NOT NULL COMMENT '商品订单编号',
  `transaction_id` varchar(64) DEFAULT NULL COMMENT '支付系统交易编号',
  `payment_type` varchar(32) NOT NULL COMMENT '支付类型',
  `trade_type` varchar(32) DEFAULT NULL COMMENT '交易类型',
  `trade_state` varchar(32) DEFAULT NULL COMMENT '交易状态',
  `payer_total` int DEFAULT NULL COMMENT '支付金额(分)',
  `content` text COMMENT '通知参数',
  PRIMARY KEY (`id`),
  KEY `idx_order_no` (`order_no`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_payment_type` (`payment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付信息表';

-- 4. 退款信息表 t_refund_info
CREATE TABLE `t_refund_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `order_no` varchar(64) NOT NULL COMMENT '商品订单编号',
  `refund_no` varchar(64) NOT NULL COMMENT '退款单编号',
  `refund_id` varchar(64) DEFAULT NULL COMMENT '支付系统退款单号',
  `total_fee` int NOT NULL COMMENT '原订单金额(分)',
  `refund` int NOT NULL COMMENT '退款金额(分)',
  `reason` varchar(255) NOT NULL COMMENT '退款原因',
  `refund_status` varchar(32) NOT NULL COMMENT '退款单状态',
  `content_return` text COMMENT '申请退款返回参数',
  `content_notify` text COMMENT '退款结果通知参数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_refund_no` (`refund_no`),
  KEY `idx_order_no` (`order_no`),
  KEY `idx_refund_id` (`refund_id`),
  KEY `idx_refund_status` (`refund_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='退款信息表';

-- ------------------------------
-- keXueUser模块表结构
-- ------------------------------

-- 1. 用户表 user
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `school` varchar(128) DEFAULT NULL COMMENT '学校',
  `major` varchar(128) DEFAULT NULL COMMENT '专业',
  `sys_beans` int DEFAULT '0' COMMENT '系统赠送豆',
  `pay_beans` int DEFAULT '0' COMMENT '购买豆',
  `invite_beans` int DEFAULT '0' COMMENT '邀请豆',
  `invite_code` varchar(32) DEFAULT NULL COMMENT '自己的邀请码',
  `inviter_id` int DEFAULT NULL COMMENT '邀请人的id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_name` (`user_name`),
  UNIQUE KEY `uk_phone` (`phone`),
  UNIQUE KEY `uk_invite_code` (`invite_code`),
  KEY `idx_inviter_id` (`inviter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- 2. 支付记录表 d_pay_record
CREATE TABLE `d_pay_record` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `drawer_id` varchar(64) NOT NULL COMMENT '设备id',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `count` int NOT NULL COMMENT '数量',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_drawer_id` (`drawer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付记录表';

-- 3. 令牌表 d_token
CREATE TABLE `d_token` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(64) NOT NULL COMMENT '用户名',
  `draw_token` varchar(255) NOT NULL COMMENT '令牌',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `drawer_id` varchar(64) NOT NULL COMMENT '设备id',
  `last_login_time` datetime NOT NULL COMMENT '最后登录时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_drawer_id` (`drawer_id`),
  KEY `idx_draw_token` (`draw_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='令牌表';

-- 4. 新闻表 news
CREATE TABLE `news` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `content` text NOT NULL COMMENT '内容',
  `url` varchar(512) DEFAULT NULL COMMENT '链接',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新闻表';

-- 5. 通知表 notices
CREATE TABLE `notices` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `content` text NOT NULL COMMENT '内容',
  `publish_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
  PRIMARY KEY (`id`),
  KEY `idx_publish_time` (`publish_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='通知表';

-- 6. 短信表 sms
CREATE TABLE `sms` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `code` varchar(6) NOT NULL COMMENT '验证码',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `state` tinyint DEFAULT '0' COMMENT '状态：0-未使用，1-已使用',
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='短信表';

SET FOREIGN_KEY_CHECKS = 1;