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

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

export interface Character {
  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
    jailTime?: number
  }
  position?: {
    x: number
    y: number
    z: number
    heading?: number
  }
  lastPlayed?: Date | string
  playtime?: number
  isOnline?: boolean
}

export interface CharacterListItem {
  id: string
  citizenid?: string
  name: string
  firstName: string
  lastName: string
  job: string
  jobLabel: string
  lastPlayed?: Date | string
  isOnline?: boolean
}

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

type Framework = 'ESX' | 'QBCORE'

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

// ==========================================
// MULTI-CHARACTER QUERIES
// ==========================================

/**
 * Get all characters for a Discord ID
 * First looks up the license from identifier_mappings, then queries game DB
 */
export async function getCharactersByDiscordId(discordId: string): Promise<Character[]> {
  const framework = await getFramework()
  
  try {
    const pool = await getGameDbPool()
    if (!pool) return []

    if (framework === 'QBCORE') {
      return await getQBCoreCharactersByDiscord(discordId)
    } else {
      return await getESXCharactersByDiscord(discordId)
    }
  } catch (error) {
    console.error('[CharacterService] Error getting characters:', error)
    return []
  }
}

/**
 * Get all characters by license identifier
 */
export async function getCharactersByLicense(license: string): Promise<Character[]> {
  const framework = await getFramework()
  
  try {
    if (framework === 'QBCORE') {
      return await getQBCoreCharactersByLicense(license)
    } else {
      return await getESXCharactersByLicense(license)
    }
  } catch (error) {
    console.error('[CharacterService] Error getting characters by license:', error)
    return []
  }
}

/**
 * Get a specific character by ID
 */
export async function getCharacterById(characterId: string): Promise<Character | null> {
  const framework = await getFramework()
  
  try {
    if (framework === 'QBCORE') {
      return await getQBCoreCharacterById(characterId)
    } else {
      return await getESXCharacterById(characterId)
    }
  } catch (error) {
    console.error('[CharacterService] Error getting character:', error)
    return null
  }
}

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

async function getESXCharactersByDiscord(discordId: string): Promise<Character[]> {
  try {
    // First: Try the discord column directly (set by FiveM script)
    let rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users WHERE discord = ?`,
      [discordId]
    )
    
    if (rows && rows.length > 0) {
      return rows.map(parseESXCharacter)
    }
    
    // Fallback: Try finding in identifier field
    const discordIdentifier = `discord:${discordId}`
    rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users WHERE identifier LIKE ?`,
      [`%${discordIdentifier}%`]
    )
    
    return rows.map(parseESXCharacter)
  } catch (error) {
    console.error('[ESX] Error getting characters by Discord:', error)
    return []
  }
}

async function getESXCharactersByLicense(license: string): Promise<Character[]> {
  try {
    // ESX multi-char: identifiers like "license:xxx" or "license:xxx:char1"
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users WHERE identifier LIKE ?`,
      [`${license}%`]
    )
    
    return rows.map(parseESXCharacter)
  } catch (error) {
    console.error('[ESX] Error getting characters by license:', error)
    return []
  }
}

async function getESXCharacterById(characterId: string): Promise<Character | null> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM users WHERE identifier = ?`,
      [characterId]
    )
    
    if (!rows || rows.length === 0) return null
    return parseESXCharacter(rows[0])
  } catch (error) {
    console.error('[ESX] Error getting character:', error)
    return null
  }
}

function parseESXCharacter(row: RowDataPacket): Character {
  const accounts = parseJson(row.accounts)
  const position = parseJson(row.position)
  const metadata = parseJson(row.metadata)
  
  return {
    id: row.identifier,
    identifier: row.identifier,
    name: `${row.firstname || ''} ${row.lastname || ''}`.trim() || 'Unknown',
    firstName: row.firstname || '',
    lastName: row.lastname || '',
    dateOfBirth: row.dateofbirth,
    gender: row.sex,
    phone: row.phone_number,
    job: {
      name: row.job || 'unemployed',
      label: row.job_label || row.job || 'Unemployed',
      grade: row.job_grade || 0,
      gradeLabel: row.job_grade_label,
    },
    money: {
      cash: accounts?.money || accounts?.cash || 0,
      bank: accounts?.bank || 0,
      black: accounts?.black_money || accounts?.black || 0,
    },
    metadata: metadata ? {
      health: metadata.health,
      armor: metadata.armor,
      isDead: metadata.isDead,
    } : undefined,
    position: position ? {
      x: position.x,
      y: position.y,
      z: position.z,
      heading: position.heading,
    } : undefined,
    lastPlayed: row.last_login || row.updated_at,
    playtime: row.playtime || 0,
  }
}

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

async function getQBCoreCharactersByDiscord(discordId: string): Promise<Character[]> {
  try {
    // First: Try the discord column directly (set by FiveM script)
    let rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM players WHERE discord = ?`,
      [discordId]
    )
    
    if (rows && rows.length > 0) {
      return rows.map(parseQBCoreCharacter)
    }

    // Fallback: Try player_identifiers table
    const pool = await getGameDbPool()
    if (pool) {
      try {
        const [idRows] = await pool.query<RowDataPacket[]>(
          `SELECT p.* FROM players p 
           INNER JOIN player_identifiers pi ON p.citizenid = pi.citizenid 
           WHERE pi.identifier = ?`,
          [`discord:${discordId}`]
        )
        if (idRows && idRows.length > 0) {
          return idRows.map(parseQBCoreCharacter)
        }
      } catch {
        // Table doesn't exist, that's fine
      }
    }

    return []
  } catch (error) {
    console.error('[QBCore] Error getting characters by Discord:', error)
    return []
  }
}

async function getQBCoreCharactersByLicense(license: string): Promise<Character[]> {
  try {
    // QBCore stores all characters with same license
    const cleanLicense = license.replace('license:', '')
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM players WHERE license LIKE ?`,
      [`%${cleanLicense}%`]
    )
    
    return rows.map(parseQBCoreCharacter)
  } catch (error) {
    console.error('[QBCore] Error getting characters by license:', error)
    return []
  }
}

async function getQBCoreCharacterById(characterId: string): Promise<Character | null> {
  try {
    const rows = await queryGameDb<RowDataPacket[]>(
      `SELECT * FROM players WHERE citizenid = ?`,
      [characterId]
    )
    
    if (!rows || rows.length === 0) return null
    return parseQBCoreCharacter(rows[0])
  } catch (error) {
    console.error('[QBCore] Error getting character:', error)
    return null
  }
}

function parseQBCoreCharacter(row: RowDataPacket): Character {
  const charinfo = parseJson(row.charinfo)
  const money = parseJson(row.money)
  const job = parseJson(row.job)
  const gang = parseJson(row.gang)
  const metadata = parseJson(row.metadata)
  const position = parseJson(row.position)
  
  return {
    id: row.citizenid,
    identifier: row.license,
    citizenid: row.citizenid,
    name: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim() || row.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,
      health: metadata.health,
      isDead: metadata.isdead,
      inJail: metadata.injail,
      jailTime: metadata.jailttime,
    } : undefined,
    position: position ? {
      x: position.x,
      y: position.y,
      z: position.z,
      heading: position.w,
    } : undefined,
    lastPlayed: row.last_updated,
    playtime: row.playtime || 0,
  }
}

// ==========================================
// ALL CHARACTERS (ADMIN)
// ==========================================

/**
 * Get all characters in the database (for admin view)
 */
export async function getAllCharacters(limit = 100, offset = 0, search?: string): Promise<{ characters: CharacterListItem[], total: number }> {
  const framework = await getFramework()
  
  try {
    if (framework === 'QBCORE') {
      return await getAllQBCoreCharacters(limit, offset, search)
    } else {
      return await getAllESXCharacters(limit, offset, search)
    }
  } catch (error) {
    console.error('[CharacterService] Error getting all characters:', error)
    return { characters: [], total: 0 }
  }
}

async function getAllESXCharacters(limit: number, offset: number, search?: string): Promise<{ characters: CharacterListItem[], total: number }> {
  try {
    let query = `SELECT identifier, firstname, lastname, job, job_grade, last_login FROM users`
    let countQuery = `SELECT COUNT(*) as total FROM users`
    const params: (string | number)[] = []
    
    if (search) {
      const searchCondition = ` WHERE firstname LIKE ? OR lastname LIKE ? OR identifier LIKE ?`
      query += searchCondition
      countQuery += searchCondition
      params.push(`%${search}%`, `%${search}%`, `%${search}%`)
    }
    
    query += ` ORDER BY last_login DESC LIMIT ? OFFSET ?`
    
    const [rows, countRows] = await Promise.all([
      queryGameDb<RowDataPacket[]>(query, [...params, limit, offset]),
      queryGameDb<RowDataPacket[]>(countQuery, params),
    ])
    
    return {
      characters: rows.map(row => ({
        id: row.identifier,
        name: `${row.firstname || ''} ${row.lastname || ''}`.trim() || 'Unknown',
        firstName: row.firstname || '',
        lastName: row.lastname || '',
        job: row.job || 'unemployed',
        jobLabel: row.job || 'Unemployed',
        lastPlayed: row.last_login,
      })),
      total: countRows[0]?.total || 0,
    }
  } catch (error) {
    console.error('[ESX] Error getting all characters:', error)
    return { characters: [], total: 0 }
  }
}

async function getAllQBCoreCharacters(limit: number, offset: number, search?: string): Promise<{ characters: CharacterListItem[], total: number }> {
  try {
    let query = `SELECT citizenid, license, charinfo, job, last_updated FROM players`
    let countQuery = `SELECT COUNT(*) as total FROM players`
    const params: (string | number)[] = []
    
    if (search) {
      const searchCondition = ` WHERE citizenid LIKE ? OR JSON_EXTRACT(charinfo, '$.firstname') LIKE ? OR JSON_EXTRACT(charinfo, '$.lastname') LIKE ?`
      query += searchCondition
      countQuery += searchCondition
      params.push(`%${search}%`, `%${search}%`, `%${search}%`)
    }
    
    query += ` ORDER BY last_updated DESC LIMIT ? OFFSET ?`
    
    const [rows, countRows] = await Promise.all([
      queryGameDb<RowDataPacket[]>(query, [...params, limit, offset]),
      queryGameDb<RowDataPacket[]>(countQuery, params),
    ])
    
    return {
      characters: rows.map(row => {
        const charinfo = parseJson(row.charinfo)
        const job = parseJson(row.job)
        return {
          id: row.citizenid,
          citizenid: row.citizenid,
          name: `${charinfo?.firstname || ''} ${charinfo?.lastname || ''}`.trim() || 'Unknown',
          firstName: charinfo?.firstname || '',
          lastName: charinfo?.lastname || '',
          job: job?.name || 'unemployed',
          jobLabel: job?.label || 'Unemployed',
          lastPlayed: row.last_updated,
        }
      }),
      total: countRows[0]?.total || 0,
    }
  } catch (error) {
    console.error('[QBCore] Error getting all characters:', error)
    return { characters: [], total: 0 }
  }
}

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

/**
 * Get live character data (for 10-second polling)
 */
export async function getCharacterLiveData(characterId: string): Promise<Character | null> {
  return getCharacterById(characterId)
}

// ==========================================
// 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
}