VicihostWebsite/scripts/export-wp.mjs

#!/usr/bin/env node
// One-time (re-runnable) export of the WordPress content into Astro content collections.
// Requires read-only DB creds via env vars: WP_DB_USER, WP_DB_PASSWORD
// (host/db default to the known wordpress_vicidial instance, override via WP_DB_HOST/WP_DB_NAME).
//
//   WP_DB_USER=viciadmin WP_DB_PASSWORD=... node scripts/export-wp.mjs

import mysql from 'mysql2/promise';
import TurndownService from 'turndown';
import fs from 'node:fs/promises';
import path from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const ROOT = path.resolve(__dirname, '..');

const DB_HOST = process.env.WP_DB_HOST || '192.168.202.2';
const DB_USER = process.env.WP_DB_USER;
const DB_PASSWORD = process.env.WP_DB_PASSWORD;
const DB_NAME = process.env.WP_DB_NAME || 'wordpress_vicihost';
const TABLE_PREFIX = 'vhmain_';

if (!DB_USER || !DB_PASSWORD) {
  console.error('Set WP_DB_USER and WP_DB_PASSWORD (read-only WordPress DB creds) before running.');
  process.exit(1);
}

const UPLOADS_SRC = '/srv/www/vhosts/vicihost-old/wp-content/uploads';
const UPLOADS_DEST = path.join(ROOT, 'src/assets/uploads');
const PAGES_DEST = path.join(ROOT, 'src/content/pages');
const POSTS_DEST = path.join(ROOT, 'src/content/posts');
const NAV_DEST = path.join(ROOT, 'src/data/nav.json');
const REDIRECTS_DEST = path.join(ROOT, 'redirects.map');

const turndown = new TurndownService({ headingStyle: 'atx', codeBlockStyle: 'fenced' });

// Emit literal <br /> rather than Markdown's trailing-two-spaces hard break. Markdown drops a
// hard break at the start of a paragraph, and this content uses leading breaks deliberately to
// space out sections -- raw tags survive that round-trip, and render identically.
// Deliberately no trailing newline: a lone tag on its own line starts a raw HTML block
// (CommonMark type 7), which would stop the rest of the paragraph being parsed as Markdown.
turndown.addRule('lineBreak', {
  filter: 'br',
  replacement: () => '<br />',
});

// This DB's post_content has literal backslash-n sequences from a past export/import,
// not real newlines -- normalize before treating it as HTML/text. The content also contains
// malformed `</br>` closing tags, which browsers (and therefore the old site) render as a
// line break, but an HTML parser drops -- rewrite them so those breaks survive.
function fixLiteralNewlines(text) {
  return text
    .replace(/\\r\\n/g, '\n')
    .replace(/\\n/g, '\n')
    .replace(/\r\n/g, '\n')
    .replace(/\r/g, '\n')
    .replace(/<\/br\s*>/gi, '<br />');
}

// Classic-editor content is stored without <p> tags and rendered through WordPress's
// wpautop filter at display time. Reproduce that so converted Markdown keeps paragraph breaks.
function wpautop(text) {
  return text
    .split(/\n\s*\n/)
    .map((block) => block.trim())
    .filter(Boolean)
    .map((block) => {
      if (/^<(h[1-6]|div|ul|ol|li|table|blockquote|p)[ >]/i.test(block)) return block;
      return `<p>${block.replace(/\n/g, '<br>\n')}</p>`;
    })
    .join('\n');
}

function frontmatterString(value) {
  return `"${String(value).replace(/\\/g, '\\\\').replace(/"/g, '\\"')}"`;
}

function rewriteImageUrls(html, fromDir) {
  const relToUploads = path.relative(fromDir, UPLOADS_DEST);
  return html.replace(
    /(?:https?:\/\/(?:www\.|wp\.)?vicihost\.com)?\/wp-content\/uploads\/([^\s"')]+)/g,
    (_match, relPath) => `${relToUploads}/${relPath}`
  );
}

// Content links between pages are stored as absolute WordPress URLs against www.vicihost.com.
// Turn them into site-relative paths. IDs with no published page/post behind them (deleted
// pages, a stray revision) are left alone: they are already broken on the live site.
function rewriteInternalLinks(html, linkMap, unresolved) {
  return html.replace(
    /https?:\/\/(?:www\.|wp\.)?vicihost\.com\/\?(?:page_id|p)=(\d+)/gi,
    (match, id) => {
      const target = linkMap.get(Number(id));
      if (!target) {
        unresolved.add(id);
        return match;
      }
      return target;
    }
  );
}

// WordPress expands [caption] at render time via do_shortcode(); the raw post_content keeps it
// literal, so it has to be expanded here or it shows up as visible markup on the page.
// The shortcode is swapped for a placeholder before the HTML->Markdown conversion and rebuilt
// afterwards, so the <img> inside still goes through Turndown (and therefore still gets picked
// up by Astro's image pipeline) rather than being frozen as raw HTML pointing into src/assets.
const CAPTION_PLACEHOLDER = (i) => `CAPTIONPLACEHOLDER${i}END`;

function extractCaptions(html, captions) {
  return html.replace(/\[caption([^\]]*)\]([\s\S]*?)\[\/caption\]/gi, (_match, attrs, inner) => {
    const lastTag = inner.lastIndexOf('>');
    captions.push({
      align: (attrs.match(/align=["']?(align\w+)["']?/i) || [, 'aligncenter'])[1],
      width: (attrs.match(/width=["']?(\d+)["']?/i) || [, ''])[1],
      mediaHtml: inner.slice(0, lastTag + 1),
      text: inner.slice(lastTag + 1).trim(),
    });
    return CAPTION_PLACEHOLDER(captions.length - 1);
  });
}

// Blank lines around the image are load-bearing: they close the surrounding raw HTML block so
// the image is parsed as Markdown rather than swallowed as literal HTML.
function buildCaption({ align, width, mediaHtml, text }) {
  const media = turndown.turndown(mediaHtml).trim();
  const style = width ? ` style="width: ${Number(width) + 10}px"` : '';
  const caption = text ? `\n<p class="wp-caption-text">${text}</p>` : '';
  return `<div class="wp-caption ${align}"${style}>\n\n${media}\n${caption}\n</div>`;
}

async function buildLinkMap(conn) {
  const [rows] = await conn.execute(
    `SELECT ID, post_type, post_name FROM ${TABLE_PREFIX}posts
     WHERE post_status = 'publish' AND post_type IN ('page', 'post')`
  );
  return new Map(rows.map((r) => [r.ID, r.post_type === 'post' ? `/blog/${r.post_name}` : `/${r.post_name}`]));
}

// Apache RewriteMap (txt type) file: "<id><space><path>" per line, one for every published
// page/post, not just the ones referenced from other content -- anyone on the web could have
// bookmarked or linked to any of them via the old ?page_id=N / ?p=N plain-permalink URLs.
async function writeRedirectMap(linkMap) {
  const lines = [...linkMap.entries()]
    .sort(([a], [b]) => a - b)
    .map(([id, target]) => `${id} ${target}`);
  await fs.writeFile(REDIRECTS_DEST, `${lines.join('\n')}\n`);
  console.log(`Wrote ${linkMap.size} redirect entries to ${REDIRECTS_DEST}`);
}

async function copyAttachments() {
  // Copy the whole uploads tree rather than just the 82 attachment rows: WordPress generates
  // multiple resized variants per image (e.g. "-1008x1024.png") that content <img> tags/srcsets
  // reference directly but that aren't tracked as their own attachment posts in the DB.
  await fs.rm(UPLOADS_DEST, { recursive: true, force: true });
  await fs.cp(UPLOADS_SRC, UPLOADS_DEST, { recursive: true });
  console.log(`Copied uploads tree to ${UPLOADS_DEST}`);
}

async function exportPostType(conn, postType, destDir, { attachmentPaths, thumbnails, linkMap, unresolved }) {
  const [rows] = await conn.execute(
    `SELECT ID, post_title, post_name, post_date, post_content, post_excerpt
     FROM ${TABLE_PREFIX}posts WHERE post_type = ? AND post_status = 'publish' ORDER BY post_date DESC`,
    [postType]
  );

  await fs.mkdir(destDir, { recursive: true });

  for (const row of rows) {
    const slug = row.post_name || String(row.ID);
    let html = fixLiteralNewlines(row.post_content || '');
    if (!/^\s*<(p|div|h[1-6]|ul|ol|table)[ >]/i.test(html)) {
      html = wpautop(html);
    }
    html = rewriteImageUrls(html, destDir);
    html = rewriteInternalLinks(html, linkMap, unresolved);

    const captions = [];
    html = extractCaptions(html, captions);
    const markdown = turndown
      .turndown(html)
      .replace(/CAPTIONPLACEHOLDER(\d+)END/g, (_m, i) => buildCaption(captions[Number(i)]));

    let excerpt = fixLiteralNewlines(row.post_excerpt || '');
    if (!excerpt) {
      const plain = html
        .replace(/CAPTIONPLACEHOLDER\d+END/g, ' ')
        .replace(/<[^>]+>/g, ' ')
        .replace(/\s+/g, ' ')
        .trim();
      // 60 words: what the theme's front-page loop sets ($sbExcerptLength = 60).
      // The slideshow truncates this further to 30 at render, as the theme does.
      excerpt = plain.split(' ').slice(0, 60).join(' ');
    }

    const frontmatter = [`title: ${frontmatterString(row.post_title)}`, `slug: ${frontmatterString(slug)}`];
    if (postType === 'post') {
      frontmatter.push(`date: ${frontmatterString(row.post_date.toISOString())}`);
      frontmatter.push(`excerpt: ${frontmatterString(excerpt)}`);

      const thumbId = thumbnails.get(row.ID);
      const thumbFile = thumbId && attachmentPaths.get(Number(thumbId));
      if (thumbFile) {
        const relToUploads = path.relative(destDir, UPLOADS_DEST);
        frontmatter.push(`featuredImage: ${frontmatterString(`${relToUploads}/${thumbFile}`)}`);
      }
    }

    await fs.writeFile(path.join(destDir, `${slug}.md`), `---\n${frontmatter.join('\n')}\n---\n\n${markdown}\n`);
  }
  console.log(`Wrote ${rows.length} ${postType}(s) to ${destDir}`);
}

async function exportNav(conn) {
  // A menu item is either a reference to a page/post (title and URL come from the target) or a
  // custom link, which carries its own label and URL -- this menu's "Home" entry is the latter.
  const [navRows] = await conn.execute(
    `SELECT p.ID, p.menu_order, p.post_title,
            MAX(CASE WHEN pm.meta_key = '_menu_item_object_id' THEN pm.meta_value END) AS object_id,
            MAX(CASE WHEN pm.meta_key = '_menu_item_type'      THEN pm.meta_value END) AS item_type,
            MAX(CASE WHEN pm.meta_key = '_menu_item_url'       THEN pm.meta_value END) AS item_url
     FROM ${TABLE_PREFIX}posts p
     JOIN ${TABLE_PREFIX}postmeta pm ON pm.post_id = p.ID
     WHERE p.post_type = 'nav_menu_item' AND p.post_status = 'publish'
     GROUP BY p.ID, p.menu_order, p.post_title
     ORDER BY p.menu_order`
  );

  const targetIds = navRows.filter((r) => r.item_type !== 'custom').map((r) => Number(r.object_id));
  const [targetRows] = targetIds.length
    ? await conn.query(`SELECT ID, post_title, post_name, post_type FROM ${TABLE_PREFIX}posts WHERE ID IN (?)`, [targetIds])
    : [[]];
  const targets = new Map(targetRows.map((r) => [r.ID, r]));

  const nav = navRows.map((r) => {
    if (r.item_type === 'custom') {
      return { title: r.post_title, href: r.item_url || '/' };
    }
    const target = targets.get(Number(r.object_id));
    const href = target?.post_type === 'post' ? `/blog/${target.post_name}` : `/${target?.post_name ?? ''}`;
    // WordPress lets a menu item override the target's label; fall back to the target title.
    return { title: r.post_title || target?.post_title || '', href };
  });

  await fs.mkdir(path.dirname(NAV_DEST), { recursive: true });
  await fs.writeFile(NAV_DEST, `${JSON.stringify(nav, null, 2)}\n`);
  console.log(`Wrote ${nav.length} nav items to ${NAV_DEST}`);
}

async function main() {
  const conn = await mysql.createConnection({ host: DB_HOST, user: DB_USER, password: DB_PASSWORD, database: DB_NAME });

  const [attachmentRows] = await conn.execute(
    `SELECT p.ID, pm.meta_value AS file FROM ${TABLE_PREFIX}posts p
     JOIN ${TABLE_PREFIX}postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_wp_attached_file'
     WHERE p.post_type = 'attachment'`
  );
  const attachmentPaths = new Map(attachmentRows.map((r) => [r.ID, r.file]));

  const [thumbRows] = await conn.execute(`SELECT post_id, meta_value FROM ${TABLE_PREFIX}postmeta WHERE meta_key = '_thumbnail_id'`);
  const thumbnails = new Map(thumbRows.map((r) => [r.post_id, r.meta_value]));

  await copyAttachments();
  const linkMap = await buildLinkMap(conn);
  const unresolved = new Set();
  await writeRedirectMap(linkMap);

  await exportPostType(conn, 'page', PAGES_DEST, { attachmentPaths, thumbnails, linkMap, unresolved });
  await exportPostType(conn, 'post', POSTS_DEST, { attachmentPaths, thumbnails, linkMap, unresolved });

  if (unresolved.size) {
    console.warn(`Left ${unresolved.size} link(s) pointing at WordPress IDs with no published page: ${[...unresolved].join(', ')}`);
  }
  await exportNav(conn);

  await conn.end();
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});