# Database setup and existing-data import

## New MySQL database

Run as a database administrator, replacing the example password:

```sql
CREATE DATABASE scholarbooks CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE USER 'scholarbooks'@'localhost' IDENTIFIED BY 'CHOOSE-A-UNIQUE-STRONG-PASSWORD';
GRANT ALL PRIVILEGES ON scholarbooks.* TO 'scholarbooks'@'localhost';
```

Use the corresponding host-specific account if your application connects from another host. The application needs no privileges on other databases. After deployment, migrations can use a separate privileged account while the runtime account is restricted to the required table operations.

Set the connection in `.env`, generate `APP_KEY` once, then run `php artisan migrate`. For a fresh application, configure the initial administrator password and run `php artisan db:seed`.

The migration preserves all 18 original tables: `application_lock`, `users`, `login_attempts`, `semesters`, `students`, `requirements`, `enrollments`, `demands`, `codes`, `allocations`, `notifications`, `imports`, `provider_deliveries`, `issues`, `issue_students`, `issue_events`, `requests`, and `audit`. Laravel also maintains its `migrations` table.

`users.remember_token` is the sole added application column. Laravel-compatible unsigned integer primary/foreign keys replace the original signed positive IDs. Existing IDs remain unchanged during import. Original string lengths, nullable fields, defaults, composite uniqueness, foreign keys, and UTC timestamp strings are preserved. A separate migration adds indexes for common semester/status reads. Original `login_attempts` history is preserved, while new authentication throttling uses Laravel's rate limiter.

The exact original MySQL DDL, reconstructed from `src/schema.php`, is available in `tests/Fixtures/legacy-schema.sql` for reference and automated migration tests. **Do not import that fixture to install the Laravel application; run migrations instead.**

## Import an existing raw-PHP installation

This command supports an existing MySQL installation matching the supplied application's schema. It does not run arbitrary uploaded SQL. If you have a dump, restore it into a separate source MySQL database first. The original application also supported SQLite; this Laravel importer intentionally targets MySQL, so an existing SQLite installation needs a separately verified transfer into a source MySQL database first.

1. Back up the old database and its private `config.php` (especially the 64-hex `app_key`). Keep the old application available for rollback.
2. Stop writes and scheduled email dispatch in the old application. Keep both applications in preview during verification. Do not send from both systems.
3. Create a **new, empty target** database. Point normal `DB_*` variables at it. Generate a new Laravel `APP_KEY` and run `php artisan migrate`. **Do not run `db:seed` or the demo seeder before import.**
4. Set the read-only source connection in `.env`:

```dotenv
LEGACY_DB_HOST=127.0.0.1
LEGACY_DB_PORT=3306
LEGACY_DB_DATABASE=scholarbooks_old
LEGACY_DB_USERNAME=legacy_readonly
LEGACY_DB_PASSWORD=your-source-password
LEGACY_APP_KEY=the_original_64_hex_characters
```

The source user only needs SELECT access. The command never alters the source.

5. Derive a permanent `SCHOLARBOOKS_HASH_KEY` from the original key. It must be `base64:` followed by the base64 encoding of the **32 binary bytes**, not of the 64-character hex string. To avoid putting the secret on a command line, once `.env` contains `LEGACY_APP_KEY`, use the local Laravel console:

```sh
php artisan tinker
```

Then evaluate locally:

```php
'base64:'.base64_encode(hex2bin(config('scholarbooks.legacy_key')))
```

Copy that result into `SCHOLARBOOKS_HASH_KEY` in `.env`. Exit Tinker. Keep this key permanently: it preserves the original HMAC fingerprints so previously imported codes/files remain recognizable. Do not publish either key or a console transcript containing them.

6. Run:

```sh
php artisan config:clear
php artisan scholarbooks:import-legacy
```

The importer copies tables in dependency order with the original IDs. It preserves password hashes, case histories, requests, code ownership, and outbox state. It decrypts old AES-256-GCM values with the old key and encrypts them with Laravel's new key. It retains code and import HMAC hashes unchanged. A decryption failure or any failed insertion rolls back the entire target copy. An extra `legacy_imported` audit record is added; `application_lock.revision` is operational rather than historical and is initialized by the migration.

7. Compare the printed row counts against the source. Sign in with an existing account, inspect student histories, and preview a protected message. Check open cases, refunds, allocations, and pending/uncertain notifications. Keep SMTP disabled until reconciliation is complete.
8. Remove `LEGACY_DB_*` credentials and `LEGACY_APP_KEY` from the new application's environment and clear/rebuild its config cache. **Keep `SCHOLARBOOKS_HASH_KEY` and the new Laravel `APP_KEY`.** Retain the old key in your protected historical backups.
9. Switch the web server to the new application's `public/` folder. Enable only the new scheduler, then enable SMTP when ready.

Existing usernames and passwords continue to work. No universal default password is introduced. Laravel rehashes valid passwords as needed at login.

## Rollback and backups

Until the new installation is accepted, retain the source database untouched. If verification fails, leave the new target offline and resume the old installation from its original database/configuration. Once the new system starts accepting changes, reverting requires a deliberate data reconciliation; do not simply switch back to an older snapshot.

Never run `migrate:fresh`, `migrate:reset`, or the standalone integration tests against operational data. Those commands intentionally remove tables and are only used in disposable test databases.