create database if not exists game CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

use game;

CREATE TABLE `app` (
    `id` int NOT NULL AUTO_INCREMENT,
    `AppName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `WsHost` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `WsPort` int DEFAULT NULL,
    `ServerName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `MysqlName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `Database` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `Topic` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `Update` int DEFAULT NULL,
    `AppId` int NOT NULL,
    `Path` varchar(100) DEFAULT NULL,
    `tz` varchar(100) DEFAULT 'Asia/Shanghai',
    PRIMARY KEY (`id`),
    UNIQUE KEY `app_unique` (`AppId`)
) ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8mb4;

-- game.server definition

CREATE TABLE `server` (
    `id` int NOT NULL AUTO_INCREMENT,
    `AppId` int NOT NULL,
    `ServerId` int NOT NULL,
    `ServerName` varchar(100) NOT NULL,
    `Status` int NOT NULL,
    `CreateTime` int NOT NULL,
    `OpenServerTime` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4;

CREATE TABLE `node` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '节点名称',
    `host` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '外网访问地址',
    `internalHost` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '内访问地址',
    `status` int DEFAULT NULL COMMENT '状态',
    `createTime` int DEFAULT NULL COMMENT '创建时间',
    `updateTime` int DEFAULT NULL COMMENT '更新时间',
    `hardware` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '硬件信息',
    `area` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区域',
    `operator` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '运营商',
    `user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户',
    `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
    `tz` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'Asia/Shanghai' COMMENT '时区',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4;

CREATE TABLE `mysql` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '节点名称',
    `host` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '访问地址',
    `port` int DEFAULT 3306 COMMENT '端口',
    `status` int DEFAULT NULL COMMENT '状态',
    `createTime` int DEFAULT NULL COMMENT '创建时间',
    `updateTime` int DEFAULT NULL COMMENT '更新时间',
    `version` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本',
    `user` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户',
    `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4;

CREATE TABLE `admin` (
    `id` int NOT NULL AUTO_INCREMENT,
    `username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
    `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
    `real_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '真实姓名',
    `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '昵称',
    `phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号',
    `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱',
    `token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '令牌',
    `group` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户组',
    `status` int DEFAULT 1 COMMENT '状态 1=正常 0=禁用',
    `expires` int DEFAULT 0 COMMENT '过期时间',
    `role` int DEFAULT NULL COMMENT '角色 0=超管 1=管理员 2=普通用户 99=外包翻译',
    `lastLoginTime` int DEFAULT 0 COMMENT '最后登录时间',
    `lastLoginIp` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '最后登录IP',
    `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '备注',
    `createTime` int DEFAULT NULL COMMENT '创建时间',
    `updateTime` int DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_unique` (`username`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4;

CREATE TABLE `admin_log` (
    `id` int NOT NULL AUTO_INCREMENT,
    `admin` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '管理员',
    `action` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作',
    `params` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '参数',
    `ip` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'IP地址',
    `createTime` int DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4;

CREATE TABLE `mail_audit` (
    `id` int NOT NULL AUTO_INCREMENT,
    `app_id` int NOT NULL DEFAULT 0 COMMENT '应用ID',
    `server_id` int NOT NULL DEFAULT 0 COMMENT '服务器ID',
    `mail_id` int NOT NULL DEFAULT 0 COMMENT '正式邮件ID',
    `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '标题',
    `subTitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '副标题',
    `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '内容',
    `title_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '英文标题',
    `subTitle_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '英文副标题',
    `content_en` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '英文内容',
    `title_ptbr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '葡语标题',
    `subTitle_ptbr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '葡语副标题',
    `content_ptbr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '葡语内容',
    `title_es_latam` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '西语标题',
    `subTitle_es_latam` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '西语副标题',
    `content_es_latam` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '西语内容',
    `items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '道具',
    `start_time` bigint NOT NULL DEFAULT 0 COMMENT '开始时间',
    `end_time` bigint NOT NULL DEFAULT 0 COMMENT '结束时间',
    `register_time` bigint NOT NULL DEFAULT 0 COMMENT '注册时间',
    `mail_type` int NOT NULL DEFAULT 1 COMMENT '邮件类型',
    `send_type` int NOT NULL DEFAULT 1 COMMENT '发送类型',
    `to_uids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '目标UID',
    `create_time` bigint NOT NULL DEFAULT 0 COMMENT '申请时间',
    `level` int NOT NULL DEFAULT 0 COMMENT '最低等级',
    `status` int NOT NULL DEFAULT 1 COMMENT '1待审核 2已通过 3已驳回',
    `applicant` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '申请人',
    `reviewer` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '审核人',
    `review_remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '审核备注',
    `review_time` bigint NOT NULL DEFAULT 0 COMMENT '审核时间',
    PRIMARY KEY (`id`),
    KEY `idx_mail_audit_app_server` (`app_id`, `server_id`),
    KEY `idx_mail_audit_status` (`status`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE `order_reissue_audit` (
    `id` int NOT NULL AUTO_INCREMENT,
    `uid` bigint NOT NULL DEFAULT 0 COMMENT '用户UID',
    `app_id` int NOT NULL DEFAULT 0 COMMENT '应用ID',
    `server_id` int NOT NULL DEFAULT 0 COMMENT '服务器ID',
    `order_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '订单号',
    `third_party_order_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '第三方订单号',
    `reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '补单理由',
    `price` decimal(10, 2) NOT NULL DEFAULT 0 COMMENT '订单金额',
    `product_id` int NOT NULL DEFAULT 0 COMMENT '商品ID',
    `pay_status` int NOT NULL DEFAULT 0 COMMENT '原支付状态',
    `applicant` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '申请人',
    `reviewer` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '审核人',
    `review_remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '审核备注',
    `status` int NOT NULL DEFAULT 1 COMMENT '1待审核 2已通过 3已驳回',
    `create_time` bigint NOT NULL DEFAULT 0 COMMENT '申请时间',
    `review_time` bigint NOT NULL DEFAULT 0 COMMENT '审核时间',
    `original_pay_time` bigint NOT NULL DEFAULT 0 COMMENT '原支付时间',
    `original_channel_order_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '原第三方订单号',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_reissue_order_id` (`order_id`),
    KEY `idx_order_reissue_uid` (`uid`),
    KEY `idx_order_reissue_status` (`status`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;