USE youlai_admin;

-- 齐云 IP 上游适配升级脚本

SET @schema_name = DATABASE();

-- 兼容未执行会员代理归属升级脚本的数据库。
-- 当前 StaticProxyAsset 实体已包含 memberUserId，MyBatis-Plus 查询会选择 member_user_id；
-- 老库缺少该字段时，静态代理自动续费/同步定时任务会报 Unknown column。
SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_static_proxy_asset'
    AND COLUMN_NAME = 'member_user_id'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_static_proxy_asset` ADD COLUMN `member_user_id` BIGINT NULL COMMENT ''会员ID'' AFTER `user_id`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_static_proxy_asset'
    AND INDEX_NAME = 'idx_xxk_static_proxy_asset_member_user_id'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_static_proxy_asset` ADD INDEX `idx_xxk_static_proxy_asset_member_user_id` (`member_user_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 会员侧订单开通会写入 member_user_id；这些表如果仍是旧结构，会导致上游已开通但本地资源/白名单插入失败。
ALTER TABLE `xxk_dynamic_channel` MODIFY COLUMN `user_id` BIGINT NULL COMMENT '用户ID';

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_channel'
    AND COLUMN_NAME = 'member_user_id'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_dynamic_channel` ADD COLUMN `member_user_id` BIGINT NULL COMMENT ''会员ID'' AFTER `user_id`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_channel'
    AND INDEX_NAME = 'idx_xxk_dynamic_channel_member_user_id'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_dynamic_channel` ADD INDEX `idx_xxk_dynamic_channel_member_user_id` (`member_user_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE `xxk_dynamic_channel_traffic_log` MODIFY COLUMN `user_id` BIGINT NULL COMMENT '用户ID';

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_channel_traffic_log'
    AND COLUMN_NAME = 'member_user_id'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_dynamic_channel_traffic_log` ADD COLUMN `member_user_id` BIGINT NULL COMMENT ''会员ID'' AFTER `user_id`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_channel_traffic_log'
    AND INDEX_NAME = 'idx_xxk_dynamic_channel_traffic_log_member_user_id'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_dynamic_channel_traffic_log` ADD INDEX `idx_xxk_dynamic_channel_traffic_log_member_user_id` (`member_user_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE `xxk_dynamic_proxy_generate_log` MODIFY COLUMN `user_id` BIGINT NULL COMMENT '用户ID';

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_proxy_generate_log'
    AND COLUMN_NAME = 'member_user_id'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_dynamic_proxy_generate_log` ADD COLUMN `member_user_id` BIGINT NULL COMMENT ''会员ID'' AFTER `user_id`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_dynamic_proxy_generate_log'
    AND INDEX_NAME = 'idx_xxk_dynamic_proxy_generate_log_member_user_id'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_dynamic_proxy_generate_log` ADD INDEX `idx_xxk_dynamic_proxy_generate_log_member_user_id` (`member_user_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE `xxk_static_proxy_asset` MODIFY COLUMN `user_id` BIGINT NULL COMMENT '用户ID';
ALTER TABLE `xxk_static_proxy_whitelist` MODIFY COLUMN `user_id` BIGINT NULL COMMENT '用户ID';

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_static_proxy_whitelist'
    AND COLUMN_NAME = 'member_user_id'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_static_proxy_whitelist` ADD COLUMN `member_user_id` BIGINT NULL COMMENT ''会员ID'' AFTER `user_id`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_static_proxy_whitelist'
    AND INDEX_NAME = 'uk_member_whitelist_ip'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_static_proxy_whitelist` ADD UNIQUE KEY `uk_member_whitelist_ip` (`member_user_id`, `whitelist_ip`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_static_proxy_whitelist'
    AND INDEX_NAME = 'idx_xxk_static_proxy_whitelist_member_user_id'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_static_proxy_whitelist` ADD INDEX `idx_xxk_static_proxy_whitelist_member_user_id` (`member_user_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product'
    AND COLUMN_NAME = 'upstream_params'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_product` ADD COLUMN `upstream_params` text DEFAULT NULL COMMENT ''上游扩展参数JSON'' AFTER `purpose_web`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product'
    AND COLUMN_NAME = 'upstream_capabilities'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_product` ADD COLUMN `upstream_capabilities` text DEFAULT NULL COMMENT ''上游能力JSON'' AFTER `upstream_params`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_duration_multiplier'
    AND COLUMN_NAME = 'duration_unit'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_product_duration_multiplier` ADD COLUMN `duration_unit` varchar(16) NOT NULL DEFAULT ''DAY'' COMMENT ''时长单位(DAY天/HOUR小时)'' AFTER `duration_days`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_duration_multiplier'
    AND COLUMN_NAME = 'duration_value'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_product_duration_multiplier` ADD COLUMN `duration_value` int NOT NULL DEFAULT 1 COMMENT ''时长数值'' AFTER `duration_unit`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `xxk_product_duration_multiplier`
SET `duration_unit` = 'DAY',
    `duration_value` = `duration_days`
WHERE (`duration_unit` IS NULL OR `duration_unit` = '' OR `duration_unit` = 'DAY')
  AND (`duration_value` IS NULL OR `duration_value` <= 1);

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_duration_multiplier'
    AND INDEX_NAME = 'uk_product_duration'
);
SET @sql = IF(
  @index_exists > 0,
  'ALTER TABLE `xxk_product_duration_multiplier` DROP INDEX `uk_product_duration`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_duration_multiplier'
    AND INDEX_NAME = 'uk_product_duration_unit_value'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_product_duration_multiplier` ADD UNIQUE KEY `uk_product_duration_unit_value` (`product_id`, `duration_unit`, `duration_value`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_duration_multiplier'
    AND INDEX_NAME = 'idx_product_duration_days'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_product_duration_multiplier` ADD INDEX `idx_product_duration_days` (`product_id`, `duration_days`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'price_type');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `price_type` varchar(20) DEFAULT NULL COMMENT ''价格类型(DEFAULT默认/NODE特殊节点)'' AFTER `product_id`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_product_type');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_product_type` varchar(32) DEFAULT NULL COMMENT ''齐云产品类型'' AFTER `price_type`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_pid');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_pid` varchar(64) DEFAULT NULL COMMENT ''齐云项目ID'' AFTER `qiyun_product_type`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_project_name');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_project_name` varchar(255) DEFAULT NULL COMMENT ''齐云项目名称'' AFTER `qiyun_pid`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_area_id');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_area_id` varchar(64) DEFAULT NULL COMMENT ''齐云省份ID'' AFTER `qiyun_project_name`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_area_name');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_area_name` varchar(255) DEFAULT NULL COMMENT ''齐云省份名称'' AFTER `qiyun_area_id`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_node_id');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_node_id` varchar(100) DEFAULT NULL COMMENT ''齐云节点ID'' AFTER `qiyun_area_name`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @column_exists = (SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'xxk_product_static_country_price' AND COLUMN_NAME = 'qiyun_node_name');
SET @sql = IF(@column_exists = 0, 'ALTER TABLE `xxk_product_static_country_price` ADD COLUMN `qiyun_node_name` varchar(255) DEFAULT NULL COMMENT ''齐云节点名称'' AFTER `qiyun_node_id`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE `xxk_product` MODIFY COLUMN `currency` varchar(10) DEFAULT 'RMB' COMMENT '币种';
ALTER TABLE `xxk_product_static_country_price` MODIFY COLUMN `currency` varchar(10) DEFAULT 'RMB' COMMENT '币种';
ALTER TABLE `xxk_proxy_order` MODIFY COLUMN `currency` varchar(10) DEFAULT 'RMB' COMMENT '币种';

UPDATE `xxk_product` SET `currency` = 'RMB' WHERE `currency` IS NULL OR `currency` = '' OR `currency` = 'USD';
UPDATE `xxk_product_static_country_price` SET `currency` = 'RMB' WHERE `currency` IS NULL OR `currency` = '' OR `currency` = 'USD';
UPDATE `xxk_proxy_order` SET `currency` = 'RMB' WHERE `currency` IS NULL OR `currency` = '' OR `currency` = 'USD';

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_static_country_price'
    AND INDEX_NAME = 'uk_product_country'
);
SET @sql = IF(
  @index_exists > 0,
  'ALTER TABLE `xxk_product_static_country_price` DROP INDEX `uk_product_country`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_static_country_price'
    AND INDEX_NAME = 'idx_product_country'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_product_static_country_price` ADD INDEX `idx_product_country` (`product_id`, `country_code`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_product_static_country_price'
    AND INDEX_NAME = 'idx_static_price_qiyun_node'
);
SET @sql = IF(
  @index_exists = 0,
  'ALTER TABLE `xxk_product_static_country_price` ADD INDEX `idx_static_price_qiyun_node` (`product_id`, `price_type`, `qiyun_node_id`) USING BTREE',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `xxk_upstream_provider`
SET `provider_code` = 'QIYUN01',
    `provider_name` = '齐云IP',
    `provider_type` = 'QIYUN',
    `base_url` = 'https://www.qiyunip.com',
    `auth_user_id` = NULL,
    `auth_token` = NULL,
    `success_codes` = '1'
WHERE `provider_type` = 'IPNUX';

-- 开放 API 模块补丁：应用、申请、独立账户、充值、回调日志和后台菜单。
CREATE TABLE IF NOT EXISTS `xxk_open_api_app` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `app_id` VARCHAR(64) NOT NULL COMMENT '应用ID',
  `app_name` VARCHAR(128) NOT NULL COMMENT '应用名称',
  `app_secret` VARCHAR(128) NOT NULL COMMENT '应用密钥',
  `member_user_id` BIGINT NOT NULL COMMENT '绑定会员ID',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(0停用 1启用)',
  `allow_ip_list` TEXT DEFAULT NULL COMMENT '允许访问IP列表，逗号/换行分隔',
  `callback_url` VARCHAR(255) DEFAULT NULL COMMENT '订单结果回调地址',
  `callback_secret` VARCHAR(128) DEFAULT NULL COMMENT '订单结果回调签名密钥',
  `last_auth_time` DATETIME DEFAULT NULL COMMENT '最近换取token时间',
  `last_auth_ip` VARCHAR(64) DEFAULT NULL COMMENT '最近换取token IP',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `create_by` BIGINT DEFAULT NULL COMMENT '创建人ID',
  `update_by` BIGINT DEFAULT NULL COMMENT '更新人ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_open_api_app_app_id` (`app_id`),
  UNIQUE KEY `uk_xxk_open_api_app_member_user_id` (`member_user_id`),
  KEY `idx_xxk_open_api_app_member_user_id` (`member_user_id`),
  KEY `idx_xxk_open_api_app_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放接口应用表';

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_open_api_app'
    AND COLUMN_NAME = 'callback_url'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_url` VARCHAR(255) DEFAULT NULL COMMENT ''订单结果回调地址'' AFTER `allow_ip_list`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @column_exists = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name
    AND TABLE_NAME = 'xxk_open_api_app'
    AND COLUMN_NAME = 'callback_secret'
);
SET @sql = IF(
  @column_exists = 0,
  'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_secret` VARCHAR(128) DEFAULT NULL COMMENT ''订单结果回调签名密钥'' AFTER `callback_url`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `xxk_open_api_apply` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `apply_no` VARCHAR(64) NOT NULL COMMENT '申请单号',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `username_snapshot` VARCHAR(64) DEFAULT NULL COMMENT '用户名快照',
  `mobile_snapshot` VARCHAR(32) DEFAULT NULL COMMENT '手机号快照',
  `contact_name` VARCHAR(64) NOT NULL COMMENT '联系人',
  `contact_mobile` VARCHAR(32) DEFAULT NULL COMMENT '联系电话',
  `contact_email` VARCHAR(128) DEFAULT NULL COMMENT '联系邮箱',
  `company_name` VARCHAR(128) DEFAULT NULL COMMENT '公司名称',
  `purpose` VARCHAR(255) NOT NULL COMMENT '申请用途',
  `scenario_description` TEXT NOT NULL COMMENT '使用场景说明',
  `allow_ip_list` TEXT DEFAULT NULL COMMENT 'IP白名单',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态(0待审核 1已通过 2已驳回)',
  `submit_remark` VARCHAR(255) DEFAULT NULL COMMENT '补充说明',
  `submit_time` DATETIME DEFAULT NULL COMMENT '提交时间',
  `audit_time` DATETIME DEFAULT NULL COMMENT '审核时间',
  `audit_by` BIGINT DEFAULT NULL COMMENT '审核人ID',
  `audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '审核备注',
  `open_api_app_id` BIGINT DEFAULT NULL COMMENT '关联开放应用ID',
  `create_by` BIGINT DEFAULT NULL COMMENT '创建人ID',
  `update_by` BIGINT DEFAULT NULL COMMENT '更新人ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_open_api_apply_no` (`apply_no`),
  KEY `idx_xxk_open_api_apply_member_user_id` (`member_user_id`),
  KEY `idx_xxk_open_api_apply_status` (`status`),
  KEY `idx_xxk_open_api_apply_submit_time` (`submit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放接口申请表';

CREATE TABLE IF NOT EXISTS `xxk_open_api_account` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '余额',
  `frozen_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '冻结余额',
  `total_recharge_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计充值金额',
  `total_consume_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计消费金额',
  `total_refund_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '累计退款金额',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(0停用 1正常)',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_open_api_account_app_id` (`open_api_app_id`),
  KEY `idx_xxk_open_api_account_member_user_id` (`member_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户表';

CREATE TABLE IF NOT EXISTS `xxk_open_api_account_flow` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `flow_no` VARCHAR(64) NOT NULL COMMENT '流水号',
  `open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `biz_type` VARCHAR(64) NOT NULL COMMENT '业务类型',
  `change_type` VARCHAR(16) NOT NULL COMMENT '变动类型(IN/OUT)',
  `change_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动金额',
  `before_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动前余额',
  `after_balance` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '变动后余额',
  `related_order_no` VARCHAR(64) DEFAULT NULL COMMENT '关联单号',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `operate_by` BIGINT DEFAULT NULL COMMENT '操作人ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_open_api_account_flow_no` (`flow_no`),
  KEY `idx_xxk_open_api_account_flow_app_id` (`open_api_app_id`),
  KEY `idx_xxk_open_api_account_flow_related_order_no` (`related_order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户流水表';

CREATE TABLE IF NOT EXISTS `xxk_open_api_recharge_order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `recharge_no` VARCHAR(64) NOT NULL COMMENT '充值单号',
  `pay_order_no` VARCHAR(64) NOT NULL COMMENT '支付单号',
  `open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `payment_type` VARCHAR(32) NOT NULL COMMENT '支付方式',
  `pay_status` VARCHAR(32) NOT NULL COMMENT '支付状态',
  `channel_order_no` VARCHAR(64) DEFAULT NULL COMMENT '渠道订单号',
  `channel_response` LONGTEXT DEFAULT NULL COMMENT '渠道响应',
  `client_type` VARCHAR(32) DEFAULT NULL COMMENT '客户端类型',
  `return_url` VARCHAR(500) DEFAULT NULL COMMENT '回跳地址',
  `amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '充值金额',
  `gift_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '赠送金额',
  `credited_amount` DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '到账金额',
  `promotion_rule_snapshot` TEXT DEFAULT NULL COMMENT '活动规则快照',
  `currency` VARCHAR(16) DEFAULT 'USD' COMMENT '币种',
  `paid_time` DATETIME DEFAULT NULL COMMENT '支付完成时间',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_xxk_open_api_recharge_no` (`recharge_no`),
  UNIQUE KEY `uk_xxk_open_api_pay_order_no` (`pay_order_no`),
  KEY `idx_xxk_open_api_recharge_order_app_id` (`open_api_app_id`),
  KEY `idx_xxk_open_api_recharge_order_member_user_id` (`member_user_id`),
  KEY `idx_xxk_open_api_recharge_order_pay_status` (`pay_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API独立账户充值单表';

CREATE TABLE IF NOT EXISTS `xxk_open_api_callback_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
  `member_user_id` BIGINT NOT NULL COMMENT '会员ID',
  `order_id` BIGINT NOT NULL COMMENT '订单ID',
  `order_no` VARCHAR(64) NOT NULL COMMENT '订单号',
  `callback_url` VARCHAR(255) DEFAULT NULL COMMENT '回调地址',
  `event_type` VARCHAR(64) NOT NULL COMMENT '事件类型',
  `request_body` LONGTEXT DEFAULT NULL COMMENT '请求报文',
  `response_body` LONGTEXT DEFAULT NULL COMMENT '响应报文',
  `response_status` INT DEFAULT NULL COMMENT '响应HTTP状态码',
  `delivery_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '投递状态(PENDING/SUCCESS/FAIL/SKIPPED)',
  `error_message` VARCHAR(500) DEFAULT NULL COMMENT '错误信息',
  `attempt_no` INT NOT NULL DEFAULT 1 COMMENT '尝试次数',
  `trigger_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '触发时间',
  `finish_time` DATETIME DEFAULT NULL COMMENT '完成时间',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_xxk_open_api_callback_log_app_id` (`open_api_app_id`),
  KEY `idx_xxk_open_api_callback_log_order_no` (`order_no`),
  KEY `idx_xxk_open_api_callback_log_status` (`delivery_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放API订单回调日志表';

INSERT INTO `sys_menu`
(`id`, `parent_id`, `tree_path`, `name`, `type`, `route_name`, `route_path`, `component`, `perm`,
 `always_show`, `keep_alive`, `visible`, `sort`, `icon`, `redirect`, `create_time`, `update_time`, `params`)
VALUES
(1220, 1100, '0,1100', '开放API管理', 'C', NULL, '/open-api-manage', 'Layout', NULL,
 1, 0, 1, 13, 'api', '/open-api-manage/open-api', NOW(), NOW(), NULL),
(1221, 1220, '0,1100,1220', '开放API申请审核', 'M', 'ProxyOpenApiApply', 'open-api', 'proxy/open-api/index', NULL,
 0, 1, 1, 1, 'form', NULL, NOW(), NOW(), NULL),
(122101, 1221, '0,1100,1220,1221', '开放API申请查询', 'B', NULL, '', NULL, 'openapi:apply:list',
 NULL, NULL, 1, 1, '', NULL, NOW(), NOW(), NULL),
(122102, 1221, '0,1100,1220,1221', '开放API申请审核', 'B', NULL, '', NULL, 'openapi:apply:audit',
 NULL, NULL, 1, 2, '', NULL, NOW(), NOW(), NULL),
(1222, 1220, '0,1100,1220', '开放API回调日志', 'M', 'ProxyOpenApiCallback', 'open-api-callback', 'proxy/open-api-callback/index', NULL,
 0, 1, 1, 2, 'notification', NULL, NOW(), NOW(), NULL),
(122201, 1222, '0,1100,1220,1222', '开放API回调日志查询', 'B', NULL, '', NULL, 'openapi:callback-log:list',
 NULL, NULL, 1, 1, '', NULL, NOW(), NOW(), NULL),
(122202, 1222, '0,1100,1220,1222', '开放API回调日志重发', 'B', NULL, '', NULL, 'openapi:callback-log:retry',
 NULL, NULL, 1, 2, '', NULL, NOW(), NOW(), NULL),
(1223, 1220, '0,1100,1220', '开放API账户中心', 'M', 'ProxyOpenApiAccount', 'open-api-account', 'proxy/open-api-account/index', NULL,
 0, 1, 0, 3, 'wallet', NULL, NOW(), NOW(), NULL)
ON DUPLICATE KEY UPDATE
`name` = VALUES(`name`),
`route_name` = VALUES(`route_name`),
`route_path` = VALUES(`route_path`),
`component` = VALUES(`component`),
`perm` = VALUES(`perm`),
`keep_alive` = VALUES(`keep_alive`),
`visible` = VALUES(`visible`),
`sort` = VALUES(`sort`),
`icon` = VALUES(`icon`),
`update_time` = NOW();

INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES
(2, 1220), (2, 1221), (2, 122101), (2, 122102), (2, 1222), (2, 122201), (2, 122202), (2, 1223);