Automate student creation

4 files changed, 71 insertions, 4 deletions

diff --git a/.gitignore b/.gitignore
index deae9b5..acf8e9a 100644
--- a/.gitignore
+++ b/.gitignore
@@ -2,3 +2,4 @@
 *lock*
 node_*
 *.env
+*.csv
diff --git a/db.js b/db.js
index 244e9a6..cd76e0a 100644
--- a/db.js
+++ b/db.js
@@ -1,4 +1,6 @@
 const mariadb = require("mariadb");
+const bcrypt = require("bcrypt");
+const nanoid = require("nanoid");
 const fs = require("fs");
 
 class DB {
@@ -10,6 +12,9 @@ class DB {
             database: process.env.DBName,
         });
         this.init();
+        this.query("SELECT * FROM users").then((res) => {
+            if (res.length === 0) this.initValues();
+        });
     }
 
     connect() {
@@ -21,10 +26,61 @@ class DB {
             if (err) throw err;
             const queries = data.split(";");
             queries.pop();
-            const conn = await this.connect();
-            for (const query of queries) await conn.query(query);
+            for (const query of queries) await this.query(query);
             console.log("Tables created!");
         });
+
+        fs.readFile(__dirname + "/names.csv", "utf8", (err, data) => {
+            if (err) throw err;
+            const classes = data.split("--");
+            classes.forEach((clazz, classIndex) => {
+                const students = clazz.split("\n");
+                students.forEach(async (student) => {
+                    // Fix undefined
+                    if (student && student.length > 3) {
+                        const [_, surname, name] = student.split(",");
+                        const names = name.split(" ");
+                        const middlename = names.length > 1 && names[1] ? names.slice(1).join(" ") : null;
+                        let username = surname.toLowerCase().slice(0, 6);
+                        if (middlename) username += middlename[0].toLowerCase();
+                        username += names[0].toLowerCase().slice(0, 2);
+                        const pwd = nanoid.nanoid(8);
+                        const password = await bcrypt.hash(pwd, 12);
+                        await this.query(
+                            "INSERT INTO users (username, name, middlename, surname, password, class_id, type_id) VALUE (?,?,?,?,?,?,?)",
+                            [username, names[0], middlename, surname, password, classIndex + 1, 2]
+                        );
+                    }
+                });
+            });
+        });
+    }
+
+    initValues() {
+        fs.readFile(__dirname + "/names.csv", "utf8", (err, data) => {
+            if (err) throw err;
+            const classes = data.split("--");
+            classes.forEach((clazz, classIndex) => {
+                const students = clazz.split("\n");
+                students.forEach(async (student) => {
+                    // Fix undefined
+                    if (student && student.length > 3) {
+                        const [_, surname, name] = student.split(",");
+                        const names = name.split(" ");
+                        const middlename = names.length > 1 && names[1] ? names.slice(1).join(" ") : null;
+                        let username = surname.toLowerCase().slice(0, 6);
+                        if (middlename) username += middlename[0].toLowerCase();
+                        username += names[0].toLowerCase().slice(0, 2);
+                        const pwd = nanoid.nanoid(8);
+                        const password = await bcrypt.hash(pwd, 12);
+                        await this.query(
+                            "INSERT INTO users (username, name, middlename, surname, password, class_id, type_id) VALUE (?,?,?,?,?,?,?)",
+                            [username, names[0], middlename, surname, password, classIndex + 1, 2]
+                        );
+                    }
+                });
+            });
+        });
     }
 
     async query(query, params) {
diff --git a/package.json b/package.json
index b6d8bdd..a7ad9d3 100644
--- a/package.json
+++ b/package.json
@@ -6,9 +6,11 @@
   "author": "LarsVomMars <lars@kroenner.eu>",
   "license": "MIT",
   "dependencies": {
+    "bcrypt": "^5.0.0",
     "dotenv": "^8.2.0",
     "express": "^4.17.1",
     "express-rate-limit": "^5.1.3",
-    "mariadb": "^2.4.2"
+    "mariadb": "^2.4.2",
+    "nanoid": "^3.1.12"
   }
 }
diff --git a/tables.sql b/tables.sql
index 39021f1..6f05523 100644
--- a/tables.sql
+++ b/tables.sql
@@ -27,7 +27,7 @@ CREATE TABLE IF NOT EXISTS users(
     id INTEGER PRIMARY KEY AUTO_INCREMENT,
     username VARCHAR(255) NOT NULL,
     name VARCHAR(255) NOT NULL,
-    middlename VARCHAR(255) NOT NULL,
+    middlename VARCHAR(255) NULL,
     surname VARCHAR(255) NOT NULL,
     password VARCHAR(255) NOT NULL,
     class_id INTEGER NOT NULL,
@@ -37,3 +37,11 @@ CREATE TABLE IF NOT EXISTS users(
     CONSTRAINT `fk_class_user` FOREIGN KEY (class_id) REFERENCES class (id),
     CONSTRAINT `fk_type_user` FOREIGN KEY (type_id) REFERENCES types (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO types VALUES (1, "teacher"), (2, "pupil");
+INSERT INTO class VALUES
+    (1, "TGM13.1"),
+    (2, "TGM13.2"),
+    (3, "TGTM13.1"),
+    (4, "TGI13.1"),
+    (5, "TGI13.2");