import { RankedRegion, RankedTierName } from "./constants"
import { Legend, LegendStats, WeaponStats } from "./types"

import db from "./db";
import { regions } from "helper";

async function getPatchDaysCondition(patch:string, tier:RankedTierName) {
  let currentPatch = (await db.query(`SELECT timestamp FROM cwr_patches WHERE changes=1 AND id='${patch}'`))?.[0]?.[0]
  if (currentPatch) currentPatch = currentPatch.timestamp
  if (!currentPatch) {
    let newTimestamp = (await db.query('SELECT MIN(timestamp) as timestamp FROM cwr_patches WHERE changes=1 LIMIT 1'))?.[0]?.[0]?.timestamp
    currentPatch = newTimestamp
}

  let foundPatch = (await db.query(`SELECT MIN(timestamp) as timestamp FROM cwr_patches WHERE changes=1 AND timestamp>${currentPatch} LIMIT 1`))?.[0]?.[0]
let nextPatch = foundPatch?.timestamp
  if (!nextPatch) nextPatch = Math.floor(Date.now() / 1000)

  const currentPatchDay = Math.floor(currentPatch / 60 / 60 / 24) + 1 // patch stats start next day
  const nextPatchDay = Math.floor(nextPatch / 60 / 60 / 24) + 1 // patch stats start next day
  let tierCondition = ((tier !== undefined) && tier.length > 0) ? `AND tier='${tier}'` : ""
  const result = `(day>${currentPatchDay} AND day<${nextPatchDay} ${tierCondition})`
  return result
}

async function getPatchGlobalInfo(daysCondition:string) {
  const totalGames = (await db.query(`SELECT SUM(games) as totalgames FROM cwr_stats WHERE ${daysCondition}`))?.[0]?.[0]?.totalgames
  const totalWins = (await db.query(`SELECT SUM(wins) as totalwins FROM cwr_stats WHERE ${daysCondition}`))?.[0]?.[0]?.totalwins
  let winRateBalance
  if (!totalWins) {
    winRateBalance = 1
  } else {
    // Because we're not counting 100% of the matches, and only higher elos, we will have more wins than losses. We use this variable to normalize the winrates
    winRateBalance = totalGames / totalWins / 2
  }

  const result = {
    winRateBalance,
    totalGames: totalGames || 0,
  }
  return result
}

async function getReqPatchAndTier(patch?:string, tier?:RankedTierName|string|null):Promise<{patch:string, tier:RankedTierName}> {
  let newPatch:string = "";
  let newTier:RankedTierName = "";
  const validTiers = new Set(['all', 'Diamond', 'Platinum', 'Gold', 'Silver'])
  
  if (tier && !validTiers.has(tier)) newTier = ""
  else if(tier && validTiers.has(tier)) newTier = tier
  if (!patch || patch === 'undefined') {
    newPatch = await getCurrentPatch()
  } else {
    newPatch = patch
  }
  return {
    patch: newPatch,
    tier: newTier,
  }
}

async function getCurrentPatch() {

  const tsNow = Math.floor(Date.now() / 1000)
  const res = (await db.query(
    `SELECT id FROM cwr_patches WHERE changes='1' AND timestamp<${tsNow}-60*60*24*2 ORDER BY timestamp DESC LIMIT 1`
  ))[0]
  let patch = res[0]?.id
  return patch
}
async function _formatRawStatsFromQuery(legendStats:LegendStats, daysCondition:string, patchGlobalInfo:any) {
if(!legendStats.games || !legendStats.damagedealt || !legendStats.matchtime) return null
  const data = {
      id: legendStats.id,
      games: legendStats.games,
      playrate: (Number(legendStats.games) / patchGlobalInfo.totalGames) * 100,
      winrate: Number(legendStats.winrate) * patchGlobalInfo.winRateBalance * 100,
      suicides: legendStats.suicides,
      damagedealt: legendStats.damagedealt,
      damagedealt_unarmed: legendStats.damagedealt_unarmed,
      damagedealt_gadgets: legendStats.damagedealt_gadgets,
      damagedealt_weaponone: legendStats.damagedealt_weaponone,
      damagedealt_weapontwo: legendStats.damagedealt_weapontwo,
      damagetaken: legendStats.damagetaken,
      matchtime: legendStats.matchtime,
      matchtime_weaponone: legendStats.matchtime_weaponone,
      matchtime_weapontwo: legendStats.matchtime_weapontwo,
      matchtime_unarmed: Number(legendStats.matchtime) - Number(legendStats.matchtime_weaponone) - Number(legendStats.matchtime_weapontwo),
      falls: legendStats.falls,
      kos: legendStats.kos,
      koweaponone: legendStats.koweaponone,
      koweapontwo: legendStats.koweapontwo,
      kounarmed: legendStats.kounarmed,
      kothrownitem: legendStats.kothrownitem,
      kogadget: legendStats.kogadgets,
    }
    const dataFloats = Object.fromEntries(
      Object.entries(data).map(([name, val]) => {
        val = parseFloat(parseFloat(val?.toString())?.toFixed(2))
        return [name, val]
      })
    )
    return dataFloats
  }
  async function getLegendStats(legendID:number, patch:string, tier:RankedTierName, patchGlobalInfo:any, daysCondition:string) {
  
    const legendAnd = `AND legend_id = ${legendID}`
    const legendStats = (await db.query(`SELECT
            legend_id AS id,
            SUM(games) as games,
            SUM(wins)/SUM(games) as winrate,
            SUM(suicides)/SUM(games) as suicides,
            SUM(falls)/SUM(games) as falls,
            SUM(kos)/SUM(games) as kos,
            SUM(damagedealt)/SUM(games) as damagedealt,
            SUM(kounarmed)/SUM(games) as kounarmed,
            SUM(kothrownitem)/SUM(games) as kothrownitem,
            SUM(kogadgets)/SUM(games) as kogadgets,
            SUM(koweapontwo)/SUM(games) as koweapontwo,
            SUM(koweaponone)/SUM(games) as koweaponone,
            SUM(damageunarmed)/SUM(games) as damagedealt_unarmed,
            SUM(damagegadgets)/SUM(games) as damagedealt_gadgets,
            SUM(damageweaponone)/SUM(games) as damagedealt_weaponone,
            SUM(damageweapontwo)/SUM(games) as damagedealt_weapontwo,
            SUM(damagetaken)/SUM(games) as damagetaken,
            SUM(matchtime)/SUM(games) as matchtime,
            SUM(timeheldweaponone)/SUM(games) as matchtime_weaponone,
            SUM(timeheldweapontwo)/SUM(games) as matchtime_weapontwo
        FROM cwr_stats WHERE ${daysCondition} ${legendAnd}
        GROUP BY legend_id`))?.[0]?.[0]
    if (!legendStats?.games) return {
      id: legendID,
      games: 0,
      playrate: 0,
      winrate: 0,
      suicides: 0,
      damagedealt: 0,
      damagedealt_unarmed: 0,
      damagedealt_gadgets: 0,
      damagedealt_weaponone: 0,
      damagedealt_weapontwo: 0,
      damagetaken: 0,
      matchtime: 0,
      matchtime_weaponone: 0,
      matchtime_weapontwo: 0,
      matchtime_unarmed: 0,
      falls: 0,
      kos: 0,
      koweaponone: 0,
      koweapontwo: 0,
      kounaramed: 0,
      kothrownitem: 0,
      kogadgets: 0,
    }
    return await _formatRawStatsFromQuery(legendStats, daysCondition, patchGlobalInfo)
  }

async function get_legends_stats(patch_param?:string, tier_param?:RankedTierName) {
    const { patch, tier } = await getReqPatchAndTier(patch_param, tier_param)
    const legends = (await db.query('SELECT * FROM cwr_legends ORDER BY bio_name'))?.[0]
    const daysCondition = await getPatchDaysCondition(patch, tier)
    const patchGlobalInfo = await getPatchGlobalInfo(daysCondition)
    const legendData = await Promise.all(legends.map((legend:Legend) => getLegendStats(legend.legend_id, patch, tier, patchGlobalInfo, daysCondition)))
    return legendData
}


  async function get_weapon_stats(patch_param?:string, tier_param?:string|null) {

    const { patch, tier } = await getReqPatchAndTier(patch_param, tier_param)
    const weapons = new Set()
    const daysCondition = await getPatchDaysCondition(patch, tier)
    const patchGlobalInfo = await getPatchGlobalInfo(daysCondition)
    const weaponsQuery = (await db.query('SELECT weapon_one, weapon_two FROM cwr_legends ORDER BY legend_id ASC'))[0]
    weaponsQuery.forEach((legend:Legend) => {
      weapons.add(legend.weapon_one)
      weapons.add(legend.weapon_two)
    })
    const weaponsResult:Array<WeaponStats> = []
    await Promise.all(
      Array.from(weapons.values()).map(async weaponID => {
        // console.log(`SELECT SUM(games) as num FROM cwr_stats WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}') AND ${daysCondition}`)
        const {games, matchTime, legends, winrate} = (await db.query(
          `SELECT SUM(games) as games, SUM(wins)/SUM(games) as winrate, SUM(matchtime)/SUM(games) as matchTime, 
          (SELECT COUNT(legend_id) FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}') as legends FROM cwr_stats
          WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}' AND ${daysCondition})
          AND ${daysCondition}`
        ))?.[0]?.[0]
        
        if (!games) return
        // const legends = (await db.query(
        //   `SELECT COUNT(legend_id) as num FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}'`
        // ))?.[0]?.[0]?.num
        // const winrate = (await db.query(
        //   `SELECT SUM(wins)/${games} as num FROM cwr_stats WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}') AND ${daysCondition}`
        // ))?.[0]?.[0]?.num
        const {damageWeaponOne, timeheld1} = (await db.query(
          `
SELECT SUM(damageweaponone)/${games} as damageWeaponOne, SUM(timeheldweaponone)/${games} as timeheld1 FROM cwr_stats
WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}') AND ${daysCondition}
          `
        ))?.[0]?.[0]
        const {damageWeaponTwo, timeheld2} = (await db.query(
`
SELECT SUM(damageweapontwo)/${games} as damageWeaponTwo, SUM(timeheldweapontwo)/${games} as timeheld2 FROM cwr_stats
WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}') AND ${daysCondition}

`
        ))?.[0]?.[0]
        // const matchTime = (await db.query(
        //   `SELECT SUM(matchtime)/${games} as num FROM cwr_stats WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}' OR weapon_two='${weaponID}') AND ${daysCondition}`
        // ))?.[0]?.[0]?.num
        // const timeheld1 = (await db.query(
        //   `SELECT SUM(timeheldweaponone)/${games} as num FROM cwr_stats WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_one='${weaponID}') AND ${daysCondition}`
        // ))?.[0]?.[0]?.num
        // const timeheld2 = (await db.query(
        //   `SELECT SUM(timeheldweapontwo)/${games} as num FROM cwr_stats WHERE legend_id IN (SELECT legend_id FROM cwr_legends WHERE weapon_two='${weaponID}') AND ${daysCondition}`
        // ))?.[0]?.[0]?.num
  
        const damageDealt = parseFloat(damageWeaponOne || 0) + parseFloat(damageWeaponTwo || 0)
        const playrate = (games / legends / patchGlobalInfo.totalGames) * 100
        const timeHeld = parseFloat(timeheld1 || 0) + parseFloat(timeheld2 || 0)
  
        const weaponData = {
          weapon: weaponID,
          legends,
          playrate: playrate.toFixed(2),
          winrate: (winrate * patchGlobalInfo.winRateBalance * 100).toFixed(2),
          dmg: damageDealt.toFixed(2),
          match_time: matchTime,
          timeheld: timeHeld,
          games: games
        }
        const dataFloats:any = Object.fromEntries(
          Object.entries(weaponData).map(([name, val]) => {
            if (name !== 'weapon') {
              val = parseFloat(parseFloat(val).toFixed(2))
            }
            return [name, val]
          })
        )
  
        weaponsResult.push(dataFloats)
      })
    )
  return weaponsResult
}
async function get_patches(patch_param?:string, tier_param?:string) {
    const { tier } = await getReqPatchAndTier(patch_param, tier_param)
    let patches = (await db.query('SELECT id FROM cwr_patches WHERE changes=1 ORDER BY timestamp DESC LIMIT 10'))?.[0]
    patches = await Promise.all(
      patches.map(async (patch:any) => {
        const daysCondition = await getPatchDaysCondition(patch.id, tier)
        const games = (await db.query(`SELECT SUM(games) as count FROM cwr_stats WHERE ${daysCondition}`))?.[0]?.[0]?.count
        patch.games = parseInt(games) || 0
        return patch
      })
    )
return patches
}
async function get_legend_rankings(legendID:number|string, sortBy:"mastery"|"peak_elo"|"elo"|string, region?:RankedRegion|string) {
  let players;
  let regionCondition = ""
  if (region && regions?.includes(region.toLowerCase())) {
    regionCondition = ` AND region='${region?.toUpperCase()}'`
  }

  switch (sortBy) {
    case 'mastery': {
      players = (await db.query(
        `SELECT cwr_player_legends.brawlhalla_id, cwr_players.name, cwr_players.region, cwr_players.rating, cwr_players.wins, cwr_players.games, cwr_player_legends.level, cwr_player_legends.xp, cwr_players.peak_rating FROM cwr_player_legends 
                LEFT JOIN cwr_players ON cwr_players.brawlhalla_id=cwr_player_legends.brawlhalla_id
                WHERE cwr_player_legends.legend_id=${legendID}${regionCondition} ORDER BY cwr_player_legends.xp DESC LIMIT 50`
      ))?.[0]
      break
    }
    case 'elo': {
      const orderField = 'rating'
      players = (await db.query(
        `SELECT cwr_player_ranked_legends.brawlhalla_id, cwr_players.name, cwr_players.region, cwr_player_ranked_legends.${orderField}, cwr_player_ranked_legends.wins, cwr_player_ranked_legends.games, cwr_player_legends.level, cwr_player_legends.xp, cwr_player_ranked_legends.peak_rating FROM cwr_player_ranked_legends 
          LEFT JOIN cwr_players ON cwr_players.brawlhalla_id=cwr_player_ranked_legends.brawlhalla_id
          LEFT JOIN cwr_player_legends ON cwr_player_legends.brawlhalla_id=cwr_player_ranked_legends.brawlhalla_id AND cwr_player_legends.legend_id=cwr_player_ranked_legends.legend_id
          WHERE cwr_player_ranked_legends.legend_id=${legendID}${regionCondition} ORDER BY cwr_player_ranked_legends.${orderField} DESC LIMIT 50`
      ))?.[0]
      break;
    }
    case 'peak_elo': {
      const orderField = 'peak_rating'
      players = (await db.query(
        `SELECT cwr_player_ranked_legends.brawlhalla_id, cwr_players.name, cwr_players.region, cwr_player_ranked_legends.${orderField}, cwr_player_ranked_legends.wins, cwr_player_ranked_legends.games, cwr_player_legends.level, cwr_player_legends.xp, cwr_player_ranked_legends.rating FROM cwr_player_ranked_legends
          LEFT JOIN cwr_players ON cwr_players.brawlhalla_id=cwr_player_ranked_legends.brawlhalla_id
          LEFT JOIN cwr_player_legends ON cwr_player_legends.brawlhalla_id=cwr_player_ranked_legends.brawlhalla_id AND cwr_player_legends.legend_id=cwr_player_ranked_legends.legend_id
          WHERE cwr_player_ranked_legends.legend_id=${legendID}${regionCondition} ORDER BY cwr_player_ranked_legends.${orderField} DESC LIMIT 50`
      ))?.[0]
      break;
    }
    default: {
      return {
        error: 'Invalid sort type'
      }
    }
  }
  return players
}
export {
    getPatchDaysCondition,
    getReqPatchAndTier,
    getCurrentPatch,
    getPatchGlobalInfo,
    _formatRawStatsFromQuery,
    get_weapon_stats,
    get_legends_stats,
    get_patches,
    get_legend_rankings

}