modules

def delete_database():
    """Run a SQL querry on the netspeedlogger database and print a table of the results"""
    db_path = get_database_path()
    print(f"Deleting netspeedlogger database at path: `{db_path}`")
    print(
        "Are you sure you want to delete the whole database? Input 'y' for yes or 'n' for no"
    )

    for i in range(10):
        confirmation = input("Please type 'y' for Yes or 'n' for No")
        if confirmation == "n":
            return "Not deleting database"
        elif confirmation == "y":
            delete_database_if_exists()
            return "Database deleted"

def results():
    """Show all results from the netspeedlogger database

    If there are more than 10000 results, will show the first 10000
    """
    sql_to_markdown(
        "select substr(timestamp,1,19) as 'Date Time', "
        "   download_speed/(1024*1024) as 'Download Speed (Mb/s)', "
        "   upload_speed/(1024*1024) as 'Upload Speed (Mb/s)', "
        "   bytes_sent/(1024) as 'kB Sent', "
        "   bytes_received/(1024) as 'kB Recieved', "
        "   server_id  as 'Server ID', "
        "   server_host as 'Server Host', "
        "   ping as 'Ping (ms)'  "
        "   from netspeedlogger limit 10000"
    )

def speedtest():
    """Run an internet speed test using speedtest-cli and save the results to a local sqlite database"""
    print("netspeedlogger speedtest")
    print("=" * len("netspeedlogger speedtest"))
    print("Starting to run an internet speed test, and logging the output")

    results_dict = run_speedtest()
    df = speedtest_dict_to_dataframe(results_dict)
    write_speedtest_to_database(df)
    print("Speedtest complete. Results:")
    print(df.to_markdown(index=False))

def sql_to_markdown(sql_query: str, showindex: bool = False):
    """Run a SQL querry on the netspeedlogger database and print a table of the results"""

    if database_has_results():
        df = query(sql_query)
        print(df.to_markdown(index=showindex))
    else:
        print("No results - run `netspeedlogger run` first")

def summary():
    """Display summary of internet speed test results as a table"""
    if database_has_results():
        df = query(
            (
                "select substr(timestamp,1,19) as 'Date Time', "
                "   download_speed/(1024*1024) as 'Download Speed (Mb/s)', "
                "   upload_speed/(1024*1024) as 'Upload Speed (Mb/s)', "
                "   ping as 'Ping (ms)'  "
                "   from netspeedlogger "
            )
        )
        print(df.describe().to_markdown(index=True))
    else:
        print("No results - run `netspeedlogger run` first")

def database_has_results():
    """Returns True if netspeedlogger database has results, False otherwise"""
    result = query("select count(*) from netspeedlogger")
    if isinstance(result, pd.DataFrame):
        return True
    else:
        return False

def delete_database_if_exists():
    """Delete netspeedlogger database if it exists"""
    dbpath = get_database_path()
    if os.path.isfile(dbpath):
        os.remove(dbpath)

def get_database_path():
    """Returns the path to the netspeedlogger sqlite database

    Set the env var NETSPEEDLOGGER to change the default folder location

    If NETSPEEDLOGGER is not set, it is stored here: ~/.netspeedlogger/netspeedlogger.sqlite3
    If the folder .netspeedlogger doesn't exist, it will be created
    """
    USERHOME = os.path.expanduser("~")

    database_folder = os.environ.get(
        "NETSPEEDLOGGER", os.path.join(USERHOME, ".netspeedlogger")
    )

    if not os.path.isdir(database_folder):
        os.makedirs(database_folder, exist_ok=True)

    return os.path.join(database_folder, "netspeedlogger.sqlite3")

def query(query: str):
    """Run a SQL querry on the netspeedlogger database"""
    database_file = get_database_path()
    if os.path.isfile(database_file):
        con = sqlite3.connect(database_file)
        df = pd.read_sql_query(query, con)
        con.close()
        return df
    else:
        return None

def run_speedtest(retries: int = 3, timeout: int = 15, sleep_between_retries: int = 10):
    """Run internet speed test with speedtest-cli library"""
    for retry_count in range(retries):
        try:
            s = speedtest.Speedtest(timeout=timeout)
            s.get_best_server()
            s.download(threads=None)
            s.upload(threads=None)
            results_dict = s.results.dict()
            validate_speedtest_result(results_dict)
            return results_dict
        except speedtest.ConfigRetrievalError as e:
            template = "An exception of type {0} occurred. Arguments:\n{1!r}"
            message = template.format(type(e).__name__, e.args)
            mylogger.warning(message)
            if (retry_count + 1) < retries:
                sleep(sleep_between_retries)
        except IndexError as e:
            template = "An exception of type {0} occurred. Arguments:\n{1!r}"
            message = template.format(type(e).__name__, e.args)
            mylogger.warning(message)
            if (retry_count + 1) < retries:
                sleep(sleep_between_retries)

    mylogger.error(f"Failed running speed test {retries} times; returning zero values")

    return {
        "download": 0,
        "upload": 0,
        "bytes_sent": 0,
        "bytes_received": 0,
        "ping": None,
        "server": {"host": None, "id": None},
        "timestamp": str(datetime.datetime.now()),
    }

def selectall():
    """Select all records in the netspeedlogger database"""
    return query("SELECT * from netspeedlogger")

def selectall_with_date_range(min_date, max_date):
    """Select all from netspeedlogger database with date range"""
    return query(
        f"SELECT * FROM netspeedlogger where timestamp >= '{min_date}' and timestamp <= '{max_date}'"
    )

def speedtest_dict_to_dataframe(results_dict):
    """Converts speedtest-cli results_dict to a pandas.DataFrame"""
    return pd.DataFrame(
        [
            {
                "download_speed": results_dict["download"],
                "upload_speed": results_dict["upload"],
                "bytes_sent": results_dict["bytes_sent"],
                "bytes_received": results_dict["bytes_received"],
                "ping": results_dict["ping"],
                "server_host": results_dict["server"]["host"],
                "server_id": results_dict["server"]["id"],
                "timestamp": str(datetime.datetime.now()),
            }
        ]
    )

def timeseries_chart(dat, variable, CHART_HEIGHT, CHART_WIDTH):
    """Altair timeseries chart for netspeedlogger"""
    return (
        alt.Chart(dat, title="")
        .mark_line(interpolate="cardinal", point=alt.OverlayMarkDef())
        .encode(
            x="timestamp:T",
            y=f"{variable}:Q",
            tooltip=[
                alt.Tooltip(variable, title=variable),
                alt.Tooltip("timestamp:T", title="Date"),
                alt.Tooltip("timestamp:T", title="Hour", timeUnit="hours"),
                alt.Tooltip("timestamp:T", title="Minute", timeUnit="minutes"),
            ],
        )
        .properties(height=CHART_HEIGHT, width=CHART_WIDTH)
    )

def validate_speedtest_result(results_dict: dict):
    """Validates speedtest-cli results results_dict has required fields"""
    mylogger.info("Validating response before inserting into database")
    assert "download" in results_dict
    assert "upload" in results_dict
    assert "bytes_sent" in results_dict
    assert "bytes_received" in results_dict
    assert "ping" in results_dict
    assert "server" in results_dict
    assert "host" in results_dict["server"]
    assert "id" in results_dict["server"]
    assert isinstance(results_dict["download"], float)
    assert isinstance(results_dict["upload"], float)
    assert isinstance(results_dict["bytes_sent"], int)
    assert isinstance(results_dict["bytes_received"], int)
    assert isinstance(results_dict["ping"], float)
    assert isinstance(results_dict["server"]["host"], str)
    assert isinstance(results_dict["server"]["id"], str)

def write_speedtest_to_database(df: pd.DataFrame):
    """Write speedtest result to netspeedlogger database"""
    database_path = get_database_path()
    mylogger.info(f"Opening database: {database_path}")
    con = sqlite3.connect(database_path)

    mylogger.info("Inserting into database")
    df.to_sql("netspeedlogger", con, if_exists="append")

    mylogger.info(str(datetime.datetime.now()))
    mylogger.info("Internet Speed Test script finished")
    con.close()