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

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

export interface ServerStats {
  totalPlayers: number
  totalVehicles: number
  totalProperties: number
  totalMoney: number
  totalBank: number
  onlinePlayers?: number
}

export interface JobStats {
  name: string
  label: string
  count: number
  percentage: number
}

export interface EconomyStats {
  totalCash: number
  totalBank: number
  totalWealth: number
  averageWealth: number
  richestPlayer: {
    name: string
    wealth: number
  } | null
  poorestPlayer: {
    name: string
    wealth: number
  } | null
}

export interface ActivityStats {
  newPlayersToday: number
  newPlayersWeek: number
  newPlayersMonth: number
  activePlayersToday: number
  activePlayersWeek: number
}

export interface VehicleStats {
  total: number
  stored: number
  out: number
  topModels: { model: string; count: number }[]
}

// ==========================================
// SERVER STATS
// ==========================================

export async function getServerStats(): Promise<ServerStats> {
  const config = await getGameDbConfig()
  if (!config) {
    return {
      totalPlayers: 0,
      totalVehicles: 0,
      totalProperties: 0,
      totalMoney: 0,
      totalBank: 0,
    }
  }

  try {
    const pool = await getGameDbPool()
    if (!pool) throw new Error('No database pool')

    if (config.framework === 'QBCORE') {
      return getQBCoreServerStats(pool)
    }
    return getESXServerStats(pool)
  } catch (error) {
    console.error('[StatsService] Error getting server stats:', error)
    return {
      totalPlayers: 0,
      totalVehicles: 0,
      totalProperties: 0,
      totalMoney: 0,
      totalBank: 0,
    }
  }
}

async function getESXServerStats(pool: Awaited<ReturnType<typeof getGameDbPool>>): Promise<ServerStats> {
  if (!pool) throw new Error('No pool')

  // Player count and economy
  const [playerRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      COUNT(*) as totalPlayers,
      COALESCE(SUM(JSON_EXTRACT(accounts, '$.money')), 0) + COALESCE(SUM(JSON_EXTRACT(accounts, '$.cash')), 0) as totalMoney,
      COALESCE(SUM(JSON_EXTRACT(accounts, '$.bank')), 0) as totalBank
    FROM users
  `)

  // Vehicle count
  let totalVehicles = 0
  try {
    const [vehicleRows] = await pool.query<RowDataPacket[]>(`SELECT COUNT(*) as count FROM owned_vehicles`)
    totalVehicles = vehicleRows[0]?.count || 0
  } catch { /* table might not exist */ }

  // Property count
  let totalProperties = 0
  const propertyTables = ['owned_properties', 'player_houses', 'properties']
  for (const table of propertyTables) {
    try {
      const [propRows] = await pool.query<RowDataPacket[]>(`SELECT COUNT(*) as count FROM ${table}`)
      totalProperties = propRows[0]?.count || 0
      break
    } catch { continue }
  }

  return {
    totalPlayers: playerRows[0]?.totalPlayers || 0,
    totalVehicles,
    totalProperties,
    totalMoney: Number(playerRows[0]?.totalMoney || 0),
    totalBank: Number(playerRows[0]?.totalBank || 0),
  }
}

async function getQBCoreServerStats(pool: Awaited<ReturnType<typeof getGameDbPool>>): Promise<ServerStats> {
  if (!pool) throw new Error('No pool')

  // Player count and economy
  const [playerRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      COUNT(*) as totalPlayers,
      COALESCE(SUM(JSON_EXTRACT(money, '$.cash')), 0) as totalMoney,
      COALESCE(SUM(JSON_EXTRACT(money, '$.bank')), 0) as totalBank
    FROM players
  `)

  // Vehicle count
  let totalVehicles = 0
  try {
    const [vehicleRows] = await pool.query<RowDataPacket[]>(`SELECT COUNT(*) as count FROM player_vehicles`)
    totalVehicles = vehicleRows[0]?.count || 0
  } catch { /* table might not exist */ }

  // Property count
  let totalProperties = 0
  const propertyTables = ['player_houses', 'apartments', 'properties']
  for (const table of propertyTables) {
    try {
      const [propRows] = await pool.query<RowDataPacket[]>(`SELECT COUNT(*) as count FROM ${table}`)
      totalProperties = propRows[0]?.count || 0
      break
    } catch { continue }
  }

  return {
    totalPlayers: playerRows[0]?.totalPlayers || 0,
    totalVehicles,
    totalProperties,
    totalMoney: Number(playerRows[0]?.totalMoney || 0),
    totalBank: Number(playerRows[0]?.totalBank || 0),
  }
}

// ==========================================
// JOB STATS
// ==========================================

export async function getJobStats(limit = 10): Promise<JobStats[]> {
  const config = await getGameDbConfig()
  if (!config) return []

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

    let rows: RowDataPacket[]

    if (config.framework === 'QBCORE') {
      [rows] = await pool.query<RowDataPacket[]>(`
        SELECT 
          JSON_EXTRACT(job, '$.name') as jobName,
          JSON_EXTRACT(job, '$.label') as jobLabel,
          COUNT(*) as count
        FROM players
        GROUP BY JSON_EXTRACT(job, '$.name'), JSON_EXTRACT(job, '$.label')
        ORDER BY count DESC
        LIMIT ?
      `, [limit])
    } else {
      [rows] = await pool.query<RowDataPacket[]>(`
        SELECT 
          job as jobName,
          job as jobLabel,
          COUNT(*) as count
        FROM users
        GROUP BY job
        ORDER BY count DESC
        LIMIT ?
      `, [limit])
    }

    const total = rows.reduce((sum, row) => sum + Number(row.count), 0)

    return rows.map(row => ({
      name: (row.jobName || 'unemployed').replace(/"/g, ''),
      label: (row.jobLabel || row.jobName || 'Unemployed').replace(/"/g, ''),
      count: Number(row.count),
      percentage: total > 0 ? Math.round((Number(row.count) / total) * 100) : 0,
    }))
  } catch (error) {
    console.error('[StatsService] Error getting job stats:', error)
    return []
  }
}

// ==========================================
// ECONOMY STATS
// ==========================================

export async function getEconomyStats(): Promise<EconomyStats> {
  const config = await getGameDbConfig()
  if (!config) {
    return {
      totalCash: 0,
      totalBank: 0,
      totalWealth: 0,
      averageWealth: 0,
      richestPlayer: null,
      poorestPlayer: null,
    }
  }

  try {
    const pool = await getGameDbPool()
    if (!pool) throw new Error('No pool')

    if (config.framework === 'QBCORE') {
      return getQBCoreEconomyStats(pool)
    }
    return getESXEconomyStats(pool)
  } catch (error) {
    console.error('[StatsService] Error getting economy stats:', error)
    return {
      totalCash: 0,
      totalBank: 0,
      totalWealth: 0,
      averageWealth: 0,
      richestPlayer: null,
      poorestPlayer: null,
    }
  }
}

async function getESXEconomyStats(pool: Awaited<ReturnType<typeof getGameDbPool>>): Promise<EconomyStats> {
  if (!pool) throw new Error('No pool')

  const [statsRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      COALESCE(SUM(JSON_EXTRACT(accounts, '$.money')), 0) + COALESCE(SUM(JSON_EXTRACT(accounts, '$.cash')), 0) as totalCash,
      COALESCE(SUM(JSON_EXTRACT(accounts, '$.bank')), 0) as totalBank,
      COUNT(*) as playerCount
    FROM users
  `)

  const [richestRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      firstname, lastname,
      (COALESCE(JSON_EXTRACT(accounts, '$.money'), 0) + COALESCE(JSON_EXTRACT(accounts, '$.cash'), 0) + COALESCE(JSON_EXTRACT(accounts, '$.bank'), 0)) as wealth
    FROM users
    ORDER BY wealth DESC
    LIMIT 1
  `)

  const [poorestRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      firstname, lastname,
      (COALESCE(JSON_EXTRACT(accounts, '$.money'), 0) + COALESCE(JSON_EXTRACT(accounts, '$.cash'), 0) + COALESCE(JSON_EXTRACT(accounts, '$.bank'), 0)) as wealth
    FROM users
    ORDER BY wealth ASC
    LIMIT 1
  `)

  const totalCash = Number(statsRows[0]?.totalCash || 0)
  const totalBank = Number(statsRows[0]?.totalBank || 0)
  const playerCount = Number(statsRows[0]?.playerCount || 1)

  return {
    totalCash,
    totalBank,
    totalWealth: totalCash + totalBank,
    averageWealth: Math.round((totalCash + totalBank) / playerCount),
    richestPlayer: richestRows[0] ? {
      name: `${richestRows[0].firstname || ''} ${richestRows[0].lastname || ''}`.trim() || 'Unknown',
      wealth: Number(richestRows[0].wealth || 0),
    } : null,
    poorestPlayer: poorestRows[0] ? {
      name: `${poorestRows[0].firstname || ''} ${poorestRows[0].lastname || ''}`.trim() || 'Unknown',
      wealth: Number(poorestRows[0].wealth || 0),
    } : null,
  }
}

async function getQBCoreEconomyStats(pool: Awaited<ReturnType<typeof getGameDbPool>>): Promise<EconomyStats> {
  if (!pool) throw new Error('No pool')

  const [statsRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      COALESCE(SUM(JSON_EXTRACT(money, '$.cash')), 0) as totalCash,
      COALESCE(SUM(JSON_EXTRACT(money, '$.bank')), 0) as totalBank,
      COUNT(*) as playerCount
    FROM players
  `)

  const [richestRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      charinfo,
      (COALESCE(JSON_EXTRACT(money, '$.cash'), 0) + COALESCE(JSON_EXTRACT(money, '$.bank'), 0)) as wealth
    FROM players
    ORDER BY wealth DESC
    LIMIT 1
  `)

  const [poorestRows] = await pool.query<RowDataPacket[]>(`
    SELECT 
      charinfo,
      (COALESCE(JSON_EXTRACT(money, '$.cash'), 0) + COALESCE(JSON_EXTRACT(money, '$.bank'), 0)) as wealth
    FROM players
    ORDER BY wealth ASC
    LIMIT 1
  `)

  const totalCash = Number(statsRows[0]?.totalCash || 0)
  const totalBank = Number(statsRows[0]?.totalBank || 0)
  const playerCount = Number(statsRows[0]?.playerCount || 1)

  const parseCharinfo = (charinfo: unknown) => {
    if (!charinfo) return 'Unknown'
    const data = typeof charinfo === 'string' ? JSON.parse(charinfo) : charinfo
    return `${data.firstname || ''} ${data.lastname || ''}`.trim() || 'Unknown'
  }

  return {
    totalCash,
    totalBank,
    totalWealth: totalCash + totalBank,
    averageWealth: Math.round((totalCash + totalBank) / playerCount),
    richestPlayer: richestRows[0] ? {
      name: parseCharinfo(richestRows[0].charinfo),
      wealth: Number(richestRows[0].wealth || 0),
    } : null,
    poorestPlayer: poorestRows[0] ? {
      name: parseCharinfo(poorestRows[0].charinfo),
      wealth: Number(poorestRows[0].wealth || 0),
    } : null,
  }
}

// ==========================================
// VEHICLE STATS
// ==========================================

export async function getVehicleStats(): Promise<VehicleStats> {
  const config = await getGameDbConfig()
  if (!config) {
    return { total: 0, stored: 0, out: 0, topModels: [] }
  }

  try {
    const pool = await getGameDbPool()
    if (!pool) throw new Error('No pool')

    const table = config.framework === 'QBCORE' ? 'player_vehicles' : 'owned_vehicles'
    const storedField = config.framework === 'QBCORE' ? 'state' : 'stored'
    const modelField = config.framework === 'QBCORE' ? 'vehicle' : 'vehicle'

    // Total and stored counts
    const [countRows] = await pool.query<RowDataPacket[]>(`
      SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN ${storedField} = 1 THEN 1 ELSE 0 END) as stored
      FROM ${table}
    `)

    // Top models
    const [modelRows] = await pool.query<RowDataPacket[]>(`
      SELECT ${modelField} as model, COUNT(*) as count
      FROM ${table}
      GROUP BY ${modelField}
      ORDER BY count DESC
      LIMIT 5
    `)

    const total = Number(countRows[0]?.total || 0)
    const stored = Number(countRows[0]?.stored || 0)

    return {
      total,
      stored,
      out: total - stored,
      topModels: modelRows.map(row => ({
        model: row.model || 'Unknown',
        count: Number(row.count),
      })),
    }
  } catch (error) {
    console.error('[StatsService] Error getting vehicle stats:', error)
    return { total: 0, stored: 0, out: 0, topModels: [] }
  }
}

// ==========================================
// COMBINED DASHBOARD STATS
// ==========================================

export interface DashboardStats {
  server: ServerStats
  jobs: JobStats[]
  economy: EconomyStats
  vehicles: VehicleStats
}

export async function getDashboardStats(): Promise<DashboardStats> {
  const [server, jobs, economy, vehicles] = await Promise.all([
    getServerStats(),
    getJobStats(),
    getEconomyStats(),
    getVehicleStats(),
  ])

  return { server, jobs, economy, vehicles }
}