247 lines
12 KiB
SQL
247 lines
12 KiB
SQL
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
SET @now_ms = CAST(UNIX_TIMESTAMP(UTC_TIMESTAMP(3)) * 1000 AS UNSIGNED);
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_policy_templates (
|
||
template_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
template_code VARCHAR(96) NOT NULL COMMENT '模板编码，跨 App 复用',
|
||
template_version VARCHAR(64) NOT NULL COMMENT '模板版本',
|
||
name VARCHAR(160) NOT NULL COMMENT '模板名称',
|
||
status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT 'active/disabled',
|
||
rule_json JSON NOT NULL COMMENT '完整策略规则 JSON，发布时编译到 owner service 运行表',
|
||
description VARCHAR(512) NOT NULL DEFAULT '' COMMENT '策略边界和适用说明',
|
||
created_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
updated_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
created_at_ms BIGINT NOT NULL,
|
||
updated_at_ms BIGINT NOT NULL,
|
||
PRIMARY KEY (template_id),
|
||
UNIQUE KEY uk_admin_policy_template_version (template_code, template_version),
|
||
KEY idx_admin_policy_templates_status (status, updated_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益政策模板表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_policy_template_versions (
|
||
version_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
template_code VARCHAR(96) NOT NULL COMMENT '模板编码',
|
||
template_version VARCHAR(64) NOT NULL COMMENT '模板版本',
|
||
status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT 'active/disabled',
|
||
rule_json JSON NOT NULL COMMENT '完整策略规则 JSON，发布时编译到 owner service 运行表',
|
||
created_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
updated_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
created_at_ms BIGINT NOT NULL,
|
||
updated_at_ms BIGINT NOT NULL,
|
||
PRIMARY KEY (version_id),
|
||
UNIQUE KEY uk_admin_policy_template_version_row (template_code, template_version),
|
||
KEY idx_admin_policy_template_versions_status (template_code, status, updated_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益政策模板版本表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_policy_instances (
|
||
instance_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
app_code VARCHAR(32) NOT NULL COMMENT '应用编码',
|
||
instance_code VARCHAR(96) NOT NULL COMMENT '实例编码',
|
||
template_code VARCHAR(96) NOT NULL COMMENT '模板编码快照',
|
||
template_version VARCHAR(64) NOT NULL COMMENT '模板版本快照',
|
||
region_scope VARCHAR(128) NOT NULL DEFAULT 'all_active_regions' COMMENT 'all_active_regions 或 region_id:<id>',
|
||
status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT 'active/disabled',
|
||
effective_from_ms BIGINT NOT NULL DEFAULT 0 COMMENT 'UTC epoch ms',
|
||
effective_to_ms BIGINT NOT NULL DEFAULT 0 COMMENT '0 表示长期有效',
|
||
publish_status VARCHAR(24) NOT NULL DEFAULT 'draft' COMMENT 'draft/published/failed',
|
||
publish_error VARCHAR(512) NOT NULL DEFAULT '',
|
||
last_published_at_ms BIGINT NOT NULL DEFAULT 0,
|
||
created_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
updated_by_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
created_at_ms BIGINT NOT NULL,
|
||
updated_at_ms BIGINT NOT NULL,
|
||
PRIMARY KEY (instance_id),
|
||
UNIQUE KEY uk_admin_policy_instance_code (app_code, instance_code),
|
||
KEY idx_admin_policy_instances_template (template_code, template_version),
|
||
KEY idx_admin_policy_instances_app_status (app_code, status, publish_status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益政策实例表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_policy_publish_jobs (
|
||
job_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
instance_id BIGINT UNSIGNED NOT NULL,
|
||
app_code VARCHAR(32) NOT NULL,
|
||
instance_code VARCHAR(96) NOT NULL,
|
||
template_code VARCHAR(96) NOT NULL,
|
||
template_version VARCHAR(64) NOT NULL,
|
||
status VARCHAR(24) NOT NULL DEFAULT 'pending' COMMENT 'pending/succeeded/failed',
|
||
target_count INT NOT NULL DEFAULT 0,
|
||
success_count INT NOT NULL DEFAULT 0,
|
||
failure_count INT NOT NULL DEFAULT 0,
|
||
error_message VARCHAR(512) NOT NULL DEFAULT '',
|
||
operator_admin_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
created_at_ms BIGINT NOT NULL,
|
||
updated_at_ms BIGINT NOT NULL,
|
||
PRIMARY KEY (job_id),
|
||
KEY idx_admin_policy_publish_jobs_instance (instance_id, created_at_ms),
|
||
KEY idx_admin_policy_publish_jobs_status (status, updated_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益政策发布任务表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_policy_publish_items (
|
||
item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
job_id BIGINT UNSIGNED NOT NULL,
|
||
owner_service VARCHAR(32) NOT NULL COMMENT 'wallet/activity/game',
|
||
app_code VARCHAR(32) NOT NULL,
|
||
region_id BIGINT NOT NULL DEFAULT 0,
|
||
status VARCHAR(24) NOT NULL DEFAULT 'pending',
|
||
error_message VARCHAR(512) NOT NULL DEFAULT '',
|
||
created_at_ms BIGINT NOT NULL,
|
||
updated_at_ms BIGINT NOT NULL,
|
||
PRIMARY KEY (item_id),
|
||
KEY idx_admin_policy_publish_items_job (job_id, owner_service, status),
|
||
KEY idx_admin_policy_publish_items_region (app_code, region_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='收益政策发布明细表';
|
||
|
||
INSERT INTO admin_policy_templates (
|
||
template_code, template_version, name, status, rule_json, description,
|
||
created_by_admin_id, updated_by_admin_id, created_at_ms, updated_at_ms
|
||
) VALUES (
|
||
'first_google70000_coin_seller_92000_100000_v1',
|
||
'v1',
|
||
'第一套政策：Google 70000 + 币商 92000-100000',
|
||
'active',
|
||
JSON_OBJECT(
|
||
'points_per_usd', 100000,
|
||
'allow_self_brushing', TRUE,
|
||
'google_coin_per_usd', 70000,
|
||
'host', JSON_OBJECT('point_ratio_percent', 70, 'affects_room_heat', FALSE, 'minimum_withdraw_points', 1000000, 'withdraw_fee_bps', 500),
|
||
'agent', JSON_OBJECT(
|
||
'period', 'rolling_30d',
|
||
'support_ratio_percent', 8,
|
||
'support_days', 30,
|
||
'qualified_host_min', 3,
|
||
'levels', JSON_ARRAY(
|
||
JSON_OBJECT('level', 1, 'name', 'Lv1', 'min_usd', 0, 'max_usd', 200, 'ratio_percent', 4, 'qualified_host_min', 3),
|
||
JSON_OBJECT('level', 2, 'name', 'Lv2', 'min_usd', 200, 'max_usd', 500, 'ratio_percent', 8, 'qualified_host_min', 5),
|
||
JSON_OBJECT('level', 3, 'name', 'Lv3', 'min_usd', 500, 'max_usd', 2000, 'ratio_percent', 16, 'qualified_host_min', 10),
|
||
JSON_OBJECT('level', 4, 'name', 'Lv4', 'min_usd', 2000, 'max_usd', 0, 'ratio_percent', 20, 'manual_review', TRUE)
|
||
),
|
||
'differential', JSON_OBJECT('enabled', TRUE, 'max_depth', 2, 'cross_level_formula', 'parent_minus_direct_child')
|
||
),
|
||
'bd', JSON_OBJECT(
|
||
'period', 'utc_calendar_month',
|
||
'qualified_agency_min', 3,
|
||
'qualified_host_per_agency_min', 3,
|
||
'base', 'host_effective_income',
|
||
'levels', JSON_ARRAY(
|
||
JSON_OBJECT('level', 1, 'name', '测试BD-1', 'threshold_usd', 100, 'salary_usd', 5, 'commission_percent', 1.0),
|
||
JSON_OBJECT('level', 2, 'name', '测试BD-2', 'threshold_usd', 300, 'salary_usd', 15, 'commission_percent', 1.0),
|
||
JSON_OBJECT('level', 3, 'name', '正式BD-1', 'threshold_usd', 700, 'salary_usd', 35, 'commission_percent', 1.5),
|
||
JSON_OBJECT('level', 4, 'name', '正式BD-2', 'threshold_usd', 1000, 'salary_usd', 50, 'commission_percent', 1.5),
|
||
JSON_OBJECT('level', 5, 'name', '高级BD-1', 'threshold_usd', 2000, 'salary_usd', 100, 'commission_percent', 2.0),
|
||
JSON_OBJECT('level', 6, 'name', '高级BD-2', 'threshold_usd', 3000, 'salary_usd', 150, 'commission_percent', 2.0),
|
||
JSON_OBJECT('level', 7, 'name', '普通BD-1', 'threshold_usd', 5000, 'salary_usd', 250, 'commission_percent', 2.5),
|
||
JSON_OBJECT('level', 8, 'name', '普通BD-2', 'threshold_usd', 7000, 'salary_usd', 350, 'commission_percent', 2.5),
|
||
JSON_OBJECT('level', 9, 'name', '普通Admin-1', 'threshold_usd', 10000, 'salary_usd', 500, 'commission_percent', 3.0),
|
||
JSON_OBJECT('level', 10, 'name', '普通Admin-2', 'threshold_usd', 20000, 'salary_usd', 1000, 'commission_percent', 3.0),
|
||
JSON_OBJECT('level', 11, 'name', '超级Admin-1', 'threshold_usd', 30000, 'salary_usd', 1500, 'commission_percent', 3.0),
|
||
JSON_OBJECT('level', 12, 'name', '超级Admin-2', 'threshold_usd', 40000, 'salary_usd', 2000, 'commission_percent', 3.0)
|
||
)
|
||
),
|
||
'manager', JSON_OBJECT('coin_seller_recharge_threshold_usd', 2000, 'coin_seller_recharge_commission_percent', 3, 'host_point_commission_percent', 2),
|
||
'coin_seller', JSON_OBJECT(
|
||
'withdraw_order_enabled', TRUE,
|
||
'levels', JSON_ARRAY(
|
||
JSON_OBJECT('name', 'Beginner', 'threshold_usd', 100, 'coin_per_usd', 92000, 'withdraw_order_enabled', FALSE),
|
||
JSON_OBJECT('name', 'Standard', 'threshold_usd', 500, 'coin_per_usd', 96000, 'withdraw_order_enabled', FALSE),
|
||
JSON_OBJECT('name', 'Senior', 'single_recharge_threshold_usd', 1000, 'coin_per_usd', 100000, 'withdraw_order_enabled', TRUE)
|
||
),
|
||
'order_timeout_seconds', 7200,
|
||
'seller_point_settle_percent', 95,
|
||
'seller_point_reward_percent', 5,
|
||
'success_rate_close_threshold_percent', 95
|
||
),
|
||
'tasks', JSON_OBJECT('reward_asset_type', 'POINT', 'new_host_7d_max_points', 350000),
|
||
'game_invite', JSON_OBJECT('enabled', TRUE, 'period', 'rolling_30d')
|
||
),
|
||
'新文档口径：允许自刷；BD 按 UTC 当月自然月；Huwaa 使用 POINT/COIN_SELLER_POINT。',
|
||
0, 0, @now_ms, @now_ms
|
||
) ON DUPLICATE KEY UPDATE
|
||
name = VALUES(name),
|
||
status = VALUES(status),
|
||
rule_json = VALUES(rule_json),
|
||
description = VALUES(description),
|
||
updated_at_ms = @now_ms;
|
||
|
||
INSERT INTO admin_policy_template_versions (
|
||
template_code, template_version, status, rule_json,
|
||
created_by_admin_id, updated_by_admin_id, created_at_ms, updated_at_ms
|
||
)
|
||
SELECT
|
||
template_code, template_version, status, rule_json,
|
||
created_by_admin_id, updated_by_admin_id, created_at_ms, updated_at_ms
|
||
FROM admin_policy_templates
|
||
WHERE template_code = 'first_google70000_coin_seller_92000_100000_v1'
|
||
AND template_version = 'v1'
|
||
ON DUPLICATE KEY UPDATE
|
||
status = VALUES(status),
|
||
rule_json = VALUES(rule_json),
|
||
updated_by_admin_id = VALUES(updated_by_admin_id),
|
||
updated_at_ms = @now_ms;
|
||
|
||
INSERT INTO admin_policy_instances (
|
||
app_code, instance_code, template_code, template_version, region_scope, status,
|
||
effective_from_ms, effective_to_ms, publish_status, created_by_admin_id, updated_by_admin_id,
|
||
created_at_ms, updated_at_ms
|
||
) VALUES (
|
||
'huwaa',
|
||
'huwaa_first_google70000_coin_seller_92000_100000',
|
||
'first_google70000_coin_seller_92000_100000_v1',
|
||
'v1',
|
||
'all_active_regions',
|
||
'active',
|
||
@now_ms,
|
||
0,
|
||
'draft',
|
||
0,
|
||
0,
|
||
@now_ms,
|
||
@now_ms
|
||
) ON DUPLICATE KEY UPDATE
|
||
template_code = VALUES(template_code),
|
||
template_version = VALUES(template_version),
|
||
region_scope = VALUES(region_scope),
|
||
status = VALUES(status),
|
||
effective_to_ms = VALUES(effective_to_ms),
|
||
updated_at_ms = @now_ms;
|
||
|
||
INSERT INTO admin_permissions (name, code, kind, description, created_at_ms, updated_at_ms) VALUES
|
||
('收益政策模板查看', 'policy-template:view', 'menu', '允许查看收益政策模板和实例', @now_ms, @now_ms),
|
||
('收益政策模板创建', 'policy-template:create', 'button', '允许创建收益政策模板', @now_ms, @now_ms),
|
||
('收益政策实例创建', 'policy-instance:create', 'button', '允许创建收益政策实例', @now_ms, @now_ms),
|
||
('收益政策实例更新', 'policy-instance:update', 'button', '允许更新收益政策实例状态', @now_ms, @now_ms),
|
||
('收益政策实例发布', 'policy-instance:publish', 'button', '允许发布收益政策实例到运行侧', @now_ms, @now_ms)
|
||
ON DUPLICATE KEY UPDATE
|
||
name = VALUES(name),
|
||
kind = VALUES(kind),
|
||
description = VALUES(description),
|
||
updated_at_ms = @now_ms;
|
||
|
||
INSERT INTO admin_menus (parent_id, title, code, path, icon, permission_code, sort, visible, created_at_ms, updated_at_ms)
|
||
SELECT parent.id, '收益政策模板', 'policy-template', '/policy/templates', 'settings', 'policy-template:view', 88, TRUE, @now_ms, @now_ms
|
||
FROM admin_menus parent
|
||
WHERE parent.code = 'operations'
|
||
ON DUPLICATE KEY UPDATE
|
||
parent_id = VALUES(parent_id),
|
||
title = VALUES(title),
|
||
path = VALUES(path),
|
||
icon = VALUES(icon),
|
||
permission_code = VALUES(permission_code),
|
||
sort = VALUES(sort),
|
||
visible = VALUES(visible),
|
||
updated_at_ms = @now_ms;
|
||
|
||
INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
|
||
SELECT r.id, p.id
|
||
FROM admin_roles r
|
||
JOIN admin_permissions p
|
||
WHERE r.code IN ('platform-admin', 'ops-admin')
|
||
AND p.code IN ('policy-template:view', 'policy-template:create', 'policy-instance:create', 'policy-instance:update', 'policy-instance:publish');
|
||
|
||
INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
|
||
SELECT r.id, p.id
|
||
FROM admin_roles r
|
||
JOIN admin_permissions p
|
||
WHERE r.code IN ('auditor', 'readonly')
|
||
AND p.code = 'policy-template:view';
|