schedule.py

import sys
import re
import requests
import sqlite3
from ics import Calendar

database = sqlite3.connect("assets/database.sqlite")

rooms: list = [
    ('T01', 'tp', 0),
    ('T02', 'tp', 0),
    ('T03', 'tp', 4),
    ('T11', 'tp', 1),
    ('T20', 'tp', 2),
    ('T21', 'tp', 2),
    ('T22', 'tp', 2),
    ('T23', 'tp', 2),
    ('T24', 'tp', 2),
    ('C1', 'td', 0),
    ('C4', 'td', 0),
    ('C5', 'td', 0),
    ('C6', 'td', 0),
    ('C7', 'td', 0),
    ('C8', 'td', 0),
    ('C9', 'td', 0),
    ('C10', 'td', 0),
    ('C11', 'td', 1),
    ('C13', 'td', 1),
    ('C14', 'td', 1),
    ('C15', 'td', 1),
]


def main():
    cursor = database.cursor()
    cursor.execute("SELECT value FROM config WHERE key='upstream_url'")
    
    if ('init' in sys.argv):
        setup_tables()
    
    try:
        print("Importing schedule... (this may take a while)")
        import_schedule(cursor.fetchone()[0])
        print("Succefuly imported schedule")
        exit(0)
    
    except:
        print("Couldn't import schedule", file=sys.stderr)
        exit(1)


def import_schedule(url: str, reset: bool = True):
    cursor = database.cursor()
    
    if reset:
        cursor.execute("DELETE FROM schedule")

    cal = Calendar(requests.get(url, allow_redirects=True).text)
    events = [(re.match('([A-Z]\d+)', e.location).group(1),
               e.begin.format("YYYY-MM-DD"),
               e.begin.shift(hours=+2).format("HH:mm"),
               e.end.shift(hours=+2).format("HH:mm"))

              for e in cal.events]

    cursor.executemany(
        "INSERT OR IGNORE INTO schedule VALUES (?,?,?,?)", events)
    database.commit()


def setup_tables():
    cursor = database.cursor()

    # Create 'rooms' table
    cursor.execute("""CREATE TABLE IF NOT EXISTS rooms (
        id TEXT PRIMARY KEY,
        type TEXT NOT NULL,
        location INTEGER NOT NULL
    )""")

    # Populate 'rooms' table
    cursor.executemany("INSERT OR IGNORE INTO rooms VALUES (?,?,?)", rooms)

    # Create 'schedule' table
    cursor.execute("""CREATE TABLE IF NOT EXISTS schedule (
        id TEXT NOT NULL,
        date TEXT NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        FOREIGN KEY(id) REFERENCES rooms(id)
    )""")

    database.commit()


if __name__ == "__main__":
    main()