import json
import mariadb
import sys
import time
from urllib.request import urlopen
from bs4 import BeautifulSoup
from datetime import datetime


def read_config(file="config.json"):
    """
    Read config file to get database configuration and other stuff.
    """
    with open(file, "r") as f:
        config = json.load(f)
    return config


def prepare_db():
    """
    Setup database table, if it not exists.
    """
    try:
        conn = mariadb.connect(
            user=config["db_user"],
            password=config["db_passwd"],
            host=config["db_host"],
            port=config["db_port"],
            database=config["db_db"]
        )
    except mariadb.Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)

    cur = conn.cursor()
    cur.execute("SHOW TABLES")

    table_exists = False
    for table in cur:
        if table == config["db_table"]:
            table_exists = True

    if not table_exists:
        cur.execute("CREATE TABLE ? (timestamp TIMESTAMP PRIMARY KEY, level_cm INT, flow_m3_s INT)", (config["db_table"]))

    conn.close()


def write_db(timestamp, level: int, flow: int):
    """
    Write data set to database.
    @param timestamp: timestamp of data set
    @param level: water level in cm
    @param flow: water flow in m3/s
    """
    try:
        conn = mariadb.connect(
            user=config["db_user"],
            password=config["db_passwd"],
            host=config["db_host"],
            port=config["db_port"],
            database=config["db_db"]
        )
    except mariadb.Error as e:
        print(f"Error connecting to MariaDB Platform: {e}")
        sys.exit(1)

    cur = conn.cursor()
    try:
        cur.execute("INSERT INTO ? (timestamp,level_cm,flow_m3_s) VALUES (?, ?, ?)", (config["db_table"], timestamp, level, flow))
    except mariadb.Error as e:
        print(f"Error: {e}")
    finally:
        conn.close()


def retrieve(url: str):
    """
    Get webpage and parse it to get timestamp, water level and flow.
    @param url: the URL to call
    @return: 3-tuple with timestamp, level, flow
    """
    page = urlopen(url)
    html = page.read().decode("utf-8")
    soup = BeautifulSoup(html, "html.parser")

    latest = soup.select(".quickbarTable tbody")[0].select("tr")[0]
    latest_ts = latest.select("td")[0].text
    ts = datetime.strptime(latest_ts.strip(), "%d.%m.%Y %H:%M")
    latest_level = latest.select("td")[1].text
    latest_flow = latest.select("td")[2].text

    return ts, int(latest_level), int(latest_flow)


config = read_config()
# prepare_db()
while True:
    ts, latest_level, latest_flow = retrieve(config["url"])
    # write_db(ts, latest_level, latest_flow)
    print(str(ts) + ": " + str(latest_level) + "cm " + str(latest_flow) + "m3/s")
    time.sleep(config["sleep_sec"])