Use actual data for leaderboard

e407f10 and ce74a2c added a basic skelton. This patch refines some of
the styling and actually pulls data from the database.

3 files changed, 65 insertions, 42 deletions

diff --git a/app.py b/app.py
index 01d8bdd..e450381 100644
--- a/app.py
+++ b/app.py
@@ -10,6 +10,7 @@ import sqlite3
 from bottle.ext import sqlite
 from beaker.middleware import SessionMiddleware
 import functools
+import datetime
 
 load_dotenv()
 
@@ -54,7 +55,7 @@ cursor.executescript("""
         preferredRole VARCHAR(6) NOT NULL,
         joinTime INT NOT NULL                 -- unix timestamp
     );
-    INSERT OR IGNORE INTO users(userId, preferredRole, joinTime) VALUES (1165955606, 'dps', strftime('%s','now'));
+    INSERT OR IGNORE INTO users(username, userId, preferredRole, joinTime) VALUES ('linuspwn', 1165955606, 'dps', strftime('%s','now'));
 """)
 cursor.close()
 connection.close()
@@ -81,21 +82,34 @@ def index():
 
 @app.route("/leaderboards.html")
 def leaderboards(db: sqlite3.Connection):
-    # all_members = db.execute("SELECT name FROM members")
-    all_members = [
-        ["a", f"10 days", 1],
-        ["b", f"8 days", 2],
-        ["c", f"6 days", 3],
-        ["d", f"3 days", 4],
-        ["e", f"1 days", 5], 
-        ["f", f"1 days", 6],
-        ["g", f"1 days", 7],
-        ["h", f"1 days", 8],
-        ["i", f"1 days", 9],
-        ["j", f"1 days", 10], 
-        ["k", f"0 days", 11]
+    def human_time_since(timestamp: int):
+        dt = datetime.datetime.fromtimestamp(timestamp)
+        delta: datetime.timedelta = datetime.datetime.now() - dt
+
+        seconds = int(delta.total_seconds())
+        periods = [
+            ('year',        60*60*24*365),
+            ('month',       60*60*24*30),
+            ('day',         60*60*24),
+            ('hour',        60*60),
+            ('minute',      60),
         ]
-    return template("leaderboards.html", all_members=all_members)
+
+        strings=[]
+        for period_name, period_seconds in periods:
+            if seconds > period_seconds:
+                period_value , seconds = divmod(seconds, period_seconds)
+                has_s = 's' if period_value > 1 else ''
+                strings.append("%s %s%s" % (period_value, period_name, has_s))
+
+        if strings:
+            return ", ".join(strings)
+        else:
+            has_s = 's' if seconds > 1 else ''
+            return f"{seconds} second{has_s}"
+
+    all_members = db.execute("SELECT username, joinTime FROM users ORDER BY joinTime");
+    return template("leaderboards.html", all_members=all_members, human_time_since=human_time_since)
 
 @app.route("/login")
 def login():
@@ -119,7 +133,7 @@ def login_callback(db: sqlite3.Connection):
     # Ensure user in database
     row = db.execute("SELECT * FROM users WHERE userId = ?", [user_id]).fetchone()
     if row == None:
-        raise HTTPError(404, "User not found")
+        raise HTTPError(404, f"User with id {user_id} not found")
 
     # Store session for subsequent requests
     session = request.environ.get("beaker.session")
diff --git a/static/styles/leaderboards.css b/static/styles/leaderboards.css
index a7db6f8..82d7dda 100644
--- a/static/styles/leaderboards.css
+++ b/static/styles/leaderboards.css
@@ -1,20 +1,25 @@
-.leaderboard{
-    width: 100%;
+.leaderboard {
+    width: 80%;
+    margin: 0 auto;
 }
 
-.top_row {
+.leaderboard thead {
     background-color: #6e1818;
     text-align: left;
 }
 
-.row {
+.leaderboard tr {
     height: 50px;
 }
 
-.data {
+.leaderboard td {
     background-color: #1c1c1c;
 }
 
-.wrapper {
+.leaderboard td, .leaderboard th {
+    padding: 1rem;
+}
+
+.center {
     text-align: center;
-}
\ No newline at end of file
+}
diff --git a/views/leaderboards.html b/views/leaderboards.html
index 2e64c55..44bc374 100644
--- a/views/leaderboards.html
+++ b/views/leaderboards.html
@@ -5,22 +5,26 @@
 {% endblock %}
 
 {% block content %}
-    <div class="wrapper">
-        <h1>Leaderboard</h1>
-        <p>You can swap leadboard using the dropdown (not implemented)</p>    
-    </div>
-    <table class="leaderboard" cellspacing="0">
-        <tr class="top_row row">
-            <th>Character name</th>
-            <th>Time in guild</th>
-            <th>Ranking</th>
-        </tr>
-        {% for character in all_members %}
-        <tr class="row">
-            <td class="data">{{ character[0] }}</td>
-            <td class="data">{{ character[1] }}</td>
-            <td class="data">{{ character[2] }}</td>
-        </tr>
-        {% endfor %}
-    </table>
-{% endblock %}
\ No newline at end of file
+	<div class="center">
+		<h1>Leaderboard</h1>
+		<p>See who is the most 1337 guild member!</p>
+	</div>
+	<table class="leaderboard" cellspacing="0">
+		<thead>
+			<tr>
+				<th>Character name</th>
+				<th>Time in guild</th>
+				<th>Ranking</th>
+			</tr>
+		</thead>
+		<tbody>
+			{% for username, join_time in all_members %}
+				<tr>
+					<td>{{ username | e}}</td>
+					<td>{{ human_time_since(join_time) | e }}</td>
+					<td>{{ loop.index }}</td>
+				</tr>
+			{% endfor %}
+		</tbody>
+	</table>
+{% endblock %}