chartboard/TASKS.md

# 📋 Chart-Board — Phased Work & Task Breakdown

> **Project:** Chart-Board (CodeIgniter 4)
> **Stack:** PHP 8.1 · CI4 · MySQL 8 · Bootstrap 5 · ApexCharts.js · Alpine.js
> **Total Estimated Duration:** ~14 Weeks (Solo) / ~7 Weeks (2-Dev Team)

---

## 📌 Legend

| Symbol | Meaning |
|--------|---------|
| 🔴 | Blocker — must be done before next task |
| 🟡 | Important — high priority |
| 🟢 | Normal priority |
| ⚙️ | Backend task |
| 🎨 | Frontend task |
| 🧪 | Testing task |
| 📄 | Documentation task |

---

## ──────────────────────────────────────────
## PHASE 1 — Project Foundation & Setup
### Estimated Time: 3–4 Days
## ──────────────────────────────────────────

### 1.1 Environment & Scaffolding

- [x] 🔴 ⚙️ Install CodeIgniter 4 via Composer (`composer create-project codeigniter4/appstarter chart-board`)
- [x] 🔴 ⚙️ Configure `.env` file — `baseURL`, `database.*`, `CI_ENVIRONMENT`
- [X] 🔴 ⚙️ Set up MySQL database `chartboard` with `utf8mb4` charset
- [ ] 🔴 ⚙️ Run the full `chartboard.sql` schema to create all 15+ tables
- [x] 🟡 ⚙️ Configure `app/Config/Database.php` for MySQL connection
- [x] 🟡 ⚙️ Set up `app/Config/App.php` — timezone, base URL, session settings
- [x] 🟢 ⚙️ Configure `app/Config/Cache.php` — file-based cache for dev, Redis for prod
- [x] 🟢 ⚙️ Configure `app/Config/Email.php` — SMTP settings for alerts/verification
- [x] 🟢 ⚙️ Set writable directory permissions (`chmod -R 777 writable/`)
- [x] 🟢 📄 Create `.gitignore` — exclude `.env`, `writable/`, `vendor/`

### 1.2 Front-End Base Setup

- [x] 🔴 🎨 Integrate Bootstrap 5 via CDN or npm build pipeline
- [x] 🔴 🎨 Integrate ApexCharts.js via CDN
- [x] 🟡 🎨 Integrate Alpine.js for lightweight reactivity
- [x] 🟡 🎨 Create base layout file `app/Views/layouts/main.php` — sidebar + topbar + content slot
- [x] 🟡 🎨 Create `app/Views/layouts/auth.php` — centered card layout for login/register
- [x] 🟢 🎨 Set up global CSS variables file `public/assets/css/variables.css`
- [x] 🟢 🎨 Set up global JS file `public/assets/js/app.js` — sidebar toggle, toast, helpers
- [x] 🟢 🎨 Add favicon, logo mark assets to `public/assets/images/`

### 1.3 CI4 Structure Setup

- [x] 🟡 ⚙️ Create base `BaseController.php` — set shared data (user session, workspace)
- [x] 🟡 ⚙️ Create `app/Config/Routes.php` skeleton — group routes by module
- [x] 🟡 ⚙️ Create `AuthFilter.php` — redirect unauthenticated users to login
- [x] 🟡 ⚙️ Create `RoleFilter.php` — check workspace role before allowing access
- [x] 🟢 ⚙️ Create `ApiAuthFilter.php` — validate `Authorization: Bearer` token for API routes
- [x] 🟢 ⚙️ Set up CI4 Encryption service config (`app/Config/Encryption.php`)
- [x] 🟢 ⚙️ Generate encryption key with `php spark key:generate`

---

## ──────────────────────────────────────────
## PHASE 2 — Authentication & User Management
### Estimated Time: 4–5 Days
## ──────────────────────────────────────────

### 2.1 User Model & Migration

- [x] 🔴 ⚙️ Create `UserModel.php` — CRUD, soft delete, findByEmail, findByApiToken
- [x] 🟡 ⚙️ Create `Migration_CreateUsersTable.php` (already in SQL; create CI4 migration file)
- [x] 🟢 ⚙️ Create `InitialSeeder.php` — seed default super admin + default workspace

### 2.2 Registration

- [x] 🔴 ⚙️ `Auth/RegisterController.php` — show form, validate input, hash password, save user
- [x] 🔴 ⚙️ Generate `verify_token`, send verification email on registration
- [x] 🟡 🎨 `app/Views/auth/register.php` — registration form with name, email, password, confirm password
- [x] 🟡 ⚙️ `Auth/RegisterController::verify()` — handle email verification token link
- [x] 🟢 🎨 Show success flash message after registration
- [ ] 🟢 🧪 Test: register with valid data, duplicate email, weak password

### 2.3 Login & Logout

- [x] 🔴 ⚙️ `Auth/LoginController.php` — validate credentials, check `email_verified`, start session
- [x] 🔴 ⚙️ Store user data in CI4 session: `user_id`, `name`, `email`, `role`
- [x] 🔴 🎨 `app/Views/auth/login.php` — email + password form, remember me checkbox
- [x] 🟡 ⚙️ `Auth/LoginController::logout()` — destroy session, redirect to login
- [x] 🟡 ⚙️ Insert record into `user_sessions` on login, delete on logout
- [ ] 🟢 🧪 Test: wrong password, unverified email, active session redirect

### 2.4 Password Reset

- [x] 🟡 ⚙️ `Auth/PasswordController::forgot()` — generate `reset_token`, set `reset_token_expiry`, send email
- [x] 🟡 ⚙️ `Auth/PasswordController::reset()` — validate token, check expiry, update password
- [x] 🟡 🎨 `app/Views/auth/forgot.php` — email input form
- [x] 🟡 🎨 `app/Views/auth/reset.php` — new password + confirm form
- [ ] 🟢 🧪 Test: expired token, already-used token, mismatched passwords

### 2.5 User Profile

- [x] 🟡 ⚙️ `ProfileController.php` — show profile, update name/avatar, change password
- [x] 🟡 🎨 `app/Views/profile/index.php` — profile card with avatar upload
- [x] 🟡 ⚙️ Handle avatar image upload to `writable/uploads/avatars/`
- [x] 🟡 ⚙️ `ProfileController::generateApiToken()` — create/rotate personal API token, save hashed
- [x] 🟢 🎨 Show/copy API token UI with regenerate button
- [ ] 🟢 🧪 Test: avatar upload size limits, password mismatch, token regeneration

### 2.6 Super Admin — User Management

- [x] 🟡 ⚙️ `Admin/UserController.php` — list all users, activate/deactivate, change role
- [x] 🟡 🎨 `app/Views/admin/users/index.php` — paginated table with search and status filter
- [x] 🟢 🎨 `app/Views/admin/users/edit.php` — edit user role and active status
- [ ] 🟢 🧪 Test: deactivate user blocks login, role change reflects immediately

---

## ──────────────────────────────────────────
## PHASE 3 — Workspace Management
### Estimated Time: 3–4 Days
## ──────────────────────────────────────────

### 3.1 Workspace CRUD

- [x] 🔴 ⚙️ `WorkspaceController.php` — create, read, update, delete workspaces
- [x] 🔴 ⚙️ `WorkspaceModel.php` — with soft delete, slug generation, owner filter
- [x] 🔴 🎨 `app/Views/workspace/index.php` — workspace list/grid with create button
- [x] 🟡 🎨 `app/Views/workspace/create.php` — name, description, timezone, logo upload form
- [x] 🟡 🎨 `app/Views/workspace/settings.php` — edit workspace details
- [x] 🟡 ⚙️ Auto-generate unique slug from workspace name on creation
- [x] 🟡 ⚙️ On workspace creation, auto-insert creator as `workspace_members` with role `admin`
- [x] 🟢 ⚙️ Soft delete workspace — cascade to members, data sources, charts, dashboards
- [ ] 🟢 🧪 Test: duplicate slug, logo upload, owner-only delete restriction

### 3.2 Members & Invitations

- [x] 🔴 ⚙️ `WorkspaceMemberController.php` — list members, change role, remove member
- [x] 🔴 ⚙️ `WorkspaceInvitationController.php` — send invite email, accept invite, cancel invite
- [x] 🟡 🎨 `app/Views/workspace/members.php` — members table with role dropdown and remove button
- [x] 🟡 🎨 `app/Views/workspace/invite.php` — email + role form, pending invites list
- [x] 🟡 ⚙️ Generate secure `token` for invite, store in `workspace_invitations`, set expiry (48h)
- [x] 🟡 ⚙️ Public route `/invite/{token}` — if user exists log them in; else redirect to register
- [x] 🟢 ⚙️ Prevent inviting existing members, prevent duplicate pending invites
- [ ] 🟢 🧪 Test: expired token, already-accepted token, role change enforcement

### 3.3 Workspace Context Switching

- [x] 🟡 ⚙️ Store `active_workspace_id` in session, set on login/switch
- [x] 🟡 🎨 Workspace switcher dropdown in sidebar — list user's workspaces, highlight active
- [x] 🟢 ⚙️ Middleware: validate user is member of active workspace on every request
- [ ] 🟢 🧪 Test: user with 0 workspaces, switching while on a dashboard page

---

## ──────────────────────────────────────────
## PHASE 4 — Data Source Connections
### Estimated Time: 5–6 Days
## ──────────────────────────────────────────

### 4.1 Data Source Model & Encryption

- [x] 🔴 ⚙️ `DataSourceModel.php` — CRUD, filter by workspace, soft delete
- [x] 🔴 ⚙️ `Libraries/Encrypter.php` — wrap CI4 Encryption to encrypt/decrypt credentials
- [x] 🔴 ⚙️ Encrypt `password`, `api_auth_value` fields before saving; decrypt on retrieval
- [ ] 🟢 🧪 Test: encrypted values are not plain text in DB, decryption returns correct value

### 4.2 Connection UI

- [x] 🔴 🎨 `app/Views/datasource/index.php` — list all data sources with type icon, status badge
- [x] 🔴 🎨 `app/Views/datasource/create.php` — dynamic form (type selector shows/hides fields)
- [x] 🟡 🎨 Alpine.js: show MySQL/PostgreSQL fields when DB type selected; show API fields for REST API
- [x] 🟡 🎨 `app/Views/datasource/edit.php` — edit form with masked password field
- [x] 🟡 🎨 Connection status badge — Untested / Connected (green) / Failed (red)
- [x] 🟢 🎨 Delete confirmation modal

### 4.3 Connection Drivers

- [x] 🔴 ⚙️ `Libraries/Connectors/MySQLConnector.php` — connect via PDO, run test query `SELECT 1`
- [x] 🔴 ⚙️ `Libraries/Connectors/PostgreSQLConnector.php` — connect via PDO pgsql
- [x] 🟡 ⚙️ `Libraries/Connectors/MongoDBConnector.php` — connect via MongoDB PHP library URI
- [x] 🟡 ⚙️ `Libraries/Connectors/RestApiConnector.php` — cURL GET/POST with auth headers
- [x] 🟡 ⚙️ `Libraries/Connectors/CsvConnector.php` — parse uploaded CSV into in-memory array
- [x] 🟢 ⚙️ `Libraries/ConnectionFactory.php` — factory to return correct connector by type
- [ ] 🟢 🧪 Test: each connector with valid/invalid credentials

### 4.4 Test Connection Endpoint

- [x] 🔴 ⚙️ `POST /datasource/test` (AJAX) — instantiate connector, run test, return JSON `{success, message}`
- [x] 🔴 🎨 "Test Connection" button with spinner; show success/error inline below button
- [x] 🟡 ⚙️ Update `status` and `last_tested_at` in DB after test
- [ ] 🟢 🧪 Test: timeout handling (set cURL timeout 10s), wrong host, wrong credentials

### 4.5 Schema Browser (for Query Builder)

- [x] 🟡 ⚙️ `GET /datasource/{id}/schema` (AJAX) — return tables list and columns per table as JSON
- [ ] 🟡 🎨 Schema sidebar in query builder — collapsible tree: Tables → Columns with types
- [x] 🟢 ⚙️ Cache schema response for 5 minutes per data source
- [ ] 🟢 🧪 Test: DB with 100+ tables, special characters in column names

---

## ──────────────────────────────────────────
## PHASE 5 — Query Builder
### Estimated Time: 5–6 Days
**Completion note:** Core MVP items are implemented (unified create/edit form, three query modes, multi-filter / multi–order-by visual builder, variables for raw + visual, API headers in `api_params`, preview table + execution log, query cache). Open: CodeMirror editor, datasource schema tree in UI, visual aggregates + GROUP BY controls, chart/dashboard variable wiring (Phase 6), and listed tests.

---

### 5.1 Saved Query Model

- [x] 🔴 ⚙️ `SavedQueryModel.php` — CRUD, filter by workspace and data source, soft delete
- [ ] 🟢 🧪 Test: save and retrieve query with complex JSON config

### 5.2 Visual Query Builder (No-Code)

- [x] 🔴 🎨 Visual mode in `app/Views/query/_form.php` (create/edit) — table name input + comma-separated columns *(no separate `visual.php`; schema dropdown / per-column checkboxes not implemented)*
- [ ] 🔴 🎨 Schema-driven table dropdown + column checkboxes with optional aliases
- [x] 🟡 🎨 Filter builder — add/remove filter rows: field, operator (=, !=, >, <, LIKE, IS NULL, IS NOT NULL), value
- [ ] 🟡 🎨 Aggregate row — apply COUNT/SUM/AVG/MIN/MAX to numeric columns
- [ ] 🟡 🎨 GROUP BY selector — multi-select *(backend `QueryBuilder::toSQL()` supports `group_by[]`; no UI yet)*
- [x] 🟡 🎨 ORDER BY — multiple sort columns, each with ASC/DESC + add/remove rows
- [x] 🟡 🎨 LIMIT input — max rows (default 500); supports `{{variable}}` in visual fields
- [x] 🟡 ⚙️ `QueryBuilder::toSQL()` — convert visual config JSON to safe parameterized SQL
- [x] 🟢 ⚙️ Prevent destructive keywords: block `DROP`, `DELETE`, `UPDATE`, `INSERT`, `TRUNCATE` in generated SQL
- [ ] 🟢 🧪 Test: multi-filter query, aggregate with group by, null filter

### 5.3 Raw SQL Mode

- [ ] 🔴 🎨 CodeMirror 6 editor integration — SQL syntax highlighting, auto-complete (table/column names) *(currently plain textarea in `_form.php`)*
- [x] 🔴 🎨 Mode selector — Raw SQL / Visual builder / API query
- [x] 🟡 ⚙️ SQL safety check before execution — regex/parse to block DDL/DML mutations
- [x] 🟡 ⚙️ `QueryController::execute()` — run sanitized SQL on the selected data source, return JSON results
- [x] 🟢 ⚙️ Enforce query timeout — kill query after 30 seconds
- [ ] 🟢 🧪 Test: malicious SQL injection attempt, timeout simulation, empty result set


### 5.4 Query Variables

- [x] 🔴 ⚙️ `Libraries/QueryVariableParser.php` — scan query string for `{{ var_name }}` pattern using regex, return list of variable names
- [x] 🔴 ⚙️ `Libraries/QueryVariableResolver.php` — resolve system variables (`{{today}}`, `{{now}}`, etc.) and substitute user values via PDO bindings; `resolveTemplateString()` for visual builder identifiers/literals
- [x] 🔴 🎨 Variable panel — Raw SQL + Visual builder: detect / configure / test values; hidden for API mode on create/edit
- [x] 🔴 🎨 Variable config form per detected variable:
  - Label (display name shown to end user)
  - Type: `text` / `number` / `date` / `date_range` / `select` / `multi_select`
  - Default value
  - For `select`/`multi_select`: options list (comma-separated or from another query)
  - Required toggle
- [x] 🟡 ⚙️ Persist variable definitions in `query_variables` table (per `saved_query_id`) on save/update *(dashboard/chart `display_config` integration pending Phase 6)*
- [ ] 🟡 🎨 Dashboard view — render a variable input widget per variable above each chart that has variables defined:
  - `text` → `<input type="text">`
  - `number` → `<input type="number">`
  - `date` → date picker
  - `date_range` → dual date range picker
  - `select` → dropdown
  - `multi_select` → multi-select dropdown with checkboxes
- [ ] 🟡 🎨 On variable value change → re-fetch chart data AJAX with new values, re-render chart without page reload
- [ ] 🟡 ⚙️ Global dashboard filter — if multiple charts share a variable with the same name, a single widget controls all of them simultaneously
- [x] 🟡 ⚙️ Resolve built-in system variables server-side before query execution (no user input needed for these)
- [x] 🟢 🎨 Variable widget in Query Builder preview — show input fields for each detected variable so creator can test values before saving
- [x] 🟢 ⚙️ `multi_select` in raw SQL — `QueryVariableResolver::resolve()` expands array values to multiple `?` placeholders (use inside `IN ({{var}})` in SQL)
- [ ] 🟢 ⚙️ Public shared dashboards — variable widgets still visible and functional for anonymous viewers
- [ ] 🟢 🧪 Test: SQL injection attempt via variable value is blocked by PDO binding; date variable resolves correctly; missing required variable shows validation error; multi-select generates correct `IN` clause


### 5.5 API Query Builder

- [x] 🟡 🎨 API query form — endpoint URL, JSON path, grouped sections (headers + field map with add/remove rows)
- [x] 🟡 🎨 HTTP headers — repeatable rows; persisted in `saved_queries.api_params` as JSON `{ "headers": [...] }`
- [x] 🟡 🎨 JSON path input — e.g. `data.results` to extract nested array
- [x] 🟡 🎨 Field map — source JSON key → column alias rows
- [x] 🟡 ⚙️ `Libraries/ApiConnector::fetch()` — variable substitution on URL, headers, extract by JSON path
- [ ] 🟢 🧪 Test: nested JSON path, missing field graceful fallback, invalid URL

### 5.6 Query Preview & Result Table

- [x] 🔴 🎨 "Run Query" button — AJAX call, show spinner while loading
- [x] 🔴 🎨 Result preview table — first 100 rows, dynamic columns, sortable headers, pagination, CSV download, execution log tab
- [x] 🟡 🎨 Row count badge, execution time badge
- [x] 🟡 🎨 Save flow — name/description/meta on same form as builder; POST to store/update `saved_queries`
- [x] 🟢 🎨 Empty state — centered “No rows returned” message in preview *(illustration asset optional)*
- [ ] 🟢 🧪 Test: 0 rows, 1000+ rows truncated, columns with special characters

### 5.7 Query Cache

- [x] 🟡 ⚙️ After execution, store result JSON in `query_cache` with MD5 cache key and TTL
- [x] 🟡 ⚙️ On next execution, check `query_cache` first; serve from cache if not expired
- [x] 🟢 ⚙️ CI4 Cron task: `DeleteExpiredQueryCache` — run every 30 minutes to purge expired rows
- [ ] 🟢 🧪 Test: cache hit serves faster, cache miss goes to DB, expired cache re-fetches

---

## ──────────────────────────────────────────
## PHASE 6 — Chart Builder
### Estimated Time: 6–7 Days
**Completion note:** Core MVP is in place: `ChartModel`, `SavedQueryRunner`, `ChartRenderer`, `ChartController`, multi-step builder (saved query first — no separate data-source step), ApexCharts live preview (`preview-query` / `preview-render`), `POST /chart/{id}/data` with CSRF refresh in JSON, chart list with Edit / Duplicate / Delete. Open: `public_token` generation, saved-query modal vs dropdown (dropdown implemented), unsaved-changes guard, table pagination/sort, legend position + custom hex + X-axis date format in UI, client `setInterval` refresh + spinner, search/filter, listed tests.

---

### 6.1 Chart Model

- [x] 🔴 ⚙️ `ChartModel.php` — CRUD, filter by workspace, soft delete
- [ ] 🟡 ⚙️ Public token generator (`is_public` / `public_token`) — pending (sharing phase)
- [ ] 🟢 🧪 Test: create chart with JSON display_config, retrieve and parse correctly

### 6.2 Chart Builder UI — Step Flow

- [x] 🔴 🎨 Multi-step chart builder UI (Step 1: Saved query → Type → Fields → Style → Preview & save)
- [x] 🔴 🎨 Step 1 — Choose saved query + run preview *(data source implied by query; no separate source step)*
- [x] 🔴 🎨 Step 2 — Chart type selector grid (12 types with icons)
- [x] 🟡 🎨 Step 3 — Field mapping: X-axis, Y-axis, Group By, Value (+ combo second metric); columns from preview
- [x] 🟡 🎨 Step 4 — Display settings: title, subtitle, palette presets, legend toggle, data label toggle, Y-axis min/max, number format, grid / smooth / stacked / horizontal bar
- [x] 🟡 🎨 Step navigation — Back / Next / Save; validation before advancing
- [x] 🟡 🎨 Live preview — ApexCharts with real query data (`chart/preview-render`)
- [ ] 🟢 🎨 "Use Saved Query" button — modal to pick query *(dropdown on Step 1 covers pick-from-list)*
- [ ] 🟢 🎨 Unsaved changes warning on browser back/close

### 6.3 Chart Rendering Engine

- [x] 🔴 ⚙️ `Libraries/ChartRenderer.php` — chart config + rows → Apex-compatible options (`buildPayload`)
- [x] 🔴 ⚙️ Renderer: `bar`, `line`, `area`, `pie`, `donut`
- [x] 🟡 ⚙️ Renderer: `scatter`, `kpi_card`, `funnel`, `gauge`, `heatmap`, `combo`
- [ ] 🟡 ⚙️ `table` — paginated HTML table with sort *(basic truncated table only)*
- [x] 🟢 ⚙️ Number formatter — currency (₹/$), percentage, decimals, K/M/B (server + client)
- [ ] 🟢 🧪 Test: each chart type with realistic data, empty data, single-row data

### 6.4 Chart Display Settings

- [x] 🟡 🎨 Color palette presets (Ocean, Forest, Sunset, Mono) *(custom hex input — open)*
- [ ] 🟡 🎨 Legend position selector (top/bottom/left/right/none) in builder UI
- [x] 🟡 🎨 Refresh interval dropdown (Manual / 1 min / 5 min / 15 min / 1 hr) *(1 day option — open)*
- [ ] 🟢 🎨 Date format selector for time-series X-axis (dd/MM, MMM dd, MMM yyyy)
- [x] 🟢 🎨 Stacked bar/area toggle

### 6.5 Chart Auto-Refresh

- [ ] 🟡 🎨 JavaScript: `refresh_interval > 0` → `setInterval` re-fetch
- [x] 🟡 ⚙️ `POST /chart/{id}/data` — re-run query, JSON render payload *(POST + `variables_json` for CSRF/vars; spec listed GET)*
- [ ] 🟢 🎨 Refresh spinner overlay while loading
- [ ] 🟢 🧪 Test: 1-minute refresh updates data without full page reload

### 6.6 Chart List & Management

- [x] 🟡 🎨 `app/Views/chart/index.php` — card grid, type, last updated, data source + query name
- [x] 🟡 🎨 Chart card actions — Edit, Duplicate, Delete *(Add to Dashboard / Share — Phase 7+)*
- [x] 🟡 ⚙️ `ChartController::duplicate()` — clone with name suffix `(copy)`
- [ ] 🟢 🎨 Search and filter charts by type or data source
- [ ] 🟢 🧪 Test: duplicate preserves all config, delete removes from all dashboards

---

## ──────────────────────────────────────────
## PHASE 7 — Dashboard Builder
### Estimated Time: 6–7 Days
## ──────────────────────────────────────────

### 7.1 Dashboard Model

- [ ] 🔴 ⚙️ `DashboardModel.php` — CRUD, filter by workspace, soft delete, public token generator
- [ ] 🔴 ⚙️ `DashboardWidgetModel.php` — CRUD widgets per dashboard, store grid position
- [ ] 🟢 🧪 Test: save layout_config JSON and retrieve widget positions correctly

### 7.2 Dashboard List

- [ ] 🔴 🎨 `app/Views/dashboard/index.php` — card grid of dashboards with pinned section at top
- [ ] 🟡 🎨 Dashboard card — name, description, chart count, last updated, share status badge
- [ ] 🟡 🎨 Create Dashboard button — modal with name + description input
- [ ] 🟢 🎨 Pin/Unpin dashboard toggle
- [ ] 🟢 🧪 Test: 0 dashboards empty state, pinned order preserved on reload

### 7.3 Dashboard View Mode

- [ ] 🔴 🎨 `app/Views/dashboard/view.php` — render all widgets in their grid positions
- [ ] 🔴 🎨 Render chart widgets: fetch data via AJAX, render ApexCharts
- [ ] 🟡 🎨 Render text widgets — parse Markdown to HTML using `marked.js`
- [ ] 🟡 🎨 Render image widgets — `<img>` with configurable object-fit
- [ ] 🟡 🎨 Global filter widgets — date range picker and dropdown filter
- [ ] 🟡 ⚙️ When global date filter changes, re-fetch all chart data with new date params injected into queries
- [ ] 🟢 🎨 Fullscreen button — expand dashboard to fill viewport, hide sidebar/topbar
- [ ] 🟢 🧪 Test: mixed widget types, dashboard with 20+ charts, date filter propagation

### 7.4 Dashboard Edit Mode (Drag & Drop)

- [ ] 🔴 🎨 Integrate `gridstack.js` or `Muuri` for drag-and-drop grid layout
- [ ] 🔴 🎨 "Edit Layout" toggle activates draggable/resizable mode on all widgets
- [ ] 🟡 🎨 Resize handles on widget cards — drag corner to resize (min 1×1, max 4×3 units)
- [ ] 🟡 🎨 "Add Widget" button in edit mode — opens modal to pick chart, text, image, or filter widget
- [ ] 🟡 🎨 Remove widget button (✕) visible only in edit mode
- [ ] 🟡 ⚙️ "Save Layout" — AJAX POST grid positions (x, y, w, h) per widget to `dashboard_widgets`
- [ ] 🟡 🎨 "Discard Changes" — reload original layout from DB without saving
- [ ] 🟢 🎨 Widget title override input (optional per-widget title different from chart name)
- [ ] 🟢 🧪 Test: save layout, reload — positions preserved exactly; concurrent edit race condition

### 7.5 Dashboard Settings

- [ ] 🟡 🎨 Dashboard settings panel — name, description, theme (light/dark/system), refresh interval
- [ ] 🟡 ⚙️ `DashboardController::updateSettings()` — update name, theme, refresh
- [ ] 🟢 🎨 Danger zone — delete dashboard with confirmation typing
- [ ] 🟢 🧪 Test: theme toggle persists on reload, refresh interval auto-starts

---

## ──────────────────────────────────────────
## PHASE 8 — Alerts & Notifications
### Estimated Time: 4–5 Days
## ──────────────────────────────────────────

### 8.1 Alert Model & CRUD

- [ ] 🔴 ⚙️ `AlertModel.php` — CRUD, filter by workspace, soft delete
- [ ] 🔴 ⚙️ `AlertHistoryModel.php` — insert triggered log, fetch recent history per alert
- [ ] 🟡 🎨 `app/Views/alert/index.php` — list alerts with status (OK/Triggered/Muted), last triggered time
- [ ] 🟡 🎨 `app/Views/alert/create.php` — form: chart selector, metric field, condition, threshold, channels
- [ ] 🟡 🎨 `app/Views/alert/edit.php` — edit + mute/unmute toggle
- [ ] 🟢 🧪 Test: create alert with all fields, edit threshold, delete alert

### 8.2 Alert Engine (Background Check)

- [ ] 🔴 ⚙️ `Libraries/AlertEngine.php` — load active alerts, run chart query, compare value to threshold
- [ ] 🔴 ⚙️ CI4 Cron (`php spark alert:check`) — runs every minute via system cron job
- [ ] 🟡 ⚙️ `AlertEngine::evaluate()` — conditions: `gt`, `lt`, `eq`, `gte`, `lte`
- [ ] 🟡 ⚙️ Skip alert if `is_muted_until` is in the future
- [ ] 🟡 ⚙️ On trigger: send Email via CI4 Email library, send Slack message via Webhook HTTP POST
- [ ] 🟡 ⚙️ Log result to `alert_history` — value, channels notified, status (sent/failed/muted)
- [ ] 🟢 ⚙️ Prevent duplicate notifications — if same alert triggered within last 5 minutes, skip
- [ ] 🟢 🧪 Test: threshold breach triggers notification, muted alert is skipped, failed webhook logs error

### 8.3 Alert Notifications UI

- [ ] 🟡 🎨 Alerts sidebar panel (as in POC) — show recent triggered alerts with current value
- [ ] 🟡 🎨 Alert history sub-page — paginated log of all past triggers per alert
- [ ] 🟡 🎨 "Mute for" button — snooze alert for 1h / 4h / 24h
- [ ] 🟢 🎨 Red badge count on sidebar Alerts nav item for active triggered alerts
- [ ] 🟢 🧪 Test: snooze clears badge, alert re-triggers after snooze expires

---

## ──────────────────────────────────────────
## PHASE 9 — Sharing & Embedding
### Estimated Time: 3–4 Days
## ──────────────────────────────────────────

### 9.1 Public Share Links

- [ ] 🔴 ⚙️ `SharedLinkModel.php` — create token, find by token, increment view count
- [ ] 🔴 ⚙️ `SharingController::generate()` — create record in `shared_links`, return public URL
- [ ] 🔴 ⚙️ Public route `GET /share/{token}` — no auth required, load dashboard/chart view-only
- [ ] 🟡 🎨 Share modal in dashboard/chart view — show public URL with copy button, QR code
- [ ] 🟡 🎨 Optional password field — bcrypt hash stored, prompt on public page if set
- [ ] 🟡 🎨 Optional expiry date picker — after expiry show "This link has expired" page
- [ ] 🟡 ⚙️ `SharingController::revoke()` — set `is_active = 0`, invalidate link
- [ ] 🟢 🎨 View count display in share modal ("Viewed 42 times")
- [ ] 🟢 🧪 Test: password protection, expired link, revoked link, view count increment

### 9.2 iFrame Embed

- [ ] 🟡 🎨 Embed tab in share modal — show iframe HTML snippet with correct URL
- [ ] 🟡 ⚙️ Public share route: set `X-Frame-Options: ALLOWALL` header for embed URLs
- [ ] 🟡 🎨 Embed view — stripped layout (no sidebar/topbar), chart/dashboard only
- [ ] 🟢 🎨 Embed size presets (640×480, 800×600, 1200×800, custom)
- [ ] 🟢 🧪 Test: embed renders in external `<iframe>`, responsive sizing

### 9.3 Export

- [ ] 🟡 🎨 Export chart as PNG — use ApexCharts `chart.dataURI()` + FileSaver.js client-side
- [ ] 🟡 🎨 Export chart data as CSV — AJAX to `GET /chart/{id}/export?format=csv`
- [ ] 🟡 ⚙️ `ChartController::export()` — re-run query, format as CSV using PHP `fputcsv`, stream download
- [ ] 🟡 ⚙️ Export chart data as Excel — use `PhpSpreadsheet` library to generate `.xlsx`
- [ ] 🟢 ⚙️ Log each export to `chart_exports` table
- [ ] 🟢 🧪 Test: CSV column headers match query columns, Excel opens without errors

---

## ──────────────────────────────────────────
## PHASE 10 — REST API
### Estimated Time: 3–4 Days
## ──────────────────────────────────────────

### 10.1 API Authentication

- [ ] 🔴 ⚙️ `ApiAuthFilter.php` — extract Bearer token from `Authorization` header, find user in `users.api_token`
- [ ] 🔴 ⚙️ Return `401 Unauthorized` JSON if token missing or invalid
- [ ] 🟢 ⚙️ Rate limiter — 60 requests per minute per token using CI4 Throttler
- [ ] 🟢 🧪 Test: valid token, invalid token, missing token, rate limit exceeded returns 429

### 10.2 API Endpoints

- [ ] 🔴 ⚙️ `Api/WorkspaceController` — `GET /api/v1/workspaces`, `POST /api/v1/workspaces`
- [ ] 🔴 ⚙️ `Api/DataSourceController` — `GET /api/v1/workspaces/{id}/datasources`, `POST`, `DELETE`
- [ ] 🔴 ⚙️ `Api/ChartController` — `GET /api/v1/workspaces/{id}/charts`, `POST`, `GET /api/v1/charts/{id}/data`
- [ ] 🟡 ⚙️ `Api/DashboardController` — `GET`, `POST`, `PUT /api/v1/dashboards/{id}`
- [ ] 🟡 ⚙️ `Api/QueryController` — `POST /api/v1/datasources/{id}/query` — run ad-hoc query
- [ ] 🟡 ⚙️ `Api/AlertController` — `GET /api/v1/alerts`, `POST`, `DELETE`
- [ ] 🟢 ⚙️ Consistent JSON response format: `{ success, data, message, errors }`
- [ ] 🟢 📄 Generate Postman collection JSON for all API endpoints
- [ ] 🟢 🧪 Test: each endpoint with valid/invalid workspace membership, missing fields

---

## ──────────────────────────────────────────
## PHASE 11 — Audit Logs
### Estimated Time: 2–3 Days
## ──────────────────────────────────────────

- [ ] 🔴 ⚙️ `Libraries/AuditLogger.php` — static `log(action, resource_type, resource_id, old, new)` method
- [ ] 🔴 ⚙️ Hook `AuditLogger::log()` into key controller actions: create/update/delete chart, dashboard, data source; login/logout; role change; share link created/revoked
- [ ] 🟡 🎨 `app/Views/audit/index.php` — paginated table of audit logs
- [ ] 🟡 🎨 Filters — by user, by action type, by date range
- [ ] 🟡 🎨 Log detail modal — show `old_value` and `new_value` JSON diff view
- [ ] 🟢 ⚙️ Auto-capture `ip_address` and `user_agent` from CI4 `IncomingRequest`
- [ ] 🟢 ⚙️ CI4 Cron: `PurgeOldAuditLogs` — delete logs older than 90 days (configurable)
- [ ] 🟢 🧪 Test: login creates log entry, chart delete logs old_value, IP captured correctly

---

## ──────────────────────────────────────────
## PHASE 12 — Settings & Configuration
### Estimated Time: 2 Days
## ──────────────────────────────────────────

- [ ] 🟡 ⚙️ `SettingsModel.php` — get/set by key and workspace_id (null = global)
- [ ] 🟡 🎨 `app/Views/settings/workspace.php` — name, logo, timezone, default refresh, default theme
- [ ] 🟡 🎨 `app/Views/settings/notifications.php` — SMTP test button, Slack webhook test button
- [ ] 🟡 🎨 `app/Views/admin/settings.php` (super admin) — allow_registration toggle, max workspaces
- [ ] 🟢 🎨 Theme toggle (light/dark) saved to `users` table or localStorage with CI4 session sync
- [ ] 🟢 🧪 Test: workspace settings persist after logout, global settings affect new registrations

---

## ──────────────────────────────────────────
## PHASE 13 — Polish, Testing & Security
### Estimated Time: 4–5 Days
## ──────────────────────────────────────────

### 13.1 UI/UX Polish

- [ ] 🟡 🎨 Loading states — skeleton shimmer on all chart cards while data loads
- [ ] 🟡 🎨 Empty states — friendly illustrations for no charts, no data sources, no dashboards
- [ ] 🟡 🎨 Toast notification system — success, error, warning toasts globally
- [ ] 🟡 🎨 Breadcrumb navigation on all pages
- [ ] 🟡 🎨 Responsive layout — sidebar collapses to hamburger on mobile
- [ ] 🟢 🎨 Keyboard shortcuts — `N` = new chart, `D` = go to dashboards, `ESC` = close modal
- [ ] 🟢 🎨 Dark mode — full dark theme toggle applied to all views
- [ ] 🟢 🎨 404 and 500 custom error pages

### 13.2 Security Hardening

- [ ] 🔴 ⚙️ Enable CI4 CSRF protection on all POST forms (`Config/Security.php`)
- [ ] 🔴 ⚙️ Enable CI4 XSS clean on all user inputs via `IncomingRequest::getVar()`
- [ ] 🔴 ⚙️ Validate all user-supplied SQL through allowlist check — block `DROP`, `ALTER`, `GRANT`, etc.
- [ ] 🟡 ⚙️ Set `HttpOnly`, `Secure`, `SameSite=Strict` on session cookie (`Config/Cookie.php`)
- [ ] 🟡 ⚙️ Add Content Security Policy headers via `ResponseTrait` or middleware
- [ ] 🟡 ⚙️ Add rate limiting on login route — block after 5 failed attempts for 15 minutes
- [ ] 🟢 ⚙️ Sanitize all file uploads — allow only `jpg`, `png`, `csv`; validate MIME type server-side
- [ ] 🟢 🧪 Test: CSRF token rejection, XSS payload in chart name, SQL injection in query field

### 13.3 Testing

- [ ] 🟡 🧪 PHPUnit: write feature tests for Auth module (register, login, logout, reset)
- [ ] 🟡 🧪 PHPUnit: write feature tests for Chart CRUD
- [ ] 🟡 🧪 PHPUnit: write feature tests for Dashboard CRUD and widget save
- [ ] 🟡 🧪 PHPUnit: write unit tests for `ChartRenderer`, `QueryBuilder::toSQL()`, `AlertEngine::evaluate()`
- [ ] 🟢 🧪 Browser test (manual): end-to-end user flow — register → create workspace → add data source → build chart → add to dashboard → share
- [ ] 🟢 🧪 Load test: dashboard with 10 charts, each auto-refreshing every 5 minutes — check DB load

---

## ──────────────────────────────────────────
## PHASE 14 — Deployment & Documentation
### Estimated Time: 2–3 Days
## ──────────────────────────────────────────

### 14.1 Deployment

- [ ] 🔴 ⚙️ Set `CI_ENVIRONMENT = production` in `.env`
- [ ] 🔴 ⚙️ Configure web server (Apache `.htaccess` or Nginx `server {}`) to point root to `public/`
- [ ] 🟡 ⚙️ Set up system cron jobs:
  - `* * * * * php /var/www/chart-board/spark alert:check`
  - `*/30 * * * * php /var/www/chart-board/spark cache:purge`
  - `0 2 * * * php /var/www/chart-board/spark logs:purge`
- [ ] 🟡 ⚙️ Configure Redis for production cache (update `Config/Cache.php`)
- [ ] 🟢 ⚙️ Set up log rotation for `writable/logs/`
- [ ] 🟢 ⚙️ Production MySQL — create read-only user for data source connections
- [ ] 🟢 ⚙️ Set up SSL certificate (Let's Encrypt)
- [ ] 🟢 📄 Create `DEPLOYMENT.md` — server setup steps, Nginx config, cron setup

### 14.2 Documentation

- [ ] 🟡 📄 Complete `README.md` — finalize installation steps, config reference
- [ ] 🟡 📄 `CONTRIBUTING.md` — code style (PSR-12), branch naming, PR checklist
- [ ] 🟢 📄 Postman Collection JSON for full REST API
- [ ] 🟢 📄 In-app help tooltips on query builder and chart builder fields
- [ ] 🟢 📄 Changelog `CHANGELOG.md` — v1.0.0 feature list

---

## 📊 Summary

| Phase | Name | Est. Days | Priority |
|-------|------|-----------|----------|
| 1 | Foundation & Setup | 3–4 | 🔴 |
| 2 | Auth & User Management | 4–5 | 🔴 |
| 3 | Workspace Management | 3–4 | 🔴 |
| 4 | Data Source Connections | 5–6 | 🔴 |
| 5 | Query Builder | 5–6 | 🔴 |
| 6 | Chart Builder | 6–7 | 🔴 |
| 7 | Dashboard Builder | 6–7 | 🔴 |
| 8 | Alerts & Notifications | 4–5 | 🟡 |
| 9 | Sharing & Embedding | 3–4 | 🟡 |
| 10 | REST API | 3–4 | 🟡 |
| 11 | Audit Logs | 2–3 | 🟢 |
| 12 | Settings | 2 | 🟢 |
| 13 | Polish, Testing & Security | 4–5 | 🟡 |
| 14 | Deployment & Docs | 2–3 | 🟡 |
| **Total** | | **~56–68 days** | |

> **Note:** Phases 1–7 are the critical path (core MVP). Phases 8–14 are enhancement and hardening layers. A team of 2 developers can parallelize frontend and backend tasks within each phase to cut delivery time roughly in half.

---

*Chart-Board · Task Breakdown v1.0 · Generated for CI4 Build*