// Restore book 43 from local DB to production
const mysql = require('../server/node_modules/mysql2/promise');
const fs = require('fs');

function esc(str) {
  if (str === null || str === undefined) return 'NULL';
  return "'" + String(str).replace(/\\/g, '\\\\').replace(/'/g, "\\'") + "'";
}

function fmtDate(d) {
  if (!d) return null;
  // MySQL datetime format
  const dt = new Date(d);
  const pad = n => String(n).padStart(2, '0');
  return `${dt.getFullYear()}-${pad(dt.getMonth()+1)}-${pad(dt.getDate())} ${pad(dt.getHours())}:${pad(dt.getMinutes())}:${pad(dt.getSeconds())}`;
}

(async () => {
  const c = await mysql.createConnection('mysql://root:123456@localhost:3306/audio_book');
  const [books] = await c.execute('SELECT * FROM Book WHERE id = 43');
  const [chapters] = await c.execute('SELECT * FROM BookChapter WHERE bookId = 43');
  await c.end();

  if (books.length === 0) {
    console.error('Book 43 not found in local DB!');
    process.exit(1);
  }

  const b = books[0];
  const lines = [];

  // Insert Book with explicit id
  lines.push('-- Restore Book 43');
  lines.push('INSERT INTO Book (id, userId, title, subtitle, description, targetAudience, style, bookScale, totalChapters, estimatedWords, progress, isPublished, genStage, autoGenerateContent, autoGenerateAudio, voiceSpeed, outlineJson, bookAnalysis, createdAt, updatedAt) VALUES (');
  lines.push('  43,');
  lines.push('  1,');
  lines.push('  ' + esc(b.title) + ',');
  lines.push('  ' + esc(b.subtitle) + ',');
  lines.push('  ' + esc(b.description) + ',');
  lines.push('  ' + esc(b.targetAudience) + ',');
  lines.push('  ' + esc(b.style) + ',');
  lines.push('  ' + esc(b.bookScale) + ',');
  lines.push('  ' + b.totalChapters + ',');
  lines.push('  ' + b.estimatedWords + ',');
  lines.push('  ' + b.progress + ',');
  lines.push('  ' + (b.isPublished ? 1 : 0) + ',');
  lines.push('  ' + esc(b.genStage) + ',');
  lines.push('  ' + (b.autoGenerateContent ? 1 : 0) + ',');
  lines.push('  ' + (b.autoGenerateAudio ? 1 : 0) + ',');
  lines.push('  ' + b.voiceSpeed + ',');
  lines.push('  ' + esc(b.outlineJson) + ',');
  lines.push('  ' + esc(b.bookAnalysis) + ',');
  lines.push('  ' + esc(fmtDate(b.createdAt)) + ',');
  lines.push('  ' + esc(fmtDate(b.updatedAt)));
  lines.push(');');
  lines.push('');

  // Insert Chapters
  chapters.forEach(ch => {
    lines.push(`-- Restore Chapter ${ch.id}`);
    lines.push('INSERT INTO BookChapter (id, bookId, parentId, level, number, title, summary, keyPoints, estimatedWords, content, wordCount, genStage, audioUrl, audioDuration, isPublic, generatedAt) VALUES (');
    lines.push('  ' + ch.id + ',');
    lines.push('  ' + ch.bookId + ',');
    lines.push('  ' + ch.parentId + ',');
    lines.push('  ' + ch.level + ',');
    lines.push('  ' + ch.number + ',');
    lines.push('  ' + esc(ch.title) + ',');
    lines.push('  ' + esc(ch.summary) + ',');
    lines.push('  ' + esc(ch.keyPoints) + ',');
    lines.push('  ' + ch.estimatedWords + ',');
    lines.push('  ' + esc(ch.content) + ',');
    lines.push('  ' + ch.wordCount + ',');
    lines.push('  ' + esc(ch.genStage) + ',');
    lines.push('  ' + esc(ch.audioUrl) + ',');
    lines.push('  ' + ch.audioDuration + ',');
    lines.push('  ' + (ch.isPublic ? 1 : 0) + ',');
    lines.push('  ' + esc(fmtDate(ch.generatedAt)));
    lines.push(');');
    lines.push('');
  });

  // Also output as JSON for easier transfer
  const sql = lines.join('\n');
  console.log(sql);

  // Save to file
  fs.writeFileSync('scripts/restore-book43.sql', sql, 'utf-8');
  console.error('SQL saved to scripts/restore-book43.sql');
})();