-- 创建用户表
CREATE TABLE IF NOT EXISTS `t_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `openid` VARCHAR(100) DEFAULT NULL COMMENT '微信小程序OpenID',
  `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `nickname` VARCHAR(50) DEFAULT NULL COMMENT '昵称',
  `avatar` VARCHAR(255) DEFAULT NULL COMMENT '头像',
  `credit_score` INT DEFAULT 100 COMMENT '信用分',
  `status` INT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_phone` (`phone`),
  UNIQUE KEY `uk_openid` (`openid`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 创建账户表
CREATE TABLE IF NOT EXISTS `t_account` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT '可用余额',
  `frozen_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '冻结金额',
  `total_recharge` DECIMAL(10,2) DEFAULT 0.00 COMMENT '累计充值',
  `total_consume` DECIMAL(10,2) DEFAULT 0.00 COMMENT '累计消费',
  `points` INT DEFAULT 0 COMMENT '积分',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户表';

-- 创建资金流水表
CREATE TABLE IF NOT EXISTS `t_fund_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` BIGINT NOT NULL COMMENT '用户ID',
  `type` TINYINT NOT NULL COMMENT '交易类型：1-充值，2-消费，3-退款，4-提现',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
  `balance_before` DECIMAL(10,2) NOT NULL COMMENT '交易前余额',
  `balance_after` DECIMAL(10,2) NOT NULL COMMENT '交易后余额',
  `order_no` VARCHAR(64) DEFAULT NULL COMMENT '关联订单号',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水表';

-- 插入测试数据
INSERT IGNORE INTO `t_user` (`id`, `openid`, `phone`, `nickname`, `avatar`, `credit_score`, `status`) VALUES
(10000, 'test_openid_1', '13800138001', '测试用户1', 'https://wx.qlogo.cn/mmopen/vi_32/default_avatar.png', 100, 1);

INSERT IGNORE INTO `t_account` (`user_id`, `balance`, `points`) VALUES
(10000, 100.00, 0);