import fs from "fs";

const transcriptPath =
  "C:/Users/15850/.cursor/projects/d-DEMONET/agent-transcripts/b932712b-5542-4604-92f5-cfdf726de39a/b932712b-5542-4604-92f5-cfdf726de39a.jsonl";
const base = "d:/DEMONET/doc/migrations/";
const raw = fs.readFileSync(transcriptPath, "utf8");
const line = raw.split(/\r?\n/).find((l) => l.startsWith('{"role":"user"') && l.includes("WorkOrdDetail"));
if (!line) {
  console.error("transcript line not found");
  process.exit(1);
}
const o = JSON.parse(line);
let c = o.message.content[0].text;
c = c.replace(/^<user_query>\n/, "").replace(/\n<\/user_query>$/, "");

const i1 = c.indexOf("CREATE TABLE `aidopdev`.`WorkOrdDetail`");
const i2 = c.indexOf("CREATE PROCEDURE `aidopdev`.`pr_Mes_LinkagePlan`");
if (i1 < 0 || i2 < 0) {
  console.error("markers", i1, i2);
  process.exit(1);
}
let workordSql = c.slice(i1, i2).trim();
const rest = c.slice(i2).trim();
const iv = rest.indexOf("CREATE ALGORITHM");
const procSql = iv > 0 ? rest.slice(0, iv).trim() : rest;
const viewSql = iv > 0 ? rest.slice(iv).trim() : "";

workordSql = workordSql.replace(/`aidopdev`\./g, "");

function addPkToTable(sql, tableName) {
  const marker = `CREATE TABLE \`${tableName}\``;
  const start = sql.indexOf(marker);
  if (start < 0) return sql;
  const nextCreate = sql.indexOf("CREATE TABLE `", start + marker.length);
  const end = nextCreate < 0 ? sql.length : nextCreate;
  let block = sql.slice(start, end);
  if (block.includes("PRIMARY KEY")) return sql;
  const closeIdx = block.lastIndexOf("\n);");
  if (closeIdx < 0) return sql;
  block = block.slice(0, closeIdx) + ",\n  PRIMARY KEY (`RecID`)" + block.slice(closeIdx);
  return sql.slice(0, start) + block + sql.slice(end);
}

for (const t of ["WorkOrdDetail", "WorkOrdMaster", "WorkOrdRouting", "WorkOrdSetupTimeRecord"]) {
  workordSql = addPkToTable(workordSql, t);
}

const part4b =
  `SET NAMES utf8mb4;\n` +
  `SET FOREIGN_KEY_CHECKS=0;\n` +
  `DROP TABLE IF EXISTS \`WorkOrdRouting\`;\n` +
  `DROP TABLE IF EXISTS \`WorkOrdDetail\`;\n` +
  `DROP TABLE IF EXISTS \`WorkOrdMaster\`;\n` +
  `DROP TABLE IF EXISTS \`WorkOrdSetupTimeRecord\`;\n` +
  workordSql +
  `\nSET FOREIGN_KEY_CHECKS=1;\n`;

fs.writeFileSync(base + "2026-04-17_aidopdev_linkage_ddl_part4b_workord.sql", part4b, "utf8");

const procBody = procSql.replace(/\s*;\s*$/, "");
const procFile = `DELIMITER //\nDROP PROCEDURE IF EXISTS \`pr_Mes_LinkagePlan\`//\n${procBody}//\nDELIMITER ;\n`;
fs.writeFileSync(base + "2026-04-17_aidopdev_linkage_ddl_part6_procedure.sql", procFile, "utf8");
fs.writeFileSync(base + "_extracted_view_raw.txt", viewSql, "utf8");

console.log("ok workord", workordSql.length, "proc", procSql.length);