timebank-cc-public/references/CYCLOS_IMPORT.md

# Cyclos Database Import Reference

This document describes how the legacy Cyclos database is imported into the Laravel Timebank application during the `php artisan db:seed` process.

## Overview

The Cyclos import is a production-grade data migration system that transfers members, accounts, transactions, profile data, and images from a Cyclos timebank database into the new Laravel-based system. The migration preserves historical data integrity while transforming it to fit the new polymorphic multi-profile architecture.

## Migration Flow

The import process is orchestrated through `database/seeders/DatabaseSeeder.php` and consists of several stages, each triggered by user confirmation prompts:

```
db:seed
├── Database refresh & base seeders
├── [Optional] migrate:cyclos              ← Main migration
│   ├── [Optional] migrate:cyclos-profiles ← Profile data
│   │   ├── profiles:clean-about           ← Cleanup
│   │   └── profiles:clean-cyclos_skills   ← Cleanup
│   └── [Optional] migrate:cyclos-gift-accounts ← Account consolidation
└── [Optional] Elasticsearch re-indexing
```

## Source Database Configuration

The source Cyclos database name is prompted during the migration process. The database must be imported into MySQL and accessible from the application.

**Setup Steps:**
1. Export the Cyclos database as a SQL dump
2. Place the dump file in the application root
3. Import into MySQL: `mysql -u root -p < cyclos_dump.sql`
4. Run the migration and enter the database name when prompted

**Input Validation:**
- If you enter the database name with a `.sql` extension (e.g., `timebank_2025_09_19.sql`), it will be automatically stripped
- The command verifies the database exists before proceeding; if not found, it displays available databases to help identify typos

During the `db:seed` process, the source database name entered in `migrate:cyclos` is cached and automatically reused by `migrate:cyclos-profiles`, so you only need to enter it once.

The destination database is read from the environment:

```php
$destinationDb = env('DB_DATABASE');
```

---

## Stage 1: Main Migration (`migrate:cyclos`)

**File:** `app/Console/Commands/MigrateCyclosCommand.php`

This is the primary migration command that handles members, images, accounts, and transactions.

### Member Group Mapping

Cyclos organizes members into groups. The migration maps these groups to Laravel model types:

| Cyclos Group ID | Group Name | Laravel Model | Status |
|-----------------|------------|---------------|--------|
| 5 | Active Users | `User` | Active |
| 6 | Inactive Users | `User` | `inactive_at` set |
| 8 | Removed Users | `User` | `deleted_at` set |
| 13 | Local Banks (Level I) | `Bank` | `level = 1` |
| 14 | Organizations | `Organization` | Active |
| 15 | Projects (Level II) | `Bank` | `level = 2` |
| 17 | Local Admins | *Not migrated* | - |
| 18 | TEST: Projects | `Organization` | Active |
| 22 | TEST: Users | `User` | Active |
| 27 | Inactive Projects | `Organization` | `inactive_at` set |

### Member Field Mapping

Each member type maps Cyclos fields to Laravel columns:

| Cyclos Field | Laravel Column | Notes |
|--------------|----------------|-------|
| `members.id` | `cyclos_id` | Foreign key reference |
| `members.name` | `full_name` | Display name |
| `members.email` | `email` | - |
| `members.member_activation_date` | `email_verified_at` | Converted via `FROM_UNIXTIME()` |
| `members.creation_date` | `created_at` | Converted via `FROM_UNIXTIME()` |
| `users.username` | `name` | Profile URL slug |
| `users.password` | `password` | Preserved hash (may need reset) |
| `users.salt` | `cyclos_salt` | For potential password compatibility |
| `users.last_login` | `last_login_at` | Converted via `FROM_UNIXTIME()` |
| `group_history_logs.start_date` | `inactive_at` / `deleted_at` | For inactive/removed members |

### Member Migration SQL Pattern

Each member type uses a similar INSERT pattern with ON DUPLICATE KEY UPDATE for idempotency:

```sql
INSERT INTO {destination}.users (cyclos_id, full_name, email, ...)
SELECT
    m.id AS cyclos_id,
    m.name AS full_name,
    m.email AS email,
    FROM_UNIXTIME(UNIX_TIMESTAMP(m.creation_date)) AS created_at,
    ...
FROM {source}.members m
JOIN {source}.users u ON m.id = u.id
WHERE m.group_id = {group_id}
ON DUPLICATE KEY UPDATE ...
```

### Removed User Handling

Removed users (group_id = 8) receive anonymized data:

```sql
CONCAT('Removed Cyclos user', m.id) AS full_name,
CONCAT(m.id, '@removed.mail') AS email,
CONCAT('Removed user ', m.id) AS name,
0 as limit_min,
0 as limit_max
```

### Profile Images Migration

Images are stored as binary data in Cyclos. The migration:

1. Selects the image with the lowest `order_number` for each member (primary profile photo)
2. Extracts binary data from `{source}.images.image`
3. Saves to `storage/app/public/profile-photos/{type}_{uniqid}.jpg`
4. Updates the profile's `profile_photo_path` column
5. Sets default avatar path for profiles without images

```php
Storage::disk('public')->put($filename, $image->image);
```

### Account Migration

Cyclos uses account types identified by `type_id`. These map to the polymorphic accounts table:

| Cyclos Type ID | Account Type | Owner Types | Notes |
|----------------|--------------|-------------|-------|
| 1 | Debit | Bank (id=1) | System settlement account |
| 2 | Community | Bank (id=1) | Community pool |
| 5 | Work | User, Organization, Bank | Primary transaction account |
| 6 | Gift | User, Organization, Bank | Temporary, consolidated later |
| 7 | Project | User, Organization, Bank | Project-specific transactions |

Account limits are pulled from white-label configuration:

```php
$userLimitMin = timebank_config('accounts.user.limit_min');
$userLimitMax = timebank_config('accounts.user.limit_max');
```

### Account Migration SQL Pattern

```sql
INSERT INTO {destination}.accounts (name, accountable_type, accountable_id, cyclos_id, ...)
SELECT
    '{accountName}' AS name,
    'App\\Models\\User' AS accountable_type,
    u.id AS accountable_id,
    a.id AS cyclos_id,
    ...
FROM {source}.accounts a
JOIN {destination}.users u ON a.member_id = u.cyclos_id
WHERE a.type_id = {type_id}
```

### Transaction Migration

**Critical Transformation:** Cyclos stores amounts as decimal hours (e.g. `0.8667` = 52 minutes). Laravel stores amounts as integer minutes.

```sql
ROUND(t.amount * 60) AS amount
```

**Known limitation:** Rounding to integer minutes per transaction accumulates small errors across accounts with many transactions. For example, an account with 502 transactions may end up with a balance ±11 minutes off from Cyclos. The `verify:cyclos-migration` command reports these discrepancies. A per-account correction mechanism is planned to address this.

Transaction mapping:

| Cyclos Field | Laravel Column | Notes |
|--------------|----------------|-------|
| `transfers.from_account_id` | `from_account_id` | Via `accounts.cyclos_id` join |
| `transfers.to_account_id` | `to_account_id` | Via `accounts.cyclos_id` join |
| `transfers.amount` | `amount` | Multiplied by 60, rounded to integer minutes |
| `transfers.description` | `description` | - |
| `transfers.type_id` | `transaction_type_id` | Direct mapping |
| - | `transaction_status_id` | Always `1` (completed) |
| `transfers.date` | `created_at` | Converted via `FROM_UNIXTIME()` |

### Validation Checks

After migration, three inline integrity checks are performed:

1. **Member Count:** Total Cyclos members (excluding group 17) must equal Users + Organizations + Banks
2. **Transaction Count:** Total Cyclos transfers must equal Laravel transactions
3. **Balance Integrity:** Net balance across all accounts must equal zero

For a full verification after the complete seed process, run:

```bash
php artisan verify:cyclos-migration
```

This checks member counts, transaction counts, per-account-type balances, gift account cleanup, and deleted profile cleanup. See `references/CYCLOS_MIGRATION_DISCREPANCIES.md` for the latest results.

```php
// Balance check using window functions
$totalNetBalance = DB::query()
    ->fromSub($accountBalances, 'account_balances')
    ->selectRaw('SUM(net_balance) as total_net_balance')
    ->first();

if ($totalNetBalance->total_net_balance != 0) {
    $this->error("OUT OF BALANCE AMOUNT: {$totalNetBalance->total_net_balance}");
}
```

### Laravel-Love Registration

After data migration, the command registers all profiles with the Laravel-Love package for reaction tracking:

```php
Artisan::call('love:register-reacters', ['--model' => 'App\Models\User']);
Artisan::call('love:register-reactants', ['--model' => 'App\Models\User']);
// ... for Organization and Bank as well
Artisan::call('love:add-reactions-to-transactions');
```

---

## Stage 2: Profile Data Migration (`migrate:cyclos-profiles`)

**File:** `app/Console/Commands/MigrateCyclosProfilesCommand.php`

This command migrates custom profile fields and preferences from Cyclos.

### Custom Field Mapping

Cyclos stores custom fields in `custom_field_values` with `field_id` identifiers:

| Field ID | Cyclos Field | Laravel Column | Model Types | Notes |
|----------|--------------|----------------|-------------|-------|
| 1 | Birthday | `date_of_birth` | User | Date format: `DD/MM/YYYY` → `YYYY-MM-DD` |
| 7 | Phone | `phone` | All | Truncated to 20 characters |
| 10 | Website | `website` | All | - |
| 13 | Skills | `cyclos_skills` | User, Organization | Truncated to 495 chars + "..." |
| 17 | About | `about` | User | - |
| 28 | General Newsletter | `message_settings.general_newsletter` | All | 790=No, 791=Yes |
| 29 | Local Newsletter | `message_settings.local_newsletter` | All | 792=No, 793=Yes |
| 35 | Motivation | `motivation` | All | - |
| 36 | Country | `locations.country_id` | All | Via code mapping |
| 38 | City | `locations.city_id` | All | Via code mapping |

### Location Code Mapping

Cyclos uses `possible_value_id` for location selection. These map to Laravel location IDs:

**Countries:**
```php
$countryCodeMap = [
    860 => 2,    // BE (Belgium)
    861 => 7,    // PT (Portugal)
    862 => 1,    // NL (Netherlands)
    863 => null, // Other/not set
];
```

**Cities:**
```php
$cityCodeMap = [
    864 => 188, // Amsterdam
    865 => 200, // Haarlem
    866 => 316, // Leiden
    867 => 305, // The Hague
    868 => 300, // Delft
    869 => 331, // Rotterdam
    870 => 272, // Utrecht
    881 => 345, // Brussels
];
```

After creating location records, the migration calls `$location->syncAllLocationData()` to populate related fields (divisions, etc.) from the parent city/country.

### Newsletter Preferences

Newsletter preferences migrate to the polymorphic `message_settings` table:

```php
$model->message_settings()->updateOrCreate(
    ['message_settingable_id' => $model->id, 'message_settingable_type' => $modelClass],
    ['general_newsletter' => $value]
);
```

### HTML Tag Stripping

After migration, HTML tags are stripped from text fields:

```php
$cleaned = strip_tags($record->cyclos_skills);
$cleaned = strip_tags($record->about);
```

---

## Stage 3: Cleanup Commands

### `profiles:clean-about`

**File:** `app/Console/Commands/CleanCyclosProfiles.php`

Removes empty paragraph markup from about fields:
- `<p></p>`
- `<p> </p>`
- `<p>&nbsp;</p>`
- `"` (single double-quote character)

Sets these to `null` for clean display.

### `profiles:clean-cyclos_skills`

**File:** `app/Console/Commands/CleanCyclosSkills.php`

Removes trailing pipe symbols from skills fields. Cyclos used pipe-delimited skill lists that sometimes had trailing pipes:

```php
$cleaned = preg_replace('/(\s*\|\s*)+$/', '', $original);
```

---

## Stage 4: Gift Account Consolidation (`migrate:cyclos-gift-accounts`)

**File:** `app/Console/Commands/MigrateCyclosGiftAccounts.php`

Cyclos had separate "gift" accounts (type_id = 6) that received welcome bonuses or gift transactions. This command consolidates gift account balances into primary work accounts and marks all gift accounts as inactive.

### How Gift Accounts Are Created

During Stage 1 (`migrate:cyclos`), gift accounts are imported from Cyclos for all profile types:

1. **All gift accounts are imported** from Cyclos `accounts` table where `type_id = 6`
2. They are created with `name = 'gift'` and associated with Users, Organizations, or Banks
3. All related transactions are also imported, preserving the original balances
4. At this stage, gift accounts are **active** (no `inactive_at` set)

### Consolidation Process

The `migrate:cyclos-gift-accounts` command then processes these accounts:

1. **Find all gift accounts** - Queries all accounts where `name = 'gift'`

2. **For each gift account, check the balance:**
   - If balance is **zero or negative**: Skip (nothing to transfer)
   - If balance is **positive**: Transfer the full balance to the owner's primary (non-gift) account

3. **Transfer positive balances:**
   ```php
   $transfer = new Transaction();
   $transfer->from_account_id = $fromAccount->id;  // Gift account
   $transfer->to_account_id = $toAccount->id;      // Primary work account
   $transfer->amount = $balance;
   $transfer->description = "Migration of balance from gift account (ID: {$fromAccount->id})";
   $transfer->transaction_type_id = 6;             // Migration type
   $transfer->save();
   ```

4. **Mark ALL gift accounts as inactive** - After the loop completes, **all** gift accounts are marked inactive, regardless of whether they had a positive balance or not:
   ```php
   $giftAccountIds = $giftAccounts->pluck('id');
   Account::whereIn('id', $giftAccountIds)->update(['inactive_at' => now()]);
   ```

### Important Notes

- **All gift accounts become inactive** after this command runs - not just those with positive balances
- Accounts with zero/negative balance are skipped for the transfer step but still marked inactive
- The original transactions remain in the gift account history (for audit purposes)
- The migration transaction creates a clear record of when and how balances were moved
- If no destination account is found for an owner, the gift account is logged as a warning but still marked inactive

---

## Database Safety Measures

### Transaction Wrapping

Every migration step is wrapped in database transactions:

```php
DB::beginTransaction();
try {
    // Migration logic
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    $this->error('Migration failed: ' . $e->getMessage());
}
```

### Job Queue Suppression

During migration, job dispatching is disabled to prevent side effects:

```php
\Queue::fake();
```

### Idempotency

Member migrations use `ON DUPLICATE KEY UPDATE` to allow re-running without data duplication.

---

## Password Handling

Cyclos password hashes are preserved in the `password` column with the salt stored in `cyclos_salt`. However, Laravel uses a different hashing mechanism (bcrypt by default), so:

- Users may need to use "Forgot Password" to set a new Laravel-compatible password
- Alternatively, a custom hash driver could be implemented to verify Cyclos passwords

---

## Running the Migration

### Prerequisites

1. Export the Cyclos database as a SQL dump
2. Import the dump into MySQL:
   ```bash
   mysql -u root -p < cyclos_dump.sql
   ```
3. Note the database name you used during import

### Full Fresh Migration

Use `seed.sh` for a complete fresh setup — it handles dropping tables, running migrations, maintenance mode, immutability restrictions, and prompts for the Cyclos DB:

```bash
./seed.sh
```

If running the seeder manually:

```bash
php artisan db:seed
```

You will be prompted through the following steps:

1. **Seed base data?** - Transaction types, permissions, countries, etc.
2. **Seed example tags?** - Optional tag data
3. **Migrate cyclos database?** - If yes:
   - You will be prompted to enter the source Cyclos database name
   - The database name is cached for use by subsequent commands
4. **Migrate cyclos profile data?** - Uses cached database name automatically
5. **Migrate cyclos gift accounts?** - Consolidates gift balances
6. **Assign Dev-Admin role?** - Optional admin assignment
7. **Re-create Elasticsearch index?** - Rebuilds search indices

### Individual Commands

When running commands individually (outside of `db:seed`), each command will prompt for the source database name if not cached:

```bash
# Main migration - prompts for database name and caches it
php artisan migrate:cyclos

# Profile data - uses cached name or prompts if cache expired
php artisan migrate:cyclos-profiles

# Cleanup commands (no database prompt needed)
php artisan profiles:clean-about
php artisan profiles:clean-cyclos_skills

# Gift account consolidation (no database prompt needed)
php artisan migrate:cyclos-gift-accounts
```

### Database Name Caching

The source database name is cached for 1 hour after being entered in `migrate:cyclos`. This allows `migrate:cyclos-profiles` to automatically use the same database without re-prompting during the `db:seed` process.

```php
// Set by migrate:cyclos
cache()->put('cyclos_migration_source_db', $sourceDb, now()->addHours(1));

// Retrieved by migrate:cyclos-profiles
$sourceDb = cache()->get('cyclos_migration_source_db');
```

---

## Key Files Reference

| File | Purpose |
|------|---------|
| `database/seeders/DatabaseSeeder.php` | Orchestrates the full seeding process |
| `app/Console/Commands/MigrateCyclosCommand.php` | Members, images, accounts, transactions |
| `app/Console/Commands/MigrateCyclosProfilesCommand.php` | Custom fields, locations, newsletters |
| `app/Console/Commands/MigrateCyclosGiftAccounts.php` | Gift account consolidation |
| `app/Console/Commands/CleanCyclosProfiles.php` | About field cleanup |
| `app/Console/Commands/CleanCyclosSkills.php` | Skills field cleanup |

---

## Post-Migration Considerations

1. **Password Resets:** Advise users to reset passwords via the forgot password flow
2. **Profile Photos:** Verify images are accessible at `/storage/profile-photos/`
3. **Balance Verification:** Run `php artisan verify:cyclos-migration` — all 20 checks should pass. See `references/CYCLOS_MIGRATION_DISCREPANCIES.md` for explanation of known discrepancies.
4. **Search Indexing:** Ensure Elasticsearch indices are rebuilt after migration
5. **Gift Accounts:** Verified automatically by `verify:cyclos-migration` (section 4)