import csv
import sys
import time
import sqlite3


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

TOW_ref = 0
data = []

query = """CREATE TABLE GEN (Id INTEGER PRIMARY KEY, TOW INT, Sat TEXT, TrPR INT)"""

path_database = time.localtime()
path_database = "test_data/{}-{}-{}_{}-{}-{}.db".format(
    path_database[2], path_database[1], path_database[0], path_database[3],
    path_database[4], path_database[5])

conn = sqlite3.connect(str(path_database))
conn.row_factory = dict_factory  # return dict when is query


GPS_PRN = range(1, 33)  # 32 satellites
Galileo_PRN = range(1, 31)  # 30 satellites
GNSS = {0: "GPS", 9: "GALILEO", 1: "SBAS"}


filename = "C:\Users\Martin\Diplomka\Project_v1\data\sat_data_V1A1.csv"
t1 = time.time()
with conn:
    cur = conn.cursor()
    cur.execute(query)
    with open(filename, 'rb') as f:
        reader = csv.reader(f)
        try:
            for row in reader:
                gps_time = row[1]
                TOW_ref = float(gps_time) % 604800
                break

            for row in reader:
                head = row
                break

            for row in reader:
                TOW = TOW_ref + float(row[0])
                Sat_PRN = row[4]
                Sat_type = row[2]
                Range = row[14]
                Sat = GNSS[int(Sat_type)] + str(Sat_PRN)
                if str(Sat_PRN) == "0" and str(Range) == "0":
                    continue
                data.append((None, float(TOW), str(Sat), float(Range)))

                if len(data) > 10000:  # s = 102.7 s; bez = 99.7 s
                    cur.executemany("INSERT INTO GEN VALUES(?, ?, ?, ?)", data)
                    data = []

                if False:
                    print row
                    print TOW
                    print Sat_PRN
                    print Sat_type
                    print Sat
                    print Range
                    print data

            cur.executemany("INSERT INTO GEN VALUES(?, ?, ?, ?)", data)
        except csv.Error as e:
            sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))

