feat: RetroAchievements API Integration

# RetroAchievements Integration Plan for gamefaqs-server

## Overview

Add RetroAchievements (RA) API integration to enable:
1. User authentication from mobile app using RA credentials
2. Fetching game info and achievements when a guide is opened
3. Auto-generating bookmarks based on achievement descriptions matched to guide content

## Architecture Decision: Pass-Through Authentication

The server will act as a **proxy** for RA API calls. User credentials (username + API key) are passed with each request from the mobile app - the server does NOT store credentials. This approach:
- Avoids storing sensitive credentials on the server
- Simplifies implementation (no session management)
- Lets users manage their own RA API keys
- Each request includes `x-ra-username` and `x-ra-apikey` headers

## New Files to Create

### 1. `src/services/RetroAchievementsService.ts`
Core service for RA API interactions:
- `validateCredentials(username, apiKey)` - Verify credentials work
- `getGameInfo(gameId, username, apiKey)` - Get game details + achievements
- `getGameInfoExtended(gameId, username, apiKey)` - Full game data with all achievements
- `getUserProgress(gameId, username, apiKey)` - User's unlock status for a game
- `searchGames(query, username, apiKey)` - Search RA games by title
- `getGameByHash(hash, username, apiKey)` - Lookup game by ROM hash (future use)

Caching: In-memory cache for game/achievement data (TTL: 1 hour) to reduce RA API calls.

### 2. `src/services/BookmarkGeneratorService.ts`
Strict matching algorithm to generate bookmarks from achievements:
- `generateBookmarks(guideId, achievements)` - Main entry point
- `findAchievementMentions(guideContent, achievement)` - Find where achievement is mentioned
- `scoreMatch(guideText, achievementTitle, achievementDescription)` - Confidence scoring

**Matching Strategy (Strict):**
1. Exact title match (case-insensitive) → High confidence
2. Significant keyword overlap (>60% of achievement keywords in surrounding text) → Medium confidence
3. Description phrase match (3+ consecutive words) → Medium confidence
4. Return only matches above threshold (configurable, default: 0.7)

Returns: Array of `{ achievement, position, confidence, snippet }` - only high-confidence matches.

### 3. `src/routes/ra.ts`
New router for all RA-related endpoints:

```
POST /api/ra/validate - Validate RA credentials
GET /api/ra/games/:raGameId - Get RA game info + achievements
GET /api/ra/games/:raGameId/progress - Get user's progress for game
GET /api/ra/search?q= - Search RA games by title
POST /api/ra/guides/:guideId/generate-bookmarks - Generate achievement bookmarks
```

### 4. `src/types/retroachievements.ts`
Type definitions for RA API responses:
- `RAGameInfo`, `RAAchievement`, `RAUserProgress`
- `RACredentials`, `RAValidationResult`
- `BookmarkSuggestion`, `GenerateBookmarksResult`

### 5. `src/middleware/raAuth.ts`
Middleware to extract and validate RA credentials from request headers:
- Extracts `x-ra-username` and `x-ra-apikey` headers
- Attaches to `req.raCredentials`
- Returns 401 if headers missing

## Files to Modify

### 1. `src/server.ts`
- Import and mount the new RA router: `app.use('/api/ra', raRouter)`

### 2. `src/config.ts`
Add new config options:
```typescript
// RetroAchievements
raApiBaseUrl: 'https://retroachievements.org/API',
raCacheTtlMs: 60 * 60 * 1000, // 1 hour cache TTL
raRequestTimeoutMs: 10000, // 10 second timeout
```

### 3. `src/types/index.ts`
Export the new RA types from `retroachievements.ts`

### 4. `src/models/Game.ts`
Add method: `findByRaGameId(raGameId: string)` - Find local game by RA ID

### 5. `src/models/Achievement.ts` (new file or enhance existing)
Add methods for upserting achievements from RA API:
- `upsertFromRA(gameId, raAchievements[])` - Bulk upsert achievements
- `findByGameId(gameId)` - Get all achievements for a game
- `updateUserProgress(gameId, unlocks[])` - Update unlock status

## Database Changes

**No schema changes required.** The existing schema already has:
- `games.ra_game_id` for linking to RA
- `achievements` table with all needed fields
- `bookmarks` table ready for generated bookmarks

## API Endpoint Details

### `POST /api/ra/validate`
Validate user's RA credentials.

**Headers:** `x-ra-username`, `x-ra-apikey`

**Response:**
```json
{
"valid": true,
"user": {
"username": "player123",
"totalPoints": 5000,
"rank": 1234
}
}
```

### `GET /api/ra/games/:raGameId`
Get game info and achievements from RA.

**Headers:** `x-ra-username`, `x-ra-apikey`

**Response:**
```json
{
"data": {
"id": 1234,
"title": "Super Mario Bros.",
"console": "NES",
"imageIcon": "/Images/000001.png",
"achievements": [
{
"id": 5678,
"title": "World 1-1",
"description": "Complete World 1-1",
"points": 5,
"badgeUrl": "..."
}
],
"numAchievements": 25,
"totalPoints": 400
}
}
```

### `GET /api/ra/games/:raGameId/progress`
Get user's achievement progress for a game.

**Headers:** `x-ra-username`, `x-ra-apikey`

**Response:**
```json
{
"data": {
"gameId": 1234,
"numAwarded": 10,
"numAchievements": 25,
"completionPercentage": 40,
"achievements": [
{
"id": 5678,
"dateAwarded": "2024-01-15T12:00:00Z",
"hardcoreAwarded": true
}
]
}
}
```

### `GET /api/ra/search?q=mario`
Search RA games by title.

**Headers:** `x-ra-username`, `x-ra-apikey`

**Response:**
```json
{
"data": [
{
"id": 1234,
"title": "Super Mario Bros.",
"console": "NES",
"imageIcon": "/Images/000001.png",
"numAchievements": 25
}
]
}
```

### `POST /api/ra/guides/:guideId/generate-bookmarks`
Generate bookmarks based on achievement matching. **This is the key feature.**

**Headers:** `x-ra-username`, `x-ra-apikey`

**Request Body:**
```json
{
"raGameId": 1234,
"minConfidence": 0.7,
"save": false
}
```

**Response:**
```json
{
"data": {
"guideId": "abc123",
"raGameId": 1234,
"totalAchievements": 25,
"matchedAchievements": 8,
"suggestions": [
{
"achievementId": 5678,
"achievementTitle": "World 1-1",
"position": 12450,
"confidence": 0.92,
"snippet": "...complete World 1-1 by reaching the flagpole...",
"matchReason": "exact_title_match"
}
],
"savedBookmarks": []
}
}
```

If `save: true`, also creates bookmarks and returns them in `savedBookmarks`.

## Matching Algorithm Details

The bookmark generator uses a **strict, multi-pass** approach:

### Pass 1: Exact Title Match
- Search for achievement title as exact phrase (case-insensitive)
- If found, confidence = 0.95
- Record position and extract snippet (±100 chars)

### Pass 2: Keyword Density Match
- Extract significant keywords from achievement title (ignore common words: "the", "a", "get", "complete", etc.)
- Scan guide in sliding windows (500 chars)
- Calculate keyword density per window
- If >60% of keywords present in window → confidence = 0.75

### Pass 3: Description Phrase Match
- Extract 3-word phrases from achievement description
- Search for these phrases in guide content
- If phrase found → confidence = 0.70

### Filtering
- Only return matches with confidence >= `minConfidence` (default 0.7)
- Deduplicate overlapping positions (keep highest confidence)
- Sort by position in guide

### What Makes This "Strict"
- **No fuzzy matching** - We don't use Levenshtein distance or similar
- **High thresholds** - 60% keyword overlap minimum, 0.7 confidence floor
- **Multiple signals required** - For medium confidence, need meaningful overlap
- **Graceful failure** - Returns empty array if no good matches (not forced matches)

## Implementation Order

1. **Types first:** Create `src/types/retroachievements.ts`
2. **Config:** Update `src/config.ts` with RA settings
3. **RA Service:** Implement `RetroAchievementsService.ts` with API calls + caching
4. **Auth middleware:** Create `src/middleware/raAuth.ts`
5. **Achievement model:** Create/update `src/models/Achievement.ts`
6. **Bookmark generator:** Implement `BookmarkGeneratorService.ts`
7. **Router:** Create `src/routes/ra.ts` with all endpoints
8. **Wire up:** Update `src/server.ts` to mount the router
9. **Tests:** Add integration tests for RA endpoints

## Error Handling

- RA API errors → 502 Bad Gateway with error details
- Invalid credentials → 401 Unauthorized
- Guide not found → 404 Not Found
- Game not linked to RA → 400 Bad Request with message
- Rate limiting from RA → 429 Too Many Requests (pass through)

## Caching Strategy

In-memory cache with Map:
- Key: `game:${raGameId}` or `achievements:${raGameId}`
- TTL: 1 hour (configurable)
- Cache invalidation: Manual via admin endpoint or TTL expiry
- User progress NOT cached (always fresh)

## Verification Plan

1. **Unit tests:** Test BookmarkGeneratorService matching algorithm with sample data
2. **Integration tests:** Test RA router endpoints with mocked RA API
3. **Manual testing:**
- Use real RA credentials to validate auth flow
- Pick a guide with known game, verify achievement fetch
- Test bookmark generation on a walkthrough guide
- Verify strict matching rejects low-quality matches

## Files Summary

| File | Action | Purpose |
|------|--------|---------|
| `src/types/retroachievements.ts` | Create | RA type definitions |
| `src/config.ts` | Modify | Add RA config options |
| `src/services/RetroAchievementsService.ts` | Create | RA API client + caching |
| `src/middleware/raAuth.ts` | Create | Extract RA credentials from headers |
| `src/models/Achievement.ts` | Create | Achievement CRUD operations |
| `src/services/BookmarkGeneratorService.ts` | Create | Strict matching algorithm |
| `src/routes/ra.ts` | Create | RA API endpoints |
| `src/server.ts` | Modify | Mount RA router |
| `src/types/index.ts` | Modify | Export RA types |
| `src/models/Game.ts` | Modify | Add findByRaGameId method |

Start by reading the existing patterns in src/server.ts, src/config.ts, src/routes, src/services, and src/models, then inspect the current database-backed Game and achievement structures. Implement the files and endpoint wiring listed in the issue, and verify completion with unit tests for BookmarkGeneratorService plus mocked integration tests for the RA routes.

Written by the indexing model from the issue text.