/**
 * Generate lobster DB garbled-text repair SQL from catalog + menu map.
 * Usage: node java/fs-service/scripts/generate-lobster-db-repair-sql.js
 * All Chinese in this file use \\u escapes (Windows-safe).
 */
const fs = require('fs');
const path = require('path');

const catalogPath = path.join(__dirname, '../src/main/java/com/fs/company/service/workflow/prompt/LobsterEnginePromptCatalog.java');
const tenantOut = path.join(__dirname, '../../fs-agent/src/main/resources/db/migration/tenant/V20260622_01__lobster_garbled_text_repair.sql');
const masterOut = path.join(__dirname, '../../sql/master/V20260622_01__lobster_garbled_text_repair.sql');

const LOBSTER_MENUS = {
  29900: '\u9f99\u8679\u5f15\u64ce',
  29901: 'AI\u751f\u4ea7\u5de5\u4f5c\u6d41',
  29902: '\u5de5\u4f5c\u6d41\u753b\u5e03',
  29903: '\u5de5\u4f5c\u6d41\u6a21\u677f\u5e93',
  29904: 'AI\u751f\u6210\u5de5\u4f5c\u6d41',
  29905: '\u5b9e\u4f8b\u76d1\u63a7',
  29906: 'AI\u4f18\u5316\u5efa\u8bae',
  29907: '\u63d0\u793a\u8bcd\u7ba1\u7406',
  29908: '\u9500\u51a0\u8bed\u6599\u5b66\u4e60',
  29909: '\u63a5\u53e3\u6ce8\u518c\u4e2d\u5fc3',
  29910: '\u6b7b\u4fe1\u961f\u5217',
  29911: '\u8282\u70b9\u5ba1\u6838',
  29912: '\u805a\u5408\u804a\u5929',
  29913: '\u6a21\u578b\u914d\u7f6e',
  29914: 'Token\u7cfb\u6570\u7ba1\u7406',
  29920: '\u6a21\u62df\u804a\u5929\u6d4b\u8bd5',
  29921: '\u8bc4\u5206\u51c6\u786e\u6027\u9a8c\u8bc1',
  29922: '\u7528\u6237\u753b\u50cf\u914d\u7f6e',
  29924: '\u6d88\u606f\u53bb\u91cd\u914d\u7f6e',
  29925: '\u654f\u611f\u8bcd\u5e93',
  29926: '\u8282\u70b9\u8be6\u60c5',
  29933: '\u79df\u6237\u8bed\u4e49\u5173\u952e\u8bcd',
  29934: '\u8eab\u4efd\u9690\u85cf\u914d\u7f6e',
  29936: '\u6e20\u9053\u63d2\u4ef6',
  29937: 'Token\u6d88\u8017\u7edf\u8ba1',
  29938: '\u5f15\u64ce\u770b\u677f',
  29939: 'AI\u8fdb\u5316\u5f15\u64ce',
  29940: '\u8282\u70b9\u80fd\u529b\u77e9\u9635',
  29941: '\u8bdd\u672f\u4f18\u5316',
  29944: '\u6e20\u9053\u914d\u7f6e',
  29946: '\u591a\u6a21\u578b\u8def\u7531\u914d\u7f6e',
  29947: '\u6267\u884c\u7b56\u7565\u914d\u7f6e',
  29948: '\u4f1a\u8bdd\u6458\u8981',
  29949: '\u5de5\u4f5c\u6d41\u6267\u884c\u4efb\u52a1',
  29950: '\u5ba2\u6237\u753b\u50cf',
  3006: '\u6d4b\u8bd5\u573a\u666f\u7ba1\u7406',
  3007: 'E2E\u8fd0\u884c\u5386\u53f2',
  3008: '\u52a8\u6001\u8282\u70b9\u5ba1\u6279',
  32481: '\u9f99\u8679\u5de5\u4f5c\u6d41'
};

const AI_SCENES = {
  workflow_llm: '\u63d0\u793a\u8bcd\u7ba1\u7406',
  summary_generation: '\u6458\u8981\u751f\u6210',
  workflow_generation: '\u5de5\u4f5c\u6d41\u751f\u6210',
  dynamic_node: '\u52a8\u6001\u8282\u70b9',
  workflow_evolution: '\u5de5\u4f5c\u6d41\u8fdb\u5316',
  quality_scoring: '\u8d28\u91cf\u8bc4\u5206',
  semantic_analysis: '\u8bed\u4e49\u5206\u6790'
};

function sqlEscape(s) {
  if (s == null) return '';
  return String(s).replace(/\\/g, '\\\\').replace(/'/g, "''");
}

function parseCatalog() {
  const src = fs.readFileSync(catalogPath, 'utf8');
  const simpleRe = /reg\(\s*"([^"]+)"\s*,\s*"([^"]+)"\s*,\s*"([^"]+)"/g;
  const rows = [];
  let m;
  while ((m = simpleRe.exec(src)) !== null) {
    rows.push({ key: m[1], name: m[2], category: m[3] });
  }
  return rows;
}

function garbledCondition(column) {
  return `(
  ${column} IS NULL OR TRIM(${column}) = ''
  OR ${column} LIKE '%?%'
  OR HEX(${column}) LIKE '3F3F%'
  OR ${column} LIKE 'u5de5%'
  OR ${column} LIKE 'u4f5c%'
  OR ${column} REGEXP '^u[0-9a-fA-F]{4}(u[0-9a-fA-F]{4})+$'
  OR ${column} LIKE '%\\\\u5de5%'
)`;
}

function menuRepairBlock(table) {
  const lines = [`-- ${table}: fix lobster menu_name`];
  for (const [id, name] of Object.entries(LOBSTER_MENUS)) {
    lines.push(
      `UPDATE ${table} SET menu_name='${sqlEscape(name)}', update_time=NOW() ` +
      `WHERE menu_id=${id} AND menu_name <> '${sqlEscape(name)}' ` +
      `AND ${garbledCondition('menu_name')};`
    );
  }
  return lines.join('\n');
}

function promptRepairBlock() {
  const rows = parseCatalog();
  const lines = ['-- lobster_system_prompt: fix garbled/empty prompt_name & prompt_category'];
  for (const row of rows) {
    lines.push(
      `UPDATE lobster_system_prompt SET prompt_name='${sqlEscape(row.name)}', ` +
      `prompt_category='${sqlEscape(row.category)}', update_time=NOW() ` +
      `WHERE prompt_key='${sqlEscape(row.key)}' AND company_id IS NULL AND (` +
      `${garbledCondition('prompt_name')} OR ${garbledCondition('prompt_category')} ` +
      `OR prompt_name <> '${sqlEscape(row.name)}' OR prompt_category <> '${sqlEscape(row.category)}');`
    );
  }
  return lines.join('\n');
}

function aiSceneBlock() {
  const lines = ['-- admin_ai_scene: fix lobster-related scene names (master DB only)'];
  for (const [code, name] of Object.entries(AI_SCENES)) {
    lines.push(
      `UPDATE admin_ai_scene SET scene_name='${sqlEscape(name)}', update_time=NOW() ` +
      `WHERE scene_code='${sqlEscape(code)}' AND (scene_name <> '${sqlEscape(name)}' OR ${garbledCondition('scene_name')});`
    );
  }
  return lines.join('\n');
}

function unicodeEscapeMenuFix() {
  return `-- Fix menu_name stored as literal u5de5... unicode escapes
UPDATE sys_menu SET menu_name = CONVERT(UNHEX('E9BE99E8909DE5BC95E6938E') USING utf8mb4)
WHERE menu_id = 29900 AND (menu_name LIKE 'u9f99u8679%' OR menu_name REGEXP '^u[0-9a-fA-F]{4}(u[0-9a-fA-F]{4})+$');

UPDATE sys_menu SET menu_name = CONVERT(UNHEX('E5B7A5E4BD9CE6B581E6A8A1E69DBF') USING utf8mb4)
WHERE menu_id IN (29903, 32481) OR menu_name LIKE 'u5de5u4f5cu6d41u6a21%';

UPDATE sys_menu SET menu_name = CONVERT(UNHEX('E5B7A5E4BD9CE6B581E689A7E8A18C') USING utf8mb4)
WHERE menu_id = 29949 OR menu_name LIKE 'u5de5u4f5cu6d41u6267%';

UPDATE sys_menu SET menu_name = CONVERT(UNHEX('E5AEA2E688B7E794BBE5838F') USING utf8mb4)
WHERE menu_id = 29950 OR menu_name LIKE 'u5ba2u6237u753b%';

UPDATE company_menu SET menu_name = CONVERT(UNHEX('E5B7A5E4BD9CE6B581E6A8A1E69DBF') USING utf8mb4)
WHERE menu_id IN (29903, 32481) OR menu_name LIKE 'u5de5u4f5cu6d41u6a21%';

UPDATE company_menu SET menu_name = CONVERT(UNHEX('E5B7A5E4BD9CE6B581E689A7E8A18C') USING utf8mb4)
WHERE menu_id = 29949 OR menu_name LIKE 'u5de5u4f5cu6d41u6267%';

UPDATE company_menu SET menu_name = CONVERT(UNHEX('E5AEA2E688B7E794BBE5838F') USING utf8mb4)
WHERE menu_id = 29950 OR menu_name LIKE 'u5ba2u6237u753b%';`;
}

function buildTenantSql() {
  return `-- Tenant DB: lobster garbled text repair (auto-generated)
-- Run via Flyway or: mysql -u... tenant_db < this file

${unicodeEscapeMenuFix()}

${menuRepairBlock('sys_menu')}

${menuRepairBlock('company_menu')}

${promptRepairBlock()}
`;
}

function buildMasterSql() {
  return `-- Master DB: lobster garbled text repair (auto-generated)

${unicodeEscapeMenuFix()}

${menuRepairBlock('sys_menu')}

${menuRepairBlock('tenant_sys_menu')}

${menuRepairBlock('tenant_company_menu')}

${aiSceneBlock()}
`;
}

fs.writeFileSync(tenantOut, buildTenantSql(), 'utf8');
fs.writeFileSync(masterOut, buildMasterSql(), 'utf8');
console.log('Wrote', tenantOut);
console.log('Wrote', masterOut);
console.log('Catalog prompts:', parseCatalog().length);