# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

**Sunbright Core** — FP&A (Financial Planning & Analysis) platform for Sunbright, a solar panel installation company with additional HVAC and roofing entities. Merges capabilities of DataRail + LiveFlow: pulls financial data from QuickBooks Online, displays in a dashboard with drill-down, and exports to Google Sheets with embedded drill-down links. Supports exporting to user-owned spreadsheet templates with formula preservation.

## Tech Stack

- **Backend:** PHP 8.x + Slim 4 + PHP-DI
- **Database:** MySQL 8.x
- **Config:** vlucas/phpdotenv (`.env` → `$_ENV`)
- **HTTP Client:** Guzzle 7.x
- **Google API:** `google/apiclient` (OAuth login + Sheets export)
- **Frontend:** HTML/CSS/JS vanilla (no framework)
- **CSS:** Tailwind CSS (CDN) + CSS custom properties (tokens.css)
- **Charts:** Chart.js (CDN)
- **Icons:** Font Awesome 6.5 (CDN)
- **Fonts:** Google Fonts (Inter)

## Running

**MAMP** (primary):
- Document Root: `/Applications/MAMP/htdocs/sunbright-fpa/public`
- Apache port 80, MySQL 127.0.0.1:3306
- URL: `http://localhost/`
- MySQL binary: `/Applications/MAMP/Library/bin/mysql80/bin/mysql`

**Dev server** (alternative):
```bash
php -S localhost:3000 -t public public/router.php
```

### Database setup
```bash
php migrations/migrate.php
```

## Architecture

```
public/
  index.php                       → Slim 4 front controller (DI: PDO, PhpRenderer, IntegrationRegistry,
                                    ReportCatalog, QboSyncService, DrilldownService, RequireAuth middleware)
  legal/
    privacy.html                  → Privacy Policy (public, served as static; required by Intuit App Assessment)
    terms.html                    → Terms of Service / EULA (public, served as static)
  css/
    tokens.css                    → CSS custom properties: light (:root) + dark (html.dark)
    theme.css                     → Component styles using var(--token)
  js/
    shared/
      api.js                      → App.get/post/del/formatMoney/esc (XSS protection)
      theme.js                    → Light/dark toggle (localStorage)
      sidebar.js                  → Collapsible sidebar (localStorage)
    components/
      modals.js                   → showModal/closeModal, triggerSync (navbar refresh)
      charts.js                   → initForecastChart/initCommissionChart (theme-aware)
      chat.js                     → initChat/sendChat (Phase 1 simulation)
    pages/
      dashboard.js                → Load report links + init charts/chat
      report-viewer.js            → Collapsible sections, drill-down links, exportToSheets,
                                    export templates (syncToSheet, saveExportTemplate, removeExportTemplate)
      sync.js                     → runSync, selectAllReports
      connect.js                  → disconnectIntegration

src/
  Config/Qbo.php                  → QBO OAuth URLs, token TTLs, auth helpers
  Helpers/ApiResponse.php         → Standardized JSON: success() / error()
  Middleware/RequireAuth.php      → Google OAuth session check, redirects to /auth/google
  Services/
    GoogleClientFactory.php       → Single source for Google\Client (create + fromToken with auto-refresh)
    GoogleSheetsExport.php        → Export report to Sheets via cell API (no formulas, locale-safe)
                                    Two modes: exportReport (new sheet, formatted) + updateExistingSheet (template-friendly clean data)
                                    Both share buildReportTab/buildDetailTab with bool $clean flag
    QboSyncService.php            → QBO data pipeline (token refresh, fetch, parse, cache)
    QboReportRenderer.php         → Parse QBO JSON → table rows, findSection for drill-down
                                    Constants: DETAIL_PAIRS, VISIBLE_REPORTS_FILTER, QBO_TXN_PATHS
    DrilldownService.php          → Cache check → fetch QBO Detail on-demand → extract section
    ReportCatalog.php             → Report definitions in DB, preloadAll(), single source of truth
    TokenCipher.php               → AES-256-GCM encryption helper for QBO OAuth tokens at rest
                                    encrypt()/decrypt() with v1: prefix for graceful migration of legacy plaintext tokens
  Integrations/
    IntegrationInterface.php      → Contract for all data sources
    IntegrationRegistry.php       → Registry with getSummary(), all(), connected()
    QuickBooks/
      QuickBooksIntegration.php   → Full implementation (OAuth, sync, reports via catalog)
    Sunbase/
      SunbaseIntegration.php      → Stub + README for another developer
    SunbriteCommissions/
      SunbriteIntegration.php     → Stub + README
  Controllers/
    AuthController.php            → Google OAuth login/logout (uses GoogleClientFactory)
    DashboardController.php       → GET / with KPIs from real QBO cache
    ConnectController.php         → Generic: all integrations via registry
    SyncController.php            → Selective sync + refresh (cached only) + remove unselected
    ReportsController.php         → Hub, viewer (summarize selector), drill-down page
    TransactionsController.php    → Legacy drill-down API with smart account mapping
    ExportController.php          → Google Sheets export to new sheet (uses FetchesGLDetail trait)
    ExportTemplateController.php  → CRUD + sync for export templates (map reports to existing sheets)
    Traits/
      FetchesGLDetail.php         → Shared GL detail fetching for ExportController + ExportTemplateController

templates/
  layout.php                      → Shared head, conditional JS per page, window.BASE_PATH
  partials/                       → sidebar (dynamic, collapsible, real user), navbar (theme toggle),
                                    kpi-cards, forecast-chart, ai-chat, commission-chart, alerts, modals
  pages/                          → dashboard, reports (hub), report-viewer, drilldown, sync, connect

appsscript/                       → Google Sheets drill-down sidebar (Code.gs + Sidebar.html + manifest + README)
                                    Bound script pasted into template spreadsheets; reads the drill link of the
                                    selected cell and calls GET /api/drilldown with the user's Google ID token
```

## Key Patterns

### Authentication
Google OAuth with `userinfo.email`, `userinfo.profile`, `spreadsheets`, and `drive.file` scopes (per-file Drive access — not full Drive). `RequireAuth` middleware redirects unauthenticated users to `/auth/google`. Only users in `users` table with `is_allowed=1` can access. Session stores user info + Google access/refresh token (server-side session only, never in DB).

### QBO OAuth Security
- **CSRF state nonce**: `ConnectController::connect()` generates a 16-byte random nonce, stores it in `$_SESSION['oauth_state'][integrationId:slug]`, and embeds it in the OAuth state parameter (`integrationId:slug:nonce`). The callback validates with `hash_equals()` and deletes the nonce immediately (single-use, replay-resistant).
- **Token encryption at rest**: QBO access and refresh tokens are encrypted with AES-256-GCM via `TokenCipher` before being written to `qbo_tokens`. Key is derived from `APP_SECRET` via SHA-256. Legacy plaintext tokens (no `v1:` prefix) are still readable and get re-encrypted on the next refresh — no migration script needed.
- **Token refresh**: only when expired (`Qbo::TOKEN_REFRESH_GRACE_SECONDS` = 60s before expiry). Failed refresh → exception → user prompted to reconnect (no silent retries).
- **Disconnect**: `QuickBooksIntegration::disconnect()` deletes `qbo_tokens`, `cached_reports`, `cached_transactions`, and `sync_log` for that company in one transaction. User-created configurations (`custom_reports`, `export_templates`) are preserved across reconnect.

### Google Client
`GoogleClientFactory::create()` — configured with all scopes, offline access.
`GoogleClientFactory::fromToken($token)` — for API calls, auto-refreshes expired tokens.
Never create `new Google\Client()` directly.

### Report Viewing
Reports support `?summarize=Month|Quarter|Year|Total` (for timeseries: P&L, BS, CashFlow, TrialBalance). Each summarize version cached separately in `cached_reports.summarize_by`. Non-timeseries reports (ARAgingSummary, etc.) always use 'Total'.

### Drill-Down
Live from QBO: `DrilldownService::getDrilldown()` fetches GeneralLedger filtered by account × date range (accepts optional `accountId` to skip name resolution — drill links from exported Sheets carry it). Navigation by page URL (not modal): `/reports/{type}/drilldown?section=X&from=A&to=B[&column=H][&accountId=N]`. Breadcrumb + link to QBO per transaction. JSON variant `GET /api/drilldown` (same params + `company`) authenticates itself: web session OR Bearer Google ID token verified against the `users` allowlist (used by the Sheets sidebar; route bypasses RequireAuth). Optional audience pinning via `SIDEBAR_TOKEN_AUDIENCE`.

### Google Sheets Export
Uses Sheets API cell data (not HYPERLINK formulas) to avoid locale issues (`;` vs `,`). Numbers written as `numberValue` (not strings) so formulas can reference them. Export uses the currently viewed summarize version.

Two export modes:
- **New Sheet** (`ExportController::export`): Creates a new Google Spreadsheet with Tab 1 (report) + Tab 2 (detail/drill-down). Full formatting: indented account names, bold headers/totals, background colors, Spanish headers ("Cuenta", "Monto"). Drill-down links + QBO transaction links.
- **Update Existing Sheet** (`ExportTemplateController::sync`): Template-friendly clean data mode. Account names without indentation (VLOOKUP-safe), no bold on data rows, English headers ("Account", "Amount"), minimal formatting (frozen header + number format only). Only clears system columns (preserves user-added columns to the right). Drill-down links Data→Detail and QBO links in Detail tab are preserved.

Both modes share `buildReportTab()` and `buildDetailTab()` via `bool $clean` flag.

**Cell drill links**: with `APP_URL` set in `.env`, numeric cells in BOTH modes hyperlink to the app's drill-down page for (account × column period) — `drillContext()` + per-column `columnRanges` metadata + `accountId`, QuickBooks-style (only the transactions behind that value). Without `APP_URL`, cells fall back to intra-sheet anchors into the Detail tab (`#gid=...&range=A{n}`). The same link doubles as metadata carrier for the Sheets sidebar (`appsscript/`).

### Export Templates
Users configure mappings via the report viewer UI: paste a Google Sheets URL → system creates/updates `Data_*` and `Detail_*` tabs on sync. Config stored in `export_templates` table. Supports:
- Single report sync (button "Actualizar Sheet")
- Sync all reports mapped to one spreadsheet (`POST /api/export-templates/sync-sheet`)
- Dropdown menu with options: export new, sync existing, configure mapping, remove mapping

Template sync writes clean data so users can build presentation layers on top:
- User can create a separate tab with formulas referencing the Data tab (e.g., `=Data_ProfitAndLoss!B5`)
- User can add custom columns to the right of Data/Detail tabs (e.g., "Modified Amount" formulas) — these survive re-sync
- Account names are plain text (no indentation) so VLOOKUP/INDEX-MATCH work

### Integration Architecture
All data sources implement `IntegrationInterface`. `IntegrationRegistry::getSummary()` builds common data for connect/sync pages. Report catalog in `integration_report_catalog` table, preloaded once per request.

### Sync
- **Navbar ↻**: `POST /api/sync/refresh` — re-syncs only what's already cached
- **Sync page**: `POST /api/sync/trigger` — syncs selected reports, removes unselected from cache
- **Report viewer**: fetches on-demand when changing summarize or first drill-down

## Database Tables

- `companies` — name, slug, qbo_realm_id
- `qbo_tokens` — per-company OAuth tokens, encrypted at rest with AES-256-GCM (auto-refreshed)
- `sync_log` — indexed on (company_id, id DESC)
- `cached_reports` — UNIQUE on (company_id, report_type, period_start, period_end, summarize_by)
- `cached_transactions` — indexed on company+date, company+account (legacy, used by TransactionsController)
- `users` — email allowlist with roles (admin/analyst/viewer), is_allowed flag
- `integration_report_catalog` — report definitions per integration, preloaded
- `export_templates` — maps reports to existing Google Sheets tabs per company, UNIQUE on (company_id, spreadsheet_id, report_type, summarize_by)

## API Endpoints

| Method | Route | Auth | Description |
|--------|-------|------|-------------|
| GET | `/auth/google` | No | Start Google OAuth |
| GET | `/auth/google/callback` | No | OAuth callback |
| GET | `/auth/logout` | No | Destroy session |
| GET | `/` | Yes | Dashboard with KPIs |
| GET | `/connect` | Yes | All integrations |
| GET | `/sync` | Yes | Selective sync UI |
| GET | `/reports` | Yes | Synced reports hub |
| GET | `/reports/{type}` | Yes | Report viewer (?summarize=Month/Quarter/Year/Total) |
| GET | `/reports/{type}/drilldown` | Yes | Drill-down page (?section=X) |
| POST | `/api/sync/trigger` | Yes | Sync selected + remove unselected |
| POST | `/api/sync/refresh` | Yes | Re-sync cached reports only |
| GET | `/api/sync/status` | Yes | Last sync per company |
| GET | `/api/kpis` | Yes | Dashboard KPIs |
| GET | `/api/transactions` | Yes | Legacy drill-down with filters |
| GET | `/api/drilldown` | Self* | JSON drill-down (web session OR Google ID token on users allowlist — Sheets sidebar) |
| POST | `/api/export/google-sheets` | Yes | Export to new Google Sheet |
| GET | `/api/export-templates` | Yes | List export template mappings |
| POST | `/api/export-templates` | Yes | Create/update export template mapping |
| DELETE | `/api/export-templates/{id}` | Yes | Remove export template mapping |
| POST | `/api/export-templates/{id}/sync` | Yes | Sync single report to mapped sheet |
| POST | `/api/export-templates/sync-sheet` | Yes | Sync all reports mapped to a sheet |
| GET | `/integrate/{id}/connect/{slug}` | Yes | Start integration OAuth |

## Current State — Fase 1 Complete

### Done
- [x] QBO OAuth connected (sandbox)
- [x] Selective sync with 24 QBO report types in catalog
- [x] Dashboard with real KPIs from QBO
- [x] Report viewer: hierarchical table, collapsible sections, sticky header, summarize selector
- [x] Drill-down on-demand from QBO Detail reports with breadcrumb navigation
- [x] Google OAuth login with user allowlist
- [x] Google Sheets export with drill-down hyperlinks (cell API, locale-safe)
- [x] Export to existing user-owned sheets with formula preservation (export templates)
- [x] Light/dark theme, collapsible sidebar, JS modular
- [x] Integration architecture with Sunbase/Sunbrite stubs + READMEs
- [x] Generic connect/sync pages via IntegrationRegistry
- [x] Production hardening: AES-256-GCM token encryption, OAuth CSRF nonce, disconnect purges cache, public Privacy/Terms pages (Intuit App Assessment ready)

### Fase 2 (prepared)
- Sunbase CRM: stub + README at `src/Integrations/Sunbase/`
- Sunbrite Commissions: stub + README at `src/Integrations/SunbriteCommissions/`