# Phase 2: Backend Migration

## Overview

This phase removes external NHTSA vPIC API dependencies from the vehicles feature and integrates direct access to the MVP Platform database. All VIN decoding logic will be ported from Python to TypeScript while maintaining exact API compatibility.

## Prerequisites

- Phase 1 infrastructure completed successfully
- MVP Platform database running and accessible
- Existing Redis service available
- Backend service can connect to MVP Platform database
- Understanding of existing vehicles feature structure

## Current Architecture Analysis

**Files to Modify/Remove**:
- `backend/src/features/vehicles/external/vpic/` (entire directory - DELETE)
- `backend/src/features/vehicles/domain/vehicles.service.ts` (UPDATE)
- `backend/src/features/vehicles/api/vehicles.controller.ts` (UPDATE)
- `backend/src/core/config/environment.ts` (UPDATE)

**New Files to Create**:
- `backend/src/features/vehicles/data/mvp-platform.repository.ts`
- `backend/src/features/vehicles/domain/vin-decoder.service.ts`
- `backend/src/features/vehicles/data/vehicle-catalog.repository.ts`

## Tasks

### Task 2.1: Remove External vPIC API Dependencies

**Action**: Delete external API directory
```bash
rm -rf backend/src/features/vehicles/external/
```

**Location**: `backend/src/core/config/environment.ts`

**Action**: Remove VPIC_API_URL environment variable:

```typescript
// REMOVE this line:
// VPIC_API_URL: process.env.VPIC_API_URL || 'https://vpic.nhtsa.dot.gov/api/vehicles',

// ADD MVP Platform database configuration:
MVP_PLATFORM_DB_HOST: process.env.MVP_PLATFORM_DB_HOST || 'mvp-platform-database',
MVP_PLATFORM_DB_PORT: parseInt(process.env.MVP_PLATFORM_DB_PORT || '5432'),
MVP_PLATFORM_DB_NAME: process.env.MVP_PLATFORM_DB_NAME || 'mvp-platform-vehicles',
MVP_PLATFORM_DB_USER: process.env.MVP_PLATFORM_DB_USER || 'mvp_platform_user',
MVP_PLATFORM_DB_PASSWORD: process.env.MVP_PLATFORM_DB_PASSWORD || 'platform_dev_password',
```

### Task 2.2: Create MVP Platform Database Connection

**Location**: `backend/src/core/config/database.ts`

**Action**: Add MVP Platform database pool configuration:

```typescript
import { Pool } from 'pg';
import { env } from './environment';

// Existing main database pool
export const dbPool = new Pool({
  host: env.DB_HOST,
  port: env.DB_PORT,
  database: env.DB_NAME,
  user: env.DB_USER,
  password: env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
});

// NEW: MVP Platform database pool
export const mvpPlatformPool = new Pool({
  host: env.MVP_PLATFORM_DB_HOST,
  port: env.MVP_PLATFORM_DB_PORT,
  database: env.MVP_PLATFORM_DB_NAME,
  user: env.MVP_PLATFORM_DB_USER,
  password: env.MVP_PLATFORM_DB_PASSWORD,
  max: 10,
  idleTimeoutMillis: 30000,
});
```

### Task 2.3: Create MVP Platform Repository

**Location**: `backend/src/features/vehicles/data/mvp-platform.repository.ts`

**Action**: Create new file with the following content:

```typescript
import { mvpPlatformPool } from '../../../core/config/database';
import { logger } from '../../../core/logging/logger';

export interface VehicleDecodeResult {
  make?: string;
  model?: string;
  year?: number;
  engineType?: string;
  bodyType?: string;
  trim?: string;
  transmission?: string;
}

export interface DropdownItem {
  id: number;
  name: string;
}

export class MvpPlatformRepository {
  
  async decodeVIN(vin: string): Promise<VehicleDecodeResult | null> {
    try {
      const query = `
        SELECT 
          make_name as make,
          model_name as model,
          model_year as year,
          engine_type,
          body_type,
          trim_name as trim,
          transmission_type as transmission
        FROM vehicle_catalog 
        WHERE vin_pattern_matches($1)
        ORDER BY confidence_score DESC
        LIMIT 1
      `;
      
      const result = await mvpPlatformPool.query(query, [vin]);
      
      if (result.rows.length === 0) {
        logger.warn('VIN decode returned no results', { vin });
        return null;
      }
      
      const row = result.rows[0];
      return {
        make: row.make,
        model: row.model,
        year: row.year,
        engineType: row.engine_type,
        bodyType: row.body_type,
        trim: row.trim,
        transmission: row.transmission
      };
      
    } catch (error) {
      logger.error('VIN decode failed', { vin, error });
      return null;
    }
  }

  async getMakes(): Promise<DropdownItem[]> {
    try {
      const query = `
        SELECT DISTINCT 
          make_id as id,
          make_name as name
        FROM vehicle_catalog 
        WHERE make_name IS NOT NULL
        ORDER BY make_name
      `;
      
      const result = await mvpPlatformPool.query(query);
      return result.rows;
      
    } catch (error) {
      logger.error('Get makes failed', { error });
      return [];
    }
  }

  async getModelsForMake(make: string): Promise<DropdownItem[]> {
    try {
      const query = `
        SELECT DISTINCT 
          model_id as id,
          model_name as name
        FROM vehicle_catalog 
        WHERE LOWER(make_name) = LOWER($1)
        AND model_name IS NOT NULL
        ORDER BY model_name
      `;
      
      const result = await mvpPlatformPool.query(query, [make]);
      return result.rows;
      
    } catch (error) {
      logger.error('Get models failed', { make, error });
      return [];
    }
  }

  async getTransmissions(): Promise<DropdownItem[]> {
    try {
      const query = `
        SELECT DISTINCT 
          ROW_NUMBER() OVER (ORDER BY transmission_type) as id,
          transmission_type as name
        FROM vehicle_catalog 
        WHERE transmission_type IS NOT NULL
        ORDER BY transmission_type
      `;
      
      const result = await mvpPlatformPool.query(query);
      return result.rows;
      
    } catch (error) {
      logger.error('Get transmissions failed', { error });
      return [];
    }
  }

  async getEngines(): Promise<DropdownItem[]> {
    try {
      const query = `
        SELECT DISTINCT 
          ROW_NUMBER() OVER (ORDER BY engine_type) as id,
          engine_type as name
        FROM vehicle_catalog 
        WHERE engine_type IS NOT NULL
        ORDER BY engine_type
      `;
      
      const result = await mvpPlatformPool.query(query);
      return result.rows;
      
    } catch (error) {
      logger.error('Get engines failed', { error });
      return [];
    }
  }

  async getTrims(): Promise<DropdownItem[]> {
    try {
      const query = `
        SELECT DISTINCT 
          ROW_NUMBER() OVER (ORDER BY trim_name) as id,
          trim_name as name
        FROM vehicle_catalog 
        WHERE trim_name IS NOT NULL
        ORDER BY trim_name
      `;
      
      const result = await mvpPlatformPool.query(query);
      return result.rows;
      
    } catch (error) {
      logger.error('Get trims failed', { error });
      return [];
    }
  }
}

export const mvpPlatformRepository = new MvpPlatformRepository();
```

### Task 2.4: Create VIN Decoder Service

**Location**: `backend/src/features/vehicles/domain/vin-decoder.service.ts`

**Action**: Create new file with TypeScript port of VIN decoding logic:

```typescript
import { logger } from '../../../core/logging/logger';
import { cacheService } from '../../../core/config/redis';
import { mvpPlatformRepository, VehicleDecodeResult } from '../data/mvp-platform.repository';

export class VinDecoderService {
  private readonly cachePrefix = 'mvp-platform';
  private readonly vinCacheTTL = 30 * 24 * 60 * 60; // 30 days

  async decodeVIN(vin: string): Promise<VehicleDecodeResult | null> {
    // Validate VIN format
    if (!this.isValidVIN(vin)) {
      logger.warn('Invalid VIN format', { vin });
      return null;
    }

    // Check cache first
    const cacheKey = `${this.cachePrefix}:vin:${vin}`;
    const cached = await cacheService.get<VehicleDecodeResult>(cacheKey);
    if (cached) {
      logger.debug('VIN decode cache hit', { vin });
      return cached;
    }

    // Decode VIN using MVP Platform database
    logger.info('Decoding VIN via MVP Platform database', { vin });
    const result = await mvpPlatformRepository.decodeVIN(vin);

    // Cache successful results
    if (result) {
      await cacheService.set(cacheKey, result, this.vinCacheTTL);
    }

    return result;
  }

  private isValidVIN(vin: string): boolean {
    // Basic VIN validation
    if (!vin || vin.length !== 17) {
      return false;
    }

    // Check for invalid characters (I, O, Q not allowed)
    const invalidChars = /[IOQ]/gi;
    if (invalidChars.test(vin)) {
      return false;
    }

    return true;
  }

  // Extract model year from VIN (positions 10 and 7)
  extractModelYear(vin: string, currentYear: number = new Date().getFullYear()): number[] {
    if (!this.isValidVIN(vin)) {
      return [];
    }

    const yearChar = vin.charAt(9); // Position 10 (0-indexed)
    const seventhChar = vin.charAt(6); // Position 7 (0-indexed)

    // Year code mapping
    const yearCodes: { [key: string]: number[] } = {
      'A': [2010, 1980], 'B': [2011, 1981], 'C': [2012, 1982], 'D': [2013, 1983],
      'E': [2014, 1984], 'F': [2015, 1985], 'G': [2016, 1986], 'H': [2017, 1987],
      'J': [2018, 1988], 'K': [2019, 1989], 'L': [2020, 1990], 'M': [2021, 1991],
      'N': [2022, 1992], 'P': [2023, 1993], 'R': [2024, 1994], 'S': [2025, 1995],
      'T': [2026, 1996], 'V': [2027, 1997], 'W': [2028, 1998], 'X': [2029, 1999],
      'Y': [2030, 2000], '1': [2031, 2001], '2': [2032, 2002], '3': [2033, 2003],
      '4': [2034, 2004], '5': [2035, 2005], '6': [2036, 2006], '7': [2037, 2007],
      '8': [2038, 2008], '9': [2039, 2009]
    };

    const possibleYears = yearCodes[yearChar.toUpperCase()];
    if (!possibleYears) {
      return [];
    }

    // Use 7th character for disambiguation if numeric (older cycle)
    if (/\d/.test(seventhChar)) {
      return [possibleYears[1]]; // Older year
    } else {
      return [possibleYears[0]]; // Newer year
    }
  }
}

export const vinDecoderService = new VinDecoderService();
```

### Task 2.5: Update Vehicles Service

**Location**: `backend/src/features/vehicles/domain/vehicles.service.ts`

**Action**: Replace external API calls with MVP Platform database calls:

```typescript
// REMOVE these imports:
// import { vpicClient } from '../external/vpic/vpic.client';

// ADD these imports:
import { vinDecoderService } from './vin-decoder.service';
import { mvpPlatformRepository } from '../data/mvp-platform.repository';

// In the createVehicle method, REPLACE:
// const vinData = await vpicClient.decodeVIN(data.vin);

// WITH:
const vinData = await vinDecoderService.decodeVIN(data.vin);

// Add new dropdown methods to the VehiclesService class:
async getDropdownMakes(): Promise<any[]> {
  const cacheKey = `${this.cachePrefix}:dropdown:makes`;
  
  try {
    const cached = await cacheService.get<any[]>(cacheKey);
    if (cached) {
      logger.debug('Makes dropdown cache hit');
      return cached;
    }

    logger.info('Fetching makes from MVP Platform database');
    const makes = await mvpPlatformRepository.getMakes();
    
    // Cache for 7 days
    await cacheService.set(cacheKey, makes, 7 * 24 * 60 * 60);
    return makes;
    
  } catch (error) {
    logger.error('Get dropdown makes failed', { error });
    return [];
  }
}

async getDropdownModels(make: string): Promise<any[]> {
  const cacheKey = `${this.cachePrefix}:dropdown:models:${make}`;
  
  try {
    const cached = await cacheService.get<any[]>(cacheKey);
    if (cached) {
      logger.debug('Models dropdown cache hit', { make });
      return cached;
    }

    logger.info('Fetching models from MVP Platform database', { make });
    const models = await mvpPlatformRepository.getModelsForMake(make);
    
    // Cache for 7 days  
    await cacheService.set(cacheKey, models, 7 * 24 * 60 * 60);
    return models;
    
  } catch (error) {
    logger.error('Get dropdown models failed', { make, error });
    return [];
  }
}

async getDropdownTransmissions(): Promise<any[]> {
  const cacheKey = `${this.cachePrefix}:dropdown:transmissions`;
  
  try {
    const cached = await cacheService.get<any[]>(cacheKey);
    if (cached) {
      logger.debug('Transmissions dropdown cache hit');
      return cached;
    }

    logger.info('Fetching transmissions from MVP Platform database');
    const transmissions = await mvpPlatformRepository.getTransmissions();
    
    // Cache for 7 days
    await cacheService.set(cacheKey, transmissions, 7 * 24 * 60 * 60);
    return transmissions;
    
  } catch (error) {
    logger.error('Get dropdown transmissions failed', { error });
    return [];
  }
}

async getDropdownEngines(): Promise<any[]> {
  const cacheKey = `${this.cachePrefix}:dropdown:engines`;
  
  try {
    const cached = await cacheService.get<any[]>(cacheKey);
    if (cached) {
      logger.debug('Engines dropdown cache hit');
      return cached;
    }

    logger.info('Fetching engines from MVP Platform database');
    const engines = await mvpPlatformRepository.getEngines();
    
    // Cache for 7 days
    await cacheService.set(cacheKey, engines, 7 * 24 * 60 * 60);
    return engines;
    
  } catch (error) {
    logger.error('Get dropdown engines failed', { error });
    return [];
  }
}

async getDropdownTrims(): Promise<any[]> {
  const cacheKey = `${this.cachePrefix}:dropdown:trims`;
  
  try {
    const cached = await cacheService.get<any[]>(cacheKey);
    if (cached) {
      logger.debug('Trims dropdown cache hit');
      return cached;
    }

    logger.info('Fetching trims from MVP Platform database');
    const trims = await mvpPlatformRepository.getTrims();
    
    // Cache for 7 days
    await cacheService.set(cacheKey, trims, 7 * 24 * 60 * 60);
    return trims;
    
  } catch (error) {
    logger.error('Get dropdown trims failed', { error });
    return [];
  }
}
```

### Task 2.6: Update Cache Key Patterns

**Action**: Update all existing cache keys to use MVP Platform prefix

In vehicles.service.ts, UPDATE:
```typescript
// CHANGE:
private readonly cachePrefix = 'vehicles';

// TO:
private readonly cachePrefix = 'mvp-platform:vehicles';
```

## Validation Steps

### Step 1: Compile TypeScript

```bash
# From backend directory
cd backend
npm run build

# Should compile without errors
```

### Step 2: Test Database Connections

```bash
# Test MVP Platform database connection
docker-compose exec backend node -e "
const { mvpPlatformPool } = require('./dist/core/config/database');
mvpPlatformPool.query('SELECT 1 as test')
  .then(r => console.log('MVP Platform DB:', r.rows[0]))
  .catch(e => console.error('Error:', e));
"
```

### Step 3: Test VIN Decoder Service

```bash
# Test VIN decoding functionality
docker-compose exec backend node -e "
const { vinDecoderService } = require('./dist/features/vehicles/domain/vin-decoder.service');
vinDecoderService.decodeVIN('1HGBH41JXMN109186')
  .then(r => console.log('VIN decode result:', r))
  .catch(e => console.error('Error:', e));
"
```

### Step 4: Verify Import Statements

Check that all imports are resolved correctly:

```bash
# Check for any remaining vpic imports
grep -r "vpic" backend/src/features/vehicles/ || echo "No vpic references found"

# Check for MVP Platform imports
grep -r "mvp-platform" backend/src/features/vehicles/ | head -5
```

## Error Handling

### Common Issues and Solutions

**Issue**: TypeScript compilation errors
**Solution**: Check import paths, verify all referenced modules exist

**Issue**: Database connection failures  
**Solution**: Verify MVP Platform database is running, check connection parameters

**Issue**: Missing external directory references
**Solution**: Update any remaining imports from deleted external/vpic directory

### Rollback Procedure

1. Restore external/vpic directory from git:
   ```bash
   git checkout HEAD -- backend/src/features/vehicles/external/
   ```

2. Revert vehicles.service.ts changes:
   ```bash
   git checkout HEAD -- backend/src/features/vehicles/domain/vehicles.service.ts
   ```

3. Remove new files:
   ```bash
   rm backend/src/features/vehicles/data/mvp-platform.repository.ts
   rm backend/src/features/vehicles/domain/vin-decoder.service.ts
   ```

4. Revert environment.ts changes:
   ```bash
   git checkout HEAD -- backend/src/core/config/environment.ts
   ```

## Next Steps

After successful completion of Phase 2:

1. Proceed to [Phase 3: API Migration](./phase-03-api-migration.md)
2. Test VIN decoding functionality thoroughly
3. Monitor performance of new database queries

## Dependencies for Next Phase

- All backend changes compiled successfully
- MVP Platform database queries working correctly
- VIN decoder service functional
- Cache keys updated to new pattern