# Sunbright FP&A Platform — Technical Specification

## Stack

| Capa          | Tecnología                                                       |
|---------------|------------------------------------------------------------------|
| Backend       | PHP 8.x + Slim 4                                                 |
| Base de datos | MySQL 8.x                                                        |
| Config        | vlucas/phpdotenv (`.env` → `$_ENV`)                              |
| HTTP client   | Guzzle 7.x (llamadas a QBO API, Google API)                      |
| Frontend      | HTML/CSS/JS vanilla (sin framework)                              |
| CSS framework | Tailwind CSS (CDN)                                               |
| Charts        | Chart.js (CDN)                                                   |
| Icons         | Font Awesome 6.5 (CDN)                                           |
| QBO OAuth     | OAuth 2.0 manual con Guzzle (no hay SDK oficial PHP mantenido)   |
| Google Sheets | Google API PHP Client (`google/apiclient`)                       |

---

## Entorno de desarrollo (MAMP)

| Config | Valor |
|--------|-------|
| MAMP Document Root | `/Applications/MAMP/htdocs/sunbright-fpa/public` |
| Apache | Puerto 80 |
| MySQL | 127.0.0.1:3306 (usuario: root/root) |
| MySQL binary | `/Applications/MAMP/Library/bin/mysql80/bin/mysql` |
| URL base | `http://localhost/` |
| APP_BASE_PATH | _(vacío)_ |
| Base de datos | `sunbright_fpa` |

```bash
# Setup inicial
php migrations/migrate.php

# Servidor alternativo (sin MAMP)
php -S localhost:3000 -t public public/router.php
```

---

## Arquitectura general

```
[QuickBooks Online API]    [Sunbase CRM API]    [Sunbrite Commissions API]
         ↓ OAuth 2.0              ↓ API key              ↓ API key
    ┌─────────────────────────────────────────────────────────────┐
    │                    PHP Backend (Slim 4)                     │
    │                                                             │
    │  /Controllers/    → Route handlers (8 controllers + trait)  │
    │  /Services/       → Lógica de negocio (6 services)          │
    │  /Config/         → Qbo.php (URLs, tokens, auth helpers)    │
    │  /Helpers/        → ApiResponse.php (JSON estandarizado)    │
    │  /Middleware/     → RequireAuth (Google OAuth session)       │
    │  /Integrations/  → IntegrationInterface + Registry          │
    └──────────────┬──────────────────────────────────────────────┘
                   │
              [MySQL 8.x]
         cached_reports, cached_transactions, export_templates,
         companies, qbo_tokens, sync_log, users,
         integration_report_catalog
                   │
    ┌──────────────┴──────────────────────────────────────────────┐
    │              Frontend (vanilla HTML/CSS/JS)                 │
    │                                                             │
    │  Dashboard → KPIs, charts (datos via fetch → API)           │
    │  Report viewer → Tabla jerárquica con drill-down            │
    │  Export → Nuevo sheet o actualizar sheet existente           │
    │  Export templates → Mapear reportes a Google Sheets tabs    │
    └─────────────────────────────────────────────────────────────┘
```

---

## Estructura de carpetas

```
sunbright-fpa/
├── composer.json
├── .env                              # Secrets (gitignored)
├── .env.example                      # Template
├── .gitignore
├── CLAUDE.md
├── public/                           # Document root para el webserver
│   ├── index.php                     # Front controller (Slim bootstrap + DI)
│   ├── css/
│   │   ├── tokens.css                # CSS custom properties: light + dark
│   │   └── theme.css                 # Component styles using var(--token)
│   └── js/
│       ├── shared/
│       │   ├── api.js                # App.get/post/del/formatMoney/esc
│       │   ├── theme.js              # Light/dark toggle (localStorage)
│       │   └── sidebar.js            # Collapsible sidebar (localStorage)
│       ├── components/
│       │   ├── modals.js             # showModal/closeModal, triggerSync
│       │   ├── charts.js             # initForecastChart/initCommissionChart
│       │   └── chat.js               # initChat/sendChat
│       └── pages/
│           ├── dashboard.js          # Load report links + init charts/chat
│           ├── report-viewer.js      # Collapsible sections, drill-down, export,
│           │                         #   export templates (syncToSheet, saveExportTemplate)
│           ├── sync.js               # runSync, selectAllReports
│           └── connect.js            # disconnectIntegration
├── src/
│   ├── Routes.php                    # Registra todas las rutas
│   ├── Config/
│   │   └── Qbo.php                   # URLs OAuth, token TTLs, auth helpers, scopes
│   ├── Helpers/
│   │   └── ApiResponse.php           # success()/error() → JSON estandarizado
│   ├── Middleware/
│   │   └── RequireAuth.php           # Google OAuth session check
│   ├── Controllers/
│   │   ├── AuthController.php        # Google OAuth login/logout
│   │   ├── DashboardController.php   # GET / → KPIs desde QBO cache
│   │   ├── ConnectController.php     # Genérico: todas las integraciones via registry
│   │   ├── SyncController.php        # Sync selectivo + refresh cached
│   │   ├── ReportsController.php     # Hub, viewer, drill-down page
│   │   ├── TransactionsController.php # Legacy drill-down API
│   │   ├── ExportController.php      # Export a nuevo Google Sheet
│   │   ├── ExportTemplateController.php # CRUD + sync para export templates
│   │   └── Traits/
│   │       └── FetchesGLDetail.php   # Shared GL detail fetching
│   ├── Services/
│   │   ├── GoogleClientFactory.php   # Google\Client factory (create + fromToken)
│   │   ├── GoogleSheetsExport.php    # exportReport (nuevo) + updateExistingSheet
│   │   ├── QboSyncService.php        # QBO data pipeline
│   │   ├── QboReportRenderer.php     # Parse QBO JSON → table rows
│   │   ├── DrilldownService.php      # Drill-down on-demand
│   │   └── ReportCatalog.php         # Report definitions in DB
│   └── Integrations/
│       ├── IntegrationInterface.php  # Contract para fuentes de datos
│       ├── IntegrationRegistry.php   # Registry central
│       ├── QuickBooks/
│       │   └── QuickBooksIntegration.php
│       ├── Sunbase/
│       │   └── SunbaseIntegration.php  # Stub + README
│       └── SunbriteCommissions/
│           └── SunbriteIntegration.php # Stub + README
├── templates/
│   ├── layout.php                    # Shared head, conditional JS, BASE_PATH
│   ├── partials/
│   │   ├── sidebar.php               # Dynamic, collapsible, real user
│   │   ├── navbar.php                # Theme toggle, sync button
│   │   ├── kpi-cards.php
│   │   ├── forecast-chart.php
│   │   ├── ai-chat.php
│   │   ├── commission-chart.php
│   │   ├── alerts.php
│   │   └── modals/
│   │       ├── board-deck.php
│   │       └── drilldown.php
│   └── pages/
│       ├── dashboard.php
│       ├── reports.php               # Hub: grid de reportes
│       ├── report-viewer.php         # Visor + export dropdown + config modal
│       ├── drilldown.php
│       ├── sync.php
│       └── connect.php
├── migrations/
│   ├── 001_create_companies.sql
│   ├── 002_create_qbo_tokens.sql
│   ├── 003_create_sync_log.sql
│   ├── 004_create_cached_reports.sql
│   ├── 005_create_cached_transactions.sql
│   ├── 006_create_users.sql
│   ├── 007_create_report_catalog.sql
│   └── 008_create_export_templates.sql
└── docs/
    ├── ROADMAP.md
    └── SPEC.md
```

---

## Schema de Base de Datos

### `companies`
```sql
CREATE TABLE companies (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  slug          VARCHAR(50) NOT NULL UNIQUE,
  qbo_realm_id  VARCHAR(50) UNIQUE DEFAULT NULL,
  is_active     TINYINT(1) DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `qbo_tokens`
```sql
CREATE TABLE qbo_tokens (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  company_id        INT NOT NULL,
  access_token      TEXT NOT NULL,
  refresh_token     TEXT NOT NULL,
  token_type        VARCHAR(20) DEFAULT 'bearer',
  expires_at        DATETIME NOT NULL,
  refresh_expires_at DATETIME NOT NULL,
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_company (company_id),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `sync_log`
```sql
CREATE TABLE sync_log (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  company_id      INT NOT NULL,
  sync_type       ENUM('full', 'incremental', 'cdc') NOT NULL,
  status          ENUM('running', 'completed', 'failed') NOT NULL,
  started_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  completed_at    DATETIME DEFAULT NULL,
  records_synced  INT DEFAULT 0,
  error_message   TEXT DEFAULT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `cached_reports`
```sql
CREATE TABLE cached_reports (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  company_id    INT NOT NULL,
  report_type   VARCHAR(50) NOT NULL,
  period_start  DATE NOT NULL,
  period_end    DATE NOT NULL,
  summarize_by  VARCHAR(20) NOT NULL DEFAULT 'Total',
  report_json   JSON NOT NULL,
  fetched_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_report (company_id, report_type, period_start, period_end, summarize_by),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `cached_transactions`
```sql
CREATE TABLE cached_transactions (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  company_id        INT NOT NULL,
  txn_id            VARCHAR(50) NOT NULL,
  txn_type          VARCHAR(50) NOT NULL,
  txn_date          DATE NOT NULL,
  account_name      VARCHAR(200),
  account_id        VARCHAR(50),
  customer_name     VARCHAR(200),
  vendor_name       VARCHAR(200),
  description       TEXT,
  amount            DECIMAL(15,2) NOT NULL,
  class_name        VARCHAR(100),
  department_name   VARCHAR(100),
  memo              TEXT,
  fetched_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_txn (company_id, txn_id, account_id),
  INDEX idx_company_date (company_id, txn_date),
  INDEX idx_company_account (company_id, account_name),
  INDEX idx_company_type (company_id, txn_type),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `users`
```sql
CREATE TABLE users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  display_name  VARCHAR(100) NOT NULL,
  google_id     VARCHAR(100) UNIQUE,
  role          ENUM('admin', 'analyst', 'viewer') DEFAULT 'analyst',
  is_allowed    TINYINT(1) DEFAULT 0,
  last_login    DATETIME DEFAULT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `integration_report_catalog`
```sql
CREATE TABLE integration_report_catalog (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  integration_id    VARCHAR(50) NOT NULL,
  report_type       VARCHAR(50) NOT NULL,
  display_name      VARCHAR(100) NOT NULL,
  category          VARCHAR(50) DEFAULT 'General',
  description       TEXT,
  is_default        TINYINT(1) DEFAULT 0,
  sort_order        INT DEFAULT 0,
  UNIQUE KEY unique_report (integration_id, report_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### `export_templates`
```sql
CREATE TABLE export_templates (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  company_id      INT NOT NULL,
  spreadsheet_id  VARCHAR(255) NOT NULL,
  spreadsheet_url VARCHAR(512) NOT NULL,
  report_type     VARCHAR(50) NOT NULL,
  summarize_by    VARCHAR(20) NOT NULL DEFAULT 'Month',
  data_tab_name   VARCHAR(100) NOT NULL,
  detail_tab_name VARCHAR(100) DEFAULT NULL,
  last_synced_at  TIMESTAMP NULL,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_mapping (company_id, spreadsheet_id, report_type, summarize_by),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

---

## API Endpoints

### Autenticación (Google OAuth — login + Sheets export en un solo flujo)
| Método | Ruta                    | Descripción                                                |
|--------|-------------------------|------------------------------------------------------------|
| GET    | `/auth/google`          | Redirige a Google OAuth (scopes: email, profile, Sheets, Drive) |
| GET    | `/auth/google/callback` | Callback: verifica allowlist, crea sesión, almacena token  |
| GET    | `/auth/logout`          | Destruye sesión, redirige a login                          |

**Middleware `RequireAuth`**: todas las rutas excepto `/auth/*`, `/css/*`, `/js/*` requieren sesión.
**Usuarios permitidos**: solo los que tienen `is_allowed=1` en tabla `users`.
**Token Google**: almacenado en `$_SESSION['google_access_token']`, auto-refresh via `GoogleClientFactory::fromToken()`.

### Integraciones (genérico — funciona para QBO, Sunbase, Sunbrite)
| Método | Ruta                                      | Descripción                              |
|--------|--------------------------------------------|------------------------------------------|
| GET    | `/integrate/{integration}/connect/{slug}`  | Inicia OAuth/auth para una empresa       |
| GET    | `/integrate/{integration}/callback`        | Callback OAuth, almacena tokens          |
| POST   | `/integrate/{integration}/disconnect/{slug}`| Desconecta empresa de integración       |

### Páginas (server-rendered)
| Método | Ruta             | Descripción                                                  |
|--------|------------------|--------------------------------------------------------------|
| GET    | `/`              | Dashboard con KPIs reales de QBO                             |
| GET    | `/connect`       | Todas las integraciones (QBO, Sunbase, Sunbrite)             |
| GET    | `/sync`          | Sync selectivo agrupado por integración                      |
| GET    | `/reports`       | Hub: grid de reportes sincronizados                          |
| GET    | `/reports/{type}`| Visor de reporte + export dropdown + config modal            |
| GET    | `/reports/{type}/drilldown` | Drill-down por sección con breadcrumb             |
| GET    | `/pnl`           | Redirige a `/reports/ProfitAndLoss`                          |

### API (JSON) — todas usan `ApiResponse::success()` / `error()`
| Método | Ruta                           | Descripción                                                              |
|--------|--------------------------------|--------------------------------------------------------------------------|
| GET    | `/api/kpis`                    | KPIs del dashboard (P&L + Balance Sheet)                                 |
| GET    | `/api/reports/pnl`             | P&L cacheado (query: company, from, to)                                  |
| GET    | `/api/reports/balance-sheet`   | Balance Sheet cacheado                                                   |
| GET    | `/api/transactions`            | Drill-down legacy (query: company, account, from, to, limit, offset)     |
| POST   | `/api/sync/trigger`            | Sync selectivo `{companies[], reports[], startDate, endDate}`            |
| POST   | `/api/sync/refresh`            | Re-sync solo reportes en cache                                           |
| GET    | `/api/sync/status`             | Última sync por empresa                                                  |
| GET    | `/api/sync/available-reports`  | Reportes de todas las integraciones via registry                         |
| POST   | `/api/sync/refresh-catalog`    | Refrescar catálogo de reportes                                           |
| GET    | `/api/integrations/status`     | Estado de conexión de las integraciones                                  |
| POST   | `/api/export/google-sheets`    | Crear nuevo Sheet con reporte + drill-down                               |
| GET    | `/api/export-templates`        | Listar mappings de export templates (?company=slug)                      |
| POST   | `/api/export-templates`        | Crear/actualizar mapping a sheet existente                               |
| DELETE | `/api/export-templates/{id}`   | Eliminar mapping                                                         |
| POST   | `/api/export-templates/{id}/sync` | Sync un reporte a su sheet mapeado                                    |
| POST   | `/api/export-templates/sync-sheet` | Sync todos los reportes mapeados a un sheet                           |

### Convenciones de respuesta (`src/Helpers/ApiResponse.php`)
```json
{
  "success": true,
  "data": { ... },
  "meta": { "total": 142, "limit": 50, "offset": 0 }
}
```

---

## QuickBooks Online API — Referencia

### OAuth 2.0
- **Authorization URL**: `https://appcenter.intuit.com/connect/oauth2`
- **Token URL**: `https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer`
- **Scope**: `com.intuit.quickbooks.accounting`
- **Access token TTL**: 1 hora
- **Refresh token TTL**: 100 días
- **Cada empresa** tiene un `realmId` único (se recibe en el callback)

### Endpoints de reportes
- `GET /v3/company/{realmId}/reports/ProfitAndLoss`
- `GET /v3/company/{realmId}/reports/BalanceSheet`
- `GET /v3/company/{realmId}/reports/CashFlow`
- `GET /v3/company/{realmId}/reports/GeneralLedger`
- `GET /v3/company/{realmId}/reports/TransactionList`

### Parámetros comunes
- `start_date`, `end_date` — rango de fecha (YYYY-MM-DD)
- `summarize_column_by` — Month, Quarter, Year
- `accounting_method` — Cash, Accrual

### Límites
- 500 requests/min por realmId
- Max 10 concurrent per realmId
- 400,000 celdas por reporte (dividir en chunks mensuales)

### Estructura de respuesta P&L
```
Header → CompanyInfo, ReportName, DateMacro
Rows → Row[] (hierarchical)
  ├── Header: { ColData: [{ value: "Income" }] }
  ├── Rows: Row[] (line items)
  │     └── ColData: [{ value: "Sales", id: "1" }, { value: "1542800.00" }]
  └── Summary: { ColData: [{ value: "Total Income" }, { value: "4287500.00" }] }
```

---

## Google Sheets Export — Implementación

### Autenticación
Google OAuth del usuario (no service account). El Sheet se crea en el Google Drive del usuario logueado.
Scopes: `spreadsheets` + `drive.file` (solicitados al momento del login).

### Servicio: `GoogleSheetsExport`
- Usa **Sheets API cell data** (no fórmulas HYPERLINK) para evitar problemas de locale (`;` vs `,`)
- Números con hyperlink embebido via `textFormat.link` en la celda
- Formato: header frozen + bold con fondo, totales con fondo gris, grand totals azul, números con separador de miles
- Formatting compartido: `formatDetailTab()` usado por ambos modos de export

### Dos modos de exportación

**1. Nuevo Sheet** (`ExportController::export` → `GoogleSheetsExport::exportReport`)
- Crea un Google Spreadsheet nuevo en el Drive del usuario
- Tab 1: reporte (tabla jerárquica con links internos al detalle)
- Tab 2: detalle (transacciones GL por cuenta con links a QBO)

**2. Actualizar Sheet existente** (`ExportTemplateController::sync` → `GoogleSheetsExport::updateExistingSheet`)
- Escribe en tabs específicos de un Sheet que el usuario ya tiene
- Cada reporte usa su par de tabs: `Data_{ReportType}` + `Detail_{ReportType}`
- Múltiples reportes pueden compartir el mismo spreadsheet
- Fórmulas del usuario en otros tabs se preservan y recalculan automáticamente
- Flujo: resolve/create tabs → clear → write → format (todo en 1-2 batchUpdate calls)

### Export Templates (tabla `export_templates`)
- El usuario configura mappings desde el report viewer UI (modal)
- Pega URL de Google Sheet → sistema extrae spreadsheet ID
- Nombres de tabs auto-generados (`Data_ProfitAndLoss`, etc.) pero editables
- CRUD via API: crear, listar, eliminar mappings
- Sync individual o masivo (todos los reportes de un sheet)

### Factory: `GoogleClientFactory`
- `create()` — cliente nuevo con todos los scopes configurados
- `fromToken($token)` — cliente con token existente, auto-refresh si expirado

### Trait: `FetchesGLDetail`
- Shared entre `ExportController` y `ExportTemplateController`
- Fetch GL detail para todas las cuentas de un reporte via QBO General Ledger API
- Extrae transacciones recursivamente de la jerarquía GL

---

## Diseño visual

- **Tema claro** por defecto (CSS custom properties en `tokens.css`)
- **Tema oscuro** via `html.dark` class (toggle en navbar, persistido en localStorage)
- **Colores** (constantes en ambos temas):
  - Primary: `#0ea5e9` (sky-500)
  - Success: `#10b981` (emerald-500)
  - Warning: `#f59e0b` (amber-500)
  - Error: `#ef4444` (red-500)
  - Indigo: `#6366f1` (accent)
- **Fuente**: Inter (Google Fonts)
- **Sidebar colapsable** con persistencia en localStorage