# Installer & Upgrade Overhaul Plan

## Status

**Date:** 2025-07-10
**Status:** Approved — ready for implementation
**App version:** 2.0.2
**Required DB version:** 1.4.0

---

## 1. Goals

1. **Single unified installer** — one PHP code path for Docker and local installs
2. **Proper migration system** — versioned, idempotent, sequential migrations
3. **Clean separation of concerns** — config creation, fresh install, upgrade as distinct steps
4. **Wizard-style UX** — step-by-step guided flow with clear status feedback
5. **Backward compatible** — supports upgrades from any previous version (1.0 through 1.4.0)
6. **Future-proof for automated updates** — migrations are idempotent, scriptable, and trackable
7. **`database.sql` always in sync** — generated from the canonical schema definition, never hand-maintained

---

## 2. Principles

- **One code path** — the PHP installer handles all DB setup in both Docker and local environments
- **Docker entrypoint** (`start.sh`) handles only infrastructure (permissions, mail config, Apache startup)
- **Backward compatible** — all upgrade paths from v1.0+ are preserved; `database.sql` still works for manual installs
- **Idempotent** — migrations can be safely re-run without data loss
- **Lightweight** — no external dependencies; written in pure PHP with PDO
- **App version and DB schema version remain decoupled** — they evolve independently

---

## 3. Architecture

### 3.1 Directory Structure

```
application/
├── installer/                          # NEW — replaces controllers/install/
│   ├── InstallerController.php         # Entry point, routes wizard steps
│   ├── ConfigManager.php               # Read/write config.php, env var resolution
│   ├── DatabaseManager.php             # Connection, schema detection, table ops
│   ├── MigrationRunner.php             # Applies pending migrations in order
│   ├── SchemaBuilder.php               # Canonical schema definition (creates full SQL dumps)
│   ├── RequirementChecker.php          # PHP version, PDO, writable dirs, etc.
│   ├── CliCommand.php                  # CLI entry point (dump-sql, migrate, status)
│   ├── views/                          # Step templates
│   │   ├── welcome.php
│   │   ├── config-form.php
│   │   ├── db-install.php
│   │   ├── upgrade.php
│   │   └── complete.php
│   └── migrations/                     # NEW — one class per version step
│       ├── MigrationInterface.php
│       ├── Version001000.php           # v1.0
│       ├── Version0011rc2.php          # v1.1rc2
│       ├── Version001100.php           # v1.1
│       ├── Version0012p1.php           # v1.2p1
│       ├── Version0012p3.php           # v1.2p3
│       ├── Version001240.php           # v1.2.4
│       ├── Version001252.php           # v1.2.5.2
│       ├── Version001256.php           # v1.2.5.6
│       ├── Version001257.php           # v1.2.5.7
│       ├── Version001261.php           # v1.2.6.1
│       ├── Version001262.php           # v1.2.6.2
│       ├── Version001263.php           # v1.2.6.3
│       ├── Version001280.php           # v1.2.8
│       ├── Version001290.php           # v1.2.9
│       ├── Version001300.php           # v1.3.0
│       └── Version001400.php           # v1.4.0 (current)
│
├── controllers/
│   └── install/                        # EXISTING — preserved for backward compat redirects
│       ├── index.php                   #    will redirect to /installer
│       ├── setup-config.php            #    will redirect to /installer/setup-config
│       ├── odm.php                     # REMOVED — replaced by SchemaBuilder
│       ├── upgrade_10.php              # REMOVED — migrated to installer/migrations/
│       └── ...                         # REMOVED — all 16 upgrade files migrated
│
└── configs/
    ├── config-sample.php               # KEPT — unchanged
    ├── config.php                      # KEPT — generated by ConfigManager
    └── docker-configs/                 # KEPT — Docker config mount point
```

### 3.2 Migration System (Custom Lightweight)

Each version step becomes a class implementing `MigrationInterface`:

```php
interface MigrationInterface
{
    public function getVersion(): string;         // e.g. '1.4.0'
    public function getDescription(): string;      // Human-readable label
    public function up(PDO $pdo, string $prefix): void;
    public function down(PDO $pdo, string $prefix): void;
}
```

#### Tracking Table

A new `{prefix}migrations` table records applied migrations:

```sql
CREATE TABLE `{prefix}migrations` (
    `version`     VARCHAR(16) NOT NULL,
    `name`        VARCHAR(255) NOT NULL,
    `executed_at` DATETIME NOT NULL,
    `batch`       INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`version`)
) ENGINE = MYISAM;
```

#### Runner Behavior

`MigrationRunner` does the following:
1. Reads applied migrations from `{prefix}migrations` table
2. Compares against registered migration classes sorted by version
3. Applies any un-applied migrations in order within a single "batch"
4. Records each migration with the same batch number
5. Updates `{prefix}odmsys` → `sys_value` to the latest applied version (backward compat)
6. Outputs status per migration (echo for web, stdout for CLI)

### 3.3 ConfigManager

Replaces the `str_replace()` file-editing approach in `setup-config.php`:

```php
class ConfigManager
{
    public function configExists(): bool;
    public function discoverConfigPath(): string;   // Tries all 4 current paths
    public function getEnvVar(string $name, ?string $default = null): ?string;
    public function writeConfig(array $params): void;  // Templated write from config-sample.php
    public function loadConfig(): void;
}
```

Key behaviors:
- Tries config paths in the same priority as `public/index.php`
- The `getEnvVar()` method consults `$_ENV` → `getenv()` → `$_SERVER` with no duplication
- `writeConfig()` does a template-based replacement on `config-sample.php` (same approach but encapsulated)

### 3.4 SchemaBuilder

The canonical schema definition — a single PHP class that can generate the complete SQL dump:

```php
class SchemaBuilder
{
    public function getVersion(): string;                                // Current schema version (1.4.0)
    public function getCreateTableStatements(string $prefix): array;      // All CREATE TABLE
    public function getDefaultDataStatements(string $prefix, array $options): array;  // Default INSERTs
    public function buildFullDump(string $prefix, array $options): string;   // Complete SQL text
}
```

The `buildFullDump()` output is what goes into `database.sql`. This keeps the standalone SQL dump in sync with the code automatically.

Available options for `buildFullDump()`:
- `'admin_password'` → the md5 hash for the admin user (default: `md5('admin')`)
- `'datadir'` → the data directory path in the settings table

### 3.5 Unified Flow

```
User visits /
     │
     ├── No config.php exists?
     │       └── Redirect → /installer/setup-config  [Config Wizard]
     │
     ├── Config exists but no {prefix}odmsys table?
     │       └── Redirect → /installer  [Fresh DB Install]
     │               ├── Step 1: Requirements check (PHP, PDO, writable dirs)
     │               ├── Step 2: Show DB config summary (pre-filled from env)
     │               └── Step 3: Create all tables + default data → login
     │
     ├── odmsys exists but version < required_db_version?
     │       └── Redirect → /installer  [Upgrade]
     │               ├── Step 1: Show current vs required version
     │               ├── Step 2: Backup reminder + confirmation
     │               └── Step 3: Run pending migrations → login
     │
     └── Version matches?
             └── Normal app flow → login
```

### 3.6 Docker Integration

**`start.sh`** is simplified to only handle infrastructure:

```bash
#!/bin/bash
mkdir -p /var/www/document_repository
chown -R www-data:www-data /var/www/document_repository /var/www/html
mkdir -p /var/www/html/application/templates_c
chmod 777 /var/www/html/application/templates_c

# Configure mail
if /configure-simple-mail.sh > /dev/null; then
    echo "Mail service configured"
fi

exec /usr/sbin/apache2ctl -D FOREGROUND
```

No more `mysql < database.sql` in the entrypoint. All DB setup is handled by the PHP installer, which pre-populates form fields from `$_ENV` when `IS_DOCKER=true`. The admin password is passed through `$_ENV['ADMIN_PASSWORD']` rather than `sed`-injected into `database.sql`.

---

## 4. Wizard Steps Detail

### 4.1 `/installer/setup-config` (Config Wizard)

| Step | URL | Description |
|------|-----|-------------|
| 0 | `/installer/setup-config?step=0` | Welcome + requirements overview |
| 1 | `/installer/setup-config?step=1` | Form: DB creds, prefix, admin password, data dir. Docker: pre-filled from env |
| 2 | `/installer/setup-config?step=2` | Write config.php, redirect to `/installer` |

### 4.2 `/installer` (Install/Upgrade Wizard)

**Fresh install mode** (no `odmsys` table):

| Step | URL | Description |
|------|-----|-------------|
| 1 | `/installer` | Auto-detect: "Fresh Install" mode detected |
| 2 | `/installer?op=requirements` | PHP version, PDO driver, writable templates_c, data dir |
| 3 | `/installer?op=install` | Create tables, insert defaults, set version → summary |

**Upgrade mode** (`odmsys` exists, version mismatch):

| Step | URL | Description |
|------|-----|-------------|
| 1 | `/installer` | Auto-detect: "Upgrade" mode, shows current vs required version |
| 2 | `/installer?op=backup-reminder` | Instructions to back up DB + files, confirm to proceed |
| 3 | `/installer?op=upgrade` | Run each pending migration with status, show completion |

---

## 5. Backward Compatibility

### 5.1 URL Preservation

- `/install/setup-config` redirects to `/installer/setup-config`
- `/install/index` redirects to `/installer`

These are small stub files in the old locations that do a `header('Location: ...')`.

### 5.2 DB Version Tracking

The `odmsys.sys_value` row is still updated by the migration runner — any external scripts or Docker health checks reading it continue to work.

### 5.3 Config File Format

`config.php` structure is unchanged — constants (`APP_DB_NAME`, `APP_DB_USER`, etc.) remain the same. Any code that reads them (the app itself, custom scripts) continues to work.

### 5.4 `database.sql`

Preserved at project root. Regenerated via CLI command. Still usable for manual installs:

```bash
mysql -u user -p dbname < database.sql
```

### 5.5 Upgrade Chain

All 16 existing upgrade steps are individually migrated to migration classes. No upgrade path from any historical version is lost.

---

## 6. CLI Commands

The `CliCommand.php` entry point provides scriptable operations:

```bash
# Generate database.sql from SchemaBuilder
php application/installer/cli.php dump-sql

# Generate database.sql with custom admin password
php application/installer/cli.php dump-sql --admin-password=md5(secret)

# Run pending migrations (for automated deployments)
php application/installer/cli.php migrate

# Show migration status
php application/installer/cli.php status
```

Makefile integration:

```makefile
dump-sql: ## Regenerate database.sql from the canonical schema definition
    @php application/installer/cli.php dump-sql > database.sql
    @echo "✅ database.sql regenerated from SchemaBuilder"
```

---

## 7. What Gets Removed

| File | Reason | Replacement |
|------|--------|-------------|
| `controllers/install/index.php` | Monolithic, tangled, 660 lines | `InstallerController.php` |
| `controllers/install/setup-config.php` | Duplicated env logic, 480 lines | `ConfigManager` + streamlined view |
| `controllers/install/odm.php` | Static schema, no versioning | `SchemaBuilder` |
| `controllers/install/upgrade_10.php` through `upgrade_136.php` | 16 files, chained manually | `installer/migrations/Version*.php` |

---

## 8. Implementation Order

| # | Task | Details |
|---|------|---------|
| 1 | `MigrationInterface` + `MigrationRunner` | Core engine with batch tracking, `migrations` table |
| 2 | `SchemaBuilder` | Canonical schema, `buildFullDump()` output matching current `database.sql` |
| 3 | Convert 16 upgrade files → migration classes | Each `Version*.php` with `up()` matching the original SQL |
| 4 | `ConfigManager` | Clean config file handling, env var resolution, path discovery |
| 5 | `DatabaseManager` | Schema detection, connection testing, `get_db_version()` |
| 6 | `RequirementChecker` | Pre-flight checks (PHP, PDO, writable dirs, extensions) |
| 7 | `InstallerController` | Wizard routing (fresh install, upgrade, auto-detection) |
| 8 | Wizard views | `welcome.php`, `config-form.php`, `db-install.php`, `upgrade.php`, `complete.php` |
| 9 | `CliCommand` | `dump-sql`, `migrate`, `status` CLI commands |
| 10 | Simplify `start.sh` | Remove `mysql < database.sql`, `sed` password injection |
| 11 | Simplify `public/index.php` | Cleaner detection logic using `ConfigManager` + `DatabaseManager` |
| 12 | Redirect stubs | `controllers/install/index.php` and `setup-config.php` → redirect to `/installer/` |
| 13 | Makefile update | Add `dump-sql` target, update `install` target |
| 14 | Integration tests | Test fresh install, upgrade from each version, Docker env var passthrough |
| 15 | Regenerate `database.sql` | `make dump-sql` to produce the first auto-generated `database.sql` |

---

## 9. Future-Proofing Notes

These decisions anticipate a future automated update feature:

- **Migrations are idempotent** — they can be run repeatedly from cron or CI without harm
- **CLI mode is a first-class citizen** — `cli.php migrate` is the same code as the web wizard
- **Batch tracking** — the `batch` column in `migrations` table groups migrations applied together, making it easy to understand what changed in a given update
- **`database.sql` is regenerated** — automated installs can always get a fresh dump matching the exact current schema
- **No external migration library** — avoids dependency conflicts and versioning headaches for what is fundamentally simple ALTER TABLE work