96 lines
4.0 KiB
SQL
96 lines
4.0 KiB
SQL
-- Backfill level badge resource metadata and user level display projections.
|
|
-- Run this on a MySQL account that can read hyapp_activity and update hyapp_wallet.
|
|
-- If the conflict SELECT returns rows, copy the shared resource into separate resources first.
|
|
|
|
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_level_badge_resource_refs;
|
|
|
|
CREATE TEMPORARY TABLE tmp_level_badge_resource_refs AS
|
|
SELECT
|
|
app_code,
|
|
track,
|
|
level,
|
|
CAST(JSON_UNQUOTE(JSON_EXTRACT(display_config_json, '$.long_badge_resource_id')) AS UNSIGNED) AS resource_id
|
|
FROM hyapp_activity.growth_level_rules
|
|
WHERE status = 'active'
|
|
AND COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(display_config_json, '$.long_badge_resource_id')) AS UNSIGNED), 0) > 0;
|
|
|
|
SELECT
|
|
resource_id,
|
|
GROUP_CONCAT(DISTINCT track ORDER BY track) AS tracks,
|
|
COUNT(DISTINCT track) AS track_count
|
|
FROM tmp_level_badge_resource_refs
|
|
GROUP BY resource_id
|
|
HAVING COUNT(DISTINCT track) > 1;
|
|
|
|
UPDATE hyapp_wallet.resources AS r
|
|
JOIN (
|
|
SELECT app_code, resource_id, MIN(track) AS track, COUNT(DISTINCT track) AS track_count
|
|
FROM tmp_level_badge_resource_refs
|
|
GROUP BY app_code, resource_id
|
|
) AS refs
|
|
ON refs.app_code = r.app_code AND refs.resource_id = r.resource_id
|
|
SET r.metadata_json = JSON_SET(
|
|
COALESCE(r.metadata_json, JSON_OBJECT()),
|
|
'$.badge_form', 'strip',
|
|
'$.badge_kind', 'level',
|
|
'$.level_track', refs.track
|
|
),
|
|
r.updated_at_ms = UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000
|
|
WHERE refs.track_count = 1
|
|
AND r.resource_type = 'badge';
|
|
|
|
UPDATE hyapp_activity.user_level_display_profiles AS p
|
|
LEFT JOIN (
|
|
SELECT a.app_code, a.user_id, refs.level, refs.resource_id
|
|
FROM hyapp_activity.user_growth_level_accounts AS a
|
|
JOIN tmp_level_badge_resource_refs AS refs
|
|
ON refs.app_code = a.app_code AND refs.track = a.track AND refs.level <= a.current_level
|
|
LEFT JOIN tmp_level_badge_resource_refs AS higher
|
|
ON higher.app_code = a.app_code
|
|
AND higher.track = a.track
|
|
AND higher.level <= a.current_level
|
|
AND higher.level > refs.level
|
|
WHERE a.track = 'wealth' AND higher.resource_id IS NULL
|
|
) AS picked
|
|
ON picked.app_code = p.app_code AND picked.user_id = p.user_id
|
|
SET p.wealth_badge_resource_id = COALESCE(picked.resource_id, 0),
|
|
p.wealth_badge_source_level = COALESCE(picked.level, 0),
|
|
p.updated_at_ms = UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000;
|
|
UPDATE hyapp_activity.user_level_display_profiles AS p
|
|
LEFT JOIN (
|
|
SELECT a.app_code, a.user_id, refs.level, refs.resource_id
|
|
FROM hyapp_activity.user_growth_level_accounts AS a
|
|
JOIN tmp_level_badge_resource_refs AS refs
|
|
ON refs.app_code = a.app_code AND refs.track = a.track AND refs.level <= a.current_level
|
|
LEFT JOIN tmp_level_badge_resource_refs AS higher
|
|
ON higher.app_code = a.app_code
|
|
AND higher.track = a.track
|
|
AND higher.level <= a.current_level
|
|
AND higher.level > refs.level
|
|
WHERE a.track = 'game' AND higher.resource_id IS NULL
|
|
) AS picked
|
|
ON picked.app_code = p.app_code AND picked.user_id = p.user_id
|
|
SET p.game_badge_resource_id = COALESCE(picked.resource_id, 0),
|
|
p.game_badge_source_level = COALESCE(picked.level, 0),
|
|
p.updated_at_ms = UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000;
|
|
|
|
UPDATE hyapp_activity.user_level_display_profiles AS p
|
|
LEFT JOIN (
|
|
SELECT a.app_code, a.user_id, refs.level, refs.resource_id
|
|
FROM hyapp_activity.user_growth_level_accounts AS a
|
|
JOIN tmp_level_badge_resource_refs AS refs
|
|
ON refs.app_code = a.app_code AND refs.track = a.track AND refs.level <= a.current_level
|
|
LEFT JOIN tmp_level_badge_resource_refs AS higher
|
|
ON higher.app_code = a.app_code
|
|
AND higher.track = a.track
|
|
AND higher.level <= a.current_level
|
|
AND higher.level > refs.level
|
|
WHERE a.track = 'charm' AND higher.resource_id IS NULL
|
|
) AS picked
|
|
ON picked.app_code = p.app_code AND picked.user_id = p.user_id
|
|
SET p.charm_badge_resource_id = COALESCE(picked.resource_id, 0),
|
|
p.charm_badge_source_level = COALESCE(picked.level, 0),
|
|
p.updated_at_ms = UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000;
|