import mysql from 'mysql2/promise'
import { prisma } from './db'

// Cached connection pool
let gameDbPool: mysql.Pool | null = null

export interface GameDbConfig {
  host: string
  port: number
  database: string
  user: string
  password: string
  framework: 'ESX' | 'QBCORE'
}

/**
 * Get the game database configuration from the database
 */
export async function getGameDbConfig(): Promise<GameDbConfig | null> {
  try {
    const config = await prisma.gameDatabaseConfig.findFirst({
      where: { isActive: true },
    })

    if (!config) {
      return null
    }

    // Always auto-detect framework based on database content and name
    let framework: 'ESX' | 'QBCORE' = 'ESX'
    
    // Step 1: Check database name for hints
    const dbNameLower = config.database.toLowerCase()
    if (dbNameLower.includes('qbcore') || dbNameLower.includes('qb-core') || dbNameLower.includes('qb_core') || dbNameLower.includes('qb')) {
      framework = 'QBCORE'
    } else if (dbNameLower.includes('esx') || dbNameLower.includes('es_extended')) {
      framework = 'ESX'
    } else {
      // Step 2: Auto-detect by checking which tables exist
      framework = await autoDetectFramework(config)
    }

    return {
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      framework: framework,
    }
  } catch (error) {
    console.error('[GameDB] Failed to get config:', error)
    return null
  }
}

// Auto-detect framework by checking which tables exist
async function autoDetectFramework(config: { host: string; port: number; database: string; user: string; password: string }): Promise<'ESX' | 'QBCORE'> {
  let tempPool: mysql.Pool | null = null
  try {
    tempPool = mysql.createPool({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      database: config.database,
      connectionLimit: 1,
    })
    
    // Check for QBCore players table with citizenid column
    try {
      const [qbRows] = await tempPool.query<mysql.RowDataPacket[]>(
        "SELECT citizenid FROM players LIMIT 1"
      )
      if (qbRows !== undefined) {
        await tempPool.end()
        return 'QBCORE'
      }
    } catch {
      // Table or column doesn't exist
    }
    
    // Check for ESX users table with identifier column
    try {
      const [esxRows] = await tempPool.query<mysql.RowDataPacket[]>(
        "SELECT identifier FROM users LIMIT 1"
      )
      if (esxRows !== undefined) {
        await tempPool.end()
        return 'ESX'
      }
    } catch {
      // Table or column doesn't exist
    }
    
    if (tempPool) await tempPool.end()
  } catch {
    if (tempPool) await tempPool.end().catch(() => {})
  }
  
  return 'ESX'
}

/**
 * Get or create a connection pool to the game database
 */
export async function getGameDbPool(): Promise<mysql.Pool | null> {
  if (gameDbPool) return gameDbPool

  const config = await getGameDbConfig()
  if (!config) return null

  try {
    gameDbPool = mysql.createPool({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      database: config.database,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      enableKeepAlive: true,
      keepAliveInitialDelay: 0,
    })

    return gameDbPool
  } catch (error) {
    console.error('[GameDB] Failed to create pool:', error)
    return null
  }
}

/**
 * Execute a query on the game database
 */
export async function queryGameDb<T = mysql.RowDataPacket[]>(
  sql: string,
  params?: unknown[]
): Promise<T> {
  const pool = await getGameDbPool()
  if (!pool) {
    throw new Error('Game database not configured')
  }

  const [rows] = await pool.query<mysql.RowDataPacket[]>(sql, params)
  return rows as T
}

/**
 * Get player data from ESX
 */
export async function getEsxPlayers(limit = 100, offset = 0) {
  const pool = await getGameDbPool()
  if (!pool) return []

  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `SELECT 
      identifier,
      accounts,
      job,
      job_grade,
      firstname,
      lastname,
      dateofbirth,
      sex,
      height,
      phone_number,
      loadout,
      position,
      COALESCE(last_property, '') as last_property
    FROM users
    ORDER BY identifier
    LIMIT ? OFFSET ?`,
    [limit, offset]
  )

  return rows.map((row) => ({
    identifier: row.identifier,
    name: `${row.firstname || ''} ${row.lastname || ''}`.trim() || 'Unknown',
    accounts: typeof row.accounts === 'string' ? JSON.parse(row.accounts) : row.accounts,
    job: row.job,
    jobGrade: row.job_grade,
    dateOfBirth: row.dateofbirth,
    sex: row.sex,
    height: row.height,
    phone: row.phone_number,
    position: typeof row.position === 'string' ? JSON.parse(row.position) : row.position,
  }))
}

/**
 * Get player data from QBCore
 */
export async function getQBCorePlayers(limit = 100, offset = 0) {
  const pool = await getGameDbPool()
  if (!pool) return []

  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `SELECT 
      citizenid,
      license,
      name,
      money,
      charinfo,
      job,
      gang,
      metadata,
      last_updated
    FROM players
    ORDER BY citizenid
    LIMIT ? OFFSET ?`,
    [limit, offset]
  )

  return rows.map((row) => {
    const charinfo = typeof row.charinfo === 'string' ? JSON.parse(row.charinfo) : row.charinfo
    const money = typeof row.money === 'string' ? JSON.parse(row.money) : row.money
    const job = typeof row.job === 'string' ? JSON.parse(row.job) : row.job
    const gang = typeof row.gang === 'string' ? JSON.parse(row.gang) : row.gang
    const metadata = typeof row.metadata === 'string' ? JSON.parse(row.metadata) : row.metadata

    return {
      citizenid: row.citizenid,
      license: row.license,
      name: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim() || row.name,
      money: {
        cash: money?.cash || 0,
        bank: money?.bank || 0,
        crypto: money?.crypto || 0,
      },
      job: {
        name: job?.name || 'unemployed',
        label: job?.label || 'Unemployed',
        grade: job?.grade?.level || 0,
        gradeLabel: job?.grade?.name || '',
      },
      gang: {
        name: gang?.name || 'none',
        label: gang?.label || 'No Gang',
        grade: gang?.grade?.level || 0,
      },
      charinfo: {
        firstname: charinfo?.firstname,
        lastname: charinfo?.lastname,
        birthdate: charinfo?.birthdate,
        gender: charinfo?.gender,
        nationality: charinfo?.nationality,
        phone: charinfo?.phone,
      },
      metadata: {
        hunger: metadata?.hunger || 100,
        thirst: metadata?.thirst || 100,
        stress: metadata?.stress || 0,
        armor: metadata?.armor || 0,
        isHandcuffed: metadata?.ishandcuffed || false,
      },
      lastUpdated: row.last_updated,
    }
  })
}

/**
 * Get player count
 */
export async function getPlayerCount(): Promise<number> {
  const config = await getGameDbConfig()
  if (!config) return 0

  const pool = await getGameDbPool()
  if (!pool) return 0

  const table = config.framework === 'QBCORE' ? 'players' : 'users'
  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `SELECT COUNT(*) as count FROM ${table}`
  )

  return rows[0]?.count || 0
}

/**
 * Get vehicles for ESX
 */
export async function getEsxVehicles(identifier: string) {
  const pool = await getGameDbPool()
  if (!pool) return []

  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `SELECT * FROM owned_vehicles WHERE owner = ?`,
    [identifier]
  )

  return rows.map((row) => ({
    plate: row.plate,
    vehicle: typeof row.vehicle === 'string' ? JSON.parse(row.vehicle) : row.vehicle,
    stored: row.stored === 1,
    garage: row.garage,
  }))
}

/**
 * Get vehicles for QBCore
 */
export async function getQBCoreVehicles(citizenid: string) {
  const pool = await getGameDbPool()
  if (!pool) return []

  const [rows] = await pool.query<mysql.RowDataPacket[]>(
    `SELECT * FROM player_vehicles WHERE citizenid = ?`,
    [citizenid]
  )

  return rows.map((row) => ({
    plate: row.plate,
    vehicle: row.vehicle,
    mods: typeof row.mods === 'string' ? JSON.parse(row.mods) : row.mods,
    stored: row.state === 1 || row.state === 'stored',
    garage: row.garage,
    fuel: row.fuel,
    engine: row.engine,
    body: row.body,
  }))
}

/**
 * Search players by name or identifier
 */
export async function searchPlayers(query: string, limit = 20) {
  const config = await getGameDbConfig()
  if (!config) return []

  const pool = await getGameDbPool()
  if (!pool) return []

  const searchPattern = `%${query}%`

  if (config.framework === 'QBCORE') {
    const [rows] = await pool.query<mysql.RowDataPacket[]>(
      `SELECT citizenid, name, charinfo, job, money
       FROM players
       WHERE citizenid LIKE ? OR name LIKE ? OR JSON_EXTRACT(charinfo, '$.firstname') LIKE ? OR JSON_EXTRACT(charinfo, '$.lastname') LIKE ?
       LIMIT ?`,
      [searchPattern, searchPattern, searchPattern, searchPattern, limit]
    )
    return rows
  } else {
    const [rows] = await pool.query<mysql.RowDataPacket[]>(
      `SELECT identifier, firstname, lastname, job, job_grade, accounts
       FROM users
       WHERE identifier LIKE ? OR firstname LIKE ? OR lastname LIKE ?
       LIMIT ?`,
      [searchPattern, searchPattern, searchPattern, limit]
    )
    return rows
  }
}

/**
 * Close the game database connection pool
 */
export async function closeGameDbPool(): Promise<void> {
  if (gameDbPool) {
    await gameDbPool.end()
    gameDbPool = null
  }
}

/**
 * Test game database connection
 */
export async function testGameDbConnection(): Promise<{ success: boolean; error?: string }> {
  try {
    const pool = await getGameDbPool()
    if (!pool) return { success: false, error: 'Game database not configured' }

    const connection = await pool.getConnection()
    await connection.ping()
    connection.release()

    return { success: true }
  } catch (error) {
    return {
      success: false,
      error: error instanceof Error ? error.message : 'Connection failed',
    }
  }
}

/**
 * Sync player data from game database to UCP database
 */
export async function syncPlayerData(discordId: string, serverId: string): Promise<boolean> {
  const config = await getGameDbConfig()
  if (!config) return false

  try {
    const pool = await getGameDbPool()
    if (!pool) return false

    // Get identifier pattern for Discord
    const identifier = `discord:${discordId}`

    if (config.framework === 'QBCORE') {
      // QBCore uses license, need to find by metadata or separate discord table
      const [rows] = await pool.query<mysql.RowDataPacket[]>(
        `SELECT * FROM players WHERE license LIKE ?`,
        [`%${discordId}%`]
      )
      
      if (rows.length > 0) {
        const player = rows[0]
        const charinfo = typeof player.charinfo === 'string' ? JSON.parse(player.charinfo) : player.charinfo
        const money = typeof player.money === 'string' ? JSON.parse(player.money) : player.money
        const job = typeof player.job === 'string' ? JSON.parse(player.job) : player.job
        const gang = typeof player.gang === 'string' ? JSON.parse(player.gang) : player.gang

        await prisma.playerProfile.upsert({
          where: { discordId_serverId: { discordId, serverId } },
          create: {
            discordId,
            serverId,
            displayName: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim(),
            phoneNumber: charinfo?.phone,
            money: BigInt(money?.cash || 0),
            bank: BigInt(money?.bank || 0),
            job: job?.name,
            jobGrade: job?.grade?.level,
            gang: gang?.name,
            gangGrade: gang?.grade?.level,
          },
          update: {
            displayName: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim(),
            phoneNumber: charinfo?.phone,
            money: BigInt(money?.cash || 0),
            bank: BigInt(money?.bank || 0),
            job: job?.name,
            jobGrade: job?.grade?.level,
            gang: gang?.name,
            gangGrade: gang?.grade?.level,
            lastSeen: new Date(),
          },
        })
        return true
      }
    } else {
      // ESX uses identifier directly
      const [rows] = await pool.query<mysql.RowDataPacket[]>(
        `SELECT * FROM users WHERE identifier = ?`,
        [identifier]
      )

      if (rows.length > 0) {
        const player = rows[0]
        const accounts = typeof player.accounts === 'string' ? JSON.parse(player.accounts) : player.accounts

        await prisma.playerProfile.upsert({
          where: { discordId_serverId: { discordId, serverId } },
          create: {
            discordId,
            serverId,
            displayName: `${player.firstname || ''} ${player.lastname || ''}`.trim(),
            phoneNumber: player.phone_number,
            money: BigInt(accounts?.money || accounts?.cash || 0),
            bank: BigInt(accounts?.bank || 0),
            job: player.job,
            jobGrade: player.job_grade,
          },
          update: {
            displayName: `${player.firstname || ''} ${player.lastname || ''}`.trim(),
            phoneNumber: player.phone_number,
            money: BigInt(accounts?.money || accounts?.cash || 0),
            bank: BigInt(accounts?.bank || 0),
            job: player.job,
            jobGrade: player.job_grade,
            lastSeen: new Date(),
          },
        })
        return true
      }
    }

    return false
  } catch (error) {
    console.error('[GameDB] Sync failed:', error)
    return false
  }
}