import threading
from IPython.display import display, HTML
import ipywidgets as widgets
import time
import requests
import os
from os import popen
import re

def mz(code):
    results = ! psql -h 127.0.0.1 -p 6875 materialize -c """$code"""
    return results

def thread_func(out):
    sql_code = """
CREATE SOURCE wikirecent
FROM FILE 'wikirecent' WITH (tail = true)
FORMAT REGEX '^data: (?P<data>.*)';"""
    out.append_stdout(mz(sql_code))
    sql_code = """
CREATE MATERIALIZED VIEW recentchanges AS
    SELECT
        val->>'$schema' AS r_schema,
        (val->'bot')::bool AS bot,
        val->>'comment' AS comment,
        (val->'id')::float::int AS id,
        (val->'length'->'new')::float::int AS length_new,
        (val->'length'->'old')::float::int AS length_old,
        val->'meta'->>'uri' AS meta_uri,
        val->'meta'->>'id' as meta_id,
        (val->'minor')::bool AS minor,
        (val->'namespace')::float AS namespace,
        val->>'parsedcomment' AS parsedcomment,
        (val->'revision'->'new')::float::int AS revision_new,
        (val->'revision'->'old')::float::int AS revision_old,
        val->>'server_name' AS server_name,
        (val->'server_script_path')::text AS server_script_path,
        val->>'server_url' AS server_url,
        (val->'timestamp')::float AS r_ts,
        val->>'title' AS title,
        val->>'type' AS type,
        val->>'user' AS user,
        val->>'wiki' AS wiki
    FROM (SELECT data::jsonb AS val FROM wikirecent);"""
    out.append_stdout(mz(sql_code))
    
    sql_code = """CREATE MATERIALIZED VIEW useredits AS
    SELECT user, count(*) FROM recentchanges GROUP BY user;"""
    
    out.append_stdout(mz(sql_code))
    sql_code = """
CREATE MATERIALIZED VIEW top10 AS
    SELECT * FROM useredits ORDER BY count DESC LIMIT 10;
    """
    out.append_stdout(mz(sql_code))
    while True:

        out.clear_output()
        import psycopg2

        con = psycopg2.connect(database="materialize", host="127.0.0.1", port="6875")

        cur = con.cursor()
        cur.execute("SELECT * FROM top10 ORDER BY count DESC;")
        rows = '\n'.join(['%s: %s' % (cur.fetchall()])
     

        out.append_stdout(rows)
        time.sleep(2)
out = widgets.Output()


thread = threading.Thread(
    target=thread_func,
    args=(out,))
thread.start()
display(out)