import urllib.request, json
from bs4 import BeautifulSoup as soup
import pymysql.cursors

cnx = pymysql.connect(user='root',
                      password='Elisabeth95',
                      host='127.0.0.1',
                      database='toiletdb')
cursor = cnx.cursor()

postnr_dict = {}
txt_dict = {}
jsoninde_dict = {}
jsonude_dict = {}

def get_postnr_aarhus(url):
    response = urllib.request.urlopen(url)
    read = response.read().decode("utf-8")
    info = json.loads(read)
    data = info

    for postnr in data:
        postnr_dict.update({postnr["navn"]: postnr["nr"]})
    print(postnr_dict)

def get_jsondata_midtby(url):
    response = urllib.request.urlopen(url)
    read = response.read().decode("latin-1")
    info = json.loads(read)
    data = info["features"]

    for toilets in data:
        jsoninde_dict.update({"Midtby " + str(toilets['properties']['JCD_nr#'][4:7]) : {
            "Status:" : toilets['properties']['Status'],
            "Adresse:" : toilets['properties']['Adresse'],
            "Postnr:" : int(toilets['properties']['Postnr#'])}})

    print(jsoninde_dict)

def get_jsondata_parkskov(url):
    response = urllib.request.urlopen(url)
    read = response.read().decode("latin-1")
    info = json.loads(read)
    data = info["features"]

    for toilets in data:
        jsonude_dict.update({toilets['properties']['Navn'] : {
            "Status:" : toilets['properties']['Sæson'],
            "Adresse:" : toilets['properties']['Adresse'],
            "Beskrivelse:" : toilets['properties']['beskrivelse']}})

    print(jsonude_dict)

def read_review_txt(name, mode='r'):
    text_file = open(name, mode)
    lines = text_file.readlines()
    counter = 1001

    for line in lines:
        x = line.split(' - ')
        if x[2][4:] == "\n":
            x.append("Ingen beskrivelse\n")
        txt_dict.update({"Review " + str(counter)[1:] : {
            "Rating:" : line[0],
            "Adresse:" : x[1].replace('Ã¥', 'å').replace('ø', "ø").replace('æ', "æ"),
            "Postnr:" : x[2],
            "Beskrivelse:" : x[3].replace('Ã¥', 'å').replace('ø', "ø").replace('æ', "æ")}})
        counter += 1

    print(txt_dict)

def toilet_area_db(object):
    ## Opretter Company med navn og antal timer
    query = ('INSERT INTO toiletinfo (catagory) VALUES (%s);')
    cursor.execute(query, ("Midtby"))
    cursor.execute(query, ("Park og Skov"))
    cursor.execute(query, ("Brugertilføjet"))
    cursor.execute(query, ("FastFood"))

def århus_zipcode(object):
    for i in postnr_dict:
        query = ('INSERT INTO zipcodedb (zipcode, cityname) VALUES (%s, %s)')
        zipcode = postnr_dict[i]
        city = i
        cursor.execute(query, (zipcode, city))

def toilet_data_db(object):
    for i in jsoninde_dict:
        query = ('INSERT INTO toiletdata (toilet_name,adress,zipcode, status, catagory_id) VALUES (%s, %s, %s, %s, %s);')
        name = i
        adress = jsoninde_dict[i]["Adresse:"]
        zipcode = jsoninde_dict[i]['Postnr:']
        if str(zipcode) in postnr_dict.values():
            zipcode = jsoninde_dict[i]['Postnr:']
        else:
            continue
        status = jsoninde_dict[i]['Status:']
        cursor.execute(query, (name,adress,zipcode,status, 1))


    for i in jsonude_dict:
        query = ('INSERT INTO toiletdata (toilet_name,adress,status, catagory_id) VALUES (%s, %s, %s, %s);')
        name = i
        adress = jsonude_dict[i]["Adresse:"]
        status = jsonude_dict[i]['Status:']
        description = jsonude_dict[i]['Beskrivelse:']
        cursor.execute(query, (name,adress,status,2))



    for i in txt_dict:
        query = ('INSERT INTO toiletdata (toilet_name,adress, zipcode, catagory_id) VALUES (%s, %s, %s, %s);')
        name = i
        adress = txt_dict[i]["Adresse:"]
        zipcode = int(txt_dict[i]['Postnr:'])
        if str(zipcode) in postnr_dict.values():
            zipcode = txt_dict[i]['Postnr:']
        else:
            continue
        cursor.execute(query, (name,adress,zipcode,3))
        cnx.commit()

def ratedescripe(object):
    for i in txt_dict:
        query1 = ('INSERT INTO ratingdb (toilet_id, rating, sum_ratings, times_rated) VALUES (%s, %s, %s, %s);')
        query2 = ('SELECT toilet_id FROM toiletdata WHERE adress = %s;')
        query3 = ('SET FOREIGN_KEY_CHECKS = 0;')
        zipcode = int(txt_dict[i]['Postnr:'])
        if str(zipcode) in postnr_dict.values():
            adress1 = txt_dict[i]["Adresse:"]
            rating = int(txt_dict[i]['Rating:'])
            count = 1
            rate = rating/1
            cursor.execute(query3)
            cursor.execute(query2, (adress1))
            test = cursor.fetchone()
            cursor.execute(query1, (test, rate, rating, count))
        else:
            continue

    for i in jsonude_dict:
        query1 = ('INSERT INTO descriptiondb (toilet_id, suggest_description) VALUES (%s, %s);')
        query2 = ('SELECT toilet_id FROM toiletdata WHERE adress = %s;')
        query3 = ('SET FOREIGN_KEY_CHECKS = 0;')
        adress1 = jsonude_dict[i]["Adresse:"]
        description = jsonude_dict[i]["Beskrivelse:"]
        cursor.execute(query3)
        cursor.execute(query2, (adress1))
        test = cursor.fetchone()
        cursor.execute(query1, (test, description))

    for i in txt_dict:
        query1 = ('INSERT INTO descriptiondb (toilet_id, suggest_description) VALUES (%s, %s);')
        query2 = ('SELECT toilet_id FROM toiletdata WHERE adress = %s;')
        query3 = ('SET FOREIGN_KEY_CHECKS = 0;')
        zipcode = int(txt_dict[i]['Postnr:'])
        if str(zipcode) in postnr_dict.values():
            adress1 = txt_dict[i]["Adresse:"]
            description = txt_dict[i]['Beskrivelse:']
            cursor.execute(query3)
            cursor.execute(query2, (adress1))
            test = cursor.fetchone()
            cursor.execute(query1, (test, description))
        else:
            continue

def reset_database(object):
    reset = ('SET FOREIGN_KEY_CHECKS = 0; truncate descriptiondb; truncate ratingdb; truncate zipcodedb; truncate toiletdata; truncate toiletinfo; SET FOREIGN_KEY_CHECKS = 1;  commit')
    cursor.execute(reset)

url = ("http://dawa.aws.dk/postnumre?kommunekode=751")
url1 = ("https://portal.opendata.dk/dataset/cf1c6b95-3d1f-4cb7-a67b-e93e2de5299c/resource/065550c2-44b0-41db-94ce-009c47b0ba2b/download/bytoiletterwgs84.json")
url2 = ("https://portal.opendata.dk/dataset/c637921c-6ad8-4c1a-bd8d-9dac8605c2d4/resource/1bd17c11-f20f-4db6-ab2a-a56baaf62980/download/toiletwgs84.json")
txt = ("Constanze-filerne2.txt")
get_postnr_aarhus(url)
get_jsondata_midtby(url1)
get_jsondata_parkskov(url2)
read_review_txt(txt)

reset_database(object)
århus_zipcode(object)
toilet_area_db(object)
toilet_data_db(object)
ratedescripe(object)

cnx.commit()
cnx.close()