# Phase 4: Database Seeding (Content Migration)

## Goal
Automate the migration of the visual page designs so that the user does not have to manually drag-and-drop blocks or retype the text for every page on a new website.

> [!NOTE]
> **This phase is optional.** Only implement it if you are migrating an existing, fully designed website to a new domain. If this is a brand-new website being built from scratch, skip this phase entirely and go to Phase 5.

## Important Context: The Two-Table System
> [!IMPORTANT]
> Keep in mind the rule from Phase 2: The Puck canvas data we are migrating is stored in the `homepage_content` table, with the `site_id` column acting as the unique identifier for the page (e.g., `${RANKTRACTION_SITE_ID}-about-us`). For the homepage, it is just `${RANKTRACTION_SITE_ID}`.

## Requirements

### 1. The Export Script (`scripts/export-cms.ts`)
* Write a Node/TypeScript script that connects to the database of the finished, source website.
* The script must read the database connection string from the **`DATABASE_URL`** environment variable in `.env.local`. Do not hardcode any credentials.
* Query the `homepage_content` table to extract all rows belonging to the source website (filter by the source `site_id`).
* Save this extracted data locally into a JSON file (e.g., `cms-seed-data.json`).

> [!CAUTION]
> Do NOT commit `cms-seed-data.json` or any database credentials to version control. Add these to `.gitignore` before running the script.

### 2. The Import Script (`scripts/seed-cms.ts`)
* Write a script that connects to the **new** website's Neon database.
* The script should read the `cms-seed-data.json` file.
* **Safety Rule:** Use an `UPSERT` operation when writing to the `homepage_content` table. This ensures that if the page already exists, it safely overwrites it without throwing unique constraint errors.
* **Dynamic ID Replacement:** Ensure the script dynamically replaces the old `site_id` prefix with the new website's `RANKTRACTION_SITE_ID` (e.g., changing `oldsite-about-us` to `newsite-about-us`).

### 3. Component Selection (Hero vs SectionHeading)
> [!WARNING]
> **CRITICAL COMPONENT BUG:** When seeding a standard content page (like About Us or Privacy Policy), do NOT use the `HeroSection` component for the page title. `HeroSection` renders as a `100svh` fullscreen cinematic banner (designed for homepage video/image backgrounds) and has a dark `bg-slate-900` overlay.
> 
> Instead, always use the `SectionHeading` component, which provides a clean, centered title block appropriate for standard pages.

## Implementation Pattern

### Export Script
```typescript
// scripts/export-cms.ts
import { config } from 'dotenv';
import { neon } from '@neondatabase/serverless';
import * as fs from 'fs';

config({ path: '.env.local' });
const sql = neon(process.env.DATABASE_URL!);
const SOURCE_SITE_ID = process.env.RANKTRACTION_SITE_ID!;

async function main() {
  const rows = await sql`SELECT * FROM homepage_content WHERE site_id LIKE ${SOURCE_SITE_ID + '%'}`;
  fs.writeFileSync('cms-seed-data.json', JSON.stringify(rows, null, 2));
  console.log(`Exported ${rows.length} rows.`);
}
main();
```

### Import Script (Drizzle ORM Pattern)
```typescript
// scripts/seed-cms.ts
// Example using Drizzle's onConflictDoUpdate for safe upsert
await db.insert(homepageContent)
  .values({
    siteId: newSiteId, // e.g. 'newsite-about-us'
    content: pageData.content,
    draftContent: pageData.draftContent,
    updatedAt: new Date()
  })
  .onConflictDoUpdate({
    target: homepageContent.siteId,
    set: { 
      content: pageData.content,
      draftContent: pageData.draftContent,
      updatedAt: new Date()
    }
  });
```

## ⚠️ Gotcha: Hardcoded Pages Are Invisible to the Sitemap

> [!IMPORTANT]
> Pages like **About Us** (`/about-us`) and **Privacy Policy** (`/privacy-policy`) are **hardcoded Next.js routes** — they live as files in `src/app/about-us/page.tsx` and `src/app/privacy-policy/page.tsx`. They are **NOT** stored in the `custom_pages` database table.
>
> The sitemap (`app/sitemap.ts`) fetches custom pages from the `custom_pages` DB table. Since these hardcoded pages are never in that table, the sitemap's DB query will **never find them** — they will be completely missing from `/sitemap.xml`, making them invisible to Google.

### Fix: Add Them Explicitly to `staticPages` in `app/sitemap.ts`

Every time you add a new hardcoded route to this project (a `page.tsx` file directly in `src/app/`), you **must** also add it to the `staticPages` array in `app/sitemap.ts`:

```typescript
// app/sitemap.ts — staticPages array
const staticPages: MetadataRoute.Sitemap = [
  { url: siteUrl,                      changeFrequency: 'daily',   priority: 1   },
  { url: `${siteUrl}/blog`,            changeFrequency: 'daily',   priority: 0.9 },
  // ↓ These MUST be added manually — they are hardcoded routes, not DB entries
  { url: `${siteUrl}/about-us`,        changeFrequency: 'monthly', priority: 0.6 },
  { url: `${siteUrl}/privacy-policy`,  changeFrequency: 'yearly',  priority: 0.3 },
];
```

> [!TIP]
> **Priority guidelines for hardcoded pages:**
> - Main informational pages (About Us, Contact): `0.5–0.7`, `monthly`
> - Legal pages (Privacy Policy, Terms): `0.2–0.4`, `yearly`
> - The homepage and blog listing are always `daily` at `0.9–1.0`

### Rule of Thumb

| Page Type | Where it lives | Appears in sitemap automatically? |
|---|---|---|
| Custom page (created via Page Editor) | `custom_pages` DB table | ✅ Yes — after publishing |
| Hardcoded route (`src/app/slug/page.tsx`) | File system only | ❌ No — must add to `staticPages` |

---

## Testing Steps
1. Run the export script: `npx tsx scripts/export-cms.ts`
2. Verify `cms-seed-data.json` was created with the expected number of rows.
3. Run the seed script: `npx tsx scripts/seed-cms.ts`
4. Verify the terminal outputs a success message.
5. Open the editor: `/page_editor_admin?path=/about`.
6. Confirm the canvas is no longer blank, but instantly populates with the designed "About Us" page.
7. Go to the live frontend route `/about` and confirm the page renders correctly.
8. Go to `/sitemap.xml` — confirm `/about-us` and `/privacy-policy` are listed.

---

## ✅ Phase 4 Completion Verification

Run this checklist to confirm Phase 4 is fully implemented:

- [ ] Seed script exists at `scripts/seed-cms.ts`
- [ ] `npx tsx scripts/seed-cms.ts` runs successfully with no errors
- [ ] Open `/page_editor_admin?path=/about` → canvas is populated (not blank)
- [ ] Live frontend route (e.g. `/about`) renders the seeded content correctly
- [ ] All seeded data includes the correct `site_id` for this website
- [ ] No data from other sites is overwritten or affected
- [ ] All hardcoded routes (`/about-us`, `/privacy-policy`, etc.) are listed in `staticPages` in `app/sitemap.ts`
- [ ] Go to `/sitemap.xml` and confirm all hardcoded routes appear

> **Phase 4 is complete when ALL boxes above are checked.**

