Unify tenant and admin loggers

3 files changed, 195 insertions, 43 deletions

diff --git a/jimbrella/logger.py b/jimbrella/logger.py
new file mode 100644
index 0000000..dc5f83c
--- /dev/null
+++ b/jimbrella/logger.py
@@ -0,0 +1,152 @@
+import sqlite3
+from datetime import datetime
+from typing import Union
+from .config import config
+
+
+class Logger:
+    def __init__(self, path: str):
+        """Read/write interface to non-volatile logger for admins to inspect.
+
+        This module does no authentication whatsoever. Authenticity must be ensured beforehand.
+        """
+        self.path = path
+
+    def read(self, maxlogs=20, serial=None) -> Union[dict, list]:
+        db = sqlite3.connect(self.path)
+        db.row_factory = sqlite3.Row
+        if serial is None:
+            data = db.execute("SELECT * FROM AdminLog ORDER BY serial DESC").fetchmany(
+                maxlogs
+            )
+        else:
+            data = db.execute(
+                "SELECT * FROM AdminLog WHERE serial = ?", (serial,)
+            ).fetchone()
+
+        db.close()
+        return data
+
+    def log_admin(
+        self, date: str, actor: str, umumbbid: int, umb_a: dict, umb_b: dict, note=""
+    ) -> None:
+        """Logs admin operations in a database so they can be recalled and possibly undone.
+
+        Every time an admin modifies the Umbrellas table via the web console, the information below are
+        kept for archival and/or rollback.
+
+        Once created, the log cannot be changed.
+
+        - serial    | incremental serial number for each modification
+        - date      | modification time, same format as Umbrellas table
+        - actor     | admin username
+        - id        | umbrella id
+        - (status|tenant_(name|id|phone|email)|lent_at)_a:
+            respective values in database pertaining to umbrella #<id> before modification
+        - (status|tenant_(name|id|phone|email)|lent_at)_b:
+            same as above but after modification
+        - note      | optional note describing the modification
+
+        Schema:
+        CREATE TABLE AdminLog(
+            serial INT PRIMARY KEY,
+            date TEXT,
+            actor TEXT,
+            id INT,
+            status_a TEXT,
+            status_b TEXT,
+            tenant_name_a TEXT,
+            tenant_name_b TEXT,
+            tenant_id_a TEXT,
+            tenant_id_b TEXT,
+            tenant_phone_a TEXT,
+            tenant_phone_b TEXT,
+            tenant_email_a TEXT,
+            tenant_email_b TEXT,
+            lent_at_a TEXT,
+            lent_at_b TEXT,
+            note TEXT
+        );
+        """
+        db = sqlite3.connect(self.path)
+
+        # get serial of most recent log, add one
+        latest = db.execute(
+            "SELECT serial FROM AdminLog ORDER BY serial DESC"
+        ).fetchone()
+        if latest is None:
+            serial = 1
+        else:
+            serial = latest[0] + 1
+
+        db.execute(
+            "INSERT INTO AdminLog VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
+            (
+                serial,
+                date,
+                actor,
+                umbid,
+                umb_a.get("status", ""),
+                umb_b.get("status", ""),
+                umb_a.get("tenant_name", ""),
+                umb_b.get("tenant_name", ""),
+                umb_a.get("tenant_id", ""),
+                umb_b.get("tenant_id", ""),
+                umb_a.get("tenant_phone", ""),
+                umb_b.get("tenant_phone", ""),
+                umb_a.get("tenant_email", ""),
+                umb_b.get("tenant_email", ""),
+                umb_a.get("lent_at", ""),
+                umb_b.get("lent_at", ""),
+                note,
+            ),
+        )
+        db.commit()
+        db.close()
+
+    def log_tenant(self, date: str, action: str, umbid: int, umb: dict) -> None:
+        """Keeps a log each time an umbrella is borrowed, returned, or marked overdue.
+
+        - serial, date  | same as AdminLog
+        - action        | one of "borrow", "return", and "overdue"
+        - id, tenant_*  | same as respective columns in table Umbrellas
+
+        Schema:
+        CREATE TABLE TenantLog(
+            serial INT PRIMARY KEY,
+            date TEXT,
+            action TEXT,
+            id INT,
+            tenant_name TEXT,
+            tenant_id TEXT,
+            tenant_phone TEXT,
+            tenant_email TEXT
+        );
+        """
+
+        db = sqlite3.connect(self.path)
+
+        latest = db.execute(
+            "SELECT serial FROM TenantLog ORDER BY serial DESC"
+        ).fetchone()
+
+        if latest is None:
+            serial = 1
+        else:
+            serial = latest[0] + 1
+
+        db.execute(
+            "INSERT INTO TenantLog VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+            (
+                serial,
+                date,
+                action,
+                umbid,
+                umb.get("tenant_name", ""),
+                umb.get("tenant_id", ""),
+                umb.get("tenant_phone", ""),
+                umb.get("tenant_email", ""),
+            ),
+        )
+        db.commit()
+        db.close()
diff --git a/jimbrella/routine.py b/jimbrella/routine.py
index 77d69f2..659fbfa 100644
--- a/jimbrella/routine.py
+++ b/jimbrella/routine.py
@@ -3,7 +3,6 @@ from dateutil.parser import isoparse
 import logging
 from .umbrellas import Umbrellas
 from .jform import JForm
-from .admin_log import AdminLog
 from .exceptions import *
 from .config import config
 from .utils import CST
@@ -40,7 +39,7 @@ def chronological_merge(*sheet_lists) -> list:
     return chronicle
 
 
-def sync_jform(takeaway: JForm, giveback: JForm, db: Umbrellas, admin: AdminLog):
+def sync_jform(takeaway: JForm, giveback: JForm, db: Umbrellas):
     takeaway_unread = takeaway.get_unread()
     giveback_unread = giveback.get_unread()
     logging.info(
@@ -63,11 +62,6 @@ def sync_jform(takeaway: JForm, giveback: JForm, db: Umbrellas, admin: AdminLog)
                     sheet["id"],
                     sheet["phone"],
                 )
-                logging.info(
-                    tenant_identity
-                    + " borrowed umbrella #{key} at {date_str}".format(**sheet)
-                )
-                admin.log("TAKEAWAY", sheet, date=sheet["date"])
             except (UmbrellaStatusError, UmbrellaNotFoundError):
                 logging.warning(
                     tenant_identity
@@ -77,12 +71,11 @@ def sync_jform(takeaway: JForm, giveback: JForm, db: Umbrellas, admin: AdminLog)
                 )
         elif sheet["jform_name"] == "giveback":
             try:
-                db.give_back(sheet["key"], sheet["name"], sheet["id"])
+                db.give_back(sheet["key"], sheet["date"], sheet["name"], sheet["id"])
                 logging.info(
                     tenant_identity
                     + " returned umbrella #{key} at {date_str}".format(**sheet)
                 )
-                admin.log("GIVEBACK", sheet, date=sheet["date"])
             except (UmbrellaStatusError, UmbrellaNotFoundError):
                 logging.warning(
                     tenant_identity
@@ -99,7 +92,7 @@ def sync_jform(takeaway: JForm, giveback: JForm, db: Umbrellas, admin: AdminLog)
                 )
 
 
-def process_overdue(db: Umbrellas, admin: AdminLog):
+def process_overdue(db: Umbrellas):
     """mark and log umbrellas that were not, but just became overdue"""
     umbrellas = db.read()
     now = datetime.now().astimezone(CST)
@@ -120,22 +113,19 @@ def process_overdue(db: Umbrellas, admin: AdminLog):
                         **umb
                     )
                 )
-                admin.log(
-                    "OVERDUE",
-                    {
-                        "key": umb["id"],
-                        "name": umb["tenant_name"],
-                        "phone": umb["tenant_phone"],
-                        "id": umb["tenant_id"],
-                        "email": "",
-                    },
-                )
 
 
 if __name__ == "__main__":
-    takeaway = JForm("takeaway", config.get("jform", "takeaway_url"), config.get("jform", "bookmark_dir"))
-    giveback = JForm("giveback", config.get("jform", "giveback_url"), config.get("jform", "bookmark_dir"))
+    takeaway = JForm(
+        "takeaway",
+        config.get("jform", "takeaway_url"),
+        config.get("jform", "bookmark_dir"),
+    )
+    giveback = JForm(
+        "giveback",
+        config.get("jform", "giveback_url"),
+        config.get("jform", "bookmark_dir"),
+    )
     db = Umbrellas(config.get("general", "db_path"))
-    admin_log = AdminLog(config.get("logging", "admin_log_path"))
-    sync_jform(takeaway, giveback, db, admin_log)
-    process_overdue(db, admin_log)
+    sync_jform(takeaway, giveback, db)
+    process_overdue(db)
diff --git a/jimbrella/umbrellas.py b/jimbrella/umbrellas.py
index 9b5c7b8..241442c 100644
--- a/jimbrella/umbrellas.py
+++ b/jimbrella/umbrellas.py
@@ -2,7 +2,7 @@ import sqlite3
 from datetime import datetime, timezone, timedelta
 from dateutil.parser import isoparse
 from typing import Union
-from .admin_log import AdminLog
+from .logger import Logger
 from .utils import human_datetime, human_timedelta, CST
 from .exceptions import *
 
@@ -14,8 +14,7 @@ class Umbrellas:
         """A database of all umbrellas and their current state.
 
         Currently, the data are represented in a SQLite database. Only admins have access to the
-        database, and have the power to modify it arbitrarily. Each time a modification is made,
-        AdminLog keeps a log.
+        database, and have the power to modify it arbitrarily.
 
         An SQL row for an umbrella consists of these columns:
         - id            | int. unique identifier for the umbrella.
@@ -44,7 +43,7 @@ class Umbrellas:
         );
         """
         self.path = path
-        self.admin_log = AdminLog(path)
+        self.logger = Logger(path)
 
     def read(self, umbid=None) -> Union[dict, list]:
         """Read umbrella data from database.
@@ -177,7 +176,7 @@ class Umbrellas:
         self.update(umb)
         umb_new = dict(self.read(umbid))
         if umb_old != umb_new:
-            self.admin_log.log(
+            self.logger.log_admin(
                 datetime.now(tz=CST).isoformat(timespec="milliseconds"),
                 actor,
                 umbid,
@@ -197,25 +196,27 @@ class Umbrellas:
         if umb["status"] != "available":
             raise UmbrellaStatusError
 
-        self.update(
-            {
-                "id": umbid,
-                "status": "lent",
-                "tenant_name": tenant_name,
-                "tenant_id": tenant_id,
-                "tenant_phone": tenant_phone,
-                "tenant_email": tenant_email,
-                "lent_at": date.isoformat(timespec="milliseconds"),
-            }
+        umb = {
+            "id": umbid,
+            "status": "lent",
+            "tenant_name": tenant_name,
+            "tenant_id": tenant_id,
+            "tenant_phone": tenant_phone,
+            "tenant_email": tenant_email,
+            "lent_at": date.isoformat(timespec="milliseconds"),
+        }
+        self.update(umb)
+        self.logger.log_tenant(
+            date.isoformat(timespec="milliseconds"), "borrow", umbid, umb
         )
 
-    def give_back(self, umbid, tenant_name, tenant_id) -> None:
+    def give_back(self, umbid, date, tenant_name, tenant_id) -> None:
         """When a user has returned an umbrella.
 
         `tenant_name` and `tenant_id` are used to verify if the umbrella is returned by the same
         person who borrowed it.
         """
-        umb = self.read(umbid)
+        umb = dict(self.read(umbid))
 
         if umb is None:
             raise UmbrellaNotFoundError(umbid)
@@ -230,10 +231,13 @@ class Umbrellas:
                 "status": "available",
             }
         )
+        self.logger.log_tenant(
+            date.isoformat(timespec="milliseconds"), "return", umbid, umb
+        )
 
     def mark_overdue(self, umbid) -> None:
         """When an umbrella is overdue, change its status to "overdue"."""
-        umb = self.read(umbid)
+        umb = dict(self.read(umbid))
 
         if umb is None:
             raise UmbrellaNotFoundError(umbid)
@@ -241,3 +245,9 @@ class Umbrellas:
             raise UmbrellaStatusError
 
         self.update({"id": umbid, "status": "overdue"})
+        self.logger.log_tenant(
+            datetime.now(tz=CST).isoformat(timespec="milliseconds"),
+            "overdue",
+            umbid,
+            umb,
+        )