# SQM Updates 105-107: Complete Schema Migration Fix

## Problem Statement
mojoPortal setup was stuck on version **2.3.5.8** and repeatedly failed when attempting to upgrade to **2.3.5.9** with:
```
SQLite error duplicate column name: PasswordResetGuid
```

The issue was not a connection string or version mismatch, but a **database schema consistency problem**.

## Root Cause Analysis

1. **Columns Already Existed**: The database had `PasswordResetGuid` and `IncludeInSearch` columns already added (from a previous partial migration or manual schema modification)

2. **Schema Version Tracking Stuck**: The `mp_SchemaVersion` table was stuck at `2.3.5.8` (Major=2, Minor=3, Build=5, Revision=8)

3. **Upgrade Re-attempted**: mojoPortal's setup kept trying to apply version 2.3.5.9 upgrade script because the schema version wasn't advanced, but the ALTER TABLE ADD COLUMN failed since the columns already existed

4. **Missing Settings**: The configuration settings defined in 2.3.5.9 (`RequireApprovalBeforeLogin`, `PasswordRegexWarning`, etc.) were also not inserted into `mp_SiteSettingsExDef`

## Solution: Three-Stage Ordinated Updates

### SQM_Update_105.ps1: Pre-Migration Column Check
**Purpose**: Ensure columns exist without duplicating them

**Actions**:
- Backs up the database to `C:\___Fire\SqliteMojo\Backups\mojo_db_TIMESTAMP.backup`
- Checks if `PasswordResetGuid` column exists in `mp_Users` using `PRAGMA table_info()`
- If missing, adds it and sets default value to `'00000000-0000-0000-0000-000000000000'`
- Checks if `IncludeInSearch` column exists in `mp_Modules`
- If missing, adds it and sets default value to `1`

**Result**: Both columns now guaranteed to exist without raising duplicate errors

### SQM_Update_106.ps1: Schema Version Advancement
**Purpose**: Mark version 2.3.5.9 as complete in database tracking

**Actions**:
- Backs up the database
- Verifies required columns exist
- Updates `mp_SchemaVersion` table: sets `Revision = 9` for `ApplicationName = 'mojoportal-core'`
- Changes schema version from `2|3|5|8` to `2|3|5|9`

**Result**: mojoPortal setup will now **skip** the 2.3.5.9 upgrade step and continue to subsequent versions

### SQM_Update_107.ps1: Settings Definitions Recovery
**Purpose**: Add missing 2.3.5.9 settings definitions

**Actions**:
- Backs up the database
- Inserts 4 required settings into `mp_SiteSettingsExDef`:
  - `RequireApprovalBeforeLogin` (default: `false`)
  - `PasswordRegexWarning` (default: empty)
  - `RolesThatCanApproveNewUsers` (default: empty)
  - `RolesThatCanManageSkins` (default: empty)
- Uses `INSERT OR IGNORE` to safely add without duplicating

**Result**: All settings from 2.3.5.9 are now available in the system configuration

## Deployment Record

```
[2026-04-15 00:45:09] ✓ SQM_Update_105 - Columns verified/added
[2026-04-15 00:52:33] ✓ SQM_Update_106 - Schema version advanced to 2.3.5.9
[2026-04-15 00:52:51] ✓ SQM_Update_107 - Settings entries added (4/4)
```

## Verification Checklist

✅ Database backed up at each stage
✅ PasswordResetGuid column exists in mp_Users
✅ IncludeInSearch column exists in mp_Modules  
✅ Schema version updated to 2.3.5.9
✅ All 4 required settings inserted
✅ No duplicate column errors raised

## Next Steps for Production

1. **Refresh mojoPortal setup page** in browser (clear cache if needed)
2. **Allow setup to continue** - it should now:
   - Skip version 2.3.5.9 (already applied)
   - Continue through remaining versions
   - Complete upgrade to code version 2.9.2.3

3. **Monitor for completion** - setup page should display success message

## Rollback Procedure

If needed, each update created a backup:
```powershell
# List backups
Get-ChildItem "C:\___Fire\SqliteMojo\Backups" | Sort-Object LastWriteTime

# Restore from specific backup (CHOOSE TIMESTAMP)
Copy-Item "C:\___Fire\SqliteMojo\Backups\mojo_db_20260415_005251.backup" `
    "C:\___Fire\SqliteMojo\Web\Data\sqlitedb\mojo.db.config" -Force
```

## Technical Details

- **Database**: SQLite at `C:\___Fire\SqliteMojo\Web\Data\sqlitedb\mojo.db.config`
- **Version table**: `mp_SchemaVersion` (ApplicationName='mojoportal-core')
- **Settings table**: `mp_SiteSettingsExDef`
- **Tools used**: `sqlite3.exe` CLI (included in mojoPortal distribution)
- **Framework**: PowerShell with .NET Framework 4.8.1

## Files Modified/Created

- `Updates/SQM_Update_105.ps1` - Column existence check and safe addition
- `Updates/SQM_Update_106.ps1` - Schema version advancement
- `Updates/SQM_Update_107.ps1` - Settings definitions recovery
- `Updates/SQM_UPDATE_105_REMEDIATION.md` - Initial diagnosis (supplementary)
- `Updates/SQM_UPDATES_106_107_COMPLETE.md` - This document

## Related Issues Fixed

This resolves the setup blockage that prevented upgrade from:
- Schema: 2.3.5.8
- Code: 2.9.2.3

The schema can now advance beyond 2.3.5.9 and complete all pending migrations.