import sqlite3
from sqlite3 import Error
from file_read_backwards import FileReadBackwards

def sql_connection():

    try:

        con = sqlite3.connect('calldata.db')

        return con

    except Error:

        print(Error)

def sql_table(con):

    cursorObj = con.cursor()

    cursorObj.execute("CREATE TABLE hams(callsign text PRIMARY KEY, lastname text, firstname text, MI text, staddress text, city text, state text, zip text, granted text, expires text)")

    con.commit()

def insert_ham(ham_info):

    cursorObj = con.cursor()

    cursorObj.execute("INSERT INTO hams(callsign, lastname, firstname, MI, staddress, city, state, zip) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", ham_info)

    con.commit()

def importFCCdata():

    with FileReadBackwards("EN.dat",encoding="utf-8") as in_file, open("calldata.txt", 'w') as out_file:
        callsign_set = set()
        for line in in_file:
            line_list = line.split('|')
            if line_list[4] in callsign_set:
                continue
            else:
                callsign_set.add(line_list[4])
                ham_info = (line_list[4], line_list[10], line_list[8], line_list[9], line_list[15], line_list[16], line_list[17], line_list[18])
                insert_ham(ham_info)

con = sql_connection()

sql_table(con)

importFCCdata()