# GBP Dashboard — Gemini Flash Execution Guide

> **📋 WHY THIS FILE EXISTS**: This is the EXACT CODE RECIPE for Flash. Every step has the
> precise Python/SQL/HTML code blocks to write, the exact file paths, and validation commands
> to run after each step. Flash follows this file line-by-line when building. It also contains
> anti-hallucination rules and common pitfalls. **Do NOT change code blocks here unless you
> want Flash to build differently.**

## PURPOSE

This document is a step-by-step execution manual for an AI coding agent (Gemini 3 Flash). Each step has explicit instructions, expected code blocks, validation checks, and human intervention markers. Follow steps IN ORDER. Do NOT skip ahead. Do NOT hallucinate file contents — always read files before editing.

---

## RULES FOR THE AGENT

1. **NEVER guess file contents** — always read the file first, then edit
2. **NEVER skip validation** — after each step, run the check listed
3. **If a step says HUMAN REQUIRED** — STOP and tell the user what they need to do manually
4. **If something fails** — do NOT retry more than 2 times. Report the error to the user
5. **Keep mock data working** — never break the mock path while building the real path
6. **One phase at a time** — complete all steps in a phase before moving to the next

---

## PHASE 0: PROJECT CLEANUP & RESTRUCTURE

### Step 0.1 — Clean Commented Code

**Action**: Remove lines 1-85 from `frontend/index.html` (the commented-out V1 HTML)
**Action**: Remove lines 1-63 from `frontend/app.js` (the commented-out V1 JS)
**Validation**: Open both files, confirm no `<!-- ... -->` comment blocks or `// ...` blocks of old V1 code remain. Active code should start at line 1.

### Step 0.2 — Create Backend Folder Structure

**Action**: Create these empty files/folders:

```text
backend/app/
├── main.py
├── config.py
├── models.py
├── database.py          (ALREADY EXISTS — will be refactored)
├── auth/
│   ├── __init__.py
│   ├── routes.py
│   ├── jwt_handler.py
│   └── google_oauth.py
├── agency/
│   ├── __init__.py
│   └── routes.py
├── client/
│   ├── __init__.py
│   └── routes.py
├── dealer/
│   ├── __init__.py
│   └── routes.py
├── google/
│   ├── __init__.py
│   ├── gbp_api.py
│   ├── mock_api.py      (MOVE existing google_api.py content here)
│   └── sync.py
└── middleware/
    ├── __init__.py
    └── auth.py
```

**Validation**: Run `dir /s /b backend\app\*.py` — should list all files above.

### Step 0.3 — Create config.py

**Action**: Create `backend/app/config.py` that loads all env vars:

```python
import os
from dotenv import load_dotenv
from pathlib import Path

base_dir = Path(__file__).resolve().parent.parent
load_dotenv(dotenv_path=base_dir / ".env")

class Settings:
    DB_NAME = os.getenv("DB_NAME", "fenesta_db")
    DB_USER = os.getenv("DB_USER", "postgres")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "")
    DB_HOST = os.getenv("DB_HOST", "127.0.0.1")
    DB_PORT = int(os.getenv("DB_PORT", 5432))
    JWT_SECRET = os.getenv("JWT_SECRET", "change-me-in-production")
    JWT_ALGORITHM = "HS256"
    JWT_EXPIRY_HOURS = 24
    USE_MOCK_DATA = os.getenv("USE_MOCK_DATA", "true").lower() == "true"
    GOOGLE_CLIENT_ID = os.getenv("GOOGLE_CLIENT_ID", "")
    GOOGLE_CLIENT_SECRET = os.getenv("GOOGLE_CLIENT_SECRET", "")

settings = Settings()
```

**Validation**: `python -c "from app.config import settings; print(settings.DB_NAME)"` should print `fenesta_db`

### Step 0.4 — Update .env

**Action**: Add these new keys to `backend/.env`:

```env
JWT_SECRET=your-random-secret-key-here-minimum-32-chars
USE_MOCK_DATA=true
GOOGLE_CLIENT_ID=
GOOGLE_CLIENT_SECRET=
```

**Keep existing DB_* variables unchanged.**

### Step 0.5 — Refactor main.py (replace server.py)

**Action**: Create `backend/app/main.py`:

```python
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
from app.auth.routes import router as auth_router
from app.agency.routes import router as agency_router
from app.client.routes import router as client_router
from app.dealer.routes import router as dealer_router

app = FastAPI(title="GBP Dashboard API")

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

app.include_router(auth_router, prefix="/api/auth", tags=["Auth"])
app.include_router(agency_router, prefix="/api/agency", tags=["Agency"])
app.include_router(client_router, prefix="/api/client", tags=["Client"])
app.include_router(dealer_router, prefix="/api/dealer", tags=["Dealer"])

@app.get("/api/health")
def health_check():
    return {"status": "ok"}
```

**Validation**: `uvicorn app.main:app --reload` should start without errors. Hit `http://127.0.0.1:8000/api/health` — should return `{"status":"ok"}`

### Step 0.6 — Update requirements.txt

**Action**: Replace `backend/requirements.txt` with:

```text
pg8000==1.31.1
python-dotenv==1.0.1
fastapi
uvicorn
pydantic
PyJWT==2.8.0
bcrypt==4.1.2
authlib==1.3.0
httpx==0.27.0
google-api-python-client==2.118.0
google-auth==2.28.0
google-auth-oauthlib==1.2.0
```

**Action**: Run `pip install -r requirements.txt`

**Validation**: `pip list | findstr PyJWT` should show PyJWT installed

### Step 0.7 — Move mock_api.py

**Action**: Copy contents of `backend/app/google_api.py` into `backend/app/google/mock_api.py`

**Action**: Add `direction_clicks` to the mock data:

```python
import random

def get_mock_google_metrics(location_id: str):
    """MOCK FUNCTION: Simulates GBP API response."""
    return {
        "location_id": location_id,
        "metrics": {
            "total_views": random.randint(10000, 18000),
            "search_impressions": random.randint(3000, 8000),
            "map_views": random.randint(4000, 9000),
            "website_clicks": random.randint(200, 600),
            "calls_made": random.randint(40, 150),
            "direction_clicks": random.randint(80, 350)
        },
        "reviews": {
            "average_rating": round(random.uniform(4.3, 4.9), 1),
            "total_reviews": random.randint(150, 400)
        },
        "trend_data": {
            "labels": ["Nov", "Dec", "Jan", "Feb", "Mar", "Apr"],
            "views": [random.randint(8000, 10000) for _ in range(6)]
        }
    }
```

**Action**: Delete old `backend/app/google_api.py` and old `backend/app/server.py`

**Validation**: `python -c "from app.google.mock_api import get_mock_google_metrics; print(get_mock_google_metrics('test'))"` — should print mock data with direction_clicks

---

## PHASE 1: DATABASE SCHEMA

### Step 1.1 — Create SQL Schema File

**Action**: Create `backend/schema.sql`:

```sql
-- Drop existing tables if rebuilding
DROP TABLE IF EXISTS metrics_cache CASCADE;
DROP TABLE IF EXISTS dealers CASCADE;
DROP TABLE IF EXISTS clients CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(255) NOT NULL,
    role            VARCHAR(20) NOT NULL CHECK (role IN ('agency', 'client', 'dealer')),
    is_first_login  BOOLEAN DEFAULT TRUE,
    is_active       BOOLEAN DEFAULT TRUE,
    created_by      INTEGER REFERENCES users(id),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clients (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    user_id         INTEGER UNIQUE REFERENCES users(id),
    gbp_account_id  VARCHAR(255),
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE dealers (
    id                  SERIAL PRIMARY KEY,
    client_id           INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    user_id             INTEGER UNIQUE REFERENCES users(id),
    dealer_name         VARCHAR(255) NOT NULL,
    google_location_id  VARCHAR(255),
    city                VARCHAR(100),
    state               VARCHAR(100),
    is_active           BOOLEAN DEFAULT TRUE,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE metrics_cache (
    id              SERIAL PRIMARY KEY,
    dealer_id       INTEGER REFERENCES dealers(id),
    metric_date     DATE NOT NULL,
    total_views     INTEGER DEFAULT 0,
    search_impressions INTEGER DEFAULT 0,
    map_views       INTEGER DEFAULT 0,
    website_clicks  INTEGER DEFAULT 0,
    calls_made      INTEGER DEFAULT 0,
    direction_clicks INTEGER DEFAULT 0,
    average_rating  DECIMAL(2,1),
    total_reviews   INTEGER DEFAULT 0,
    fetched_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(dealer_id, metric_date)
);
```

### Step 1.2 — ⚠️ HUMAN REQUIRED: Run Schema

**Tell the user**: "Please run this SQL file against your PostgreSQL database. Open pgAdmin or psql and execute `backend/schema.sql` against the `fenesta_db` database."

**Validation**: After human confirms, connect to DB and run `SELECT table_name FROM information_schema.tables WHERE table_schema='public'` — should show users, clients, dealers, metrics_cache.

### Step 1.3 — Seed Agency Superuser

**Action**: Create `backend/seed.py`:

```python
import bcrypt
from app.database import get_connection

def seed_agency_admin():
    conn = get_connection()
    cursor = conn.cursor()
    
    password = "Admin@123"  # Temporary — will be changed on first login
    password_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode()
    
    cursor.execute("""
        INSERT INTO users (email, password_hash, full_name, role, is_first_login)
        VALUES (%s, %s, %s, %s, %s)
        ON CONFLICT (email) DO NOTHING
    """, ("admin@hashtagorang.in", password_hash, "HO Admin", "agency", True))
    
    conn.commit()
    cursor.close()
    conn.close()
    print("Agency admin seeded: admin@hashtagorang.in / Admin@123")

if __name__ == "__main__":
    seed_agency_admin()
```

**Action**: Run `python -m seed` from the `backend` directory

**Validation**: `SELECT email, role FROM users` should show the agency admin row

---

## PHASE 2: AUTH SYSTEM

### Step 2.1 — JWT Handler

**Action**: Create `backend/app/auth/jwt_handler.py`:

```python
import jwt
import datetime
from app.config import settings

def create_token(user_id: int, email: str, role: str) -> str:
    payload = {
        "user_id": user_id,
        "email": email,
        "role": role,
        "exp": datetime.datetime.utcnow() + datetime.timedelta(hours=settings.JWT_EXPIRY_HOURS)
    }
    return jwt.encode(payload, settings.JWT_SECRET, algorithm=settings.JWT_ALGORITHM)

def verify_token(token: str) -> dict:
    try:
        return jwt.decode(token, settings.JWT_SECRET, algorithms=[settings.JWT_ALGORITHM])
    except jwt.ExpiredSignatureError:
        return None
    except jwt.InvalidTokenError:
        return None
```

**Validation**: Write a quick test — create a token, then verify it, print decoded payload.

### Step 2.2 — Auth Middleware

**Action**: Create `backend/app/middleware/auth.py`:

```python
from fastapi import Request, HTTPException, Depends
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from app.auth.jwt_handler import verify_token

security = HTTPBearer()

async def get_current_user(credentials: HTTPAuthorizationCredentials = Depends(security)):
    token = credentials.credentials
    payload = verify_token(token)
    if not payload:
        raise HTTPException(status_code=401, detail="Invalid or expired token")
    return payload

def require_role(*allowed_roles):
    async def role_checker(current_user: dict = Depends(get_current_user)):
        if current_user["role"] not in allowed_roles:
            raise HTTPException(status_code=403, detail="Access denied")
        return current_user
    return role_checker
```

### Step 2.3 — Auth Routes

**Action**: Create `backend/app/auth/routes.py` with login and change-password endpoints.

- `POST /login` — accepts email+password, verifies with bcrypt, returns JWT + role + is_first_login
- `POST /change-password` — accepts old_password + new_password, hashes new, updates DB, sets is_first_login=false

**Validation**: Use curl or browser to test login with the seeded agency account. Should receive a JWT token.

### Step 2.4 — Google OAuth Routes (Alternate Login)

**Action**: Create `backend/app/auth/google_oauth.py`

- `GET /google` — builds Google OAuth URL, redirects user
- `GET /google/callback` — exchanges code for user info, checks email in users table, returns JWT

**⚠️ HUMAN REQUIRED**: Before this works, user must:

1. Go to console.cloud.google.com
2. Create OAuth 2.0 Web Application credentials
3. Set authorized redirect URI: `http://localhost:8000/api/auth/google/callback`
4. Copy Client ID + Secret into `.env`

**Agent**: Implement the code but tell user it won't work until they complete the human steps above.

### Step 2.5 — Frontend Login Page

**Action**: Rebuild `frontend/index.html` login section:

- Email + password form
- "Sign in with Google" button (links to `/api/auth/google`)
- On successful login: store JWT in localStorage, check role, redirect:
  - agency → show agency dashboard
  - client → show client dashboard
  - dealer → show dealer dashboard
- If `is_first_login` is true → show password change form first

**Validation**: Open frontend in browser, login with `admin@hashtagorang.in` / `Admin@123`. Should get forced to change password. After changing, should see agency dashboard (empty for now).

---

## PHASE 3: AGENCY DASHBOARD

### Step 3.1 — Agency Routes

**Action**: Create `backend/app/agency/routes.py`:

- `GET /clients` — requires role=agency, queries clients joined with users, returns list
- `POST /clients` — requires role=agency, creates user (role=client) + client record, generates temp password with `secrets.token_urlsafe(8)`, returns the temp password
- `DELETE /clients/{id}` — soft delete (is_active=false on both user and client)

### Step 3.2 — Agency Frontend

**Action**: Build agency dashboard section in the frontend:

- Table: Client Name | Email | # Dealers | Status | Actions
- "Add Client" button → modal with: Client Name, Email, GBP Account ID (optional)
- Shows generated temp password after creation (so agency can share with client)

**Validation**: Login as agency, add a test client "Fenesta" with email `fenesta@example.com`. Should see it in the table.

---

## PHASE 4: CLIENT DASHBOARD

### Step 4.1 — Client Routes

**Action**: Create `backend/app/client/routes.py`:

- `GET /dealers` — lists dealers for the logged-in client
- `POST /dealers` — creates user (role=dealer) + dealer record + temp password
- `GET /dashboard/cumulative` — aggregates metrics across all client's dealers
- `GET /dashboard/dealer/{id}` — single dealer metrics (must belong to this client!)

### Step 4.2 — Client Frontend

**Action**: Build client dashboard:

- **Tab 1 — Cumulative**: 7 KPI cards (including Direction Clicks) + trend chart showing aggregated data
- **Tab 2 — Dealers**: Table of dealers with mini-metrics, click to drill down
- **Tab 3 — Manage**: Add/edit/deactivate dealers

**⚠️ RECHECK**: When building cumulative metrics:

- SUM all count metrics (views, clicks, calls, directions)
- WEIGHTED AVERAGE for rating (weight = total_reviews per dealer)
- Trend data = sum per month across all dealers

**Validation**: Login as the Fenesta client. Add 2-3 test dealers. Cumulative dashboard should show combined numbers.

---

## PHASE 5: DEALER DASHBOARD (REFACTOR)

### Step 5.1 — Dealer Routes

**Action**: Create `backend/app/dealer/routes.py`:

- `GET /dashboard` — fetches metrics for the logged-in dealer's location_id only
- `POST /post` — creates a Google post (mock for now)

### Step 5.2 — Dealer Frontend

**Action**: Refactor existing dealer dashboard:

- Add 7th KPI card: "Direction Clicks" with a teal/cyan border color
- Use JWT auth for all API calls
- Dealer can only see their own data

**Validation**: Login as a test dealer. Should see 7 KPI cards including Direction Clicks. Should NOT be able to access client or agency pages.

---

## PHASE 6: GOOGLE API INTEGRATION

### ⚠️ HUMAN REQUIRED — COMPLETE BEFORE STARTING THIS PHASE

The user must have:

1. Applied for and received Google Business Profile API access
2. Enabled all required APIs in Google Cloud Console
3. Created a Service Account and added it as Manager on the GBP account
4. Placed the service account JSON key file in `backend/` as `service_account.json`

**Agent**: Ask the user "Have you completed the Google API approval steps? Do you have the service account key file?" If NO, skip this phase and keep using mock data.

### Step 6.1 — Build GBP API Wrapper

**Action**: Create `backend/app/google/gbp_api.py`:

- `get_locations(account_id)` — calls Business Information API
- `get_performance_metrics(location_id, start_date, end_date)` — calls Performance API with these metrics:
  - BUSINESS_IMPRESSIONS_DESKTOP_SEARCH, BUSINESS_IMPRESSIONS_MOBILE_SEARCH
  - BUSINESS_IMPRESSIONS_DESKTOP_MAPS, BUSINESS_IMPRESSIONS_MOBILE_MAPS
  - WEBSITE_CLICKS, CALL_CLICKS, BUSINESS_DIRECTION_REQUESTS
- `create_post(location_id, content)` — calls My Business API v4

### Step 6.2 — Build Sync Service

**Action**: Create `backend/app/google/sync.py`:

- Function that iterates all active dealers, fetches metrics, upserts into metrics_cache
- Can be called manually via `/api/internal/sync-metrics` or via cron

### Step 6.3 — Toggle Mock vs Real

**Action**: In config.py, check `USE_MOCK_DATA`. If true, use mock_api. If false, use gbp_api.

**Validation**: Set `USE_MOCK_DATA=false`, run sync, check metrics_cache table has real data.

---

## CHECKLIST — THINGS TO RECHECK DURING EXECUTION

### Security Checks

- [ ] Passwords are NEVER stored in plaintext — always bcrypt hashed
- [ ] JWT secret is at least 32 characters and loaded from .env
- [ ] Role-based access is enforced on EVERY endpoint (not just frontend)
- [ ] Client can only see their OWN dealers (filter by client_id)
- [ ] Dealer can only see their OWN data (filter by dealer's user_id)
- [ ] Google credentials are in .env or separate file, NEVER in code
- [ ] No SQL injection — always use parameterized queries (%s placeholders)

### Data Integrity Checks

- [ ] Cumulative dashboard correctly SUMs count metrics
- [ ] Cumulative rating is WEIGHTED AVERAGE not simple average
- [ ] Direction clicks field exists in mock data, DB schema, and frontend
- [ ] Trend chart handles dealers with different amounts of data gracefully
- [ ] Deactivated users/clients/dealers are excluded from all queries

### Frontend Checks

- [ ] JWT is attached to every API request after login
- [ ] Role-based routing works (agency→agency page, client→client page, dealer→dealer page)
- [ ] Forced password change screen appears on first login
- [ ] Logout clears JWT from localStorage
- [ ] "Sign in with Google" button is visible but shows helpful error if not configured

### API Call Checks

- [ ] All endpoints return proper HTTP status codes (200, 201, 400, 401, 403, 404, 500)
- [ ] Error responses have a consistent format: `{"detail": "error message"}`
- [ ] CORS allows the frontend origin
- [ ] Database connections are properly closed (use try/finally)

---

## COMMON PITFALLS — DO NOT MAKE THESE MISTAKES

1. **DO NOT** create separate login pages for each role. Use ONE login page that detects role from JWT.
2. **DO NOT** store JWT in cookies without httpOnly flag. Use localStorage for simplicity in MVP.
3. **DO NOT** call Google API on every dashboard load. Use cached data from metrics_cache table.
4. **DO NOT** let clients see other clients' dealers. Always filter by the logged-in user's client_id.
5. **DO NOT** delete the mock data path. Keep `USE_MOCK_DATA=true` as default for development.
6. **DO NOT** forget to handle pagination when listing locations from Google API.
7. **DO NOT** hardcode the agency email. The system should support multiple agency users in the future.
8. **DO NOT** make the frontend a SPA with a framework. Keep it as vanilla HTML/JS — it's simpler and already working.