# Project Handoff

This document summarizes the current state of the spreadsheet importer so it can be copied into a new project or used as a reference for the next stage.

## What This App Does

This is a plain PHP and Bootstrap web app that:

- accepts an Excel upload
- validates the workbook headers
- ignores the `Revenue Type` column
- normalizes and stores imported row data in PostgreSQL
- calculates stage 1 derived fields during import
- wipes previous imported data before each new import
- shows a 20-row preview on the home page
- provides a full DataTables page for the latest imported batch

The app is structured so future import stages can add more rules without rewriting the UI or database layer.

## Move Notes

The codebase is largely relocatable because internal includes and asset URLs are built from the project root.

Before moving the project into a new folder:

- set `APP_URL` to the new base URL or folder path
- keep `DATABASE_URL` or the values in `config.php` aligned with the target environment
- rerun `composer install` if `vendor/` is not copied with the project
- confirm the web server is still pointing at `public/` as the document root

The only folder-sensitive setting in the app itself is the redirect base URL used after imports and clears.

## Current Stack

- PHP
- Bootstrap 5
- PostgreSQL
- Composer for the spreadsheet library
- OpenSpout for reading Excel files

## Important Files

- `config.php`
  - Root config file for app name, URL, and PostgreSQL connection settings.
  - This replaced `.env`-style configuration.

- `bootstrap.php`
  - Starts the session.
  - Loads Composer's autoloader if present.
  - Requires the app files manually.
  - Exposes the `app_config()` helper for config lookup.

- `index.php`
  - Root entrypoint.
  - Loads `public/index.php` so the app can be reached from the project root.

- `public/index.php`
  - Main web entrypoint.
  - Creates the controller and renders a friendly setup error page if startup fails.

- `public/assets/app.js`
  - Handles file upload progress.
  - Drives the chunked import loop with repeated `process-chunk` requests.
  - Handles the clear-data button.
  - Reloads the page after import or clear completes.

- `public/assets/styles.css`
  - Custom Bootstrap styling and layout.

- `templates/index.php`
  - Home page with upload form, status messages, and a 20-row preview table.

- `templates/table.php`
  - Full-table page for the latest imported batch.
  - DataTables server-side search and paging.
  - ColumnControl header buttons and header filters.

- `templates/partials/navbar.php`
  - Shared navigation between the preview and full-table pages.

- `bin/migrate.php`
  - Creates the PostgreSQL schema.

- `app/Database.php`
  - Builds the PDO connection from `config.php`.

- `app/Http/AppController.php`
  - Coordinates upload, clear, render, and JSON responses.

- `app/Import/SpreadsheetReader.php`
  - Streams workbook data with OpenSpout.
  - Builds a temporary chunk cache from the uploaded file.
  - Detects the real header row.
  - Ignores `Revenue Type`.
  - Normalizes cell values.

- `app/Import/SpreadsheetRowProcessor.php`
  - Applies all stage 1 calculated field rules.

- `app/Import/ImportService.php`
  - Orchestrates validation, clearing, processing, and storing.

- `app/Import/ImportRepository.php`
  - Handles PostgreSQL inserts, truncation, counting, and pagination.

## Database Design

The schema is intentionally flexible and uses JSONB for future growth.

### `import_batches`

Stores metadata for each import run:

- source filename
- original headers
- calculated headers
- row count
- warning count
- created timestamp

### `import_rows`

Stores each imported row:

- batch id
- source row number
- original row data as JSONB
- normalized row data as JSONB
- calculated row data as JSONB
- merged row data as JSONB

The row-level JSONB storage makes it easier to add more calculated fields and import stages later without changing the schema every time.

## Import Flow

The current flow is:

1. User uploads an Excel workbook.
2. The app validates the file extension.
3. The upload is stored in a temp file and an import token is returned.
4. The workbook is streamed once into a temporary chunk cache.
5. The browser repeatedly calls `action=process-chunk` with that token.
6. Required columns are checked by header name.
7. Existing imported data is wiped.
8. A new batch record is created.
9. Each cached row is normalized.
10. Derived fields are calculated.
11. The row is inserted into PostgreSQL.
12. The home page reloads and shows a 20-row preview table.
13. The full table page provides server-side search and pagination for the latest batch.

## Validation Rules

Required columns:

- `Accrual Category`
- `Local Selling Company (S/T)`
- `Contracting Party`
- `Rights Holder Liable Flag`
- `Global Transfer Pricing Flag`
- `Domestic/ Foreign Flag`
- `Ownership Type`

The app also:

- handles blank and null values safely
- preserves formatted text values from Excel
- treats numeric-looking values consistently
- shows readable import errors

## Stage 1 Calculated Fields

### 1. `Actual/Accrual`

Rule:

- If `Accrual Category` equals `##`, return `Actual`
- Otherwise return `Accrual`

### 2. `US/ex-US sale`

Rule:

- If `Local Selling Company (S/T)` is between `1700` and `1809` inclusive, return `US`
- Otherwise return `ex-US`

### 3. `Contracting Party Filtered`

Rule:

- If `Contracting Party` equals `1756`, return `1756`
- If `Contracting Party` equals `1100`, return `1100`
- If `Contracting Party` equals `1902`, return `1902`
- Otherwise return `LOCAL`

### 4. `String`

Rule:

Concatenate, in this exact order with no separator:

- `Contracting Party Filtered`
- `Rights Holder Liable Flag`
- `Global Transfer Pricing Flag`
- `Domestic/ Foreign Flag`
- `Ownership Type`

Example:

- `1756YYFDI`

### 5. `2110 Applicable X201`

Rule:

- If the `String` value is in the approved list, return `X`
- Otherwise return blank

### 6. `Class`

Rules are applied top to bottom and the first match wins:

- `A` if `Contracting Party = 1902`
- `B` if `Contracting Party = 1756`, `Rights Holder Liable Flag = Y`, and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive
- `C` if `Contracting Party = 1756`, `Local Selling Company (S/T)` is between `1700` and `1808` inclusive, and `2110 Applicable X201 = X`
- `D` if `Contracting Party = 1756`, `Local Selling Company (S/T)` is between `1700` and `1808` inclusive, and `2110 Applicable X201` is not `X`
- `E` if `Contracting Party = 1100` and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive
- `F` if `Contracting Party = 1100` and `Local Selling Company (S/T)` is between `1700` and `1808` inclusive
- `G` if `Contracting Party Filtered = LOCAL`
- `H` if `Contracting Party = 1756`, `Rights Holder Liable Flag = N`, `Global Transfer Pricing Flag = Y`, and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive

## UI

The Bootstrap UI includes:

- upload form
- navbar for preview/full-table navigation
- progress bar
- success/error messages
- imported row count
- clear imported data button
- 20-row data preview table
- current batch summary
- full table page with search and pagination

The app uses relative asset paths so it works from either:

- the project root, or
- the `public/` directory

## How To Set This Up In A New Project

1. Copy these files/folders into the new project:
   - `config.php`
   - `bootstrap.php`
   - `index.php`
   - `bin/`
   - `app/`
   - `public/`
   - `templates/`
   - `composer.json`
   - `composer.lock`

2. Install dependencies:
   ```bash
   composer install
   ```

3. Update `config.php` with the new server's database settings.

4. Run migrations:
   ```bash
   composer run migrate
   ```

5. Configure the web server to point at the project root or `public/`.

6. Open the app in a browser and upload the workbook.

## Notes For Future Stages

- Keep the processing logic out of the template.
- Add new calculated fields in `SpreadsheetRowProcessor`.
- Add new schema fields only if JSONB is no longer sufficient.
- If later stages need multiple import histories, stop truncating and switch to batch filtering in the UI.
- If the workbook structure changes, update header detection in `SpreadsheetReader`.

## Current Caveats

- PostgreSQL credentials must be correct in `config.php`.
- The app currently expects the database to exist before migration.
- The import path uses PhpSpreadsheet, so Composer remains required.
- Large workbooks should continue to use pagination in the preview table.

## Working Assumptions

- The header names remain stable.
- The example workbook is representative of the real import structure.
- `Revenue Type` should always be ignored.
- Future stages will extend the current batch/import model rather than replace it.