# 应用信息表
# 我个人一般命名表名都喜欢 {项目标识符}_{主要表类型}_{info|其他扩展类型}
# 比如订单表会起为 pino_order_info, 然后后续相关表如第三方回调信息记录就会记录成 pino_order_callback
# 一般是前缀代表项目归属, 后缀代表业务表类型, info 就是默认列表区分
create table if not exists pino_app_info
(
    # 应用基础信息
    id              bigint unsigned  not null auto_increment comment '数据主键, 用于标识递增, 主键即是appid',
    name            varchar(64)      not null comment '应用名称',
    ident           varchar(64)      not null comment 'APP 唯一字符串标识(首字母必须是字母), 最好不要暴露 id 给外部, 会被人嗅探id递增值递归获取所有应用, 另外还有保证全局唯一的功能',
    app_key         varchar(64)      not null default '' comment '用于客户端|前端做授权哈希加密的时候密钥, 也就是 app key',
    app_secure      varchar(64)      not null default '' comment '用于服务端|游戏做授权|支付哈希加密的时候密钥, 也就是 app secure',
    status          tinyint unsigned not null default '0' comment '应用上线状态, 默认0代表刚创建, 参照具体的流程状态值处理',
    uptime          bigint unsigned  not null comment '游戏上线时间, 毫秒级别的UTC时间戳, 一旦上线完成之后这个值就不会变, 注意: 上线之后很多操作都是需要做限制修改处理',

    # 其他授权/支付所需信息
    notify_url      varchar(255)     not null default '' comment '支付回调通知地址',
    redirect_url    varchar(255)     not null default '' comment '支付完成跳转地址',

    # 区分平台的配置
    platform        tinyint unsigned not null default '0' comment '用户创建时候的平台标识ID(0=未知,参照 platform_info 信息表), 一旦确定就无法更改, 应用类型也参照自定义的第三方平台选择',
    language        varchar(8)       not null comment '默认游戏语言, 渲染登录窗体语言可选 en|zh-CN|zh-TW|ja 之类, 也就是默认功能响应的语言',
    display         tinyint unsigned not null default '0' comment '应用的显示类型, 0 - 默认 | 1 - portrait(竖屏)| 2- landscape(横屏), 实际上默认0即可, 备用字段',

    # 应用运营展示信息
    keyboards       varchar(255)     not null default '' comment 'XXX,YYY,ZZZ形式',
    icon            varchar(255)     not null default '' comment '游戏图标链接, 图标标准尺寸推荐为 192*192',
    title           varchar(255)     not null default '' comment '应用标题, 常用于展示如 "XXY第一款XXX传奇游戏 - ZZZ" 之类长标题',
    description     varchar(255)     not null default '' comment '标题内容, 可能会有大量展示的应用信息',
    company         varchar(255)     not null default '' comment '用于保存所属公司或者个人信息',
    portrait_cover  varchar(255)     not null default '' comment '竖版背景图片地址, 推荐尺寸为 640*1380',
    landscape_cover varchar(255)     not null default '' comment '横版背景图片地址, 推荐尺寸为 1380*640',


    # 创建|更新信息
    create_time     bigint unsigned  not null comment '创建时间, 毫秒级别的UTC时间戳',
    create_ip       varchar(64)      not null comment '应用创建IP地址',
    create_uid      bigint unsigned  not null comment '应用创建后台管理员ID',
    update_time     bigint unsigned  not null default '0' comment '数据更新时间, 毫秒级别的UTC时间戳',
    update_ip       varchar(64)      not null default '' comment '更新IP地址',
    update_uid      bigint unsigned  not null default '0' comment '应用更新后台管理员ID',


    # 随着启动初始化传递的 KEY-VALUE 的 JSON数据, { "xxx": "yyy", "zzz":123 }
    # 必须采用一元的对象组保存, 在初始化的时候会原样返回给客户端, 让客户端可以接收到对应属性值做调用
    properties      json             not null default JSON_OBJECT() comment '可能需要设置的额外游戏配置项(JSON对象组形式)',


    #=================================
    ## 对应不同平台需要记录的字段
    #=================================

    # H5的字段相对简单, 因为基本只需要处理成游戏启动路径即可(本质上就是网页游戏)
    game_url        varchar(255)     not null default '' comment '游戏路径, 只有H5之类才需要用到',

    # Android 的字段就比较多, 需要母包地址/签名证书/证书密钥, 这也是最复杂的配置之一
    # 研发方提交母包之后需要服务端做 apktool 反编译大量数据
    # 这部分可以后台自己帮助生成, 也可以用户提交上来的相关数据, 用于后续分包来对包重新签名
    # 微端可以看做是是发行方自行维护 Android, 所以下面的信息也是可以自己处理, 唯一不同的是分包需要自行做包名唯一化
    # 微端出分包的话, 母包是 'com.pino.game(这个包名可以随意定制)', 那么这里的微端包名必须为  'com.pino.game.{平台标识}.{应用字符串标识}'
    # 比如想出 google 的微端包, 而数据库应用的 ident 字段为 'test123', 那么母包 'com.pino.hello', 提交上来之后会做重新反编译包名修改成 'com.pino.hello.google.test123'
    # 这样会自动将包设置唯一避免出现所属的包已经被人使用的问题, 需要在此提醒必须保证 ident 字段的首位必须为字母来防止可能出现包名错误
    package_url     varchar(255)     not null default '' comment '母包上传路径, 只有 Android/微端 之类才需要用到',
    package_key     varchar(255)     not null default '' comment '证书保存地址, 只有 Android/微端 之类才需要用到',
    package_pwd     varchar(255)     not null default '' comment '证书密钥信息, 只有 Android/微端 之类才需要用到',

    # iOS 略, iOS 是另一个纬度的打包配置功能, 因为这边没有对应设备所以这里没办法细致说明

    unique ident_unique (ident),
    index status_idx (status),
    primary key (id)
) comment '应用信息表'
    engine = InnoDB
    charset = utf8mb4
    collate = utf8mb4_unicode_ci
;


# 平台信息表
# 这里简略记录下就行, 这里的定义一般是开发提供处理的定义, 其他情况不会让业务管理员去手动创建
# 甚至直接写死本地文件配置更好, 可以避免管理人员去随意修改.
create table if not exists pino_app_platform
(
    id    bigint unsigned not null auto_increment comment '数据主键, 用于标识递增',
    name  varchar(64)     not null comment '平台名称, 比如 Google/TikTop/Facebook',
    ident varchar(64)     not null comment '平台标识(首字母必须是字母), 要求字母全小写且不能带特殊符号',
    unique ident_unique (ident),
    primary key (id)
) comment '渠道信息表'
    engine = InnoDB
    charset = utf8mb4
    collate = utf8mb4_unicode_ci
;

# 基础的支付信息, 支持跨境支付
create table if not exists order_info
(
    order_id            varchar(64)      not null comment '订单标识',

    # 订单支付信息
    order_region        varchar(3)       not null comment '订单发起支付的地区, 如 CN|US|HK 等',
    order_currency      varchar(3)       not null comment '订单发起支付的货币, 如 CNY|USD|HKD 等',
    order_unit          tinyint unsigned not null default '2' comment '订单发起的最小单位, 即小数点后位数',
    order_amount        bigint unsigned  not null comment '订单发起金额, 需要采用最小货币单位',

    # 订单结算信息, 默认结算信息和支付信息一致
    # 但是有的时候会出现某些第三方支付完成之后的结算单位和地区不一致, 这种情况下最好将 支付|回调 之后的订单状态分开保存
    settlement_region   varchar(3)       not null default '' comment '订单结算支付的地区, 如 CN|US 等',
    settlement_currency varchar(3)       not null default '' comment '订单结算支付的货币, 如 CNY|USD 等',
    settlement_unit     tinyint unsigned not null default '2' comment '订单结算货币的最小单位, 即小数点后位数',
    settlement_amount   bigint unsigned  not null comment '订单结算金额, 需要采用最小货币单位',

    # 其他略
    primary key (order_id)
) comment '支付订单信息表'
    engine = InnoDB
    charset = utf8mb4
    collate = utf8mb4_unicode_ci;

# 假设目前用户支付 100 日元, 获取用日元最小货币金额
# 计算公式为 最小货币数值 = 结算金额 * (10 ^ 最小单位)
SELECT (settlement_amount * POW(10, settlement_unit)) as amount
FROM order_info
WHERE settlement_currency = 'JPY';


# 如果先直接转化为元单位, 比如人民币的 分 -> 元 方式就可以按照下面处理
SELECT (settlement_amount / POW(10, settlement_unit)) as amount
FROM order_info
WHERE settlement_currency = 'CNY';


# 如果要对其不同地区支付的货币分组, 比如统计 JPY|CNY|USD 每个货币支付金额分组排列
SELECT settlement_currency                               AS currency_code,
       SUM(settlement_amount / POW(10, settlement_unit)) AS total_amount,
       COUNT(order_id)                                   AS order_count
FROM order_info
WHERE settlement_currency IN ('JPY', 'CNY', 'USD')
GROUP BY settlement_currency # 按货币分组
ORDER BY total_amount DESC, # 总金额从高到低
         currency_code ASC; # 货币代码从低到高