import { getCharacterById, type Character } from './character-service'
import { queryGameDb, getGameDbConfig, getGameDbPool } from '@/lib/game-db'
import type { RowDataPacket } from 'mysql2'

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

export interface LivePlayerData {
  character: Character
  vehicles: VehicleData[]
  inventory: InventoryItem[]
  recentActivity?: ActivityLog[]
  isOnline: boolean
  lastUpdate: string
}

export interface VehicleData {
  id?: string
  plate: string
  model: string
  modelLabel?: string
  stored: boolean
  garage?: string
  fuel?: number
  engine?: number
  body?: number
}

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

export interface ActivityLog {
  id: string
  action: string
  details?: string
  timestamp: Date
}

export interface DashboardLiveData {
  stats: {
    totalPlayers: number
    onlinePlayers: number
    totalMoney: number
    totalBank: number
    averagePlaytime: number
    newPlayersToday: number
  }
  recentPlayers: {
    id: string
    name: string
    job: string
    money: number
    lastSeen: string
  }[]
  jobs: {
    name: string
    label: string
    count: number
  }[]
  economy: {
    totalCash: number
    totalBank: number
    totalBlackMoney: number
  }
}

// ==========================================
// FRAMEWORK DETECTION
// ==========================================

type Framework = 'ESX' | 'QBCORE'

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

// ==========================================
// LIVE PLAYER DATA
// ==========================================

/**
 * Get live data for a specific character (used for 10-second polling)
 */
export async function getLivePlayerData(characterId: string): Promise<LivePlayerData | null> {
  try {
    const character = await getCharacterById(characterId)
    if (!character) return null

    const [vehicles, inventory] = await Promise.all([
      getPlayerVehicles(characterId),
      getPlayerInventory(characterId),
    ])

    return {
      character,
      vehicles,
      inventory,
      isOnline: false, // Would need FiveM txAdmin or live connection to check
      lastUpdate: new Date().toISOString(),
    }
  } catch (error) {
    console.error('[LiveData] Error getting player data:', error)
    return null
  }
}

/**
 * Get dashboard stats (used for admin dashboard polling)
 */
export async function getDashboardLiveData(): Promise<DashboardLiveData | null> {
  const framework = await getFramework()
  
  try {
    if (framework === 'QBCORE') {
      return await getQBCoreDashboardData()
    } else {
      return await getESXDashboardData()
    }
  } catch (error) {
    console.error('[LiveData] Error getting dashboard data:', error)
    return null
  }
}

// ==========================================
// ESX LIVE DATA
// ==========================================

async function getESXDashboardData(): Promise<DashboardLiveData> {
  const pool = await getGameDbPool()
  if (!pool) throw new Error('Database not connected')

  // Get all stats in parallel
  const [
    totalPlayersResult,
    moneyStatsResult,
    recentPlayersResult,
    jobsResult,
    newPlayersResult,
  ] = await Promise.all([
    pool.query<RowDataPacket[]>('SELECT COUNT(*) as count FROM users'),
    pool.query<RowDataPacket[]>(`
      SELECT 
        SUM(JSON_EXTRACT(accounts, '$.money')) as totalCash,
        SUM(JSON_EXTRACT(accounts, '$.bank')) as totalBank,
        SUM(JSON_EXTRACT(accounts, '$.black_money')) as totalBlack
      FROM users
    `),
    pool.query<RowDataPacket[]>(`
      SELECT identifier, firstname, lastname, job, accounts, last_login 
      FROM users 
      ORDER BY last_login DESC 
      LIMIT 10
    `),
    pool.query<RowDataPacket[]>(`
      SELECT job, COUNT(*) as count 
      FROM users 
      WHERE job IS NOT NULL AND job != 'unemployed'
      GROUP BY job 
      ORDER BY count DESC 
      LIMIT 10
    `),
    pool.query<RowDataPacket[]>(`
      SELECT COUNT(*) as count FROM users 
      WHERE DATE(last_login) = CURDATE() OR DATE(created_at) = CURDATE()
    `),
  ])

  const totalPlayers = (totalPlayersResult[0] as RowDataPacket[])[0]?.count || 0
  const moneyStats = (moneyStatsResult[0] as RowDataPacket[])[0] || {}
  const recentPlayers = (recentPlayersResult[0] as RowDataPacket[]) || []
  const jobs = (jobsResult[0] as RowDataPacket[]) || []
  const newPlayersToday = (newPlayersResult[0] as RowDataPacket[])[0]?.count || 0

  return {
    stats: {
      totalPlayers,
      onlinePlayers: 0, // Would need FiveM connection
      totalMoney: Number(moneyStats.totalCash) || 0,
      totalBank: Number(moneyStats.totalBank) || 0,
      averagePlaytime: 0,
      newPlayersToday,
    },
    recentPlayers: recentPlayers.map(p => {
      const accounts = typeof p.accounts === 'string' ? JSON.parse(p.accounts) : p.accounts
      return {
        id: p.identifier,
        name: `${p.firstname || ''} ${p.lastname || ''}`.trim() || 'Unknown',
        job: p.job || 'unemployed',
        money: (accounts?.money || 0) + (accounts?.bank || 0),
        lastSeen: p.last_login?.toISOString() || new Date().toISOString(),
      }
    }),
    jobs: jobs.map(j => ({
      name: j.job,
      label: j.job,
      count: j.count,
    })),
    economy: {
      totalCash: Number(moneyStats.totalCash) || 0,
      totalBank: Number(moneyStats.totalBank) || 0,
      totalBlackMoney: Number(moneyStats.totalBlack) || 0,
    },
  }
}

// ==========================================
// QBCORE LIVE DATA
// ==========================================

async function getQBCoreDashboardData(): Promise<DashboardLiveData> {
  const pool = await getGameDbPool()
  if (!pool) throw new Error('Database not connected')

  const [
    totalPlayersResult,
    moneyStatsResult,
    recentPlayersResult,
    jobsResult,
    newPlayersResult,
  ] = await Promise.all([
    pool.query<RowDataPacket[]>('SELECT COUNT(*) as count FROM players'),
    pool.query<RowDataPacket[]>(`
      SELECT 
        SUM(JSON_EXTRACT(money, '$.cash')) as totalCash,
        SUM(JSON_EXTRACT(money, '$.bank')) as totalBank,
        SUM(JSON_EXTRACT(money, '$.crypto')) as totalCrypto
      FROM players
    `),
    pool.query<RowDataPacket[]>(`
      SELECT citizenid, charinfo, job, money, last_updated 
      FROM players 
      ORDER BY last_updated DESC 
      LIMIT 10
    `),
    pool.query<RowDataPacket[]>(`
      SELECT JSON_EXTRACT(job, '$.name') as job_name, JSON_EXTRACT(job, '$.label') as job_label, COUNT(*) as count 
      FROM players 
      WHERE JSON_EXTRACT(job, '$.name') != 'unemployed'
      GROUP BY JSON_EXTRACT(job, '$.name'), JSON_EXTRACT(job, '$.label')
      ORDER BY count DESC 
      LIMIT 10
    `),
    pool.query<RowDataPacket[]>(`
      SELECT COUNT(*) as count FROM players 
      WHERE DATE(last_updated) = CURDATE()
    `),
  ])

  const totalPlayers = (totalPlayersResult[0] as RowDataPacket[])[0]?.count || 0
  const moneyStats = (moneyStatsResult[0] as RowDataPacket[])[0] || {}
  const recentPlayers = (recentPlayersResult[0] as RowDataPacket[]) || []
  const jobs = (jobsResult[0] as RowDataPacket[]) || []
  const newPlayersToday = (newPlayersResult[0] as RowDataPacket[])[0]?.count || 0

  return {
    stats: {
      totalPlayers,
      onlinePlayers: 0,
      totalMoney: Number(moneyStats.totalCash) || 0,
      totalBank: Number(moneyStats.totalBank) || 0,
      averagePlaytime: 0,
      newPlayersToday,
    },
    recentPlayers: recentPlayers.map(p => {
      const charinfo = typeof p.charinfo === 'string' ? JSON.parse(p.charinfo) : p.charinfo
      const money = typeof p.money === 'string' ? JSON.parse(p.money) : p.money
      const job = typeof p.job === 'string' ? JSON.parse(p.job) : p.job
      return {
        id: p.citizenid,
        name: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim() || 'Unknown',
        job: job?.name || 'unemployed',
        money: (money?.cash || 0) + (money?.bank || 0),
        lastSeen: p.last_updated?.toISOString() || new Date().toISOString(),
      }
    }),
    jobs: jobs.map(j => ({
      name: (j.job_name || '').replace(/"/g, ''),
      label: (j.job_label || '').replace(/"/g, ''),
      count: j.count,
    })),
    economy: {
      totalCash: Number(moneyStats.totalCash) || 0,
      totalBank: Number(moneyStats.totalBank) || 0,
      totalBlackMoney: Number(moneyStats.totalCrypto) || 0,
    },
  }
}

// ==========================================
// VEHICLE & INVENTORY DATA
// ==========================================

async function getPlayerVehicles(characterId: string): Promise<VehicleData[]> {
  const framework = await getFramework()
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    if (framework === 'QBCORE') {
      const [rows] = await pool.query<RowDataPacket[]>(
        'SELECT * FROM player_vehicles WHERE citizenid = ?',
        [characterId]
      )
      return rows.map(v => ({
        plate: v.plate,
        model: v.vehicle || v.model || 'Unknown',
        stored: v.state === 1,
        garage: v.garage,
        fuel: v.fuel,
        engine: v.engine,
        body: v.body,
      }))
    } else {
      const [rows] = await pool.query<RowDataPacket[]>(
        'SELECT * FROM owned_vehicles WHERE owner = ?',
        [characterId]
      )
      return rows.map(v => {
        const vehicleData = typeof v.vehicle === 'string' ? JSON.parse(v.vehicle) : v.vehicle
        return {
          plate: v.plate,
          model: vehicleData?.model || v.vehicle || 'Unknown',
          stored: v.stored === 1,
          garage: v.garage || v.parking,
        }
      })
    }
  } catch (error) {
    console.error('[LiveData] Error getting vehicles:', error)
    return []
  }
}

async function getPlayerInventory(characterId: string): Promise<InventoryItem[]> {
  const framework = await getFramework()
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    // Try different inventory systems
    const tables = framework === 'QBCORE' 
      ? ['player_inventory', 'inventory', 'inventories']
      : ['inventory', 'user_inventory', 'inventories']

    for (const table of tables) {
      try {
        const idColumn = framework === 'QBCORE' ? 'citizenid' : 'owner'
        const [rows] = await pool.query<RowDataPacket[]>(
          `SELECT * FROM ${table} WHERE ${idColumn} = ?`,
          [characterId]
        )
        
        if (rows.length > 0) {
          // Different inventory formats
          if (rows[0].items) {
            // JSON format
            const items = typeof rows[0].items === 'string' ? JSON.parse(rows[0].items) : rows[0].items
            return Object.values(items).filter((item: unknown) => item !== null).map((item: unknown) => {
              const i = item as Record<string, unknown>
              return {
                name: i.name as string || 'Unknown',
                label: i.label as string,
                count: i.amount as number || i.count as number || 1,
                slot: i.slot as number,
              }
            })
          } else {
            // Row-per-item format
            return rows.map(row => ({
              name: row.item || row.name || 'Unknown',
              label: row.label,
              count: row.count || row.amount || 1,
              slot: row.slot,
            }))
          }
        }
      } catch {
        continue
      }
    }
    
    return []
  } catch (error) {
    console.error('[LiveData] Error getting inventory:', error)
    return []
  }
}

// ==========================================
// EXPORT
// ==========================================

export { getCharacterById } from './character-service'