<?php
/**
 * Created by PhpStorm.
 * User: leanderschmedemann
 * Date: 10.04.17
 * Time: 13:24
 */

require_once('includes.inc.php');
$data = array();
$error = 0;
$cid = isset($_GET['category']) ?
    $_GET['category'] : -1;
$limit = isset($_GET['limit']) ?
    $_GET['limit'] : 10;
$offset = isset($_GET['offset']) ?
    $_GET['offset'] : 0;
$db = new Database();

if($cid == -1) {
    $overview = $db->query(
        'SELECT "name", COALESCE("highscore", -1) as "highscore"
        FROM "Users"
        ORDER BY COALESCE("highscore", -1) DESC, "name" ASC
        LIMIT $1 OFFSET $2',
        array($limit, $offset)
    )['data'];
    foreach($overview as $row) {
        $name = $row['name'];
        $score = $row['highscore'];
        $place = $db->query(
            'SELECT COUNT(*)
            FROM "Users"
            WHERE "highscore" > $1',
            array($score)
        )['data'][0]['count'] + 1;
        $data[] = array(
            'place' => $place,
            'name' => $name,
            'score' => $score
        );
    }
} else {
    $overview = $db->query(
        'SELECT u."name", COALESCE(h."score", -1) AS "c_score"
        FROM "Users" u LEFT OUTER JOIN
        (
            SELECT "Users_ID", "score"
            FROM "Highscores"
            WHERE "Categories_ID" = $1
        ) h
        ON u."ID" = h."Users_ID"
        ORDER BY "c_score" DESC, u."name" ASC
        LIMIT $2 OFFSET $3',
        array($cid, $limit, $offset)
    )['data'];
    foreach($overview as $row) {
        $name = $row['name'];
        $score = $row['c_score'];
        $place = $db->query(
            'SELECT COUNT(*)
            FROM "Highscores"
            WHERE "score" > $1 AND "Categories_ID" = $2',
            array($score, $cid)
        )['data'][0]['count'] + 1;
        $data[] = array(
            'place' => $place,
            'name' => $name,
            'score' => $score
        );
    }
}

$response = Database::create_response($data, $error);
print_r($response);