# Database Setup & Deploy

How to build the petav3 database from scratch, keep it up to date, and load the
data that is **not** carried in git. Applies to local dev and live deploy alike —
the only difference is which database `.env` points at.

> **PHP 8.4+ required.** Commands below use `php artisan`; on Windows/Laragon use the
> full 8.4 binary path if `php` resolves to the wrong version.

---

## Going live (prod) — quick steps

The short version; the annotated **Production runbook** (with gotchas) is at the bottom.

```bash
# 1. .env → petav3 prod DB (+ APP_KEY, APP_ENV=production), AND set the `reference`
#    connection to petav2:  REFERENCE_DB_HOST=159.65.139.125  REFERENCE_DB_DATABASE=peta_wk_dev
#    REFERENCE_DB_USERNAME=readuser  REFERENCE_DB_PASSWORD=…
php artisan migrate                              # 2. table structure
php artisan db:seed --class=RolesSeeder          # 3. roles only (NOT full db:seed — it has demo data) + make a real admin
php artisan db:seed --class=PositionsSeeder      # 3b. admin positions lookup (Caller/Appointment/Closer/Marketing/Normal, ids 1–5)
php artisan market:import                        # 4. market data, direct from petav2 (no dump, no 2006 error)
php artisan db:seed --class='\LmsImportSeeder'   # 5. Learn content
npm run build && php artisan storage:link        # 6. assets + storage; then start the redis-ai queue worker
# 7. Repoint the external scraper at the petav3 prod DB.
```

> ⚠️ Two pre-reqs for step 4: petav3's prod server must be able to **reach** petav2's DB
> (`159.65.139.125:3306`), and `readuser` must be **allowed from petav3's IP** with SELECT on
> `peta_wk_dev`. The command fails fast with a clear message if either is missing.

---

## The three kinds of "data" and where they live

| Thing | In git? | How it gets into the DB |
|---|---|---|
| **Table structure** (all tables) | ✅ migrations | `php artisan migrate` |
| **Baseline data** (roles, demo users, memberships, …) | ✅ seeders | `php artisan db:seed` |
| **LMS / Learn content** (~200 rows, small) | ✅ committed SQL | `db:seed --class='\LmsContentSeeder'` |
| **Market data** (`catalog_*`, `market_airbnbs`, `market_property_agents`) | Real datasets are not in git | **Prod/staging:** `php artisan market:import`. **Local:** `db:seed --class='\MarketDataSeeder'` creates a small committed-code fixture with no external connection. |

The full scraped datasets remain outside git because of their size. Production
loads them through the governed, batched `market:import` path. Local development
does not need production or petaV2 credentials: the explicit `MarketDataSeeder`
creates three catalogue projects plus representative floor-plan analytics,
Airbnb rows and agents directly in the redesigned schema.

`MarketDataSeeder` and `LmsContentSeeder` are intentionally **NOT** called by
`DatabaseSeeder`. Invoke them explicitly; this prevents local fixture market
data from ever being seeded accidentally during a production baseline setup.

---

## Command cheat-sheet

| Command | Destructive? | When |
|---|---|---|
| `php artisan migrate:fresh --seed` | **YES — drops all tables** | First-time setup, or a deliberate full reset |
| `php artisan migrate` | No — runs only new migrations | After pulling a branch that added tables |
| `php artisan db:seed` | No — adds baseline data | After a fresh migrate, or to (re)seed baseline |
| `php artisan market:import` | No — stable-identity upsert | **Prod/staging**: load/refresh the three redesigned target datasets from a configured read-only source |
| `php artisan db:seed --class='\MarketDataSeeder'` | No — stable-identity upsert | **Local dev**: create a deterministic sample directly in the redesigned schema; no petaV2/prod access |
| `php artisan db:seed --class='\LmsContentSeeder'` | No | To load Learn content (Learn branch) |

> The `\` prefix is required — these seeders live in the global namespace
> (`database/seeds/`), not `Database\Seeders\`.

---

## First-time local setup

```bash
composer install
php artisan key:generate
php artisan migrate:fresh --seed          # structure + baseline (roles, demo users, …)
php artisan db:seed --class='\MarketDataSeeder' # optional local Analyze fixture
php artisan storage:link
```

Then load the data that isn't in git (see the two sections below). Finally:

```bash
npm install && npm run dev                # Vite dev server
php artisan serve --port=8001             # app on :8001
```

## Day-to-day (after pulling changes)

```bash
git pull
composer install
npm install
php artisan migrate                       # NOT migrate:fresh — keeps your data
php artisan db:seed                        # only if baseline seeders changed
```

Re-run a specific seeder only if that data changed. **Never** `migrate:fresh` on a
DB you want to keep — it drops everything.

---

## Loading market data

### Local development (no external database)

```bash
php artisan migrate
php artisan db:seed --class='\MarketDataSeeder'
```

This creates a small MY fixture in `catalog_projects`,
`catalog_project_sources`, `catalog_floor_plans`,
`catalog_floor_plan_analytics`, `developers`,
`catalog_project_developers`, `market_airbnbs` and
`market_property_agents`. Re-running it is idempotent by stable source
identity and does not write any retiring `properties` / `property_*` /
`airbnbs` / `property_agents` table.

### Production or staging (real source)

```bash
php artisan market:import --source=reference
```

The source connection must be read-only. The command currently imports only:

- `edgeprop_projects` → catalogue source/canonical rows;
- `airbnbs` → `market_airbnbs`;
- `propsense_agents` → `market_property_agents`.

Options: `--chunk=`, `--tables=` and `--fresh`. No dump file is accepted by
the redesigned workflow.

## Loading Learn content (`LmsContentSeeder`)

> Lives on the `john-learn-petav3-migration` branch (the LMS-to-petav3 migration).
> Its dump (`database/data/lms_content.sql`, ~200 rows) **is** committed, so no
> mysqldump needed:

```bash
php artisan db:seed --class='\LmsContentSeeder'
```

---

## Live deploy (petav3 hosting)

The code is **connection-agnostic** — going live is configuration, not code. **Run by a
human with prod access, one step at a time, verifying each.** Do not point an unattended
agent at prod: `migrate:fresh` DROPS every table and these steps are irreversible.

### Production runbook (in order)

1. **`.env`** — point the **default** connection (`DB_HOST`, `DB_DATABASE`, `DB_USERNAME`,
   `DB_PASSWORD`) at the live petav3 DB; set `APP_KEY`, `APP_ENV=production`, `APP_URL`.
2. **`php artisan migrate`** — builds all table structure. (NOT `migrate:fresh` on a populated DB.)
3. **Baseline — selective, NOT the full `db:seed`.** ⚠️ `DatabaseSeeder` also seeds **demo**
   records (`PeopleSeeder`, `LeadsSeeder`, `EventSeriesSeeder`, `MembershipsSeeder`, …) meant
   for dev. In prod, seed only what's real:
   ```bash
   php artisan db:seed --class=RolesSeeder        # roles + permissions
   # + create a real admin user (console/tinker) — do NOT seed demo leads/users into prod
   ```
   Run the other baseline seeders only if the team explicitly wants that sample data live.
4. **Market data (three redesigned targets) — use the direct importer on prod.**
   Put petav2's **read-only** creds in `.env` as the `reference` connection
   (`REFERENCE_DB_HOST=159.65.139.125`, `REFERENCE_DB_DATABASE=peta_wk_dev`,
   `REFERENCE_DB_USERNAME=readuser`, `REFERENCE_DB_PASSWORD=…`), then:
   ```bash
   php artisan market:import        # reads petav2 → upserts petav3 in 500-row batches
   ```
   No `mysqldump`, no `.sql` file. **Use this on prod**; batched stable-identity
   upserts target only the catalogue plus governed Airbnb/agent tables. The
   local `MarketDataSeeder` is sample data and must never be run in production.
5. **Learn content.** `php artisan db:seed --class='\LmsImportSeeder'` (loads the committed
   `lms_content.sql`). ⚠️ Confirm the Learn/Course content state with the team first — the
   module was reworked on `master`, so what's "live content" vs demo is a coordination item.
6. **App runtime.** `npm run build` (production assets, **not** `dev`), `php artisan storage:link`,
   and start a **queue worker on `redis-ai`** (`php artisan queue:work redis-ai --queue=ai`) —
   the Amenities-tab AI jobs never run without it.
7. **Keep ingestion scheduled.** Provider output must enter through its
   adapter/import path; external writers must not write target tables directly.

No PHP/Vue code changes — only `.env`. The local dev DB is **never** uploaded;
the live DB is rebuilt from migrations, approved seeders and governed imports.

### Server packages (one-time, via `scripts/server-setup.sh`)

The AI Video pipeline needs OS packages beyond PHP: **`ffmpeg`** (renders/stitches the
clips) and **`fonts-noto-cjk`** (Chinese captions — GD falls back to it for CJK text, so
without it Chinese captions render as boxes/tofu on Linux even though they look fine on a
macOS dev box). `scripts/server-setup.sh` installs both as part of provisioning. On a
machine that was set up before this was added — or any fresh box that skipped the script —
install them manually, otherwise Chinese videos won't render:

```bash
sudo apt-get install -y ffmpeg fonts-noto-cjk
```

### Inertia SSR (public-page server rendering)

The public marketing pages (`/{country}/home`, `/{country}/new-projects`, funnel
`/{slug}` and slot `/{funnel}/{slot}` landings) are server-rendered for SEO — crawlers
and WhatsApp/Facebook share-scrapers get full HTML instead of an empty Inertia shell.
Only those pages render on the server (`config/inertia.php` → `ssr.only`, enforced by
`App\Ssr\PublicPagesGateway`); the Manage/portal pages always client-render.

Moving parts:

- **Bundle** — `npm run build` builds the client assets **and** the SSR bundle
  (`bootstrap/ssr/ssr.js`, gitignored). The deploy script runs this automatically.
- **Render server** — `petav3-inertia-ssr.service` (systemd, installed by
  `scripts/server-setup.sh`) runs `php artisan inertia:start-ssr` on `:13714`.
  The deploy script restarts it on every code deploy (it holds the old bundle in
  memory until bounced).
- **Flag** — `INERTIA_SSR_ENABLED=true` in the prod `.env` (then `php artisan
  config:cache`). Rollout order on a new box: deploy code → build → start the unit →
  flip the flag.

**Failure mode is graceful:** if the daemon is down or the bundle is missing, pages
fall back to client-side rendering — degraded SEO, never an outage. Health check:
`php artisan inertia:check-ssr`; logs: `journalctl -u petav3-inertia-ssr -n 50`.

Related SEO surface (no daemon needed — rendered by Blade regardless of SSR):
per-page OpenGraph/description/canonical/JSON-LD tags come from `App\Support\Seo`
(see `docs/modules_handbook/shared/seo/readMe.md`), `/sitemap.xml` is generated from
active countries + funnels + slots (cached 1 h), and `public/robots.txt` must have its
`Sitemap:` line pointed at the real production domain before go-live.

### The `reference` connection (transitional)

Analyze reads market data from the **default** `mysql` connection — it does **not**
use the `reference` connection. `reference` currently survives only for the LMS
module until `john-learn-petav3-migration` lands; once it does, the `reference`
connection (`REFERENCE_DB_*`) is removed entirely and this section goes away. Until
then, in local dev `REFERENCE_DB_*` should point at your local petav3 DB so the LMS
reference migration can run.

## Project catalogue deployment sequence (PetaV3 → PetaHK)

The catalogue is canonical and multi-source: `catalog_projects` (one row per real-world
development), `catalog_project_sources` (one row per provider record) and
`catalog_floor_plans` (catalogue-owned unit types). PetaV3 and PetaHK are separate
deployments/databases; catalogue data moves between them with the versioned JSON
export/import commands — **catalogue data only, never workflow data** (`projects`,
`focus_projects`, owner rows, campaigns, leads all stay local to each deployment).

Production order for a catalogue refresh + HK sync:

```bash
php artisan migrate --force
php artisan edgeprop:import /absolute/path/to/source.json
php artisan catalogue:export /absolute/path/to/catalogue-hk.json --country=HK
# copy the file to the PetaHK deployment, then there:
php artisan catalogue:import /absolute/path/to/catalogue-hk.json --dry-run
php artisan catalogue:import /absolute/path/to/catalogue-hk.json
```

Notes:

- Import is idempotent: canonical projects upsert by **UUID**, sources by provider code +
  external id, floor plans by UUID; missing country/provider lookup rows are created from
  the package metadata. Re-running the same package changes nothing.
- Active admin manual overrides (`manual_overrides` on `catalog_projects` /
  `catalog_floor_plans`) survive every scraper import and travel inside the package.
- An unsupported `schema_version` aborts before any write; each catalogue aggregate is
  written in its own transaction.
- Media binaries do not travel in the package — only stable URLs already stored as
  catalogue values.

## Zoom recording media archiving — enablement + backfill runbook

Zoom cloud recordings (video/audio) are re-hosted into the shared `MediaService` (private
GCS) so playback survives Zoom-side deletion; `zoom_recordings.media_id` points at the
durable copy, and `play_url`/`download_url` are kept untouched as the secondary Zoom
reference. Both the auto-archive-on-sync hook and the manual/backfill command already
ship in code; go-live is a config flip plus a batched backfill, no code changes.

**Enable auto-archiving on future syncs** (`ZoomRecordingSyncService::queueArchiving()` —
gated OFF by default so a deploy never auto-starts large uploads):

```bash
# .env
ZOOM_ARCHIVE_RECORDINGS=true
php artisan config:cache
```

**Backfill the existing rows** (`php artisan zoom:archive-recordings` — this command runs
regardless of `ZOOM_ARCHIVE_RECORDINGS`, since that flag only gates the sync hook):

```bash
# 1. See the volume before committing to anything.
php artisan zoom:archive-recordings --dry-run

# 2. Run in --limit BATCHES on the default redis-video lane — it shares workers
#    with video renders, so a full-backlog burst of Zoom downloads returns 429s.
php artisan zoom:archive-recordings --limit=50
#    (let it drain, then repeat)

# 3. Between batches, watch Horizon for 429s / failed jobs on the video queue.
#    A transient 429 self-heals via the job's $tries/$backoff (3 tries, 120s
#    backoff). If 429s persist or video renders lag, switch to a dedicated
#    redis-media lane (maxProcesses 2-3 — services.zoom.archive.queue_connection
#    is config-driven, so this is a config-only swap, no code change) and
#    continue batching.

# 4. Verify progress at any point:
#    SELECT COUNT(*) FROM zoom_recordings WHERE media_id IS NOT NULL;
```

`--force` re-archives rows that already have `media_id` — this is a **safe swap**, not a
delete-then-replace: the job stores and verifies the new `Media`, attaches it, and only
**then** deletes the previous `Media`, so a failed re-archive never destroys a working
copy. Full option reference: `php artisan help zoom:archive-recordings`.

Config (`config('services.zoom.archive')`, all env-backed except `collection` /
`backfill_max_processes`): `enabled` (`ZOOM_ARCHIVE_RECORDINGS`, default `false`),
`queue_connection` (`ZOOM_ARCHIVE_QUEUE_CONNECTION`, default `redis-video`),
`download_timeout` (`ZOOM_ARCHIVE_DOWNLOAD_TIMEOUT`, default `900`),
`retry_failed_on_sync` (`ZOOM_ARCHIVE_RETRY_FAILED_ON_SYNC`, default `false`),
`stream_via_signed_url` (`ZOOM_ARCHIVE_STREAM_VIA_SIGNED_URL`, default `false`).