// Content importer for The Small Business MBA.
//
// Reads backend/content/pillars.json (group definitions) and every
// backend/content/<pillar-slug>/*.md lesson file, converts the markdown to
// brand HTML, strips em dashes, and upserts pillars + lessons into the DB.
//
// Idempotent: re-running updates existing pillars/lessons by slug. Safe to run
// repeatedly as content is added (core pillars and the 10 bonus courses).
//
// Run: npm run import   (after migrate)
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { ulid } from 'ulid';
import { query, queryOne, execute } from './client.js';

const here = path.dirname(fileURLToPath(import.meta.url));
const contentDir = path.join(here, '..', '..', 'content');
const now = () => Math.floor(Date.now() / 1000);

// ---- text cleaning -------------------------------------------------------
// Brand rule: no em dashes anywhere. Replace with readable punctuation.
function stripEmDashes(text) {
  return text
    .replace(/\s*[—–]\s*/g, ', ')   // spaced em/en dash -> comma
    .replace(/[—–]/g, '-');          // any stragglers -> hyphen
}

function escapeHtml(s) {
  return s.replace(/[&<>"']/g, (c) => ({ '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;', "'": '&#39;' }[c]));
}

function inline(text) {
  let t = escapeHtml(text);
  // Protect {{ ... }} auto-calc tokens (used by interactive resources) from markdown
  // emphasis/code processing, so formula operators like * and / survive intact.
  const toks = [];
  t = t.replace(/\{\{[^}]*\}\}/g, (m) => { toks.push(m); return '@@@' + (toks.length - 1) + '@@@'; });
  t = t.replace(/\[([^\]]+)\]\(([^)]+)\)/g, '<a href="$2">$1</a>');
  t = t.replace(/\*\*([^*]+)\*\*/g, '<strong>$1</strong>');
  t = t.replace(/(^|[^*])\*([^*]+)\*(?!\*)/g, '$1<em>$2</em>');
  t = t.replace(/`([^`]+)`/g, '<code>$1</code>');
  t = t.replace(/@@@(\d+)@@@/g, (_, i) => toks[+i]);
  return t;
}

// Google Docs exports escape markdown punctuation (\*\*, \_, \#, \>, \<). Undo that.
function unescapeMd(text) {
  return text.replace(/\\([\\`*_{}\[\]()#+.!>~|<-])/g, '$1');
}

// Minimal, dependency-free markdown to semantic HTML. Output renders inside the
// member app's .lesson-doc styling, so no inline styles are needed here.
function mdToHtml(md) {
  const lines = unescapeMd(stripEmDashes(md)).split('\n');
  const out = [];
  let i = 0;
  let listType = null; // 'ul' | 'ol' | 'check' | null

  const closeList = () => { if (listType) { out.push(listType === 'ol' ? '</ol>' : '</ul>'); listType = null; } };

  while (i < lines.length) {
    const raw = lines[i];
    const line = raw.trim();

    if (line === '') { closeList(); i += 1; continue; }

    if (line === '---' || line === '***') { closeList(); out.push('<hr>'); i += 1; continue; }

    // Worksheet fill-in box: a line that is exactly [FILL] or [FILL 5] (5 = lines tall).
    // Used by rebuilt resources so members can write/print answers. Renders as a box.
    const fillm = line.match(/^\[\[?\s*fill\s*(\d+)?\s*\]\]?$/i);
    if (fillm) {
      closeList();
      const n = fillm[1] ? Math.max(1, Math.min(20, Number(fillm[1]))) : 3;
      out.push(`<div class="fillbox" style="min-height:${(n * 1.7).toFixed(1)}em"></div>`);
      i += 1;
      continue;
    }

    // Task-list checklist: - [ ] item  /  - [x] done. Renders as styled checkboxes.
    const taskm = line.match(/^[-*]\s+\[([ xX])\]\s+(.*)$/);
    if (taskm) {
      if (listType !== 'check') { closeList(); out.push('<ul class="checklist">'); listType = 'check'; }
      const done = taskm[1].toLowerCase() === 'x';
      out.push(`<li class="chk${done ? ' done' : ''}">${inline(taskm[2])}</li>`);
      i += 1;
      continue;
    }

    // Markdown table: a row of cells, then a separator row of dashes.
    if (line.startsWith('|') && i + 1 < lines.length &&
        /-/.test(lines[i + 1]) && /^\|?[\s:|-]+\|?$/.test(lines[i + 1].trim())) {
      closeList();
      const cells = (s) => s.trim().replace(/^\||\|$/g, '').split('|').map((c) => c.trim());
      const header = cells(line);
      i += 2; // skip header + separator
      const rows = [];
      while (i < lines.length && lines[i].trim().startsWith('|')) { rows.push(cells(lines[i])); i += 1; }
      let t = '<table><thead><tr>' + header.map((c) => `<th>${inline(c)}</th>`).join('') + '</tr></thead><tbody>';
      for (const r of rows) t += '<tr>' + r.map((c) => `<td>${inline(c)}</td>`).join('') + '</tr>';
      out.push(t + '</tbody></table>');
      continue;
    }

    // Headings. The page already shows the lesson title as h1, so:
    //   #, ## -> h2 ; ###, #### -> h3. A bare "LESSON CONTENT" wrapper is dropped.
    const h = line.match(/^(#{1,6})\s+(.*)$/);
    if (h) {
      closeList();
      const level = h[1].length;
      let txt = h[2].replace(/[*_`]/g, '').trim();
      if (/^lesson content$/i.test(txt)) { i += 1; continue; }
      const tag = level <= 2 ? 'h2' : 'h3';
      out.push(`<${tag}>${inline(txt)}</${tag}>`);
      i += 1;
      continue;
    }

    if (line.startsWith('> ')) {
      closeList();
      out.push(`<blockquote>${inline(line.slice(2))}</blockquote>`);
      i += 1;
      continue;
    }

    // Bullet list: -, *, or the course docs' arrow bullet ->
    const bullet = line.match(/^([-*]|→|->)\s+(.*)$/);
    if (bullet) {
      if (listType !== 'ul') { closeList(); out.push('<ul>'); listType = 'ul'; }
      out.push(`<li>${inline(bullet[2])}</li>`);
      i += 1;
      continue;
    }

    const ordered = line.match(/^\d+\.\s+(.*)$/);
    if (ordered) {
      if (listType !== 'ol') { closeList(); out.push('<ol>'); listType = 'ol'; }
      out.push(`<li>${inline(ordered[1])}</li>`);
      i += 1;
      continue;
    }

    // Paragraph: gather consecutive plain lines.
    closeList();
    const para = [line];
    i += 1;
    while (i < lines.length && lines[i].trim() !== '' && !/^(#{1,6}\s|>\s|[-*]\s|→\s|->\s|\d+\.\s|---|\*\*\*)/.test(lines[i].trim())) {
      para.push(lines[i].trim());
      i += 1;
    }
    out.push(`<p>${inline(para.join(' '))}</p>`);
  }
  closeList();
  return out.join('\n');
}

// ---- frontmatter ---------------------------------------------------------
// Parses a leading --- ... --- block of simple key: value pairs.
function parseFrontmatter(text) {
  const m = text.match(/^---\s*\n([\s\S]*?)\n---\s*\n?([\s\S]*)$/);
  if (!m) return { meta: {}, body: text };
  const meta = {};
  for (const line of m[1].split('\n')) {
    const kv = line.match(/^([a-zA-Z_]+):\s*(.*)$/);
    if (kv) {
      let v = kv[2].trim().replace(/^["']|["']$/g, '');
      meta[kv[1]] = v;
    }
  }
  return { meta, body: m[2] };
}

function slugify(s) {
  return s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
}

// ---- upserts -------------------------------------------------------------
async function upsertPillar(p) {
  const existing = await queryOne(`SELECT id FROM pillars WHERE slug = ?`, [p.slug]);
  if (existing) {
    await execute(
      `UPDATE pillars SET title = ?, subtitle = ?, kind = ?, intro_video_url = ?, sort = ? WHERE id = ?`,
      [p.title, p.subtitle || null, p.kind || 'core', p.intro_video_url || null, p.sort || 0, existing.id]
    );
    return existing.id;
  }
  const id = ulid();
  await execute(
    `INSERT INTO pillars (id, slug, title, subtitle, kind, intro_video_url, sort, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, p.slug, p.title, p.subtitle || null, p.kind || 'core', p.intro_video_url || null, p.sort || 0, now()]
  );
  return id;
}

async function upsertLesson(pillarId, lesson) {
  const existing = await queryOne(`SELECT id FROM lessons WHERE slug = ?`, [lesson.slug]);
  if (existing) {
    await execute(
      `UPDATE lessons SET pillar_id = ?, title = ?, summary = ?, body_html = ?, video_url = ?, est_minutes = ?, sort = ?, is_free_preview = ?, published = ?, updated_at = ? WHERE id = ?`,
      [pillarId, lesson.title, lesson.summary, lesson.body_html, lesson.video_url, lesson.est_minutes, lesson.sort, lesson.is_free_preview, lesson.published, now(), existing.id]
    );
    return 'updated';
  }
  await execute(
    `INSERT INTO lessons (id, pillar_id, slug, title, summary, body_html, video_url, est_minutes, sort, is_free_preview, published, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [ulid(), pillarId, lesson.slug, lesson.title, lesson.summary, lesson.body_html, lesson.video_url, lesson.est_minutes, lesson.sort, lesson.is_free_preview, lesson.published, now(), now()]
  );
  return 'inserted';
}

// Upsert one quiz (JSON) for a lesson, keyed by lesson_id.
async function upsertQuiz(lessonId, quiz) {
  const questions = (Array.isArray(quiz.questions) ? quiz.questions : []).map((q) => ({
    q: stripEmDashes(String(q.q || '')),
    options: (q.options || []).map((o) => stripEmDashes(String(o))),
    correct: Number(q.correct),
    explanation: q.explanation ? stripEmDashes(String(q.explanation)) : null,
  }));
  // Standardize every quiz to an 80% pass-to-advance threshold (the Kajabi norm),
  // regardless of the per-file value, so short and long quizzes gate consistently.
  const passPct = 80;
  const existing = await queryOne(`SELECT id FROM quizzes WHERE lesson_id = ?`, [lessonId]);
  if (existing) {
    await execute(
      `UPDATE quizzes SET pass_pct = ?, question_count = ?, questions = ?, updated_at = ? WHERE id = ?`,
      [passPct, questions.length, JSON.stringify(questions), now(), existing.id]
    );
    return 'updated';
  }
  await execute(
    `INSERT INTO quizzes (id, lesson_id, pass_pct, question_count, questions, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [ulid(), lessonId, passPct, questions.length, JSON.stringify(questions), now(), now()]
  );
  return 'inserted';
}

// Replace a lesson's resources wholesale. Each resource's markdown `body` is
// rebuilt into brand HTML (em dashes stripped by mdToHtml) -- never the raw file.
async function replaceResources(lessonId, resources) {
  await execute(`DELETE FROM lesson_resources WHERE lesson_id = ?`, [lessonId]);
  let i = 0;
  for (const r of resources) {
    await execute(
      `INSERT INTO lesson_resources (id, lesson_id, sort, type, title, body_html, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [ulid(), lessonId, i, stripEmDashes(String(r.type || 'worksheet')), stripEmDashes(String(r.title || 'Resource')), mdToHtml(String(r.body || '')), now(), now()]
    );
    i += 1;
  }
  return resources.length;
}

async function run() {
  const manifestPath = path.join(contentDir, 'pillars.json');
  if (!fs.existsSync(manifestPath)) {
    console.error('No content/pillars.json found at', manifestPath);
    process.exit(1);
  }
  const manifest = JSON.parse(fs.readFileSync(manifestPath, 'utf8'));
  // Optional video map (Bunny): { lessons: { slug: embedUrl }, pillars: { slug: introEmbedUrl } }.
  const videosPath = path.join(contentDir, 'videos.json');
  const videos = fs.existsSync(videosPath) ? JSON.parse(fs.readFileSync(videosPath, 'utf8')) : {};
  const vLessons = videos.lessons || {};
  const vPillars = videos.pillars || {};
  const pillarIdBySlug = {};

  for (const p of manifest.pillars) {
    if (vPillars[p.slug]) p.intro_video_url = vPillars[p.slug];
    pillarIdBySlug[p.slug] = await upsertPillar(p);
  }
  console.log(`pillars: ${manifest.pillars.length} upserted`);

  let inserted = 0;
  let updated = 0;
  let quizIns = 0;
  let quizUpd = 0;
  let resCount = 0;
  let videoCount = 0;
  for (const p of manifest.pillars) {
    const dir = path.join(contentDir, p.slug);
    if (!fs.existsSync(dir)) continue;
    const files = fs.readdirSync(dir).filter((f) => f.endsWith('.md')).sort();
    for (const file of files) {
      const text = fs.readFileSync(path.join(dir, file), 'utf8');
      const { meta, body } = parseFrontmatter(text);
      const prefix = file.match(/^(\d+)/);
      const lesson = {
        slug: meta.slug || slugify(file.replace(/^\d+[-_]?/, '').replace(/\.md$/, '')),
        title: stripEmDashes(meta.title || file.replace(/\.md$/, '')),
        summary: meta.summary ? stripEmDashes(meta.summary) : null,
        body_html: mdToHtml(body),
        video_url: meta.video_url || null,
        est_minutes: meta.est_minutes ? Number(meta.est_minutes) : null,
        sort: meta.sort ? Number(meta.sort) : prefix ? Number(prefix[1]) : 0,
        is_free_preview: /^(1|true|yes)$/i.test(meta.free_preview || '') ? 1 : 0,
        published: /^(0|false|no)$/i.test(meta.published || '') ? 0 : 1,
      };
      if (vLessons[lesson.slug]) lesson.video_url = vLessons[lesson.slug];
      const res = await upsertLesson(pillarIdBySlug[p.slug], lesson);
      if (res === 'inserted') inserted += 1; else updated += 1;
      if (lesson.video_url) videoCount += 1;
      console.log(`  ${p.slug}/${file} -> ${res} (${lesson.slug})`);

      // Optional sibling quiz file: NN-title.quiz.json
      const quizPath = path.join(dir, file.replace(/\.md$/, '.quiz.json'));
      if (fs.existsSync(quizPath)) {
        try {
          const quiz = JSON.parse(fs.readFileSync(quizPath, 'utf8'));
          const row = await queryOne(`SELECT id FROM lessons WHERE slug = ?`, [lesson.slug]);
          if (row) {
            const qres = await upsertQuiz(row.id, quiz);
            if (qres === 'inserted') quizIns += 1; else quizUpd += 1;
            console.log(`    quiz -> ${qres} (${(quiz.questions || []).length} questions)`);
          }
        } catch (e) {
          console.error(`    quiz FAILED for ${file}: ${e.message}`);
        }
      }

      // Optional sibling resources file: NN-title.resources.json
      const resPath = path.join(dir, file.replace(/\.md$/, '.resources.json'));
      if (fs.existsSync(resPath)) {
        try {
          const rj = JSON.parse(fs.readFileSync(resPath, 'utf8'));
          const row = await queryOne(`SELECT id FROM lessons WHERE slug = ?`, [lesson.slug]);
          if (row) {
            const nres = await replaceResources(row.id, Array.isArray(rj.resources) ? rj.resources : []);
            resCount += nres;
            console.log(`    resources -> ${nres}`);
          }
        } catch (e) {
          console.error(`    resources FAILED for ${file}: ${e.message}`);
        }
      }
    }
  }
  console.log(`lessons: ${inserted} inserted, ${updated} updated`);
  console.log(`quizzes: ${quizIns} inserted, ${quizUpd} updated`);
  console.log(`resources: ${resCount} rebuilt`);
  console.log(`videos: ${videoCount} lessons wired to Bunny`);
  console.log('import complete');
  process.exit(0);
}

export { mdToHtml, unescapeMd, stripEmDashes };

// Only run the importer when invoked directly (so the converter can be unit-tested).
if (process.argv[1] && process.argv[1].endsWith('import_content.js')) {
  run().catch((err) => {
    console.error('import failed:', err);
    process.exit(1);
  });
}
