yumi-java/sql/20260506_likei_google_product_config.sql

-- LIKEI Google Play in-app product configs.
-- This script is idempotent for the two product_package values below:
-- it keeps one row per sys_origin + platform + product_package, updates it,
-- removes duplicate rows for those packages, and inserts missing rows.
--
-- Production LIKEI regions verified from mongo-1 Mongo sys_region_config:
--   7455586935705044691  Pakistan - 03
--   2047598855125528577  Pakistan - rolex
--   2046578237923979266  Europe Region
--   2046488527570530306  Africa Region
--   2046067717605224449  Southeast Asia Region
--   2046067036517363714  Middle East Region
--   2046066409959649281  Turkey Region
--   2045389832842178561  South Asia
--   69d88849bdbcc07ebc3f118c  Default
--
-- For production, confirm the LIKEI region ids in Mongo first:
--   db.sys_region_config.find(
--     {sysOrigin: "LIKEI", del: false},
--     {_id: 1, regionName: 1, regionCode: 1}
--   )
-- If production has different region ids, replace @target_regions before running.

START TRANSACTION;

SET @sys_origin := CONVERT('LIKEI' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @platform := CONVERT('GOOGLE' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @obsolete_product_package := CONVERT('coins.800000' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @target_regions := CONVERT('7455586935705044691,2047598855125528577,2046578237923979266,2046488527570530306,2046067717605224449,2046067036517363714,2046066409959649281,2045389832842178561,69d88849bdbcc07ebc3f118c' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @country_codes := NULL;
SET @operator_id := 0;

CREATE TEMPORARY TABLE tmp_likei_google_product_config (
  row_no INT NOT NULL PRIMARY KEY,
  product_package VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  obtain_candy DECIMAL(10, 2) NOT NULL,
  reward_candy DECIMAL(10, 2) NOT NULL DEFAULT 0,
  sort_no INT NOT NULL,
  description VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
);

INSERT INTO tmp_likei_google_product_config (
  row_no,
  product_package,
  unit_price,
  obtain_candy,
  reward_candy,
  sort_no,
  description
) VALUES
  (1, 'coins.80000', 1.00, 80000.00, 0.00, 1, '80000 coins'),
  (2, 'coins.240000', 3.00, 240000.00, 0.00, 2, '240000 coins');

DELETE FROM sys_product_config_v2
WHERE sys_origin = @sys_origin
  AND platform = @platform
  AND product_package = @obsolete_product_package;

DELETE FROM sys_product_config_package
WHERE product_package = @obsolete_product_package;

CREATE TEMPORARY TABLE tmp_likei_google_product_keep AS
SELECT
  t.product_package,
  MIN(t.id) AS keep_id
FROM sys_product_config_v2 t
JOIN tmp_likei_google_product_config s
  ON s.product_package = t.product_package
WHERE t.sys_origin = @sys_origin
  AND t.platform = @platform
GROUP BY t.product_package;

DELETE t
FROM sys_product_config_v2 t
JOIN tmp_likei_google_product_keep k
  ON k.product_package = t.product_package
WHERE t.sys_origin = @sys_origin
  AND t.platform = @platform
  AND t.id <> k.keep_id;

UPDATE sys_product_config_v2 t
JOIN tmp_likei_google_product_keep k
  ON k.keep_id = t.id
JOIN tmp_likei_google_product_config s
  ON s.product_package = k.product_package
SET
  t.description = s.description,
  t.unit_price = s.unit_price,
  t.obtain_candy = s.obtain_candy,
  t.reward_candy = s.reward_candy,
  t.regions = @target_regions,
  t.country_codes = @country_codes,
  t.sort = s.sort_no,
  t.is_showcase = 1,
  t.update_time = CURRENT_TIMESTAMP,
  t.update_user = @operator_id;

SET @next_product_config_id := (
  SELECT COALESCE(MAX(id), 0)
  FROM sys_product_config_v2
);

INSERT INTO sys_product_config_v2 (
  id,
  sys_origin,
  description,
  product_package,
  unit_price,
  obtain_candy,
  reward_candy,
  platform,
  regions,
  country_codes,
  sort,
  is_showcase,
  create_time,
  update_time,
  create_user,
  update_user
)
SELECT
  (@next_product_config_id := @next_product_config_id + 1) AS id,
  @sys_origin,
  s.description,
  s.product_package,
  s.unit_price,
  s.obtain_candy,
  s.reward_candy,
  @platform,
  @target_regions,
  @country_codes,
  s.sort_no,
  1,
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP,
  @operator_id,
  @operator_id
FROM tmp_likei_google_product_config s
LEFT JOIN tmp_likei_google_product_keep k
  ON k.product_package = s.product_package
WHERE k.keep_id IS NULL;

SELECT
  id,
  sys_origin,
  product_package,
  unit_price,
  obtain_candy,
  reward_candy,
  platform,
  regions,
  country_codes,
  sort,
  is_showcase
FROM sys_product_config_v2
WHERE sys_origin = @sys_origin
  AND platform = @platform
  AND product_package IN (
    SELECT product_package
    FROM tmp_likei_google_product_config
  )
ORDER BY sort, unit_price;

DROP TEMPORARY TABLE IF EXISTS tmp_likei_google_product_keep;
DROP TEMPORARY TABLE IF EXISTS tmp_likei_google_product_config;

COMMIT;