const Sequelize = require('sequelize');
const { Router } = require('express');
const { db, Hacker, ApplicationReview, HackerApplication } = require('js/server/models');
const { createHttpError } = require('./errors');

const statsRouter = new Router();

statsRouter.get('/', (req, res, next) => {

  // Get:
  // - The total number of applications
  // - The total number of sign ups
  // - The number of reviews (note that each application needs to be reviewed twice)
  // TODO: Get the numbers of reviews made per admin
  // - The number of applications that have been reviewed at least twice

  const hackerCountPromise = Hacker.count();
  const hackerApplicationCountPromise = HackerApplication.count();
  const reviewCountPromise = ApplicationReview.count();

  const applicationsReviewedQuery = 
    "SELECT COUNT(*) FROM (" + 
      "SELECT \"hackerApplicationId\", COUNT(id)" + 
      "FROM \"application-reviews\"" +
      "GROUP BY \"hackerApplicationId\"" +
    ") review_counts WHERE count >= 2";

  const applicationsReviewedCountPromise = 
  db.query(applicationsReviewedQuery, { type: db.QueryTypes.SELECT }).then((counts) => {
    // Get the number from the object that's returned
    return parseInt(counts[0].count);
  });

  const leaderboardQuery = `SELECT "admins"."name", COALESCE(reviews.count, 0) AS count, "admins"."id" FROM "admins" LEFT JOIN (SELECT "adminId", COUNT(DISTINCT "hackerApplicationId") FROM "application-reviews" GROUP BY "adminId") AS reviews ON "admins"."id" = "adminId" ORDER BY count DESC, "admins"."name" ASC`;

  const leaderboardPromise = db.query(leaderboardQuery, { type: db.QueryTypes.SELECT });

  Promise.all([
    hackerCountPromise,
    hackerApplicationCountPromise,
    reviewCountPromise,
    applicationsReviewedCountPromise,
    leaderboardPromise,
  ])
  .then(
    ([
      hackerCount,
      hackerApplicationCount,
      reviewCount,
      applicationsReviewedCount,
      leaderboard,
    ]) => {
      res.json({
        hackerCount,
        hackerApplicationCount,
        reviewCount,
        applicationsReviewedCount,
        leaderboard,
      });
    }
  ).catch(next);
});

module.exports = statsRouter;

@@ -1261,16 +1261,20 @@
 dQuery =
+%0A   
  %60SELECT
@@ -1346,16 +1346,22 @@
 id%22 FROM
+%0A     
  %22admins
@@ -1361,16 +1361,24 @@
 %22admins%22
+%0A       
  LEFT JO
@@ -1379,16 +1379,26 @@
 EFT JOIN
+%0A         
  (SELECT
@@ -1451,16 +1451,28 @@
 d%22) FROM
+%0A           
  %22applic
@@ -1485,16 +1485,26 @@
 reviews%22
+%0A         
  GROUP B
@@ -1526,16 +1526,24 @@
  reviews
+%0A       
  ON %22adm
@@ -1563,16 +1563,20 @@
 adminId%22
+%0A   
  ORDER B

Structure the leaderboard SQL query a little more readably