owl.py

from flask import Flask, render_template, request, make_response, g, request_tearing_down, redirect, url_for, session, send_file
from flask.helpers import flash
import psycopg2
import psycopg2.extras
import sys
import os
from cas import CASClient
import logging
from flask_wtf import FlaskForm
import flask_wtf.file
import wtforms
import wtforms.validators as validators
import wtforms.fields.html5 as wtforms_html5
import secrets
import re
from dataclasses import dataclass
from decimal import Decimal
import datetime
import dateutil.tz
from http import HTTPStatus
import click
import email.message
import email.headerregistry
import textwrap
import subprocess
from json.encoder import JSONEncoder
from collections import defaultdict
import urllib.parse
from html import escape
from markupsafe import Markup
import csv
import io

### Flask app object ###

app = Flask(__name__)
app.config.from_pyfile('config.py')
app.jinja_options['extensions'].append('jinja2.ext.do')
app.jinja_env.lstrip_blocks = True
app.jinja_env.trim_blocks = True
app.logger.setLevel(logging.DEBUG)

def set_g_now():
    if not hasattr(g, 'now'):
        g.now = datetime.datetime.now(tz=dateutil.tz.UTC)

def filter_timeformat(dt):
    return dt.astimezone().strftime("%Y-%m-%d %H:%M")

def filter_reltimeformat(dt):
    if dt is None:
        return ""

    dt = dt.astimezone()
    absolute = dt.strftime("%Y-%m-%d %H:%M")
    set_g_now()

    def unit(x, u):
        if x > 1:
            return f"{x} {u}s"
        else:
            return f"{x} {u}"

    rel = abs(dt - g.now)
    sec = int(rel.total_seconds())
    if sec < 5:
        return absolute + " (about now)"

    if sec >= 2*86400:
        out = unit(sec // 86400, 'day')
    elif sec >= 2*3600:
        out = unit(sec // 3600, 'hour')
    elif sec >= 2*60:
        out = unit(sec // 60, 'minute')
    else:
        out = unit(sec, 'second')

    if dt > g.now:
        out = "in " + out
    else:
        out = out + " ago"

    return f"{absolute} ({out})"

def filter_mailto(email, text=None):
    safe_email = urllib.parse.quote(email, safe='@')
    return Markup(f'<a href="mailto:{escape(safe_email)}">{escape(text if text is not None else email)}</a>')

app.jinja_env.filters['timeformat'] = filter_timeformat
app.jinja_env.filters['reltimeformat'] = filter_reltimeformat
app.jinja_env.filters['mailto'] = filter_mailto

### Database connection ###

db_connection = None
db = None

def db_connect():
    global db_connection, db
    db_connection = psycopg2.connect(
                host = app.config['DB_HOST'],
                user = app.config['DB_USER'],
                password = app.config['DB_PASSWD'],
                dbname = app.config['DB_NAME'],
            )
    db = db_connection.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)

def db_query(query, args=()):
    if db is None:
        db_connect()
    try:
        db.execute(query, args)
    except psycopg2.DatabaseError:
        # Reconnect if the connection died (timeout, server restart etc.)
        db_connect()
        db.execute(query, args)

def db_reset_signal(sender, **extra):
    # At the end of every request, we have to close the implicitly opened
    # transaction. Otherwise we end up with serving stale data.
    if db_connection is not None:
        try:
            db_connection.rollback()
        except:
            pass

request_tearing_down.connect(db_reset_signal, app)

### CAS ###

cas_client = CASClient(
    version = 'CAS_2_SAML_1_0',
    server_url = 'https://cas.cuni.cz/cas/'
)


def have_session_p():
    if 'uid' not in session:
        return False

    g.uid = session['uid']
    g.name = session['name']
    g.is_admin = ('admin' in session)
    g.inline_att = session.get('inline', False)
    return True


def login_redirect(next=None, current=False):
    if current:
        next = request.script_root + request.path
    return redirect(url_for('login', next=next or url_for('index')))


### Main ###


@app.route('/')
def index():
    if not have_session_p():
        return login_redirect()

    db_query("""
        SELECT *
        FROM owl_semesters
        ORDER BY rank DESC
        """)
    semesters = db.fetchall()

    db_query("""
            SELECT *
            FROM owl_courses
            JOIN owl_enroll USING(cid)
            WHERE uid=%s
            ORDER BY name
        """, (g.uid,))
    course_list = db.fetchall()

    courses = {s.semid: [] for s in semesters}
    sem_teacher = {s.semid: False for s in semesters}
    for c in course_list:
        courses[c.semid].append(c)
        if c.is_teacher:
            sem_teacher[c.semid] = True

    return render_template('main.html',
        semesters=semesters,
        courses=courses,
        sem_teacher=sem_teacher,
    )


@app.route('/doc/manual')
def manual():
    return render_template('manual.html')


class EnrollTokenForm(FlaskForm):
    token = wtforms.StringField("Token", validators=[validators.DataRequired()])


@app.route('/join/', methods=('GET', 'POST'))
def enroll():
    if not have_session_p():
        return login_redirect(current=True)

    form = EnrollTokenForm()

    if not form.validate_on_submit():
        return render_template('join.html', form=form, error=None)

    token = form.token.data
    db_query("SELECT c.*, s.ident AS sident FROM owl_courses c JOIN owl_semesters s USING(semid) WHERE enroll_token=%s", (token,))
    course = db.fetchone()
    if not course:
        app.logger.info('Invalid enroll token for uid=%d', g.uid)
        return render_template('join.html', form=form, error='Unrecognized token')

    app.logger.info('Enrolling uid=%d to cid=%d', g.uid, course.cid)
    db_query("""
            INSERT INTO owl_enroll(uid, cid)
            VALUES (%s, %s)
            ON CONFLICT DO NOTHING
        """,
        (g.uid, course.cid))
    db_connection.commit()

    return redirect(url_for('course_index', sident=course.sident, cident=course.ident))


def course_init(sident, cident):
    if not have_session_p():
        return login_redirect(current=True)

    db_query("""
            SELECT c.*, s.ident AS sident
            FROM owl_courses c
            JOIN owl_semesters s USING(semid)
            WHERE s.ident=%s AND c.ident=%s
        """,
        (sident, cident))
    g.course = db.fetchone()
    if not g.course:
        return "No such course", HTTPStatus.NOT_FOUND

    if g.is_admin:
        g.is_teacher = True
        return None

    db_query("SELECT * FROM owl_enroll WHERE uid=%s AND cid=%s", (g.uid, g.course.cid))
    enroll = db.fetchone()
    if not enroll:
        return "Not enrolled in this course", HTTPStatus.FORBIDDEN

    g.is_teacher = enroll.is_teacher
    return None


def topic_init(sident, cident, tident):
    err = course_init(sident, cident)
    if err:
        return err

    db_query("SELECT * FROM owl_topics WHERE cid=%s AND ident=%s", (g.course.cid, tident))
    g.topic = db.fetchone()
    if not g.topic:
        return "No such topic", HTTPStatus.NOT_FOUND

    if g.topic.type not in "DTA":
        return "Bad topic type", HTTPStatus.NOT_FOUND

    if not g.topic.public and not g.is_teacher and not g.is_admin:
        return "This is a private topic", HTTPStatus.FORBIDDEN

    if g.course.student_grading:
        db_query("SELECT * FROM owl_student_graders WHERE tid=%s AND uid=%s", (g.topic.tid, g.uid));
        g.is_grader = g.is_teacher or bool(db.fetchone())
    else:
        g.is_grader = g.is_teacher

    return None


def must_be_teacher():
    if not g.is_teacher and not g.is_admin:
        return "You must be a teacher of this course", HTTPStatus.FORBIDDEN

    return None


def must_be_admin():
    if not g.is_admin:
        return "You must be an administrator", HTTPStatus.FORBIDDEN

    return None


def full_name(u):
    return u.first_name + ' ' + u.last_name


@app.route('/c/<sident>/<cident>/')
def course_index(sident, cident):
    err = course_init(sident, cident)
    if err:
        return err

    if g.is_teacher:
        target_uid = -1
    else:
        target_uid = g.uid

    if g.course.student_grading:
        sql_is_grader = "EXISTS (SELECT * FROM owl_student_graders x WHERE x.tid=t.tid AND x.uid=%s)"
        sql_is_grader_params = [g.uid]
    else:
        sql_is_grader = "FALSE"
        sql_is_grader_params = []

    db_query(f"""
            SELECT t.cid, t.tid, t.ident, t.title, t.type, t.deadline, t.max_points,
                s.seen AS last_seen,
                (SELECT MAX(x.created)
                 FROM owl_posts x
                 WHERE x.tid = t.tid
                   AND (x.target_uid=-1 OR x.target_uid = %s)
                ) AS last_posted,
                (SELECT x.points
                 FROM owl_posts x
                 WHERE x.tid = t.tid
                   AND (x.target_uid=-1 OR x.target_uid = %s)
                   AND x.points IS NOT NULL
                 ORDER BY x.created DESC
                 LIMIT 1
                ) AS points,
                {sql_is_grader} AS is_grader
            FROM owl_topics t
            LEFT JOIN owl_seen s ON s.tid = t.tid
                                 AND s.observer_uid = %s
                                 AND (s.target_uid=%s OR t.type='D' AND s.target_uid=-1)
            WHERE t.cid = %s
              AND t.public = true
            ORDER by rank, created
        """, [g.uid, g.uid] + sql_is_grader_params + [g.uid, target_uid, g.course.cid])
    topics = db.fetchall()

    course_total = Decimal('0.00')
    course_max = Decimal('0.00')
    is_grader = False
    for t in topics:
        if t.points is not None:
            course_total += t.points
        if t.max_points is not None:
            course_max += t.max_points
        if t.is_grader:
            is_grader = True

    return render_template(
        'course.html',
        topics=topics,
        course_total=course_total,
        course_max=course_max,
        is_grader=is_grader)


### Posts ###


def validate_attachment(form, field):
    f = field.data
    if not f:
        return

    if f.content_length > 4*2**20:
        raise wtforms.ValidationError("Attachment too large (4MB maximum)")

    if is_pdf(f):
        g.att_type = 'pdf'
    elif is_utf8(f):
        g.att_type = 'txt'
    else:
        raise wtforms.ValidationError("Must be a PDF or plain text file")

    f.seek(0)


def is_pdf(f):
    f.seek(0)
    header = f.read(9)
    return len(header) == 9 and (header[:7] == b'%PDF-1.' or header[:7] == b'%PDF-2.')


def is_utf8(f):
    # FIXME: This is inefficient
    f.seek(0)
    x = f.read()
    try:
        x.decode(encoding='utf-8', errors='strict')
    except:
        return False
    return True


class TopicPostForm(FlaskForm):
    comment = wtforms.TextAreaField("Comment:", validators=[validators.Length(max=65536)])
    attachment = flask_wtf.file.FileField("Attachment (PDF or UTF-8 text):", validators=[validate_attachment])
    points = wtforms.DecimalField("Points:", validators=[validators.Optional()])
    submit = wtforms.SubmitField("Submit")
    ack_time = wtforms.HiddenField()


@app.route('/c/<sident>/<cident>/<tident>/', methods=('GET', 'POST'))
@app.route('/c/<sident>/<cident>/<tident>/<int:student_uid>/', methods=('GET', 'POST'))
def topic_index(sident, cident, tident, student_uid=None):
    err = topic_init(sident, cident, tident)
    if err:
        return err

    if student_uid is not None:
        if not g.is_grader:
            return "Only graders are allowed to do that", HTTPStatus.FORBIDDEN
    else:
        if g.topic.type == 'D':
            # In discussion threads, all posts are global
            student_uid = -1
        elif g.is_teacher:
            # Teachers view and post only global items at their page
            student_uid = -1
        else:
            student_uid = g.uid

    form = TopicPostForm()

    if request.method == 'POST':
        if form.validate_on_submit():
            return topic_post(form, student_uid)
        else:
            flash('Post not submitted, see below for errors.', 'error')

    db_query("""
            SELECT p.pid, p.target_uid, p.author_uid, CONCAT(u.first_name, ' ', u.last_name) AS author_name, p.created, p.modified, p.comment, p.attachment, p.points
            FROM owl_posts p
            LEFT JOIN owl_users u ON u.uid = p.author_uid
            WHERE tid=%s
              AND (target_uid=%s OR target_uid=-1)
            ORDER by created
        """, (g.topic.tid, student_uid))
    posts = db.fetchall()

    seen = None
    db_query("""
            SELECT seen
            FROM owl_seen
            WHERE observer_uid=%s
              AND target_uid IS NOT DISTINCT FROM %s
              AND tid=%s
        """, (g.uid, student_uid, g.topic.tid))
    seen_row = db.fetchone()
    if seen_row:
        seen = seen_row.seen

    if posts and (seen == None or seen < posts[-1].created):
        form.ack_time.data = posts[-1].created

    return render_template('topic.html', posts=posts, form=form, student_uid=student_uid, seen=seen)


def strip_comment(c):
    c = c.replace("\r\n", "\n")
    c = c.strip()
    if c != "":
        return c
    else:
        return None


def topic_post(form, student_uid):
    comment = None
    if form.comment.data:
        comment = strip_comment(form.comment.data)

    attach = None
    if form.attachment.data:
        f = form.attachment.data
        attach = secrets.token_hex(16) + "." + g.att_type   # Type filled in by form field validator
        f.save(os.path.join(app.instance_path, "files", attach))
        app.logger.info("Uploaded file: attach=%s by_uid=%d", attach, g.uid)

    points = None
    if g.is_grader and form.points.data is not None:
        points = form.points.data

    if comment or attach or points is not None:
        db_query("""
                INSERT INTO owl_posts
                (tid, target_uid, author_uid, comment, attachment, points)
                VALUES (%s, %s, %s, %s, %s, %s)
                RETURNING *
            """,
            (g.topic.tid, student_uid, g.uid, comment, attach, points))

        new_post = db.fetchone()
        assert new_post
        app.logger.info(f"New post: pid={new_post.pid} tid={new_post.tid} author_uid={new_post.author_uid} target_uid={new_post.target_uid} points={points} attach={attach}")

        db_query("INSERT INTO owl_notify(pid) VALUES(%s) ON CONFLICT DO NOTHING", (new_post.pid,))
    else:
        new_post = None

    if new_post:
        ack_time = new_post.created
    elif form.ack_time.data:
        ack_time = form.ack_time.data
    else:
        ack_time = None

    if ack_time:
        db_query("""
                INSERT INTO owl_seen
                (observer_uid, target_uid, tid)
                VALUES (%s, %s, %s)
                ON CONFLICT (observer_uid, target_uid, tid) DO UPDATE SET seen = %s
            """, (g.uid, student_uid, g.topic.tid, ack_time))

    db_connection.commit()

    if new_post:
        wake_up_mule()
        return redirect(url_for('topic_index',
                sident=g.course.sident,
                cident=g.course.ident,
                tident=g.topic.ident,
                student_uid=(student_uid if student_uid != g.uid and student_uid >= 0 else None)
            ) + "#p" + str(new_post.pid))
    elif g.is_teacher:
        return redirect(url_for('teacher', sident=g.course.sident, cident=g.course.ident))
    elif g.is_grader:
        return redirect(url_for('topic_student_grade', sident=g.course.sident, cident=g.course.ident, tident=g.topic.ident))
    else:
        return redirect(url_for('course_index', sident=g.course.sident, cident=g.course.ident))


class EditPostForm(FlaskForm):
    comment = wtforms.TextAreaField("Comment:", validators=[validators.Length(max=65536)])
    points = wtforms.DecimalField("Points:", validators=[validators.Optional()])
    submit = wtforms.SubmitField("Submit")
    silent_submit = wtforms.SubmitField("Silent submit")
    delete = wtforms.SubmitField("☠ Delete post ☠")


def edit_allowed_p(post):
    # We must check that the post belongs to the selected course (for which g.is_teacher is valid)
    if g.topic.cid != g.course.cid:
        return False

    return (g.is_admin or
            g.is_teacher or
            post.author_uid == g.uid and g.topic.public)


@app.route('/c/<sident>/<cident>/<tident>/grade')
def topic_student_grade(sident=None, cident=None, tident=None):
    err = topic_init(sident, cident, tident)
    if err:
        return err

    if not g.is_grader:
        return "Only graders can grade", HTTPStatus.FORBIDDEN

    db_query("""
            SELECT s.uid, s.first_name, s.last_name
            FROM owl_enroll e
            JOIN owl_users s ON s.uid = e.uid
            WHERE e.cid = %s
              AND e.is_teacher = false
            ORDER BY s.last_name, s.first_name
        """, (g.course.cid,))

    students = {}
    for s in db.fetchall():
        students[s.uid] = s

    solutions = {}
    for uid, s in students.items():
        solutions[uid] = TSolution(
            points=None,
            last_activity=None,
            last_seen_by_me=None,
            last_seen_by_teacher=None,
        )

    db_query("""
            SELECT p.target_uid AS target_uid, p.created AS post_created, p.points
            FROM owl_posts p
            JOIN owl_users s ON s.uid = p.target_uid
            WHERE p.tid = %s
              AND p.target_uid >= 0
            ORDER BY p.target_uid, p.created, p.pid
        """, (g.topic.tid,))

    for p in db.fetchall():
        s = solutions[p.target_uid]
        if p.points is not None:
            s.points = p.points
        s.last_activity = p.post_created

    db_query("""
            SELECT s.target_uid, s.seen
            FROM owl_seen s
            WHERE s.tid = %s
              AND s.observer_uid = %s
        """, (g.topic.tid, g.uid))

    for t in db.fetchall():
        if t.target_uid in solutions:
            s = solutions[t.target_uid]
            s.last_seen_by_me = t.seen

    return render_template('topic-grade.html', students=students, solutions=solutions)


@app.route('/post/<sident>/<cident>/<int:pid>/', methods=('GET', 'POST'))
def edit_post(sident, cident, pid):
    err = course_init(sident, cident)
    if err:
        return err

    db_query("SELECT * FROM owl_posts WHERE pid=%s FOR UPDATE", (pid,))
    post = db.fetchone()
    if not post:
        return "Not allowed to edit this post", HTTPStatus.FORBIDDEN

    db_query("SELECT * FROM owl_topics WHERE tid=%s", (post.tid,))
    g.topic = db.fetchone()
    assert g.topic

    if g.course.student_grading:
        db_query("SELECT * FROM owl_student_graders WHERE tid=%s AND uid=%s", (g.topic.tid, g.uid));
        g.is_grader = g.is_teacher or bool(db.fetchone())
    else:
        g.is_grader = g.is_teacher

    if not edit_allowed_p(post):
        return "Not allowed to edit this post", HTTPStatus.FORBIDDEN

    if request.method == 'POST':
        form = EditPostForm()
        if not form.validate_on_submit():
            flash('Post not submitted, see below for errors.', 'error')
            return render_template('edit-post.html', form=form)

        if g.is_grader and post.target_uid >= 0:
            student_uid = post.target_uid
        else:
            student_uid = None
        return_to = url_for('topic_index', sident=g.course.sident, cident=g.course.ident, tident=g.topic.ident, student_uid=student_uid)

        if form.delete.data:
            if g.is_teacher:
                app.logger.info(f"Deleted post: pid={pid} tid={post.tid} author_uid={post.author_uid} target_uid={post.target_uid} by_uid={g.uid} public={g.topic.public}")
                db_query("DELETE FROM owl_posts WHERE pid=%s", (pid,))
                db_connection.commit()
                flash('Post deleted.', 'info')
                return redirect(return_to)
            cmt = "*Post deleted by its author.*"
            att = None
            points = None
        else:
            cmt = strip_comment(form.comment.data)
            att = post.attachment
            points = form.points.data

        changed = False
        if cmt != post.comment or att != post.attachment:
            db_query("UPDATE owl_posts SET comment=%s, attachment=%s WHERE pid=%s", (cmt, att, pid))
            changed = True
        if g.is_grader and points != post.points:
            db_query("UPDATE owl_posts SET points=%s WHERE pid=%s", (points, pid))
            changed = True

        if changed:
            app.logger.info(f"Edited post: pid={pid} tid={post.tid} author_uid={post.author_uid} target_uid={post.target_uid} by_uid={g.uid} public={g.topic.public}")
            if g.topic.public:
                db_query("UPDATE owl_posts SET modified=CURRENT_TIMESTAMP WHERE pid=%s", (pid,))
            else:
                db_query("UPDATE owl_posts SET created=CURRENT_TIMESTAMP, modified=NULL WHERE pid=%s", (pid,))
            if not form.silent_submit.data:
                db_query("INSERT INTO owl_notify(pid) VALUES(%s) ON CONFLICT DO NOTHING", (pid,))
            flash('Post changed.', 'info')

        db_connection.commit()

        if changed:
            wake_up_mule()

        return redirect(return_to)

    form = EditPostForm(obj=post)
    return render_template('edit-post.html', form=form)


### Teacher's overview ###


@dataclass
class TSolution:
    points: Decimal
    last_activity: datetime.datetime
    last_seen_by_me: datetime.datetime
    last_seen_by_teacher: datetime.datetime


@app.route('/teacher/c/<sident>/<cident>/')
@app.route('/teacher/s/<sident>/')
def teacher(sident, cident=None):
    if not have_session_p():
        return login_redirect(current=True)

    db_query("CREATE TEMPORARY TABLE tmp_cids (cid int) ON COMMIT DROP");

    if cident is not None:
        err = course_init(sident, cident)
        if err:
            return err
        db_query("INSERT INTO tmp_cids VALUES(%s)", (g.course.cid,))
    else:
        db_query("SELECT * FROM owl_semesters WHERE ident=%s", (sident,))
        semester = db.fetchone()
        if not semester:
            return "No such semester", HTTPStatus.NOT_FOUND

        db_query("""
                INSERT INTO tmp_cids
                SELECT cid
                    FROM owl_enroll
                    JOIN owl_courses c USING(cid)
                    WHERE uid=%s
                      AND is_teacher=true
                      AND c.semid=%s
            """, (g.uid, semester.semid))

    db_query("""
            SELECT c.*, s.ident AS sident
            FROM owl_courses c
            JOIN owl_semesters s USING(semid)
            WHERE cid IN (SELECT cid FROM tmp_cids)
            ORDER BY c.ident
        """)

    courses = {}
    course_totals = {}
    topics = {}
    topics_followed_by_header = {}
    last_tid = {}
    for c in db.fetchall():
        courses[c.cid] = c
        course_totals[c.cid] = 0
        topics[c.cid] = {}
        topics_followed_by_header[c.cid] = {}
        last_tid[c.cid] = None

    db_query("""
            SELECT *
            FROM owl_topics
            WHERE cid IN (SELECT cid FROM tmp_cids)
              AND type IN ('H', 'T', 'A')
            ORDER BY cid, rank
        """)

    for t in db.fetchall():
        if t.type in "TA":
            topics[t.cid][t.tid] = t
            if t.max_points is not None:
                course_totals[t.cid] += t.max_points
            last_tid[t.cid] = t.tid
        elif t.type == 'H':
            if last_tid[t.cid] is not None:
                topics_followed_by_header[t.cid][last_tid[t.cid]] = True

    for cid, tid in last_tid.items():
        topics_followed_by_header[cid][tid] = True

    db_query("""
            SELECT c.cid, s.uid, s.first_name, s.last_name, s.email
            FROM owl_courses c
            JOIN owl_enroll e ON e.cid = c.cid
            JOIN owl_users s ON s.uid = e.uid
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
              AND e.is_teacher = false
            ORDER BY c.ident, s.last_name, s.first_name
        """)

    students = { cid: {} for cid in courses.keys() }
    for s in db.fetchall():
        students[s.cid][s.uid] = s

    solutions = {}
    for c in courses.values():
        cid = c.cid
        solutions[cid] = {}
        for s in students[cid].values():
            uid = s.uid
            solutions[cid][uid] = {}
            for t in topics[cid].values():
                solutions[cid][uid][t.tid] = TSolution(
                    points=None,
                    last_activity=None,
                    last_seen_by_me=None,
                    last_seen_by_teacher=None,
                )

    db_query("""
            SELECT c.cid, t.tid, p.author_uid, p.target_uid AS target_uid, p.created AS post_created, p.points
            FROM owl_courses c
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_posts p ON p.tid = t.tid
            JOIN owl_users s ON s.uid = p.target_uid
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
              AND p.target_uid >= 0
            ORDER BY c.ident, t.ident, p.target_uid, p.created, p.pid
        """)

    for p in db.fetchall():
        s = solutions[p.cid][p.target_uid][p.tid]
        if p.points is not None:
            s.points = p.points
        s.last_activity = p.post_created

    totals = {}
    for c in solutions.keys():
        totals[c] = {}
        for u in solutions[c].keys():
            totals[c][u] = 0
            for s in solutions[c][u].values():
                if s.points is not None:
                    totals[c][u] += s.points

    db_query("""
            SELECT c.cid, t.tid, s.target_uid, s.seen
            FROM owl_courses c
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_seen s ON s.tid = t.tid
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
              AND s.observer_uid = %s
        """, (g.uid,))

    for t in db.fetchall():
        try:
            s = solutions[t.cid][t.target_uid][t.tid]
            s.last_seen_by_me = t.seen
        except KeyError:
            pass

    db_query("""
            SELECT c.cid, t.tid, s.target_uid, s.seen
            FROM owl_courses c
            JOIN owl_topics t ON t.cid = c.cid
            JOIN owl_seen s ON s.tid = t.tid
            WHERE c.cid IN (SELECT cid FROM tmp_cids)
              AND s.observer_uid IN (SELECT uid FROM owl_enroll WHERE cid=c.cid AND is_teacher=true)
              AND s.observer_uid <> %s
        """, (g.uid,))

    for t in db.fetchall():
        try:
            s = solutions[t.cid][t.target_uid][t.tid]
            s.last_seen_by_teacher = t.seen
        except KeyError:
            pass

    # app.logger.debug(courses)
    # app.logger.debug(topics)
    # app.logger.debug(students)
    # app.logger.debug(solutions)
    # app.logger.debug(totals)

    set_g_now()
    return render_template(
        'teacher.html',
        courses=courses,
        topics=topics,
        topics_followed_by_header=topics_followed_by_header,
        students=students,
        solutions=solutions,
        course_totals=course_totals,
        totals=totals)


### Serving files ###


ext_to_mime_type = {
    'pdf': 'application/pdf',
    'txt': 'text/plain; charset=utf-8',
}


@app.route('/files/<name>')
def serve_file(name):
    m = re.fullmatch(r'([0-9a-zA-Z]+)\.([0-9a-zA-Z]+)', name)
    if m:
        return send_file(os.path.join(app.instance_path, "files", name),
            mimetype = ext_to_mime_type[m.group(2)],
            attachment_filename = name)
    else:
        return "No such file", HTTPStatus.NOT_FOUND


### Exports ###

@app.route('/teacher/c/<sident>/<cident>/results.json')
def results_json(sident, cident):
    err = course_init(sident, cident) or must_be_teacher()
    if err:
        return err

    return export_points('json')


@app.route('/teacher/c/<sident>/<cident>/results.csv')
def results_csv(sident, cident):
    err = course_init(sident, cident) or must_be_teacher()
    if err:
        return err

    return export_points('csv')


def export_points(format):
    db_query("""
            SELECT u.uid, u.ukco, u.first_name, u.last_name
            FROM owl_users u
            JOIN owl_enroll e USING(uid)
            WHERE e.cid = %s
              AND e.is_teacher = false
            ORDER BY uid
        """, (g.course.cid,))
    students = db.fetchall()

    db_query("""
            SELECT *
            FROM owl_topics
            WHERE cid=%s
              AND type IN ('A', 'T')
            ORDER BY rank, tid
        """, (g.course.cid,))
    topics = db.fetchall()

    db_query("""
            SELECT t.ident AS tident, p.target_uid, p.points
            FROM owl_topics t
            JOIN owl_posts p USING(tid)
            WHERE t.cid = %s
              AND p.points IS NOT NULL
            ORDER BY p.created
        """, (g.course.cid,))
    awards = db.fetchall()

    points = { s.uid: {} for s in students }
    for a in awards:
        if a.target_uid == -1:
            for uid in points.keys():
                points[uid][a.tident] = float(a.points)
        elif a.target_uid in points:
            points[a.target_uid][a.tident] = float(a.points)

    if format == "json":
        out = []
        for s in students:
            out.append({
                "uid": s.uid,
                "ukco": s.ukco,
                "name": full_name(s),
                "tasks": points[s.uid],
                "points": sum(points[s.uid].values()),
            })

        enc = JSONEncoder(ensure_ascii=False, sort_keys=True, indent=4)
        resp = make_response(enc.encode(out))
        resp.mimetype = 'application/json'
        return resp
    elif format == "csv":
        out = io.StringIO()
        writer = csv.writer(out, delimiter=',', quoting=csv.QUOTE_MINIMAL)
        writer.writerow(['uid', 'ukco', 'name', 'points'] + [t.ident for t in topics])
        for s in students:
            writer.writerow([
                s.uid,
                s.ukco,
                full_name(s),
                sum(points[s.uid].values()),
            ] + [points[s.uid].get(t.ident, "") for t in topics])
        resp = make_response(out.getvalue())
        resp.mimetype = 'text/csv'
        resp.mimetype_params['charset'] = 'utf-8'
        return resp
    else:
        raise NotImplementedError()


### User settings ###