# Documentation: `pivot_analysis_react.php`

The file `pivot_analysis_react.php` is an interactive web application designed for evaluating, filtering, and managing analysis and review records from a MySQL database (`preq.review`). It combines a PHP backend with a single-page React frontend running directly in the browser.

---

## 1. System Architecture & Technology Stack

```
+--------------------------------------------------------------+
|                     MySQL Database                           |
|                     (Table: review)                          |
+------------------------------+-------------------------------+
                               |
                               | PDO (Prepared Statements)
                               v
+--------------------------------------------------------------+
|                PHP Backend (pivot_analysis_react.php)        |
|  - Session management ($_SESSION['active_selection'])        |
|  - Data aggregation ($pivotData)                             |
|  - Endpoints:                                                |
|      * GET  /?reset=1 / ?select_source=...                   |
|      * POST / (Batch actions via FormData)                   |
|      * POST /?save_individual_actions=1 (JSON REST API)     |
+------------------------------+-------------------------------+
                               |
                               | window.phpData (JSON Bootstrapping)
                               v
+--------------------------------------------------------------+
|                Browser: React 18 + Babel Standalone          |
|  - In-browser JSX transpilation                              |
|  - Component state (useState, useEffect)                     |
|  - Asynchronous data synchronization (fetch API)             |
+--------------------------------------------------------------+
```

| Area | Component / Tool | Function & Details |
| :--- | :--- | :--- |
| **Backend** | PHP (>= 7.4 / 8.x) | Session management, data aggregation, routing, and endpoint logic |
| **Database** | MySQL / MariaDB via PDO | Connection to `preq.review`, parameterized queries for SQL injection protection |
| **Frontend Framework** | React 18 & ReactDOM | Component architecture (`useState`, `useEffect`), reactivity, modal dialogs |
| **Compiler** | Babel Standalone (CDN) | In-browser compilation of JSX without a build step (`npm/webpack`) |
| **Styling** | Modern CSS3 | Flexbox, CSS Grid, modals, responsive layout, badges & animations |

---

## 2. Core Features

### 1. Data Source and Tenant Selection
* **Tenant Filter (`tenant` / `Caseid`):** `getSelectionOptions()` retrieves all distinct combinations from the database.
* The selected combination is persisted in `$_SESSION['active_selection']`.
* Clicking **"Reset selection"** (`?reset=1`) clears the active selection and filters.

### 2. Dynamic Pivot Aggregation & Statistics
* **Aggregation:** Records are grouped and counted by the tuple `(search_term, pp_action, user_defined_action)` (`$pivotData`).
* **KPI / Stats Grid:**
  * Total number of records (`totalRecords`)
  * Unique search terms (`uniqueSearchTerms`)
  * Unique Privacy Pattern actions (`uniquePpActions`)
  * Unique user actions (`uniqueUserActions`)

### 3. Filter Options
* Filter the pivot table by:
  * **Search Term**
  * **PP Action** (Default system action)
  * **User Defined Action** (Currently assigned user action)

### 4. Actions & Editing

#### A. Batch Editing by Search Term (`ActionModal`)
* Clicking the **"Edit"** button opens a modal dialog.
* Allows assigning a `user_defined_action` to all rows matching the specified `search_term`:
  * `A` = Anonymize
  * `I` = Ignore Match
  * `D` = Delete File
  * `N` = No Action
  * `-` = Reset

#### B. Individual Row Editing in Detail View (`DetailModal`)
* Clicking a linked **Search Term** opens a tabular detail view of all individual records.
* Allows row-by-row assignment of custom actions for each specific record (`_id`).
* Persisted asynchronously via `fetch` request to `?save_individual_actions=1` (JSON payload).

---

## 3. Database Schema & SQL Statements

The application interacts with the MySQL table `preq.review`. Below is the overview of relevant columns and the detailed SQL statements executed in the application.

### Table Schema (`review`) – Relevant Columns

| Column | Type / Meaning | Description |
| :--- | :--- | :--- |
| `_id` | Primary Key / ID | Unique row identifier for the match record. |
| `tenant` | String / Text | Tenant or client identifier. |
| `Caseid` | String / Text | Case or process identifier. |
| `search_term` | String / Text | Matched search term / pattern. |
| `pp_action` | String / Code | Privacy Pattern default action (system proposal). |
| `user_defined_action` | String / Code (`A`, `I`, `D`, `N`, `-`) | User-assigned review action. |

---

### SQL Statements in Detail

#### 1. Retrieve Tenant & Case Options (`getSelectionOptions`)
Retrieves all existing, non-empty pairs of `tenant` and `Caseid` to populate the selection dropdown.

```sql
SELECT DISTINCT `tenant`, `Caseid`
FROM `review`
WHERE `tenant` IS NOT NULL
  AND TRIM(`tenant`) <> ''
  AND `Caseid` IS NOT NULL
  AND TRIM(`Caseid`) <> ''
ORDER BY `tenant`, `Caseid`;
```
* **Execution:** `PDO::query()`
* **Purpose:** Supplies structured options in `tenant|Caseid` format for session management.

---

#### 2. Load Review Records (`loadReviewData`)
Loads records from the table, optionally filtering by the currently active tenant/case selection.

* **With Tenant Filter (Standard flow after selection):**
  ```sql
  SELECT *
  FROM `review`
  WHERE `tenant` = :tenant AND `Caseid` = :caseid;
  ```
  * **Prepared Statement Parameters:**
    * `:tenant` (string) = Selected tenant
    * `:caseid` (string) = Selected Case ID

* **Without Filter (Fallback when no selection is active):**
  ```sql
  SELECT *
  FROM `review`;
  ```
* **Return Value:** Array of all records (`$data`) and column names (`$headers`) for pivot calculation and detail views.

---

#### 3. Mass & Individual Record Updates (`saveRowActions`)
To update multiple records efficiently in a single database roundtrip, a dynamic `CASE ... WHEN ... THEN` construct is constructed using fully parameterized Prepared Statements.

```sql
UPDATE `review`
SET `user_defined_action` = CASE `_id`
    WHEN :id_001 THEN :action_001
    WHEN :id_002 THEN :action_002
    /* ... additional rows ... */
    ELSE `user_defined_action`
END
WHERE `_id` IN (:id_001, :id_002, /* ... */);
```

* **Prepared Statement Parameters (dynamically generated):**
  * `:id_<hash/id>` = Row identifier (`_id`)
  * `:action_<hash/id>` = Assigned action code (`A`, `I`, `D`, `N`, `-`)
* **Advantages:**
  * **High Performance:** Thousands of records are updated in a single SQL query (avoids looping individual `UPDATE` statements).
  * **SQL Injection Protection:** All values (`_id` and `action`) are strictly bound via named parameters.
  * **Atomicity:** All updates occur within a single atomic query.

---

## 4. Backend Functions & Endpoints

### PHP Functions
* `getDbConnection(): PDO`: Provides a singleton PDO connection instance with UTF-8 encoding and error handling (`PDO::ERRMODE_EXCEPTION`, `PDO::FETCH_ASSOC`).
* `getSelectionOptions(PDO $pdo, string $tableName): array`: Reads distinct `tenant`/`Caseid` pairs from the database.
* `loadReviewData(PDO $pdo, string $tableName, ?string $selection): array`: Fetches records for the active selection.
* `saveRowActions(PDO $pdo, string $tableName, array $rowActions): int`: Builds and executes the dynamic batch update SQL statement via Prepared Statements.

### Request Handling & Endpoints

1. **`GET /?reset=1`**
   * Clears `$_SESSION['active_selection']`.
   * Issues an HTTP 302 redirect back to the base URL.

2. **`GET /?select_source={tenant}|{Caseid}`**
   * Persists the selected tenant/case identifier in `$_SESSION['active_selection']`.
   * Issues an HTTP 302 redirect back to the application.

3. **`POST /?save_individual_actions=1` (Asynchronous REST API)**
   * **Header:** `Content-Type: application/json`
   * **Payload:**
     ```json
     {
       "row_actions": {
         "101": "A",
         "102": "I",
         "103": "D"
       }
     }
     ```
   * **Response:**
     ```json
     {
       "success": true,
       "updatedCount": 3,
       "message": "Changes saved successfully."
     }
     ```

4. **`POST /` (Classic Form Submit for Batch Updates by Search Term)**
   * **Payload (FormData / URL-encoded):**
     * `search_term`: Target search term.
     * `action`: `anonymize` (`A`), `ignore` (`I`), `delete` (`D`), `none` (`N`), `reset` (`-`).
   * Iterates through all matching records server-side and executes `saveRowActions()`.

---

## 5. Frontend Component Hierarchy

* `App`: Root component; orchestrates global flash messaging and data flow.
* `Header`: Displays title, description, and technology badges.
* `DataSourceSelector`: Dropdown for selecting `tenant`/`Caseid` and resetting selection.
* `StatsGrid` & `StatCard`: KPI cards displaying aggregation metrics.
* `FilterSection` & `FilterSelect`: Filter controls mapped to URL parameters.
* `PivotTable`: Main table displaying aggregated pivot data, summary totals, and modal triggers.
* `ActionModal`: Dialog for assigning actions across all rows of a search term.
* `DetailModal`: Detailed modal view for granular, row-by-row inspection and editing.
