import { NextRequest, NextResponse } from 'next/server'

interface GameDbTestRequest {
  host: string
  port: number
  database: string
  user: string
  password: string
  framework: 'auto' | 'esx' | 'qbcore'
}

// ESX table signatures
const ESX_TABLES = ['users', 'items', 'jobs', 'job_grades', 'owned_vehicles', 'owned_properties']
const ESX_USER_COLUMNS = ['identifier', 'accounts', 'job', 'job_grade', 'firstname', 'lastname']

// QBCore table signatures  
const QBCORE_TABLES = ['players', 'player_vehicles', 'player_houses', 'player_contacts', 'apartments']
const QBCORE_PLAYER_COLUMNS = ['citizenid', 'charinfo', 'money', 'job', 'gang', 'metadata']

export async function POST(request: NextRequest) {
  try {
    const body: GameDbTestRequest = await request.json()
    const { host, port, database, user, password, framework } = body

    if (!host || !database || !user) {
      return NextResponse.json(
        { success: false, error: 'Missing required fields' },
        { status: 400 }
      )
    }

    // Validate host format
    const hostRegex = /^[a-zA-Z0-9.-]+$/
    if (!hostRegex.test(host)) {
      return NextResponse.json(
        { success: false, error: 'Invalid host format' },
        { status: 400 }
      )
    }

    // Validate port
    const portNum = port || 3306
    if (portNum < 1 || portNum > 65535) {
      return NextResponse.json(
        { success: false, error: 'Invalid port number' },
        { status: 400 }
      )
    }

    try {
      const mysql = await import('mysql2/promise')
      const connection = await mysql.createConnection({
        host,
        port: portNum,
        user,
        password: password || '',
        database,
        connectTimeout: 10000,
      })
      
      // Test connection
      await connection.ping()
      
      // Get all tables in the database
      const [tables] = await connection.query<mysql.RowDataPacket[]>(
        `SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?`,
        [database]
      )
      
      const tableNames = tables.map((row) => row.TABLE_NAME.toLowerCase())
      
      let detectedFramework: 'esx' | 'qbcore' | null = null
      let frameworkDetails: Record<string, unknown> = {}
      
      // Auto-detect or verify framework
      if (framework === 'auto' || framework === 'esx') {
        // Check for ESX tables
        const esxTablesFound = ESX_TABLES.filter(t => tableNames.includes(t))
        
        if (esxTablesFound.length >= 3) {
          // Verify ESX user table structure
          try {
            const [columns] = await connection.query<mysql.RowDataPacket[]>(
              `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users'`,
              [database]
            )
            const columnNames = columns.map((row) => row.COLUMN_NAME.toLowerCase())
            const esxColumnsFound = ESX_USER_COLUMNS.filter(c => columnNames.includes(c))
            
            if (esxColumnsFound.length >= 4) {
              detectedFramework = 'esx'
              
              // Get player count
              const [countResult] = await connection.query<mysql.RowDataPacket[]>(
                `SELECT COUNT(*) as count FROM users`
              )
              
              frameworkDetails = {
                tablesFound: esxTablesFound,
                columnsVerified: esxColumnsFound,
                playerCount: countResult[0]?.count || 0,
              }
            }
          } catch {
            // users table might not exist or have different structure
          }
        }
      }
      
      if (!detectedFramework && (framework === 'auto' || framework === 'qbcore')) {
        // Check for QBCore tables
        const qbcoreTablesFound = QBCORE_TABLES.filter(t => tableNames.includes(t))
        
        if (qbcoreTablesFound.length >= 2) {
          // Verify QBCore player table structure
          try {
            const [columns] = await connection.query<mysql.RowDataPacket[]>(
              `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'players'`,
              [database]
            )
            const columnNames = columns.map((row) => row.COLUMN_NAME.toLowerCase())
            const qbcoreColumnsFound = QBCORE_PLAYER_COLUMNS.filter(c => columnNames.includes(c))
            
            if (qbcoreColumnsFound.length >= 3) {
              detectedFramework = 'qbcore'
              
              // Get player count
              const [countResult] = await connection.query<mysql.RowDataPacket[]>(
                `SELECT COUNT(*) as count FROM players`
              )
              
              frameworkDetails = {
                tablesFound: qbcoreTablesFound,
                columnsVerified: qbcoreColumnsFound,
                playerCount: countResult[0]?.count || 0,
              }
            }
          } catch {
            // players table might not exist or have different structure
          }
        }
      }
      
      await connection.end()
      
      if (!detectedFramework) {
        // If forced framework but not detected
        if (framework !== 'auto') {
          return NextResponse.json({
            success: false,
            error: `Could not verify ${framework.toUpperCase()} tables in database. Found tables: ${tableNames.slice(0, 10).join(', ')}${tableNames.length > 10 ? '...' : ''}`,
          }, { status: 400 })
        }
        
        return NextResponse.json({
          success: false,
          error: `Could not detect ESX or QBCore framework. Found tables: ${tableNames.slice(0, 10).join(', ')}${tableNames.length > 10 ? '...' : ''}. Make sure you are connecting to the correct FiveM database.`,
        }, { status: 400 })
      }
      
      return NextResponse.json({
        success: true,
        framework: detectedFramework,
        details: frameworkDetails,
        tables: tableNames.slice(0, 20),
      })
      
    } catch (dbError) {
      const errorMessage = dbError instanceof Error ? dbError.message : 'Connection failed'
      
      if (errorMessage.includes('ECONNREFUSED')) {
        return NextResponse.json(
          { success: false, error: 'Connection refused. Is MySQL running on the FiveM server?' },
          { status: 400 }
        )
      }
      
      if (errorMessage.includes('Access denied')) {
        return NextResponse.json(
          { success: false, error: 'Access denied. Check game database username and password.' },
          { status: 400 }
        )
      }
      
      if (errorMessage.includes('Unknown database')) {
        return NextResponse.json(
          { success: false, error: `Database '${database}' does not exist. Check the database name.` },
          { status: 400 }
        )
      }

      if (errorMessage.includes('ETIMEDOUT') || errorMessage.includes('ENOTFOUND')) {
        return NextResponse.json(
          { success: false, error: 'Could not reach database server. Check host and port.' },
          { status: 400 }
        )
      }

      // For demo mode when mysql2 isn't available
      if (errorMessage.includes('Cannot find module')) {
        console.log('[Installer] mysql2 not available, simulating game database test')
        return NextResponse.json({
          success: true,
          framework: framework === 'auto' ? 'esx' : framework,
          details: { playerCount: 0, simulated: true },
        })
      }

      return NextResponse.json(
        { success: false, error: errorMessage },
        { status: 400 }
      )
    }
  } catch (error) {
    console.error('[Installer] Game database test error:', error)
    return NextResponse.json(
      { success: false, error: 'Failed to test FiveM database connection' },
      { status: 500 }
    )
  }
}