import "dotenv/config";
import { pathToFileURL } from "node:url";
import { count } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import {
  navItems,
  pages,
  postTags,
  posts,
  settings,
  tags,
  users,
} from "../src/db/schema";
import { hashPassword } from "../src/lib/auth/password";
// Bodies below are authored in Markdown for maintainability, but the
// database stores editor HTML — convert at insert time.
import { renderMarkdown } from "../src/lib/markdown";
import { slugify } from "../src/lib/slug";

const POST_BODIES = {
  hello: `Welcome to **Yap Blog**, a small blog that runs on Next.js, PostgreSQL, and Drizzle ORM.

This post exists so the front page is not empty on first boot. Log in at [/admin](/admin) to write your own, or delete everything here and start fresh.

## What you can do

- Write posts in Markdown with a live preview
- Organize them with tags
- Publish static pages and pin them to the navigation
- Point the home page at the post list, a tag, or a page

> The best time to start a blog was ten years ago. The second-best time is tonight, after dark, in base03.

Happy writing!`,

  solarized: `Every terminal eventually goes through a phase. Mine never left it.

[Solarized](https://ethanschoonover.com/solarized/) is a sixteen-color palette designed by Ethan Schoonover with *fixed contrast relationships* — the light and dark variants share the same four accent-friendly content tones, so switching themes never changes how loud your text feels.

## The dark half

| Name   | Hex       | Role                    |
| ------ | --------- | ----------------------- |
| base03 | \`#002b36\` | background              |
| base02 | \`#073642\` | highlighted background  |
| base01 | \`#586e75\` | secondary text          |
| base0  | \`#839496\` | body text               |
| base1  | \`#93a1a1\` | emphasized text         |

The trick is that nothing is ever pure black or pure white. The background is a deep blue-green lagoon, and the text hovers above it like fog.

## Why it survives

Fashion cycles through editor themes the way it cycles through denim. Solarized persists because it was *engineered*, not just picked: every pair of tones was checked for perceptual contrast on calibrated displays in both CIELAB and by tired human eyes at 2 a.m.

This blog wears it out of gratitude.`,

  markdown: `Everything on this site is written in Markdown and rendered server-side through a sanitizing pipeline. This post is the kitchen sink that proves it.

## Text

Plain paragraphs, **bold**, *italics*, ~~strikethrough~~, and \`inline code\` all work. So do [links](https://www.markdownguide.org/) and footnote-ish parentheticals (like this one).

## Lists

1. Ordered lists
2. With multiple items
   - And nested bullets
   - Like these

## Code

\`\`\`ts
export function slugify(input: string): string {
  return input
    .normalize("NFKD")
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "");
}
\`\`\`

## Quotes and rules

> A blockquote, styled with a Solarized comment-tone border.

---

## Tables

| Feature   | Supported |
| --------- | --------- |
| GFM tables | yes      |
| Task lists | mostly   |

## What does *not* work

Raw \`<script>\` tags are stripped by the sanitizer, event handlers never survive, and \`javascript:\` URLs are removed. Try it in the editor preview — the pipeline is identical.`,

  drizzle: `Drizzle sits in a comfortable middle ground: more structure than raw SQL strings, far less machinery than a heavyweight ORM.

## Schema as the source of truth

The whole database lives in one TypeScript file. Columns, enums, foreign keys, check constraints — all plain declarations that \`drizzle-kit generate\` turns into versioned SQL migrations.

\`\`\`ts
export const posts = pgTable("posts", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  slug: text("slug").notNull().unique(),
  status: contentStatusEnum("status").notNull().default("draft"),
});
\`\`\`

## Queries that read like SQL

\`\`\`ts
db.select()
  .from(posts)
  .where(eq(posts.status, "published"))
  .orderBy(desc(posts.publishedAt));
\`\`\`

No magic query builder dialect to memorize — if you can read SQL, you can read this. And because every column is typed, renaming one breaks the build instead of production.`,

  keyboard: `A blog admin you cannot drive from the keyboard is a blog you will slowly stop using.

## Small things that compound

- Every control on this site is reachable with <kbd>Tab</kbd> and visible when focused — the focus ring is Solarized blue, two pixels, unmissable.
- The mobile menu closes on <kbd>Escape</kbd>.
- Destructive buttons ask for confirmation in a dialog the keyboard already owns.
- The first thing focus lands on after the page loads is a *skip to content* link.

## Semantic HTML is most of the work

Screen readers and keyboards both navigate by landmarks: \`<header>\`, \`<nav>\`, \`<main>\`, \`<aside>\`, \`<footer>\`. Get those right, label the navs, and half of accessibility falls out for free.

The other half is discipline about focus and contrast, which — conveniently — Solarized already solved.`,

  postgres: `PostgreSQL is the only database this blog will ever need, and probably the only one you need too.

## Constraints are features

The schema pushes invariants into the database itself:

- \`UNIQUE\` on every slug — duplicate URLs are impossible, even under race conditions
- \`CHECK ((url IS NULL) <> (page_id IS NULL))\` — a navigation item points at exactly one thing
- \`ON DELETE SET NULL\` — deleting the tag your home page features cannot break the site

## The nice-to-haves you get for free

Transactional DDL means migrations either fully apply or fully roll back. \`TRUNCATE ... RESTART IDENTITY CASCADE\` resets the test database in one statement. And \`GENERATED ALWAYS AS IDENTITY\` ends the serial-vs-sequence confusion forever.

Boring technology, chosen deliberately, is a superpower.`,

  writing: `The hardest part of maintaining a blog is not the software. It is the sitting down.

## Lower the activation energy

This is why the editor here does so little: a title, a textarea, a preview button. No blocks to arrange, no toolbar to negotiate with. The distance between *having a thought* and *publishing it* is four form fields.

## Drafts are a promise to yourself

Half-finished ideas go in as drafts. They never appear publicly — not on the front page, not in tag listings, not in the sidebar counts — but they sit in the admin list, quietly accusing, until you finish them.

Write badly, publish anyway, revise tomorrow. The edit button forgives everything.`,

  draftIdeas: `Rough backlog — do not publish.

- [ ] Post about the session model (hashed tokens, 7-day expiry)
- [ ] Compare rehype-sanitize schemas
- [ ] Dark/light theme toggle using the semantic token layer
- [ ] Benchmark: how many posts before pagination matters?`,

  draftSecret: `This draft exists purely so the test suite can verify that draft posts and their tags never leak onto the public site.

If you can read this without being logged in, something is very wrong.`,
} as const;

const PAGE_BODIES = {
  about: `**Yap Blog** is a demonstration blog for a small, self-hosted publishing platform built with Next.js, PostgreSQL, and Drizzle ORM.

## The stack

- **Next.js App Router** — server components and server actions, no client-side data fetching
- **PostgreSQL** — one normalized schema, real constraints
- **Drizzle ORM** — typed schema, generated SQL migrations
- **Solarized Dark** — the only correct terminal palette, now on the web

## The author

The administrator account on this instance is created from environment variables at seed time. Only a scrypt hash of the password ever touches the database.

Want one of these yourself? Clone the repository, run \`docker compose up -d\`, and follow the README.`,

  colophon: `This site is set in **Geist** with code in **Geist Mono**, colored exclusively with the sixteen Solarized values, and served by a single Next.js process talking to a single PostgreSQL database.

No analytics, no trackers, no cookies except the one that keeps the admin signed in.

Pages like this one are written in Markdown in the admin area and can be linked from the top navigation. Unpublish a page and any navigation item pointing at it vanishes until it returns.`,

  roadmap: `Unpublished scratchpad for future work:

1. Image uploads to object storage
2. RSS feed
3. Full-text search with \`tsvector\`
4. A second theme to prove the token layer works`,
} as const;

function daysAgo(days: number): Date {
  return new Date(Date.now() - days * 24 * 60 * 60 * 1000);
}

/*
 * Bulk demo posts
 * ---------------
 * The handful of posts above are handwritten showcases; the ~44 below are
 * generated filler so listings paginate realistically (~11 pages at the
 * seeded 5 posts/page). Each post gets a real title and a body assembled
 * deterministically from its topic's paragraph pool, so posts within a
 * topic share prose but no two bodies are identical.
 */

type BulkTopic = {
  tagSlugs: string[];
  intros: string[];
  sections: Array<{ heading: string; body: string }>;
  closers: string[];
  code?: string;
};

const BULK_TOPICS: Record<string, BulkTopic> = {
  design: {
    tagSlugs: ["design"],
    intros: [
      "Most interface problems are not creativity problems. They are restraint problems.",
      "You can learn a lot about a design system by looking at what it forbids.",
    ],
    sections: [
      {
        heading: "Start from the reading experience",
        body: "A blog is a reading machine. Line length around seventy characters, generous leading, and a type scale with only a few stops will do more for the page than any amount of decoration. Everything else is negotiable; the paragraph is not.",
      },
      {
        heading: "Constraints make consistency cheap",
        body: "When the palette is sixteen colors and the spacing scale has eight steps, most decisions are already made. The remaining ones are small enough to make quickly and reverse painlessly. That is the entire trick behind design tokens.",
      },
      {
        heading: "Polish is mostly alignment",
        body: "If two edges almost line up, make them line up. If two grays are almost the same, make them the same. A screen full of *almosts* reads as sloppy even when nobody can say why, and a screen full of exact matches reads as intentional.",
      },
      {
        heading: "Design for the second visit",
        body: "First impressions matter less than the hundredth impression. Navigation that never moves, headers that never surprise, and links that always look like links are boring on day one and priceless on day ninety.",
      },
    ],
    closers: [
      "None of this requires taste. It requires deciding once and then refusing to redecide every week.",
      "Boring, applied consistently, compounds into beautiful.",
    ],
  },
  typescript: {
    tagSlugs: ["typescript"],
    intros: [
      "The compiler is the cheapest reviewer you will ever hire.",
      "Types are documentation that cannot drift out of date.",
    ],
    sections: [
      {
        heading: "Model states, not fields",
        body: "A form that is either *editing*, *saving*, or *failed* should be a union of three shapes, not five booleans that can contradict each other. Once illegal states cannot be represented, half the defensive code deletes itself.",
      },
      {
        heading: "Let inference do the typing",
        body: "Annotate the boundaries — function arguments, module exports, API responses — and let inference handle everything in between. Code with type noise on every line is as hard to read as code with none.",
      },
      {
        heading: "Parse at the edges",
        body: "Data that enters the system through a form, a request, or an environment variable gets parsed once, immediately, into a known shape. Everything downstream then works with honest types instead of optimistic assertions.",
      },
      {
        heading: "Strictness is a one-way door",
        body: "Turning strict mode on late in a project is a week of archaeology. Turning it on from the first commit costs nothing. There is no third option where it stays off and the codebase stays healthy.",
      },
    ],
    closers: [
      "The goal is not type gymnastics. The goal is deleting the tests you no longer need.",
      "Every `any` is a small loan against future debugging time, at a terrible interest rate.",
    ],
    code: '```ts\ntype SaveState =\n  | { status: "editing" }\n  | { status: "saving" }\n  | { status: "failed"; error: string };\n```',
  },
  postgres: {
    tagSlugs: ["postgresql"],
    intros: [
      "The database outlives every framework that talks to it.",
      "Ask the database to enforce the rule, and it will never forget to.",
    ],
    sections: [
      {
        heading: "Constraints beat conventions",
        body: "A unique index does not care that two requests arrived in the same millisecond. A check constraint does not care that a new teammate skipped the onboarding doc. Rules that live in the schema are the only rules that hold under concurrency.",
      },
      {
        heading: "EXPLAIN before you optimize",
        body: "Most slow queries are slow for one boring reason: a sequential scan that should be an index scan. Reading the plan takes a minute; guessing takes an afternoon and usually lands on the wrong fix.",
      },
      {
        heading: "Migrations are code review for your data model",
        body: "Generated SQL sitting in a diff is the moment to catch the nullable column that should not be nullable. Once it ships, the mistake acquires rows, and rows have gravity.",
      },
      {
        heading: "Use fewer databases than you think you need",
        body: "Postgres will happily be your queue, your cache, your search index, and your JSON store while your project earns the traffic that justifies specialized tools. One backup, one connection string, one thing to learn deeply.",
      },
    ],
    closers: [
      "Boring technology is a compliment, and Postgres is the most complimented software alive.",
      "Data quality is not a cleanup task. It is a schema design decision from day one.",
    ],
    code: "```sql\nALTER TABLE posts\n  ADD CONSTRAINT posts_slug_format\n  CHECK (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$');\n```",
  },
  nextjs: {
    tagSlugs: ["nextjs"],
    intros: [
      "The server is a better place for most of the work than we spent a decade pretending it was.",
      "Every kilobyte of JavaScript you do not ship is a feature.",
    ],
    sections: [
      {
        heading: "Server components change the default",
        body: "Data fetching next to rendering, no client bundle cost, no loading spinner choreography. The client is reserved for the parts that are genuinely interactive, which in a blog is a menu button and a couple of forms.",
      },
      {
        heading: "Server actions are just functions",
        body: "A mutation is a typed function call that happens to cross the network. No endpoint naming committee, no JSON envelope bikeshed, no client-side fetch wrapper. Validate at the top, authorize before anything else, return field errors as data.",
      },
      {
        heading: "Layouts are an ownership boundary",
        body: "The chrome fetches what the chrome needs; the page fetches what the page needs. Route groups let two trees share a URL space without sharing chrome, which is exactly how an admin panel wants to live inside a public site.",
      },
      {
        heading: "Streaming needs a status-code budget",
        body: "The moment the shell flushes, the status code is spent. Routes that can 404 should resolve before streaming begins; routes that never 404 can stream skeletons freely. Decide per route, not per app.",
      },
    ],
    closers: [
      "The mental model is old: render on the server, enhance where needed. It just has good tooling now.",
      "Fewer moving parts on the client means fewer places for the bug to hide.",
    ],
    code: '```tsx\nexport default async function Page() {\n  const posts = await listPublishedPosts({ page: 1, perPage: 10 });\n  return <PostList posts={posts.items} />;\n}\n```',
  },
  writing: {
    tagSlugs: ["writing"],
    intros: [
      "The blank page is not the enemy. The closed editor is.",
      "Nobody is waiting for your post, which is exactly why you can publish it.",
    ],
    sections: [
      {
        heading: "Lower the stakes on purpose",
        body: "A post is not a thesis. Three paragraphs that say one true thing beat three thousand words that circle four maybe-true things. If it grows, it grows in the editor, not in your head.",
      },
      {
        heading: "Write for one specific reader",
        body: "Pick a person — a colleague, a past version of yourself, the next stranger with your exact bug — and explain it to them. Prose addressed to everyone lands on no one.",
      },
      {
        heading: "Endings are allowed to be abrupt",
        body: "You do not owe the reader a summary of what they just read. When the point has been made, stop. The best closing line is usually the one you almost deleted for being too plain.",
      },
      {
        heading: "Momentum beats inspiration",
        body: "A mediocre paragraph on Tuesday makes a good paragraph possible on Wednesday. The drafts folder is not a graveyard; it is a compost heap, and compost is how gardens work.",
      },
    ],
    closers: [
      "Publish it. You can be embarrassed and findable, or polished and imaginary.",
      "The archive you envy is just someone else's pile of Tuesdays.",
    ],
  },
  tooling: {
    tagSlugs: ["tooling"],
    intros: [
      "Good tooling is invisible until you work somewhere without it.",
      "Every manual step is a future incident report.",
    ],
    sections: [
      {
        heading: "Scripts are institutional memory",
        body: "The deploy ritual that lives in someone's shell history is one resignation away from being lost. The same ritual as a script in the repo is documentation that executes.",
      },
      {
        heading: "Make the fast path the right path",
        body: "If linting runs on save and tests run in a keystroke, they happen constantly. If they require remembering a command with four flags, they happen the night before release. Friction decides behavior more than policy does.",
      },
      {
        heading: "Update tools on a schedule, not in a panic",
        body: "Small weekly bumps fail in small ways. The eighteen-month mega-upgrade fails in ways that get their own retrospective document and a nickname.",
      },
    ],
    closers: [
      "The best developer experience improvements are the ones nobody thanks you for, because nobody notices the problem is gone.",
      "Sharpen the saw, but also: stop carrying the saw everywhere by hand.",
    ],
    code: '```json\n{\n  "scripts": {\n    "check": "npm run lint && npm run typecheck && npm test"\n  }\n}\n```',
  },
  accessibility: {
    tagSlugs: ["accessibility", "design"],
    intros: [
      "Accessibility is not a feature you add. It is damage you stop doing.",
      "The keyboard user is not an edge case; they are the test you can run yourself, today.",
    ],
    sections: [
      {
        heading: "Semantics do the heavy lifting",
        body: "A real button, a real nav, a real heading hierarchy: assistive tech understands these for free. Recreating them from divs means re-implementing the browser badly, one ARIA attribute at a time.",
      },
      {
        heading: "Focus must be visible, always",
        body: "Removing the focus ring because it 'looks busy' is unplugging the only steering wheel some users have. Style it boldly instead — a confident ring reads as designed, not accidental.",
      },
      {
        heading: "Alt text is editorial, not technical",
        body: "The question is not 'what pixels are here' but 'what would the sighted reader take away'. Sometimes that is a description; sometimes it is an empty string, because the image was decoration all along.",
      },
    ],
    closers: [
      "Tab through your site once a week. It costs ninety seconds and finds bugs your test suite cannot see.",
      "Accessible sites are faster, simpler, and easier to test. The virtue is a side effect of the quality.",
    ],
  },
  performance: {
    tagSlugs: ["performance", "nextjs"],
    intros: [
      "Performance is a feature users notice by its absence.",
      "The profiler has ended more arguments than any style guide ever will.",
    ],
    sections: [
      {
        heading: "Measure, then touch",
        body: "The slow part is never where intuition points. Ten minutes with real timings regularly reveals that the 'expensive render' is fine and the innocent-looking query runs four hundred times.",
      },
      {
        heading: "The cheapest work is the skipped kind",
        body: "Before making a request faster, ask whether it needs to happen. Caching, deduplication, and pagination are not optimizations; they are decisions not to do the work at all.",
      },
      {
        heading: "Budgets keep you honest",
        body: "A page-weight budget turns 'it feels slower lately' into 'we crossed 200 KB in March'. Numbers with thresholds get defended; vibes get eroded one dependency at a time.",
      },
    ],
    closers: [
      "Fast software is mostly the accumulation of small refusals.",
      "Users cannot tell you the site is slow. They just come back less often.",
    ],
    code: "```ts\nconst [countRows, itemRows] = await Promise.all([\n  countQuery,\n  pageQuery.limit(perPage).offset(offset),\n]);\n```",
  },
};

const BULK_POSTS: Array<{ title: string; topic: keyof typeof BULK_TOPICS }> = [
  { title: "Contrast is a budget", topic: "design" },
  { title: "Narrowing is the whole game", topic: "typescript" },
  { title: "Indexes I actually use", topic: "postgres" },
  { title: "Server components, one year in", topic: "nextjs" },
  { title: "Write the middle first", topic: "writing" },
  { title: "My terminal is my IDE", topic: "tooling" },
  { title: "Focus rings are not optional", topic: "accessibility" },
  { title: "Measure before you memoize", topic: "performance" },
  { title: "Whitespace does the heavy lifting", topic: "design" },
  { title: "satisfies changed how I write configs", topic: "typescript" },
  { title: "CHECK constraints are cheap insurance", topic: "postgres" },
  { title: "Streaming is a UX decision", topic: "nextjs" },
  { title: "Short posts are allowed", topic: "writing" },
  { title: "Dotfiles as documentation", topic: "tooling" },
  { title: "Alt text is an editorial skill", topic: "accessibility" },
  { title: "The fastest request is no request", topic: "performance" },
  { title: "Designing empty states first", topic: "design" },
  { title: "Discriminated unions for UI state", topic: "typescript" },
  { title: "Explaining EXPLAIN to myself", topic: "postgres" },
  { title: "Route groups keep layouts honest", topic: "nextjs" },
  { title: "Editing is deleting", topic: "writing" },
  { title: "The linter argues so we don't have to", topic: "tooling" },
  { title: "Keyboard first, mouse second", topic: "accessibility" },
  { title: "Lazy loading below the fold", topic: "performance" },
  { title: "The case for boring navigation", topic: "design" },
  { title: "The readonly habit", topic: "typescript" },
  { title: "Migrations without fear", topic: "postgres" },
  { title: "Caching is a contract", topic: "nextjs" },
  { title: "Keep a someday file", topic: "writing" },
  { title: "Scripts over memory", topic: "tooling" },
  { title: "Semantic HTML is free accessibility", topic: "accessibility" },
  { title: "Budgets make performance a feature", topic: "performance" },
  { title: "Color tokens before color choices", topic: "design" },
  { title: "Generics you can actually read", topic: "typescript" },
  { title: "The case against clever SQL", topic: "postgres" },
  { title: "Server actions without the footguns", topic: "nextjs" },
  { title: "Publish on a schedule, not a mood", topic: "writing" },
  { title: "Slow tools teach bad habits", topic: "tooling" },
  { title: "Typography defaults worth stealing", topic: "design" },
  { title: "Parsing, not validating, in practice", topic: "typescript" },
  { title: "Timestamps, time zones, and regret", topic: "postgres" },
  { title: "The app router mental model", topic: "nextjs" },
  { title: "Titles are promises", topic: "writing" },
  { title: "Small screens are the honest ones", topic: "design" },
];

function buildBulkBody(topicKey: keyof typeof BULK_TOPICS, index: number): string {
  const topic = BULK_TOPICS[topicKey];
  const intro = topic.intros[index % topic.intros.length];
  const first = topic.sections[index % topic.sections.length];
  let second = topic.sections[(index + 2) % topic.sections.length];
  if (second === first) {
    second = topic.sections[(index + 1) % topic.sections.length];
  }
  const closer = topic.closers[index % topic.closers.length];

  const parts = [intro, `## ${first.heading}`, first.body];
  if (topic.code && index % 2 === 0) parts.push(topic.code);
  parts.push(`## ${second.heading}`, second.body, closer);
  return parts.join("\n\n");
}

export async function seed(databaseUrl: string, log: (msg: string) => void = () => {}) {
  const pool = new Pool({ connectionString: databaseUrl, max: 3 });
  const db = drizzle(pool);

  try {
    // --- Administrator (from env; hash updated on every run) ---------------
    const username = process.env.ADMIN_USERNAME?.trim() || "admin";
    const password = process.env.ADMIN_PASSWORD;
    if (!password) {
      throw new Error("ADMIN_PASSWORD is not set — copy .env.example to .env first.");
    }
    const passwordHash = await hashPassword(password);
    await db
      .insert(users)
      .values({ username, passwordHash, role: "admin" })
      .onConflictDoUpdate({
        target: users.username,
        set: { passwordHash, role: "admin" },
      });
    log(`admin user “${username}” ready`);

    // --- Site settings (only created, never overwritten) -------------------
    await db
      .insert(settings)
      .values({
        id: 1,
        siteTitle: "Yap Blog",
        headerText: "Field notes from a solarized terminal.",
        footerText: "© 2026 Yap Blog · Set in base03 · Powered by Next.js, Postgres & Drizzle",
        postsPerPage: 5,
        excerptWords: 40,
        homeMode: "posts",
        theme: "solarized-dark",
        font: "geist",
      })
      .onConflictDoNothing({ target: settings.id });

    // --- Content (skipped when posts already exist) ------------------------
    const [{ value: postCount }] = await db.select({ value: count() }).from(posts);
    if (postCount > 0) {
      log("content already present — skipping demo content");
      return;
    }

    const tagRows = await db
      .insert(tags)
      .values([
        { name: "Design", slug: "design" },
        { name: "Next.js", slug: "nextjs" },
        { name: "PostgreSQL", slug: "postgresql" },
        { name: "TypeScript", slug: "typescript" },
        { name: "Writing", slug: "writing" },
        { name: "Tooling", slug: "tooling" },
        { name: "Accessibility", slug: "accessibility" },
        { name: "Performance", slug: "performance" },
        // Used only by a draft post — must never appear in the public sidebar.
        { name: "Secrets", slug: "secrets" },
      ])
      .returning();
    const tagId = new Map(tagRows.map((t) => [t.slug, t.id]));

    const postRows = await db
      .insert(posts)
      .values([
        {
          title: "Hello, Nightfall",
          slug: "hello-nightfall",
          body: renderMarkdown(POST_BODIES.hello),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(42),
          featuredImageUrl: "https://picsum.photos/seed/nightfall/1200/600",
          featuredImageAlt: "Abstract dark landscape at dusk",
        },
        {
          title: "Why Solarized Dark refuses to die",
          slug: "why-solarized-dark-refuses-to-die",
          body: renderMarkdown(POST_BODIES.solarized),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(35),
          featuredImageUrl: "https://picsum.photos/seed/solarized/1200/600",
          featuredImageAlt: "Deep blue-green gradient reminiscent of the Solarized base tones",
        },
        {
          title: "The Markdown kitchen sink",
          slug: "markdown-kitchen-sink",
          body: renderMarkdown(POST_BODIES.markdown),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(28),
        },
        {
          title: "Drizzle ORM in anger: schema, migrations, and calm",
          slug: "drizzle-orm-in-anger",
          body: renderMarkdown(POST_BODIES.drizzle),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(21),
          featuredImageUrl: "https://picsum.photos/seed/drizzle/1200/600",
          featuredImageAlt: "Rain drizzling on a window at night",
        },
        {
          title: "Keyboard-first blogging",
          slug: "keyboard-first-blogging",
          body: renderMarkdown(POST_BODIES.keyboard),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(14),
        },
        {
          title: "Postgres is enough",
          slug: "postgres-is-enough",
          body: renderMarkdown(POST_BODIES.postgres),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(7),
          // Deliberately broken URL: demonstrates the image-error fallback.
          featuredImageUrl: "https://broken.invalid/elephant.jpg",
          featuredImageAlt: "A sturdy elephant carrying a database",
        },
        {
          title: "On actually writing",
          slug: "on-actually-writing",
          body: renderMarkdown(POST_BODIES.writing),
          authorName: "Matt",
          status: "published",
          publishedAt: daysAgo(2),
        },
        {
          title: "Draft: ideas backlog",
          slug: "draft-ideas-backlog",
          body: renderMarkdown(POST_BODIES.draftIdeas),
          authorName: "Matt",
          status: "draft",
        },
        {
          title: "Secret draft (should never be public)",
          slug: "secret-draft",
          body: renderMarkdown(POST_BODIES.draftSecret),
          authorName: "Matt",
          status: "draft",
        },
      ])
      .returning();
    const postId = new Map(postRows.map((p) => [p.slug, p.id]));

    const link = (postSlug: string, ...tagSlugs: string[]) =>
      tagSlugs.map((slug) => ({
        postId: postId.get(postSlug)!,
        tagId: tagId.get(slug)!,
      }));

    await db.insert(postTags).values([
      ...link("hello-nightfall", "writing", "design"),
      ...link("why-solarized-dark-refuses-to-die", "design"),
      ...link("markdown-kitchen-sink", "writing", "design"),
      ...link("drizzle-orm-in-anger", "typescript", "postgresql", "nextjs"),
      ...link("keyboard-first-blogging", "design", "nextjs"),
      ...link("postgres-is-enough", "postgresql"),
      ...link("on-actually-writing", "writing"),
      ...link("draft-ideas-backlog", "secrets"),
      ...link("secret-draft", "secrets"),
    ]);

    // Bulk demo posts, older than the handwritten ones so those stay on
    // page 1. Published every ~2 weeks going back roughly two years.
    const bulkRows = await db
      .insert(posts)
      .values(
        BULK_POSTS.map((spec, i) => {
          const slug = slugify(spec.title);
          return {
            title: spec.title,
            slug,
            body: renderMarkdown(buildBulkBody(spec.topic, i)),
            authorName: "Matt",
            status: "published" as const,
            publishedAt: daysAgo(50 + i * 15),
            ...(i % 3 === 0
              ? {
                  featuredImageUrl: `https://picsum.photos/seed/${slug}/1200/600`,
                  featuredImageAlt: `Abstract illustration for “${spec.title}”`,
                }
              : {}),
          };
        }),
      )
      .returning({ id: posts.id, slug: posts.slug });

    const bulkSlugToId = new Map(bulkRows.map((r) => [r.slug, r.id]));
    await db.insert(postTags).values(
      BULK_POSTS.flatMap((spec) => {
        const postId = bulkSlugToId.get(slugify(spec.title))!;
        return BULK_TOPICS[spec.topic].tagSlugs.map((slug) => ({
          postId,
          tagId: tagId.get(slug)!,
        }));
      }),
    );

    const pageRows = await db
      .insert(pages)
      .values([
        { title: "About", slug: "about", body: renderMarkdown(PAGE_BODIES.about), status: "published" },
        { title: "Colophon", slug: "colophon", body: renderMarkdown(PAGE_BODIES.colophon), status: "published" },
        { title: "Roadmap", slug: "roadmap", body: renderMarkdown(PAGE_BODIES.roadmap), status: "draft" },
      ])
      .returning();
    const aboutPage = pageRows.find((p) => p.slug === "about")!;

    await db.insert(navItems).values([
      { label: "All posts", url: "/posts", pageId: null, sortOrder: 0 },
      { label: "About", url: null, pageId: aboutPage.id, sortOrder: 1 },
      { label: "Solarized", url: "https://ethanschoonover.com/solarized/", pageId: null, sortOrder: 2 },
    ]);

    log(
      `seeded ${postRows.filter((p) => p.status === "published").length + bulkRows.length} published posts, ` +
        `${postRows.filter((p) => p.status === "draft").length} drafts, ` +
        `${tagRows.length} tags, ${pageRows.length} pages`,
    );
  } finally {
    await pool.end();
  }
}

const isDirectRun =
  process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href;

if (isDirectRun) {
  const url = process.env.DATABASE_URL;
  if (!url) {
    console.error("DATABASE_URL is not set — copy .env.example to .env first.");
    process.exit(1);
  }
  seed(url, (msg) => console.log(`[seed] ${msg}`))
    .then(() => console.log("[seed] done"))
    .catch((error) => {
      console.error("[seed] failed:", error);
      process.exit(1);
    });
}