# 01 — Geographic Extraction

## Context
Fogbreak replaces Follow Up Boss, ShowingTime, Sisu, Paperless Pipeline, DocuSign, and RealScout as a single AI-native real estate platform. It must scale from city to state to nationwide. Currently there are 232 hardcoded Monterey Peninsula references across 20 files. Every one must be extracted into database-driven templates.

## What You're Doing
Removing ALL hardcoded geographic references from the codebase and replacing them with a database-driven market template system. After this, adding a new city/state/market requires zero code changes — just new database rows.

## Existing Codebase
- PHP 8.x backend, MySQL 8, vanilla JS PWA frontend
- `app/api/` contains all backend modules
- `app/fogbreak.html` is the main 8-tab SPA (2,622 lines)
- `CLAUDE.md` at project root has full architecture docs

## Files With Hardcoded Geography (232 total occurrences)

| File | Count | What's Hardcoded |
|------|-------|-----------------|
| `app/api/transactions.php` | 47 | 9 jurisdiction compliance templates, city-to-jurisdiction mapping function, checklist items per city |
| `app/api/admin.php` | 39 | Social post templates ("Monterey Peninsula"), city references in CRUD, listing descriptions |
| `app/fogbreak.html` | 20 | UI labels, dropdown options, header text, placeholder text |
| `app/api/properties.php` | 14 | Property search defaults, area descriptions, MLS field assumptions |
| `app/api/drips.php` | 13 | Campaign templates mentioning Peninsula cities, email copy |
| `app/index.html` | 10 | Login page branding, meta descriptions |
| `app/js/outreach-drafts.js` | 11 | Draft templates with city names, market language |
| `app/api/deadlines.php` | 8 | Deadline templates tied to specific jurisdictions |
| `app/ARCHITECTURE.html` | 4 | Documentation references (keep as historical but mark as Tenant #1 example) |
| `app/api/TENANTS_API.md` | 3 | Documentation |
| `app/js/pipeline-analytics.js` | 2 | Market area references |
| `app/portal.html` | 2 | Client portal branding |
| `app/manifest.json` | 1 | App name/description |
| Others | ~8 | Scattered references |

## Step-by-Step Instructions

### Step 1: Create Market Template Tables

Create `app/api/markets.php` with these auto-migration tables:

```sql
-- Market hierarchy
CREATE TABLE IF NOT EXISTS markets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT DEFAULT NULL,
    parent_market_id INT DEFAULT NULL,
    market_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    level ENUM('national','state','county','city','neighborhood') NOT NULL,
    country VARCHAR(2) DEFAULT 'US',
    state VARCHAR(2) DEFAULT NULL,
    county VARCHAR(100) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    timezone VARCHAR(50) DEFAULT 'America/Los_Angeles',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_market (tenant_id, market_key)
);

-- Flexible key-value configs per market
CREATE TABLE IF NOT EXISTS market_configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    config_key VARCHAR(100) NOT NULL,
    config_value JSON NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    UNIQUE KEY unique_config (market_id, config_key),
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);

-- Compliance templates (replaces hardcoded jurisdiction checklists)
CREATE TABLE IF NOT EXISTS compliance_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    template_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    category ENUM('inspection','permit','certification','disclosure','insurance','other') NOT NULL,
    description TEXT,
    default_days INT DEFAULT NULL,
    estimated_cost DECIMAL(10,2) DEFAULT NULL,
    responsible_party ENUM('buyer','seller','agent','tc','escrow','title','other') DEFAULT 'agent',
    is_required TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    notes TEXT DEFAULT NULL,
    effective_date DATE DEFAULT NULL,
    expiry_date DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);

-- Deadline templates (replaces hardcoded relative due dates)
CREATE TABLE IF NOT EXISTS deadline_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    template_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    relative_to ENUM('acceptance','listing','inspection','appraisal','closing','custom') NOT NULL,
    offset_days INT NOT NULL,
    offset_business_days TINYINT(1) DEFAULT 0,
    is_critical TINYINT(1) DEFAULT 0,
    notify_days_before INT DEFAULT 3,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);

-- MLS field mappings per market
CREATE TABLE IF NOT EXISTS mls_field_mappings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    mls_provider VARCHAR(100) NOT NULL,
    mls_field_name VARCHAR(100) NOT NULL,
    fogbreak_field_name VARCHAR(100) NOT NULL,
    transform_function VARCHAR(100) DEFAULT NULL,
    is_required TINYINT(1) DEFAULT 0,
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);

-- Market-specific terminology
CREATE TABLE IF NOT EXISTS market_terminology (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    term_key VARCHAR(100) NOT NULL,
    display_term VARCHAR(255) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    UNIQUE KEY unique_term (market_id, term_key),
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);

-- AI prompts per market
CREATE TABLE IF NOT EXISTS market_ai_prompts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_id INT NOT NULL,
    prompt_key VARCHAR(100) NOT NULL,
    system_prompt TEXT NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    UNIQUE KEY unique_prompt (market_id, prompt_key),
    FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE
);
```

### Step 2: Build Market Resolution Function

In `markets.php`, create a function that resolves market config with inheritance:

```php
function resolveMarketConfig($db, $marketId, $configKey) {
    // Walk up the parent chain until we find a value
    $current = $marketId;
    while ($current) {
        $stmt = $db->prepare("SELECT config_value FROM market_configs WHERE market_id = ? AND config_key = ?");
        $stmt->execute([$current, $configKey]);
        $row = $stmt->fetch();
        if ($row) return json_decode($row['config_value'], true);

        $stmt = $db->prepare("SELECT parent_market_id FROM markets WHERE id = ?");
        $stmt->execute([$current]);
        $parent = $stmt->fetch();
        $current = $parent ? $parent['parent_market_id'] : null;
    }
    return null;
}
```

### Step 3: Seed Monterey Peninsula Data as Tenant #1 Market Config

Create `db/seeds/monterey-peninsula.sql` that inserts ALL current hardcoded data as database rows:
- 1 national market (US), 1 state (CA), 1 county (Monterey), 9 city markets
- All 9 jurisdiction compliance templates migrated from `transactions.php`
- All deadline templates migrated from `deadlines.php`
- All city-to-jurisdiction mappings from the `$map` array in `transactions.php`
- Social post templates from `admin.php` line 1425-1456
- Drip campaign copy from `drips.php`
- Outreach draft templates from `outreach-drafts.js`

### Step 4: Refactor transactions.php

Replace the hardcoded `getJurisdiction()` function (lines ~694-708) and the `getComplianceItems()` switch statement (lines ~740+) with database queries:

```php
function getJurisdiction($db, $city, $marketId = null) {
    if ($marketId) {
        $stmt = $db->prepare("SELECT id, market_key FROM markets WHERE id = ?");
        $stmt->execute([$marketId]);
        return $stmt->fetch();
    }
    // Fallback: look up by city name
    $stmt = $db->prepare("SELECT id, market_key FROM markets WHERE city = ? AND level = 'city' LIMIT 1");
    $stmt->execute([$city]);
    return $stmt->fetch();
}

function getComplianceItems($db, $marketId) {
    $stmt = $db->prepare("
        SELECT * FROM compliance_templates
        WHERE market_id = ?
        ORDER BY sort_order ASC
    ");
    $stmt->execute([$marketId]);
    return $stmt->fetchAll();
}
```

### Step 5: Refactor Every File

For each of the 20 files listed above:

1. **Find every hardcoded geographic string** (grep for: Carmel, Pacific Grove, Monterey, Marina, Seaside, Del Rey, Pebble Beach, Sand City, Peninsula)
2. **Replace with database lookups or tenant config references**
3. **For UI strings:** Pull from `market_terminology` table or tenant branding config
4. **For email/campaign copy:** Pull from `market_ai_prompts` or template table
5. **For social posts (admin.php lines 1425-1456):** Replace template strings with AI generation call (see instruction 10-AI-COPY-ENGINE.md) or market-specific templates from database

### Step 6: Update fogbreak.html

- Remove "Monterey Peninsula" from any header, label, or placeholder
- Replace city dropdown options with dynamic load from `/api/markets.php?action=list`
- Replace jurisdiction references with market-aware lookups
- Meta description in `<head>` should pull from tenant config, not hardcode

### Step 7: Update manifest.json and index.html

- `manifest.json`: name/description should be generic ("Fogbreak" not "Matthams Group — Monterey Peninsula")
- `index.html`: All branding pulled from tenant config

### Step 8: Verify Zero References Remain

Run: `grep -rn -i "carmel\|pacific grove\|monterey\|peninsula\|marina\|seaside\|del rey\|pebble beach\|sand city" app/`

The ONLY results should be in:
- `db/seeds/monterey-peninsula.sql` (the data migration — this is correct)
- `ARCHITECTURE.html` (historical docs — acceptable, add note that it's Tenant #1 example)
- `CLAUDE.md` (project docs — update to note these are Tenant #1 examples)

Everything else: zero hits.

## Acceptance Criteria
- [ ] `markets` table and related tables created with auto-migration
- [ ] Market config resolution with parent inheritance works
- [ ] All 9 Monterey jurisdictions seeded as database rows
- [ ] `transactions.php` uses database queries instead of hardcoded arrays
- [ ] `deadlines.php` uses database templates instead of hardcoded values
- [ ] `admin.php` social post generation uses market-aware templates
- [ ] `drips.php` campaign templates are market-aware
- [ ] `fogbreak.html` has zero hardcoded city names
- [ ] `outreach-drafts.js` pulls from market templates
- [ ] `grep` finds zero geographic references in application code
- [ ] Adding a new market requires ONLY database inserts, zero code changes
- [ ] All existing functionality works exactly as before for Tenant #1