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

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

export interface NormalizedJob {
  name: string
  label: string
  grades: NormalizedJobGrade[]
  defaultDuty?: boolean
  offDutyPay?: boolean
}

export interface NormalizedJobGrade {
  grade: number
  name: string
  label: string
  salary?: number
  isBoss?: boolean
}

export interface JobEmployee {
  citizenId: string
  license?: string
  name: string
  job: string
  grade: number
  gradeName: string
  phone?: string
  lastSeen?: Date
  }

// ==========================================
// ESX ADAPTER
// ==========================================

async function getEsxJobs(): Promise<NormalizedJob[]> {
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    // Get all jobs
    const [jobs] = await pool.query<RowDataPacket[]>(`
      SELECT name, label FROM jobs ORDER BY name
    `)

    // Get all job grades
    const [grades] = await pool.query<RowDataPacket[]>(`
      SELECT job_name, grade, name, label, salary FROM job_grades ORDER BY job_name, grade
    `)

    // Build normalized job list
    const jobMap = new Map<string, NormalizedJob>()

    for (const job of jobs) {
      jobMap.set(job.name, {
        name: job.name,
        label: job.label || job.name,
        grades: [],
      })
    }

    for (const grade of grades) {
      const job = jobMap.get(grade.job_name)
      if (job) {
        job.grades.push({
          grade: grade.grade,
          name: grade.name,
          label: grade.label || grade.name,
          salary: grade.salary || 0,
          isBoss: grade.grade === Math.max(...job.grades.map(g => g.grade), grade.grade),
        })
      }
    }

    // Update isBoss after all grades are added
    for (const job of jobMap.values()) {
      const maxGrade = Math.max(...job.grades.map(g => g.grade), 0)
      job.grades = job.grades.map(g => ({
        ...g,
        isBoss: g.grade === maxGrade,
      }))
    }

    return Array.from(jobMap.values())
  } catch (error) {
    console.error('[JobAdapter:ESX] Failed to get jobs:', error)
    return []
  }
}

async function getEsxJobEmployees(jobName: string): Promise<JobEmployee[]> {
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    const [employees] = await pool.query<RowDataPacket[]>(`
      SELECT 
        u.identifier as citizenId,
        CONCAT(u.firstname, ' ', u.lastname) as name,
        u.job,
        u.job_grade as grade,
        jg.label as gradeName,
        u.phone_number as phone
      FROM users u
      LEFT JOIN job_grades jg ON jg.job_name = u.job AND jg.grade = u.job_grade
      WHERE u.job = ?
      ORDER BY u.job_grade DESC, u.firstname
    `, [jobName])

    return employees.map(e => ({
      citizenId: e.citizenId,
      name: e.name || 'Unknown',
      job: e.job,
      grade: e.grade || 0,
      gradeName: e.gradeName || `Grade ${e.grade}`,
      phone: e.phone,
    }))
  } catch (error) {
    console.error('[JobAdapter:ESX] Failed to get employees:', error)
    return []
  }
}

// ==========================================
// QBCORE ADAPTER
// ==========================================

async function getQbCoreJobs(): Promise<NormalizedJob[]> {
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    // QBCore stores jobs in shared.lua or database
    // First try to get from database if available
    const [dbJobs] = await pool.query<RowDataPacket[]>(`
      SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(job, '$.name')) as name,
             JSON_UNQUOTE(JSON_EXTRACT(job, '$.label')) as label
      FROM players
      WHERE job IS NOT NULL
    `)

    // Get unique jobs and build grade structure from player data
    const jobMap = new Map<string, NormalizedJob>()

    for (const row of dbJobs) {
      if (row.name && row.name !== 'null' && !jobMap.has(row.name)) {
        jobMap.set(row.name, {
          name: row.name,
          label: row.label || row.name,
          grades: [],
        })
      }
    }

    // Get grade information from players
    const [gradeData] = await pool.query<RowDataPacket[]>(`
      SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(job, '$.name')) as jobName,
        JSON_UNQUOTE(JSON_EXTRACT(job, '$.grade.level')) as grade,
        JSON_UNQUOTE(JSON_EXTRACT(job, '$.grade.name')) as gradeName
      FROM players
      WHERE job IS NOT NULL
      GROUP BY jobName, grade, gradeName
      ORDER BY jobName, CAST(grade AS UNSIGNED)
    `)

    for (const grade of gradeData) {
      const job = jobMap.get(grade.jobName)
      if (job && grade.grade !== null) {
        const gradeNum = parseInt(grade.grade) || 0
        if (!job.grades.find(g => g.grade === gradeNum)) {
          job.grades.push({
            grade: gradeNum,
            name: grade.gradeName || `Grade ${gradeNum}`,
            label: grade.gradeName || `Grade ${gradeNum}`,
            salary: 0,
            isBoss: false,
          })
        }
      }
    }

    // Add default grades if none found
    for (const job of jobMap.values()) {
      if (job.grades.length === 0) {
        job.grades = [
          { grade: 0, name: 'Employee', label: 'Employee', salary: 0, isBoss: false },
          { grade: 1, name: 'Manager', label: 'Manager', salary: 0, isBoss: false },
          { grade: 2, name: 'Boss', label: 'Boss', salary: 0, isBoss: true },
        ]
      } else {
        // Mark highest grade as boss
        const maxGrade = Math.max(...job.grades.map(g => g.grade))
        job.grades = job.grades.map(g => ({
          ...g,
          isBoss: g.grade === maxGrade,
        }))
      }
    }

    // Add common jobs if not present
    const commonJobs = ['police', 'ambulance', 'mechanic', 'taxi', 'realestate', 'cardealer']
    for (const jobName of commonJobs) {
      if (!jobMap.has(jobName)) {
        jobMap.set(jobName, {
          name: jobName,
          label: jobName.charAt(0).toUpperCase() + jobName.slice(1),
          grades: [
            { grade: 0, name: 'Recruit', label: 'Recruit', salary: 0, isBoss: false },
            { grade: 1, name: 'Officer', label: 'Officer', salary: 0, isBoss: false },
            { grade: 2, name: 'Sergeant', label: 'Sergeant', salary: 0, isBoss: false },
            { grade: 3, name: 'Lieutenant', label: 'Lieutenant', salary: 0, isBoss: false },
            { grade: 4, name: 'Boss', label: 'Boss', salary: 0, isBoss: true },
          ],
        })
      }
    }

    return Array.from(jobMap.values())
  } catch (error) {
    console.error('[JobAdapter:QBCore] Failed to get jobs:', error)
    return []
  }
}

async function getQbCoreJobEmployees(jobName: string): Promise<JobEmployee[]> {
  const pool = await getGameDbPool()
  if (!pool) return []

  try {
    const [employees] = await pool.query<RowDataPacket[]>(`
      SELECT 
        citizenid,
        license,
        JSON_UNQUOTE(JSON_EXTRACT(charinfo, '$.firstname')) as firstname,
        JSON_UNQUOTE(JSON_EXTRACT(charinfo, '$.lastname')) as lastname,
        JSON_UNQUOTE(JSON_EXTRACT(charinfo, '$.phone')) as phone,
        JSON_UNQUOTE(JSON_EXTRACT(job, '$.name')) as job,
        CAST(JSON_UNQUOTE(JSON_EXTRACT(job, '$.grade.level')) AS UNSIGNED) as grade,
        JSON_UNQUOTE(JSON_EXTRACT(job, '$.grade.name')) as gradeName,
        last_updated
      FROM players
      WHERE JSON_UNQUOTE(JSON_EXTRACT(job, '$.name')) = ?
      ORDER BY grade DESC, firstname
    `, [jobName])

    return employees.map(e => ({
      citizenId: e.citizenid,
      license: e.license,
      name: `${e.firstname || ''} ${e.lastname || ''}`.trim() || 'Unknown',
      job: e.job,
      grade: e.grade || 0,
      gradeName: e.gradeName || `Grade ${e.grade}`,
      phone: e.phone,
      lastSeen: e.last_updated,
    }))
  } catch (error) {
    console.error('[JobAdapter:QBCore] Failed to get employees:', error)
    return []
  }
}

// ==========================================
// UNIFIED ADAPTER INTERFACE
// ==========================================

/**
 * Get all jobs from the game database (framework-agnostic)
 */
export async function getAllJobs(): Promise<NormalizedJob[]> {
  const config = await getGameDbConfig()
  if (!config) return []

  if (config.framework === 'QBCORE') {
    return getQbCoreJobs()
  } else {
    return getEsxJobs()
  }
}

/**
 * Get a specific job by name
 */
export async function getJobByName(jobName: string): Promise<NormalizedJob | null> {
  const jobs = await getAllJobs()
  return jobs.find(j => j.name === jobName) || null
}

/**
 * Get all employees for a job
 */
export async function getJobEmployees(jobName: string): Promise<JobEmployee[]> {
  const config = await getGameDbConfig()
  if (!config) return []

  if (config.framework === 'QBCORE') {
    return getQbCoreJobEmployees(jobName)
  } else {
    return getEsxJobEmployees(jobName)
  }
}

/**
 * Get employee count for a job
 */
export async function getJobEmployeeCount(jobName: string): Promise<number> {
  const config = await getGameDbConfig()
  if (!config) return 0

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

  try {
    if (config.framework === 'QBCORE') {
      const [rows] = await pool.query<RowDataPacket[]>(`
        SELECT COUNT(*) as count FROM players
        WHERE JSON_UNQUOTE(JSON_EXTRACT(job, '$.name')) = ?
      `, [jobName])
      return rows[0]?.count || 0
    } else {
      const [rows] = await pool.query<RowDataPacket[]>(`
        SELECT COUNT(*) as count FROM users WHERE job = ?
      `, [jobName])
      return rows[0]?.count || 0
    }
  } catch (error) {
    console.error('[JobAdapter] Failed to get employee count:', error)
    return 0
  }
}

/**
 * Change employee grade/rank
 */
export async function setEmployeeGrade(citizenId: string, jobName: string, newGrade: number): Promise<boolean> {
  const config = await getGameDbConfig()
  if (!config) return false

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

  try {
    if (config.framework === 'QBCORE') {
      // Get current job data
      const [current] = await pool.query<RowDataPacket[]>(`
        SELECT job FROM players WHERE citizenid = ?
      `, [citizenId])
      
      if (current.length === 0) return false

      const job = typeof current[0].job === 'string' ? JSON.parse(current[0].job) : current[0].job
      
      // Get grade info
      const jobInfo = await getJobByName(jobName)
      const gradeInfo = jobInfo?.grades.find(g => g.grade === newGrade)

      job.grade = {
        level: newGrade,
        name: gradeInfo?.name || `Grade ${newGrade}`,
      }

      await pool.query(`
        UPDATE players SET job = ? WHERE citizenid = ?
      `, [JSON.stringify(job), citizenId])
    } else {
      await pool.query(`
        UPDATE users SET job_grade = ? WHERE identifier = ? AND job = ?
      `, [newGrade, citizenId, jobName])
    }

    return true
  } catch (error) {
    console.error('[JobAdapter] Failed to set grade:', error)
    return false
  }
}

/**
 * Fire employee (set to unemployed)
 */
export async function fireEmployee(citizenId: string): Promise<boolean> {
  const config = await getGameDbConfig()
  if (!config) return false

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

  try {
    if (config.framework === 'QBCORE') {
      const unemployedJob = JSON.stringify({
        name: 'unemployed',
        label: 'Unemployed',
        payment: 0,
        type: 'none',
        onduty: false,
        isboss: false,
        grade: { level: 0, name: 'Unemployed' },
      })

      await pool.query(`
        UPDATE players SET job = ? WHERE citizenid = ?
      `, [unemployedJob, citizenId])
    } else {
      await pool.query(`
        UPDATE users SET job = 'unemployed', job_grade = 0 WHERE identifier = ?
      `, [citizenId])
    }

    return true
  } catch (error) {
    console.error('[JobAdapter] Failed to fire employee:', error)
    return false
  }
}

/**
 * Hire employee (set job)
 */
export async function hireEmployee(citizenId: string, jobName: string, grade: number = 0): Promise<boolean> {
  const config = await getGameDbConfig()
  if (!config) return false

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

  try {
    const jobInfo = await getJobByName(jobName)
    const gradeInfo = jobInfo?.grades.find(g => g.grade === grade)

    if (config.framework === 'QBCORE') {
      const newJob = JSON.stringify({
        name: jobName,
        label: jobInfo?.label || jobName,
        payment: gradeInfo?.salary || 0,
        type: 'job',
        onduty: false,
        isboss: gradeInfo?.isBoss || false,
        grade: { 
          level: grade, 
          name: gradeInfo?.name || `Grade ${grade}`,
        },
      })

      await pool.query(`
        UPDATE players SET job = ? WHERE citizenid = ?
      `, [newJob, citizenId])
    } else {
      await pool.query(`
        UPDATE users SET job = ?, job_grade = ? WHERE identifier = ?
      `, [jobName, grade, citizenId])
    }

    return true
  } catch (error) {
    console.error('[JobAdapter] Failed to hire employee:', error)
    return false
  }
}