loggen.py

import sqlite3
import requests
from datetime import datetime


class DB():
    def __init__(self, data_dir):
        self.data_dir = data_dir
        try:
            self.connection = sqlite3.connect(f"{data_dir}/loggen.sqlite")
        except Exception:
            raise Exception("Something went wrong connecting to the database. Are you sure loggen.sqlite exists?")

    _create_loggen_table = """
    CREATE TABLE IF NOT EXISTS messages (
        id string PRIMARY KEY,
        msg_id string NOT NULL,
        created_on string NOT NULL,
        updated_on string NOT NULL,
        type string NOT NULL,
        text string NOT NULL
    );
    """

    _create_politiloggen_table = """
    CREATE TABLE IF NOT EXISTS politiloggen (
        id string PRIMARY KEY,
        area string NOT NULL,
        created_on string NOT NULL,
        updated_on string NOT NULL,
        district string NOT NULL,
        municipality string NOT NULL,
        is_active bool NOT NULL,
        district_id integer NOT NULL,
        FOREIGN KEY(id) REFERENCES Artists(msg_id)
    );
    """

    def populate_tables(self):
        try:
            c = self.connection.cursor()
            c.execute(self._create_loggen_table)
            c.execute(self._create_politiloggen_table)
        except Exception:
            raise Exception("Something went wrong populating the database.")

        self.connection.close()

class PolitiLoggen():
    def __init__(self):
        self.baseUrl = "https://politiloggen-vis-frontend.bks-prod.politiet.no/api/messagethread"
        self.headers = {"content-type": "application/json"}
        self.baseData = {
            "sortByEnum": "Date",
            "sortByAsc": False,
            "timeSpanType": "Custom",
            "dateTimeFrom": "2022-01-01T00:00:00.000Z",
            "dateTimeTo": "2024-01-17T13:40:50.845Z",
            "skip": 0,
            "take": 40,
            #"district": "Møre og Romsdal politidistrikt",
            "category": []
            }

    def parseMessage(self, message: dict) -> dict:
        updates = []
        for update in message.get("messages"):
            updates.append({
                "createdOn": update.get("createdOn"),
                "type": update.get("type"),
                "msg_id": update.get("id").split("-")[0],
                "text": update.get("text"),
                "id": update.get("id"),
                "updatedOn": update.get("updatedOn"),
            })
        return {
            "area": message.get("area"),
            "category": message.get("category"),
            "createdOn": message.get("createdOn"),
            "district": message.get("district"),
            "messages": updates,
            "municipality": message.get("municipality"),
            "updatedOn": message.get("updatedOn"),
            "id": message.get("id"),
            "is_active": message.get("isActive"),
            "district_id": message.get("districtId"),
        }

    def getMessages(self):
        response = requests.post(self.baseUrl, json=self.baseData, headers=self.headers)
        return [self.parseMessage(message) for message in response.json().get("messageThreads")]

if __name__ == "__main__":
    db = DB(".")
    db.populate_tables()
    pl = PolitiLoggen()

    db = DB(".")
    conn = db.connection
    cursor = conn.cursor()
    for message in pl.getMessages():
        if cursor.execute("SELECT * FROM politiloggen WHERE id=?", (message["id"],)).fetchone():
            cursor.execute("""
                           UPDATE politiloggen SET is_active=?, updated_on=?
                           WHERE id=?
                           """, (message["is_active"], message["updatedOn"], message["id"]))
            continue
        cursor.execute("""
                       INSERT INTO politiloggen
                       (id, area, created_on, updated_on, district, municipality, is_active, district_id)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                       """
            , (
            message["id"],
            message["area"],
            message["createdOn"],
            message["updatedOn"],
            message["district"],
            message["municipality"],
            message["is_active"],
            message["district_id"],
            )
        )
        for update in message["messages"]:
            if cursor.execute("SELECT * FROM messages WHERE id=?", (update["id"],)).fetchone():
                continue
            cursor.execute("""INSERT INTO messages
                           (id, msg_id, created_on, updated_on, type, text)
                           VALUES (?, ?, ?, ?, ?, ?)
                           """
                           , (
                update["id"],
                update["msg_id"],
                update["createdOn"],
                update["updatedOn"],
                update["type"],
                update["text"],
                )
            )
    conn.commit()
    conn.close()