import { queryGameDb, getGameDbConfig, getGameDbPool } from '@/lib/game-db'
import { prisma } from '@/lib/db'
import type { RowDataPacket } from 'mysql2'

// ==========================================
// TYPES
// ==========================================

export interface PlayerIdentifiers {
  discord?: string
  steam?: string
  license?: string
  ip?: string
  xbl?: string
  live?: string
  fivem?: string
}

export interface PlayerCharacter {
  id: string
  identifier: string
  citizenid?: string
  name: string
  firstName: string
  lastName: string
  dateOfBirth?: string
  gender?: number | string
  nationality?: string
  phone?: string
  job: {
    name: string
    label: string
    grade: number
    gradeLabel?: string
  }
  gang?: {
    name: string
    label: string
    grade: number
  }
  money: {
    cash: number
    bank: number
    crypto?: number
    black?: number
  }
  metadata?: {
    hunger?: number
    thirst?: number
    stress?: number
    armor?: number
    health?: number
    isDead?: boolean
    inJail?: boolean
    jailtTime?: number
  }
  position?: {
    x: number
    y: number
    z: number
    heading?: number
  }
  lastSeen?: Date
  playtime?: number
}

export interface PlayerVehicle {
  id?: string
  plate: string
  model: string
  modelLabel?: string
  stored: boolean
  garage?: string
  fuel?: number
  engine?: number
  body?: number
  mods?: Record<string, unknown>
}

export interface PlayerProperty {
  id?: string
  name: string
  label?: string
  type?: string
  owned: boolean
  garage?: boolean
  coords?: { x: number; y: number; z: number }
}

export interface PlayerInventoryItem {
  name: string
  label?: string
  count: number
  slot?: number
  weight?: number
  info?: Record<string, unknown>
}

export interface PlayerStats {
  totalPlaytime: number // in minutes
  totalSessions: number
  totalDeaths: number
  totalKills: number
  vehiclesOwned: number
  propertiesOwned: number
  totalWealth: number
  lastSeen: Date | null
  firstSeen: Date | null
}

export interface PlayerProfile {
  character: PlayerCharacter
  identifiers: PlayerIdentifiers
  vehicles: PlayerVehicle[]
  properties: PlayerProperty[]
  inventory: PlayerInventoryItem[]
  stats: PlayerStats
}

// ==========================================
// FRAMEWORK DETECTION & ABSTRACTION
// ==========================================

type Framework = 'ESX' | 'QBCORE'

async function getFramework(): Promise<Framework> {
  const config = await getGameDbConfig()
  return config?.framework || 'ESX'
}

// ==========================================
// IDENTIFIER MAPPING
// ==========================================

/**
 * Find player by Discord ID
 */
export async function findPlayerByDiscord(discordId: string): Promise<PlayerCharacter | null> {
  const framework = await getFramework()
  const discordIdentifier = `discord:${discordId}`
  
  try {
    if (framework === 'QBCORE') {
      return await findQBCorePlayerByIdentifier(discordIdentifier)
    } else {
      return await findESXPlayerByIdentifier(discordIdentifier)
    }
  } catch (error) {
    console.error('[PlayerService] Error finding player by Discord:', error)
    return null
  }
}

/**
 * Find player by any identifier
 */
export async function findPlayerByIdentifier(identifier: string): Promise<PlayerCharacter | null> {
  const framework = await getFramework()
  
  try {
    if (framework === 'QBCORE') {
      return await findQBCorePlayerByIdentifier(identifier)
    } else {
      return await findESXPlayerByIdentifier(identifier)
    }
  } catch (error) {
    console.error('[PlayerService] Error finding player:', error)
    return null
  }
}

// ==========================================
// ESX IMPLEMENTATION
// ==========================================

async function findESXPlayerByIdentifier(identifier: string): Promise<PlayerCharacter | null> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users WHERE identifier = ? OR identifier LIKE ?`,
      [identifier, `%${identifier}%`]
    )

    if (!rows || rows.length === 0) return null

    const player = rows[0]
    const accounts = parseJson(player.accounts)
    const position = parseJson(player.position)

    return {
      id: player.identifier,
      identifier: player.identifier,
      name: `${player.firstname || ''} ${player.lastname || ''}`.trim() || 'Unknown',
      firstName: player.firstname || '',
      lastName: player.lastname || '',
      dateOfBirth: player.dateofbirth,
      gender: player.sex,
      phone: player.phone_number,
      job: {
        name: player.job || 'unemployed',
        label: player.job || 'Unemployed',
        grade: player.job_grade || 0,
      },
      money: {
        cash: accounts?.money || accounts?.cash || 0,
        bank: accounts?.bank || 0,
        black: accounts?.black_money || accounts?.black || 0,
      },
      position: position ? {
        x: position.x,
        y: position.y,
        z: position.z,
        heading: position.heading,
      } : undefined,
    }
  } catch (error) {
    console.error('[ESX] Error finding player:', error)
    return null
  }
}

export async function getESXPlayers(limit = 100, offset = 0): Promise<PlayerCharacter[]> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users ORDER BY identifier LIMIT ? OFFSET ?`,
      [limit, offset]
    )

    return rows.map(player => {
      const accounts = parseJson(player.accounts)
      const position = parseJson(player.position)

      return {
        id: player.identifier,
        identifier: player.identifier,
        name: `${player.firstname || ''} ${player.lastname || ''}`.trim() || 'Unknown',
        firstName: player.firstname || '',
        lastName: player.lastname || '',
        dateOfBirth: player.dateofbirth,
        gender: player.sex,
        phone: player.phone_number,
        job: {
          name: player.job || 'unemployed',
          label: player.job || 'Unemployed',
          grade: player.job_grade || 0,
        },
        money: {
          cash: accounts?.money || accounts?.cash || 0,
          bank: accounts?.bank || 0,
          black: accounts?.black_money || accounts?.black || 0,
        },
        position: position ? {
          x: position.x,
          y: position.y,
          z: position.z,
        } : undefined,
      }
    })
  } catch (error) {
    console.error('[ESX] Error getting players:', error)
    return []
  }
}

export async function getESXVehicles(identifier: string): Promise<PlayerVehicle[]> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM owned_vehicles WHERE owner = ?`,
      [identifier]
    )

    return rows.map(vehicle => {
      const vehicleData = parseJson(vehicle.vehicle)
      return {
        plate: vehicle.plate,
        model: vehicleData?.model || vehicle.vehicle || 'Unknown',
        stored: vehicle.stored === 1 || vehicle.stored === true,
        garage: vehicle.garage || vehicle.parking,
        mods: vehicleData,
      }
    })
  } catch (error) {
    console.error('[ESX] Error getting vehicles:', error)
    return []
  }
}

export async function getESXProperties(identifier: string): Promise<PlayerProperty[]> {
  try {
    // Try different property table formats
    const pool = await getGameDbPool()
    if (!pool) return []

    // Check for common property tables
    const tables = ['owned_properties', 'player_houses', 'properties', 'housing']
    
    for (const table of tables) {
      try {
        const [rows] = await pool.query<RowDataPacket[]>(
          `SELECT * FROM ${table} WHERE owner = ? OR identifier = ?`,
          [identifier, identifier]
        )
        
        if (rows.length > 0) {
          return rows.map(prop => ({
            id: prop.id?.toString(),
            name: prop.name || prop.property || 'Unknown Property',
            label: prop.label || prop.name,
            type: prop.type || 'house',
            owned: true,
            garage: prop.garage === 1,
          }))
        }
      } catch {
        // Table doesn't exist, try next
        continue
      }
    }
    
    return []
  } catch (error) {
    console.error('[ESX] Error getting properties:', error)
    return []
  }
}

// ==========================================
// QBCORE IMPLEMENTATION
// ==========================================

async function findQBCorePlayerByIdentifier(identifier: string): Promise<PlayerCharacter | null> {
  try {
    // QBCore typically stores license in the license column
    // Discord is often in a separate identifiers table or metadata
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM players WHERE license LIKE ? OR citizenid = ? LIMIT 1`,
      [`%${identifier.replace('discord:', '')}%`, identifier]
    )

    if (!rows || rows.length === 0) {
      // Try identifiers table if it exists
      try {
        const idRows = await queryGameDb<RowDataPacket[]>(
          `SELECT p.* FROM players p 
           INNER JOIN player_identifiers pi ON p.citizenid = pi.citizenid 
           WHERE pi.identifier = ?`,
          [identifier]
        )
        if (idRows && idRows.length > 0) {
          return parseQBCorePlayer(idRows[0])
        }
      } catch {
        // identifiers table doesn't exist
      }
      return null
    }

    return parseQBCorePlayer(rows[0])
  } catch (error) {
    console.error('[QBCore] Error finding player:', error)
    return null
  }
}

function parseQBCorePlayer(player: RowDataPacket): PlayerCharacter {
  const charinfo = parseJson(player.charinfo)
  const money = parseJson(player.money)
  const job = parseJson(player.job)
  const gang = parseJson(player.gang)
  const metadata = parseJson(player.metadata)
  const position = parseJson(player.position)

  return {
    id: player.citizenid,
    identifier: player.license,
    citizenid: player.citizenid,
    name: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim() || player.name || 'Unknown',
    firstName: charinfo?.firstname || '',
    lastName: charinfo?.lastname || '',
    dateOfBirth: charinfo?.birthdate,
    gender: charinfo?.gender,
    nationality: charinfo?.nationality,
    phone: charinfo?.phone,
    job: {
      name: job?.name || 'unemployed',
      label: job?.label || 'Unemployed',
      grade: job?.grade?.level || 0,
      gradeLabel: job?.grade?.name,
    },
    gang: gang ? {
      name: gang.name || 'none',
      label: gang.label || 'None',
      grade: gang.grade?.level || 0,
    } : undefined,
    money: {
      cash: money?.cash || 0,
      bank: money?.bank || 0,
      crypto: money?.crypto || 0,
    },
    metadata: metadata ? {
      hunger: metadata.hunger,
      thirst: metadata.thirst,
      stress: metadata.stress,
      armor: metadata.armor,
      isDead: metadata.isdead,
      inJail: metadata.injail,
      jailtTime: metadata.jailttime,
    } : undefined,
    position: position ? {
      x: position.x,
      y: position.y,
      z: position.z,
      heading: position.w,
    } : undefined,
    lastSeen: player.last_updated ? new Date(player.last_updated) : undefined,
  }
}

export async function getQBCorePlayers(limit = 100, offset = 0): Promise<PlayerCharacter[]> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM players ORDER BY citizenid LIMIT ? OFFSET ?`,
      [limit, offset]
    )

    return rows.map(parseQBCorePlayer)
  } catch (error) {
    console.error('[QBCore] Error getting players:', error)
    return []
  }
}

export async function getQBCoreVehicles(citizenid: string): Promise<PlayerVehicle[]> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM player_vehicles WHERE citizenid = ?`,
      [citizenid]
    )

    return rows.map(vehicle => {
      const mods = parseJson(vehicle.mods)
      return {
        plate: vehicle.plate,
        model: vehicle.vehicle || vehicle.model || 'Unknown',
        stored: vehicle.state === 1 || vehicle.state === 'stored' || vehicle.stored === 1,
        garage: vehicle.garage,
        fuel: vehicle.fuel,
        engine: vehicle.engine,
        body: vehicle.body,
        mods,
      }
    })
  } catch (error) {
    console.error('[QBCore] Error getting vehicles:', error)
    return []
  }
}

export async function getQBCoreProperties(citizenid: string): Promise<PlayerProperty[]> {
  try {
    const pool = await getGameDbPool()
    if (!pool) return []

    // Check for common QBCore housing tables
    const tables = ['player_houses', 'properties', 'apartments', 'housing']
    
    for (const table of tables) {
      try {
        const [rows] = await pool.query<RowDataPacket[]>(
          `SELECT * FROM ${table} WHERE citizenid = ? OR owner = ?`,
          [citizenid, citizenid]
        )
        
        if (rows.length > 0) {
          return rows.map(prop => ({
            id: prop.id?.toString(),
            name: prop.name || prop.house || 'Unknown Property',
            label: prop.label,
            type: prop.type || 'house',
            owned: true,
            garage: prop.garage === 1,
          }))
        }
      } catch {
        continue
      }
    }
    
    return []
  } catch (error) {
    console.error('[QBCore] Error getting properties:', error)
    return []
  }
}

// ==========================================
// UNIFIED SERVICE FUNCTIONS
// ==========================================

/**
 * Get all players with pagination
 */
export async function getPlayers(limit = 100, offset = 0): Promise<PlayerCharacter[]> {
  const framework = await getFramework()
  
  if (framework === 'QBCORE') {
    return getQBCorePlayers(limit, offset)
  }
  return getESXPlayers(limit, offset)
}

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

  try {
    const table = config.framework === 'QBCORE' ? 'players' : 'users'
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT COUNT(*) as count FROM ${table}`
    )
    return rows[0]?.count || 0
  } catch (error) {
    console.error('[PlayerService] Error getting player count:', error)
    return 0
  }
}

/**
 * Search players by name or identifier
 */
export async function searchPlayers(query: string, limit = 20): Promise<PlayerCharacter[]> {
  const framework = await getFramework()
  const searchPattern = `%${query}%`

  try {
    if (framework === 'QBCORE') {
      const rows = await queryGameDb<RowDataPacket[]>(
        `SELECT * 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.map(parseQBCorePlayer)
    } else {
      const rows = await queryGameDb<RowDataPacket[]>(
        `SELECT * FROM users 
         WHERE identifier LIKE ? 
         OR firstname LIKE ? 
         OR lastname LIKE ?
         LIMIT ?`,
        [searchPattern, searchPattern, searchPattern, limit]
      )
      return rows.map(player => {
        const accounts = parseJson(player.accounts)
        return {
          id: player.identifier,
          identifier: player.identifier,
          name: `${player.firstname || ''} ${player.lastname || ''}`.trim() || 'Unknown',
          firstName: player.firstname || '',
          lastName: player.lastname || '',
          job: {
            name: player.job || 'unemployed',
            label: player.job || 'Unemployed',
            grade: player.job_grade || 0,
          },
          money: {
            cash: accounts?.money || accounts?.cash || 0,
            bank: accounts?.bank || 0,
          },
        }
      })
    }
  } catch (error) {
    console.error('[PlayerService] Error searching players:', error)
    return []
  }
}

/**
 * Get vehicles for a player
 */
export async function getPlayerVehicles(playerId: string): Promise<PlayerVehicle[]> {
  const framework = await getFramework()
  
  if (framework === 'QBCORE') {
    return getQBCoreVehicles(playerId)
  }
  return getESXVehicles(playerId)
}

/**
 * Get properties for a player
 */
export async function getPlayerProperties(playerId: string): Promise<PlayerProperty[]> {
  const framework = await getFramework()
  
  if (framework === 'QBCORE') {
    return getQBCoreProperties(playerId)
  }
  return getESXProperties(playerId)
}

/**
 * Get full player profile
 */
export async function getPlayerProfile(discordId: string): Promise<PlayerProfile | null> {
  const character = await findPlayerByDiscord(discordId)
  if (!character) return null

  const playerId = character.citizenid || character.identifier
  
  const [vehicles, properties] = await Promise.all([
    getPlayerVehicles(playerId),
    getPlayerProperties(playerId),
  ])

  // Calculate stats
  const totalWealth = character.money.cash + character.money.bank + (character.money.crypto || 0)

  return {
    character,
    identifiers: {
      discord: discordId,
      license: character.identifier,
    },
    vehicles,
    properties,
    inventory: [], // Would need inventory table query
    stats: {
      totalPlaytime: character.playtime || 0,
      totalSessions: 0,
      totalDeaths: 0,
      totalKills: 0,
      vehiclesOwned: vehicles.length,
      propertiesOwned: properties.length,
      totalWealth,
      lastSeen: character.lastSeen || null,
      firstSeen: null,
    },
  }
}

/**
 * Sync player data to UCP database
 */
export async function syncPlayerToUCP(discordId: string, userId?: string): Promise<boolean> {
  const profile = await getPlayerProfile(discordId)
  if (!profile) return false

  try {
    await prisma.playerProfile.upsert({
      where: { 
        discordId_serverId: { 
          discordId, 
          serverId: 'default' // Single server mode
        } 
      },
      create: {
        discordId,
        serverId: 'default',
        userId,
        displayName: profile.character.name,
        phoneNumber: profile.character.phone,
        money: BigInt(profile.character.money.cash),
        bank: BigInt(profile.character.money.bank),
        job: profile.character.job.name,
        jobGrade: profile.character.job.grade,
        gang: profile.character.gang?.name,
        gangGrade: profile.character.gang?.grade,
        playtime: profile.stats.totalPlaytime,
        lastSeen: new Date(),
      },
      update: {
        userId,
        displayName: profile.character.name,
        phoneNumber: profile.character.phone,
        money: BigInt(profile.character.money.cash),
        bank: BigInt(profile.character.money.bank),
        job: profile.character.job.name,
        jobGrade: profile.character.job.grade,
        gang: profile.character.gang?.name,
        gangGrade: profile.character.gang?.grade,
        playtime: profile.stats.totalPlaytime,
        lastSeen: new Date(),
      },
    })

    // Update stats
    const existingProfile = await prisma.playerProfile.findUnique({
      where: { discordId_serverId: { discordId, serverId: 'default' } },
      include: { stats: true },
    })

    if (existingProfile) {
      await prisma.playerStats.upsert({
        where: { profileId: existingProfile.id },
        create: {
          profileId: existingProfile.id,
          vehiclesOwned: profile.vehicles.length,
          housesOwned: profile.properties.length,
        },
        update: {
          vehiclesOwned: profile.vehicles.length,
          housesOwned: profile.properties.length,
        },
      })
    }

    return true
  } catch (error) {
    console.error('[PlayerService] Error syncing player:', error)
    return false
  }
}

// ==========================================
// HELPER FUNCTIONS
// ==========================================

function parseJson(value: unknown): Record<string, unknown> | null {
  if (!value) return null
  if (typeof value === 'object') return value as Record<string, unknown>
  if (typeof value === 'string') {
    try {
      return JSON.parse(value)
    } catch {
      return null
    }
  }
  return null
}