# Admin Portal Authentication Fix & Seeding Guide

## Problem Identified

The admin authentication code had a **critical schema misalignment**:

### The Issue
1. **CredentialLookup** (`credential_lookup.ex:73`) expected `select: u.roles`
2. **User Schema** had NO `roles` field — only `metadata` (JSON)
3. **Database migration** created NO `roles` column
4. **Result**: Admin login would fail with database query errors

## What Was Fixed

### 1. Migration Added
**File**: `apps/wallet_database/priv/repo/migrations/20260325120000_add_roles_to_users.exs`
- Added `roles` column as JSON array to `users` table
- Applied successfully: `mix ecto.migrate -r WalletDatabase.Repo`

### 2. User Schema Updated
**File**: `apps/wallet_database/lib/wallet_database/schemas/auth/user.ex`
- Added `field :roles, {:array, :string}, default: []` (line 34)
- Added `:roles` to `@optional` fields (line 40)
- Added `:roles` to Jason encoder (line 19)

### 3. Database Seeded
**File**: `apps/wallet_database/priv/repo/seeds_dev_admin_users.sql`
- Created 6 dev users with proper roles
- Created 6 password credentials in `user_credentials` table
- Uses PBKDF2-SHA256 hashed passwords matching DevSeeds config

## Seeded Admin Accounts

| Email                      | Password      | Role(s)             | Use Case                    |
|----------------------------|---------------|---------------------|-----------------------------|
| admin@mercurypay.dev       | AdminPass@1   | admin               | Full system access          |
| ops@mercurypay.dev         | OpsPass@1     | ops_supervisor      | Operations management       |
| agent@mercurypay.dev       | AgentPass@1   | ops_agent           | Front-line support          |
| compliance@mercurypay.dev  | ComplPass@1   | compliance_officer  | KYC/AML case review         |
| sre@mercurypay.dev         | SrePass@1     | sre                 | Platform health monitoring  |
| customer@mercurypay.dev    | CustPass@1    | customer            | Customer portal testing     |

## Schema Alignment Verification

### ✅ Database Structure
```sql
mysql> desc users;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| user_id        | varchar(40)          | NO   | PRI | NULL    |       |
| email          | varchar(255)         | NO   | UNI | NULL    |       |
| ...            | ...                  | ...  | ... | ...     | ...   |
| roles          | json                 | NO   |     | NULL    |       |  ← NEW
| inserted_at    | datetime(6)          | NO   |     | NULL    |       |
| updated_at     | datetime(6)          | NO   |     | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+

mysql> desc user_credentials;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| credential_id    | varchar(40) | NO   | PRI | NULL    |       |
| user_id          | varchar(40) | NO   | MUL | NULL    |       |  ← FK to users
| credential_type  | varchar(24) | NO   |     | NULL    |       |
| password_hash    | varchar(255)| YES  |     | NULL    |       |  ← PBKDF2 hash
| ...              | ...         | ...  | ... | ...     | ...   |
+------------------+-------------+------+-----+---------+-------+
```

### ✅ Ecto Schema Alignment
- **User.roles** (line 34): `field :roles, {:array, :string}, default: []`
- **CredentialLookup** (line 73): `select: u.roles` — now works correctly
- **DevSeeds**: In-memory ETS store matches DB structure

### ✅ Authentication Flow
```
Login Request (email + password)
  ↓
CredentialLookup.lookup_credentials/1
  ↓
  ├─→ DB available? → Query users JOIN user_credentials
  │   └─→ Returns {user_id, password_hash}
  │
  └─→ DB not available? → DevSeeds.lookup/1 (ETS fallback)
      └─→ Returns {user_id, password_hash}
  ↓
WalletAuth.Commands.LoginWithPassword.execute/3
  ↓
  ├─→ Verify password hash (PBKDF2)
  ├─→ Create session token
  └─→ Store in browser session as "admin_token"
  ↓
AdminAuth.ensure_authenticated_admin/1 plug
  ↓
  ├─→ Validate JWT token
  ├─→ Extract roles from claims
  └─→ Assign current_user_id + roles to socket
  ↓
Policy.evaluate/3 (ABAC authorization)
  ↓
Admin LiveView rendered with role-based nav
```

## How to Re-seed

### Option 1: Run SQL File Directly
```bash
mysql -u root -pdataaegis123 < apps/wallet_database/priv/repo/seeds_dev_admin_users.sql
```

### Option 2: Via MySQL Client
```bash
mysql -u root -pdataaegis123 wallet_app_dev < apps/wallet_database/priv/repo/seeds_dev_admin_users.sql
```

### Option 3: Manual SQL (for single user)
```sql
USE wallet_app_dev;

-- Insert user
INSERT INTO users (
  user_id, email, display_name, status, tier, kyc_status,
  mfa_enabled, correlation_id, metadata, roles,
  inserted_at, updated_at
) VALUES (
  'usr_manual_test',
  'test@example.com',
  'Test User',
  'active',
  'standard',
  'approved',
  false,
  'corr_manual',
  JSON_OBJECT(),
  JSON_ARRAY('admin'),
  NOW(6),
  NOW(6)
);

-- Insert credential
INSERT INTO user_credentials (
  credential_id, user_id, credential_type, password_hash,
  last_changed_at, metadata, inserted_at, updated_at
) VALUES (
  'cred_manual_test',
  'usr_manual_test',
  'password',
  'pbkdf2_sha256$100000$...',  -- Get from: mix run -e 'IO.puts WalletAuth.Credentials.CredentialPolicy.hash("YourPassword")'
  NOW(6),
  JSON_OBJECT(),
  NOW(6),
  NOW(6)
);
```

## Verification Commands

### Check Seeded Users
```bash
mysql -u root -pdataaegis123 wallet_app_dev -e "
SELECT u.user_id, u.email, u.roles, u.status, c.credential_type
FROM users u
LEFT JOIN user_credentials c ON c.user_id = u.user_id
WHERE u.email LIKE '%@mercurypay.dev'
ORDER BY u.email;"
```

### Test Login Flow (Elixir)
```elixir
# From iex -S mix
alias WalletWeb.AdminAuth.CredentialLookup
alias WalletAuth.Commands.LoginWithPassword

# 1. Lookup credentials
{:ok, {user_id, hash}} = CredentialLookup.lookup_credentials("admin@mercurypay.dev")
# Returns: {:ok, {"usr_dev_admin", "pbkdf2_sha256$..."}}

# 2. Get roles
{:ok, roles} = CredentialLookup.get_roles("admin@mercurypay.dev")
# Returns: {:ok, ["admin"]}

# 3. Execute login
{:ok, session} = LoginWithPassword.execute("admin@mercurypay.dev", "AdminPass@1", [
  credential_lookup_fn: &CredentialLookup.lookup_credentials/1,
  token_opts: [roles: ["admin"]]
])
# Returns: session with JWT token
```

## DevSeeds vs Database

Both work together in this environment:

### DevSeeds (ETS In-Memory)
- **Status**: ✅ Active in dev/test (config: `enable_dev_seeds: true`)
- **Purpose**: Fallback when DB not available
- **Data**: Same 6 users, same passwords, same roles
- **Location**: `apps/wallet_web/lib/wallet_web/dev_seeds.ex`

### Database (MySQL)
- **Status**: ✅ Seeded successfully
- **Purpose**: Production-like persistence layer
- **Data**: Now matches DevSeeds exactly
- **Location**: `wallet_app_dev` database

### Priority (CredentialLookup)
1. **Database first** (if `WalletDatabase.Repo` is running)
2. **DevSeeds fallback** (if DB unavailable or query fails)

## Access Admin Portal

### 1. Start the Application
```bash
mix phx.server
```

### 2. Navigate to Admin Login
```
http://localhost:4000/admin/login
```

### 3. Login with Any Admin Account
- **Email**: `admin@mercurypay.dev`
- **Password**: `AdminPass@1`

### 4. Available Admin Routes
After successful login:
- `/admin/users` — User search
- `/admin/transactions` — Transaction inquiry
- `/admin/devices` — Device management
- `/admin/services` — Service controls
- `/admin/compliance/kyc` — KYC case review
- `/admin/compliance/aml` — AML alert review
- `/admin/compliance/sar` — SAR record management
- `/admin/compliance/exceptions` — Exception tracking
- `/admin/platform/health` — Health checks
- `/admin/platform/incidents` — Incident command
- `/admin/platform/slo` — SLO violation tracking
- `/admin/tenant` — Tenant configuration
- `/admin/policies` — Limit & fee policies

## Files Modified

1. `apps/wallet_database/lib/wallet_database/schemas/auth/user.ex` — Added `roles` field
2. `apps/wallet_database/priv/repo/migrations/20260325120000_add_roles_to_users.exs` — New migration
3. `apps/wallet_database/priv/repo/seeds_dev_admin_users.sql` — Seeding script (NEW)

## Next Steps

1. ✅ **Schema aligned** — User.roles matches CredentialLookup expectations
2. ✅ **Database seeded** — 6 admin users ready for testing
3. ✅ **Migration applied** — roles column added to users table
4. ⏭️ **Test admin portal** — Login and verify all 15 LiveViews work
5. ⏭️ **Run full test suite** — Ensure no regressions: `mix test`

---

**Generated**: 2026-03-25
**Phase**: 9C Complete (Back-Office Console MVP)
**Commit**: TBD (after verification)