# 02 — Database Redesign

## Context
Fogbreak replaces Follow Up Boss, ShowingTime, Sisu, Paperless Pipeline, DocuSign, and RealScout. Current stack is MySQL 8 on Bluehost shared hosting. For nationwide scale with strict tenant isolation, we migrate to PostgreSQL with row-level security. This instruction covers the full schema redesign including new tables for every replacement platform.

## Prerequisites
- `01-GEOGRAPHIC-EXTRACTION.md` must be complete (market template tables exist)

## What You're Doing
1. Design the complete PostgreSQL schema covering ALL platform replacements
2. Build migration scripts from current MySQL schema
3. Add row-level security for tenant isolation
4. Create JSONB config columns for flexible market/tenant settings
5. Update `config.php` for PostgreSQL PDO connection

## New Tables Required

### Showing Management (replaces ShowingTime)
```sql
CREATE TABLE showings (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    property_id INT NOT NULL,
    listing_agent_id INT,
    buyer_agent_id INT,
    buyer_name VARCHAR(255),
    buyer_email VARCHAR(255),
    buyer_phone VARCHAR(50),
    requested_at TIMESTAMPTZ NOT NULL,
    confirmed_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    status VARCHAR(20) DEFAULT 'requested' CHECK (status IN ('requested','confirmed','completed','cancelled','no_show')),
    confirmation_method VARCHAR(20) CHECK (confirmation_method IN ('auto','email','sms','phone','manual')),
    showing_instructions TEXT,
    special_notes TEXT,
    duration_minutes INT DEFAULT 30,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE showing_feedback (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    showing_id INT REFERENCES showings(id),
    agent_id INT,
    overall_rating INT CHECK (overall_rating BETWEEN 1 AND 5),
    price_opinion VARCHAR(20) CHECK (price_opinion IN ('too_high','about_right','too_low','no_opinion')),
    buyer_interest VARCHAR(20) CHECK (buyer_interest IN ('very_interested','somewhat','not_interested','no_opinion')),
    feedback_text TEXT,
    is_anonymous BOOLEAN DEFAULT FALSE,
    submitted_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE showing_routes (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    agent_id INT NOT NULL,
    route_date DATE NOT NULL,
    optimized_order JSONB,
    total_drive_minutes INT,
    total_showings INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE showing_preferences (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    property_id INT NOT NULL,
    confirmation_type VARCHAR(20) DEFAULT 'manual' CHECK (confirmation_type IN ('auto','manual','appointment_center')),
    min_notice_hours INT DEFAULT 2,
    max_daily_showings INT,
    blackout_times JSONB,
    showing_instructions TEXT,
    lockbox_info TEXT,
    require_confirmed_appointment BOOLEAN DEFAULT TRUE,
    notify_seller BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### Analytics & Coaching (replaces Sisu)
```sql
CREATE TABLE kpi_definitions (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    metric_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    calculation_type VARCHAR(20) CHECK (calculation_type IN ('count','sum','average','ratio','custom')),
    source_table VARCHAR(100),
    source_field VARCHAR(100),
    unit VARCHAR(20) DEFAULT 'number',
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE agent_goals (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    agent_id INT NOT NULL,
    metric_key VARCHAR(100) NOT NULL,
    target_value DECIMAL(12,2) NOT NULL,
    period_type VARCHAR(20) CHECK (period_type IN ('daily','weekly','monthly','quarterly','yearly')),
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE agent_metrics_daily (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    agent_id INT NOT NULL,
    metric_date DATE NOT NULL,
    calls INT DEFAULT 0,
    texts INT DEFAULT 0,
    emails_sent INT DEFAULT 0,
    appointments_set INT DEFAULT 0,
    appointments_held INT DEFAULT 0,
    offers_written INT DEFAULT 0,
    offers_accepted INT DEFAULT 0,
    listings_taken INT DEFAULT 0,
    closings INT DEFAULT 0,
    gci DECIMAL(12,2) DEFAULT 0,
    custom_metrics JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, agent_id, metric_date)
);

CREATE TABLE leaderboards (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    metric_key VARCHAR(100) NOT NULL,
    period_type VARCHAR(20) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    display_on_tv BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE coaching_sessions (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    coach_id INT NOT NULL,
    agent_id INT NOT NULL,
    session_date DATE NOT NULL,
    notes TEXT,
    ai_insights JSONB,
    action_items JSONB,
    status VARCHAR(20) DEFAULT 'scheduled' CHECK (status IN ('scheduled','completed','cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE sales_challenges (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    metric_key VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    prize_description TEXT,
    point_value DECIMAL(10,2) DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### Transaction Coordination (enhances Paperless Pipeline replacement)
```sql
CREATE TABLE tc_workflows (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    market_id INT REFERENCES markets(id),
    name VARCHAR(255) NOT NULL,
    transaction_type VARCHAR(50) CHECK (transaction_type IN ('buyer','seller','dual','lease','referral','new_build')),
    task_template JSONB NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tc_task_instances (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    transaction_id INT NOT NULL,
    workflow_id INT REFERENCES tc_workflows(id),
    task_name VARCHAR(255) NOT NULL,
    assigned_to INT,
    due_date DATE,
    completed_at TIMESTAMPTZ,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','in_progress','completed','blocked','skipped')),
    depends_on INT[],
    notes TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### Property Matching (replaces RealScout)
```sql
CREATE TABLE smart_searches (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    client_id INT NOT NULL,
    agent_id INT,
    name VARCHAR(255),
    criteria JSONB NOT NULL,
    ai_preferences JSONB,
    match_score_threshold INT DEFAULT 70,
    alert_frequency VARCHAR(20) DEFAULT 'instant' CHECK (alert_frequency IN ('instant','daily','weekly')),
    is_active BOOLEAN DEFAULT TRUE,
    last_matched_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE property_matches (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    search_id INT REFERENCES smart_searches(id),
    property_id INT NOT NULL,
    match_score INT CHECK (match_score BETWEEN 0 AND 100),
    ai_explanation TEXT,
    client_reaction VARCHAR(20) CHECK (client_reaction IN ('loved','liked','neutral','disliked','hidden')),
    alerted_at TIMESTAMPTZ,
    viewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### AI & Marketing
```sql
CREATE TABLE ai_conversations (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    agent_id INT,
    client_id INT,
    channel VARCHAR(20) CHECK (channel IN ('web_chat','sms','email','portal','internal')),
    messages JSONB NOT NULL DEFAULT '[]',
    context JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE marketing_campaigns (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    property_id INT,
    campaign_type VARCHAR(30) CHECK (campaign_type IN ('listing_launch','open_house','price_reduction','just_sold','market_update','custom')),
    channels JSONB DEFAULT '[]',
    content JSONB DEFAULT '{}',
    schedule JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','scheduled','running','completed','paused')),
    ai_generated BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE social_posts (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    campaign_id INT REFERENCES marketing_campaigns(id),
    platform VARCHAR(20) CHECK (platform IN ('instagram','facebook','linkedin','tiktok','x')),
    content TEXT,
    media_urls JSONB DEFAULT '[]',
    hashtags JSONB DEFAULT '[]',
    scheduled_at TIMESTAMPTZ,
    posted_at TIMESTAMPTZ,
    platform_post_id VARCHAR(255),
    engagement_data JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','scheduled','posted','failed')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE virtual_staging_jobs (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    property_id INT,
    original_image_url TEXT NOT NULL,
    staged_image_url TEXT,
    room_type VARCHAR(50),
    style VARCHAR(50),
    provider VARCHAR(50),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','processing','completed','failed')),
    cost DECIMAL(8,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE video_generation_jobs (
    id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(id),
    property_id INT,
    source_images JSONB NOT NULL,
    output_url TEXT,
    voiceover_url TEXT,
    duration_seconds INT,
    provider VARCHAR(50),
    style VARCHAR(50),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','processing','completed','failed')),
    cost DECIMAL(8,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

## Row-Level Security

After creating all tables, enable RLS:

```sql
-- Enable RLS on every tenant-aware table
ALTER TABLE showings ENABLE ROW LEVEL SECURITY;
ALTER TABLE showing_feedback ENABLE ROW LEVEL SECURITY;
-- ... repeat for ALL tables with tenant_id ...

-- Create policy: users can only see their own tenant's data
CREATE POLICY tenant_isolation ON showings
    USING (tenant_id = current_setting('app.tenant_id')::INT);
-- ... repeat for ALL tables ...
```

## Migration Script

Create `db/migrations/001_mysql_to_postgres.sql` that:
1. Creates all existing tables (from MySQL CREATE TABLE statements in each PHP file)
2. Creates all new tables listed above
3. Enables RLS on every tenant-aware table
4. Creates indexes for common queries
5. Adds JSONB columns where flexible config is needed

Create `db/migrations/002_migrate_data.py` (Python script) that:
1. Connects to old MySQL database
2. Reads all existing data
3. Transforms and inserts into PostgreSQL
4. Verifies row counts match

## Update config.php

Update `config.php.example` to support PostgreSQL:

```php
define('DB_DRIVER', 'pgsql');  // 'mysql' or 'pgsql'
define('DB_HOST', 'localhost');
define('DB_PORT', 5432);
define('DB_NAME', 'fogbreak');
define('DB_USER', '');
define('DB_PASS', '');

function getDB() {
    static $pdo = null;
    if ($pdo === null) {
        $dsn = DB_DRIVER === 'pgsql'
            ? 'pgsql:host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME
            : 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';
        $pdo = new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        // Set tenant context for RLS
        if (DB_DRIVER === 'pgsql' && isset($_SESSION['tenant_id'])) {
            $pdo->exec("SET app.tenant_id = " . intval($_SESSION['tenant_id']));
        }
    }
    return $pdo;
}
```

## Acceptance Criteria
- [ ] All existing MySQL tables recreated in PostgreSQL
- [ ] All new tables for showings, analytics, coaching, TC workflows, smart searches, AI, marketing created
- [ ] Row-level security enabled on every tenant-aware table
- [ ] Data migration script moves all existing data
- [ ] config.php supports both MySQL and PostgreSQL (backward compatible)
- [ ] All existing API modules work with PostgreSQL
- [ ] Every table has tenant_id — no exceptions