# Koha Recovery → opac-ils-lite Migration Plan

> **Source**: `koha_recovery.sql` (180 MB, 159 tables, MySQL/InnoDB, charset utf8mb4)  
> **Target**: `opac-ils-lite` (Laravel 11 + SQLite, 19 tables)  
> **Strategy**: Selective migration — extract only what the new schema supports; discard Koha-specific internals

---

## Overview of Data to Migrate

| Koha Source Table | → | New Target Table | Rows | Notes |
|---|---|---|---|---|
| `branches` | → | `libraries` | **1** | Single library (Mountain Top University) |
| `categories` | → | `patron_categories` | **8** | Patron type codes |
| `itemtypes` | → | `item_types` | **10** | Loan/material types |
| `borrowers` | → | `patrons` | **3,998** | Core patrons + passwords |
| `biblio` + `biblioitems` | → | `biblios` | **11,530** | Merged bibliographic records |
| `items` | → | `items` | **11,628** | Physical copies |
| `issues` | → | `issues` | **3** | Active checkouts |
| `old_issues` | → | `issues` (historical) | **15** | Returned loans → import as closed issues |
| `accountlines` | → | `fines` | **7** | Patron fines/charges |
| `auth_header` | → | `authorities` | **24,328** | Authority headings (text only, no MARC blob) |
| `opac_news` | → | `news` | **5** | Library news |
| `aqbooksellers` | → | `vendors` | check | Acquisition vendors |
| `aqbudgetperiods` | → | `budgets` | check | Budget periods |
| `aqorders` | → | `orders` | **7** | Acquisition orders |
| `course_reserves` | → | `course_reserves` | **0** | Empty in source |
| `discharges` | → | `discharges` | **0** | Empty in source |
| `reserves` (active holds) | → | `holds` | 0 | No active holds in source |

> [!NOTE]
> Staff permissions (`staff_permissions`) and system preferences (`system_preferences`) will be seeded from data in the Koha source rather than migrated as-is, because the new schema structure differs significantly.

---

## Pre-Migration Requirements

> [!IMPORTANT]
> The new system uses **SQLite** (not MySQL). All migration scripts must be written as PHP Artisan commands that transform and insert via Eloquent/PDO — **not** raw SQL dumps.

> [!WARNING]
> The `patrons` table requires `email` and `username` to be **unique and non-null**. Many Koha borrowers may have NULL or duplicate emails — these must be sanitized before import.

> [!WARNING]
> Koha `biblio` and `biblioitems` are two separate tables joined by `biblionumber`. The new `biblios` table merges them — you must join on `biblionumber` during migration.

---

## Step-by-Step Migration Checklist

---

### PHASE 0 — Setup & Preparation

- `[ ]` **0.1** Back up current `database/database.sqlite` before any migration  
  ```bash
  copy database\database.sqlite database\database.sqlite.bak
  ```
- `[ ]` **0.2** Confirm all migrations are run and schema is fresh  
  ```bash
  php artisan migrate:fresh
  ```
- `[ ]` **0.3** Import `koha_recovery.sql` into a **temporary MySQL database** (local MySQL or Docker)  
  ```bash
  mysql -u root -p koha_temp < koha_recovery.sql
  ```
  This is needed because the SQL uses MySQL-specific syntax and you need to query it efficiently.
- `[ ]` **0.4** Create the migration command scaffold  
  ```bash
  php artisan make:command MigrateKohaData
  ```

---

### PHASE 1 — Libraries (branches → libraries)

**Source**: `branches`  
**Target**: `libraries`  
**Rows**: 1

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `branchcode` | → | `branchcode` | direct |
| `branchname` | → | `name` | direct |
| `branchaddress1` + `branchaddress2` + `branchaddress3` | → | `address` | concatenate with newline |
| `branchphone` | → | `phone` | direct |
| `branchemail` | → | `email` | direct |

- `[ ]` **1.1** Write `migrateLibraries()` method in the Artisan command
- `[ ]` **1.2** Run and verify: `SELECT * FROM libraries;` should show 1 row (MTU branch)

---

### PHASE 2 — Patron Categories (categories → patron_categories)

**Source**: `categories`  
**Target**: `patron_categories`  
**Rows**: 8

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `categorycode` | → | `code` | direct |
| `description` | → | `description` | direct |
| `category_type` | → | `category_type` | Koha: `A`(adult),`S`(staff),`C`(child) → map to: `adult`,`staff`,`student`,`child` |
| `enrolmentperiod` | → | `enrolment_period` | in months; default to 12 if NULL |
| `issuelimit` | → | `max_checkouts` | default 5 if NULL |

- `[ ]` **2.1** Write `migratePatronCategories()` method
- `[ ]` **2.2** Run and verify: 8 categories imported

---

### PHASE 3 — Item Types (itemtypes → item_types)

**Source**: `itemtypes`  
**Target**: `item_types`  
**Rows**: 10

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `itemtype` | → | `code` | direct |
| `description` | → | `description` | direct |
| *(no direct loan_period in Koha itemtypes)* | → | `loan_period` | default = 21 |
| `rentalcharge_daily` | → | `fine_rate` | default 0.20 if NULL |

> [!NOTE]
> Koha stores loan periods in `circulation_rules`, not `itemtypes`. For now, use the default 21-day period. You can refine later.

- `[ ]` **3.1** Write `migrateItemTypes()` method
- `[ ]` **3.2** Run and verify: 10 item types imported

---

### PHASE 4 — Patrons (borrowers → patrons)

**Source**: `borrowers`  
**Target**: `patrons`  
**Rows**: ~3,998 (after deduplication)

> [!CAUTION]
> This is the most complex step. Many borrowers have NULL emails. You must generate placeholder emails for those, and the `patron_categories` and `libraries` tables must already be populated (Phase 1–2 dependency).

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `cardnumber` | → | `cardnumber` | direct; generate if NULL |
| `firstname` | → | `firstname` | direct |
| `surname` | → | `surname` | direct |
| `email` | → | `email` | sanitize: if NULL → `cardnumber@mtu.library` |
| `userid` | → | `username` | sanitize: if NULL → `user_{borrowernumber}` |
| `password` | → | `password` | **Koha uses MD5 hashing** — re-hash with bcrypt or force reset |
| `categorycode` | → | `category_id` | FK lookup to `patron_categories.code` |
| `branchcode` | → | `library_id` | FK lookup to `libraries.branchcode` |
| `dateexpiry` | → | `expiry_date` | direct |

**Sanitization Rules**:
1. Skip `deletedborrowers` (already deleted)
2. NULL email → generate `{cardnumber}@mtu.edu.ng`
3. NULL username → `user_{borrowernumber}`
4. Duplicate email → append `_{n}` suffix
5. NULL cardnumber → generate `CARD{borrowernumber}`
6. Passwords: Koha stored MD5 hashes — **cannot be directly reused** (different algorithm). Set to `bcrypt('changeme123')` and flag for password reset.

- `[ ]` **4.1** Write data quality audit query on MySQL temp DB  
  ```sql
  SELECT COUNT(*) FROM borrowers WHERE email IS NULL;
  SELECT COUNT(*) FROM borrowers WHERE cardnumber IS NULL;
  SELECT email, COUNT(*) FROM borrowers GROUP BY email HAVING COUNT(*) > 1;
  ```
- `[ ]` **4.2** Write `migratePatrons()` method with sanitization
- `[ ]` **4.3** Store a mapping table `koha_borrowernumber → new patron.id` for use in later phases
- `[ ]` **4.4** Run and verify row count: expect ~3,998 patrons

---

### PHASE 5 — Bibliographic Records (biblio + biblioitems → biblios)

**Source**: `biblio` JOIN `biblioitems ON biblionumber`  
**Target**: `biblios`  
**Rows**: ~11,530

**Column Mapping** (joined query):
| Koha Column | Table | → | New Column | Transform |
|---|---|---|---|---|
| `biblionumber` | biblio | → | *(koha_id stored in notes for tracing)* | |
| `title` | biblio | → | `title` | direct |
| `author` | biblio | → | `author` | direct |
| `isbn` | biblioitems | → | `isbn` | take first ISBN if multiple (longtext) |
| `publishercode` | biblioitems | → | `publisher` | direct |
| `publicationyear` | biblioitems | → | `pub_year` | cast to int |
| `cn_class` | biblioitems | → | `call_number` | direct (LCC class) |
| `notes` | biblio | → | `notes` | direct |
| `copyrightdate` | biblio | → | *(fallback for pub_year)* | use if publicationyear NULL |

> [!NOTE]
> `cover_url` and `subject` are not in the core Koha tables — they come from MARC metadata (`biblio_metadata.marcxml`). For now, leave NULL. You can enhance later with MARC parsing.

- `[ ]` **5.1** Write `migrateBiblios()` method using JOIN query
- `[ ]` **5.2** Store `koha_biblionumber → new biblio.id` mapping table
- `[ ]` **5.3** Run and verify: expect ~11,530 records

---

### PHASE 6 — Items (items → items)

**Source**: `items`  
**Target**: `items`  
**Rows**: ~11,628

**Dependencies**: Phase 3 (item_types), Phase 5 (biblios), Phase 1 (libraries)

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `barcode` | → | `barcode` | direct; generate if NULL |
| `biblionumber` | → | `biblio_id` | FK via mapping from Phase 5 |
| `homebranch` | → | `library_id` | FK lookup via `libraries.branchcode` |
| `itype` | → | `item_type_id` | FK lookup via `item_types.code` |
| `location` | → | `location` | direct |
| `notforloan`/`damaged`/`itemlost`/`withdrawn` | → | `status` | derive: `available`, `checkedout`, `lost`, `damaged` |

**Status Derivation Logic**:
```
IF itemlost > 0 → 'lost'
ELSE IF damaged > 0 → 'damaged'  
ELSE IF withdrawn > 0 → 'withdrawn'
ELSE IF onloan IS NOT NULL → 'checkedout'
ELSE → 'available'
```

- `[ ]` **6.1** Write `migrateItems()` method
- `[ ]` **6.2** Store `koha_itemnumber → new item.id` mapping
- `[ ]` **6.3** Run and verify: expect ~11,628 items

---

### PHASE 7 — Issues / Loans (issues + old_issues → issues)

**Source**: `issues` (active, 3 rows) + `old_issues` (returned, 15 rows)  
**Target**: `issues`  
**Rows**: ~18

**Dependencies**: Phase 4 (patrons), Phase 6 (items)

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `borrowernumber` | → | `patron_id` | FK via patron mapping |
| `itemnumber` | → | `item_id` | FK via item mapping |
| `issuedate` | → | `issuedate` | direct |
| `date_due` | → | `date_due` | direct |
| `returndate` | → | `returndate` | NULL for active; date for old_issues |

> [!NOTE]
> `old_issues` are completed loans — set `returndate` to the actual return date. Active `issues` have NULL `returndate`.

- `[ ]` **7.1** Write `migrateIssues()` — first active, then old
- `[ ]` **7.2** Update item status to `checkedout` for active loans (cross-reference Phase 6)
- `[ ]` **7.3** Run and verify: 3 active + 15 historical = 18 total

---

### PHASE 8 — Fines (accountlines → fines)

**Source**: `accountlines`  
**Target**: `fines`  
**Rows**: 7

**Dependencies**: Phase 4 (patrons), Phase 7 (issues)

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `borrowernumber` | → | `patron_id` | FK via patron mapping |
| `issue_id` | → | `issue_id` | FK via issue mapping (NULL if no issue) |
| `amount` | → | `amount` | take absolute value (Koha credits are negative) |
| `debit_type_code` | → | `fine_type` | direct (e.g. `OVERDUE`) |
| `date` | → | `due_date` | direct |

- `[ ]` **8.1** Write `migrateFines()` method
- `[ ]` **8.2** Run and verify: 7 fines imported

---

### PHASE 9 — Authorities (auth_header → authorities)

**Source**: `auth_header`  
**Target**: `authorities`  
**Rows**: 24,328

> [!WARNING]
> Koha's `auth_header` stores full MARC XML in the `marcxml` field. The new `authorities` table only stores `type` and `heading`. You must parse the MARC XML to extract the heading text, OR use the `authtypecode` as `type` and a simplified heading extraction.

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `authtypecode` | → | `type` | direct (e.g., `PERSO_NAME`, `CORPO_NAME`) |
| `marcxml` | → | `heading` | Parse: extract `<datafield tag="100">`/`<datafield tag="110">` `$a` subfield from XML |
| *(none)* | → | `see_also` | NULL |

**MARC XML Extraction (PHP snippet)**:
```php
$xml = simplexml_load_string($marcxml);
$heading = '';
foreach ($xml->datafield as $field) {
    $tag = (string)$field['tag'];
    if (in_array($tag, ['100','110','111','130','150','151'])) {
        foreach ($field->subfield as $sub) {
            if ((string)$sub['code'] === 'a') {
                $heading = (string)$sub;
                break 2;
            }
        }
    }
}
```

- `[ ]` **9.1** Write `migrateAuthorities()` with batch chunking (1000 rows at a time)
- `[ ]` **9.2** Run and verify: expect ~24,328 authorities (some may be skipped if MARC parsing fails)

---

### PHASE 10 — Library News (opac_news → news)

**Source**: `opac_news`  
**Target**: `news`  
**Rows**: 5

**Column Mapping**:
| Koha Column | → | New Column | Transform |
|---|---|---|---|
| `title` | → | `title` | direct |
| `content` | → | `content` | direct |
| `timestamp` | → | `published_date` | cast to date |
| `branchcode` | → | `library_id` | FK lookup; NULL if global |
| *(no active flag in Koha)* | → | `active` | default true |

- `[ ]` **10.1** Write `migrateNews()` method
- `[ ]` **10.2** Run and verify: 5 news items

---

### PHASE 11 — Vendors & Orders (aqbooksellers → vendors, aqorders → orders)

**Source**: `aqbooksellers`, `aqorders`  
**Target**: `vendors`, `orders`

**Vendors mapping**:
| Koha | → | New | Transform |
|---|---|---|---|
| `name` | → | `name` | direct |
| `address1`+`address2`+`address3`+`address4` | → | `address` | concatenate |
| `phone` | → | `contact` | direct |
| `active` | → | `active` | direct |

**Orders mapping**:
| Koha | → | New | Transform |
|---|---|---|---|
| `basketno` → resolve to `aqbasket.booksellerid` | → | `vendor_id` | FK via vendor mapping |
| `biblionumber` | → | `biblio_id` | FK via biblio mapping |
| `quantity` | → | `quantity` | direct |
| `unitprice` | → | `price` | direct |
| `orderstatus` | → | `status` | map: `new`→`pending`, `complete`→`received`, `cancelled`→`cancelled` |

- `[ ]` **11.1** Write `migrateVendors()` and `migrateOrders()` methods
- `[ ]` **11.2** Verify vendors and orders imported correctly

---

### PHASE 12 — Staff Permissions

**Source**: Koha `borrowers.flags` (bitmask integer)  
**Target**: `staff_permissions`

> [!NOTE]
> Koha encodes staff permissions as a bitmask in `borrowers.flags`. The new system has explicit boolean columns per permission.

**Koha Flags Bitmask**:
| Bit | Permission |
|---|---|
| 0 | superlibrarian |
| 1 | circulate |
| 2 | catalogue |
| 3 | parameters |
| 4 | borrowers |
| 5 | permissions |
| 6 | reserveforothers |
| 7 | editcatalogue |
| 9 | updatecharges |
| 10 | acquisition |
| 11 | staffaccess |
| 13 | tools |
| 16 | serials |
| 24 | reports |

**Strategy**: Select all borrowers where `flags > 0` and decode the bitmask, then insert into `staff_permissions`.

- `[ ]` **12.1** Write `migrateStaffPermissions()` method
- `[ ]` **12.2** Verify superlibrarian (flags=1) is set for admin users

---

### PHASE 13 — System Preferences

**Source**: Koha `systempreferences` table  
**Target**: `system_preferences`

Key preferences to migrate:
- `LibraryName` → library name display setting
- `dateformat` → date formatting
- `maxoutstanding` → fine threshold
- `noissuescharge` → checkout block threshold
- `OPACBaseURL` → OPAC URL setting

- `[ ]` **13.1** Write `migrateSystemPreferences()` for key settings only
- `[ ]` **13.2** Verify key preferences appear in admin panel

---

### PHASE 14 — Course Reserves

**Source**: `course_reserves` (0 rows in backup)  
**Target**: `course_reserves`

- `[ ]` **14.1** Skip — source is empty; leave for manual data entry

---

### PHASE 15 — Discharges

**Source**: `discharges` (0 rows in backup)  
**Target**: `discharges`

- `[ ]` **15.1** Skip — source is empty; leave for future use

---

## Artisan Command Structure

Create a single orchestrator command:

```php
// app/Console/Commands/MigrateKohaData.php
php artisan migrate:koha --source-db=koha_temp --step=all

// Individual steps:
php artisan migrate:koha --step=libraries
php artisan migrate:koha --step=categories
php artisan migrate:koha --step=item-types
php artisan migrate:koha --step=patrons
php artisan migrate:koha --step=biblios
php artisan migrate:koha --step=items
php artisan migrate:koha --step=issues
php artisan migrate:koha --step=fines
php artisan migrate:koha --step=authorities
php artisan migrate:koha --step=news
php artisan migrate:koha --step=vendors
php artisan migrate:koha --step=orders
php artisan migrate:koha --step=staff-permissions
php artisan migrate:koha --step=preferences
```

The command reads from a **separate MySQL connection** (`koha_source`) defined in `config/database.php`, and writes to the default SQLite connection.

---

## Database Config Addition

Add to `config/database.php`:

```php
'koha_source' => [
    'driver'   => 'mysql',
    'host'     => env('KOHA_DB_HOST', '127.0.0.1'),
    'database' => env('KOHA_DB_DATABASE', 'koha_temp'),
    'username' => env('KOHA_DB_USERNAME', 'root'),
    'password' => env('KOHA_DB_PASSWORD', ''),
    'charset'  => 'utf8mb4',
    'collation'=> 'utf8mb4_unicode_ci',
],
```

Add to `.env`:
```env
KOHA_DB_HOST=127.0.0.1
KOHA_DB_DATABASE=koha_temp
KOHA_DB_USERNAME=root
KOHA_DB_PASSWORD=
```

---

## Verification Checklist (Post-Migration)

- `[ ]` **V1** Total patrons in new DB ≥ 3,990
- `[ ]` **V2** Total biblios ≥ 11,500
- `[ ]` **V3** Total items ≥ 11,600
- `[ ]` **V4** Staff admin can log in (test with known borrowernumber)
- `[ ]` **V5** OPAC search returns results
- `[ ]` **V6** Checkout (circulation) works for a patron
- `[ ]` **V7** Fines visible on patron account
- `[ ]` **V8** Library name shows "Mountain Top University Library"
- `[ ]` **V9** News items appear on OPAC
- `[ ]` **V10** Authorities browsable in staff catalogue

---

## Known Data Gaps (Accept & Document)

| Gap | Reason | Workaround |
|---|---|---|
| Patron passwords | Koha used MD5; bcrypt required | Force reset to `changeme123` |
| MARC bibliographic metadata | Full MARC XML not stored in `biblios` | Title/author/ISBN sufficient for this system |
| Subscription/serial records | New system tracks via `subscriptions` table | Requires separate migration script |
| Hold queue | No active holds in backup | Fresh start |
| Patron images | Binary blob in `patronimage` | Not supported in new schema |
| Action logs | 159-table audit trail | Not applicable to new simplified system |

---

## Estimated Timeline

| Phase | Task | Estimated Time |
|---|---|---|
| 0 | Setup | 30 min |
| 1–3 | Libraries, Categories, ItemTypes | 30 min |
| 4 | Patrons (3,998 rows) | 1–2 hrs |
| 5 | Biblios (11,530 rows) | 1 hr |
| 6 | Items (11,628 rows) | 1 hr |
| 7–8 | Issues + Fines | 30 min |
| 9 | Authorities (24,328 + MARC parsing) | 2–3 hrs |
| 10–13 | News, Vendors, Orders, Preferences | 1 hr |
| 14–15 | Verification | 1 hr |
| **Total** | | **~9–11 hrs** |