-- 将旧版 tt_code.code_technology 中的混合技术栈拆分到前端、后端和数据库字段。
--
-- 安全性：
-- 1. 只填充 NULL 或空字符串，不覆盖已经维护的新字段；
-- 2. 保留 code_technology 原值，便于核对和回退；
-- 3. 可重复执行，第二次执行不会再次修改已经填充的数据。

START TRANSACTION;

UPDATE `tt_code`
SET
    `frontend_technology` = CASE
        WHEN NULLIF(TRIM(`frontend_technology`), '') IS NOT NULL
            THEN `frontend_technology`
        WHEN LOWER(`code_technology`) LIKE '%vue3%'
            THEN CONCAT(
                'Vue 3',
                IF(LOWER(`code_technology`) LIKE '%elementui%', ' + Element UI', ''),
                IF(LOWER(`code_technology`) LIKE '%nodejs14%', ' + Node.js 14',
                    IF(LOWER(`code_technology`) LIKE '%nodejs%', ' + Node.js', '')),
                IF(`code_technology` LIKE '%微信小程序%', ' + 微信小程序', ''),
                IF(`code_technology` LIKE '%原生小程序%', ' + 原生小程序', '')
            )
        WHEN LOWER(`code_technology`) LIKE '%vue2%'
            THEN CONCAT(
                'Vue 2',
                IF(LOWER(`code_technology`) LIKE '%elementui%', ' + Element UI', ''),
                IF(LOWER(`code_technology`) LIKE '%nodejs14%', ' + Node.js 14',
                    IF(LOWER(`code_technology`) LIKE '%nodejs%', ' + Node.js', '')),
                IF(`code_technology` LIKE '%微信小程序%', ' + 微信小程序', ''),
                IF(`code_technology` LIKE '%原生小程序%', ' + 原生小程序', '')
            )
        WHEN LOWER(`code_technology`) LIKE '%vue%'
            THEN CONCAT(
                'Vue',
                IF(LOWER(`code_technology`) LIKE '%elementui%', ' + Element UI', ''),
                IF(LOWER(`code_technology`) LIKE '%nodejs14%', ' + Node.js 14',
                    IF(LOWER(`code_technology`) LIKE '%nodejs%', ' + Node.js', ''))
            )
        WHEN LOWER(`code_technology`) LIKE '%thymeleaf%'
            THEN CONCAT(
                'Thymeleaf',
                IF(LOWER(`code_technology`) LIKE '%bootstrap%', ' + Bootstrap', ''),
                IF(LOWER(`code_technology`) LIKE '%jquery%', ' + jQuery', '')
            )
        WHEN LOWER(`code_technology`) LIKE '%jsp%'
            THEN CONCAT(
                'JSP',
                IF(LOWER(`code_technology`) LIKE '%jstl%', ' + JSTL', ''),
                IF(LOWER(`code_technology`) LIKE '%bootstrap%', ' + Bootstrap', ''),
                IF(LOWER(`code_technology`) LIKE '%layui%', ' + Layui', '')
            )
        WHEN LOWER(`code_technology`) LIKE '%html%'
            THEN 'HTML'
        WHEN LOWER(`code_technology`) LIKE '%bootstrap%'
            THEN CONCAT(
                'Bootstrap',
                IF(LOWER(`code_technology`) LIKE '%highcharts%', ' + Highcharts', '')
            )
        ELSE '未单独记录'
    END,
    `backend_technology` = CASE
        WHEN NULLIF(TRIM(`backend_technology`), '') IS NOT NULL
            THEN `backend_technology`
        WHEN LOWER(`code_technology`) LIKE '%php%'
            THEN 'PHP'
        WHEN LOWER(`code_technology`) LIKE '%servlet%'
            THEN CONCAT(
                'Java 8 + Servlet',
                IF(LOWER(`code_technology`) LIKE '%jdbc%', ' + JDBC', '')
            )
        WHEN LOWER(`code_technology`) LIKE '%springboot2%'
            THEN CONCAT(
                'Java 8 + Spring Boot 2',
                IF(LOWER(`code_technology`) LIKE '%mybatis-plus%'
                    OR LOWER(`code_technology`) LIKE '%mybatisplus%', ' + MyBatis-Plus',
                    IF(LOWER(`code_technology`) LIKE '%mybatis%', ' + MyBatis', ''))
            )
        WHEN LOWER(`code_technology`) LIKE '%springboot%'
            THEN CONCAT(
                'Java 8 + Spring Boot',
                IF(LOWER(`code_technology`) LIKE '%mybatis-plus%'
                    OR LOWER(`code_technology`) LIKE '%mybatisplus%', ' + MyBatis-Plus',
                    IF(LOWER(`code_technology`) LIKE '%mybatis%', ' + MyBatis', ''))
            )
        WHEN LOWER(`code_technology`) LIKE '%springmvc%'
            OR LOWER(`code_technology`) LIKE '%springmmvc%'
            OR LOWER(`code_technology`) LIKE '%ssm%'
            THEN CONCAT(
                'Java 8 + Spring + Spring MVC',
                IF(LOWER(`code_technology`) LIKE '%mybatis-plus%'
                    OR LOWER(`code_technology`) LIKE '%mybatisplus%', ' + MyBatis-Plus',
                    IF(LOWER(`code_technology`) LIKE '%mybatis%'
                        OR LOWER(`code_technology`) LIKE '%mabtis%', ' + MyBatis', ''))
            )
        ELSE '未单独记录'
    END,
    `database_technology` = CASE
        WHEN NULLIF(TRIM(`database_technology`), '') IS NOT NULL
            THEN `database_technology`
        WHEN LOWER(`code_environment`) LIKE '%mysql8.0%'
            OR LOWER(`code_environment`) LIKE '%mysql 8.0%'
            OR LOWER(`code_environment`) LIKE '%mysql8%'
            OR LOWER(`code_environment`) LIKE '%mysql 8%'
            THEN CONCAT(
                'MySQL 8.0',
                IF(LOWER(`code_environment`) LIKE '%redis%', ' + Redis', '')
            )
        WHEN LOWER(`code_environment`) LIKE '%mysql5.7%'
            OR LOWER(`code_environment`) LIKE '%mysql 5.7%'
            THEN CONCAT(
                'MySQL 5.7',
                IF(LOWER(`code_environment`) LIKE '%redis%', ' + Redis', '')
            )
        WHEN LOWER(`code_environment`) LIKE '%mysql%'
            OR LOWER(`code_technology`) LIKE '%mysql%'
            THEN CONCAT(
                'MySQL',
                IF(LOWER(`code_environment`) LIKE '%redis%', ' + Redis', '')
            )
        ELSE '未单独记录'
    END
WHERE NULLIF(TRIM(`code_technology`), '') IS NOT NULL
  AND (
      NULLIF(TRIM(`frontend_technology`), '') IS NULL
      OR NULLIF(TRIM(`backend_technology`), '') IS NULL
      OR NULLIF(TRIM(`database_technology`), '') IS NULL
  );

SELECT ROW_COUNT() AS `backfilled_rows`;

COMMIT;

-- 执行结果检查：三个 blank_* 都应为 0。
SELECT
    COUNT(*) AS `total`,
    SUM(NULLIF(TRIM(`frontend_technology`), '') IS NULL) AS `frontend_blank`,
    SUM(NULLIF(TRIM(`backend_technology`), '') IS NULL) AS `backend_blank`,
    SUM(NULLIF(TRIM(`database_technology`), '') IS NULL) AS `database_blank`
FROM `tt_code`;