# Read a CSV file in Python, libe-by-line, by Jeff Heaton (http://www.jeffheaton.com/tutorials/)
import codecs
import csv
import os
import psycopg2
import sys

csvItems = os.listdir('../../data/CSV')

# FILENAME = "../../data/CSV/0valtice.csv"
ENCODING = 'windows-1250'

conn = psycopg2.connect(host="localhost",database="uzemi", user="postgres", password="heslo")
cur = conn.cursor()
hold = 0

cur.execute('SELECT * FROM cities_csv_names WHERE NOT finished_simple;')
csvNames = cur.fetchall()

print(len(csvNames))

for ar in csvNames:
    hold = hold + 1
    # print(ar)

    FILENAME = ar[1]
    print('Filename: ', FILENAME)

    curId = ar[0]
    print('curId: ', curId)

    # quit()
    # continue
    with codecs.open('../../data/CSV/' + FILENAME, "r", ENCODING) as fp:
        reader = csv.reader(fp)
        # read CSV headers
        headers = next(reader)

        # for loggin out name, on second row set to true and not logging anymore
        secondHold = False

        # read rest of file
        for row in reader:
            # split each row for accessibility
            firstHoldRow = row[0].split(';')

            # rows split
            # print(holdRow[0], holdRow[1], holdRow[2], holdRow[3], holdRow[4], holdRow[5], holdRow[6], holdRow[7], holdRow[8], holdRow[9], holdRow[10], holdRow[11], holdRow[12], holdRow[13], holdRow[14], holdRow[15], holdRow[16], holdRow[17], holdRow[18])
            holdRow = []

            # empty strings set to null because of db

            for x in range(len(firstHoldRow)):
                if not firstHoldRow[x]:
                    holdRow.append(None)
                else:
                    holdRow.append(firstHoldRow[x])


            # print(holdRow)
            # quit()
            # just logging the name
            if secondHold == False:
                print('City: ', holdRow[2])
                secondHold = True


            cityId = holdRow[1]
            cityName = holdRow[2]
            cityPartId = holdRow[7]
            cityPartName = holdRow[8]
            cityStreetId = holdRow[9]
            cityStreetName = holdRow[10]
            soType = holdRow[11]
            numberDomovni = holdRow[12]
            numberOrientacni = holdRow[13]
            signOrientacni = holdRow[14]
            psc = holdRow[15]
            xCoord = holdRow[17]
            yCoord = holdRow[16]

            # NEW TABLE cities_data_simple
            sql = """ INSERT INTO cities_data_simple
            (
                city_id,
                city_name,
                city_part_id,
                city_part_name,
                city_street_id,
                city_street_name,
                so_type,
                number_domovni,
                number_orientacni,
                sign_orientacni,
                psc,
                x_coord,
                y_coord,
                names_id)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

            cur.execute(sql, (
                cityId,
                cityName,
                cityPartId,
                cityPartName,
                cityStreetId,
                cityStreetName,
                soType,
                numberDomovni,
                numberOrientacni,
                signOrientacni,
                psc,
                xCoord,
                yCoord,
                curId
            ))


            # OLD TABLE cities_data
            # sql = """INSERT INTO cities_data
            # (
            #     kod_adm,
            #     kod_obce,
            #     nazev_obce,
            #     kod_momc,
            #     nazev_momc,
            #     kod_mop,
            #     nazev_mop,
            #     kod_casti_obce,
            #     nazev_casti_obce,
            #     kod_ulice,
            #     nazev_ulice,
            #     typ_so,
            #     cislo_domovni,
            #     cislo_orientacni,
            #     znak_cisla_orientacniho,
            #     psc,
            #     souradnice_y,
            #     souradnice_x,
            #     platnost_od,
            #     names_id)
            #     VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""".replace('\n', ' ')

            # cur.execute(sql, (holdRow[0], holdRow[1], holdRow[2], holdRow[3], holdRow[4], holdRow[5], holdRow[6], holdRow[7], holdRow[8], holdRow[9], holdRow[10], holdRow[11], holdRow[12], holdRow[13], holdRow[14], holdRow[15], holdRow[16], holdRow[17], holdRow[18], curId))

        cur.execute('UPDATE cities_csv_names SET finished_simple = TRUE WHERE index = %s;', [curId])

        print('Committing. ', hold, ' / ', len(csvNames))
        print('*************')
        conn.commit()
        # quit()

            # Print individual fields of the row
            # print("{},{},{},{} = {}".format(row[0],row[1],row[2],row[3],row[4]))