ddstats-server/libs/database/wrapper.js

import { sqlite, skinDB } from './init.js'
import { simpleSanitize } from './searcher.js'

const entriesPerPage = process.env.ENTRIES_PER_PAGE ?? 50

/**
 * This function returns all data pertaining a certain player
 * 
 * @param {string} player The player to fetch

 * @returns {object} An object containing the players data
 */
export function player(player) {
    /* Misc */
    const firstFinish = sqlite.prepare(`SELECT map, time, date, server FROM race WHERE player = ? ORDER BY date ASC LIMIT 1`).get(player)

    if(!firstFinish)
        return undefined

    /* Points */
    let points = {}
    let rank = {}

    const pointsData = sqlite.prepare(`SELECT * FROM points WHERE player = ?`)

    for (const pointsType of pointsData.iterate(player)) {
        rank[pointsType.type] = pointsType.rank
    }
    for (const pointsType of pointsData.iterate(player)) {
        points[pointsType.type] = pointsType.points
    }

    return {
        player,
        firstFinish,
        points,
        rank,
    }
}

/**
 * This function returns all data pertaining a certain map
 * 
 * @param {string} map The map to fetch

 * @returns {object} An object containing map data
 */
export function map(map) {
    const a = sqlite.prepare(`
        SELECT * FROM maps WHERE map = ?
    `).get(map)

    return prettyifyMap(a)
}

/**
 * This function returns all data pertaining to all maps

 * @returns {array} An array contaning all map objects
 */
export function allMaps() {
    // This is kinda dumb
    return searchMap(
            "", 
            ["Novice", "Moderate", "Brutal" ,"Insane", "Dummy", "DDmaX", "Oldschool", "Solo", "Race", "Fun"], 
            [0,1,2,3,4,5], "release", "desc", 1)
}

/**
 * This function returns all data pertaining a certain map
 * 
 * @param {string} map The map to fetch

 * @returns {object} An object containing map data
 */
 export function prettyifyMap(a) {
    if(!a)
        return undefined

    let output = {
        map: a.map,
        category: a.category,
        points: a.points,
        stars: a.stars,
        release: a.release,
        mappers: a.mapper.split(" & "),

        times: {
            average: a.avgTime,
            median: a.medianTime,
            topTime: a.topTime,
            topTimeTeam: (a.topTeamTime != -1) ? a.topTeamTime : undefined,
        },
        finishes: {
            total: a.finishesTotal,
            team: a.finishesTeam,
            unique: a.finishesUnique,
        }
    }

    return output
}

/**
 * This function returns the race leaderboard for a map
 * 
 * @param {string} map The map to check
 * @param {number} start At which rank the leaderboard should begin
 * @param {number} end At which rank the leaderboard should end

 * @returns {array} An array containing the leaderboard
 */
export function leaderboardRace(map, start, end) {
    const leaderboard = sqlite.prepare(`
        SELECT rank, time, date, player, server FROM rankings WHERE map = ? LIMIT ?, ?`)
    .all(map, start -1, end)

    return leaderboard
}

/**
 * This function returns the teamrace leaderboard for a map
 * 
 * @param {string} map The map to check
 * @param {number} start At which rank the leaderboard should begin
 * @param {number} end At which rank the leaderboard should end

 * @returns {array} An array containing the leaderboard
 */
 export function leaderboardTeamrace(map, start, end) {
    // TODO: Optimize array creation of players
    let leaderboard = []

    const a = sqlite.prepare(`
        SELECT teamrank, time, date, player, server FROM teamrankings WHERE map = ? AND teamrank >= ? AND teamrank <= ? GROUP BY teamrank`)

    for(const teamrank of a.iterate(map, start, end)) {
        let players = []
        const b = sqlite.prepare(`SELECT player FROM teamrankings WHERE map = ? AND teamrank = ?`)

        if(!b)
            return undefined

        for(const player of b.iterate(map, teamrank.teamrank)) {
            players.push(player.player)
        }
        leaderboard.push({
            teamrank: teamrank.teamrank,
            time: teamrank.time,
            date: teamrank.date,
            server: teamrank.server,
            players: players,
        })
    }
    return leaderboard
}

/**
 * This function returns the points leaderboard for a specific type
 * (points, pointsRank, pointsTeam, pointsThisWeek, pointsThisMonth)
 * 
 * @param {string} type Which type of points to fetch
 * @param {string} region Which region should be included (Global, Europe, Asia, SA, NA, Africa, ME, OLD, Other)
 * @param {number} start At which rank the leaderboard should begin
 * @param {number} end At which rank the leaderboard should end

 * @returns {array} An array containing the leaderboard
 */
export function leaderboardPoints(type, region, start, end) {
    let output = []
    let leaderboard

    if(region == "Global") {
        leaderboard = sqlite.prepare(`
            SELECT rank, region, player, points FROM points WHERE type = ? AND rank >= ? AND rank <= ? ORDER BY rank`)
        .all(type, start, end)
    }
    else {
        leaderboard = sqlite.prepare(`
            SELECT rank, region, player, points FROM points WHERE type = ? AND region = ? ORDER BY rank ASC LIMIT ?, ${end}`)
        .all(type, region, start - 1)
    }
    let rank = 1
    for (const entry of leaderboard) {
        let flag = skinDB.prepare(`SELECT COUNT(flag) as a, flag FROM skindata WHERE player = ? GROUP BY flag ORDER BY a DESC`).get(entry.player)?.flag ?? "default"

        output.push({ rank: rank, global: entry.rank, player: entry.player, points: entry.points, region: entry.region, flag: flag })
        ++rank
    }
    return output
}

/**
 * This function returns all finished maps by a specific player
 * togheter with their respective rank, teamrank, amount of finishes.
 * Finishes are grouped by map category (Novice, Brutal)
 * 
 * @param {string} player The player to check

 * @returns {object} An object containing all finishes grouped by category
 */
export function finishedMaps(player) {
    const finishesStmt = sqlite.prepare(
    `
        SELECT a.map,
               a.category,
               a.points,
               a.rank,
               b.teamrank,
               a.finishes
        FROM   rankings AS a
            LEFT OUTER JOIN teamrankings AS b
                        ON a.player = b.player
                            AND a.category = b.category
                            AND a.map = b.map
        WHERE  a.player = ?
    `)

    if(!finishesStmt)
        return undefined

    let finishes = {
        Novice: [],
        Moderate: [],
        Brutal: [],
        Insane: [],
        Dummy: [],
        DDmaX: [],
        Oldschool: [],
        Solo: [],
        Race: [],
        Fun: []
    }
    for (const finish of finishesStmt.iterate(player)) {
        finishes[finish.category].push(finish)
    }

    return finishes
}

/**
 * This function returns all unfinished maps by a specific player
 * togheter with category, points, finishTotal and medianTime.
 * Maps are grouped by the map category (Novice, Brutal)
 * 
 * @param {string} player The player to check

 * @returns {object} An object containing all unfinished maps
 */
 export function unfinishedMaps(player) {
    const maps = sqlite.prepare(
    `
        SELECT a.map,
               a.category,
               a.points,
               b.finishesTotal,
               b.medianTime
        FROM   (SELECT category,
                       map,
                       points
                FROM   maps
                WHERE  map NOT IN (SELECT map
                                FROM   rankings
                                WHERE  player = ? )) AS a
            JOIN maps AS b
                ON a.category = b.category
                    AND a.map = b.map
        ORDER  BY b.category ASC; 
    `)

    if(!maps)
        return undefined

    let unfinished = {
        Novice: [],
        Moderate: [],
        Brutal: [],
        Insane: [],
        Dummy: [],
        DDmaX: [],
        Oldschool: [],
        Solo: [],
        Race: [],
        Fun: []
    }
    for (const map of maps.iterate(player)) {
        unfinished[map.category].push(map)
    }

    return unfinished
}

export function graphMap(map, player) {
    let finishes

    if(player)
        finishes = sqlite.prepare(`SELECT * FROM race WHERE map = ? AND player = ? ORDER BY date`).all(map, player)
    else
        finishes = sqlite.prepare(`SELECT * FROM graphRecordCache WHERE map = ? ORDER BY date`).all(map)

    if(!finishes)
        return undefined

    let array = []
    let currentFinish
    let currentBest = 0

    for (const record of finishes) {
        currentFinish = record.time
        if (currentFinish <= currentBest || currentBest == 0) {
            currentBest = currentFinish
            array.push({ t: record.date, y: record.time, player: record.player})
        }
    }
    return array
}
/**
 * This searches the DB for maps
 * 
 * @param {string} query The query to search for
 * @param {array} categories Which categories to be searched ["Novice", "Insane"]
 * @param {array} stars Only include maps with a specific amount of stars [1, 5]
 * @param {string} sortBy What to sort after
 * @param {string} order "ASC" | "DESC"
 * @param {number} page Which page?
 * 
 * @returns {array} Returns an array of all maps found
 */
export function searchMap(query, categories, stars, sortBy, order, page) {
    const categoriesJoined = categories.join(',')
    const starsJoined = stars.join(',')

    let output = []
    // TODO: Would it be possible to NOT duplicate this?
    const mapCount = sqlite.prepare(`
        SELECT COUNT(*) as a FROM maps WHERE map LIKE '%${query}%' 
            AND ',' || ? || ',' LIKE '%,' || category || ',%'
            AND ',' || ? || ',' LIKE '%,' || stars || ',%'`)
        .get(categoriesJoined, starsJoined).a

    const maps = sqlite.prepare(`
        SELECT * FROM maps WHERE map LIKE '%${query}%' 
            AND ',' || ? || ',' LIKE '%,' || category || ',%'
            AND ',' || ? || ',' LIKE '%,' || stars || ',%'
            ORDER BY ${simpleSanitize(sortBy)} ${simpleSanitize(order)} LIMIT ${entriesPerPage * (page - 1)}, ${entriesPerPage}`)
        .all(categoriesJoined, starsJoined)

    const totalPages = Math.ceil(mapCount/50)
    const pageInfo = { page, mapCount: mapCount, totalPages }

    for(const map of maps) {
        output.push(prettyifyMap(map))
    }

    return { "pageInfo": pageInfo, "maps": output }
}

export function mapToRegion(srv) {
    if(srv == "RUS" || srv == "GER" || srv == "NLD" || srv == "TUR" || srv == "POL" || srv == "FRA")
        return "Europe"

    if(srv == "IND" || srv == "SGP" || srv == "CHN" || srv == "JAP" || srv == "KOR")
        return "Asia"

    if(srv == "COL" || srv == "ARG" || srv == "CHL" || srv == "BRA")
        return "SA"
    
    if(srv == "CAN" || srv == "USA")
        return "NA"

    if(srv == "ZAF")
        return "Africa"
    
    /* Middle east */
    if(srv == "KSA" || srv == "UAE" || srv == "IRN")
        return "ME"

    if(srv == "")
        return "OLD"

    return "Other"
}

export function playerServer(player) {
    /* This can be undefined if a player has changed their name */
    const server = sqlite.prepare(
        `SELECT server, COUNT(server) as a FROM rankings WHERE player = ? GROUP BY server ORDER BY a DESC`)
    .get(player)?.server ?? ""

    return server
}


export default {
    player,
    finishedMaps,
    unfinishedMaps,

    leaderboardPoints,
    leaderboardRace,
    leaderboardTeamrace,

    map,
    graphMap,
    allMaps,
    searchMap,

    mapToRegion,
    playerServer,
}