add /admin/stats/

6 files changed, 159 insertions, 4 deletions

diff --git a/services/app/src/App.php b/services/app/src/App.php
index 77f24be..b2ffba0 100644
--- a/services/app/src/App.php
+++ b/services/app/src/App.php
@@ -142,6 +142,8 @@ final class App
                     });
                 });
             });
+
+            $group->get('/stats/', $this->handleAdminStats(...))->setName('admin_stats');
         })
         ->add(AdminRequiredMiddleware::create($app))
         ->add(AuthRequiredMiddleware::create($app, 'login'));
@@ -865,6 +867,33 @@ final class App
         return $this->makeRedirectResponse($response, $routeParser->urlFor('admin_testcase_list', ['qslug' => $qslug]));
     }
 
+    private function handleAdminStats(
+        ServerRequestInterface $request,
+        ResponseInterface $response,
+        QuizRepository $quizRepo,
+        AnswerRepository $answerRepo,
+    ): ResponseInterface {
+        $quizzes = $quizRepo->listAll();
+        $rankings = [];
+        $best_codes = [];
+        foreach ($quizzes as $quiz) {
+            $rankings[] = $answerRepo->getRankingByBestScores($quiz->quiz_id, upto: 3);
+            $best_codes[] = $answerRepo->getBestCode($quiz->quiz_id) ?? '<no answers>';
+        }
+
+        $n_attendees = $answerRepo->countUniqueAuthors();
+        $n_answers = $answerRepo->countAll();
+
+        return $this->render($request, $response, 'admin_stats.html.twig', [
+            'page_title' => '管理画面 - 各種統計',
+            'n_attendees' => $n_attendees,
+            'n_answers' => $n_answers,
+            'quizzes' => $quizzes,
+            'rankings' => $rankings,
+            'best_codes' => $best_codes,
+        ]);
+    }
+
     private function handleApiAnswerStatuses(
         string $aid,
         ServerRequestInterface $request,
diff --git a/services/app/src/Repositories/AnswerRepository.php b/services/app/src/Repositories/AnswerRepository.php
index 77a0cc8..1fa3e08 100644
--- a/services/app/src/Repositories/AnswerRepository.php
+++ b/services/app/src/Repositories/AnswerRepository.php
@@ -118,6 +118,58 @@ final class AnswerRepository
     }
 
     /**
+     * @param positive-int $upto
+     * @return Answer[]
+     */
+    public function getRankingByBestScores(int $quiz_id, int $upto, bool $show_admin = false): array
+    {
+        $q = $this->conn
+            ->query()
+            ->select('answers')
+            ->leftJoin('users', 'answers.author_id = users.user_id')
+            ->fields([
+                ...self::ANSWER_FIELDS,
+                ...self::ANSWER_JOIN_USER_FIELDS,
+                'ROW_NUMBER() OVER(PARTITION BY answers.author_id ORDER BY answers.code_size ASC, answers.submitted_at ASC) AS r',
+            ])
+            ->where(
+                'quiz_id = :quiz_id AND execution_status = :execution_status'
+                . ($show_admin ? '' : ' AND users.is_admin = FALSE')
+            );
+
+        $result = $this->conn
+            ->query()
+            ->select($q)
+            ->fields([
+                ...self::ANSWER_FIELDS,
+                'author_name',
+                'author_is_admin',
+            ])
+            ->where('r = 1')
+            ->orderBy([['code_size', 'ASC'], ['submitted_at', 'ASC']])
+            ->execute(['quiz_id' => $quiz_id, 'execution_status' => AggregatedExecutionStatus::OK->toInt()]);
+        return array_map($this->mapRawRowToAnswer(...), $result);
+    }
+
+    public function getBestCode(int $quiz_id, bool $show_admin = false): ?string
+    {
+        $result = $this->conn
+            ->query()
+            ->select('answers')
+            ->leftJoin('users', 'answers.author_id = users.user_id')
+            ->fields([...self::ANSWER_FIELDS, ...self::ANSWER_JOIN_USER_FIELDS])
+            ->where(
+                'quiz_id = :quiz_id AND execution_status = :execution_status'
+                . ($show_admin ? '' : ' AND users.is_admin = FALSE')
+            )
+            ->orderBy([['code_size', 'ASC'], ['submitted_at', 'ASC']])
+            ->first()
+            ->execute(['quiz_id' => $quiz_id, 'execution_status' => AggregatedExecutionStatus::OK->toInt()]);
+
+        return isset($result) ? $this->mapRawRowToAnswer($result)->code : null;
+    }
+
+    /**
      * @return Answer[]
      */
     public function listAllCorrectAnswers(int $quiz_id, bool $show_admin = false): array
@@ -136,6 +188,34 @@ final class AnswerRepository
         return array_map($this->mapRawRowToAnswer(...), $result);
     }
 
+    public function countUniqueAuthors(bool $show_admin = false): int
+    {
+        $result = $this->conn
+            ->query()
+            ->select('answers')
+            ->leftJoin('users', 'answers.author_id = users.user_id')
+            ->fields(['COUNT(DISTINCT author_id) AS count'])
+            ->where($show_admin ? '' : 'users.is_admin = FALSE')
+            ->first()
+            ->execute();
+        assert(isset($result['count']));
+        return (int) $result['count'];
+    }
+
+    public function countAll(bool $show_admin = false): int
+    {
+        $result = $this->conn
+            ->query()
+            ->select('answers')
+            ->leftJoin('users', 'answers.author_id = users.user_id')
+            ->fields(['COUNT(*) AS count'])
+            ->where($show_admin ? '' : 'users.is_admin = FALSE')
+            ->first()
+            ->execute();
+        assert(isset($result['count']));
+        return (int) $result['count'];
+    }
+
     public function create(
         int $quiz_id,
         int $author_id,
diff --git a/services/app/src/Sql/Internal/Select.php b/services/app/src/Sql/Internal/Select.php
index cf3f77a..d5943aa 100644
--- a/services/app/src/Sql/Internal/Select.php
+++ b/services/app/src/Sql/Internal/Select.php
@@ -33,7 +33,7 @@ final class Select
 
     public function __construct(
         private readonly QueryBuilder $sql,
-        private readonly string $table,
+        private readonly string|Select $table,
     ) {
     }
 
@@ -94,7 +94,7 @@ final class Select
     /**
      * @internal
      */
-    public function _getTable(): string
+    public function _getTable(): string|Select
     {
         return $this->table;
     }
diff --git a/services/app/src/Sql/QueryBuilder.php b/services/app/src/Sql/QueryBuilder.php
index 3a5443b..f6aee00 100644
--- a/services/app/src/Sql/QueryBuilder.php
+++ b/services/app/src/Sql/QueryBuilder.php
@@ -27,7 +27,7 @@ final class QueryBuilder
     ) {
     }
 
-    public function select(string $table): Select
+    public function select(string|Select $table): Select
     {
         return new Select($this, $table);
     }
@@ -57,6 +57,11 @@ final class QueryBuilder
         $this->conn->exec($sql);
     }
 
+    public function raw(string $sql): void
+    {
+        $this->conn->exec($sql);
+    }
+
     /**
      * @internal
      * @param Select $select
@@ -87,7 +92,11 @@ final class QueryBuilder
 
         return "SELECT " .
             implode(', ', $fields) .
-            " FROM $table" .
+            (
+                $table instanceof Select
+                    ? " FROM (" . $this->compileSelect($table) . ")"
+                    : " FROM $table"
+            ) .
             ($join !== null ? " $join->type $join->table ON $join->on" : '') .
             ($where !== '' ? " WHERE $where" : '') .
             (
diff --git a/services/app/templates/admin_overview.html.twig b/services/app/templates/admin_overview.html.twig
index 2103616..6256165 100644
--- a/services/app/templates/admin_overview.html.twig
+++ b/services/app/templates/admin_overview.html.twig
@@ -10,4 +10,7 @@
   <div>
     <a href="{{ url_for('admin_quiz_list') }}">問題一覧</a>
   </div>
+  <div>
+    <a href="{{ url_for('admin_stats') }}">各種統計</a>
+  </div>
 {% endblock %}
diff --git a/services/app/templates/admin_stats.html.twig b/services/app/templates/admin_stats.html.twig
new file mode 100644
index 0000000..7b1b2fa
--- /dev/null
+++ b/services/app/templates/admin_stats.html.twig
@@ -0,0 +1,34 @@
+{% extends '_page.html.twig' %}
+
+{% block content %}
+  <p>
+    このページは管理画面です
+  </p>
+  <h2>統計</h2>
+  <ul>
+    <li>参加者数: {{ n_attendees }} 名</li>
+    <li>回答数: {{ n_answers }}</li>
+    <li>好成績者
+      <ul>
+        {% for quiz in quizzes %}
+          <li>第{{ loop.index }}問: {{ quiz.title }}
+            <ul>
+              {% for answer in rankings[loop.index - 1] %}
+                <li>{{ loop.index }}位: {{ answer.author_name }} ({{ answer.code_size }} byte)</li>
+              {% endfor %}
+            </ul>
+          </li>
+        {% endfor %}
+      </ul>
+    </li>
+  </ul>
+  <h2>問題</h2>
+  {% for quiz in quizzes %}
+    <h3>第{{ loop.index }}問: {{ quiz.title }}</h3>
+    <p>
+      {{ quiz.description }}
+    </p>
+    <h4>最短解</h4>
+    <pre><code class="hljs language-php">{{ best_codes[loop.index - 1] }}</code></pre>
+  {% endfor %}
+{% endblock %}