path: root/modules/timetrack3/timetrack3.js

"use strict";

const cmn = require("../$common.js");
const crypto = require("crypto");
const basicAuth = require("basic-auth");
const fs = require("fs");
const sqlite3 = require("sqlite3");
const mkdirp = require("mkdirp");

let moddir = null;

const ROOT_ENDPOINT = "/timetrack3";
const DB_DIR = cmn.persistdir + "/timetrack3";
const DB_PATH = DB_DIR + "/db";

let DB = null;

function openDatabase() {
  const predb = new sqlite3.Database(DB_PATH, sqlite3.OPEN_READWRITE, err => {
    if (err && err.code == "SQLITE_CANTOPEN") {
      mkdirp.sync(DB_DIR);

      const predb2 = new sqlite3.Database(DB_PATH, err => {
        if (err) {
          console.error("Cannot create database:", err);
          process.exit(1);
        }
        predb2.serialize();
        predb2.exec(`
          pragma foreign_keys = ON;
          create table users (
            id        integer primary key autoincrement not null,
            name      text    not null,
            pwhash    text    not null,  -- 16-byte salt + "$" + scrypt(keylen=64)
            cursheet  text    null,
            curdescr  text    null,
            curindate integer null,  -- unix timestamp
            unique (name)
          ) strict;
          create table events (
            id       integer primary key autoincrement not null,
            username integer not null,
            sheet    text    not null,
            descr    text    not null,
            indate   integer not null,  -- unix timestamp
            outdate  integer not null,  -- unix timestamp
            foreign key (username) references users (name) on delete cascade
          )
        `);
        DB = predb2;
      });
    } else if (err) {
      console.error("Cannot open database:", err);
      process.exit(1);
    } else {
      predb.serialize();
      predb.exec("pragma foreign_keys = ON;");
      DB = predb;
    }
  });
}

function scryptHash(password, cb) {
  crypto.randomBytes(16, function(err, salt) {
    if (err) {
      cb(err, null);
      return;
    }
    crypto.scrypt(password, salt, 64, (err, key) => {
      if (err) cb(err, null);
      else cb(null, salt.toString("hex") + "$" + key.toString("hex"));
    });
  });
}

function scryptCompare(password, hash, cb) {
  hash = hash.split("$");
  if (hash.length != 2) {
    cb(new Error("Invalid hash in database"), null);
    return;
  }
  const salt = Buffer.from(hash[0], "hex"), shash = hash[1];
  crypto.scrypt(password, salt, 64, (err, key) => {
    if (err) cb(err, null);
    else if(key.toString("hex") == shash) cb(null, true);
    else cb(null, false);
  });
}


function sendUnauth(res) {
  res.set("WWW-Authenticate", "Basic realm=Authorization required");
  return res.sendStatus(401);
}

function unknownUserHandler(req, res, next){
  res.sendFile(moddir + "/unknownuser.html");
}

function authMiddleware(req, res, next){
  const user = basicAuth(req);
  req.authuser = null;
  if (!user || !user.name) {
    sendUnauth(res);
    return;
  }
  req.authuser = user.name;

  DB.get("select pwhash from users where name = ?", [user.name], (err, row) => {
    if (err || row == undefined) {
      unknownUserHandler(req, res, next);
      return;
    }
    scryptCompare(user.pass, row.pwhash, (err, ok) => {
      if (ok) next();
      else sendUnauth(res);
    });
  });
}

function asciiValid(str) {
  for (let i = 0; i < str.length; i++) {
    const c = str.charCodeAt(i);
    if (c < 32 || c >= 127) return false;
  }
  return true;
}


function dbCallback(res, fn) {
  return function(err) {
    if (err) {
      console.error(err);
      res.status(500).send("Something went wrong...");
    } else {
      fn(...Array.from(arguments).slice(1));
    }
  };
}

function fatalRollback() {
  DB.exec("rollback", err => {
    if (err) {
      console.log("ROLLBACK", err);
      process.exit(1);
    }
  });
}


module.exports = function(app, io, _moddir){
  openDatabase();

  moddir = _moddir;

  // first the endpoints that need to bypass authMiddleware

  // - -> html
  app.get(ROOT_ENDPOINT + "/authfail", (req, res) => {
    sendUnauth(res);
  });

  // - -> html (account info is in basic auth)
  app.post(ROOT_ENDPOINT + "/createuser", (req, res) => {
    const user = basicAuth(req);
    if (!user || !user.name) {
      res.status(400).send("No credentials sent");
      return;
    }
    if (user.name.length < 3 || user.name.length > 32 || !asciiValid(user.name)) {
      res.status(400).send("Invalid username");
      return;
    }
    if (user.pass.length < 3 || user.pass.length > 32 || !asciiValid(user.pass)) {
      res.status(400).send("Invalid password");
      return;
    }

    DB.get("select id from users where name = ?", [user.name], dbCallback(res, row => {
      if (row != undefined) {
        res.status(400).send("User already exists");
        return;
      }

      DB.get("select count(*) as cnt from users", dbCallback(res, row => {
        if (row.cnt >= 20) {
          res.status(500).send("Too many accounts created, please contact Tom...");
          return;
        }

        scryptHash(user.pass, (err, hash) => {
          if (!hash) {
            res.status(500).send("Something went wrong...");
            console.log(err);
            return;
          }
          DB.run("insert into users (name, pwhash) values (?, ?)", [user.name, hash]);
          res.status(200).end();
        });
      }));
    }));
  });

  // for all the other endpoints, authorisation is needed
  app.all([ROOT_ENDPOINT, ROOT_ENDPOINT + "/*"], authMiddleware);

  // - -> html
  app.get(ROOT_ENDPOINT, (req, res) => {
    res.sendFile(moddir + "/timetrack.html");
  });

  // - -> {sheet, descr, indate} (date in unix timestamp)
  app.get(ROOT_ENDPOINT + "/current", (req, res) => {
    DB.get("select cursheet, curdescr, curindate from users where name = ?", [req.authuser], dbCallback(res, row => {
      res.json({
        sheet: row.cursheet,
        descr: row.curdescr,
        indate: row.curindate,
      });
    }));
  });

  // - -> [{id, sheet, descr, indate, outdate}] (dates in unix timestamp)
  app.get(ROOT_ENDPOINT + "/recent", (req, res) => {
    DB.all("select id, sheet, descr, indate, outdate from events where username = ? order by indate desc limit 20", [req.authuser], dbCallback(res, rows => {
      // We got the rows in descending order so that we could apply the limit clause; reorder them in ascending order again
      rows.reverse();

      res.json(rows.map(row => ({
        id: row.id,
        sheet: row.sheet,
        descr: row.descr,
        indate: row.indate,
        outdate: row.outdate,
      })));
    }));
  });

  // - -> [{sheet, total}] (totals in seconds)
  app.get(ROOT_ENDPOINT + "/sheets", (req, res) => {
    DB.all("select sheet, sum(outdate - indate) as total from events where username = ? group by sheet", [req.authuser], dbCallback(res, rows => {
      res.json(rows.map(row => ({
        sheet: row.sheet,
        total: row.total,
      })));
    }));
  });

  // id -> -
  app.delete(ROOT_ENDPOINT + "/event", (req, res) => {
    const id = +req.body;
    if (id < 0 || ~~id != id || isNaN(id)) {
      res.status(404).send("Unknown id");
      return;
    }

    DB.run("delete from events where username = ? and id = ?", [req.authuser, id], function(err) {  // uses 'this'
      if (err) {
        console.error(err);
        res.status(500).send("Something went wrong...");
        return;
      }

      if (this.changes == 0) {
        res.status(404).send("Event not found");
      } else {
        res.status(200).send();
      }
    });
  });

  // {sheet, descr, date} -> - (date in unix timestamp)
  app.post(ROOT_ENDPOINT + "/checkin", (req, res) => {
    let obj;
    try {
      obj = JSON.parse(req.body);
    } catch (e) {
      res.status(400).send("Invalid request");
      return;
    }
    const sheet = obj.sheet + "", descr = obj.descr + "", date = new Date(obj.date * 1000);
    if (sheet.length == 0 || isNaN(date.getTime())) {
      res.status(400).send("Invalid data");
      return;
    }

    // 'immediate' to make this a write transaction
    DB.exec("begin immediate", err => {
      if (err) {
        console.error(err);
        res.status(500).send("Something went wrong...");
        return;
      }

      DB.get("select (select count(*) from events where username = ?) as cnt, (select cursheet from users where name = ?) as cursheet", [req.authuser, req.authuser], (err, row) => {
        if (err) {
          console.error(err);
          res.status(500).send("Something went wrong...");
          fatalRollback();
          return;
        }

        if (row.cnt >= 10000) {
          res.status(400).send("Isn't 10000 events enough for you?");
          fatalRollback();
          return;
        }

        if (row.cursheet) {
          res.status(409).send("Already checked in");
          fatalRollback();
          return;
        }

        DB.run("update users set cursheet = ?, curdescr = ?, curindate = ? where name = ?", [sheet, descr, ~~(date.getTime() / 1000), req.authuser], err => {
          if (err) {
            console.error(err);
            res.status(500).send("Something went wrong...");
            fatalRollback();
            return;
          }

          DB.exec("commit", err => {
            if (err) {
              console.error(err);
              res.status(500).send("Something went wrong...");
              fatalRollback();
              return;
            }

            res.status(200).end();
          });
        });
      });
    });
  });

  // {date} -> - (date in unix timestamp)
  app.post(ROOT_ENDPOINT + "/checkout", (req, res) => {
    let obj;
    try {
      obj = JSON.parse(req.body);
    } catch (e) {
      res.status(400).send("Invalid request");
      return;
    }
    const date = new Date(obj.date * 1000);
    if (isNaN(date.getTime())) {
      res.status(400).send("Invalid data");
      return;
    }

    // 'immediate' to make this a write transaction
    DB.exec("begin immediate", err => {
      if (err) {
        console.error(err);
        res.status(500).send("Something went wrong...");
        return;
      }

      DB.get("select count(*) as cnt from events where username = ?", [req.authuser], (err, row) => {
        if (err) {
          console.error(err);
          res.status(500).send("Something went wrong...");
          fatalRollback();
          return;
        }

        if (row.cnt >= 10000) {
          res.status(400).send("Isn't 10000 events enough for you?");
          fatalRollback();
          return;
        }

        DB.get("select cursheet, curdescr, curindate from users where name = ?", [req.authuser], (err, row) => {
          if (err) {
            console.error(err);
            res.status(500).send("Something went wrong...");
            fatalRollback();
            return;
          }

          if (!row.cursheet) {
            res.status(409).send("Not checked in");
            fatalRollback();
            return;
          }

          const sheet = row.cursheet, descr = row.curdescr, indate = row.curindate;

          DB.run("update users set cursheet = null, curdescr = null, curindate = null where name = ?", [req.authuser], err => {
            if (err) {
              console.error(err);
              res.status(500).send("Something went wrong...");
              fatalRollback();
              return;
            }

            DB.run("insert into events(username, sheet, descr, indate, outdate) values (?, ?, ?, ?, ?)", [req.authuser, sheet, descr, indate, ~~(date.getTime() / 1000)], err => {
              if (err) {
                console.error(err);
                res.status(500).send("Something went wrong...");
                fatalRollback();
                return;
              }

              DB.exec("commit", err => {
                if (err) {
                  console.error(err);
                  res.status(500).send("Something went wrong...");
                  fatalRollback();
                  return;
                }

                res.status(200).end();
              });
            });
          });
        });
      });
    });
  });
};